Excel 2020: двенадцать преимуществ XLOOKUP - Советы по Excel

Новая функция XLOOKUP внедряется в Office 365 с ноября 2019 года. Джо МакДэйд из команды Excel разработал XLOOKUP, чтобы объединить людей, использующих ВПР, и людей, использующих ИНДЕКС / ПОИСКПОЗ. В этом разделе обсуждаются 12 преимуществ XLOOKUP:

По умолчанию используется точное совпадение.
Третий аргумент функции VLOOKUP, основанный на целых числах, теперь является правильной ссылкой.
IFNA встроена для обработки пропущенных значений.
У XLOOKUP нет проблем с переходом влево.
Найдите совпадение, меньшее или большее, без сортировки таблицы.
XLOOKUP может выполнять HLOOKUP.
Найдите последнее совпадение, выполнив поиск снизу.
Подстановочные знаки по умолчанию отключены, но вы можете снова включить их.
Возвратите все 12 месяцев в единую формулу.
Может возвращать ссылку на ячейку, если XLOOKUP стоит рядом с двоеточием, например XLOOKUP (); XLOOKUP ()
Может выполнять двустороннее сопоставление, такое как INDEX (, MATCH, MATCH).
Может суммировать все поиски в одной формуле, как это может сделать ПРОСМОТР.

Вот синтаксис: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

Преимущество XLOOKUP 1: точное соответствие по умолчанию

99% моих формул ВПР заканчиваются на ЛОЖЬ или 0, чтобы указать точное соответствие. Если вы всегда используете версию VLOOKUP с точным соответствием, вы можете начать отключать match_mode от функции XLOOKUP.

На следующем рисунке вы смотрите вверх W25-6 из ячейки A4. Вы хотите найти этот предмет в L8: L35. Когда он будет найден, вам потребуется соответствующая цена из столбца N. Нет необходимости указывать False в качестве match_mode, поскольку по умолчанию XLOOKUP имеет точное совпадение.

XLOOKUP значение в A4. Посмотрите в L8: L35. Верните соответствующую цену из N8: N35.

Преимущество XLOOKUP 2: Results_Array - это ссылка, а не целое число

Подумайте о формуле ВПР, которую вы использовали бы перед XLOOKUP. Третьим аргументом было бы 3, чтобы указать, что вы хотите вернуть третий столбец. Всегда была опасность, что невежественный коллега вставит (или удалит) столбец в вашей таблице. Если в таблице есть дополнительный столбец, ВПР, возвращавшая цену, начнет возвращать описание. Поскольку XLOOKUP указывал на ссылку на ячейку, формула переписывается, чтобы продолжать указывать на цену, которая теперь находится в столбце O.

Старая функция ВПР не сработает, если кто-то вставит новый столбец в таблицу поиска. XLOOKUP продолжает работать.

Преимущество XLOOKUP 3: IFNA встроена как необязательный аргумент

Ужасная ошибка # Н / Д возвращается, если искомое значение не найдено в таблице. В прошлом, чтобы заменить # N / A чем-то другим, вам приходилось использовать IFERROR или IFNA, обернутые вокруг VLOOKUP.

Если элемент не найден, он возвращает # N / A из VLOOKUP или XLOOKUP…

Благодаря предложению Рико на моем канале YouTube, команда Excel добавила необязательный четвертый аргумент для if_not_found. Если вы хотите заменить эти ошибки # N / A нулем, просто добавьте 0 в качестве четвертого аргумента. Или вы можете использовать текст, например «Значение не найдено».

Необязательный четвертый аргумент в XLOOKUP - «если не найден». Поставьте там 0 или «Не найдено».

XLOOKUP Преимущество 4: Нет проблем при просмотре слева от ключевого поля

ВПР не может смотреть слева от ключевого поля, не прибегая к ВПР (A4, ВЫБРАТЬ ((1,2), G7: G34, F7: F34), 2, Ложь). С XLOOKUP нет проблем с размещением Results_array слева от Lookup_array.

