#1. Решаем задачи в Excel

Опубликовано: 24 июня 2019

Примеры ExcelВ рубрике «Решаем задачи в Excel» будут рассматриваться конкретные примеры и их реализация с помощью программы Эксель.

При этом будут подробно разбираться стандартные функциии Excel и их применение. Например, сейчас мы рассмотрим функции: МАКС, ПОИСКПОЗ, ИНДЕКС, СЦЕПИТЬ.

Давайте решим вот такую задачку, которую предложили в комментариях:

Задача для решения в Excel

То есть у нас есть два столбца с данными — в первом находится список имен сотрудников, а во втором указан их возраст.

Начальные условия

Требуется определить сотрудника с максимальным возрастом и вывести его имя в отдельной строке, например, в виде: Егор-55.

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

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

Итак, во-первых, нам нужно определить максимальный возраст. Сделать это можно с помощью функции МАКС, которая возвращает максимальное значение из списка аргументов. В данном случае списком аргументов у нас будет являться диапазон значений из столбца «Возраст».

Функция МАКС в Excel

Но нам нужно вывести не только возраст, но и имя сотрудника. Имя сотрудника и его возраст находятся в одной строке, поэтому нам нужно определить номер этой строки.

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

Наш критерий — это наибольший возраст сотрудника, поэтому формула будет выглядеть так:

Функция ПОИСКПОЗ в Excel

Указываем искомое значение, то есть нашу ячейку с рассчитанным максимальным возрастом, затем указываем диапазон, в котором это значение нужно найти, и в заключение указываем тип сопоставления.

Всего может быть три типа сопоставления: -1, 0 и 1.

При 1 функция найдет наибольшее значение, которое меньше или равно значению аргумента, при -1 найдет наименьшее значение, которое больше или равно значению аргумента. Мы же укажем 0, так как в этом случае функция ПОИСКПОЗ выведет первое значение найденное в диапазоне, которое равно искомому значению, что нам и нужно сделать.

В итоге получим цифру 5 — это порядковый номер строки в выбранном нами диапазоне:

Порядковый номер строки в выбранном диапазоне ячеек

Итак, осталось лишь вывести имя сотрудника, которому соответствует максимальный возраст. Для этого воспользуемся функцией ИНДЕКС, которая возвращает значение ячейки, заданного номером строки и номером столбца.

Функция ИНДЕКС в Excel

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

Осталось вывести результат в нужном виде, например, в таком - Егор-55

Для этого воспользуемся функцией СЦЕПИТЬ, которая позволяет соединить текстовые значения из нескольких ячеек в одну. Просто перечислим адреса ячеек через точку с запятой. Так как нам нужно разделить имя и возраст тире, то вставим его в функцию в виде текста, то есть в кавычках.

Функция сцепить в Excel

Готово! Остается лишь объединить все проделанные нами расчеты в одну формулу. Для этого поэтапно будем копировать и вставлять ранее нами созданные формулы, чтобы получить одну итоговую. То есть мы заменяем ссылки на ячейки с формулами самими формулами и делаем это последовательно.

Итоговая формула

Теперь можем удалить промежуточные расчеты.

Задача решена.

Результат расчетов в Excel

Не упустите возможность сделать доброе дело:

нажмите на кнопку социальной сети, в которой вы зарегистрированы, чтобы другие люди тоже получили пользу от этого материала. СПАСИБО!