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

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

Какво е XLOOKUP?

Новата функция XLOOKUP има решения за някои от най-големите ограничения на VLOOKUP. Освен това той също замества HLOOKUP. Например, XLOOKUP може да гледа отляво, по подразбиране да е точно съвпадение и ви позволява да посочите диапазон от клетки вместо номер на колона. VLOOKUP не е толкова лесен за използване или толкова гъвкав. Ще ви покажем как работи всичко.

За момента XLOOKUP е достъпен само за потребители в програмата Insiders. Всеки може да се присъедини към програмата Insiders за достъп до най-новите функции на Excel веднага щом станат налични. Microsoft скоро ще започне да го разпространява за всички потребители на Office 365.

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

Нека се потопим направо с пример за XLOOKUP в действие. Вземете примерните данни по-долу. Искаме да върнем отдела от колона F за всеки идентификатор в колона А.

Това е класически пример за търсене на точно съвпадение. Функцията XLOOKUP изисква само три части информация.

Изображението по-долу показва XLOOKUP с шест аргумента, но само първите три са необходими за точно съвпадение. Така че нека се съсредоточим върху тях:

  • Lookup_value:  Какво търсите.
  • Lookup_array:  Къде да търсите.
  • Return_array:  диапазонът, съдържащ стойността, която трябва да се върне.

Следната формула ще работи за този пример: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Нека сега разгледаме няколко предимства, които XLOOKUP има пред VLOOKUP тук.

Няма повече номер на индекс на колона

Скандалният трети аргумент на VLOOKUP беше да посочи номера на колоната на информацията, която да се връща от масив на таблица. Това вече не е проблем, защото XLOOKUP ви позволява да изберете диапазона, от който да се върнете (колона F в този пример).

И не забравяйте, XLOOKUP може да преглежда данните, останали от избраната клетка, за разлика от VLOOKUP. Повече за това по-долу.

Освен това вече нямате проблем със счупена формула при вмъкване на нови колони. Ако това се случи във вашата електронна таблица, обхватът на връщане ще се коригира автоматично.

Точното съвпадение е по подразбиране

Винаги беше объркващо, когато изучавахте VLOOKUP, защо трябваше да посочите точно съвпадение.

За щастие, XLOOKUP по подразбиране е точно съвпадение - далеч по-честата причина да се използва формула за търсене). Това намалява необходимостта от отговор на този пети аргумент и гарантира по-малко грешки от потребителите, нови за формулата.

Така накратко, XLOOKUP задава по-малко въпроси от VLOOKUP, по-лесен е за потребителя и е по-траен.

XLOOKUP може да гледа наляво

Възможността да изберете диапазон за търсене прави XLOOKUP по-гъвкав от VLOOKUP. При XLOOKUP редът на колоните на таблицата няма значение.

VLOOKUP беше ограничено чрез търсене в най-лявата колона на таблица и след това връщане от определен брой колони вдясно.

В примера по-долу трябва да търсим идентификатор (колона E) и да върнем името на човека (колона D).

Следната формула може да постигне това: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Какво да правя, ако не е намерено

Потребителите на функции за търсене са много запознати със съобщението за грешка # N / A, което ги поздравява, когато техният VLOOKUP или тяхната функция MATCH не могат да намерят това, от което се нуждае. И често има логична причина за това.

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

XLOOKUP идва със собствен вграден аргумент „ако не бъде намерен“, за да се справи с такива грешки. Нека го видим в действие с предишния пример, но с грешно въведен идентификатор.

Следващата формула ще покаже текста „Неправилен идентификатор“ вместо съобщението за грешка: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Използване на XLOOKUP за търсене на обхват

Въпреки че не е толкова често срещано като точното съвпадение, много ефективно използване на формула за търсене е да се търси стойност в диапазони. Вземете следния пример. Искаме да върнем отстъпката в зависимост от изразходваната сума.

Този път не търсим конкретна стойност. Трябва да знаем къде стойностите в колона Б попадат в диапазоните в колона Д. Това ще определи спечелената отстъпка.

XLOOKUP има незадължителен пети аргумент (не забравяйте, че по подразбиране е точното съвпадение), наречен режим на съвпадение.

Можете да видите, че XLOOKUP има по-големи възможности с приблизителни съвпадения от тези на VLOOKUP.

Има възможност да се намери най-близкото съвпадение, по-малко от (-1) или най-близкото по-голямо от (1) търсената стойност. Има и опция за използване на заместващи знаци (2) като? или *. Тази настройка не е включена по подразбиране, както беше при VLOOKUP.

Формулата в този пример връща най-близката по-малка от търсената стойност, ако не бъде намерено точно съвпадение: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Има обаче грешка в клетка C7, където се връща грешка # N / A (аргументът „ако не е намерен“ не е използван). Това би трябвало да върне 0% отстъпка, тъй като харченето на 64 не отговаря на критериите за някаква отстъпка.

Друго предимство на функцията XLOOKUP е, че не изисква обхватът на търсене да бъде във възходящ ред, както прави VLOOKUP.

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

Формулата веднага коригира грешката. Не е проблем да имате „0“ в долната част на диапазона.

Лично аз все още щях да сортирам таблицата по колоната за търсене. Наличието на „0“ отдолу ще ме побърка. Но фактът, че формулата не се счупи, е брилянтен.

XLOOKUP Заменя и функцията HLOOKUP

Както споменахме, функцията XLOOKUP също е тук, за да замести HLOOKUP. Една функция за замяна на две. Отлично!

Функцията HLOOKUP е хоризонталното търсене, използвано за търсене по редове.

Не е толкова известен като неговия брат или сестра VLOOKUP, но е полезен за примери като по-долу, където заглавията са в колона А, а данните са по редове 4 и 5.

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

В този пример формулата се използва за връщане на продажната стойност, свързана с името в клетка A2. Изглежда по реда 4, за да намери името, и връща стойността от ред 5:=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP може да гледа отдолу нагоре

Обикновено трябва да търсите списък, за да намерите първото (често единствено) появяване на стойност. XLOOKUP има шести аргумент, наречен режим на търсене. Това ни позволява да превключим търсенето да започне отдолу и да потърсим списък, за да намерим последното появяване на стойност вместо това.

В примера по-долу бихме искали да намерим нивото на запасите за всеки продукт в колона А.

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

Шестият аргумент на функцията XLOOKUP предоставя четири опции. Ние се интересуваме от използването на опцията „Търсене от първо към първо“.

Попълнената формула е показана тук: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

В тази формула четвъртият и петият аргумент бяха игнорирани. Той не е задължителен и искахме по подразбиране да има точно съвпадение.

Закръглям

Функцията XLOOKUP е с нетърпение очакван наследник на функциите VLOOKUP и HLOOKUP.

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

Поради скорошното въвеждане на динамични масиви в Excel, той може също да върне диапазон от стойности. Това определено е нещо, което си струва да се проучи допълнително.

Дните на VLOOKUP са номерирани. XLOOKUP е тук и скоро ще бъде де факто формулата за търсене.