Функция Excel XLOOKUP: Всичко, което трябва да знаете (10 примера)

Гледайте видео - Функция Excel XLOOKUP (10 примера XLOOKUP)

Excel Функция XLOOKUP най -накрая пристигна.

Ако сте използвали VLOOKUP или INDEX/MATCH, сигурен съм, че ще харесате гъвкавостта, която предоставя функцията XLOOKUP.

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

Така че нека започнем!

Какво е XLOOKUP?

XLOOKUP е нова функция е Office 365 и е нова и подобрена версия на функцията VLOOKUP/HLOOKUP.

Той прави всичко, което VLOOKUP правеше преди, и много повече.

XLOOKUP е функция, която ви позволява бързо да търсите стойност в набор от данни (вертикална или хоризонтална) и да върнете съответната стойност в някой друг ред/колона.

Например, ако имате резултати за студенти в изпит, можете да използвате XLOOKUP, за да проверите бързо колко ученик е вкарал, използвайки името на студента.

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

Но преди да вляза в примерите, има голям въпрос - как да получа достъп до XLOOKUP?

Как да получите достъп до XLOOKUP?

Засега XLOOKUP е достъпен само за потребителите на Office 365.

Така че, ако използвате предишни версии на Excel (2010/2013/2016/2019), няма да можете да използвате тази функция.

Също така не съм сигурен дали това някога ще бъде пуснато за предишни версии или не (може би Microsoft може да създаде добавка по начина, по който го направиха за Power Query). Но засега можете да го използвате само ако сте в Office 365.

Щракнете тук, за да надстроите до Office 365

В случай, че вече сте в Office 365 (Начало, Лично или Университетско издание) и нямате достъп до него, можете да отидете в раздела Файл и след това да щракнете върху Акаунт.

Ще има програма Office Insider и можете да кликнете и да се присъедините към програмата Office Insider. Това ще ви даде достъп до функцията XLOOKUP.

Очаквам XLOOKUP да бъде наличен във всички версии на Office 365 скоро.

Забележка: XLOOKUP е наличен и за Office 365 за Mac и Excel за уеб (Excel онлайн)

Синтаксис на функцията XLOOKUP

По -долу е синтаксисът на функцията XLOOKUP:

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

Ако сте използвали VLOOKUP, ще забележите, че синтаксисът е доста подобен, с някои страхотни допълнителни функции, разбира се.

Не се притеснявайте, ако синтаксисът и аргументът изглеждат прекалено много. Покривам ги с някои лесни примери за XLOOKUP по -късно в този урок, които ще го направят кристално ясен.

Функцията XLOOKUP може да разказва 6 аргумента (3 задължителни и 3 по избор):

1. lookup_value - стойността, която търсите
2. lookup_array - масивът, в който търсите стойността за търсене
3. return_array - масивът, от който искате да извлечете и върнете стойността (съответстваща на позицията, където е намерена стойността за търсене)
4. [if_not_found] - стойността, която трябва да се върне в случай, че стойността за търсене не е намерена. В случай, че не посочите този аргумент, ще бъде върната грешка #N/A
5. [match_mode] - Тук можете да посочите желания тип съвпадение:
   • 0 - Точно съвпадение, където lookup_value трябва да съвпада точно със стойността в lookup_array. Това е опцията по подразбиране.
   • -1 - Търси точно съвпадение, но ако бъде намерено, връща следващия по -малък елемент/стойност
   • 1 - Търси точно съвпадение, но ако е намерено, връща следващия по -голям елемент/стойност
   • 2 - За да направите частично съвпадение, използвайки заместващи знаци (* или ~)
