Какая формула Excel лучше?

Функции ПРОСМОТР являются одними из наиболее ценных формул в Microsoft Excel. Эти инструменты помогают найти отдельные данные в одном столбце, а затем показать связанные данные из другого столбца.

С течением времени ВПР стал широко используемой и важной формулой поиска. Однако теперь Microsoft Excel выпустила новый инструмент поиска XLOOKUP, разработанный для последних выпусков Office 2021 и Office 365.

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

Что такое ВПР?

Что такое ВПР?

ВПР — это функция Excel, которая помогает найти определенный фрагмент данных в списке. Он может показать вам другую информацию из той же строки, когда найдет нужные вам данные. Люди уже давно используют ВПР в Excel.

ВПР в основном используется для поиска вверх и вниз по списку (поэтому его называют «вертикальным» поиском). Многие используют его, чтобы получить подробную информацию из больших списков или выполнить математические расчеты на основе того, что они находят.

Синтаксис

Для работы функций поиска требуется как минимум три части. Вещи в квадратных коробках — это дополнительные опции, которые вы можете использовать. Вот синтаксис функции ВПР:

=ВПР(искомое_значение, table_array, col_index_num, [range_lookup])

Параметры

• Lookup_value – это то, что вы пытаетесь найти. Он ищет это и показывает соответствующую информацию из другого столбца.
• Table_array — здесь вы указываете инструменту, с чего начать и где прекратить поиск.
• Col_index_num – это все равно, что сказать, в каком столбце содержится нужный вам ответ.
• Range_lookup – здесь можно указать ИСТИНА или ЛОЖЬ. ИСТИНА означает, что вы согласны с почти догадкой. FALSE означает, что вам нужно только то, что нужно.

Что такое XLOOKUP?

Что такое XLOOKUP?

XLOOKUP — это новый инструмент поиска в Excel, который помогает найти определенный фрагмент данных в списке. Когда он находит то, что вы ищете, он показывает вам другие сведения из той же строки или столбца. Это похоже на обновленный ВПР.

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

Синтаксис

=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

Параметры

• Lookup_value – это то, что вы пытаетесь найти.
• Lookup_array — здесь вы указываете инструменту, где ему следует искать.
• Return_array — здесь вы хотите получить подробную информацию о том, как элемент будет найден.
• [if_not_found] – Если вы используете это, вы можете выбрать, что скажет инструмент, если он ничего не найдет. Если вы пропустите его, вы увидите ошибку #Н/Д.
• [match_code] – Вы можете использовать здесь 0, -1, 1 или 2, чтобы указать инструменту, насколько точно вы хотите, чтобы все совпадало.

Разница между ВПР и XLOOKUP

Если присмотреться, вот фундаментальные различия между VLOOKUP и XLOOKUP, подчеркивающие их индивидуальные сильные стороны и области применения.

Прочность и гибкость

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

Но XLOOKUP сложнее. Вы можете добавлять или удалять столбцы, и это по-прежнему работает нормально. Помните: добавление чего-либо за пределы области поиска может привести к ошибке.

Индексный номер столбца

С помощью VLOOKUP вы должны точно знать, какой номер столбца вы просматриваете. Вы часто возвращались к своему основному столу, чтобы еще раз проверить. Правильно указать номер столбца очень важно. Если это неправильно, на вашем листе может появиться ошибка типа «#REF» или дать неверный ответ.

Но с XLOOKUP все проще. Вам больше не нужно считать столбцы! Просто скажите ему, что вы ищете и где искать. Это избавит вас от подсчетов и ошибок, а значит, меньше хлопот.

Пользовательские сообщения об ошибках

Пользовательское сообщение об ошибке

XLOOKUP — это здорово, потому что, если он не может найти то, что вы ищете, вы можете заставить его произнести специальное сообщение, которое вы выбираете с помощью [if_not_found] часть.

ВПР не имеет этой хитрости. Если чего-то не хватает, вам, возможно, придется использовать такие приемы, как ЕСЛИОШИБКА, чтобы обработать это и не показывать ошибки.

Сортировка диапазона поиска

Сортировка искомого значения

При использовании ВПР, если вы выполняете свободный (ИСТИНА) поиск, все должно быть в порядке от меньшего к большему. Если это не так, вы можете получить неправильный ответ.

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

Сортировка данных

VLOOKUP требует, чтобы данные располагались в порядке от меньшего к большему. Но XLOOKUP — это круто, потому что он может обрабатывать данные, даже если они перепутаны.

Совместимость со старыми версиями Excel.

ВПР отлично работает со старыми версиями Excel. Но XLOOKUP — более новый инструмент, поэтому он доступен только в Microsoft 365, 2021 и онлайн-версии. Excel для Интернета. Если вы предоставляете общий доступ к таблице, использующей XLOOKUP, убедитесь, что у другого человека на данный момент есть Microsoft 365.

Направление поиска

Направление поиска VLOOKUP и XLOOKUP

VLOOKUP и XLOOKUP находят вещи по-разному. ВПР перемещается вверх и вниз по списку. Он начинается с первого столбца и движется дальше, пока не найдет то, что хочет. Это может быть немного медленно с большими списками или если что-то перепутано.

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

Позиция возвращаемого значения

У VLOOKUP есть небольшой недостаток. Когда вы запрашиваете данные, он смотрит только на столбцы справа. Если вам нужно что-то слева? Везет, как утопленнику. Это ограничение может усложнить извлечение данных из столбцов слева от них.

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

