1. Программное обеспечение Microsoft OfficeExcelКак использовать функцию XLOOKUP в Excel 2016

Грег Харви

Подписчики Excel 2016 для Office 365 в Windows и Mac теперь поддерживают новую функцию XLOOKUP, которая позиционируется как значительно более простая и универсальная замена очень популярной (но часто порочащейся) функции вертикального поиска VLOOKUP (не знаю, что такое X в XLOOKUP). расшифровывается как; eXtensive, возможно?).

Для тех из вас, кто еще не знаком с VLOOKUP (считается третьей наиболее часто используемой функцией сразу после SUM и AVERAGE), эта функция выполняет поиск по строке в крайнем левом столбце назначенной таблицы поиска сверху вниз, пока не найдет значение в столбец поиска, обозначенный номером смещения, который совпадает или превышает номер, который вы ищете. Хотя функция VLOOKUP чрезвычайно полезна для поиска определенных элементов в длинном списке или столбце таблицы данных на рабочем листе, она имеет несколько ограничений, которые не разделяются этой новой функцией поиска, например XLOOKUP:

  • По умолчанию находит точные совпадения для вашего значения поиска в диапазоне поиска Может выполнять поиск как по вертикали (по строке), так и по горизонтали (по столбцу) в таблице, что исключает необходимость использования функции HLOOKUP при поиске по столбцу по горизонтали Можно выполнить поиск влево или вправо, чтобы диапазон поиска в вашей таблице поиска не обязательно находился в столбце слева от столбца, обозначенного как диапазон возврата, чтобы функция работала Когда используется точное совпадение по умолчанию, работает, даже если значения в диапазоне поиска не отсортированы в определенном порядке Может искать от нижней строки до верхней части диапазона поиска, используя необязательный аргумент режима поиска

Функция XLOOKUP имеет пять возможных аргументов, первые три из которых являются обязательными, а последние два необязательными, используя следующий синтаксис:

XLOOKUP (искомое_значение, просматриваемый_массив, return_array, [match_mode], [search_mode])

Обязательный аргумент lookup_value обозначает значение или элемент, который вы ищете. Обязательный аргумент массива look_up указывает диапазон ячеек, в которых необходимо выполнить поиск для этого значения поиска, а аргумент return_array обозначает диапазон ячеек, содержащих значение, которое вы хотите вернуть, когда Excel находит точное совпадение.

* Помните, что при назначении аргументов lookup_array и return_array в вашей функции XLOOKUP оба диапазона должны иметь одинаковую длину, в противном случае Excel вернет значение #VALUE! ошибка в вашей формуле. Это еще одна причина, по которой вам следует использовать имена диапазонов или имена столбцов указанной таблицы данных при определении этих аргументов, а не указывать их или вводить ссылки на их ячейки.

Необязательный аргумент match_mode может содержать любое из следующих четырех значений:

  • 0 для точного соответствия (по умолчанию, то же самое, когда не указан аргумент match_mode) -1 для точного соответствия или следующего меньшего значения 1 для точного соответствия или следующего большего значения 2 для частичного совпадения с использованием подстановочных знаков, соединенных со ссылкой на ячейку в аргументе lookup_value

Необязательный аргумент search_mode может содержать любое из следующих четырех значений:

  • 1 для поиска «от начала до конца», то есть сверху вниз (по умолчанию, так же как и в случае, когда не указан аргумент search_mode) -1 для поиска по порядку, то есть снизу вверх 2 для двоичного поиска в порядке возрастания -2 для двоичного поиска в порядке убывания

Лучший способ понять всю мощь и универсальность новой функции XLOOKUP - это увидеть ее в действии на листе Excel. На следующем рисунке у меня есть лист с простой таблицей данных о продажах за 2019 год, упорядоченный по странам Чтобы использовать XLOOKUP для возврата общих продаж из этой таблицы в ячейку E4 в зависимости от страны, которую вы ввели в ячейку D4 рабочего листа, выполните следующие действия:

  1. Поместите курсор в ячейку E4 рабочего листа Выберите параметр «Поиск и справка» на вкладке «Формулы», а затем XLOOKUP в нижней части раскрывающегося меню, чтобы открыть диалоговое окно «Аргументы функций». Щелкните ячейку D4 на листе, чтобы ввести ссылку на ячейку в текстовом поле аргумента Lookup_value. Нажмите клавишу Tab, чтобы выбрать текстовое поле аргумента Lookup_array, затем щелкните ячейку A4 и, удерживая нажатой клавишу Shift, нажмите клавишу со стрелкой Ctrl, чтобы выбрать A4: A8 в качестве диапазона для поиска (поскольку диапазон A3: B8 определен как таблица данных Excel, Таблица1 [Страна] появляется в текстовом поле вместо диапазона A4: A8). Нажмите клавишу Tab, чтобы выбрать текстовое поле аргумента Return_array, затем щелкните ячейку B4 и, удерживая нажатой клавишу «Shift», нажмите клавишу со стрелкой «Ctrl», чтобы выбрать B4: B8 в качестве диапазона, содержащего значения, которые должны быть возвращены на основе результатов поиска (который отображается как Таблица 1 [Всего продаж] в текстовом поле).

