Функция ПРОСМОТРX
Функция XLOOKUP используется для поиска элементов таблицы или диапазона по строкам. Например, найдите цену автомобильной части по номеру части или найдите имя сотрудника на основе идентификатора сотрудника. С помощью функции XLOOKUP можно искать в одном столбце условие поиска и возвращать результат из той же строки в другом столбце, независимо от того, на какой стороне находится возвращаемый столбец.
Примечание: Функция XLOOKUP недоступна в Excel 2016 и Excel 2019, однако вы можете столкнуться с ситуацией использования книги в Excel 2016 или Excel 2019 с функцией XLOOKUP, созданной другим пользователем, использующим более новую версию Excel.
Синтаксис
Функция XLOOKUP выполняет поиск диапазона или массива, а затем возвращает элемент, соответствующий первому обнаружению совпадения. Если совпадения не существует, функция XLOOKUP может вернуть ближайшее (приблизительное) совпадение.
=ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска])
|
Аргумент |
Описание |
|---|---|
|
искомое_значение Обязательный* |
Значение, которое требуется найти |
|
просматриваемый_массив Обязательно |
Массив или диапазон для поиска |
|
return_array Обязательный |
Возвращаемый массив или диапазон |
|
[if_not_found] Необязательный |
Если допустимое совпадение не найдено, возвращается указанный текст [if_not_found]. Если допустимое совпадение не найдено и [if_not_found] отсутствует, #N/A . |
|
[режим_сопоставления] Необязательно |
Укажите тип сопоставления: 0 — точное совпадение. Если ничего не найдено, #N/A. Этот параметр используется по умолчанию. -1 — точное совпадение. Если ничего не найдено, возвращается следующий элемент меньшего размера. 1 — точное совпадение. Если ничего не найдено, возвращается следующий более крупный элемент. 2 — совпадение с использованием особого значения подстановочных знаков: *, ?, ~. |
|
[режим_поиска] Необязательно |
Укажите режим поиска для использования: 1. Выполните поиск, начиная с первого элемента. Этот параметр используется по умолчанию. -1 — обратный поиск, начиная с последнего элемента. 2. Выполните двоичный поиск, который зависит lookup_array сортировки в порядке возрастания . Если сортировка не выполнена, будут возвращены недопустимые результаты. -2 — выполнение двоичного поиска на основе сортировки просматриваемого_массива по убыванию. Если сортировка не выполнена, будут возвращены недопустимые результаты. |
Примеры
В примере 1 функция XLOOKUP используется для поиска имени страны в диапазоне, а затем возвращает код страны телефона. Он включает lookup_value ( ячейка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Он не включает аргумент match_mode, так как функция XLOOKUP по умолчанию создает точное соответствие.
Примечание: Функция XLOOKUP использует массив подстановки и возвращаемый массив, в то время как функция ВПР использует один массив таблиц, за которым следует номер индекса столбца. Эквивалентная формула ВПР в этом случае будет: =ВПР(F2,B2:D11;3,FALSE)
———————————————————————————
В примере 2 выполняется поиск сведений о сотрудниках на основе идентификатора сотрудника. В отличие от функции ВПР, функция XLOOKUP может возвращать массив с несколькими элементами, поэтому одна формула может возвращать имя сотрудника и отдел из ячеек C5:D14.
———————————————————————————
Пример 3 добавляет if_not_found в предыдущий пример.
———————————————————————————
В примере 4 показано, как в столбце C найти персональный доход, введенный в ячейку E2, и найти соответствующую ставку налога в столбце B. Он задает if_not_found, возвращающий 0 (ноль ), если ничего не найдено. Аргумент match_mode имеет значение 1. Это означает, что функция будет искать точное совпадение, и если не удается найти его, она возвращает следующий более крупный элемент. Наконец, search_mode имеет значение 1, то есть функция будет выполнять поиск от первого элемента до последнего.
Примечание: Столбец lookup_array XARRAY находится справа от столбца return_array, тогда как функция ВПР может выглядеть только слева направо.
———————————————————————————
Пример 5 использует вложенную функцию XLOOKUP для выполнения вертикального и горизонтального сопоставления. Сначала он ищет валовую прибыль в столбце B, а затем ищет Qtr1 в верхней строке таблицы (диапазон C5:F5) и, наконец, возвращает значение на пересечении двух. Это похоже на совместное использование функций INDEX и MATCH .
Совет: Для замены функции HLOOKUP можно также использовать функцию XLOOKUP .
Примечание: Формула в ячейках D3:F3: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))).
———————————————————————————
Пример 6 использует функцию СУММ и две вложенные функции XLOOKUP для суммирования всех значений между двумя диапазонами. В этом случае мы хотим суммировать значения для грушей, которые находятся между ними.
Формула в ячейке E3: =СУММ(XLOOKUP(B3,B6:B10;E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Как это работает? Функция XLOOKUP возвращает диапазон, поэтому при вычислении формула выглядит следующим образом: =СУММ($E$7:$E$9). Чтобы увидеть, как это работает, выделите ячейку с формулой XLOOKUP, похожей на эту, а затем выберите формулы > Formula Auditing > Evaluate Formula и нажмите кнопку "Оценить", чтобы выполнить вычисление.
Примечание: Благодарим Microsoft Excel MVP, Bill Jelen, for suggesting this example.
———————————————————————————
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
