#ExcelWorldChamp 3-1 непростое задание с Power Query

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

Третий тур Data Relays часть 1

В первой части заданий представлены результаты 3-х полуфинальных забегов спортсменок на 400 метров. Каждый отдельный забег отображен в отдельной таблице, в разных забегах принимали участи различные спортсменки.

Участница забега попадает в финал, если она оказалась в числе двух лучших в своем забеге или в числе двух лучших по результатам всех забегов, исключая тех, кто вышел в финал по результатам своего забега. Таким образом, всего должно быть 8 финалисток.

Вот, кстати, исходные таблицы с результатами забегов.

Задание Data Relays часть1 ExcelWorldChamp третьего тура

Задание Data Relays часть1 ExcelWorldChamp третьего тура

Что необходимо сделать:

Используя инструмент Power Query необходимо создать обновляемый запрос:

  1. Соединить все результаты забегов в одну таблицу.
  2. Рассчитать точный возраст участниц в годах на дату 14 августа 2016 года. Результат не округлять, например 25,67 – корректный результат.
  3. Напротив записей участников добавить запись (TRUE/FALSE), где TRUE – получает атлет, добравшийся до финала, а FALSE – соответственно, нет.

Для решения задачи можно делать несколько различных запросов к исходным данным.

Результирующая таблица должна содержать: имена атлетов, страну, номер раунда, возраст, результат забега и TRUE/FALSE квалификацию в финал.

Финальную таблицу следует отсортировать в зависимости от результата забега от самого быстрого до самого медленного, если атлеты имеют одинаковый результат забега, дальнейшую сортировку произвести по номеру (от меньшего к большему).

Совмещаем все таблицы воедино и определяем финалисток

Соединять таблицы воедино и добавлять результат попадания в финал будем поэтапно. Сначала необходимо сделать 3 запроса к каждой таблице. Вкладка "Данные", группа "Скачать и преобразовать", команда "Из таблицы".

Команда для создания запроса Power Query

Команда для создания запроса Power Query

Для всех трех запросов выбираем, при выгрузке результата, "Только создать соединение".

Выгрузка запроса

Выгрузка запроса

В результате в редакторе запросов у нас должны появиться три подключения.

Три созданных запроса в редакторе Power Query

Три созданных запроса в редакторе Power Query

Теперь из каждой таблицы необходимо выбрать двух лучших по результатам забега (с минимальным временем), плюс запросы к самим таблицам следует оставить в первозданном виде.

Выбираем первый запрос, дублируем его с помощью контекстного меню на самом запросе, сортируем таблицу по результату забега и оставляем только первые две строки, с помощью команды "Сократить строки" на вкладке "Главная" редактора запросов.

Команда Сократить строки в Power Query

Команда Сократить строки в Power Query

Дальше добавляем новый столбец командой "Добавить пользовательский столбец", а в формуле столбца просто пишем значение "TRUE", не забывая изменить заголовок столбца на "Finals".

Добавление пользовательского столбца

Добавление пользовательского столбца

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

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

Соединение нескольких запросов

Соединение нескольких запросов

И результат добавления всех трех запросов.

Результат добавления всех трех запросов

Результат добавления всех трех запросов

Дальше с помощью той же команды "Комбинировать", но выбираем "Слияние запросов" добавляем столбцы "Finals" из запросов: Round1Finals, Round2Finals, Round3Finals. Эту операцию следует проделать трижды, а в качестве столбцов для согласования лучше выбрать номер атлета (теоретически можно и имя, но могут попасться атлеты с одинаковыми именами).

Диалоговое окно слияния запросов

Диалоговое окно слияния запросов

В трех новых столбцах следует отобразить результат прохождения в финал, т.е. трижды выбрать столбце "Finals".

Нахождение двух финалисток вне раунда

Нахождение двух финалисток вне раунда

Теперь, следует убрать спортсменок, которые прошли в финал, т.е. отобразить только записи, которые содержать в последних трех столбцах значение "null". Фильтр делается по заголовку столбца, ровно также, как работает стандартный Автофильтр в Excel. Дальше сортируем записи по результату, оставляем только 2 первые и добавляем новый столбце "Finals" в который записываем значение "TRUE", а три столбца "Finals" которые остались от предыдущих запросов удаляем. В завершение можно переименовать наш запрос в "FinalAccrossRounds".

Найдены последние две финалистки (всего их 8)

Найдены последние две финалистки (всего их 8)

Теперь с помощью знакомой команды "Комбинировать" соединяем всех финалисток в одну таблицу и называем получившийся запрос "FinalAllRounds".

Список всех восьми финалисток

Список всех восьми финалисток

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

Итоговая таблица которая будет выгружена на лист Excel

Итоговая таблица которая будет выгружена на лист Excel

Осталось только поменять пустые значения на "FALSE". Делается это командой "Замена значений" из группы "Любой столбец" вкладки "Преобразование" или из контекстного меню по заголовку столбца.

Замена значений в Редакторе запросов

Замена значений в Редакторе запросов

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

Результат работы Power Query на листе Excel

Результат работы Power Query на листе Excel

Осталось только посчитать возраст атлетов, это делается формулой основу которой будет составлять функция ДОЛЯГОДА (YEARFRAC).

=ДОЛЯГОДА(ДАТА([@[Birth Year]];[@[Birth Month]];[@[Birth Day]]);ДАТА(2016;8;16);1)

Простая формула, главное знать, что есть для таких целей специальная функция.

Итоговая таблица.

Конечный результат уже с расчетом возраста

Конечный результат уже с расчетом возраста