Функция XLOOKUP

Използвайте функцията XLOOKUP , когато трябва да намерите неща в таблица или диапазон по ред. Например Вижте цените на автомобилната част по номера на частта или Намерете име на служител, базирано на неговия ИД на служител. С помощта на XLOOKUP можете да търсите в една колона за термин за търсене и да върнете резултат от същия ред в друга колона, независимо от коя страна е включена колоната за връщане.

Оказване на голямо въздействие с известия

Забележка: Тази функция в момента е достъпна за Microsoft 365 абонати в текущия канал. Той ще бъде достъпен за Microsoft 365 абонати в полу-годишен канал на Enterprise, започващ от 2020 юли. За повече информация за това как се пускат функциите за Microsoft 365 абонати, вижте кога получавам най-новите функции за Microsoft 365.

Функцията XLOOKUP търси диапазон или масив и връща елемент, съответстващ на първото съвпадение, което намери. Ако не съществува съвпадение, тогава XLOOKUP може да върне най-близкото (приблизително) съвпадение. 

= XLOOKUP (lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode]) 

Аргумент

Описание

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

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

Стойността за справка

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

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

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

return_array

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

Масивът или диапазонът, който трябва да се върне

[if_not_found]

По желание

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

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

[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: Б11) и return_array (диапазон D2: D11). Не включва аргумента match_mode, тъй като XLOOKUP по подразбиране за точно съвпадение.

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

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

Пример 2

В този пример търсим информация за служителя въз основа на ИД номер на служител. За разлика от VLOOKUP, XLOOKUP е в състояние да върне масив с множество елементи, което позволява една формула да върне име на служителя и отдела от клетки C5: Г14.

Пример за функцията XLOOKUP, използвана за връщане на име на служител и департамент на базата на служителите на IDt. Формулата е: = XLOOKUP (B2; B5: Б14; C5: Г14; 0; 1)

Пример 3

В този пример се добавя аргументът if_not_found към примера по-горе.

Пример за функцията XLOOKUP, използвана за връщане на име на служител и департамент на базата на ИД на служителя с аргумента if_not_found. Формулата е = XLOOKUP (B2; B5: Б14; C5: Г14; 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)

Забележка: За разлика от VLOOKUP, колоната lookup_array е отдясно на колоната 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, подобна на тази, след което преминете към формули > проверката на формули > изчисляване на формулаи натиснете бутона оценка , за да преминете през изчислението.

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

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

Винаги можете да попитате експерт в техническата общност на Excel, да получите поддръжка в общността за отговори или да предложите нова функция или подобрение на User Voice за Excel.

Вж. също

Функция XMATCH

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

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

Забележка:  Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас? Ето статията на английски за справка.

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

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

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

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×