С XLOOKUP не возникает проблем с возвратом категории из столбца F при поиске номеров деталей в столбце G. Это всегда было слабым местом ВПР: он не мог смотреть влево.

Преимущество XLOOKUP 5: следующее меньшее или большее совпадение без сортировки

ВПР была возможность искать точное совпадение или просто меньшее значение. Вы можете либо оставить четвертый аргумент вне ВПР, либо изменить False на True. Чтобы это работало, таблица поиска должна быть отсортирована в возрастающей последовательности.

Пример приблизительного совпадения версии ВПР. Любая продажа от 10 тысяч до 20 тысяч дает бонус в размере 12 долларов.

Но функция VLOOKUP не могла вернуть точное совпадение или следующий более крупный элемент. Для этого вам пришлось переключиться на использование MATCH с -1 в качестве match_mode, и вы должны были быть осторожными, чтобы таблица поиска была отсортирована по убыванию.

Необязательный пятый аргумент match_mode XLOOKUP может искать только точное совпадение, равное или просто меньшее, равное или большее. Обратите внимание, что значения в XLOOKUP имеют больше смысла, чем в MATCH:

-1 находит значение равное или чуть меньшее
0 найти точное совпадение
1 находит значение равным или чуть большим.

Но самое удивительное: таблицу поиска не нужно сортировать, и любой match_mode будет работать.

Ниже match_mode -1 найдите следующий меньший элемент.

Пятый аргумент XLOOKUP - Match_Mode. 0 для точного совпадения. Отрицательный используется для точного совпадения или следующего меньшего элемента. Положительный 1 соответствует точному совпадению или следующему большему элементу. 2 для совпадения с подстановочными знаками. Чтобы отразить то, что будет делать ВПР с True в четвертом аргументе, поместите отрицательное значение в качестве аргумента match_mode в XLOOKUP.

Здесь match_mode, равное 1, определяет, какое транспортное средство требуется в зависимости от количества людей в группе. Обратите внимание, что таблица поиска не сортируется по пассажирам, а название транспортного средства находится слева от ключа.

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

В таблице сказано:

Автобус вмещает 64 человека
Автомобиль вмещает 4 человека
Мотоцикл вмещает 1 человека
Tour Van вмещает 12 человек
Фургон вмещает 6 человек.