Нажмите OK, чтобы ввести формулу XLOOKUP в ячейку E4.

Создание формулы XLOOKUP

Excel вводит формулу XLOOKUP в ячейку E4 рабочего листа и возвращает 4900 в результате, потому что Коста-Рика в настоящее время вводится в поисковую ячейку D4, и, как видно из таблицы продаж 2019 года, это действительно общий объем продаж для этой страны.

Поскольку XLOOKUP работает как справа налево, так и слева направо, вы можете использовать эту функцию точно так же, чтобы вернуть страну из этой таблицы продаж на основе определенного показателя продаж. На следующем рисунке показано, как вы это делаете. На этот раз вы создаете формулу XLOOKUP в ячейке D4 и назначаете значение, введенное в ячейку E4 (в данном случае 11 000), в качестве аргумента lookup_value.

Кроме того, вы вводите -1 в качестве аргумента match_mode, чтобы переопределить функцию точного соответствия по умолчанию, чтобы Excel возвращал страну с точным соответствием значению продаж, введенному в поисковой ячейке E4, или страну со следующим меньшим общим объемом продаж (Мексика с В этом случае 10 000 долл. США, поскольку в этой таблице нет страны с общим объемом продаж 11 000 долл. США). Без указания аргумента match_mode для этой формулы Excel будет возвращать #NA в качестве результата, поскольку в этой таблице продаж нет точного соответствия $ 11 000.

XLOOKUP формула в D4

Поскольку функция XLOOKUP одинаково удобна при поиске по столбцам по горизонтали, так же как и при поиске по строке, вы можете использовать ее для создания формулы, которая выполняет двусторонний поиск (вместо необходимости создавать формулу, которая объединяет функции INDEX и MATCH как в прошлом). На следующем рисунке, содержащем таблицу производственного графика 2019 года для номеров деталей с AB-100 по AB-103 для месяцев с апреля по декабрь, показано, как это делается.

вложенные функции XLOOKUP

В ячейке B12 я создал следующую формулу:

= XLOOKUP (part_lookup $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

Эта формула начинается с определения функции XLOOKUP, которая ищет строку по вертикали для точного соответствия записи детали, сделанной в ячейке с именем part_lookup (в данном случае ячейке B10) в диапазоне ячеек $ A $ 3: $ A $ 6 рабочей таблицы , Однако обратите внимание, что аргумент return_array для этой исходной функции LOOKUP сам по себе является второй функцией XLOOKUP.

Эта вторая вложенная функция XLOOKUP просматривает диапазон ячеек $ B $ 2: $ J $ 2 по горизонтали по столбцу для точного соответствия записи даты, сделанной в ячейке с именем date_lookup (в данном случае ячейка B11). Аргумент return_array для этой второй вложенной функции XLOOKUP - это $ B $ 3: $ J $ 6, диапазон ячеек всех производственных значений в таблице.

Эта формула работает так, что Excel сначала вычисляет результат второй вложенной функции XLOOKUP, выполняя горизонтальный поиск, который в этом случае возвращает массив в диапазоне ячеек D3: D6 столбца июня 19 (со значениями: 438, 153, 306 и 779) как результат. Этот результат, в свою очередь, становится аргументом return_array для исходной функции XLOOKUP, которая выполняет вертикальный поиск по строке для точного соответствия записи номера детали, выполненной в ячейке B11 (с именем part_lookup). Поскольку в этом примере эта ячейка part_lookup содержит AB-102, формула возвращает только производственное значение июня 19, 306, из результата второй, следующей функции XLOOKUP.

Вот оно! Первый взгляд на XLOOKUP, мощную, универсальную и довольно простую в использовании новую функцию поиска, которая может выполнять не только поиск по одному значению, выполняемый функциями VLOOKUP и HLOOKUP, но также и двусторонний поиск значений, выполняемый путем объединения Функции INDEX и MATCH также.

* К сожалению, функция XLOOKUP не имеет обратной совместимости с более ранними версиями Microsoft Excel, которые поддерживают только функции VLOOKUP и HLOOKUP или совместимы с текущими версиями, которые еще не включают ее в качестве одной из своих функций поиска, таких как Excel 2019 и Excel Online. Это означает, что если вы предоставите рабочую книгу, содержащую формулы XLOOKUP, коллегам или клиентам, использующим версию Excel, в которой нет этой новой функции поиска, все эти формулы будут возвращать #NAME? Значения ошибок при открытии рабочего листа.