Перейти к содержимому

Найти последнее вхождение значения поиска в Excel (Оптимизированное руководство)

Author: Siluvia Last Modified: 2025-06-05

По умолчанию функция ВПР в Excel возвращает первое совпадающее значение при поиске. Однако, если ваш список содержит несколько совпадающих значений и вам нужно вернуть последнее вхождение, как это сделать? В этой статье рассматриваются несколько оптимизированных методов для нахождения последнего совпадения значения поиска в списке — подходящих как для начинающих, так и для продвинутых пользователей Excel.


Найти последнее вхождение значения поиска с помощью функций ИНДЕКС и ПОИСКПОЗ

Этот метод работает во всех версиях Excel и основан на комбинации формул массива ИНДЕКС и ПОИСКПОЗ.

=INDEX($C$2:$C$13,MATCH(2,1/(B2:B13=E2)))

Find the last occurrence of a Lookup value by INDEX and MATCH functions

Объяснение этой формулы:
  1. B2:B13=E2: Проверяет, какие ячейки в диапазоне B2:B13 соответствуют продукту в E2.
  2. 1/(B2:B13=E2): Преобразует ИСТИНА в 1, а ЛОЖЬ в #ДЕЛ/0! (ошибка деления).
  3. ПОИСКПОЗ(2, 1/(B2:B13=E2)):
    o Ищет число 2 в массиве.
    o Поскольку 2 не существует, ПОИСКПОЗ возвращает позицию последнего числового значения (1).
    o Это дает номер строки последнего «KTW» в столбце B.
  4. ИНДЕКС($C$2:$C$13, ...): Возвращает значение продаж из столбца C в строке, найденной с помощью ПОИСКПОЗ.

Найти последнее вхождение значения поиска с помощью Kutools для Excel

Если вы часто работаете с расширенными поисками, Kutools для Excel предлагает мощное решение с возможностью простого клика, позволяющее выполнять сложные задачи по извлечению данных без необходимости написания или запоминания сложных формул.

Это особенно ценно для пользователей, которые регулярно работают с большими наборами данных или предпочитают более наглядный и удобный интерфейс вместо традиционных методов, основанных на формулах. Независимо от того, являетесь ли вы новичком или опытным пользователем Excel, Kutools поможет оптимизировать ваш рабочий процесс и значительно повысить производительность.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

После установки Kutools для Excel выполните следующие шаги:

1. Щелкните Kutools > Супер ПОИСК > Поиск снизу вверх, чтобы активировать функцию. Смотрите скриншот:

click LOOKUP from Bottom to Top feature of kutools

2. В диалоговом окне Поиск снизу вверх настройте следующее.

  • Выберите ячейки со значениями поиска и выходные ячейки в разделах Значения поиска и Диапазон вывода;
  • Выберите весь диапазон данных, ключевой столбец, который вы ищете, и столбец возврата в разделе Диапазон данных;
  • Нажмите кнопку ОК, чтобы сразу получить результаты. Смотрите скриншот:

specify the options in the dialog box

Совет: Если вы хотите заменить значение ошибки #Н/Д другим текстовым значением, просто нажмите кнопку Опции и отметьте опцию Заменить не найденные значения (#Н/Д) указанным значением, затем введите нужный текст.

Найти последнее вхождение значения поиска с помощью функции XLOOKUP

В Excel 365 или Excel 2021 вы можете упростить процесс, используя XLOOKUP, который поддерживает обратный поиск.

Введите следующую формулу в пустую ячейку и нажмите клавишу Enter, чтобы получить последнее соответствующее значение:

=XLOOKUP(E2, $B$2:$B$13, $C$2:$C$13,,,-1)

Find the last occurrence of a Lookup value by xlookup function

Объяснение этой формулы:
  1. E2 — это значение, которое вы хотите найти.
  2. $B$2:$B$13 — это диапазон, где вы ищете значение.
  3. $C$2:$C$13 — это диапазон, из которого нужно вернуть результат.
  4. Последний аргумент -1 указывает Excel искать снизу вверх, поэтому он находит последнее совпадение.

📝 Заключение

Нахождение последнего вхождения значения поиска в Excel может быть достигнуто несколькими эффективными способами, в зависимости от версии Excel и личных предпочтений.

  • Метод ИНДЕКС и ПОИСКПОЗ надежен и работает во всех версиях, хотя требует логики массива.
  • Функция XLOOKUP, доступная в Excel 365 и 2021, обеспечивает более чистое и интуитивно понятное решение с встроенной поддержкой обратного поиска.
  • Для пользователей, предпочитающих подход без кода, Kutools для Excel предлагает удобный интерфейс, который обрабатывает расширенные поиски всего несколькими щелчками мыши.

Независимо от выбранного метода, каждый из них поможет быстро получить последние релевантные данные и повысить эффективность анализа данных. Если вас интересуют дополнительные советы и хитрости по работе с Excel, наш сайт предлагает тысячи учебных материалов, которые помогут вам освоить Excel.


Связанные статьи

Найти самую раннюю и самую позднюю даты в диапазоне в Excel
В столбце дат непросто быстро найти самую раннюю и самую позднюю даты, если вы не можете их отсортировать. На самом деле, есть несколько хитростей, чтобы легко и удобно найти самую раннюю и самую позднюю даты в Excel, вы можете следовать методам, описанным в этой статье.

Найти или получить квартал из заданной даты в Excel
Предположим, у вас есть список дат в столбце, и теперь вы хотите найти и получить кварталы из этих дат, как быстро это сделать в Excel? Эта статья представляет собой трюки для решения этой задачи.

Vlookup для сравнения двух списков в разных листах
Предположим, у вас есть два листа «Имя-1» и «Имя-2» со списком имен, и теперь вы хотите сравнить эти два списка и найти совпадающие имена в Именах-1, если они существуют в Именах-2. Ручное сравнение между двумя листами одно за другим — это больно. Эта статья предоставляет быстрые способы помочь вам справиться с этим без усилий.

Vlookup и сумма совпадений в строках или столбцах в Excel
Использование vlookup и функции суммы помогает быстро найти указанные критерии и одновременно суммировать соответствующие значения. В этой статье мы покажем вам два метода для vlookup и суммы первого или всех совпадающих значений в строках или столбцах в Excel.

Лучшие инструменты для повышения продуктивности работы с Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных на основе: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Расширенных функций Kutools
Популярные функции: Найти, выделить или отметить дубликаты | Удалить пустые строки | Объединить столбцы или ячейки без потери данных |   Округлить без формулы ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP с несколькими значениями | Многолистовой поиск | Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Избранные функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгами и листами | Библиотека автотекста | Выбор даты | Объединить данные | Шифрование/расшифровка ячеек | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр ячеек с жирным/курсивом/зачёркнутым...) ...
Топ-15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ практических формул (Расчет возраста на основе даты рождения, ...) |19 инструментов вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов для объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое

Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
  • Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!