Функция ПРОСМОТРX

Находите данные по строкам в таблице или диапазоне с помощью функции ПРОСМОТРX. Например, можно найти цену автомобильной детали по ее номеру или найти имя сотрудника по его идентификатору. С помощью функции ПРОСМОТРX вы можете искать в одном столбце искомый запрос и возвращать результат из той же строки в другом столбце, независимо от того, на какой стороне находится возвращаемый столбец.

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

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

Синтаксис

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

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

Аргумент	Описание
искомое_значение Обязательно*	Значение для поиска Если этот параметр опущен, функция ПРОСМОТРX возвращает пустые ячейки, которые он находит в lookup_array*.
просматриваемый_массив Обязательно	Массив или диапазон для поиска
return_array Обязательный	Возвращаемый массив или диапазон
[if_not_found] Необязательный	Если допустимое совпадение не найдено, верните текст [if_not_found], который вы указали. Если допустимое совпадение не найдено и [if_not_found] отсутствует, возвращается #N/A.
[режим_сопоставления] Необязательно	Укажите тип сопоставления: 0 — точное совпадение. Если ничего не найдено, верните #N/A. Этот параметр используется по умолчанию. -1 — точное совпадение. Если ни один элемент не найден, верните следующий элемент меньшего размера. 1 — точное совпадение. Если ни один элемент не найден, верните следующий более крупный элемент. 2 — совпадение с использованием особого значения подстановочных знаков: *, ?, ~.
[режим_поиска] Необязательно	Укажите используемый режим поиска: 1 — выполнение поиска, начиная с первого элемента. Этот параметр используется по умолчанию. -1 — выполнение обратного поиска, начиная с последнего элемента. 2 — выполнение двоичного поиска на основе сортировки просматриваемого_массива по возрастанию. Если сортировка не выполнена, будут возвращены недопустимые результаты. -2 — выполнение двоичного поиска на основе сортировки просматриваемого_массива по убыванию. Если сортировка не выполнена, будут возвращены недопустимые результаты.

Аргумент

Описание

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

Обязательно*

Значение для поиска *Если этот параметр опущен, функция ПРОСМОТРX возвращает пустые ячейки, которые он находит в lookup_array.

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

Обязательно

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

return_array

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

Возвращаемый массив или диапазон

[if_not_found]

Необязательный

Если допустимое совпадение не найдено, верните текст [if_not_found], который вы указали.

Если допустимое совпадение не найдено и [if_not_found] отсутствует, возвращается #N/A.

[режим_сопоставления]

Необязательно

Укажите тип сопоставления:

0 — точное совпадение. Если ничего не найдено, верните #N/A. Этот параметр используется по умолчанию.

-1 — точное совпадение. Если ни один элемент не найден, верните следующий элемент меньшего размера.

1 — точное совпадение. Если ни один элемент не найден, верните следующий более крупный элемент.

2 — совпадение с использованием особого значения подстановочных знаков: *, ?, ~.

[режим_поиска]

Необязательно

Укажите используемый режим поиска:

1 — выполнение поиска, начиная с первого элемента. Этот параметр используется по умолчанию.

-1 — выполнение обратного поиска, начиная с последнего элемента.

2 — выполнение двоичного поиска на основе сортировки просматриваемого_массива по возрастанию. Если сортировка не выполнена, будут возвращены недопустимые результаты.

-2 — выполнение двоичного поиска на основе сортировки просматриваемого_массива по убыванию. Если сортировка не выполнена, будут возвращены недопустимые результаты.

Примеры

В примере 1 используется функция ПРОСМОТРX для поиска названия страны в диапазоне, а затем возврата ее телефонного кода страны. Он включает аргументы lookup_value (ячейка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Он не включает аргумент match_mode, так как по умолчанию функция ПРОСМОТРХ создает точное совпадение.

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

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

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

В примере 2 выполняется поиск сведений о сотруднике на основе идентификатора сотрудника. В отличие от ВПР, функция ПРОСМОТРX может возвращать массив из нескольких элементов, поэтому одна формула может возвращать как имя сотрудника, так и отдел из ячеек 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)

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

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

Пример 5 использует вложенную функцию ПРОСМОТРX для выполнения вертикального и горизонтального сопоставления. Сначала выполняется поиск валовой прибыли в столбце B, затем выполняется поиск Qtr1 в верхней строке таблицы (диапазон C5:F5) и, наконец, возвращается значение на пересечении двух этих значений. Это аналогично совместному использованию функций ИНДЕКС и ПОИСКПОЗ.

Совет: Для замены функции ГПР можно также использовать функцию ПРОСМОТРX.

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

Примечание: Формула в ячейках D3:F3: =ПРОСМОТРX(D2,$B6:$B17,ПРОСМОТРX($C3,$C5:$G5,$C6:$G17)).

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

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

Использование XLOOKUP с СУММ для суммирования диапазона значений, которые попадают между двумя выбранными значениями

Формула в ячейке E3: =СУММ(ПРОСМОТРХ(B3,B6:B10,E6:E10):ПРОСМОТРХ(C3,B6:B10,E6:E10))

Как это работает? Функция ПРОСМОТРX возвращает диапазон, поэтому при вычислении формула выглядит следующим образом: =СУММ($E$7:$E$9). Вы можете увидеть, как это работает самостоятельно, выбрав ячейку с формулой ПРОСМОТРX, аналогичную этой, а затем выберите Формулы > Проверка формул > Вычислить формулу, а затем выберите Оценить, чтобы выполнить вычисление.

Примечание: Благодарим MVP Microsoft Excel Билла Джелен за предложение этого примера.

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

См. также

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

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

Синтаксис

Примеры

См. также

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

Нужны дополнительные параметры?

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

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