В заключительной части первого тура решения задач по мировому чемпионату по Excel мы будем строить диаграмму и упражняться в построении формул для автоматизации вычислений.
Смотрите также видеоверсию статьи «#ExcelWordlChamp 1-3 строим графики и формулы».
Создание диаграммы (Chart Gymnastics)
После задания на манипуляцию данными Data Relays, решение которых мы рассматривали в первой и во второй части, участникам предлагается построить такую диаграмму, которая наилучшим образом будет представлять табличные данные.
Итак, согласно правилам подбора правильного типа диаграммы, во-первых, для сравнения следует использовать гистограмму, а во-вторых, для сравнения во времени отлично подойдет график. Кроме того, в одной таблице фактически присутствуют данные, которые условно можно разделить на два типа (участники и количество стран/мероприятий), поэтому выберем смешанный тип диаграммы.
Для смешанного типа диаграммы по оси «х» отложим года, а ось «у» будет двойной на основной оси «y» представим участников, а на вспомогательной – количество стран участниц и мероприятий. Для отслеживания количества олимпийских игр и стран-участниц в динамике хорошим вариантом будет график. Для отображения количества соревнующихся была выбрана гистограмма, причем вариант с накоплением, таким образом удастся визуально оценить участников сразу по нескольким параметрам:
- общее количество участников на олимпийских играх;
- соотношение между мужчинами и женщинами;
- долевое соотношение между частниками, разделенными по половому признаку.
Упражнения с формулами (Formula Wrestling)
В последнем задании необходимо продемонстрировать свои навыки логического мышления при построении универсальных формул, которые будут автоматически пересчитываться в случае, если исходные данные поменяются. При решении задания разрешается использовать пустую область справа для вспомогательных вычислений, в том числе и ссылаться на эти вычисления.
Всего нужно ответить на 5 вопросов:
- Какой средний результат между всеми тремя раундами?
- Какой возраст самого молодого атлета?
- Сколько атлетов 25 лет и моложе?
- Какой самый быстрый результат (не зависимо от раунда) для атлета 25 лет и моложе?
- Сколько всего стран принимало участие?
Поочередно ответим на поставленные вопросы.
Какой средний результат между всеми тремя раундами.
С первого взгляда хочется просто воспользоваться функцией СРЗНАЧ, выделив все результаты, однако, в этом случае будет подсчитано среднее значение забегов вне зависимости от раунда, а не средний результат раунда, поэтому рассчитаем среднее каждого раунда, а потом найдем среднее между тремя средними.
Несмотря на такой каламбур слов формула очень простая.
=СРЗНАЧ(СРЗНАЧ(первый_раунд);СРЗНАЧ(первый_раунд);СРЗНАЧ(первый_раунд))
Какой возраст самого молодого атлета.
Молодой атлет имеет минимальный возраст, поскольку необходимо найти его возраст, то просто вводим функцию МИН.
=МИН(G20:G95)
Сколько атлетов 25 лет и моложе.
Для ответа на этот вопрос понадобиться функция СЧЁТЕСЛИ, не забываем, что условия необходимо вводить в кавычках.
=СЧЁТЕСЛИ(G20:G95;"<=25")
Какой самый быстрый результат (не зависимо от раунда) для атлета 25 лет и моложе.
Единственное задание первого тура где необходимо сделать дополнительные вычисления в свободной области справа. Промежуточное вычисление заключается в банальном нахождении самого быстрого результата вне зависимости от раунда для каждого атлета. Это делается с применением функции МИН, а последующее нахождение самого быстрого результата атлета 25 лет и моложе производится с помощью новой функции Excel 2016 – МИНЕСЛИ.
=МИНЕСЛИ($P$4:$P$79;$G$20:$G$95;"<25")
Сколько всего стран принимало участие.
Суть последнего задания заключается в подсчете уникальных текстовых записей. Это можно сделать с помощью простой универсальной формулы, в основе которой лежит функция СЧЁТЕСЛИ, либо с помощью более громоздкой формулы в основе которой лежит функция ЧАСТОТА.
Поскольку число записей небольшое с производительностью в вычислениях проблем не будет, поэтому воспользуемся простой формулой подсчета уникальных значений.
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(F20:F95;F20:F95))
Как видим, из 76 записей в исходной таблице только 48 записей представляют уникальные страны.
