Функцията XLOOKUP в Excel – новата перла в короната

Здравейте,

създадох този блог, с идеята, че човек се чувства истински човек, когато помага на другите около себе си. Затова преди да започна представянето на XLOOKUP искам да помоля уважемия читател да помогне на една благородна кауза. Възпитаници на СУ „Емилиян Станев“ във Велико Търново събират средства за лечението на своята учителка Цветанка Пенчева. Повече подробности и данни за банковата сметка – тук

*  * * * *

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

Тук може да намерите официалното описание на XLOOKUP от Майкрософт на български и на английски език. В края на статията съм сложил линкове към статии и клипове на някои от Ексел експертите, които следя. Те не само описват синтаксиса на функцията, но и дават някои интересни идеи за нейното използване.

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

Простота – само три задължителни аргумента

XLOOKUP има само 3 задължителни параметра:

  • ключ за търсене
  • област, в която трябва да намерим ключа
  • област, в която са данните, които искаме да ни върне Ексел

И така става и много по-лесно за обяснение и възприемане. Защото ако имате една таблица с имената на някакви симпатяги и техните любими цветове и спортове, как в нея ще намерите кой е любимият цвят на Спиро?

Ами ще си отбележите, че търсите Спиро (ключ за търсене). След това ще отидете в колоната с имената (защото това е колоната, където е ключът за търсене), ще потърсите „Спиро“ и като го намерите ще плъзнете с показалеца до колоната с цветовете (където са данните, които търсим)., за да видите, че любимият цвят на Спиро е жълто-черният. И ако мислите, че такъв цвят няма, то мога да ви уверя, че в Пловдив не мислим така 😊.

Както виждате с XLOOKUP няма ляво, няма дясно. Няма TRUE/FALSE. Всичко е сведено до абсолютния минимум и в 95% от случаите наистина са необходими само тези три аргумента. По този начин формулите се пишат по-бързо и има по-малко възможности за грешки.

Търси и по редове

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

А така по-добре се вижда как формулата търси по редове.

Както споменах по-горе, XLOOKUP не прави нищо, което не можем да направим с някоя друга формула. Но забележете как точно можем да намерим сумата за съответния месец с VLOOKUP и с INDEX/MATCH. С новата формула е далеч по-просто и ясно, нали?

Връща референция, а не стойност

Тук се налага най-напред да обясня какво означава това. Представете си следната случка – вървим заедно с вас по улицата и аз виждам в далечината до автобусната спирка треньора на Италия Роберто Манчини да разговаря с нашия Ясен Петров (представяме си все пак). Мога да ви кажа – „Виж там до спирката е Роберто Манчини“ и така ще ви дам конкретна стойност, т.е. вие знаете кой точно се намира до спирката. Но мога да кажа и така: „Виж кой е до автобусната спирка“ и когато погледнете към нея, вие ще видите именно Роберто Манчини. В този случай съм ви дал референция. По същия начин в нашия пример по-горе XLOOKUP не ни върна стойността на приходите за месец април, т.е. числото 879, а върна адреса на клетката, която стои под месец Април в реда Приходи и когато погледнем какво има там, виждаме 879.

Това е изключително удобно, когато искаме да създадем динамична област, която се променя при промяна на някакъв параметър. Например често срещано във финансовите отчети е понятието „от началото на периода“, т.е. колко е стойността на показателя (приходи, разходи, печалба) от началото на годината (Year-to-date или YTD), от началото на месеца (Month-to-date или MTD) или дори от началото на седмицата (Week-to-date или WTD). При управлението на проекти имаме дори от самото начало на проекта – Life-to-date или LTD. Без значение какъв е периодът ние обикновено знаем началната точка – в случая януари, но крайната се променя при избора на съответния месец.

Ето как става това с XLOOKUP:

 

Директно задаваме „Стойност ако не намери“

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

Това е направено, за да реши един досаден проблем при ползването както на VLOOKUP, INDEX/MATCH – когато не намери търсената стойност, те връщат #N/A. Това първо изглежда грозно, второ може да попречи на други формули за сумиране, броене и т.н. Затова се налага да се добявя към VLOOKUP, INDEX/MATCH и условие с IF или IFERROR.

При XLOOKUP просто можем да посочим каква стойност да върне – дава възможност с nested xlookups да се заместят вложени IF-ове

Вече няма TRUE / FALSE

Тези, които работят с VLOOKUP знаят, колко важен е последният аргумент на тази функция. Всъщност те знаят, че той задължително трябва да бъде FALSE. Само в такъв случай формулата търси точно съвпадение на ключа за търсене и ако не го намери, ще ни върне грешка #N/A. По-неприятното е, че стойността по подразбиране е TRUE, при която VLOOKUP връща някаква стойност, дори и да не намери ключа.

С XLOOKUP стойността по подразбиране е за „точно съвпадение“ и ако не го намери връща #N/A. Параметърът за това е 0 (нула). Ако желаем можем да изберем  -1 и в такъв случай, ако не намери точната стойност формулата ще върне това, което съответства на следващия по-малък елемент. А можем да изберем и 1 и тогава, ако не намери ще ни върне следващия по-голям елемент

Търсене отдолу-нагоре

При VLOOKUP търсенето става „отгоре на долу“ и формулата връща първото съвпадение, което намери.

При XLOOKUP можем да изберем и търсене „отдолу на горе“, при което формулата ще върне последното съвпадение, което намери.

За целта се използва шестият аргумента на функцията – Search mode. Честно казано, аз не се сещам за практическо приложение на тази функционалност, но това се основава само и единствено на моята практика.

Заключение

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

Jon Acampora: XLOOKUP for Excel: Explained in 3 Minutes – YouTube

Mynda Treacy: Excel XLOOKUP Function Definitive Guide – YouTube

Trumpexcel: Excel XLOOKUP Function: All You Need to Know (10 Examples)

Chandoo: What is XLOOKUP? 13 formula examples to really understand it

ablebits: Excel XLOOKUP function with formula examples

Публикувано в Формули. Постоянна връзка.