Функция ВПР всегда была одной из самых мощных функций 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, которая ищет имя в столбце «Представители». Функция вернет результат из столбца Total. Функция XLOOKUP для этого:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: указывает на ячейку поиска Rep Name
- C2:C44: Это столбец Rep, который является поисковым массивом
- G2:G33: это столбец Total, который представляет собой возвращаемый массив
- 0: выбирает точное совпадение
- 1: выбирает первое совпадение в результатах
-
Когда вы нажмете Enter и наберете имя торгового представителя, ячейка общего результата покажет вам первый результат в таблице для этого торгового представителя.
Image -
Если вы хотите найти самую последнюю распродажу (поскольку таблица упорядочена по дате в обратном порядке), измените последний аргумент XLOOKUP на - 1, который начнет поиск из последней ячейки в массиве поиска и вместо этого предоставить вам этот результат.
Image -
В этом примере показан аналогичный поиск, который можно выполнить с помощью функции ВПР, используя столбец «Повторение» в качестве первого столбца таблицы поиска. Однако XLOOKUP позволяет искать любой столбец в любом направлении. Например, если вы хотите найти торгового представителя, который продал первый заказ Binder в этом году, вы должны использовать следующую функцию XLOOKUP:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: указывает на ячейку поиска элемента
- D2:D44: Это столбец Item, который является поисковым массивом
- C2:C44: это столбец Rep, который является возвращаемым массивом слева от массива поиска
- 0: выбирает точное совпадение
- 1: выбирает первое совпадение в результатах
-
На этот раз результатом будет имя торгового представителя, который продал первый заказ в этом году.
Image
Выполнить вертикальное и горизонтальное сопоставление с XLOOKUP
Еще одна возможность XLOOKUP, на которую не способна функция VLOOKUP, - это возможность выполнять как вертикальный, так и горизонтальный поиск, то есть вы можете искать элемент как по столбцу, так и по строке.
Эта функция двойного поиска является эффективной заменой других функций Excel, таких как ИНДЕКС, ПОИСКПОЗ или ГПР.
-
В приведенном ниже примере таблицы продажи каждого торгового представителя разделены по кварталам. Если вы хотите увидеть продажи за третий квартал для определенного торгового представителя без функции XLOOKUP, такой поиск будет затруднен.
Image -
С функцией XLOOKUP этот вид поиска упрощается. Используя следующую функцию XLOOKUP, вы можете искать продажи за третий квартал для определенного торгового представителя:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: указывает на ячейку поиска репутации
- B2:B42: Это столбец Item, который является массивом поиска столбцов
- K2: Указывает на ячейку поиска квартала
- C1:H1: Это массив поиска строк
- C2:H42: Это массив поиска суммы в долларах за каждый квартал
Эта вложенная функция XLOOKUP сначала идентифицирует торгового представителя, а следующая функция XLOOKUP определяет нужный квартал. Возвращаемое значение будет ячейкой, в которой эти два перехвата.
-
Результатом этой формулы является прибыль за первый квартал для представителя по имени Томпсон.
Image
Использование функции XLOOKUP
Функция XLOOKUP доступна только для подписчиков Office Insider, но вскоре будет развернута для всех подписчиков Microsoft 365.
Если вы хотите протестировать функцию самостоятельно, вы можете стать участником программы предварительной оценки Office. Выберите File > Account, затем выберите раскрывающийся список Office Insider, чтобы подписаться.
Как только вы присоединитесь к программе предварительной оценки Office, ваша установленная версия Excel будет получать все последние обновления, и вы сможете начать использовать функцию XLOOKUP.
