Съдържание
×
Формули и функции
Формули и функции

XLOOKUP

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

Вашият браузър не поддържа видео.

Синтаксис

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

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

Пример 5    използва вложена функция XLOOKUP, за да извърши както вертикално, така и хоризонтално съвпадение. Първо търси брутна печалба в колона B, след което търси Qtr1 в горния ред на таблицата (диапазон 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 MVP, Bill Jelen, за предлагането на този пример.

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

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

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

Вж. също

XMATCH функция

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

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

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

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

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

Доколко сте доволни от качеството на превода?

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

Имате ли допълнителна обратна връзка? (по избор)

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

×