Завершающая часть завершающей части, изначально планировалось совместить построение диаграмм с упражнениями с формулами, однако, видео получилось слишком длинным, поэтому упражнения с формулами были вынесены в отдельную часть.
Итак, во втором туре использовались функции: ВПР, ГПР, ИНДЕКС, ПОИСКПОЗ, СЦЕП, СУММ, СЧЁТЕСЛИ, СЧЁТ, МИН, МАКС, НАИБОЛЬШИЙ. Данный материал будет отличной тренировкой для тех, кто хочет немного подтянуть свой уровень.
Смотрите также видеоверсию статьи «#ExcelWorldChamp 2-4 упражнения с формулами. Хитрые судьи».
Упражнения с формулами (Formula Wrestling)
Переходим к упражнению с формулами второго тура. В отличие от упражнений третьего тура (где упражнение с формулами оказалось настоящим выносом мозга), здесь формулы не доставили больших проблем, хотя и рассчитаны на подготовленного пользователя.
Общие условия для упражнений с формулами
Условия для расчетов с помощью создания формул не изменились: можно использовать дополнительную область для вспомогательных расчетов, формулы могут ссылаться на вспомогательные расчеты, все значения формул должны пересчитываться автоматически при изменении выходных условий и последнее, засчитываться будет не только правильность расчетов, но и их простота и аккуратность. Чем меньше и понятнее операции, тем лучше.
Специфические условия для второго тура упражнений с формулами
Исходные условия представлены заданиями из трех таблиц:
Таблица 1 отображает очки гимнастов от 5 судей. Финальный результат гимнаста – это среднее судейских оценок исключая самую высокую и самую низкую оценки.
Таблица 2 отображает страну гимнастов.
Таблица 3 отображает страну судей.
Собственно вопросы на которые нужно ответить:
- Который из гимнастов занял 3-е место (лучший третий результат). (В ответе указать только номер атлета)
- Какую страну представляет гимнаст, занявший 4-е место. (Ответ представить в виде Страна-Атлет)
- Какой судья дал больше всего минимальных оценок. (В ответе указать только номер судьи)
- Оценки какого судьи исключались чаще всего. (В ответе указать номер судьи)
- В ситуациях, когда судья и атлет представляли одну и ту же страну, сколько раз результаты выбрасывались.
Гимнаст, который занял третье место.
Для выполнения этого задания, как, впрочем, и следующего необходимо рассчитать финальные результаты гимнастов., среднюю оценку без максимального и минимального балла.
=(СУММ(F26:J26)-МАКС(F26:J26)-МИН(F26:J26))/(СЧЁТ(F26:J26)-2)
И расчет итоговых оценок для гимнастов.
Теперь, чтобы вычислить третье место, достаточно в первом столбце найти третье наибольшее число функцией НАИБОЛЬШИЙ (LARGE), а номер атлета высчитать функцией ВПР (VLOOKUP).
=ВПР(НАИБОЛЬШИЙ($P$4:$P$11;3);$P$4:$Q$11;2;ЛОЖЬ)
Результат: 8173
Страна гимнаста, занявшего четвертое место
Для ответа на этот вопрос также понадобятся дополнительные расчеты сделанные для первого задания, чтобы найти результат 4го места, потом, на основании этого результата высчитываем номер студента, по номеру студента находим его страну из второй таблицы исходных условий и возвращаем результат с использованием функции СЦЕП (СЦЕПИТЬ, если у вас версия Excel ниже 2016) в формате: «Страна-Атлет».
Поскольку вторая таблица со странами и атлетами составлена так, что номер атлета находится во втором столбце, то, для нахождения страны по номеру атлета, приходится воспользоваться связкой ИНДЕКС и ПОИСКПОЗ.
=СЦЕП(ИНДЕКС(T03_01_02;ПОИСКПОЗ(ВПР(НАИБОЛЬШИЙ($P$4:$P$11;4);$P$4:$Q$11;2;ЛОЖЬ);T03_01_02[Athlete Number];0);1);"-";ВПР(НАИБОЛЬШИЙ($P$4:$P$11;4);$P$4:$Q$11;2;ЛОЖЬ))
Результат: ITA-3150
Какой судья дал больше всего минимальных оценок
Для ответа на этот вопрос необходимо найти минимальные оценки по каждому атлету и просуммировать их по судьям, понадобятся дополнительные вычисления. Если оценка минимальная, значит ставится 1.
Если оценка минимальная, значит ставится 1, в верху суммы минимальных оценок. В ответе номер судьи выбирается с помощью функции ГПР, по наибольшему значению в верхней строке.
=ГПР(МАКС($P$13:$T$13);$P$13:$T$14;2;ЛОЖЬ)
Результат: J4
Оценки какого судьи исключались чаще всего
Решение очень похоже на предыдущее задание, за тем исключением, что необходимо найти не только минимальные оценки, но и максимальные. В четвертом задании вспомогательная таблица будет содержать по две единички на каждого атлета.
А вот нахождение самого судьи аналогичное предыдущему варианту, используется функция ГПР (HLOOKUP).
=ГПР(МАКС($P$24:$T$24);$P$24:$T$25;2;ЛОЖЬ)
Результат: J2
В ситуациях, когда судья и атлет представляли одну и ту же страну, сколько раз результаты не учитывались
Вспомогательная таблица для последнего вопроса очень похожа на предыдущую, за тем исключением, что слева добавлены страны гимнастов, которым выставлялись оценки (фактически не записаны не сами гимнасты, а их страны), а на пересечении максимальной и минимальной оценки ставилась не «1» или «0», а страна судьи, который поставил либо максимальную, либо минимальную оценку. Знать это максимальная или минимальная оценка нем нет необходимости.
Поскольку минимальные и максимальные оценки уже найдены, то для вспомогательной таблицы последнего задания использовалась вспомогательная таблица предыдущего задания.
В последнем столбце подсчитываются только случаи совпадения страны атлета и страны судьи, как можно заметить, в коллегии присутствовали два судьи, представляющие страну BRA и своему гимнасту они одновременно поставили и максимальную и минимальную оценки. Вот так бывает.
Вся сложность последнего расчета заключалась в составлении дополнительных таблиц, поскольку финальная формула представляет собой сумму значений в последнем столбце.
=СУММ(V36:V43)
Результат: 4.
Не забывайте подписываться на обновления, совсем скоро решения заданий третьего тура.
