Как найти последнее вхождение искомого значения в Excel (оптимизированное руководство)
По умолчанию функция ВПР в Excel возвращает первое совпадающее значение при поиске. Однако если в вашем списке несколько совпадающих значений и вам нужно получить последнее вхождение, как этого добиться? В этой статье рассматриваются несколько оптимизированных методов поиска последнего совпадения искомого значения в списке — подходящих как для начинающих, так и для опытных пользователей Excel.
Как найти последнее вхождение искомого значения в Excel
Поиск последнего вхождения искомого значения с помощью функций ИНДЕКС и ПОИСКПОЗ
Этот метод совместим со всеми версиями Excel и использует комбинацию формул массива ИНДЕКС и ПОИСКПОЗ.
=INDEX($C$2:$C$13,MATCH(2,1/(B2:B13=E2)))
- B2:B13=E2: Проверяет, какие ячейки в диапазоне B2:B13 совпадают с продуктом из ячейки E2.
- 1/(B2:B13=E2)): Преобразует значение ИСТИНА в 1, а ЛОЖЬ — в ошибку #ДЕЛ/0! (ошибка деления).
- MATCH(2, 1/(B2:B13=E2)):
o Ищет число 2 в массиве.
o Так как значение 2 отсутствует, функция MATCH возвращает позицию последнего числового значения (1).
o В итоге получается номер строки последнего вхождения «KTW» в столбце B. - INDEX($C$2:$C$13, …): возвращает значение продаж из столбца C в той строке, которую нашла функция ПОИСКПОЗ.
Поиск последнего вхождения искомого значения с помощью Kutools для Excel
Если вы часто сталкиваетесь со сложными поисками, Kutools для Excel предлагает мощное решение «точка-и-щелчок», которое позволяет легко извлекать данные — без необходимости писать или запоминать громоздкие формулы.
Это особенно ценно для пользователей, регулярно работающих с большими объёмами данных, а также для тех, кто предпочитает наглядный и удобный интерфейс традиционным формульным методам. Независимо от того, новичок вы или опытный пользователь Excel, Kutools поможет оптимизировать ваш рабочий процесс и значительно повысить производительность.
После установки Kutools для Excel выполните следующие действия:
1. Нажмите Kutools > Супер ПОИСК > Поиск снизу вверх, чтобы активировать эту функцию. См. скриншот:
2. В диалоговом окне «Поиск снизу вверх» выполните следующую настройку.
- Выберите ячейки с искомым значением и ячейки для вывода результата в разделе Область поиска и вывода;
- Выберите весь Диапазон данных, значение Ключевой столбец, которое вы ищете, и Столбец для возврата в разделе Диапазон данных;
- Нажмите кнопку «ОК», чтобы мгновенно получить результаты. См. скриншот:
Поиск последнего вхождения искомого значения с помощью функции XПОИСК
В Excel 365 или Excel 2021 вы можете упростить процесс с помощью функции XПОИСК, которая изначально поддерживает поиск с конца.
Введите следующую формулу в пустую ячейку и нажмите клавишу Enter, чтобы получить последнее соответствующее значение, как показано ниже:
=XLOOKUP(E2, $B$2:$B$13, $C$2:$C$13,,,-1)
- E2 — это искомое значение.
- $B$2:$B$13 — диапазон, в котором выполняется поиск нужного значения.
- $C$2:$C$13 — диапазон, из которого будет возвращён результат.
- Последний аргумент –1 указывает Excel искать снизу вверх, что позволяет найти последнее совпадение.
📝 Заключение
Найти последнее вхождение искомого значения в Excel можно несколькими эффективными способами — всё зависит от версии Excel и ваших предпочтений.
- Метод с использованием функций ИНДЕКС и ПОИСКПОЗ надёжен, совместим со всеми версиями Excel и работает корректно — хотя и требует применения логики массивов.
- Функция XПОИСК, доступная в Excel 365 и Excel 2021, обеспечивает более чистое и интуитивно понятное решение с встроенной поддержкой обратного поиска.
- Для тех, кто предпочитает решать задачи без написания кода, Kutools для Excel предлагает удобный интерфейс, с помощью которого сложные поисковые операции выполняются всего за несколько щелчков мыши.
Независимо от выбранного метода, каждый из них поможет вам быстро получить актуальные соответствующие данные и повысить эффективность работы с Анализом данных. Если вы хотите узнать больше советов и приёмов по работе в Excel, на нашем сайте представлены тысячи обучающих материалов, которые помогут вам освоить Excel.
Связанные статьи
Как найти самую раннюю и самую позднюю даты в диапазоне в Excel
В столбце с датами бывает непросто быстро определить самую раннюю и самую позднюю даты, если сортировка недоступна. На самом деле существует несколько эффективных способов легко и удобно найти эти даты в Excel. Воспользуйтесь методами из этой статьи — и задача будет решена в считаные секунды!
Как определить квартал по дате в Excel
У вас есть список дат в столбце и нужно быстро определить, к какому кварталу относится каждая из них? В этой статье вы найдёте простые и эффективные приёмы, которые помогут легко справиться с этой задачей в Excel.
Сравнение двух списков на разных листах с помощью ВПР
Допустим, у вас есть два листа — «Name-1» и «Name-2» — содержащие списки имён, и вы хотите найти совпадающие имена из «Name-1», которые присутствуют в «Name-2». Сравнивать такие списки вручную между разными листами — крайне утомительно. В этой статье мы предлагаем быстрые приёмы, которые позволят решить эту задачу легко и без лишних усилий.
ВПР с суммированием совпадений по строкам или столбцам в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек