Функцията XLOOKUP е изключителна за вътрешната програма на офис 365. Функцията LOOKUP има много функционалности, които преодоляват много от слабостите на функцията VLOOKUP и HLOOKUP, но за съжаление засега не са достъпни за нас. Но не се притеснявайте, можем да създадем функция XLOOKUP, която работи абсолютно същата като предстоящата функция XLOOKUP MS Excel. Ще добавим функционалности към него един по един.
VBA код на функцията XLOOKUP
По -долу функцията за търсене на UDF ще реши много проблеми. Копирайте го или изтеглете xl добавката по-долу файла по-долу.
Функция XLOOKUP (lk като вариант, lCol като диапазон, rCol като диапазон) XLOOKUP = Работен лист Функция.Индекс (rCol, WorksheetFunction.Match (lk, lCol, 0)) Крайна функция
Обяснение:
Горният код е просто основен INDEX-MATCH, използван във VBA. Това опростява много неща, пред които е изправен нов потребител. Ако разреши сложността на функцията INDEX-MATCH и използва само три аргумента. Можете да го копирате във вашия Excel файл или да изтеглите .xlam файла по-долу и да го инсталирате като добавка в Excel. Ако не знаете как да създадете и използвате добавка, щракнете тук, това ще ви помогне.
Добавка XLOOKUP
нека да видим как работи на работен лист на excel.
Синтаксис на XLOOKUP
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: Това е стойността, която искате да търсите в lookup_array.
lookup_array: Това е едноизмерен диапазон, в който искате да търсите lookup_value.
резултат_масив: Това също е едноизмерен диапазон. Това е диапазонът, от който искате да извлечете стойността.
Нека видим тази функция XLOOKUP в действие.
Примери за XLOOKUP:
Тук имам таблица с данни в excel. Нека разгледаме някои функционалности, използвайки тази таблица с данни.
Функционалност 1. Точно Търсене от лявата и дясната страна на стойността за търсене.
Както знаем, че функцията Excel VLOOKUP не може да извлича стойности отляво на стойността за търсене. За това трябва да използвате сложната комбинация INDEX-MATCH. Но вече не.
Ако приемем, че трябва да извлечем цялата налична информация в таблицата на някои номера на ролки. В този случай ще трябва да извлечете и региона, който е вляво от колоната с номера на ролката.
Напишете тази формула, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Получаваме резултата Север за ролка номер 112. Копирайте или плъзнете надолу формулата в клетките по -долу, за да ги запълните със съответните региони.
Как работи?
Механизмът е прост. Тази функция търси lookup_value в lookup_array и връща индекса на първо точно съвпадение. След това използва този индекс, за да извлече стойността от резултат_масив. Тази функция работи перфектно с именувани диапазони.
По същия начин използвайте тази формула, за да извлечете стойността от всяка колона.
Функционалност 2. Точна Хоризонтално Търсене над и под стойността за търсене.
XLOOKUP работи и като точна функция HLOOKUP. Функцията HLOOKUP има същото ограничение като VLOOKUP. Той не може да извлече стойност отгоре на стойността за търсене. Но XLOOKUP не само работи като HLOOKUP, но и преодолява тази слабост. Да видим как.
Хипотетично, ако искате да сравните два записа. Записът за търсене, който вече имате. Записът, с който искате да сравните, е над lookup_range. В този случай използвайте тази формула.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
плъзнете надолу формулата и имате целия запис за сравняване на ред.
Функционалност 3. Няма нужда от номер на колона и точно съвпадение по подразбиране.
Когато използвате функцията VLOOKUP, трябва да кажете номера на колоната, от която искате да извлечете стойностите. За това трябва да преброите колоните или да използвате някои трикове, да използвате помощта на други функции. С този UDF XLOOKUP не е нужно да правите това.
Ако използвате VLOOKUP само за извличане на някаква стойност от една колона или за да проверите дали има стойност в колоната, това е най -доброто решение според мен.
Функционалност 4. Заменя функцията INDEX-MATCH, VLOOKUP, HLOOKUP
За прости задачи нашата функция XLOOKUP заменя гореспоменатите функции.
Ограничения на XLOOKUP:
Що се отнася до сложни формули, като VLOOKUP с Dynamic Col Index, където VLOOKUP идентифицира колоната за търсене с заглавки, този XLOOKUP ще се провали.
Друго ограничение е, че ако трябва да потърсите няколко произволни колони или редове от таблицата, тази функция ще бъде безполезна, тъй като трябва да пишете тази формула отново и отново. Това може да бъде преодоляно с помощта на именувани диапазони.
Засега не сме добавили приблизителната функционалност, така че, разбира се, не можете да получите приблизителното съвпадение. Ще го добавим твърде скоро.
Ако функцията XLOOKUP не успее да намери стойността за търсене, тя ще върне грешка #VALUE, а не #N/A.
Така че да, момчета, по този начин използвате XLOOKUP за извличане, търсене и валидиране на стойности в таблици на Excel. Можете да използвате тази дефинирана от потребителя функция за безпроблемно търсене вляво или нагоре от стойността за търсене. Ако все още имате някакви съмнения или някакво конкретно изискване, свързано с тази функция или EXCEL 2010/2013/2016/2019/365 или заявка, свързана с VBA, попитайте го в секцията за коментари по -долу. Със сигурност ще получите отговор.
Създайте VBA функция за връщане на масив | За да върнем масив от дефинирана от потребителя функция, трябва да го декларираме, когато назоваваме UDF.
Масиви в Excel Formul | Научете какви масиви има в Excel.
Как да създадете потребителска функция чрез VBA | Научете как да създавате потребителски функции в Excel
Използване на функция, дефинирана от потребителя (UDF) от друга работна книга, използваща VBA в Microsoft Excel | Използвайте дефинираната от потребителя функция в друга работна книга на Excel
Връща стойности на грешки от дефинирани от потребителя функции, използвайки VBA в Microsoft Excel | Научете как можете да върнете стойности за грешка от дефинирана от потребителя функция
Популярни статии:
Разделете Excel листа на множество файлове въз основа на колона с помощта на VBA | Този VBA код разделя базата на Excel на уникални стойности в определена колона. Изтеглете работния файл.
Изключете предупредителните съобщения с помощта на VBA в Microsoft Excel 2016 | За да изключим предупредителните съобщения, които прекъсват работещия VBA код, използваме класа Application.
Добавяне и запазване на нова работна книга с помощта на VBA в Microsoft Excel 2016 | За да добавяте и запазвате работни книги, използвайки VBA, използваме клас Работни книги. Работни книги. Добавянето лесно добавя нова работна книга, но …