Как выполнить поиск с помощью функции ВПР и вернуть последнее совпадающее значение в Excel?
В Excel функция ВПР обычно используется для поиска и извлечения данных из таблицы. Однако, по умолчанию «ВПР» возвращает только первое найденное совпадающее значение. Что делать, если вам нужно вернуть последнее совпадающее значение? Для этого мы используем альтернативные формулы с такими функциями, как «ПОИСКПОЗ», «XLOOKUP», «ИНДЕКС», «ПОИСКПОЗ» или «Kutools». Мы также рассмотрим, как оптимизировать эти методы для повышения производительности и удобства использования.
Поиск с помощью ВПР и возврат последнего совпадающего значения в Excel
Поиск с помощью ВПР и возврат последнего совпадающего значения с использованием функции ПОИСКПОЗ
Функция «ПОИСКПОЗ» является мощным инструментом в Excel, который можно использовать для поиска последнего совпадающего значения в наборе данных.
Пожалуйста, введите следующую формулу в указанную ячейку, затем перетащите маркер заполнения вниз до ячеек, чтобы получить последнее соответствующее значение, как показано ниже:
=LOOKUP(2,1/($A$2:$A$12=E2),$C$2:$C$12)
- «A2:A12» — это диапазон, содержащий столбец для поиска;
- «E2» — это ячейка, содержащая значение для поиска;
- «C2:C12» — это диапазон, содержащий возвращаемые значения.
- «1/($A$2:$A$12=E2)» создает массив значений #ДЕЛ/0! и 1 там, где условие выполняется.
- «ПОИСКПОЗ(2,...)» сканирует массив в поисках последней единицы, эффективно находя последнее совпадение.
Поиск с помощью ВПР и возврат последнего совпадающего значения с Kutools для Excel
«Kutools для Excel» предоставляет простой и эффективный способ выполнения расширенных поисков, включая возврат последнего совпадающего значения из набора данных. Следуйте этим шагам, чтобы достичь этого без сложных формул.
После установки Kutools для Excel сделайте следующее:
1. Нажмите «Kutools» > «Супер ПОИСК» > «Поиск снизу вверх», см. скриншот:
2. В диалоговом окне «Поиск снизу вверх» выполните следующие действия:
- Выберите ячейки со значениями для поиска и выходными ячейками из разделов «Область размещения списка и Значения для поиска»;
- Затем укажите соответствующие элементы из раздела «Диапазон данных».
- Наконец, нажмите кнопку «OK».
Затем все последние совпадающие элементы будут возвращены одновременно, см. скриншот:
Если вы хотите заменить значение ошибки #Н/Д другим текстовым значением, просто нажмите кнопку «Опции» и отметьте опцию «Заменить не найденные значения (#N/A) указанным значением», затем введите нужный текст.
Поиск с помощью ВПР и возврат последнего совпадающего значения с использованием функций ИНДЕКС и ПОИСКПОЗ
Хотя традиционная функция ВПР не поддерживает эту функциональность, вы можете комбинировать мощные функции «ИНДЕКС» и «ПОИСКПОЗ», чтобы добиться этого. Этот метод динамичен, эффективен и работает во всех версиях Excel.
Пожалуйста, введите следующую формулу в указанную ячейку, нажмите «Ctrl» + «Shift» + «Enter» в Excel 2019 и более ранних версиях, нажмите «Enter» напрямую в Excel 365, Excel 2021 и более поздних версиях.
=INDEX($C$2:$C$12,MATCH(2,1/($A$2:$A$12=E2)))Затем перетащите маркер заполнения вниз до ячеек, чтобы получить последнее соответствующее значение, как показано ниже:
- «A2:A12» — это диапазон, содержащий столбец для поиска;
- «E2» — это ячейка, содержащая значение для поиска;
- «C2:C12» — это диапазон, содержащий возвращаемые значения.
- «1/($A$2:$A$12=E2)» создает массив значений #ДЕЛ/0! и 1 там, где условие выполняется.
- «ПОИСКПОЗ(2, 1/($A$2:$A$12=E2))» ищет число 2 в массиве, созданном выражением 1/($A$2:$A$12=E2). Поскольку 2 нет в массиве, ПОИСКПОЗ находит позицию последней допустимой единицы, которая соответствует последней совпадающей строке в $A$2:$A$12.
- «ИНДЕКС($C$2:$C$12,...)» использует номер строки из ПОИСКПОЗ для получения соответствующего значения из $C$2:$C$12.
Поиск с помощью ВПР и возврат последнего совпадающего значения с использованием функции XLOOKUP
Функция XLOOKUP, представленная в Excel 365, 2021 и более поздних версиях, является мощной и универсальной заменой для «ВПР» и «ГПР». Одной из ключевых особенностей является возможность выполнять поиск в обратном порядке, что делает ее идеальной для поиска последнего совпадающего значения в наборе данных.
Пожалуйста, введите следующую формулу в указанную ячейку, затем перетащите маркер заполнения вниз до ячеек, чтобы получить последнее соответствующее значение, как показано ниже:
=XLOOKUP(E2, $A$2:$A$12, $C$2:$C$12, , , -1)
- «E2»: Значение для поиска;
- «A2:A12»: Массив для поиска, то есть диапазон, где функция ищет значение для поиска;
- «C2:C12»: Массив для возврата, то есть диапазон, из которого возвращается соответствующее значение;
- ,,: Эти две запятые представляют собой необязательные аргументы для if_not_found и match_mode. В данном случае мы оставляем их пустыми.
- «-1»: Указывает режим поиска для начала поиска снизу диапазона.
Возвращение последнего совпадающего значения в Excel может быть достигнуто различными методами в зависимости от ваших потребностей и версии Excel, которую вы используете. Каждый метод имеет свои преимущества, и ваш выбор должен зависеть от версии Excel, которой вы располагаете. Овладев одним или несколькими из этих методов, вы значительно улучшите свои навыки управления данными и оптимизируете рабочий процесс в 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% и сократите сотни лишних кликов мышью каждый день!