Новият XLOOKUP на Excel ще замени VLOOKUP, осигурявайки мощен заместител на една от най-популярните функции на Excel. Тази нова функция решава някои от ограниченията на VLOOKUP и има допълнителна функционалност. Ето какво трябва да знаете.
Съдържание
Какво е XLOOKUP?
Новата функция XLOOKUP има решения за някои от най-големите ограничения на VLOOKUP. Освен това, той също замества HLOOKUP. Например, XLOOKUP може да гледа отляво, по подразбиране има точно съвпадение и ви позволява да посочите диапазон от клетки вместо номер на колона. VLOOKUP не е толкова лесен за използване или толкова универсален. Ще ви покажем как работи всичко.
За момента XLOOKUP е достъпен само за потребители на програмата Insiders. Всеки може присъединете се към програмата Insiders за достъп до най-новите функции на Excel веднага щом станат достъпни. Microsoft скоро ще започне да го въвежда за всички потребители на Office 365.
Как да използвате функцията XLOOKUP
Нека се потопим направо с пример за XLOOKUP в действие. Вземете примерните данни по-долу. Искаме да върнем отдела от колона F за всеки идентификатор в колона A.
Това е класически пример за търсене на точно съвпадение. Функцията 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 беше ограничен чрез търсене в най-лявата колона на таблица и след това връщане от определен брой колони вдясно.
В примера по-долу трябва да потърсим ID (колона 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,„Неправилен ID“)
Използване на XLOOKUP за търсене на диапазон
Въпреки че не е толкова често, колкото точното съвпадение, много ефективно използване на формула за търсене е да се търси стойност в диапазони. Вземете следния пример. Искаме да върнем отстъпката в зависимост от изразходваната сума.
Този път не търсим конкретна стойност. Трябва да знаем къде стойностите в колона B попадат в диапазоните в колона E. Това ще определи спечелената отстъпка.
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 е тук и скоро ще бъде де факто формулата за търсене.