За и против

Каждая формула Excel имеет свои пределы, даже если в противном случае она делает невероятные вещи. Давайте быстро разберем сильные и слабые стороны каждого из VLOOKUP и XLOOKUP.

Подробнее: Как скрыть столбцы в Excel

Плюсы ВПР

Плюсы ВПР

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

Плюсы XLOOKUP

Плюсы XLOOKUP

• Умные настройки по умолчанию: XLOOKUP по умолчанию начинается с точного совпадения, что делает его более безопасным. Приблизительное совпадение по умолчанию в ВПР иногда может давать неожиданные результаты.
• Гибкость поиска: XLOOKUP не просто осуществляет поиск вверх и вниз; оно тоже идет из стороны в сторону. Нет необходимости в дополнительных функциях, если ваши данные расположены горизонтально.
• Двусторонний поиск: с помощью XLOOKUP вы можете искать в прямом направлении (от первого к последнему) или в обратном направлении (от последнего к первому). Таким образом, поиск самых последних данных, таких как последние цены, становится проще простого.
• Обычная ссылка на столбец: с помощью XLOOKUP вы ссылаетесь на ячейки как обычно, что делает его менее подверженным ошибкам, чем VLOOKUP, если вы меняете таблицу (например, добавляете или удаляете столбцы).
• Приблизительное совпадение: XLOOKUP может найти точное совпадение, ближайшее меньшее значение или точное совпадение или ближайшее большее значение. Кроме того, ваши данные не обязательно должны быть в порядке.
• Встроенная обработка ошибок: XLOOKUP имеет собственный аргумент if_not_found. Таким образом, вы можете точно сказать ему, что делать или говорить, если он не может найти совпадение. Нет необходимости в дополнительных функциях, таких как ЕСЛИОШИБКА.

Минусы ВПР

• Сообщения об ошибках: отсутствует возможность отображать пользовательские сообщения об ошибках.
• Направление поиска: поиск осуществляется исключительно сверху вниз, поиск по горизонтали невозможен.
• Возврат одного значения: возвращает только одно значение.
• Зависимость столбца: при поиске сильно зависит от первого столбца таблицы.
• Приблизительный поиск: округление не производится, что может привести к неправильным результатам, если его не настроить тщательно.
• Чувствительность настройки: незначительные неправильные настройки могут привести к ошибкам типа «#REF».

Минусы XLOOKUP

• Совместимость: недоступно в старых версиях Excel, что ограничивает его использование на старых платформах.
• Сортировка данных: без правильной сортировки данных могут быть возвращены неправильные значения.
• Бинарный поиск. Хотя этот метод может использовать бинарный поиск, он имеет свой собственный набор ограничений и может подходить не для всех наборов данных.

Вот изящная сравнительная таблица, показывающая различия между плюсами и минусами между ними:

Функция/АспектVLOOKUPXLOOKUPИнтуитивное управлениеИспользует простой метод, проверяя первый столбец таблицы.Использует интеллектуальные настройки по умолчанию и может осуществлять поиск как по вертикали, так и по горизонтали.Направление поискаИсключительно вертикальное (сверху вниз).И вертикальное, и горизонтальное, с опциями для прямого и обратного направления.Ошибка СообщенияНевозможно отображать пользовательские сообщения об ошибках. Можно отображать пользовательские сообщения об ошибках с помощью [if_not_found] аргумент. Зависимость столбца для поиска опирается на первый столбец. Может выполнять поиск по любому столбцу в предоставленном наборе данных. Требование к сортировке данных. Для приблизительного поиска данные нужны в порядке от наименьшего к наибольшему. Может обрабатывать данные, даже если они не в порядке, но есть особые требования для двоичных файлов. Поиск. СовместимостьРаботает со старыми версиями Excel. Эксклюзивно для Microsoft 365, Excel 2021 и Excel для Интернета. Справочник по столбцам Требуется ручной ввод индексных номеров столбцов. Обычно ссылается на ячейки, уменьшая количество ошибок при изменении структур таблицы. Гибкость поискаОграничена вертикальным поиском. Может выполнять двусторонний поиск, что делает его универсальным для различных наборов данных. Пользовательская обработка ошибок. Требуются такие функции, как ЕСЛИОШИБКА, для обработки пропущенных значений. Встроенная обработка ошибок с помощью аргумента «if_not_found».

Почему XLOOKUP лучше, чем VLOOKUP?

Почему XLOOKUP лучше, чем VLOOKUP?

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

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

Последние мысли

В заключение отметим, что переход от VLOOKUP к XLOOKUP в Microsoft Excel знаменует собой значительный прогресс в поиске и извлечении данных.

XLOOKUP устраняет многие недостатки VLOOKUP, предлагая универсальность двустороннего поиска, настраиваемые сообщения об ошибках и гибкость выбора столбца или строки. Он упрощает извлечение данных из больших наборов данных и обеспечивает улучшенную обработку ошибок.

Однако важно отметить его ограниченную совместимость со старыми версиями Excel. Хотя XLOOKUP остается основополагающим во многих электронных таблицах, XLOOKUP становится более динамичным, ориентированным на пользователя инструментом для тех, кто использует Microsoft 365 или Excel в Интернете.

У обоих есть сильные стороны, но XLOOKUP устанавливает новый стандарт возможностей анализа данных Excel.