Как выполнить трёхмерный поиск в Excel?
В реальных бизнес-сценариях вы часто сталкиваетесь с таблицами, где данные структурированы по нескольким критериям — например, по типу товара, местоположению магазина и партии поставки. Представьте, что каждый товар (KTE, KTO, KTW) распределён по трём магазинам (A, B, C), и каждый магазин получил его двумя отдельными партиями. Если вам нужно найти количество товара KTO в магазине B из первой поставки — как показано на скриншоте ниже, — ручной поиск окажется не только медленным, но и чреват ошибками, особенно при работе с объёмными наборами данных. В этом руководстве представлены несколько эффективных способов выполнить трёхмерный поиск в Excel: с помощью формул, кода VBA и сводных таблиц — чтобы быстро получить нужное значение из заданного диапазона.
Решение с формулой массива Excel
Чтобы выполнить поиск по нескольким критериям в Excel, укажите все три критерия в ячейках G1, G2 и G3. Затем используйте приведённую ниже формулу массива для поиска совпадающих значений в таблице (см. изображение выше):
=INDEX($A$3:$D$11, MATCH(G1&,G2,$A$3:$A$11&,$B$3:$B$11,0), MATCH(G3,$A$2:$D$2,0))
Введите эту формулу в пустую ячейку, где вы хотите отобразить результат. После ввода формулы нажмите Shift + Ctrl + Enter, так как это формула массива — именно это действие позволяет Excel обрабатывать несколько критериев, объединённых по диапазонам. Формула находит строку, в которой одновременно выполняются критерий1 и критерий2, и извлекает значение из столбца по критерию3.
Распространённые проблемы возникают из-за неточных ссылок на ячейки или если вы забудете нажать Shift + Ctrl + Enter. При адаптации формулы под другие таблицы обязательно убедитесь, что диапазоны и ячейки с критериями указывают на правильные расположения.
Пояснение параметров формулы:
- $A$3:$D$11: Полный диапазон данных вашей таблицы.
- G1&G2: Объединённые значения первого и второго критериев (например, товар и магазин).
- $A$3:$A$11&$B$3:$B$11: диапазоны, в которых хранятся критерий 1 и критерий 2. Символ «&» позволяет сопоставлять комбинированные критерии построчно.
- G3, $A$2:$D$2: третий критерий (например, партия поставки) и диапазон, содержащий метки партий.
Формула не учитывает регистр и одинаково сопоставляет текст как в верхнем, так и в нижнем регистре. Если возникают ошибки при вводе данных, проверьте ячейки с критериями на наличие пробелов в начале или конце — они могут помешать корректному сопоставлению.
Совет: Если вам часто нужна формула типа «Поиск — Многокритериальный поиск», но её синтаксис кажется сложным, сохраните её в области Автотекст приложения Kutools для Excel. Это позволит мгновенно применять формулу в любой момент и на любом листе — достаточно просто щёлкнуть по ней и, при необходимости, скорректировать ссылки на ячейки. Такой подход избавит вас от необходимости запоминать формулу или снова и снова искать её синтаксис в интернете. |
Пример файла
Функция ВПР
В этом руководстве подробно разбираются синтаксис и аргументы функции ВПР, а также приводятся ключевые примеры, которые помогут вам легко разобраться в её работе.
ВПР с Раскрывающийся список
Функции ВПР и раскрывающийся список в Excel невероятно полезны. Но пробовали ли вы когда-нибудь использовать ВПР вместе с раскрывающимся списком?
ВПР и СУММ
Совместное использование функций ВПР и СУММ позволяет мгновенно находить данные по заданным критериям и одновременно суммировать соответствующие значения.
Использовать условное форматирование строки или ячейки, если два столбца совпадают в Excel
В этой статье описан способ применения условного форматирования к строкам или ячейкам при совпадении значений в двух столбцах Excel.
ВПР с возвратом значения по умолчанию
В Excel функция ВПР возвращает ошибку #Н/Д, если совпадение не найдено. Чтобы избежать этой ошибки, можно задать значение по умолчанию — оно будет автоматически подставляться вместо ошибки, когда совпадений нет.
Лучшие инструменты для повышения продуктивности в офисе
Kutools для Excel решает большинство ваших задач и повышает продуктивность на 80 %
- Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
- Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
- Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
- Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
- Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
- Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
- Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
- Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
- Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…
- Включите вкладки для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — это удобнее, чем использовать отдельные окна.
- Повышает продуктивность на 50 % и экономит сотни кликов мышью каждый день!