6. [режим на търсене] - Тук указвате как функцията XLOOKUP трябва да търси в lookup_array
   • 1 - Това е опцията по подразбиране, при която функцията започва да търси lookup_value от горе (първи елемент) до дъното (последен елемент) в lookup_array
   • -1 - Извършва ли търсенето отдолу нагоре. Полезно, когато искате да намерите последната съвпадаща стойност в lookup_array
   • 2 - Извършва двоично търсене, при което данните трябва да бъдат сортирани във възходящ ред. Ако не се сортира, това може да даде грешка или грешни резултати
   • -2 - Извършва двоично търсене, при което данните трябва да бъдат сортирани в низходящ ред. Ако не се сортира, това може да даде грешка или грешни резултати

Примери за функция XLOOKUP

Сега нека преминем към интересната част - някои практически примери за XLOOKUP.

Тези примери ще ви помогнат да разберете по -добре как работи XLOOKUP, как се различава от VLOOKUP и INDEX/MATCH и някои подобрения и ограничения на тази функция.

Щракнете тук, за да изтеглите примерния файл и продължете

Пример 1: Извличане на стойност за търсене

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

По -долу е формулата, която прави това:

= XLOOKUP (F2, A2: A15, B2: B15)

В горната формула току -що използвах задължителните аргументи, където търси името (отгоре надолу), намира точно съвпадение и връща съответната стойност от B2: B15.

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

Една незабавна полза от lookup_array и return_array като отделни аргументи означава, че сега можете погледни наляво. VLOOKUP имаше това ограничение, при което можете само да погледнете нагоре и да намерите стойност, която е вдясно. Но с XLOOKUP това ограничение изчезна.

Ето един пример. Имам същия набор от данни, където името е отдясно, а return_range е отляво.

По -долу е формулата, която мога да използвам, за да получа резултата за Грег по математика (което означава да гледам вляво от lookup_value)

= XLOOKUP (F2, D2: D15, A2: A15)

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

Пример 2: Търсене и извличане на цял запис

Нека вземем същите данни като пример.

В този случай не искам просто да извлека резултата на Грег по математика. Искам да получа резултатите по всички предмети.

В този случай мога да използвам следната формула:

= XLOOKUP (F2, A2: A15, B2: D15)

Горната формула използва диапазон return_array, който е повече от колона (B2: D15). Така че, когато стойността за търсене е намерена в A2: A15, формулата връща целия ред от return_array.

Също така не можете да изтриете само клетки, които са част от масива, които са били попълнени автоматично. В този пример не можете да изтриете H2 или I2. Ако се опитате, нищо няма да се случи. Ако изберете тези клетки, формулата в лентата с формули ще бъде затъмнена (което показва, че тя не може да бъде променена)

Можете да изтриете формулата в клетка G2 (където първоначално я въведохме), тя ще изтрие целия резултат.

Това е полезно подобрение, както по -рано с VLOOKUP, ще трябва да посочите номера на колоната отделно за всяка формула.

Пример 3: Двупосочно търсене с помощта на XLOOKUP (хоризонтално и вертикално търсене)

По -долу е набор от данни, където искам да знам резултата на Грег по математика (субектът в клетка G2).

Това може да стане с помощта на двупосочно търсене, където търся името в колона А и името на темата в ред 1. Ползата от това двупосочно търсене е, че резултатът е независим от името на ученика на името на предмета. Ако променя името на предмета на Химия, тази двупосочна формула XLOOKUP все още ще работи и ще ми даде правилния резултат.

По-долу е формулата, която ще извърши двупосочното търсене и ще даде правилния резултат:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Тази формула използва вложен XLOOKUP, където първо я използвам, за да извлека всички белези на ученика в клетка F2.

Така че резултатът от XLOOKUP (F2, A2: A15, B2: D15) е {21,94,81}, което е масив от оценки, отбелязани от Грег в този случай.

След това това се използва отново във външната формула XLOOKUP като масив за връщане. Във външната формула XLOOKUP търся името на обекта (което е в клетка G1) и масивът за търсене е B1: D1.

Ако името на обекта е Math, тази външна формула XLOOKUP извлича първата стойност от масив за връщане - което е {21,94,81} в този пример.

