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 .
[режим_на_съвпадане] По желание	Задайте типа на съвпадението: 0 – Точно съвпадение. Ако не е намерен, върнете #N/A. Това е настройката по подразбиране. -1 - Точно съвпадение. Ако не е намерен, върнете следващия по-малък елемент. 1 – Точно съвпадение. Ако не е намерен, върнете следващия по-голям елемент. 2 – Съвпадение със заместващ символ, при което *, ?, и ~ имат специално значение.
[режим_на_търсене] По желание	Задайте режима на търсене, който да се използва: 1 – Извършете търсене, започвайки от първия елемент. Това е настройката по подразбиране. -1 - Извършване на обратно търсене, започвайки от последния елемент. 2 – Извършете двоично търсене, което разчита на lookup_array, които се сортират във възходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати. -2 – Изшършва двоично търсене, което разчита на „търсен_масив“, който се сортира във низходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати.

Аргумент

Описание

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

Изисква*

Стойността, която трябва да се търси *Ако липсва, 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 използва справочен масив и върнат масив, докато 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 или да получите поддръжка в Общността за отговори от.

XMATCH функция

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

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

Синтаксис

Примери

Вж. също

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

Искате ли още опции?

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

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