Как найти значение на пересечении строки и столбца в Excel?
В больших таблицах данные часто организованы как матрица, например:: строки — это менеджеры, столбцы — месяцы, а на пересечении — выручка. Нужно быстро узнать, сколько заработал конкретный менеджер в конкретном месяце. Вручную искать очень долго и можно ошибиться со значением, ВПР здесь не поможет, потому что поиск идёт сразу по двум параметрам: по строке (менеджер) и по столбцу (месяц).
Для таких задач есть связка функций ИНДЕКС и ПОИСКПОЗ, которая находит значение на пересечении нужной строки и столбца.
Шаг 1: Подготовка данных Предположим, у нас есть таблица:
Столбец B — список менеджеров.
Строка 2 — месяцы.
Внутри таблицы — значения выручки.
Шаг 2: Создаём формулу для поиска по двум параметрам В ячейку, где должен отображаться результат (например, D20), введите формулу:
B2:O15 — вся таблица с данными (захватываем полностью с шапкой).
B20 — ячейка с именем менеджера (значение, которое ищем в первом столбце таблицы).
B2:B15 — первый столбец таблицы, в котором ищем менеджера (столбец с именами).
C20 — ячейка с названием месяца (значение, которое ищем в первой строке таблицы).
B2:O2 — первая строка таблицы, в которой ищем месяц (шапка с месяцами).
0 — точное совпадение (для обоих ПОИСКПОЗ).
Шаг 3: Разбираем формулу
ПОИСКПОЗ(B20; B2:B15; 0) — находит номер строки, где в столбце B находится нужный менеджер (например, Иванов).
2.ПОИСКПОЗ(C20; B2:O2; 0) — находит номер столбца, где в строке 2 находится нужный месяц (например, Январь).
3.ИНДЕКС(B2:O15; номер_строки; номер_столбца) — возвращает значение из таблицы B2:O15 на пересечении найденных строки и столбца.
Важно: Если мы выделяем таблицу, начиная с ячейки B2 (ИНДЕКС(B2:O15...)), то и внутри функции ПОИСКПОЗ выделение столбца и строки должно начинаться с этой же ячейки B2 (ПОИСКПОЗ(B20; B2:B15...), ПОИСКПОЗ(C20; B2:O2...)).
Шаг 4: Добавляем удобные выпадающие списки Чтобы не вводить менеджеров и месяцы вручную:
Создайте список уникальных менеджеров (например, на другом листе).
При изменении менеджера или месяца в выпадающих списках формула автоматически пересчитывается и показывает нужное значение.
Хотите увидеть этот процесс вживую и узнать ещё больше полезных советов? Смотрите наглядный пример в нашем Telegram-канале: https://t.me/Natalia_ProExcel/199
Заключение
Связка ИНДЕКС и ПОИСКПОЗ — это универсальный инструмент для поиска по двум и более критериям. Она работает быстрее и гибче ВПР, особенно в больших таблицах. Подходит для любых матричных данных: продажи, тарифы, расписания и другие виды информационных таблиц.
Освоить все способы работы с формулами поиска и анализа данных в Excel можно на курсе «С Excel На ты». Мы показываем, как быстро находить любые данные в сложных таблицах.