Оглавление
×
Формулы и функции
Формулы и функции

ПРОСМОТРX

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

Примечание: Функция XLOOKUP недоступна в Excel 2016 и Excel 2019, однако вы можете столкнуться с ситуацией использования книги в Excel 2016 или Excel 2019 с функцией XLOOKUP, созданной другим пользователем, использующим более новую версию Excel.

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

Синтаксис

Функция XLOOKUP выполняет поиск диапазона или массива, а затем возвращает элемент, соответствующий первому обнаружению совпадения. Если совпадения не существует, функция XLOOKUP может вернуть ближайшее (приблизительное) совпадение. 

=ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска]) 

Аргумент

Описание

искомое_значение

Обязательный*

Значение, которое требуется найти

*Если этот параметр опущен, функция XLOOKUP возвращает пустые ячейки, которые находятся в lookup_array.   

просматриваемый_массив

Обязательно

Массив или диапазон для поиска

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, используемой для возврата имени сотрудника и отдела на основе идентификатора сотрудника. Формула = XLOOKUP(B2,B5:B14,C5:C14).

Примечание: Функция XLOOKUP использует массив подстановки и возвращаемый массив, в то время как функция ВПР использует один массив таблиц, за которым следует номер индекса столбца. Эквивалентная формула ВПР в этом случае будет: =ВПР(F2,B2:D11;3,FALSE)

———————————————————————————

В примере 2    выполняется поиск сведений о сотрудниках на основе идентификатора сотрудника. В отличие от функции ВПР, функция XLOOKUP может возвращать массив с несколькими элементами, поэтому одна формула может возвращать имя сотрудника и отдел из ячеек C5:D14.

Пример функции XLOOKUP, используемой для возврата имени сотрудника и отдела на основе идентификатора сотрудника. Формула: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

Пример 3    добавляет if_not_found в предыдущий пример.

Пример функции XLOOKUP, используемой для возврата имени сотрудника и отдела на основе идентификатора сотрудника с if_not_found аргумента. Формула = XLOOKUP(B2,B5:B14;C5:D14;0,1,"Сотрудник не найден")

———————————————————————————

В примере 4    показано, как в столбце C найти персональный доход, введенный в ячейку E2, и найти соответствующую ставку налога в столбце B. Он задает if_not_found, возвращающий 0 (ноль ), если ничего не найдено. Аргумент match_mode имеет значение 1. Это означает, что функция будет искать точное совпадение, и если не удается найти его, она возвращает следующий более крупный элемент. Наконец, search_mode имеет значение 1, то есть функция будет выполнять поиск от первого элемента до последнего.

Изображение функции XLOOKUP, используемой для возврата ставки налога на основе максимального дохода. Это приблизительное совпадение. Формула: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Примечание: Столбец lookup_array XARRAY находится справа от столбца return_array, тогда как функция ВПР может выглядеть только слева направо.

———————————————————————————

Пример 5    использует вложенную функцию XLOOKUP для выполнения вертикального и горизонтального сопоставления. Сначала он ищет валовую прибыль в столбце B, а затем ищет Qtr1 в верхней строке таблицы (диапазон C5:F5) и, наконец, возвращает значение на пересечении двух. Это похоже на совместное использование функций INDEX и MATCH .

Совет: Для замены функции HLOOKUP можно также использовать функцию XLOOKUP .

Изображение функции XLOOKUP, используемой для возврата горизонтальных данных из таблицы путем вложения 2 XLOOKUP. Формула: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

Примечание: Формула в ячейках D3:F3: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))).

———————————————————————————

Пример 6    использует функцию СУММ и две вложенные функции XLOOKUP для суммирования всех значений между двумя диапазонами. В этом случае мы хотим суммировать значения для грушей, которые находятся между ними.

Использование функции 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.

См. также

Функция ПОИСКПОЗX

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

Спасибо за ваш отзыв!

×