#ExcelWordlChamp 1-1 решаем задачи первого тура

Начинаем поэтапное решение заданий мирового чемпионата #ExcelWordlChamp, напоминаем, что согласно правил, задания чемпионата разбиты на разделы: Data Relays (манипулирование данными в книге), Chart Gymnastics (визуализация данных), Formula Wresting (использование формул Excel).

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

Data Relays часть 1

В первом этапе раздел, ответственный за манипулирование данными, представлен двумя листами с заданиями. Сами задания не сложные, но относительно объемные, поэтому в текущем материале рассмотрим решение заданий первого листа.

image

На листе находится три таблицы с данными об атлетах, которые соревновались в беге на 100 метров.

image

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

Задание первого тура #ExcelWorldChamp раздела Data Relays

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

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

Выполнение задания

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

=ЕСЛИ(D20=3;E20;НД())

Если раунд равняется “3”, тогда записать данные имени соответствующего спортсмена, в противном случае вызвать функцию НД(), которая вернет ошибку “#Н/Д”. Это позволит, с одной стороны, скопировать имена атлетов, а с другой убедится, что лишних данных нет (атлетов из второго тура).

image

Для вывода страны атлета нужно будет воспользоваться связкой функций ИНДЕКС и ПОИСКПОЗ, которые решают проблему функции ВПР, когда столбец с искомым значением не первый. Формула для поиска страны атлета получилась следующая:

=ИНДЕКС(T01_01_02;ПОИСКПОЗ(L4;T01_01_02[Name];0);1)

Поскольку исходные данные отформатированы как таблицы, то использовать абсолютные ссылки для закрепления областей не было необходимости. Подробно рассмотреть работу связки ИНДЕКС и ПОИСКПОЗ можно в данном материале.

image

Эту же связку можно было бы использовать и при нахождении возраста атлета, в исходной таблице имя атлета находится в первом столбце (а именно по имени и происходит выборка), поэтому здесь смело можно использовать более простую в освоении формулу ВПР.

=ВПР(L4;T01_01_03;2;ЛОЖЬ)

Ищется имя атлета и возвращается значение второго столбца.

image

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

image

Такую однотипную операцию необходимо проделать для каждого из трех раундов и на последок остается только отсортировать полученные данные, вкладка “Главная”, группа “Редактирование”, команда “Сортировка и фильтр/ настраиваемая сортировка”

image

Изначально сортируются данные по результату забега третьего раунда, а потом по второму раунду.

На этом все, подробное выполнение задания с пояснениями можно посмотреть на видео.