Третий тур мирового чемпионата #ExcelWorldChamp, как и положено, отличился самыми сложными заданиями и, если первые части отличаются просто большим количеством итераций для достижения результата, то упражнение с формулами заставило изрядно попотеть, однако, давайте обо всем по порядку.
Смотрите также видеоверсию статьи «#ExcelWorldChamp 3-1 непростое задание с Power Query».
Третий тур Data Relays часть 1
В первой части заданий представлены результаты 3-х полуфинальных забегов спортсменок на 400 метров. Каждый отдельный забег отображен в отдельной таблице, в разных забегах принимали участи различные спортсменки.
Участница забега попадает в финал, если она оказалась в числе двух лучших в своем забеге или в числе двух лучших по результатам всех забегов, исключая тех, кто вышел в финал по результатам своего забега. Таким образом, всего должно быть 8 финалисток.
Вот, кстати, исходные таблицы с результатами забегов.
Что необходимо сделать:
Используя инструмент Power Query необходимо создать обновляемый запрос:
- Соединить все результаты забегов в одну таблицу.
- Рассчитать точный возраст участниц в годах на дату 14 августа 2016 года. Результат не округлять, например 25,67 – корректный результат.
- Напротив записей участников добавить запись (TRUE/FALSE), где TRUE – получает атлет, добравшийся до финала, а FALSE – соответственно, нет.
Для решения задачи можно делать несколько различных запросов к исходным данным.
Результирующая таблица должна содержать: имена атлетов, страну, номер раунда, возраст, результат забега и TRUE/FALSE квалификацию в финал.
Финальную таблицу следует отсортировать в зависимости от результата забега от самого быстрого до самого медленного, если атлеты имеют одинаковый результат забега, дальнейшую сортировку произвести по номеру (от меньшего к большему).
Совмещаем все таблицы воедино и определяем финалисток
Соединять таблицы воедино и добавлять результат попадания в финал будем поэтапно. Сначала необходимо сделать 3 запроса к каждой таблице. Вкладка «Данные», группа «Скачать и преобразовать», команда «Из таблицы».
Для всех трех запросов выбираем, при выгрузке результата, «Только создать соединение».
В результате в редакторе запросов у нас должны появиться три подключения.
Теперь из каждой таблицы необходимо выбрать двух лучших по результатам забега (с минимальным временем), плюс запросы к самим таблицам следует оставить в первозданном виде.
Выбираем первый запрос, дублируем его с помощью контекстного меню на самом запросе, сортируем таблицу по результату забега и оставляем только первые две строки, с помощью команды «Сократить строки» на вкладке «Главная» редактора запросов.
Дальше добавляем новый столбец командой «Добавить пользовательский столбец», а в формуле столбца просто пишем значение «TRUE», не забывая изменить заголовок столбца на «Finals».
В завершение неплохо переименовать запрос, чтобы однозначно его идентифицировать, например, «Round1Finals». Такую же операцию по выявлению финалисток следует произвести для второго и третьего раунда.
Следующим шагом будет выявление двух финалисток среди оставшихся спортсменок, т.е. двух лучших среди всех раундов, исключив шестерых отобранных. Для этого нам снова необходимо дублировать один их трех первых запросов запросов, а затем, с помощью команды «Комбинировать» добавить данные остальных двух, например, если взять запрос к первой таблице и добавить данные запросов ко второй и третей таблицы, то получится вот так:
И результат добавления всех трех запросов.
Дальше с помощью той же команды «Комбинировать», но выбираем «Слияние запросов» добавляем столбцы «Finals» из запросов: Round1Finals, Round2Finals, Round3Finals. Эту операцию следует проделать трижды, а в качестве столбцов для согласования лучше выбрать номер атлета (теоретически можно и имя, но могут попасться атлеты с одинаковыми именами).
В трех новых столбцах следует отобразить результат прохождения в финал, т.е. трижды выбрать столбце «Finals».
Теперь, следует убрать спортсменок, которые прошли в финал, т.е. отобразить только записи, которые содержать в последних трех столбцах значение «null». Фильтр делается по заголовку столбца, ровно также, как работает стандартный Автофильтр в Excel. Дальше сортируем записи по результату, оставляем только 2 первые и добавляем новый столбце «Finals» в который записываем значение «TRUE», а три столбца «Finals» которые остались от предыдущих запросов удаляем. В завершение можно переименовать наш запрос в «FinalAccrossRounds».
Теперь с помощью знакомой команды «Комбинировать» соединяем всех финалисток в одну таблицу и называем получившийся запрос «FinalAllRounds».
Наконец, пришло время скомбинировать итоговую таблицу, которую, в конечном итоге выгрузим на рабочий лист в качестве ответа. Итоговая таблица будет содержать в себе объединение трех первых таблиц (Комбинировать/Добавить запросы) и столбец «Finals» из таблицы «FinalAllRounds». В роли «столбца-ключа» выступит номер атлета.
Осталось только поменять пустые значения на «FALSE». Делается это командой «Замена значений» из группы «Любой столбец» вкладки «Преобразование» или из контекстного меню по заголовку столбца.
Все итоговая таблица готова, можно смело выгружать ее на, но перед выгрузкой проведем сортировку, как сказано в задании сначала по результату забега, а если два одинаковых результата, тогда по номеру атлета.
Осталось только посчитать возраст атлетов, это делается формулой основу которой будет составлять функция ДОЛЯГОДА (YEARFRAC).
=ДОЛЯГОДА(ДАТА([@[Birth Year]];[@[Birth Month]];[@[Birth Day]]);ДАТА(2016;8;16);1)
Простая формула, главное знать, что есть для таких целей специальная функция.
Итоговая таблица.
