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

Excel 2016 за абонати на Office 365 на Windows и Mac вече поддържа нова функция XLOOKUP, рекламирана като значително по-проста и по-гъвкава замяна на много популярната (все пак често злонамерена) функция за вертикално търсене, VLOOKUP (не знам какво означава X в XLOOKUP означава; обширен, може би?).

За тези от вас, които все още не са запознати с VLOOKUP (считана за третата най-използвана функция веднага след SUM и AVERAGE), тази функция търси вертикално по ред в най-лявата колона на определена таблица за търсене отгоре надолу, докато не намери стойност в колона за търсене, обозначена с номер на отместване, който съвпада или надвишава този, който търсите. Въпреки че е изключително полезна за намиране на определени елементи в дълъг списък или колона от таблица с данни във вашия работен лист, функцията VLOOKUP има няколко ограничения, които не се споделят от тази нова функция за търсене, като XLOOKUP:

• По подразбиране намира точни съвпадения за вашата стойност за търсене в диапазона за търсене
• Може да търси както вертикално (по ред), така и хоризонтално (по колона) в таблица, като по този начин замества необходимостта от използване на функцията HLOOKUP при хоризонтално търсене по колона
• Може да търси наляво или надясно, така че диапазонът за търсене във вашата таблица за търсене не трябва да се намира в колона вляво от тази, определена като диапазон за връщане, за да работи функцията
• Когато се използва точното съвпадение по подразбиране, работи дори когато стойностите в диапазона за търсене не са сортирани в определен ред
• Може да търси от долния ред до горния в диапазона на масива за търсене, като използва допълнителен аргумент за режим на търсене

Функцията XLOOKUP има пет възможни аргумента, първите три от които са задължителни, а последните два са незадължителни, използвайки следния синтаксис:

XLOOKUP ( lookup_value , масив_търсене , return_array [ match_mode ], [ search_mode ])

Задължителният аргумент lookup_value обозначава стойността или елемента, който търсите. Задължителният аргумент на масива look_up обозначава диапазона от клетки, които да бъдат търсени за тази стойност за търсене, а аргументът return_array обозначава диапазона от клетки, съдържащи стойността, която искате да бъде върната, когато Excel намери точно съвпадение.

* Имайте предвид, когато определяте аргументите lookup_array и return_array във вашата функция XLOOKUP, и двата диапазона трябва да са с еднаква дължина, в противен случай Excel ще върне #VALUE! грешка във вашата формула. Това е повече причината да използвате имена на диапазони или имена на колони на определена таблица с данни, когато дефинирате тези аргументи, вместо да ги посочвате или да въвеждате в техните препратки към клетките .

Незадължителният аргумент match_mode може да съдържа някоя от следните четири стойности:

• 0 за точно съвпадение (по подразбиране, същото като когато не е определен аргумент match_mode )
• -1 за точно съвпадение или следващата по-малка стойност
• 1 за точно съвпадение или следващата по-голяма стойност
• 2 за частично съвпадение с помощта на заместващи знаци, присъединени към препратка към клетка в аргумента lookup_value

Незадължителният аргумент search_mode може да съдържа някоя от следните четири стойности:

• 1 за търсене от първо до последно, тоест отгоре надолу (по подразбиране, същото като когато не е определен аргумент search_mode )
• -1 за търсене от последно до първо, тоест отдолу нагоре
• 2 за двоично търсене във възходящ ред
• -2 за двоично търсене в низходящ ред

Най-добрият начин да разберете силата и гъвкавостта на новата функция XLOOKUP е да я видите в действие в работен лист на Excel. На следващата фигура имам работен лист с проста таблица с данни за продажбите за 2019 г., подредена по държави. За да използвате XLOOKUP за връщане на общите продажби от тази таблица в клетка E4 въз основа на държавата, която въвеждате в клетка D4 на работния лист, предприемете следните стъпки:

Позиционирайте курсора на клетката в клетка E4 на работния лист

Щракнете върху опцията Търсене и справка в раздела Формули, последвано от XLOOKUP в долната част на падащото меню, за да отворите диалоговия прозорец Аргументи на функциите.

Щракнете върху клетка D4 в работния лист, за да въведете препратката към клетката в текстовото поле за аргумент Lookup_value.

Натиснете Tab, за да изберете текстовото поле за аргумент Lookup_array, след това щракнете върху клетка A4 и задръжте Shift, докато натискате Ctrl-стрелка надолу, за да изберете A4:A8 като диапазон за търсене (тъй като диапазонът A3:B8 е дефиниран като таблица с данни на Excel, Table1[Country] се появява в текстовото поле на мястото на диапазона A4:A8).

