XLOOKUP функция
Използвайте функцията XLOOKUP, за да намирате неща в таблица или диапазон по ред. Например потърсете цената на дадена автомобилна част по номера на частта или намерете име на служител въз основа на неговия ИД на служител. С XLOOKUP можете да търсите израз за търсене в една колона и да връщате резултат от същия ред в друга колона, независимо от коя страна е върнатата колона.
Забележка: XLOOKUP обаче не е наличен в Excel 2016 и Excel 2019, но е възможно да попаднете на ситуация да използвате работна книга в Excel 2016 или Excel 2019 с функцията XLOOKUP в нея, създадена от някой друг, който използва по-нова версия на Excel.
Синтаксис
Функцията XLOOKUP претърсва диапазон или масив и след това връща елемента, съответстващ на първото съвпадение, което намери. Ако няма съвпадение, XLOOKUP може да върне най-близкото (приблизително) съвпадение.
=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])
|
Аргумент |
Описание |
|---|---|
|
справка_стойност Изисква* |
Стойността, която трябва да се търси |
|
търсен_масив Задължително |
Масивът или диапазонът за търсене |
|
return_array Задължително |
Масивът или диапазонът за връщане |
|
Това е if_not_found. По желание |
Когато не е намерено валидно съвпадение, върнете текста [if_not_found], който предоставяте. Ако не бъде намерено валидно съвпадение и [if_not_found] липсва, се връща #N/A . |
|
Това е match_mode. По желание |
Задайте типа на съвпадението: 0 – Точно съвпадение. Ако не е намерен, върнете #N/A. Това е настройката по подразбиране. -1 - Точно съвпадение. Ако не е намерен, върнете следващия по-малък елемент. 1 – Точно съвпадение. Ако не е намерен, върнете следващия по-голям елемент. 2 - Заместващ символ, където *, ?, и ~ имат специално значение. |
|
Това е search_mode. По желание |
Задайте режима на търсене, който да се използва: 1 – Извършете търсене, започвайки от първия елемент. Това е настройката по подразбиране. -1 - Извършване на обратно търсене, започвайки от последния елемент. 2 – Извършете двоично търсене, което разчита на lookup_array, които се сортират във възходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати. -2 - Извършете двоично търсене, което разчита на lookup_array се сортират в низходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати. |
Примери
Пример 1 използва XLOOKUP, за да потърси име на страна в диапазон и след това да върне телефонния си код на страна. Включва аргументите lookup_value (клетка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Той не включва аргумента match_mode , тъй като XLOOKUP създава точно съвпадение по подразбиране.
Забележка: XLOOKUP използва справочен масив и върнат масив, докато VLOOKUP използва единичен масив на таблицата, последван от индекс на колона. Еквивалентната формула VLOOKUP в този случай би била: =VLOOKUP(F2;B2:D11;3;FALSE)
———————————————————————————
Пример 2 търси информация за служителите въз основа на ИД номер на служител. За разлика от VLOOKUP, XLOOKUP може да върне масив с множество елементи, така че една формула може да върне както име на служител, така и отдел от клетки C5:D14.
———————————————————————————
Пример 3 добавя аргумент if_not_found към предишния пример.
———————————————————————————
Пример 4 търси в колона C личния доход, въведен в клетка E2, и намира съответстваща данъчна ставка в колона B. Той задава аргумента if_not_found да връща 0 (нула), ако не се намери нищо. Аргументът match_mode е зададен на 1, което означава, че функцията ще търси точно съвпадение и ако не може да намери такъв, връща следващия по-голям елемент. И накрая, аргументът search_mode е зададен на 1, което означава, че функцията ще търси от първия елемент до последния.
Забележка: Колоната lookup_array на XARRAY е отдясно на return_array колона, докато VLOOKUP може да изглежда само от ляво надясно.
———————————————————————————
Пример 5 използва вложена XLOOKUP функция, за да изпълни както вертикално, така и хоризонтално съвпадение. Първо търси Брутна печалба в колона B, след което търси Тр1 в горния ред на таблицата (диапазон C5:F5) и накрая връща стойността в пресечната точка на двете. Това е подобно на използването на функциите INDEX и MATCH заедно.
Съвет: Можете също да използвате XLOOKUP, за да заместите функцията HLOOKUP .
Забележка: Формулата в клетки D3:F3 е: =XLOOKUP(D2;$B 6:$B 17;XLOOKUP($C 3;$C 5:$G 5;$C 6:$G 17)).
———————————————————————————
Пример 6 използва функцията SUM и две вложени функции XLOOKUP, за да сумира всички стойности между два диапазона. В този случай искаме да сумираме стойностите за грозде, банани и да включим крушите, които са между двете.
Формулата в клетка E3 е: =SUM(XLOOKUP(B3;B6:B10;E6:E10):XLOOKUP(C3;B6:B10;E6:E10))
Как работи? XLOOKUP връща диапазон, така че когато се изчислява, формулата завършва така: =SUM($E$7:$E$9). Можете да видите как работи това самостоятелно, като изберете клетка с формула на XLOOKUP, подобна на тази, след което изберете Формули > Проверка на формули > Изчисляване на формула и след това изберете Изчисли, за да преминете през изчислението.
Забележка: Благодарение на MVP на Microsoft Excel Бил Желен за предлагането на този пример.
———————————————————————————
Вж. също
Винаги можете да попитате експерт в техническата общност на Excel или да получите поддръжка в Общността за отговори от.
Нуждаете ли се от още помощ?
Искате ли още опции?
Разгледайте ползите от абонамента, прегледайте курсовете за обучение, научете как да защитите устройството си и още.
Общностите ви помагат да задавате и отговаряте на въпроси, да давате обратна връзка и да получавате информация от експерти с богати знания.
