Быстрые ссылки
В Excel есть множество способов найти и извлечь данные из таблицы или диапазона по искомому значению. На самом деле, поскольку Microsoft постоянно выпускает новые, улучшенные альтернативы, вариантов стало даже слишком много! Поэтому я расскажу о трех функциях, которыми пользуюсь чаще всего.
Все примеры в этой статье работают с отформатированными таблицами Excel, поскольку такая структура данных дает массу преимуществ. Поэтому в формулах используются структурированные ссылки на таблицы. Если будете применять эти функции для поиска в обычных диапазонах, просто используйте прямые ссылки на ячейки.
XLOOKUP: Современная замена функций VLOOKUP и HLOOKUP
Функция XLOOKUP — безусловно, королева всех функций поиска в Excel. Удивительно, что Microsoft понадобилось больше 30 лет, чтобы её создать!
Она возвращает один или несколько элементов из диапазона или массива по первому или последнему найденному совпадению. Поскольку работает вертикально и горизонтально, ищет данные слева, справа, сверху или снизу и может вернуть целый столбец или строку, она полностью заменяет своих предшественников — VLOOKUP и HLOOKUP. Также можно задать, какое значение вернуть, если совпадений не найдено.
Правда, если у вас Excel старше 2021 года, функция XLOOKUP недоступна.
Microsoft 365 Personal
Microsoft 365 включает доступ к приложениям Office, таким как Word, Excel и PowerPoint, на пяти устройствах, 1 ТБ хранилища OneDrive и многое другое.
Синтаксис функции XLOOKUP
У XLOOKUP шесть аргументов:
где
Аргументы a и c должны иметь одинаковую высоту для вертикального поиска или одинаковую ширину для горизонтального.
Функция XLOOKUP в действии
XLOOKUP может вернуть одно значение по одному критерию. В этом случае формула в ячейке H3 возвращает очки игрока по ID из ячейки H1:
Здесь не нужно вводить аргументы e (режим совпадения) или f (режим поиска), поскольку настройки по умолчанию (точное совпадение и поиск сверху вниз) — именно то, что нужно.
Используйте проверку данных Excel, чтобы создать выпадающий список вариантов для поиска. Тогда не придется вводить сообщение об отсутствии совпадения в аргументе d.
XLOOKUP также можно использовать для возврата результатов из нескольких соседних ячеек. Здесь формула в ячейке G4 возвращает пол, страну, команду и очки игрока при вводе ID, где возвращаемое значение (аргумент c) включает первый столбец, затем двоеточие, затем последний:
Когда возвращаемый массив (аргумент c) состоит более чем из одной ячейки, как в примере выше, XLOOKUP превращается в функцию динамического массива. Это значит, что результат «переливается» из ячейки с формулой в соседние ячейки, поэтому нужно убедиться, что они свободны. Иначе получите ошибку #SPILL!
Чтобы использовать XLOOKUP для возврата значений из несоседних столбцов или строк, нужно вложить функцию FILTER в аргумент возвращаемого массива, указав нули и единицы в фигурных скобках — они покажут Excel, какие столбцы возвращать. Эта формула ищет ID в ячейке H1 и возвращает соответствующие Страну (третий столбец) и Очки (пятый столбец):
Поскольку эта формула использует номера индексов столбцов, при добавлении или удалении столбцов из таблицы её придется скорректировать для выбора правильных возвращаемых массивов.
Наконец, можно объединить XLOOKUP с логическими операторами для возврата значения по нескольким критериям. Эта формула проверяет, истинен ли каждый критерий в массиве поиска (аргумент b), возвращая 1 для истины или 0 для лжи. Первое совпадение возвращается, если все критерии истинны (то есть результат вычисления массива поиска равен искомому значению (1)):
Поскольку столбец Очки отсортирован по убыванию, результатом будет ID игрока-женщины из Канады с наивысшим счетом.
Чтобы найти канадскую спортсменку с наименьшими очками, измените порядок сортировки столбца Очки на возрастающий или введите -1 в режим поиска (аргумент f).
Плюсы и минусы XLOOKUP
Вот преимущества и недостатки этой полезной функции:
Преимущества XLOOKUP
Недостатки XLOOKUP
Работает с вертикальными и горизонтальными наборами данных.
Не работает в версиях Excel старше 2021 года.
Возвращаемый массив может быть слева, справа, сверху или снизу от массива поиска.
Возвращает только одно совпадение (первое или последнее).
Может вернуть один результат или динамический массив.
Не может вернуть несоседние столбцы или строки без использования функции FILTER.
Работает с одним или несколькими значениями поиска.
Нельзя использовать в отформатированной таблице Excel при возврате динамического массива.
Гибкие типы совпадений и режимы поиска.
Позволяет обрабатывать ошибки изначально.
Поддерживает поиск по шаблонам для частичных совпадений.
Может быть вложена для выполнения двусторонних поисков.
INDEX с XMATCH: Более мощная альтернатива INDEX с MATCH
Многие пользователи Excel с многолетним стажем до сих пор выполняют поиск старой связкой INDEX-MATCH, в первую очередь потому, что она гибче, чем VLOOKUP и XLOOKUP. Однако объединение INDEX с XMATCH, обновленной версией MATCH, дает еще больше возможностей.
INDEX с XMATCH возвращает элемент из диапазона или массива по первому или последнему найденному совпадению. Как и XLOOKUP, работает с вертикальными и горизонтальными наборами данных, может искать в любом направлении и возвращать целые столбцы или строки.
Однако, поскольку XMATCH новее MATCH, она доступна только тем, кто использует Excel для веб или настольные версии Excel 2021 года или новее.
Эти две функции обычно используются вместе, потому что INDEX определяет столбец поиска, а XMATCH — строку поиска.
Синтаксис INDEX-MATCH
Поскольку я покажу, как объединить эти функции в поиске, вот общий синтаксис для вертикального (самого распространенного) поиска:
Другими словами, аргументы XMATCH (b, c, d и e) указывают функции INDEX, в какой строке массива или таблицы искать, а последний аргумент INDEX (f) определяет столбец. Вместе они указывают Excel, в какой ячейке искать нужное значение.
Для горизонтального поиска нужно ввести номер строки перед использованием XMATCH для определения столбца.
Связка функций INDEX-XMATCH в действии
Можно использовать INDEX с XMATCH для возврата одного значения по одному критерию. В этом примере пара используется для возврата очков (столбец 5 в таблице Игроки) игрока, чей ID находится в ячейке H1:
Обратите внимание: хотя синтаксис поначалу кажется сложным, если нужно вернуть точное совпадение в поиске сверху вниз, можно опустить два аргумента.
Если совпадения нет, Excel возвращает ошибку #N/A. Чтобы этого избежать, используйте проверку данных для создания выпадающего списка вариантов поиска. Или заключите всю формулу в функцию IFERROR:
Проблема примеров выше в том, что номер столбца жестко прописан в формуле. Вместо этого можно встроить второй набор аргументов XMATCH для возврата этого значения через двусторонний поиск:
Здесь номер столбца определяется сопоставлением переменной в G3 с заголовками столбцов в таблице Игроки.
Также можно использовать INDEX с XMATCH для возврата значения по нескольким критериям, объединив их с логическими операторами. Отсортировав столбец E по убыванию, я могу использовать эту формулу для возврата ID игрока-женщины с наивысшими очками в команде C:
Чтобы найти игрока-женщину с наименьшими очками в команде C, измените порядок сортировки столбца E на возрастающий или введите -1 в режим поиска (аргумент e).
Плюсы и минусы INDEX с XMATCH
Если всё ещё сомневаетесь, подходят ли вам эти функции, вот краткое резюме их плюсов и минусов:
Преимущества INDEX/XMATCH
Недостатки INDEX/XMATCH
Работает с вертикальными и горизонтальными наборами данных.
Не работает в версиях Excel старше 2021 года и недоступна в мобильном приложении Excel.
Возвращаемый массив может быть слева, справа, сверху или снизу от массива поиска.
Возвращает только один результат.
Работает с одним или несколькими значениями поиска.
Возвращает только одно совпадение (первое или последнее).
Гибкие типы совпадений и режимы поиска.
Не позволяет указать альтернативное значение при отсутствии совпадений без использования функции IFERROR.
XMATCH можно вкладывать для выполнения двусторонних поисков.
Использование двух функций одновременно означает более крутую кривую обучения, чем поиск с одной функцией.
Значения по умолчанию для необязательных аргументов более логичны в XMATCH, чем в MATCH.
Не возвращает динамический массив, поэтому можно использовать в таблицах Excel.
Поддерживает поиск по шаблонам для частичных совпадений.
FILTER: Простая функция, которая возвращает все подходящие значения
В то время как XLOOKUP и INDEX с XMATCH возвращают одно совпадение, функция FILTER в Excel возвращает все совпадения — весомая причина выбрать именно её. Правда, только пользователи Excel 2021 или новее, Excel для веб или мобильного приложения Excel могут использовать эту функцию.
Синтаксис функции FILTER
Вот как работает функция FILTER:
где
Аргументы a и b должны ссылаться на массивы одинакового размера.
Функция FILTER в действии
Можно использовать функцию FILTER для возврата массива из столбца по одному критерию. Эта формула, введенная в ячейку H3, возвращает ID всех игроков, чья страна соответствует значению в ячейке H1, выдавая «Нет совпадений» вместо ошибки при отсутствии результатов:
Отфильтрованные значения возвращаются в том порядке, в каком они появляются в исходных данных.
FILTER — это функция динамического массива. Это значит, что результат «переливается» из ячейки с формулой в соседние ячейки. Поэтому нужно убедиться, что эти ячейки свободны — иначе получите ошибку #SPILL!
В примере выше в качестве возвращаемого массива был выбран только столбец ID таблицы Игроки. Однако также можно использовать FILTER для возврата соответствующих данных из всех столбцов. После ручного ввода заголовков столбцов в ячейки G3-K3, в ячейке G4 я ввел:
где Игроки (аргумент a) — это имя таблицы, означающее, что все столбцы возвращаются в отфильтрованном результате.
Существует несколько способов возврата данных из несоседних столбцов, но самый простой — повторить функцию FILTER для каждого столбца, который нужно вернуть.
Наконец, можно использовать FILTER для возврата значений, соответствующих нескольким условиям, используя логические операторы. Эта формула возвращает данные для всех женщин в команде C:
Используйте проверку данных Excel для создания выпадающего списка вариантов фильтров. Тогда не понадобится вводить сообщение об отсутствии совпадений в аргументе c.
Плюсы и минусы FILTER
Вот обзор преимуществ и недостатков функции FILTER в Excel:
Преимущества FILTER
Недостатки FILTER
Возвращает все подходящие значения.
Не работает в версиях Excel старше 2021 года.
Возвращаемый массив может быть слева, справа, сверху или снизу от фильтруемого массива.
Возвращает динамический массив, что означает невозможность использования в таблице Excel.
Работает с одним или несколькими критериями фильтрации.
Возвращает ноль, если исходная ячейка пустая или содержит null.
Имеет простой синтаксис, поскольку не требует типов поиска, совпадений или режимов.
Работает с вертикальными и горизонтальными наборами данных.
Позволяет указать значение при отсутствии совпадений.
Кстати, функции CHOOSECOLS и CHOOSEROWS в Excel формально не являются функциями поиска, но они идеальны, если нужно быстро извлечь определенные столбцы или строки из данных.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru