Как да използвам функцията Excel XLOOKUP -

Обобщение

Функцията Excel XLOOKUP е модерен и гъвкав заместител на по-стари функции като VLOOKUP, HLOOKUP и LOOKUP. XLOOKUP поддържа приблизително и точно съвпадение, заместващи символи (*?) За частични съвпадения и търсене във вертикални или хоризонтални диапазони.

Предназначение

Справочни стойности в диапазон или масив

Върната стойност

Съответстваща стойност (и) от масива за връщане

Синтаксис

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Аргументи

  • lookup - Справочната стойност.
  • lookup_array - масивът или диапазонът за търсене.
  • return_array - масивът или диапазонът за връщане.
  • not_found - (по избор) Стойност за връщане, ако не е намерено съвпадение.
  • match_mode - (по избор) 0 = точно съвпадение (по подразбиране), -1 = точно съвпадение или следващо най-малко, 1 = точно съвпадение или следващо по-голямо, 2 = съвпадение на заместващ знак.
  • search_mode - (по избор) 1 = търсене от първо (по подразбиране), -1 = търсене от последно, 2 = двоично търсене възходящо, -2 = двоично търсене низходящо.

Версия

Excel 365

Бележки за употреба

XLOOKUP е модерен заместител на функцията VLOOKUP. Това е гъвкава и гъвкава функция, която може да се използва в най-различни ситуации.

XLOOKUP може да намира стойности във вертикални или хоризонтални диапазони, може да изпълнява приблизителни и точни съвпадения и поддържа заместващи символи (*?) За частични съвпадения. В допълнение, XLOOKUP може да търси данни, започвайки от първата стойност или последната стойност (вижте подробности за типа на съвпадението и режима на търсене по-долу). В сравнение с по-старите функции като VLOOKUP, HLOOKUP и LOOKUP, XLOOKUP предлага няколко ключови предимства.

Не е намерено съобщение

Когато XLOOKUP не може да намери съвпадение, връща грешка # N / A, подобно на други функции за съвпадение в Excel. За разлика от другите функции за съвпадение, XLOOKUP поддържа незадължителен аргумент, наречен not_found, който може да се използва за заменяне на грешката # N / A, когато в противен случай се появява. Типичните стойности за not_found може да са "Not found", "No match", "No result" и др. Когато предоставяте стойност за not_found, затворете текста в двойни кавички ("").

Забележка: Внимавайте, ако предоставите празен низ ("") за not_found. Ако не бъде намерено съвпадение, XLOOKUP няма да покаже нищо вместо # N / A. Ако искате да видите грешка # N / A, когато съвпадение не е намерено, пропуснете аргумента изцяло.

Тип на съвпадението

По подразбиране XLOOKUP ще извърши точно съвпадение. Поведението на съвпадението се контролира от незадължителен аргумент, наречен match_type, който има следните опции:

Тип на съвпадението Поведение
0 (по подразбиране) Точно съвпадение. Ще върне # N / A, ако няма съвпадение.
-1 Точно съвпадение или следващ по-малък елемент.
1 Точно съвпадение или следващ по-голям елемент.
2 Съвпадение на заместващ знак (*,?, ~)

Режим на търсене

По подразбиране XLOOKUP ще започне да съвпада от първата стойност на данните. Поведението при търсене се контролира от незадължителен аргумент, наречен search_mode , който предоставя следните опции:

Режим на търсене Поведение
1 (по подразбиране) Търсене от първа стойност
-1 Търсене от последната стойност (обратно)
2 Бинарни стойности за търсене, сортирани във възходящ ред
-2 Двоични стойности за търсене, сортирани в низходящ ред

Бинарните търсения са много бързи, но данните трябва да бъдат сортирани според изискванията. Ако данните не са сортирани правилно, бинарното търсене може да върне невалидни резултати, които изглеждат напълно нормални.

Пример # 1 - основно точно съвпадение

По подразбиране XLOOKUP ще извърши точно съвпадение. В примера по-долу XLOOKUP се използва за извличане на продажби въз основа на точно съвпадение на Movie. Формулата в H5 е:

=XLOOKUP(H4,B5:B9,E5:E9)

По-подробно обяснение тук.

Пример # 2 - основно приблизително съвпадение

За да активирате приблизително съвпадение, предоставете стойност за аргумента "match_mode". В примера по-долу XLOOKUP се използва за изчисляване на отстъпка въз основа на количество, което изисква приблизително съвпадение. Формулата във F5 предоставя -1 за match_mode, за да даде възможност за приблизително съвпадение с поведение "точно съвпадение или следващо най-малко":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

По-подробно обяснение тук.

Пример # 3 - множество стойности

XLOOKUP може да върне повече от една стойност едновременно за едно и също съвпадение. Примерът по-долу показва как XLOOKUP може да бъде конфигуриран да връща три съвпадащи стойности с една формула. Формулата в C5 е:

=XLOOKUP(B5,B8:B15,C8:E15)

Забележете, че масивът за връщане (C8: E15) включва 3 колони: First, Last, Department. И трите стойности се връщат и се разливат в диапазона C5: E5.

Пример # 4 - двупосочно търсене

XLOOKUP може да се използва за извършване на двупосочно търсене, чрез влагане на един XLOOKUP вътре в друг. В примера по-долу "вътрешният" XLOOKUP извлича цял ред (всички стойности за Glass), който се предава на "външния" XLOOKUP като масив за връщане. Външният XLOOKUP намира подходящата група (B) и връща съответната стойност (17.25) като краен резултат.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Повече подробности тук.

Пример # 5 - съобщението не е намерено

Подобно на другите функции за търсене, ако XLOOKUP не намери стойност, връща грешка # N / A. За да покажете персонализирано съобщение вместо # N / A, предоставете стойност за незадължителния аргумент "не е намерен", затворен в двойни кавички (""). Например, за да покажете „Не е намерено“, когато не е намерен съвпадащ филм, въз основа на работния лист по-долу, използвайте:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Можете да персонализирате това съобщение както искате: „Няма съвпадение“, „Филмът не е намерен“ и т.н.

Пример # 6 - сложни критерии

С възможност за естествена обработка на масиви, XLOOKUP може да се използва със сложни критерии. В примера по-долу XLOOKUP съвпада с първия запис, където: акаунтът започва с "x", а регионът е "изток", а месецът не е април:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Подробности: (1) прост пример, (2) по-сложен пример.

Предимства на XLOOKUP

XLOOKUP предлага няколко важни предимства, особено в сравнение с VLOOKUP:

  • XLOOKUP може да търси данни отдясно или отляво на справочните стойности
  • XLOOKUP може да върне множество резултати (пример № 3 по-горе)
  • По подразбиране XLOOKUP е точно съвпадение (VLOOKUP по подразбиране е приблизително)
  • XLOOKUP може да работи с вертикални и хоризонтални данни
  • XLOOKUP може да извърши обратно търсене (от последно към първо)
  • XLOOKUP може да върне цели редове или колони, а не само една стойност
  • XLOOKUP може да работи с масиви, за да прилага сложни критерии

Бележки

  1. XLOOKUP може да работи както с вертикални, така и с хоризонтални масиви.
  2. XLOOKUP ще върне # N / A, ако търсената стойност не бъде намерена.
  3. В масив_търсене трябва да има измерение съвместима с return_array аргумента, в противен случай ще се върне XLOOKUP #VALUE!
  4. Ако XLOOKUP се използва между работни книги, и двете работни книги трябва да са отворени, в противен случай XLOOKUP ще върне #REF !.
  5. Подобно на функцията INDEX, XLOOKUP връща референция като резултат.

Подобни видеа

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

Интересни статии...