Това прави същото, което досега се постигаше с помощта на комбинацията INDEX и MATCH

Щракнете тук, за да изтеглите примерния файл и продължете

Пример 4: Когато стойността за търсене не е намерена (обработка на грешки)

Управлението на грешки вече е добавено към формулата XLOOKUP.

Четвъртият аргумент във функцията XLOOKUP е [if_not_found], където можете да посочите какво искате в случай, че търсенето не може да бъде намерено.

Да предположим, че имате набора от данни, както е показано по -долу, където искате да получите резултата по математика в случай, че съвпадението, и в случай, че името не е намерено, искате да се върнете - „Не се появи“

Формулата по -долу ще направи това:

= XLOOKUP (F2, A2: A15, B2: B15, "Не се появи")

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

Пример 5: Вложен XLOOKUP (Търсене в множество диапазони)

Гениалността на аргумента [if_not_found] е, че той ви позволява да използвате вложена формула XLOOKUP.

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

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

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

В горната формула използвах аргумента [if_not_found], за да използвам друга формула XLOOKUP. Това ви позволява да добавите втория XLOOKUP в същата формула и да сканирате две таблици с една формула.

Не съм сигурен колко вложени XLOOKUP можете да използвате във формула. Опитах до 10 и се получи, после се отказах 🙂

Пример 6: Намерете последната съвпадаща стойност

Това беше изключително необходимо и XLOOKUP направи това възможно. Сега не е нужно да намирате сложни начини да получите последната съвпадаща стойност в диапазон.

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

Формулата по -долу ще търси последната стойност за всеки отдел и ще даде името на последното наемане:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)

И формулата по -долу ще даде датата на наемане на последното наемане за всеки отдел:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)

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

Пример 7: Приблизително съвпадение с XLOOKUP (Намерете данъчна ставка)

Друго забележително подобрение с XLOOKUP е, че сега има четири режима на съвпадение (VLOOKUP има 2, а MATCH има 3).

Можете да посочите някой от четирите аргумента, за да решите как трябва да се съпостави стойността за търсене:

• 0 - Точно съвпадение, където lookup_value трябва да съвпада точно със стойността в lookup_array. Това е опцията по подразбиране.
• -1 - Търси точно съвпадение, но ако бъде намерено, връща следващия по -малък елемент/стойност
• 1 - Търси точно съвпадение, но ако е намерено, връща следващия по -голям елемент/стойност
• 2 - За да направите частично съвпадение, използвайки заместващи знаци (* или ~)

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

По -долу имам набор от данни, където искам да намеря комисионната на всеки човек - и комисионната трябва да бъде изчислена, като се използва таблицата вдясно.

По -долу е формулата, която ще направи това:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2

Това просто използва стойността на продажбите като търсене и разглежда през таблицата за търсене вдясно. В тази формула използвах -1 като пети аргумент ([match_mode]), което означава, че ще търси точно съвпадение, а когато не намери такова, ще върне стойността, малко по -малка от стойността за търсене .

И както казах, не е нужно да се притеснявате дали данните ви са сортирани по не.

Щракнете тук, за да изтеглите примерния файл и продължете

Пример 8: Хоризонтално търсене

XLOOKUP може да прави както вертикално, така и хоризонтално търсене.

По -долу имам набор от данни, в който има имена на ученици и техните резултати в редове и искам да извлека резултата за името в клетка В7.

Формулата по -долу ще направи това:

= XLOOKUP (B7, B1: O1, B2: O2)

Това не е нищо друго освен просто търсене (подобно на това, което видяхме в пример 1), но хоризонтално.

Всички примери, които обхващам за вертикално търсене, могат да бъдат направени и с хоризонтално търсене с помощта на XLOOKUP (сбогом с VLOOKUP и HLOOKUP).

Пример 9: Условно търсене (Използване на XLOOKUP с други формули)

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

По -долу е набор от данни, където имам имената на учениците и техните резултати и искам да знам името на ученика, който е вкарал максималния резултат по всеки предмет и броя на учениците, които са вкарали повече от 80 във всеки предмет.

По -долу е формулата, която ще даде името на ученика с най -високи оценки по всеки предмет:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)

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

Например за математика, когато използвам XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), тя ми дава всички резултати по математика. След това мога да използвам функцията MAX, за да намеря максималния резултат в този диапазон.

След това този максимален резултат става моя стойност за търсене, а диапазонът за търсене ще бъде масивът, върнат от XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Използвам това в друга формула XLOOKUP, за да извлека името на ученика, който е вкарал максималните оценки.

И за да преброите броя на учениците, които са вкарали повече от 80, използвайте формулата по -долу:

= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

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

Пример 10: Използване на Wildcard в XLOOKUP

Точно както можете да използвате заместващи знаци във VLOOKUP и MATCH, можете да направите това и с XLOOKUP.

Но има разлика.

В XLOOKUP трябва да посочите, че използвате заместващи знаци (в петия аргумент). Ако не посочите това, XLOOKUP ще ви даде грешка.

По -долу е набор от данни, в който има имена на компании и тяхната пазарна капитализация.

Искам да потърся име на компания в колона D и да извлека пазарната капитализация от таблицата вляво. И тъй като имената в колона D не съвпадат точно, ще трябва да използвам заместващи знаци.

По -долу е формулата, която ще направи това:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

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

Това казва на формулата да прегледа всички клетки и ако съдържа думата в клетка D2 (която е Apple), считайте я за точно съвпадение. Без значение колко и какви знаци са преди и след текста в клетка D2.

И за да сте сигурни, че XLOOKUP приема заместващи знаци, петият аргумент е зададен на 2 (съвпадение на заместващи знаци).

Пример 11: Намерете последната стойност в колоната

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

Да предположим, че имате набор от данни, както е показано по -долу и искате да знаете коя е последната компания и каква е пазарната капитализация на тази последна компания.

Формулата по -долу ще ви даде името на последната компания:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

И формулата по -долу ще даде пазарната капитализация на последната компания в списъка:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

Тези формули отново използват заместващи знаци. В тях съм използвал звездичка (*) като стойност за търсене, което означава, че това ще счита първата клетка, която среща, като точно съвпадение (като звездичка може да бъде всеки знак и произволен брой знаци).

И тъй като посоката е отдолу нагоре (за вертикално подредените данни), тя ще върне последната стойност в списъка.

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

Щракнете тук, за да изтеглите примерния файл и продължете

Ами ако нямате XLOOKUP?

Тъй като XLOOKUP вероятно ще бъде достъпен само за потребители на Office 365, един от начините да го получите е да надстроите до Office 365.

Ако вече имате Office 365 Home, Personal или University edition, вече имате достъп до XLOOKUP. Всичко, което трябва да направите, е да се присъедините към програмата Office Insider.

За да направите това, отидете в раздела Файл, щракнете върху Акаунт и след това върху опцията за вътрешна информация на Office. Ще има възможност да се присъедините към вътрешната програма.

В случай, че имате други абонаменти за Office 365 (като Enterprise), сигурен съм, че XLOOKUP и други страхотни функции (като динамични масиви, формули като SORT и FILTER) скоро ще станат достъпни.

В случай, че използвате Excel 2010/2013/2016/2019, няма да имате XLOOKUP и ще трябва да продължите да използвате комбинацията VLOOKUP, HLOOKUP и INDEX/MATCH, за да извлечете най -доброто от формулите за търсене.

Обратна съвместимост с XLOOKUP

Това е едно нещо, за което трябва да внимавате - XLOOKUP е НЕ обратно съвместим.

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

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

Това са 11 примера за XLOOKUP, които могат да ви помогнат да извършите по -бързо всички справки и справки, както и да го направите лесен за използване.

Надявам се, че сте намерили този урок за полезен!