Посмотреть видео - Функция XLOOKUP в Excel (10 примеров XLOOKUP)
Excel Функция XLOOKUP наконец-то прибыл.
Если вы использовали ВПР или ИНДЕКС / ПОИСКПОЗ, я уверен, что вам понравится гибкость, которую обеспечивает функция XLOOKUP.
В этом руководстве я расскажу все, что нужно знать о функции XLOOKUP, и несколько примеров, которые помогут вам узнать, как лучше всего ее использовать.
Итак, приступим!
Что такое XLOOKUP?
XLOOKUP - это новая функция Office 365 и новая улучшенная версия функции ВПР / ГПР.
Он делает все, что раньше делала ВПР, и многое другое.
XLOOKUP - это функция, которая позволяет быстро искать значение в наборе данных (по вертикали или горизонтали) и возвращать соответствующее значение в другой строке / столбце.
Например, если у вас есть оценки учащихся на экзамене, вы можете использовать XLOOKUP, чтобы быстро проверить, сколько учащихся набрал, используя имя учащегося.
Сила этой функции станет еще более очевидной, когда я углублюсь в некоторые Примеры XLOOKUP позже в этом руководстве.
Но прежде чем я перейду к примерам, возникает большой вопрос - как мне получить доступ к XLOOKUP?
Как получить доступ к XLOOKUP?
На данный момент XLOOKUP доступен только для пользователей Office 365.
Таким образом, если вы используете предыдущие версии Excel (2010/2013/2016/2019), вы не сможете использовать эту функцию.
Я также не уверен, будет ли это когда-либо выпущено для предыдущих версий или нет (возможно, Microsoft сможет создать надстройку так же, как они это сделали для Power Query). Но на данный момент вы можете использовать его только в Office 365.
Щелкните здесь, чтобы перейти на Office 365
Если вы уже используете Office 365 (Домашний, Персональный или Университетский) и у вас нет доступа к нему, вы можете перейти на вкладку «Файл» и нажать «Учетная запись».
Там будет программа предварительной оценки Office, и вы можете щелкнуть ее и присоединиться к программе предварительной оценки Office. Это даст вам доступ к функции XLOOKUP.
Я ожидаю, что XLOOKUP скоро будет доступен во всех версиях Office 365.
Примечание. XLOOKUP также доступен для Office 365 для Mac и Excel в Интернете (Excel онлайн).Синтаксис функции XLOOKUP
Ниже приведен синтаксис функции XLOOKUP:
= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Если вы использовали ВПР, вы заметите, что синтаксис очень похож, но, конечно же, с некоторыми замечательными дополнительными функциями.
Не волнуйтесь, если синтаксис и аргумент покажутся вам слишком сложными. Я расскажу о них с помощью нескольких простых примеров XLOOKUP позже в этом руководстве, которые сделают его кристально ясным.Функция XLOOKUP может передавать 6 аргументов (3 обязательных и 3 необязательных):
- lookup_value - ценность, которую вы ищете
- lookup_array - массив, в котором вы ищете значение поиска
- return_array - массив, из которого вы хотите получить и вернуть значение (соответствующее позиции, где найдено значение поиска)
- [if_not_found] - значение, возвращаемое в случае, если искомое значение не найдено. Если вы не укажете этот аргумент, будет возвращена ошибка # Н / Д.
- [match_mode] - Здесь вы можете указать желаемый тип соответствия:
- 0 - Точное совпадение, где lookup_value должно точно соответствовать значению в lookup_array. Это вариант по умолчанию.
- -1 - Ищет точное совпадение, но если оно найдено, возвращает следующий меньший элемент / значение.
- 1 - Ищет точное совпадение, но если оно найдено, возвращает следующий более крупный элемент / значение.
- 2 - Для частичного сопоставления с использованием подстановочных знаков (* или ~)
- [search_mode] - Здесь вы указываете, как функция XLOOKUP должна искать в lookup_array
- 1 - Это вариант по умолчанию, когда функция начинает искать значение lookup_value сверху (первый элемент) до низа (последний элемент) в массиве lookup_array.
- -1 - Осуществляет поиск снизу вверх. Полезно, если вы хотите найти последнее совпадающее значение в lookup_array
- 2 - Выполняет двоичный поиск, в котором данные необходимо отсортировать в порядке возрастания. Если не отсортировать, это может привести к ошибке или неверным результатам.
- -2 - Выполняет двоичный поиск, в котором данные необходимо отсортировать по убыванию. Если не отсортировать, это может привести к ошибке или неверным результатам.
Примеры функций XLOOKUP
Теперь перейдем к самому интересному - нескольким практическим примерам XLOOKUP.
Эти примеры помогут вам лучше понять, как работает XLOOKUP, чем он отличается от VLOOKUP и INDEX / MATCH, а также некоторые улучшения и ограничения этой функции.
Щелкните здесь, чтобы загрузить файл с примером, и следуйте инструкциям.
Пример 1: получение значения поиска
Предположим, у вас есть следующий набор данных, и вы хотите получить математическую оценку Грега (значение поиска).
Ниже приведена формула, которая делает это:
= XLOOKUP (F2; A2: A15; B2: B15)
В приведенной выше формуле я только что использовал обязательные аргументы, в которых он ищет имя (сверху вниз), находит точное совпадение и возвращает соответствующее значение из B2: B15.
Одно очевидное различие между функциями XLOOKUP и VLOOKUP заключается в том, как они обрабатывают поисковый массив. В VLOOKUP у вас есть весь массив, в котором значение поиска находится в крайнем левом столбце, а затем вы указываете номер столбца, из которого вы хотите получить результат. XLOOKUP, с другой стороны, позволяет вам выбирать lookup_array и return_array отдельно.Одно мгновенное преимущество использования lookup_array и return_array в качестве отдельных аргументов означает, что теперь вы можете посмотри налево. У VLOOKUP было это ограничение, когда вы могли только искать и находить значение, которое находится справа. Но с XLOOKUP это ограничение снято.
Вот пример. У меня такой же набор данных, где имя справа, а return_range слева.
Ниже приведена формула, которую я могу использовать, чтобы получить оценку Грега по математике (что означает просмотр слева от lookup_value).
= XLOOKUP (F2; D2: D15; A2: A15)
XLOOKUP решает еще одну серьезную проблему: если вы вставляете новый столбец или перемещаете столбцы, полученные данные все равно будут правильными. ВПР, скорее всего, приведет к поломке или неправильному результату в таких случаях, поскольку в большинстве случаев значение индекса столбца жестко запрограммировано.
Пример 2: поиск и выборка всей записи
Возьмем для примера те же данные.
В данном случае я не хочу просто получать оценку Грега по математике. Я хочу получить баллы по всем предметам.
В этом случае я могу использовать следующую формулу:
= XLOOKUP (F2; A2: A15; B2: D15)
В приведенной выше формуле используется диапазон return_array, превышающий столбец (B2: D15). Поэтому, когда значение поиска находится в A2: A15, формула возвращает всю строку из return_array.
Кроме того, вы не можете удалить только ячейки, которые являются частью массива, который был автоматически заполнен. В этом примере вы не можете удалить H2 или I2. Если вы попробуете, ничего не произойдет. Если вы выберете эти ячейки, формула в строке формул будет недоступна (что означает, что ее нельзя изменить).
Вы можете удалить формулу в ячейке G2 (там, где мы ее изначально ввели), она удалит весь результат.
Это полезное усовершенствование, так как раньше с ВПР вам нужно будет указать номер столбца отдельно для каждой формулы.
Пример 3: двусторонний поиск с использованием XLOOKUP (горизонтальный и вертикальный поиск)
Ниже приведен набор данных, в котором я хочу узнать оценку Грега по математике (тема в ячейке G2).
Это можно сделать с помощью двустороннего поиска, при котором я ищу имя в столбце A и имя субъекта в строке 1. Преимущество этого двустороннего поиска заключается в том, что результат не зависит от имени учащегося и имени субъекта. Если я изменю название темы на «Химия», эта двусторонняя формула XLOOKUP по-прежнему будет работать и даст мне правильный результат.
Ниже приведена формула, которая будет выполнять двусторонний поиск и давать правильный результат:
= XLOOKUP (G1; B1: D1; XLOOKUP (F2; A2: A15; B2: D15))
В этой формуле используется вложенный XLOOKUP, где сначала я использую его для получения всех оценок учащегося в ячейке F2.
Таким образом, результат XLOOKUP (F2, A2: A15, B2: D15) равен {21,94,81}, что в данном случае представляет собой массив оценок, выставленных Грегом.
Затем он снова используется во внешней формуле XLOOKUP в качестве возвращаемого массива. Во внешней формуле XLOOKUP я ищу имя субъекта (которое находится в ячейке G1), а массив поиска - B1: D1.
Если имя субъекта - Math, эта внешняя формула XLOOKUP извлекает первое значение из возвращаемого массива - в данном примере это {21,94,81}.
Это делает то же самое, что до сих пор достигалось с помощью комбинации ИНДЕКС и ПОИСКПОЗ.
Щелкните здесь, чтобы загрузить файл с примером, и следуйте инструкциям.
Пример 4: Когда значение поиска не найдено (обработка ошибок)
В формулу XLOOKUP добавлена обработка ошибок.
Четвертым аргументом в функции XLOOKUP является [if_not_found], где вы можете указать, что вы хотите, если поиск не может быть найден.
Предположим, у вас есть набор данных, как показано ниже, где вы хотите получить оценку по математике в случае совпадения, и в случае, если имя не найдено, вы хотите вернуть - «Не отображалось»
Приведенная ниже формула сделает это:
= XLOOKUP (F2; A2: A15; B2: B15; «Не отображалось»)
В этом случае я жестко запрограммировал то, что хочу получить в случае, если совпадения нет. Вы также можете использовать ссылку на ячейку или формулу.
Пример 5: Вложенный XLOOKUP (поиск в нескольких диапазонах)
Гениальность аргумента [if_not_found] заключается в том, что он позволяет использовать вложенная формула XLOOKUP.
Например, предположим, что у вас есть два отдельных списка, как показано ниже. Хотя у меня эти две таблицы находятся на одном листе, вы можете разместить их на отдельных листах или даже в книгах.
Ниже приведена вложенная формула XLOOKUP, которая проверяет имя в обеих таблицах и возвращает соответствующее значение из указанного столбца.
= XLOOKUP (A12; A2: A8; B2: B8; XLOOKUP (A12; F2: F8; G2: G8))
В приведенной выше формуле я использовал аргумент [if_not_found], чтобы использовать другую формулу XLOOKUP. Это позволяет вам добавить второй XLOOKUP в ту же формулу и сканировать две таблицы с помощью одной формулы.
Я не уверен, сколько вложенных файлов XLOOKUP можно использовать в формуле. Я пробовал до 10, и это сработало, потом я сдался 🙂
Пример 6: Найдите последнее совпадающее значение
Этот был крайне необходим, и XLOOKUP сделал это возможным. Теперь вам не нужно искать запутанные способы получить последнее совпадающее значение в диапазоне.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите проверить, когда последний человек был нанят в каждый отдел и какова дата найма.
Приведенная ниже формула будет искать последнее значение для каждого отдела и давать имя последнего найма:
= XLOOKUP (F1; $ B $ 2: $ B $ 15; $ A $ 2: $ A $ 15 ,,, - 1)
В приведенной ниже формуле будет указана дата последнего найма для каждого отдела:
= XLOOKUP (F1; $ B $ 2: $ B $ 15; $ C $ 2: $ C $ 15 ,,, - 1)
Поскольку XLOOKUP имеет встроенную функцию для указания направления поиска (от первого к последнему или от последнего к первому), это выполняется с помощью простой формулы. С вертикальными данными VLOOKUP и INDEX / MATCH всегда смотрят сверху вниз, но с XLOOKUP также могут указывать направление снизу вверх.
Пример 7: приблизительное совпадение с XLOOKUP (найти налоговую ставку)
Еще одно заметное улучшение с XLOOKUP состоит в том, что теперь есть четыре режима сопоставления (VLOOKUP имеет 2, а MATCH - 3).
Вы можете указать любой из четырех аргументов, чтобы решить, как должно совпадать значение поиска:
- 0 - Точное совпадение, где lookup_value должно точно соответствовать значению в lookup_array. Это вариант по умолчанию.
- -1 - Ищет точное совпадение, но если оно найдено, возвращает следующий меньший элемент / значение.
- 1 - Ищет точное совпадение, но если оно найдено, возвращает следующий более крупный элемент / значение.
- 2 - Для частичного сопоставления с использованием подстановочных знаков (* или ~)
Ниже у меня есть набор данных, в котором я хочу найти комиссию каждого человека - и комиссию необходимо рассчитать с помощью таблицы справа.
Ниже приведена формула, которая сделает это:
= XLOOKUP (B2; $ E $ 2: $ E $ 6; $ F $ 2: $ F $ 6,0; -1) * B2
Это просто использует значение продаж в качестве справочника и просматривает справочную таблицу справа. В этой формуле я использовал -1 в качестве пятого аргумента ([match_mode]), что означает, что он будет искать точное совпадение, а если он не найдет его, он вернет значение, немного меньшее, чем значение поиска. .
И, как я уже сказал, вам не нужно беспокоиться о том, отсортированы ли ваши данные.
Щелкните здесь, чтобы загрузить файл с примером, и следуйте инструкциям.
Пример 8: горизонтальный поиск
XLOOKUP может выполнять как вертикальный, так и горизонтальный поиск.
Ниже у меня есть набор данных, в котором у меня есть имена студентов и их оценки в строках, и я хочу получить оценку для имени в ячейке B7.
Приведенная ниже формула сделает это:
= XLOOKUP (B7; B1: O1; B2: O2)
Это не что иное, как простой поиск (аналогичный тому, что мы видели в примере 1), но горизонтальный.
Все приведенные мной примеры вертикального просмотра также могут быть выполнены с помощью горизонтального просмотра с помощью XLOOKUP (прощай, VLOOKUP и HLOOKUP).
Пример 9: Условный поиск (использование XLOOKUP с другими формулами)
Это немного продвинутый пример, который также демонстрирует возможности XLOOKUP, когда вам нужно выполнять сложные поисковые запросы.
Ниже приведен набор данных, в котором у меня есть имена студентов и их баллы, и я хочу знать имя студента, который набрал максимум баллов по каждому предмету, и количество студентов, набравших более 80 баллов по каждому предмету.
Ниже приведена формула, по которой будет указано имя ученика с наивысшими оценками по каждому предмету:
= XLOOKUP (МАКС (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A 2 доллара: 15 австралийских долларов)
Поскольку XLOOKUP можно использовать для возврата всего массива, я использовал его, чтобы сначала получить все оценки для требуемой темы.
Например, для математики, когда я использую XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), я получаю все оценки по математике. Затем я могу использовать функцию MAX, чтобы найти максимальное количество баллов в этом диапазоне.
Затем эта максимальная оценка становится моим значением поиска, а диапазон поиска будет массивом, возвращаемым XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15).
Я использую это в другой формуле XLOOKUP, чтобы получить имя ученика, набравшего максимальное количество баллов.
А чтобы подсчитать количество студентов, набравших более 80 баллов, используйте следующую формулу:
= СЧЁТЕСЛИ (XLOOKUP (G1; $ B $ 1: $ D $ 1; $ B $ 2: $ D $ 15); "> 80")
Здесь просто используется формула XLOOKUP, чтобы получить диапазон всех значений для данной темы. Затем он помещает его в функцию СЧЁТЕСЛИ, чтобы получить количество баллов, превышающее 80.
Пример 10: Использование подстановочного знака в XLOOKUP
Подобно тому, как вы можете использовать подстановочные знаки в VLOOKUP и MATCH, вы также можете сделать это с помощью XLOOKUP.
Но есть разница.
В XLOOKUP вам нужно указать, что вы используете подстановочные знаки (в пятом аргументе). Если вы не укажете это, XLOOKUP выдаст ошибку.
Ниже приведен набор данных, в котором у меня есть названия компаний и их рыночная капитализация.
Я хочу найти название компании в столбце D и получить рыночную капитализацию из таблицы слева. И поскольку имена в столбце D не являются точными совпадениями, мне придется использовать подстановочные знаки.
Ниже приведена формула, которая сделает это:
= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11`` 2)
В приведенной выше формуле я использовал подстановочный знак звездочки (*) до и после D2 (он должен быть заключен в двойные кавычки и соединен с D2 с помощью амперсанда).
Это указывает формуле на просмотр всех ячеек, и если она содержит слово в ячейке D2 (которая является Apple), считайте его точным совпадением. Неважно, сколько и каких символов стоит до и после текста в ячейке D2.
И чтобы убедиться, что XLOOKUP принимает подстановочные знаки, пятый аргумент имеет значение 2 (совпадение подстановочных знаков).
Пример 11: найти последнее значение в столбце
Поскольку XLOOKUP позволяет выполнять поиск снизу вверх, вы можете легко найти последнее значение в списке, а также получить соответствующее значение из столбца.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите знать, какая компания была последней и какова рыночная капитализация этой последней компании.
Приведенная ниже формула даст вам название последней компании:
= XLOOKUP ("*"; A2: A11; A2: A11`` 2, -1)
А приведенная ниже формула даст рыночную капитализацию последней компании в списке:
= XLOOKUP ("*"; A2: A11; B2: B11`` 2, -1)
В этих формулах снова используются подстановочные знаки. В них я использовал звездочку (*) в качестве значения поиска, что означает, что это будет рассматривать первую встреченную ячейку как точное совпадение (поскольку звездочка может быть любым символом и любым количеством символов).
И поскольку направление - снизу вверх (для вертикально расположенных данных), он вернет последнее значение в списке.
И вторая формула, так как использует отдельный return_range, чтобы получить рыночную капитализацию фамилии в списке.
Щелкните здесь, чтобы загрузить файл с примером, и следуйте инструкциям.
Что делать, если у вас нет XLOOKUP?
Поскольку XLOOKUP, скорее всего, будет доступен только для пользователей Office 365, один из способов получить его - перейти на Office 365.
Если у вас уже есть Office 365 для дома, личного или университетского, у вас уже есть доступ к XLOOKUP. Все, что вам нужно сделать, это присоединиться к программе предварительной оценки Office.
Для этого перейдите на вкладку «Файл», нажмите «Учетная запись», а затем выберите опцию «Инсайдерский офис». Будет возможность присоединиться к инсайдерской программе.
Если у вас есть другие подписки на Office 365 (например, Enterprise), я уверен, что XLOOKUP и другие замечательные функции (например, динамические массивы, формулы, такие как SORT и FILTER) станут доступны в ближайшее время.
Если вы используете Excel 2010/2013/2016/2019, у вас не будет XLOOKUP, и вам придется продолжать использовать комбинации ВПР, ГПР и ИНДЕКС / ПОИСКПОЗ, чтобы максимально эффективно использовать формулы поиска.
Обратная совместимость XLOOKUP
Это одна вещь, о которой вам нужно быть осторожным: XLOOKUP - это НЕ обратно совместимый.
Это означает, что если вы создадите файл и воспользуетесь формулой XLOOKUP, а затем откроете его в версии, в которой нет XLOOKUP, он покажет ошибки.
Поскольку XLOOKUP - огромный шаг вперед в правильном направлении, я считаю, что это станет формулой поиска по умолчанию, но, безусловно, пройдет несколько лет, прежде чем она получит широкое распространение. В конце концов, я все еще вижу, что некоторые люди используют Excel 2003.
Итак, это 11 примеров XLOOKUP, которые помогут вам быстрее выполнять поиск и справки, а также упростят использование.
Надеюсь, вы нашли этот урок полезным!
