Функция ВПР всегда была одной из самых мощных функций Excel. Это позволяет вам искать значения в первом столбце таблицы и возвращать значения из полей справа. Но в Excel также есть функция XLOOKUP, которая позволяет вам искать значение в любом столбце или строке и возвращать данные из любого другого столбца.
Как работает XLOOKUP
Функцию XLOOKUP гораздо проще использовать, чем функцию VLOOKUP, потому что вместо указания значения для столбца результатов можно указать весь диапазон.
Функция также позволяет искать как столбец, так и строку, находя значение в пересекающейся ячейке.
Параметры функции XLOOKUP следующие:
= XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value : значение, которое вы хотите найти
- lookup_array : массив (столбец), который вы хотите найти
- return_array : результат (столбец), из которого вы хотите получить значение
- match_mode (необязательно) : выберите точное совпадение (0), точное совпадение или следующее наименьшее значение (-1) или совпадение с подстановочным знаком (2).
- search_mode (необязательно) : выберите, выполнять ли поиск, начиная с первого элемента в столбце (1), последнего элемента в столбце (-1), двоичного поиска по возрастанию (2) или двоичного поиска по убыванию (-2).
Ниже приведены некоторые из наиболее распространенных поисков, которые вы можете сделать с помощью функции XLOOKUP.
Как искать один результат с помощью XLOOKUP
Самый простой способ использования XLOOKUP — это поиск одного результата с использованием точки данных из одного столбца.
-
Этот пример электронной таблицы представляет собой список заказов, представленных торговыми представителями, включая товар, количество единиц, стоимость и общую продажу.
-
Если вы хотите найти первую продажу в списке, представленном конкретным торговым представителем, вы можете создать функцию XLOOKUP, которая ищет имя в столбце Rep. Функция вернет результат из столбца Всего. Функция XLOOKUP для этого:
= XLOOKUP (I2, С2: С44, G 2: G44,0,1)
- I2 : указывает на ячейку поиска Rep Rep.
- C2: C44 : это столбец Rep, который является поисковым массивом
- G2: G33 : это столбец Total, который является возвращаемым массивом
- 0 : выбирает точное совпадение
- 1 : выбирает первое совпадение в результатах
-
Когда вы нажимаете Enter и вводите имя торгового представителя, в ячейке Итоговый результат отобразится первый результат в таблице для этого торгового представителя.
-
Если вы хотите выполнить поиск самой последней продажи (поскольку таблица упорядочена по дате в обратном порядке), измените последний аргумент XLOOKUP на -1 , что начнет поиск с последней ячейки в массиве поиска и предоставит вам это результат вместо.
-
В этом примере показан подобный поиск, который можно выполнить с помощью функции VLOOKUP , используя столбец Rep в качестве первого столбца таблицы поиска. Тем не менее, XLOOKUP позволяет искать любой столбец в любом направлении. Например, если вы хотите найти торгового представителя, который продал первый заказ Binder за год, вы должны использовать следующую функцию XLOOKUP:
= XLOOKUP (I2, D2: D44, С2: C44,0,1)
- D2 : указывает на ячейку поиска предметов
- D2: D44 : это столбец Item, который является поисковым массивом
- C2: C44 : это столбец Rep, который является возвращаемым массивом слева от поискового массива
- 0 : выбирает точное совпадение
- 1 : выбирает первое совпадение в результатах
-
На этот раз результатом будет имя торгового представителя, который продал первый заказ на переплет за год.
Выполните вертикальное и горизонтальное соответствие с XLOOKUP
Еще одна возможность XLOOKUP, на которую не способна VLOOKUP, — это возможность выполнять как вертикальный, так и горизонтальный поиск, то есть вы можете искать элемент по столбцу, а также по строке.
-
В следующем примере электронной таблицы продажи для каждого торгового представителя делятся на кварталы. Если вы хотите увидеть продажи в третьем квартале для конкретного торгового представителя без функции XLOOKUP, такой поиск будет затруднительным.
-
С функцией XLOOKUP этот вид поиска прост. Используя следующую вспомогательную функцию XLOOKUP, вы можете искать продажи в третьем квартале для конкретного торгового представителя:
= XLOOKUP (J2, В2: В42, XLOOKUP (К2, С1: H1, С2: H42))
- J2 : указывает на ячейку поиска Rep
- B2: B42 : это столбец Item, который является массивом поиска столбцов
- K2 : указывает на ячейку поиска Quarter
- C1: H1 : это массив поиска строк
- C2: H42 : это массив поиска для суммы в долларах за каждый квартал
Эта вложенная функция XLOOKUP сначала идентифицирует торгового представителя, а следующая функция XLOOKUP определяет желаемый квартал. Возвращаемое значение будет ячейкой, где эти два перехвата.
-
Результатом по этой формуле является доход за первый квартал для представителя с именем Томпсон.
Использование функции XLOOKUP
Функция XLOOKUP доступна только для подписчиков Office Insider, но вскоре будет распространена на всех подписчиков Office 365.
Если вы хотите самостоятельно протестировать эту функцию, вы можете стать инсайдером Office. Выберите « Файл» > « Учетная запись» , затем выберите выпадающий список Office Insider для подписки.
Как только вы присоединитесь к программе Office Insider, ваша установленная версия Excel получит все последние обновления, и вы сможете начать использовать функцию XLOOKUP.
