Продолжаем решать первого, разогревочного тура мирового чемпионата по табличному процессору Excel. Что за чемпионат, можно узнать из этой новости, первая часть решения задач первого тура находится здесь.
Вторая часть первого тура также посвящена теме Data Relays, т.е. проверке владения необходимыми навыками для манипуляции данных.
Смотрите также видеоверсию статьи «#ExcelWordlChamp 1-2 решаем задачи первого тура часть 2».
Что нужно сделать?
Вторая часть задания по манипуляции данными заключается в работе с одной таблицей, в которой содержаться результаты атлетов забега на 200 метров. Задание заключается в том, чтобы создать новую таблицу, в которой будут размещается страна и ее лучший результат (причем страны не должны повторяться). Запись необходимо дополнить именем атлета, его номером, раундом, в котором показан лучший результат, и временем. Сортировку необходимо выполнить по результату, а если встречается два одинаковых результат, то по номере атлета (атлет с меньшим номером должен находится выше по списку).
Выбираем уникальные записи стран и минимальный результат забега
В нашем случае записи стран – это и будет отправной точкой в решении задачи. Необходимо скопировать столбце со странами в область справа и удалить дубликаты, быстрее всего это можно сделать командой “Удалить дубликаты”, группы “Работа с данными” вкладки “Данные”.
После удаления дубликатов необходимо занести результаты забегов стран. Здесь есть пара нюансов, во-первых, у одной страны может быть несколько результатов, а, во-вторых, минимальный результат может быть в любом из трех раундов. Самым быстрым способом решения такой задачи будет использование функции МИНЕСЛИМН (MINIFS), правда подойдет он только владельца последней версии Excel 2016. Для предыдущих версий табличного процессора придется использовать конструкцию с использованием логической ЕСЛИ и промежуточными вычислениями.
=МИНЕСЛИ(минимальный_диапазон;диапазон_условий1;условие1)
В нашем случаем первым будет диапазон с результатами забега в исходной таблице, диапазон условий – это диапазон со странами, также из исходной таблицы, а условие — ссылка на ячейку со страной из отфильтрованного списка стран.
Используем конструкцию ИНДЕКС и ПОИСКПОЗ
Нахождение остальных данных для новой таблице (раунд, имя атлета и его номер) нужно будет использовать чрезвычайно удобную связку функций ИНДЕКС и ПОИСКПОЗ. Однако, прежде чем приступить непосредственно к выборке, на необходимо определиться с уникальным значением по которому и осуществлять поиск строки в исходной таблице. Изначально такого значения нет, но его можно сделать соединив значения двух столбцов, поскольку новая таблица строится вокруг списка стран с результатом лучшего забега, то этими столбцами будет страна и результат забега.
Для соединения двух текстовых строк можно использовать новую функцию СЦЕП (или СЦЕПИТЬ, если у вас не Excel 2016), а можно обойтись простым “&”.
Теперь все готово для нахождения недостающих данных таблицы (номера раунда, имя и номера атлета). Формула для нахождение всех этих параметров будет отличаться только номером возвращаемого столбца.
=ИНДЕКС(исходная_таблица;ПОИСКПОЗ(СЦЕП(столбец_страна;столбец_время_забега);созданный_идентификатор;0);номер_столбца)
Например, для имени атлета формула следующая:
=ИНДЕКС(T01_02_01;ПОИСКПОЗ(СЦЕП(Q4;O4);T01_02_01[Column1];0);3)
Соответственно, формулы для номера атлета и раунда будут отличаться только “номером_столбца” (2 и 1).
На последнем этапе выполнения этого задания остается только отсортировать полученную таблицу с помощью настраиваемой сортировки. Вкладка “Главная”, команда “Сортировка и фильтр / настраиваемая сортировка”.
Сначала таблицу следует отсортировать по результату лучшего забега, а, если две страны имеют одинаковый результат, тогда произвести сортировку по номеру атлета (атлет с меньшим номером должен находится выше по списку).
На этом с заданием все, до встречи в следующем задании.
