Свързани теми
×
Формули и функции
Формули и функции

XLOOKUP

Използвайте функцията 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_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode]) 

Аргумент

Описание

справка_стойност

Задължително*

Стойността, която трябва да се търси

*Ако липсва, XLOOKUP връща празни клетки, които намери в lookup_array.   

търсен_масив

Задължително

Масивът или диапазонът за търсене

return_array

Задължително

Масивът или диапазонът за връщане

Това е if_not_found.

По желание

Когато не е намерено валидно съвпадение, върнете текста [if_not_found], който предоставяте.

Ако не бъде намерено валидно съвпадение и [if_not_found] липсва, се връща #N/A .

Това е match_mode.

По желание

Задайте типа на съвпадението:

0 – Точно съвпадение. Ако не е намерен, върнете #N/A. Това е настройката по подразбиране.

-1 - Точно съвпадение. Ако не е намерен, върнете следващия по-малък елемент.

1 – Точно съвпадение. Ако не е намерен, върнете следващия по-голям елемент.

2 - Заместващ символ, където *, ?, и ~ имат специално значение.

Това е search_mode.

По желание

Задайте режима на търсене, който да се използва:

1 – Извършете търсене, започвайки от първия елемент. Това е настройката по подразбиране.

-1 - Извършване на обратно търсене, започвайки от последния елемент.

2 – Извършете двоично търсене, което разчита на lookup_array, които се сортират във възходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати.

-2 - Извършете двоично търсене, което разчита на lookup_array се сортират в низходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати.

Примери

Пример 1    използва XLOOKUP, за да потърси име на страна в диапазон и след това да върне телефонния си код на страна. Включва аргументите lookup_value (клетка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Той не включва аргумента match_mode , тъй като XLOOKUP създава точно съвпадение по подразбиране.

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

Забележка: XLOOKUP използва справочен масив и върнат масив, докато VLOOKUP използва единичен масив на таблицата, последван от индекс на колона. Еквивалентната формула VLOOKUP в този случай би била: =VLOOKUP(F2;B2:D11;3;FALSE)

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

Пример 2    търси информация за служителите въз основа на ИД номер на служител. За разлика от VLOOKUP, 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 колона, докато VLOOKUP може да изглежда само от ляво надясно.

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

Пример 5    използва вложена XLOOKUP функция, за да изпълни както вертикално, така и хоризонтално съвпадение. Първо търси Брутна печалба в колона B, след което търси Тр1 в горния ред на таблицата (диапазон C5:F5) и накрая връща стойността в пресечната точка на двете. Това е подобно на използването на функциите INDEX и MATCH заедно.

Съвет: Можете също да използвате XLOOKUP, за да заместите функцията HLOOKUP .

Изображение на функцията XLOOKUP, използвана за връщане на хоризонтални данни от таблица чрез влагане на 2 XLOOKUPs. Формулата е: =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    използва функцията SUM и две вложени функции XLOOKUP, за да сумира всички стойности между два диапазона. В този случай искаме да сумираме стойностите за грозде, банани и да включим крушите, които са между двете.

Използване на XLOOKUP със SUM за сумиране на диапазон от стойности, които попадат между две селекции

Формулата в клетка E3 е: =SUM(XLOOKUP(B3;B6:B10;E6:E10):XLOOKUP(C3;B6:B10;E6:E10))

Как работи? XLOOKUP връща диапазон, така че когато се изчислява, формулата завършва така: =SUM($E$7:$E$9). Можете да видите как работи това самостоятелно, като изберете клетка с формула на XLOOKUP, подобна на тази, след което изберете Формули > Проверка на формули > Изчисляване на формула и след това изберете Изчисли, за да преминете през изчислението.

Забележка: Благодарение на MVP на Microsoft Excel Бил Желен за предлагането на този пример.

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

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на excel или да получите поддръжка в Общността за отговори от.

Получаване на живи и безплатни отговори в Excel

Вж. също

XMATCH функция

Функции на Excel (по азбучен ред)

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

Нуждаете се от още помощ?

Разширете уменията си

Преглед на обучението >

Получавайте първи новите функции

Присъединете се към Microsoft приобщени >

Беше ли полезна тази информация?

Доколко сте доволни от качеството на езика?
Какво е повлияло на вашия потребителски опит?

Благодарим ви за обратната връзка!

×