Как выполнить поиск снизу вверх в Excel с помощью функции ВПР?
Функция ВПР в Excel по умолчанию ищет значения сверху вниз и возвращает первое совпадение. Однако иногда требуется выполнять поиск снизу вверх. Поскольку ВПР не поддерживает обратный поиск «из коробки», для этого приходится использовать альтернативные методы.
Поиск последнего совпадающего значения снизу вверх в Excel
- С помощью функции ПРОСМОТР
- С помощью Kutools для Excel
- С помощью функции XПОИСК (Excel 365/2021 или новее)
Поиск последнего совпадающего значения снизу вверх с помощью функции ПРОСМОТР
Чтобы найти совпадающее значение снизу вверх, воспользуйтесь следующей формулой с функцией ПРОСМОТР. Выполните следующие действия:
Введите приведённую ниже формулу в пустую ячейку, куда вы хотите поместить результат:
=LOOKUP(2,1/($A$2:$A$17=D2),$B$2:$B$17)Затем перетащите маркер заполнения вниз до тех ячеек, в которые вы хотите поместить результаты. Последние соответствующие значения появятся сразу — см. скриншот:
- $A$2:$A$17=D2: Эта часть проверяет, какие ячейки в диапазоне A2:A17 равны значению в ячейке D2. В результате возвращается массив из значений ИСТИНА (если условие выполнено) или ЛОЖЬ (если нет).
- 1/($A$2:$A$17=D2): Это преобразует массив значений ИСТИНА/ЛОЖЬ в единицы (для ИСТИНА) и ошибки #ДЕЛ/0! (для ЛОЖЬ), поскольку деление 1 на ИСТИНА даёт 1, а деление на ЛОЖЬ (эквивалентную нулю) приводит к ошибке.
- ПРОСМОТР(2, …): функция ПРОСМОТР ищет значение 2 в массиве, созданном на шаге 2. Поскольку 2 превышает все значения 1 в этом массиве, она сопоставляется с последней единицей — то есть с последним элементом, удовлетворяющим заданному условию.
- $B$2:$B$17 — это диапазон, из которого возвращается соответствующее значение. Функция возвращает значение из диапазона B2:B17, совпадающее с последней найденной единицей на шаге 3.
Поиск последнего совпадающего значения снизу вверх с помощью Kutools для Excel
К счастью, «Kutools для Excel» значительно упрощает эту задачу благодаря мощной функции «Поиск снизу вверх», которая мгновенно находит и возвращает последнее совпадающее значение в списке — без необходимости использовать сложные формулы.
После установки Kutools для Excelвыполните следующие действия:
1. Нажмите «Kutools» > «Супер ПОИСК» > «Поиск снизу вверх» (см. скриншот):
2. В диалоговом окне «Поиск снизу вверх» выполните следующие действия:
- Выберите ячейки со значениями для поиска и ячейки для вывода в разделах «Область размещения списка» и «Диапазон значений поиска»;
- Затем укажите нужные элементы в разделе «Диапазон данных».
- Наконец, нажмите кнопку «OK».
3. Все последние совпадающие значения будут возвращены сразу — см. скриншот:
Скачайте Kutools для Excel и начните бесплатный пробный период прямо сейчас!
Если вы хотите заменить ошибку #Н/Д другим текстом, просто нажмите кнопку «Параметры», установите флажок «Заменить результат вывода, который не найден, и вернуть „#N/A" указанным значением» и введите нужный текст.
Поиск последнего совпадающего значения снизу вверх с помощью функции XПОИСК
Функция XПОИСК — это современная альтернатива функциям ВПР и ГПР, которая упрощает поиск последнего совпадающего значения.
Введите следующую формулу в пустую ячейку, а затем перетащите маркер заполнения вниз до тех ячеек, чтобы получить последние соответствующие значения, как показано ниже:
=XLOOKUP(D2, $A$2:$A$17, $B$2:$B$17, , , -1)
- D2: значение, которое нужно найти.
- $A$2:$A$17 — диапазон поиска.
- $B$2:$B$17 — диапазон возвращаемых значений.
- -1: поиск от последнего значения к первому (снизу вверх).
Заключение
- Если вы используете Excel 365/2021 или более позднюю версию, функция XПОИСК — самый эффективный и простой способ найти последнее совпадающее значение.
- Для более старых версий Excel надёжной альтернативой служит функция ПРОСМОТР, хотя её применение требует немного более глубокого понимания работы с массивами.
- Если вы предпочитаете удобный способ без формул, отличным выбором станет Kutools для Excel — правда, для этого потребуется установить стороннюю надстройку.
Применяя эти методы, вы сможете эффективно выполнять поиск с помощью ВПР снизу вверх и находить последнее совпадающее значение в своих данных. Хотите освоить ещё больше полезных приёмов работы в Excel? На нашем сайте доступны тысячи обучающих материалов, которые помогут вам стать настоящим мастером Excel!
Другие связанные статьи:
- Поиск значений с помощью ВПР по нескольким листам
- В Excel функцию ВПР легко использовать для получения соответствующих значений в одной таблице на листе. Но задумывались ли вы, как выполнить поиск сразу по нескольким листам? Допустим, у вас есть три листа с диапазонами данных, и вы хотите получить нужные значения на основе критериев из этих трёх листов.
- Использование точного и приближённого соответствия в функции ВПР в Excel
- В Excel функция ВПР — одна из самых важных: она ищет значение в крайнем левом столбце таблицы и возвращает данные из той же строки указанного диапазона. Но насколько успешно вы применяете ВПР в своей работе? В этой статье я покажу, как эффективно использовать функцию ВПР в Excel.
- ВПР с возвратом пустой ячейки или определённого значения вместо 0 или #Н/Д
- При использовании функции ВПР, если ячейка совпадения пуста, обычно возвращается 0, а если искомое значение не найдено — ошибка #Н/Д, как показано на скриншоте ниже. Как настроить формулу так, чтобы вместо 0 или ошибки #Н/Д отображалась пустая ячейка или заданный вами текст?
- ВПР с возвратом всей строки / Вся строка найденного значения в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек