#1. Решаем задачи в Excel
В рубрике «Решаем задачи в Excel» будут рассматриваться конкретные примеры и их реализация с помощью программы Эксель.
При этом будут подробно разбираться стандартные функциии Excel и их применение. Например, сейчас мы рассмотрим функции: МАКС, ПОИСКПОЗ, ИНДЕКС, СЦЕПИТЬ.
Давайте решим вот такую задачку, которую предложили в комментариях:
То есть у нас есть два столбца с данными — в первом находится список имен сотрудников, а во втором указан их возраст.
Требуется определить сотрудника с максимальным возрастом и вывести его имя в отдельной строке, например, в виде: Егор-55.
Давайте разберем решение задачи и заодно рассмотрим функции Экселя, которые могут быть нам полезны.
Сразу хочу отметить, что у любой задачи может быть несколько решений и сейчас я предложу решение, которое первым пришло мне на ум.
Итак, во-первых, нам нужно определить максимальный возраст. Сделать это можно с помощью функции МАКС, которая возвращает максимальное значение из списка аргументов. В данном случае списком аргументов у нас будет являться диапазон значений из столбца «Возраст».
Но нам нужно вывести не только возраст, но и имя сотрудника. Имя сотрудника и его возраст находятся в одной строке, поэтому нам нужно определить номер этой строки.
Поможет нам в этом функция ПОИСКПОЗ, которая возвращает относительную позицию ячейки в массиве данных, соответствующую определенному критерию.
Наш критерий — это наибольший возраст сотрудника, поэтому формула будет выглядеть так:
Указываем искомое значение, то есть нашу ячейку с рассчитанным максимальным возрастом, затем указываем диапазон, в котором это значение нужно найти, и в заключение указываем тип сопоставления.
Всего может быть три типа сопоставления: -1, 0 и 1.
При 1 функция найдет наибольшее значение, которое меньше или равно значению аргумента, при -1 найдет наименьшее значение, которое больше или равно значению аргумента. Мы же укажем 0, так как в этом случае функция ПОИСКПОЗ выведет первое значение найденное в диапазоне, которое равно искомому значению, что нам и нужно сделать.
В итоге получим цифру 5 — это порядковый номер строки в выбранном нами диапазоне:
Итак, осталось лишь вывести имя сотрудника, которому соответствует максимальный возраст. Для этого воспользуемся функцией ИНДЕКС, которая возвращает значение ячейки, заданного номером строки и номером столбца.
Укажем весь диапазон ячеек столбца с именами, а в качестве номера строки укажем полученное нами ранее число. В итоге получаем имя.
Осталось вывести результат в нужном виде, например, в таком — Егор-55
Для этого воспользуемся функцией СЦЕПИТЬ, которая позволяет соединить текстовые значения из нескольких ячеек в одну. Просто перечислим адреса ячеек через точку с запятой. Так как нам нужно разделить имя и возраст тире, то вставим его в функцию в виде текста, то есть в кавычках.
Готово! Остается лишь объединить все проделанные нами расчеты в одну формулу. Для этого поэтапно будем копировать и вставлять ранее нами созданные формулы, чтобы получить одну итоговую. То есть мы заменяем ссылки на ячейки с формулами самими формулами и делаем это последовательно.
Теперь можем удалить промежуточные расчеты.
Задача решена.
