XLOOKUP функция
Applies To
Excel за Microsoft 365 Excel за Microsoft 365 за Mac Excel за уеб Excel 2024 Excel 2024 за Mac Excel 2021 Excel 2021 за Mac Excel 2019 Excel 2016 Excel за iPad Excel за iPhone Excel за таблети с Android: Excel за телефони с Android:Използвайте функцията 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])
Аргумент |
Описание |
---|---|
справка_стойност Изисква* |
Стойността, която трябва да се търси *Ако липсва, XLOOKUP връща празни клетки, които намери в lookup_array. |
търсен_масив Задължително |
Масивът или обхватът за търсене |
return_array Задължителен |
Масивът или диапазонът за връщане |
Това е if_not_found. По желание |
Когато не е намерено валидно съвпадение, върнете текста [if_not_found], който предоставяте. Ако не бъде намерено валидно съвпадение и [if_not_found] липсва, се връща #N/A . |
[режим_на_съвпадане] По желание |
Задайте типа на съвпадението: 0 – Точно съвпадение. Ако не е намерен, върнете #N/A. Това е настройката по подразбиране. -1 - Точно съвпадение. Ако не е намерен, върнете следващия по-малък елемент. 1 – Точно съвпадение. Ако не е намерен, върнете следващия по-голям елемент. 2 – Съвпадение със заместващ символ, при което *, ?, и ~ имат специално значение. |
[режим_на_търсене] По желание |
Задайте режима на търсене, който да се използва: 1 – Извършете търсене, започвайки от първия елемент. Това е настройката по подразбиране. -1 - Извършване на обратно търсене, започвайки от последния елемент. 2 – Извършете двоично търсене, което разчита на lookup_array, които се сортират във възходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати. -2 – Изшършва двоично търсене, което разчита на „търсен_масив“, който се сортира във низходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати. |
Примери
Пример 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 или да получите поддръжка в Общността за отговори от.