В этой статье разберем три способа, которые позволяют "подтянуть" данные, находящиеся на пересечении определенного столбца и строки. Все три способа уже упоминались мною ранее в контексте рассмотрения функций или инструментов Эксель, но теперь хочу объединить их в одной статье.
Данный пример был разобран в статье, посвященной функции ПРОСМОТРХ и именно применение этой функции, пожалуй, будет наиболее простым решением.
Задача состоит в следующем - по указанным параметрам из основной таблицы необходимо получить значение.
Функция ПРОСМОТРХ
Функция ПРОСМОТРХ может работать с динамическими массивами, а также умеет производить поиск как по вертикали, так и по горизонтали. Поэтому необходимая нам формула будет включать две вложенные функции ПРОСМОТРХ. С помощью первой функции будем искать значение в столбце.
На выходе получим горизонтальный динамический диапазон, в котором теперь нужно найти данные, соответствующие указанному месяцу, поэтому созданную формулу обернем еще одной функцией ПРОСМОТРХ, в которой будем искать название месяца в строке заголовков и по этому названию подтянем значение из ранее полученного горизонтального массива.
Это вариант решения имеет один серьезный недостаток - функция ПРОСМОТРХ доступна лишь в последних версиях Эксель и если на вашем компьютере установлен Microsoft Office 2019 или более ранний, то придется искать альтернативное решение.
Связка функций ИНДЕКС и ПОИСКПОЗ
Универсальным решением, доступным во всех версиях Эксель, станет использование связки функций ИНДЕКС и ПОИСКПОЗ.
Напомню, что функция ИНДЕКС возвращает значение из выбранного диапазона по указанным координатам, то есть нужно указать номер строки и столбца, на пересечении которых находится искомое значение. В нашем примере диапазоном будет вся область значений таблицы, то есть без заголовков строк и столбцов. Необходимо найти значение на пересечении третей строки и третьего столбца. Если укажем номер строки и столбца вручную, то подтянется необходимая сумма.
Чтобы автоматизировать подстановку номеров строки и столбца задействуем функцию ПОИСКПОЗ. Она возвращает позицию искомого значения в указанном диапазоне. Нам нужно найти позицию выбранного объекта в столбце с наименованиями.
Теперь по аналогии найдем позицию указанного месяца в строке заголовков.
Все, что осталось сделать, это объединить все три формулы в одну.
Пересечение диапазонов
И есть еще одно довольно изящное решение - мы можем задействовать оператор пересечения диапазонов.
Создадим именованные диапазоны для всех строк и столбцов исходной таблицы. Сделать это очень просто - выделяем всю таблицу с заголовками и через вкладку Формулы выбираем пункт, позволяющий создать именованные диапазоны из выделенного.
В появившемся диалоговом окне указываем, что имена будут автоматически создаваться на основе данных из верхней строки и левого столбца.
В результате для каждой строки и столбца будет создан отдельный именованный диапазон, в чем легко убедиться, открыв Диспетчер имен.
Чтобы получить данные на пересечении этих диапазонов создаем формулу, в которой через пробел указываем их названия. Пробел и является оператором пересечения.
Вот только мы не можем прямо сослаться на ячейки с интересующими нас значениями, так как нам нужны не ссылки на ячейки, а значения, которые в них находятся.
Получить их поможет функция ДВССЫЛ.
Таким образом задача поиска данных по строке и столбцу имеет, как минимум три решения.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм