Excel 2020: Дванадесет предимства на XLOOKUP - Съвети за Excel

Новата функция XLOOKUP се въвежда в Office 365 от ноември 2019 г. Джо Макдейд от екипа на Excel е проектирал XLOOKUP, за да обедини хората, които използват VLOOKUP, и хората, които използват INDEX / MATCH. Този раздел ще обсъди 12-те предимства на XLOOKUP:

  1. Точното съвпадение е по подразбиране.
  2. Базираният на цяло число трети аргумент на VLOOKUP вече е подходяща препратка.
  3. IFNA е вградена за обработка на липсващи стойности.
  4. XLOOKUP няма проблем да отиде наляво.
  5. Намерете следващо по-малко или следващо по-голямо съвпадение, без да сортирате таблицата.
  6. XLOOKUP може да прави HLOOKUP.
  7. Намерете последното съвпадение, като търсите отдолу.
  8. По подразбиране заместващите символи са „изключени“, но можете да ги включите отново.
  9. Върнете всички 12 месеца в една формула.
  10. Може да върне препратка към клетка, ако XLOOKUP е до двоеточие като XLOOKUP (); XLOOKUP ()
  11. Може да направи двупосочен мач като INDEX (, MATCH, MATCH).
  12. Може да сумира всички справки в една формула, каквато може да бъде LOOKUP.

Ето синтаксиса: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

Полза XLOOKUP 1: Точно съвпадение по подразбиране

99% от моите VLOOKUP формули завършват на, FALSE или, 0, за да посочат точно съвпадение. Ако винаги използвате версията с точно съвпадение на VLOOKUP, можете да започнете да оставяте match_mode на вашата функция XLOOKUP.

На следващата фигура търсите W25-6 от клетка A4. Искате да потърсите този елемент в L8: L35. Когато бъде намерена, искате съответната цена от колона N. Не е необходимо да задавате False като match_mode, защото XLOOKUP по подразбиране е точно съвпадение.

XLOOKUP стойността в A4. Погледнете в L8: L35. Върнете съответната цена от N8: N35.

Предимство на XLOOKUP 2: масивът Results_Array е препратка вместо цяло число

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

Старият VLOOKUP ще се провали, ако някой вмъкне нова колона в справочната таблица. XLOOKUP продължава да работи.

Предимство на XLOOKUP 3: IFNA е вграден като незадължителен аргумент

Ужасяващата грешка # N / A се връща, когато вашата справочна стойност не е намерена в таблицата. В миналото, за да замените # N / A с нещо друго, ще трябва да използвате IFERROR или IFNA, увити около VLOOKUP.

Когато елемент не е намерен, той връща # N / A от VLOOKUP или XLOOKUP …

Благодарение на предложение от Рико в моя канал в YouTube, екипът на Excel включи незадължителен четвърти аргумент за if_not_found. Ако искате да замените тези # N / A грешки с нула, просто добавете 0 като четвърти аргумент. Или можете да използвате малко текст, например „Стойността не е намерена“.

Незадължителният четвърти аргумент в XLOOKUP е "ако не е намерен". Поставете 0 или „Не е намерено“ там.

XLOOKUP Полза 4: Няма проблем да гледате вляво от ключовото поле

VLOOKUP не може да гледа вляво от ключовото поле, без да прибягва до VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). При XLOOKUP няма проблем да има масив Results_ar вляво от Lookup_array.

При XLOOKUP няма проблем да върнете категорията от колона F, докато търсите номера на части в колона G. Това винаги е било слабостта на VLOOKUP: не може да гледа наляво.

XLOOKUP Предимство 5: Следващо по-малко или следващо по-голямо съвпадение без сортиране

VLOOKUP имаше опция да търси точното съвпадение или просто по-малка стойност. Можете или да оставите четвъртия аргумент извън VLOOKUP, или да промените False на True. За да работи това, таблицата за търсене трябваше да бъде сортирана във възходяща последователност.

Пример за версията за приблизително съвпадение на VLOOKUP. Всяка продажба от 10 хиляди до 20 хиляди получава бонус от $ 12.

Но VLOOKUP нямаше възможност да върне точното съвпадение или следващия по-голям елемент. За това трябваше да преминете към използване на MATCH с -1 като match_mode и трябваше да внимавате таблицата за търсене да бъде сортирана по низходящ ред.

Незадължителният пети аргумент на XLOOKUP match_mode може да търси само точното съвпадение, равно или просто по-малко, равно или просто по-голямо. Имайте предвид, че стойностите в XLOOKUP имат повече смисъл, отколкото в MATCH:

  • -1 намира стойността, равна на или просто по-малка
  • 0 намерете точно съвпадение
  • 1 намира стойността, равна на или просто по-голяма.

Но най-невероятната част: таблицата за търсене не трябва да се сортира и всеки match_mode ще работи.

По-долу match_mode на -1 намира следващия по-малък елемент.