В качестве бонуса данные сортируются по транспортному средству (в старом решении с использованием MATCH таблица должна быть отсортирована по убыванию по емкости. Также: транспортное средство находится слева от емкости.

XLOOKUP Преимущество 6: Вбок XLOOKUP заменяет HLOOKUP

Lookup_array и results_array могут быть горизонтальными с помощью XLOOKUP, что упрощает замену HLOOKUP.

Здесь справочная таблица горизонтальная. Раньше для этого требовалось HLOOKUP, но XLOOKUP может работать с таблицей, которая идет боком.

Преимущество XLOOKUP 7: поиск последнего совпадения снизу

У меня есть старое видео на YouTube с ответом на вопрос с британской конной фермы. У них был автопарк. Каждый раз, когда автомобиль приходил за топливом или обслуживанием, они записывали автомобиль, дату и пробег в электронную таблицу. Они хотели узнать последний известный пробег для каждого автомобиля. Хотя в эпоху Excel-2017 MAXIFS может решить эту проблему сегодня, решение много лет назад представляло собой загадочную формулу с использованием LOOKUP и делением на ноль.

Сегодня необязательный шестой аргумент XLOOKUP позволяет указать, что поиск должен начинаться с нижней части набора данных.

Найдите последнее совпадение в списке.

Заметка

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

Осторожно

На рисунке выше показано, что существуют режимы поиска с использованием старого двоичного поиска. Джо МакДэйд не советует их использовать. Во-первых, улучшенный алгоритм поиска 2018 года достаточно быстр, поэтому нет значительного выигрыша в скорости. Во-вторых, вы рискуете, что невежественный сотрудник сортирует таблицу поиска и введет неправильные ответы.

Преимущество XLOOKUP 8. Подстановочные знаки по умолчанию "отключены"

Большинство людей не осознавали, что ВПР обрабатывает звездочку, вопросительный знак и тильду как символы подстановки, как описано в «# 51 Использование подстановочного знака в ВПР» на стр. 143. В XLOOKUP подстановочные знаки по умолчанию отключены. Если вы хотите, чтобы XLOOKUP рассматривал эти символы как подстановочные знаки, используйте 2 в качестве Match_Mode.

Очень немногие люди осознавали, что ВПР обрабатывает звездочки в поисковом значении как подстановочные знаки. По умолчанию XLOOKUP не использует подстановочные знаки, но вы можете заставить его вести себя как ВПР, если вы используете режим сопоставления 2: сопоставление подстановочных знаков.

Преимущество XLOOKUP 9: возврат всех 12 месяцев в единую формулу!

Это действительно преимущество динамических массивов, но это моя любимая причина полюбить XLOOKUP. Когда вам нужно вернуть все 12 месяцев в поиске, одна формула, введенная в B6 с прямоугольным return_array, вернет несколько результатов. Эти результаты распространятся на соседние ячейки.

На рисунке ниже одна формула, введенная в B7, возвращает все 12 ответов, показанных в B7: M7.

Один XLOOKUP в столбце «Январь» возвращает числа с января по декабрь. Это делается путем указания result_array с 12 столбцами.

Преимущество XLOOKUP 10: может возвращать ссылку на ячейку, если она смежна с двоеточием

Этот сложный, но красивый. Раньше существовало семь функций, которые изменяли бы от возврата значения ячейки на возврат ссылки на ячейку, если бы функция касалась двоеточия. Для примера см. Использование A2: INDEX () в качестве энергонезависимого смещения. XLOOKUP - это функция восьмерок, которая предлагает такое поведение, присоединяясь к CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET и SWITCH.

Рассмотрим следующий рисунок. Кто-то выберите Cherry в E4 и Fig в E5. Вам нужна формула, которая суммирует все от B6 до B9.

На рисунке показаны две формулы XLOOKUP в двух ячейках. Первый возвращает 15 из ячейки B6. Второй возвращает 30 из B9. Но затем в третьей ячейке есть формула, которая объединяет две формулы XLOOKUP двоеточием, а затем превращает это в функцию СУММ. Результатом является СУММА B6: B9, поскольку XLOOKUP может возвращать ссылку на ячейку, если функция отображается рядом с оператором, например двоеточием. Чтобы доказать, что это работает, на следующих нескольких рисунках будет показана эта формула в диалоговом окне Evaluate Formula.

На рисунке выше вы можете видеть, что XLOOKUP для E4 вернет 15 из ячейки B6. XLOOKUP из E5 вернет 30 из B9. Однако, если вы возьмете две функции XLOOKUP из ячеек D9 и D10 и соедините их с двоеточием между ними, поведение XLOOKUP изменится. Вместо 15, первый XLOOKUP возвращает адрес ячейки B6!

Чтобы доказать это, я выбрал D7 и использовал формулы, вычислить формулу. После двухкратного нажатия кнопки Evaluate следующая вычисляемая часть - это XLOOKUP ("Cherry", A4: A29, B4: B29), как показано здесь.

Это показывает диалоговое окно «Вычислить формулу» непосредственно перед вычислением первого XLOOKUP. Этот XLOOKUP появляется непосредственно перед двоеточием.

Нажмите Evaluate еще раз, и, как ни удивительно, формула XLOOKUP возвращает 6 $ B $ вместо 15, хранящихся в B6. Это происходит потому, что после формулы XLOOKUP стоит двоеточие.

Нажмите «Оценить», и первый XLOOKUP вернет 6 B $ вместо 15.

Нажмите Evaluate еще два раза, и промежуточная формула будет = SUM (B6: B9).

После вычисления второго XLOOKUP промежуточная формула будет = СУММ (B6: B9).

Это удивительное поведение, о котором большинство людей не знает. MVP Excel Чарльз Вильямс сказал мне, что его можно запустить с помощью любого из этих трех операторов рядом с XLOOKUP:

Двоеточие
Пробел (оператор пересечения)
Запятая (оператор союза)

Преимущество XLOOKUP 11: двустороннее сопоставление, например INDEX (, MATCH, MATCH)

Для всех моих друзей по VLOOKUP люди INDEX / MATCH ждали, чтобы увидеть, сможет ли XLOOKUP справиться с двусторонним соответствием. Хорошая новость: он может это сделать. Плохая новость: методология немного отличается от ожидаемой фанатами INDEX / MATCH. Это может быть немного выше их голов. Но я уверен, что они могут прийти к такому методу.

Для двустороннего соответствия вы хотите найти, какая строка содержит номер счета A621, показанный в J3. Итак, XLOOKUP начинается достаточно просто: = XLOOKUP (J3, A5: A15. Но затем вам нужно предоставить массив результатов. Вы можете использовать тот же прием, что и в XLOOKUP. Преимущество 9: вернуть все 12 месяцев в единую формулу выше, но используйте его для возврата вертикального вектора. Внутренний XLOOKUP ищет месяц J4 в заголовках месяцев в B4: G4. return_array указан как B5: G15. В результате внутренний XLOOKUP возвращает массив, подобный показанному в I10. : I20 ниже.Поскольку A621 находится в пятой ячейке массива lookup_array, а 104 - в пятой ячейке массива results_array, вы получите правильный ответ из формулы. Ниже J6 показывает старый способ. J7 возвращает новый способ.

XLookup J3 в списке учетных записей в A5: A15. Для массива результатов используйте XLOOKUP (J4, B4: G4, B5: G15). В этой формуле B4: G4 - это список месяцев. B5: G15 - прямоугольный массив значений для всех счетов за все месяцы. В другой ячейке только внутренний XLOOKUP показывает, как он возвращает весь столбец значений за май.

Преимущество XLOOKUP 12: суммирование всех значений поиска в одной формуле

Древняя функция LOOKUP предлагала две странные уловки. Во-первых, если вы пытаетесь вычислить общую сумму начисленных бонусных расходов, вы можете попросить LOOKUP найти все значения в одной формуле. На изображении ниже ПРОСМОТР (C4: C14 выполняет 11 поисков. Но функция ПРОСМОТР не предлагает точного совпадения и требует сортировки таблицы поиска.

Найдите 13 значений и просуммируйте их. Раньше это работало с LOOKUP, но также работает с XLOOKUP. Задайте все значения поиска C4: C14 в качестве первого аргумента. Оберните XLOOKUP функцией СУММ.

С помощью XLOOKUP вы можете указать диапазон в качестве lookup_value, и XLOOKUP вернет все ответы. Преимущество в том, что XLOOKUP может выполнять точные совпадения.

Уловка использования LOOKUP для суммирования всех результатов поиска работает только с версией поиска с приблизительным соответствием. Здесь XLOOKUP выполняет точное сопоставление всех имен в L4: L14 и получает общую сумму всех результатов.

Дополнительный совет: как насчет скрученного ПРОСМОТРА?

MVP Excel Майк Гирвин часто демонстрирует уловку функции ПРОСМОТР, в которой Lookup_Vector расположен вертикально, а Result_Vector - горизонтально. XLOOKUP изначально не поддерживает этот трюк. Но, если вы немного обманете и оберните массив результатов в функцию TRANSPOSE, вы можете управлять искаженным поиском.

Здесь поисковый массив вертикальный, а массив результатов горизонтальный. Старая функция LOOKUP может справиться с этим, но чтобы сделать это с помощью XLOOKUP, вы должны обернуть любой массив в TRANSPOSE.