Начинаем поэтапное решение заданий мирового чемпионата #ExcelWordlChamp, напоминаем, что согласно правил, задания чемпионата разбиты на разделы: Data Relays (манипулирование данными в книге), Chart Gymnastics (визуализация данных), Formula Wresting (использование формул Excel).
Первый этап чемпионата проходил с 3 по 9 октября и являл собой проверку базовых знаний в упомянутых трех направлениях. В начале этапа всем желающим принять участие, становился доступным файл Excel, скачав который, можно приступить к выполнению заданий.
Смотрите также видеоверсию статьи «#ExcelWordlChamp 1-1 решаем задачи первого тура».
Data Relays часть 1
В первом этапе раздел, ответственный за манипулирование данными, представлен двумя листами с заданиями. Сами задания не сложные, но относительно объемные, поэтому в текущем материале рассмотрим решение заданий первого листа.
На листе находится три таблицы с данными об атлетах, которые соревновались в беге на 100 метров.
В первой таблице результаты забегов в 3х раундах, атлеты, которые имеют результат в третьем раунде, имеют результат во втором и первом раунде, однако, не все атлеты, которые имеют результаты 1го или второго раунда имеют результат третьего. Во второй таблице представлено распределение атлетов по странам, а в третьей – имена и возраст атлетов.
Задание первого тура #ExcelWorldChamp раздела Data Relays
Задание состоит в том, чтобы создать новую таблицу в которую вывести только имена атлетов, которые завершили третий раунд, страну атлета, его возраст и результаты всех трех раундов в один рядок.
Данные в новой таблице должны быть отсортированы по результату забега в третьем раунде. Если атлеты имели одинаковый результат забега в третьем раунде, то дальнейшую сортировку следует сделать по результату второго раунда (также от меньшего значения к большему).
Выполнение задания
Первоначальные данные – имена атлетов, завершивших третий раунд можно просто скопировать, лучше это будет сделать формулой с проверкой на раунд:
=ЕСЛИ(D20=3;E20;НД())
Если раунд равняется “3”, тогда записать данные имени соответствующего спортсмена, в противном случае вызвать функцию НД(), которая вернет ошибку “#Н/Д”. Это позволит, с одной стороны, скопировать имена атлетов, а с другой убедится, что лишних данных нет (атлетов из второго тура).
Для вывода страны атлета нужно будет воспользоваться связкой функций ИНДЕКС и ПОИСКПОЗ, которые решают проблему функции ВПР, когда столбец с искомым значением не первый. Формула для поиска страны атлета получилась следующая:
=ИНДЕКС(T01_01_02;ПОИСКПОЗ(L4;T01_01_02[Name];0);1)
Поскольку исходные данные отформатированы как таблицы, то использовать абсолютные ссылки для закрепления областей не было необходимости. Подробно рассмотреть работу связки ИНДЕКС и ПОИСКПОЗ можно в данном материале.
Эту же связку можно было бы использовать и при нахождении возраста атлета, в исходной таблице имя атлета находится в первом столбце (а именно по имени и происходит выборка), поэтому здесь смело можно использовать более простую в освоении формулу ВПР.
=ВПР(L4;T01_01_03;2;ЛОЖЬ)
Ищется имя атлета и возвращается значение второго столбца.
Что касается данных для забегов то здесь также удобно использовать ВПР, однако, необходимо, во первых, для каждого раунда выделять только часть таблицы с раундом.
Такую однотипную операцию необходимо проделать для каждого из трех раундов и на последок остается только отсортировать полученные данные, вкладка “Главная”, группа “Редактирование”, команда “Сортировка и фильтр/ настраиваемая сортировка”
Изначально сортируются данные по результату забега третьего раунда, а потом по второму раунду.
На этом все, подробное выполнение задания с пояснениями можно посмотреть на видео.