Петият аргумент на XLOOKUP е Match_Mode. 0 е за точно съвпадение. Отрицателният се използва за Точно съвпадение или Следващ по-малък елемент. Положителна 1 е за точно съвпадение или следващ по-голям елемент. 2 е за Wildcard Match. За да отразявате това, което би направило VLOOKUP с True в четвъртия аргумент, поставете отрицателен като аргумент match_mode в XLOOKUP.

Тук, match_mode на 1, намира какво превозно средство е необходимо в зависимост от броя на хората в партията. Имайте предвид, че таблицата за търсене не е сортирана по пътници и името на превозното средство е вляво от клавиша.

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

Таблицата казва:

  • Автобусът побира 64 души
  • Колата побира 4 души
  • Мотоциклетът побира 1 човек
  • Тур Ван побира 12 души
  • Ван побират 6 души.

Като бонус данните се сортират по превозно средство (в старото решение, като се използва МАТЧ, таблицата ще трябва да се сортира в низходящ ред по капацитет. Също така: превозното средство е вляво от капацитета.

Предимство 6 на XLOOKUP: Настрани XLOOKUP замества HLOOKUP

Lookup_array и results_array могат да бъдат хоризонтални с XLOOKUP, което улеснява подмяната на HLOOKUP.

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

XLOOKUP Полза 7: Търсете отдолу за най-новото съвпадение

Имам старо видео в YouTube, отговарящо на въпрос от британска ферма за коне. Те разполагаха с автопарк. Всеки път, когато превозно средство влезе за гориво или услуга, те регистрираха превозно средство, дата и пробег в електронна таблица. Те искаха да намерят най-новия известен пробег за всяко превозно средство. Докато MAXIFS от ерата на Excel 2017 може да реши това днес, решението преди много години беше тайнствена формула, използваща LOOKUP и включваща деление на нула.

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

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

Забележка

Макар че това е голямо подобрение, то ви позволява да намерите само първия или последния мач. Някои хора се надяваха, че това ще ви позволи да намерите второто или третото съвпадение, но това не е намерението на аргумента search_mode.

Внимание

Фигурата по-горе показва, че има режими на търсене, използващи старото двоично търсене. Джо Макдейд не препоръчва използването им. Първо, подобреният алгоритъм за търсене от 2018 г. е достатъчно бърз, за ​​да няма значителна полза от скоростта. Второ, рискувате безсилен колега да сортира справочната таблица и да въведе грешни отговори.

Предимство на XLOOKUP 8: По подразбиране заместващите символи са „изключени“

Повечето хора не осъзнават, че VLOOKUP третира звездичка, въпросителен знак и тилда като заместващи символи, както е описано в "# 51 Използвайте заместващ знак в VLOOKUP" на страница 143. С XLOOKUP заместващите символи са изключени по подразбиране. Ако искате XLOOKUP да третира тези символи като заместващ символ, използвайте 2 като Match_Mode.

Много малко хора осъзнаха, че VLOOKUP третира звездичките в справочната стойност като заместващ знак. По подразбиране XLOOKUP не използва заместващи символи, но можете да го принудите да се държи като VLOOKUP, ако използвате режим на съвпадение 2: Съвпадение на заместващи символи.

Предимство на XLOOKUP 9: Върнете всички 12 месеца в една формула!

Това наистина е предимство на Dynamic Arrays, но това е любимата ми причина да обичам XLOOKUP. Когато трябва да върнете всички 12 месеца в справка, една формула, въведена в B6 с правоъгълен return_array, ще върне множество резултати. Тези резултати ще се разлеят в съседни клетки.

На фигурата по-долу една формула, въведена в B7, връща всички 12 отговора, показани в B7: M7.

Единичен XLOOKUP в колоната за януари връща числа за януари до декември. Това се прави, като се посочи масив от резултати с 12 колони.

Предимство на XLOOKUP 10: Може да върне референция за клетка, ако е в непосредствена близост до дебелото черво

Този е сложен, но красив. В миналото имаше седем функции, които биха се променили от връщане на стойност на клетка към връщане на препратка към клетка, ако функцията докосваше двоеточие. За пример вижте Използване на A2: INDEX () като Енергично OFFSET. XLOOKUP е функцията осмици, която предлага това поведение, като се присъединява към CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET и SWITCH.

Обмислете следната фигура. Някой избира Череша в Е4 и Фигура в Е5. Искате формула, която ще сумира всичко от B6 до B9.

Фигурата показва две формули XLOOKUP в две клетки. Първият връща 15 от клетка B6. Второто повторение 30 от B9. Но след това в трета клетка има формула, която обединява двете формули XLOOKUP с двоеточие и след това обгръща това във функция SUM. Резултатът е сумата на B6: B9, защото XLOOKUP може да върне препратка към клетка, ако функцията се появи до оператор като двоеточие. За да се докаже, че това работи, следващите няколко фигури ще покажат тази формула в диалоговия прозорец Evaluate Formula.

На фигурата по-горе можете да видите, че XLOOKUP от E4 ще върне 15 от клетка B6. XLOOKUP от E5 ще върне 30-те от B9. Ако обаче вземете двете функции XLOOKUP от клетки D9 и D10 и ги съберете с двоеточие между тях, поведението на XLOOKUP се променя. Вместо да върне 15, първият XLOOKUP връща адреса на клетката B6!

За да докажа това, избрах D7 и използвах формули, оценявам формула. След като натиснете Evaluate два пъти, следващата част, която трябва да се изчисли, е XLOOKUP ("Череша", A4: A29, B4: B29), както е показано тук.

Това показва диалоговия прозорец Evaluate Formula непосредствено преди оценката на първия XLOOKUP. Този XLOOKUP се появява точно преди дебелото черво.

Натиснете Evaluate отново и изумително, формулата XLOOKUP връща $ B $ 6 вместо 15-те, съхранени в B6. Това се случва, защото веднага след тази формула XLOOKUP има дебело черво.

Натиснете Evaluate и първият XLOOKUP връща $ B $ 6 вместо 15.

Натиснете Evaluate още два пъти и междинната формула ще бъде = SUM (B6: B9).

След оценка на втория XLOOKUP, междинната формула е = SUM (B6: B9).

Това е невероятно поведение, за което повечето хора не знаят. Excel MVP Чарлз Уилямс ми казва, че може да се задейства с всеки от тези три оператора до XLOOKUP:

  • Дебело черво
  • Пространство (оператор на пресичане)
  • Запетая (оператор на Съюза)

Предимство на XLOOKUP 11: Двупосочен мач като ИНДЕКС (, МАЧ, МАЧ)

За всички мои приятели от VLOOKUP хората от INDEX / MATCH чакаха да видят дали XLOOKUP може да се справи с двупосочен мач. Страхотната новина: може да го направи. Лошата новина: методологията е малко по-различна от очакванията на INDEX / MATCH феновете. Може да е малко над главите им. Но съм сигурен, че те могат да стигнат до този метод.

За двупосочно съвпадение искате да намерите кой ред съдържа номера на сметката A621, показан в J3. И така, XLOOKUP започва достатъчно лесно: = XLOOKUP (J3, A5: A15. Но след това трябва да предоставите масив results_array. Можете да използвате същия трик като в XLOOKUP Полза 9: Върнете всички 12 месеца в една формула по-горе, но използвайте го, за да върнете вертикален вектор. Вътрешен XLOOKUP търси месеца J4 в заглавията на месеца в B4: G4. Return_array е посочен като B5: G15. Резултатът е, че вътрешният XLOOKUP връща масив като този, показан в I10 : I20 по-долу. Тъй като A621 се намира в петата клетка на lookup_array, а 104 се намира в петата клетка на масива results_array, получавате правилния отговор от формулата. По-долу J6 показва стария начин. J7 връща новия начин.

XLookup J3 в списъка с акаунти в A5: A15. За масива с резултати използвайте XLOOKUP (J4, B4: G4, B5: G15). В тази формула B4: G4 е списък с месеци. B5: G15 е правоъгълният масив от стойности за всички сметки за всички месеци. В друга клетка само вътрешният XLOOKUP показва как връща цялата колона със стойности за май.

Предимство на XLOOKUP 12: Сумирайте всички справочни стойности в една формула

Древната функция LOOKUP предлагаше два странни трика. Първо, ако се опитвате да разберете общата сума на натрупаните бонус разходи, можете да поискате LOOKUP да търси всички стойности в една формула. На изображението по-долу LOOKUP (C4: C14 прави 11 справки. Но функцията LOOKUP не предлага точно съвпадение и изисква таблицата за търсене да бъде сортирана.

Търсете 13 стойности и ги сумирайте. Преди това работеше с LOOKUP, но работи и с XLOOKUP. Посочете всички справочни стойности C4: C14 като първи аргумент. Увийте XLOOKUP във функция SUM.

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

Трикът с използването на LOOKUP за сумиране на всички резултати от търсенето е работил само с приблизителната версия на Lookup. Тук XLOOKUP прави точно съвпадение на всички имена в L4: L14 и получава общо всички резултати.

Бонус съвет: Какво ще кажете за Twisted LOOKUP?

Excel MVP Mike Girvin често показва трик на функцията LOOKUP, където Lookup_Vector е вертикален, а Result_Vector е хоризонтален. XLOOKUP няма да поддържа този трик. Но ако измамите малко и увиете масива results_array във функцията TRANSPOSE, можете да управлявате усукано търсене.

Тук масивът за търсене е вертикален, а масивът с резултати е хоризонтален. Старата функция LOOKUP може да се справи с това ,, но за да го направите с XLOOKUP, трябва да увиете единия масив в TRANSPOSE.

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