Выполнение двустороннего поиска в Excel: подробное руководство
Двусторонний поиск в Excel позволяет пользователям точно и эффективно извлекать нужные значения из больших наборов данных, одновременно определяя требуемую строку и указывая нужный столбец в таблице. В отличие от простого одномерного поиска, двусторонний поиск учитывает сразу два критерия: один — в заголовках строк, другой — в заголовках столбцов, обеспечивая точное и контекстуальное извлечение данных. Эта функция значительно повышает эффективность анализа данных, составления отчётов и принятия решений в Excel. Ниже приведены три эффективных метода реализации двустороннего поиска.
Возьмём, к примеру, следующий отчёт о продажах: мне нужно узнать объём продаж за июнь в Париже.
Выполнение двустороннего поиска в Excel
Способ 1: выполнение двустороннего поиска с помощью функций ИНДЕКС и ПОИСКПОЗ
Комбинация функций ИНДЕКС и ПОИСКПОЗ — это гибкое, надёжное и универсальное решение, совместимое со всеми версиями Excel, включая более старые. Функция ИНДЕКС возвращает значение из заданной позиции в таблице, а ПОИСКПОЗ определяет позицию искомого значения в указанном диапазоне.
Скопируйте или введите следующую формулу в пустую ячейку, чтобы получить результат, затем нажмите клавишу Enter:
=INDEX(B2:F13, MATCH(I1, A2:A13, 0), MATCH(I2, B1:F1, 0))
- MATCH(I1, A2:A13, 0) находит позицию строки «Июн» в столбце месяцев;
- MATCH(I2, B1:E1, 0) находит позицию столбца «Париж» в заголовках городов;
- INDEXвозвращает значение на пересечении 6-й строки и 2-го столбца в диапазоне B2:E13.
Преодолейте ограничения VLOOKUP с Kutools: Супер ПОИСК
Чтобы устранить ограничения функции ВПР (VLOOKUP) в Excel, Kutools разработал ряд расширенных функций поиска, предлагая пользователям более мощное и гибкое решение для работы с данными.
- 🔍Многолистовой поиск…: Ищите нужные данные сразу на нескольких листах.
- 📝Поиск — Многокритериальный поиск…Поиск - Многокритериальный поиск…: Ищите данные, соответствующие сразу нескольким критериям.
- ➕Поиск и суммирование…: Найдите данные по заданному значению и просуммируйте результаты.
- 📋Поиск один-ко-многим (возврат нескольких результатов…)Один-ко-многим поиску (возврат нескольких результатов…): Получайте сразу несколько соответствующих значений по одному исходному значению.
- Получите Kutools для Excel прямо сейчас!
Способ 2: Perform Two-Way Lookups with XLOOKUP (Excel 365, Excel 2021)
Функция XПОИСК значительно упрощает двусторонний поиск и особенно рекомендуется, если вы используете Excel 365 или Excel 2021 и новее. Она лаконична, интуитивно понятна и гораздо удобнее в использовании по сравнению с комбинацией ИНДЕКС/ПОИСКПОЗ.
Скопируйте или введите следующую формулу в пустую ячейку, чтобы получить результат, затем нажмите клавишу Enter:
=XLOOKUP(I1, A2:A13, XLOOKUP(I2, B1:F1, B2:F13))
- Внутренняя функция XПОИСК находит столбец «Париж» и возвращает массив Весь столбец;
- Внешняя функция XПОИСК находит «Июн» в столбце с месяцами и возвращает соответствующее значение из столбца, относящегося к Парижу.
Способ 3: выполнение двустороннего поиска с помощью функций ВПР и ПОИСКПОЗ
Комбинация ВПР и ПОИСКПОЗ — ещё один знакомый приём, особенно ценный для тех, кто привык работать с ВПР. Он широко используется, отличается эффективностью и совместим практически со всеми версиями Excel.
Скопируйте или введите следующую формулу в пустую ячейку, чтобы получить результат, затем нажмите клавишу Enter:
=VLOOKUP(I1, A1:F13, MATCH(I2, A1:F1, 0), FALSE)
- MATCH(I2, A1:F1, 0)определяет числовую позицию «Парижа» среди заголовков, указывая индекс соответствующего столбца для функции ВПР.
- VLOOKUP(I1, A1:F13, column_number, FALSE)определяет строку по месяцу «Июнь» и извлекает значение на пересечении с найденным столбцом.
Заключение:
Для пользователей современных версий Excel функция XПОИСК предлагает наиболее интуитивное решение. Комбинация ИНДЕКС/ПОИСКПОЗ остаётся самой универсальной с точки зрения совместимости между разными версиями. Метод ВПР/ПОИСКПОЗ тоже работает, но накладывает больше ограничений. Выберите тот способ, который лучше всего подходит под вашу версию Excel и решаемые задачи. Если вы хотите узнать ещё больше полезных советов и приёмов работы в Excel,на нашем сайте доступны тысячи обучающих материалов, которые помогут вам освоить Excel.
См. также:
Как выполнить ВПР между двумя датами и получить соответствующее значение в Excel?
Как с помощью ВПР вернуть несколько значений в одну ячейку Excel?
Как с помощью ВПР найти следующее большее значение в Excel?
Как выполнить ВПР из другой книги Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек