Функцията VLOOKUP винаги е била една от най-мощните функции на Excel. Позволява ви да търсите стойности в първата колона на таблица и да връщате стойности от полета вдясно. Но Excel има и функция, наречена XLOOKUP, която ви позволява да търсите стойност във всяка колона или ред и да връщате данни от всяка друга колона.
Как работи XLOOKUP
Функцията XLOOKUP е много по-лесна за използване от функцията VLOOKUP, защото вместо да посочите стойност за колоната с резултати, можете да посочите целия диапазон.
Функцията също ви позволява да търсите както в колона, така и в ред, като локализирате стойността в пресичащата се клетка.
Параметрите на функцията XLOOKUP са както следва:
=XLOOKUP (търсена_стойност, търсене_масив, върнат_масив, [режим_на_съвпадение], [режим_търсене])
- lookup_value: Стойността, която искате да търсите
- lookup_array: Масивът (колона), който искате да търсите
- return_array: Резултатът (колона), от който искате да извлечете стойност
- match_mode (по избор): Изберете точно съвпадение (0), точно съвпадение или следваща най-малка стойност (-1) или съвпадение със заместващ знак (2).
- search_mode (по избор): Изберете дали да търсите, започвайки с първия елемент в колоната (1), последния елемент в колоната (-1), двоично търсене във възходящ ред (2) или двоично търсене в низходящ ред (-2).
По-долу са някои от най-често срещаните търсения, които можете да направите с функцията XLOOKUP.
Как да търсите един резултат с помощта на XLOOKUP
Най-лесният начин да използвате XLOOKUP е да търсите един резултат, използвайки точка от данни от една колона.
-
Тази примерна електронна таблица е списък с поръчки, изпратени от търговски представители, включително артикул, брой единици, цена и обща продажба.
Image -
Ако искате да намерите първата продажба в списъка, изпратен от конкретен търговски представител, можете да създадете функция XLOOKUP, която търси в колоната Rep за име. Функцията ще върне резултата от колоната Total. Функцията XLOOKUP за това е:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Сочи към Име на представител клетка за търсене
- C2:C44: Това е колоната Rep, която е масивът за търсене
- G2:G33: Това е общата колона, която е върнатият масив
- 0: Избира точно съвпадение
- 1: Избира първото съвпадение в резултатите
-
Когато натиснете Enter и въведете името на търговски представител, клетката Общ резултат ще ви покаже първия резултат в таблицата за този търговски представител.
Image -
Ако искате да търсите най-новата разпродажба (тъй като таблицата е подредена по дата в обратен ред), променете последния XLOOKUP аргумент на - 1, което ще започне търсенето от последната клетка в масива за търсене и вместо това ще ви предостави този резултат.
Image -
Този пример показва подобно търсене, което можете да извършите с функция VLOOKUP, като използвате колоната Rep като първа колона на справочната таблица. Въпреки това, XLOOKUP ви позволява да търсите всяка колона в двете посоки. Например, ако искате да намерите търговския представител, който е продал първата поръчка на Binder за годината, ще използвате следната функция XLOOKUP:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: Сочи към клетката за търсене на артикул
- D2:D44: Това е колоната "Елемент", която е масивът за търсене
- C2:C44: Това е колоната Rep, която е масивът за връщане вляво от масива за търсене
- 0: Избира точно съвпадение
- 1: Избира първото съвпадение в резултатите
-
Този път резултатът ще бъде името на търговския представител, продал първата поръчка за класьори за годината.
Image
Извършване на вертикално и хоризонтално съответствие с XLOOKUP
Друга възможност на XLOOKUP, на която VLOOKUP не може, е възможността за извършване както на вертикално, така и на хоризонтално търсене, което означава, че можете да търсите елемент надолу в колона, а също и в ред.
Тази функция за двойно търсене е ефективен заместител на други функции на Excel като INDEX, MATCH или HLOOKUP.
-
В следната примерна електронна таблица продажбите за всеки търговски представител са разделени по тримесечия. Ако искате да видите продажбите през третото тримесечие за конкретен търговски представител, без функцията XLOOKUP, този вид търсене би било трудно.
Image -
С функцията XLOOKUP този вид търсене е лесно. С помощта на следващата функция XLOOKUP можете да търсите продажби за третото тримесечие за конкретен търговски представител:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: Сочи към клетката за търсене на Rep
- B2:B42: Това е колоната "Елемент", която е масивът за търсене на колони
- K2: Сочи към клетката за търсене на квартал
- C1:H1: Това е масивът за търсене на редове
- C2:H42: Това е масивът за търсене на сумата в долари за всяко тримесечие
Тази вложена функция XLOOKUP първо идентифицира търговския представител, а следващата функция XLOOKUP идентифицира желаното тримесечие. Върнатата стойност ще бъде клетката, където тези две се пресичат.
-
Резултатът за тази формула е печалбата за първото тримесечие на представителя с името Томпсън.
Image
Използване на функцията XLOOKUP
Функцията XLOOKUP е достъпна само за абонати на Office Insider, но скоро ще бъде въведена за всички абонати на Microsoft 365.
Ако искате сами да изпробвате функцията, можете да станете Office Insider. Изберете Файл > Акаунт, след което изберете падащото меню Office Insider, за да се абонирате.
След като се присъедините към програмата Office Insider, вашата инсталирана версия на Excel ще получи всички най-нови актуализации и можете да започнете да използвате функцията XLOOKUP.
