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