Пользовательские Excel XLOOKUP Функция

Posted on: 2020年10月22日 2020年11月12日
Tags: lookup-formulas

Функция XLOOKUP является эксклюзивной для внутренней программы Office 365.

Функция ПРОСМОТР имеет множество функций, которые преодолевают многие из недостатков функций ВПР и ГПР, но, к сожалению, на данный момент она недоступна для нас. Но не волнуйтесь, мы можем создать функцию XLOOKUP, которая работает точно так же, как предстоящая функция XLOOKUP в MS Excel. Мы будем добавлять к нему функции одну за другой.

Код VBA функции XLOOKUP. Приведенная ниже функция поиска UDF решит многие проблемы. Скопируйте его или загрузите надстройку xl, расположенную ниже.

Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range)

XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0))

End Function

Пояснение:

Приведенный выше код — это просто базовый индекс-ПОИСКПОЗ, используемый в VBA. Это упрощает многие вещи, с которыми сталкивается новый пользователь. Если разрешает сложность функции ИНДЕКС-ПОИСКПОЗ и использует только три аргумента. Вы можете скопировать его в свой файл Excel или загрузить файл .xlam ниже и установить его как надстройку Excel. Если вы не знаете, как создать и использовать надстройку, `link: / excel-macros-and-vba-add-in-in-vb [щелкните здесь, это поможет вам].

XLOOKUP Add-In]

давайте посмотрим, как это работает на листе Excel.

Синтаксис XLOOKUP

=XLOOKUP(lookup_value, lookup_array, result_array)

lookup_value: это значение, которое вы хотите найти в

lookup_array.

lookup_array: это одномерный диапазон, в котором вы хотите искать значение lookup_value. result_array: это также одномерный диапазон. Это диапазон, из которого вы хотите получить значение.

Давайте посмотрим на эту функцию XLOOKUP в действии.

Примеры XLOOKUP:

Здесь у меня есть таблица данных в Excel. Давайте исследуем некоторые функции, используя эту таблицу данных.

Функциональность 1.ExactLookup слева и справа от значения поиска. Как мы знаем, функция Excel VLOOKUP не может извлекать значения слева от значения поиска. Для этого вы должны использовать сложную комбинацию ИНДЕКС-ПОИСКПОЗ. Но не больше.

Предполагая, что нам нужно получить всю информацию, доступную в таблице некоторых номеров рулонов. В этом случае вам также нужно будет получить регион, который находится слева от столбца номера рулона.

Запишите эту формулу I2:

=XLOOKUP(H2,$B$2:$B$14,$A$2:$A$14)

Мы получаем результат «Север» для ролика 112. Скопируйте или перетащите формулу в ячейки ниже, чтобы заполнить их соответствующими регионами.

Как это работает?

Механизм простой. Эта функция ищет lookup_value в lookup_array и возвращает индекс первого точного совпадения. Затем использует этот индекс для получения значения из result_array. Эта функция отлично работает с именованными диапазонами.

Аналогичным образом используйте эту формулу для получения значения из каждого столбца.

Функциональные возможности 2. ExactHorizontalLookup над и под значением поиска. XLOOKUP также работает как точная функция HLOOKUP. Функция HLOOKUP имеет те же ограничения, что и VLOOKUP. Он не может получить значение, превышающее значение поиска. Но XLOOKUP не только работает как HLOOKUP, но и преодолевает эту слабость. Посмотрим как.

Гипотетически, если вы хотите сравнить две записи. Запись поиска у вас уже есть. Запись, с которой вы хотите сравнить, находится выше диапазона поиска. Используйте эту формулу в этом случае.

=XLOOKUP(H7,$A$9:$E$9,$A$2:$E$2)

перетащите формулу вниз, и у вас будет вся запись сравнения строки.

Функциональность 3. Нет необходимости указывать номер столбца и точное совпадение по умолчанию.

Когда вы используете функцию VLOOKUP, вы должны указать номер столбца, из которого вы хотите получить значения. Для этого вам нужно посчитать столбцы или использовать какие-то уловки, воспользоваться помощью других функций. С этим UDF XLOOKUP этого делать не нужно.

Если вы используете ВПР только для получения какого-либо значения из одного столбца или для проверки наличия значения в столбце, это лучшее решение, на мой взгляд.

Функциональность 4. Заменяет функцию ИНДЕКС-ПОИСКПОЗ, ВПР, ГПР

Для простых задач наша функция XLOOKUP заменяет вышеупомянутые функции.

Ограничения XLOOKUP:

Когда дело доходит до сложных формул, таких как //lookup-formulas/vlookup-with-dynamic-col-index.html[VLOOKUP с динамическим индексом столбца] `, где мы VLOOKUP идентифицирует столбец подстановки с заголовками, этот XLOOKUP завершится ошибкой.

Еще одно ограничение заключается в том, что если вам нужно найти несколько случайных столбцов или строк из таблицы, эта функция будет бесполезной, поскольку вам придется писать эту формулу снова и снова. Это можно преодолеть, используя link: / excel-range-name-all-about-excel-named-range-excel-range-name [именованные диапазоны].

На данный момент мы не добавили приблизительную функциональность, поэтому, конечно, вы не можете получить приблизительное соответствие. Мы добавим это слишком скоро.

Если функция XLOOKUP не может найти искомое значение, она вернет ошибку #VALUE, а не # N / A.

Так что да, ребята, вот как вы используете XLOOKUP для извлечения, поиска и проверки значений в таблицах Excel. Вы можете использовать эту определяемую пользователем функцию для беспроблемного поиска слева или вверх от значения поиска. Если у вас все еще есть какие-либо сомнения или какие-либо особые требования, связанные с этой функцией или запросом, связанным с EXCEL 2010/2013/2016/2019/365 или VBA, задайте их в разделе комментариев ниже. Вы обязательно получите ответ.

Статьи по теме:

link: / custom-functions-in-vba-create-vba-function-to-return-array [Создать функцию VBA для возврата массива] | Чтобы вернуть массив из пользовательской функции, мы должны объявить его при присвоении имени UDF.

link: / excel-array-formulas-array-in-excel-formula [Массивы в Excel Formul] | Узнайте, какие массивы есть в Excel.

link: / vba-user-defined-function [Как создать пользовательскую функцию с помощью VBA] | Узнайте, как создавать пользовательские функции в Excel `link: / custom-functions-userdefined-functions-from-other-workbooks-using-vba-in-microsoft-excel [Использование пользовательской функции (UDF) из другой книги с использованием VBA в Microsoft Excel] `| Используйте пользовательскую функцию в другой книге Excel `link: / custom-functions-return-error-values-from-user-defined-functions-using-vba-in-microsoft-excel [Возвращать значения ошибок из пользовательских функции с использованием VBA в Microsoft Excel] `| Узнайте, как можно вернуть значения ошибок из пользовательской функции