#ExcelWordlChamp 1-3 строим графики и формулы

В заключительной части первого тура решения задач по мировому чемпионату по Excel мы будем строить диаграмму и упражняться в построении формул для автоматизации вычислений.

Создание диаграммы (Chart Gymnastics)

После задания на манипуляцию данными Data Relays, решение которых мы рассматривали в первой и во второй части, участникам предлагается построить такую диаграмму, которая наилучшим образом будет представлять табличные данные.

image

Итак, согласно правилам подбора правильного типа диаграммы, во-первых, для сравнения следует использовать гистограмму, а во-вторых, для сравнения во времени отлично подойдет график. Кроме того, в одной таблице фактически присутствуют данные, которые условно можно разделить на два типа (участники и количество стран/мероприятий), поэтому выберем смешанный тип диаграммы.

image

Для смешанного типа диаграммы по оси "х" отложим года, а ось "у" будет двойной на основной оси "y" представим участников, а на вспомогательной – количество стран участниц и мероприятий. Для отслеживания количества олимпийских игр и стран-участниц в динамике хорошим вариантом будет график. Для отображения количества соревнующихся была выбрана гистограмма, причем вариант с накоплением, таким образом удастся визуально оценить участников сразу по нескольким параметрам:

  • общее количество участников на олимпийских играх;
  • соотношение между мужчинами и женщинами;
  • долевое соотношение между частниками, разделенными по половому признаку.
Упражнения с формулами (Formula Wrestling)

В последнем задании необходимо продемонстрировать свои навыки логического мышления при построении универсальных формул, которые будут автоматически пересчитываться в случае, если исходные данные поменяются. При решении задания разрешается использовать пустую область справа для вспомогательных вычислений, в том числе и ссылаться на эти вычисления.

Всего нужно ответить на 5 вопросов:

  1. Какой средний результат между всеми тремя раундами?
  2. Какой возраст самого молодого атлета?
  3. Сколько атлетов 25 лет и моложе?
  4. Какой самый быстрый результат (не зависимо от раунда) для атлета 25 лет и моложе?
  5. Сколько всего стран принимало участие?

Поочередно ответим на поставленные вопросы.

Какой средний результат между всеми тремя раундами.

С первого взгляда хочется просто воспользоваться функцией СРЗНАЧ, выделив все результаты, однако, в этом случае будет подсчитано среднее значение забегов вне зависимости от раунда, а не средний результат раунда, поэтому рассчитаем среднее каждого раунда, а потом найдем среднее между тремя средними.

Несмотря на такой каламбур слов формула очень простая.

=СРЗНАЧ(СРЗНАЧ(первый_раунд);СРЗНАЧ(первый_раунд);СРЗНАЧ(первый_раунд))

Какой возраст самого молодого атлета.

Молодой атлет имеет минимальный возраст, поскольку необходимо найти его возраст, то просто вводим функцию МИН.

=МИН(G20:G95)

Сколько атлетов 25 лет и моложе.

Для ответа на этот вопрос понадобиться функция СЧЁТЕСЛИ, не забываем, что условия необходимо вводить в кавычках.

=СЧЁТЕСЛИ(G20:G95;"<=25")

Какой самый быстрый результат (не зависимо от раунда) для атлета 25 лет и моложе.

Единственное задание первого тура где необходимо сделать дополнительные вычисления в свободной области справа. Промежуточное вычисление заключается в банальном нахождении самого быстрого результата вне зависимости от раунда для каждого атлета. Это делается с применением функции МИН, а последующее нахождение самого быстрого результата атлета 25 лет и моложе производится с помощью новой функции Excel 2016 – МИНЕСЛИ.

=МИНЕСЛИ($P$4:$P$79;$G$20:$G$95;"<25")

image

Сколько всего стран принимало участие.

Суть последнего задания заключается в подсчете уникальных текстовых записей. Это можно сделать с помощью простой универсальной формулы, в основе которой лежит функция СЧЁТЕСЛИ, либо с помощью более громоздкой формулы в основе которой лежит функция ЧАСТОТА.

Поскольку число записей небольшое с производительностью в вычислениях проблем не будет, поэтому воспользуемся простой формулой подсчета уникальных значений.

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(F20:F95;F20:F95))

Как видим, из 76 записей в исходной таблице только 48 записей представляют уникальные страны.

image