Натиснете Tab, за да изберете текстовото поле за аргумент Return_array, след това щракнете върху клетка B4 и задръжте Shift, докато натискате Ctrl-стрелка надолу, за да изберете B4:B8 като диапазон, съдържащ стойностите, които трябва да бъдат върнати въз основа на резултатите от търсенето (което изглежда като Таблица 1 [Общи продажби] в текстовото поле).

Щракнете върху OK, за да въведете формулата XLOOKUP в клетка E4.

Създаване на формула с XLOOKUP в клетка E4, която връща продажбите въз основа на държавата, въведена в клетка D4.

Excel въвежда формулата XLOOKUP в клетка E4 на работния лист и връща 4900 като резултат, тъй като Коста Рика в момента е въведена в справочната клетка D4 и както можете да видите в таблицата за продажби за 2019 г., това наистина са общите продажби, направени за тази страна.

Тъй като XLOOKUP работи както отдясно наляво, така и отляво надясно, можете да използвате тази функция също толкова добре, за да върнете страната от тази таблица за продажби въз основа на конкретна цифра на продажбите. Следващата фигура ви показва как да направите това. Този път създавате формулата XLOOKUP в клетка D4 и обозначавате стойността, въведена в клетка E4 (11 000, в този случай) като аргумент lookup_value.

В допълнение, вие въвеждате -1 като аргумент match_mode, за да замените зададената по подразбиране точно съвпадение на функцията, така че Excel да върне страната с точно съвпадение на стойността на продажбите, въведена в справочната клетка E4, или тази със следващата по-ниска обща стойност на продажбите (Мексико с $10 000 в този случай, тъй като в тази таблица няма държава с $11 000 общи продажби). Без да посочи аргумент match_mode за тази формула, Excel ще върне #NA като резултат, тъй като в тази таблица за продажби няма точно съвпадение с $11 000.

Създаване на формула с XLOOKUP в клетка D4, която връща страната въз основа на продажбите, въведени в клетка E4

Тъй като функцията XLOOKUP е еднакво удобна за търсене хоризонтално по колона, както и вертикално по ред, можете да я използвате, за да създадете формула, която извършва двупосочно търсене (замествайки необходимостта от създаване на формула, която комбинира функциите INDEX и MATCH като в миналото). Следващата фигура, съдържаща таблицата с производствения график за 2019 г. за номера на части, AB-100 до AB-103 за месеците април до декември, ви показва как се прави това.

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

В клетка B12 създадох следната формула:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

Тази формула започва с дефиниране на функция XLOOKUP, която вертикално търси по ред за точно съвпадение с записа на частта, направен в клетката с име part_lookup (клетка B10, в този случай) в диапазона от клетки $A$3:$A$6 на производствената таблица . Имайте предвид обаче, че аргументът return_array за тази оригинална функция LOOKUP е втора функция XLOOKUP.

Тази втора, вложена функция XLOOKUP търси в диапазона от клетки $B$2:$J$2 хоризонтално по колона за точно съвпадение с записа на датата, направен в клетката с име date_lookup (клетка B11, в този случай). Аргументът return_array за тази втора, вложена функция XLOOKUP е $B$3:$J$6, диапазонът от клетки на всички производствени стойности в таблицата.

Начинът, по който работи тази формула, е, че Excel първо изчислява резултата от втората, вложена функция XLOOKUP, като извършва хоризонтално търсене, което в този случай връща масива в диапазона от клетки D3: D6 на колоната Jun-19 (със стойностите: 438, 153, 306 и 779) като негов резултат. Този резултат от своя страна се превръща в аргумент return_array за оригиналната функция XLOOKUP, която извършва вертикално търсене по ред за точно съвпадение с записа на номер на част, направен в клетка B11 ​​(наречен part_lookup). Тъй като в този пример тази клетка part_lookup съдържа AB-102, формулата връща само производствената стойност за юни 19, 306, от резултата от втората, следващата функция XLOOKUP.

Ето го! Първи поглед към XLOOKUP, мощна, гъвкава и сравнително лесна за използване нова функция за търсене, която може не само да извършва търсене с една стойност, извършвано от функциите VLOOKUP и HLOOKUP, но също така и двупосочно търсене на стойност, извършвано чрез комбиниране на Функции INDEX и MATCH също.

* За съжаление, функцията XLOOKUP не е обратно съвместима с по-ранни версии на Microsoft Excel, които поддържат само функциите VLOOKUP и HLOOKUP или е съвместима с текущи версии, които все още не я включват като една от функциите си за търсене, като Excel 2019 и Excel Online . Това означава, че ако споделяте работна книга, съдържаща формули XLOOKUP, с колеги или клиенти, които използват версия на Excel, която не включва тази нова функция за търсене, всички тези формули ще върнат #NAME? стойности за грешки, когато отворят работния му лист.