Чем заменить впр

Исправление ошибки #Н/Д в функции ВПР

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel для Windows Phone 10 Еще. Меньше

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

Совет: Кроме того, ознакомьтесь с материалом Краткая справочная карточка: советы по устранению неполадок функции ВПР. На ней указаны основные причины получения результата #Н/Д. Сведения приводятся в удобном формате PDF. Файл в формате PDF можно распечатать или предоставить другим пользователям.

Проблема: искомое значение не находится в первом столбце аргумента таблица

Одно из ограничений функции ВПР состоит в том, что она можно искать значения только в крайнем левом столбце таблицы. Если искомое значение находится не в первом столбце массива, появится ошибка #НД.

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

Ошибка #Н/Д #N/A возникает, поскольку значение поиска «Капуста» находится во втором столбце (Продукты) аргумента таблица A2:C10. В этом случае Excel ищет значение в столбце A, а не в столбце B.

Решение. Чтобы исправить ошибку, измените ссылку ВПР так, чтобы она указывала на правильный столбец. Если это невозможно, попробуйте переместить столбцы. Это тоже может быть весьма неудобно при использовании больших или сложных таблиц, где значения в ячейках получены в результате других вычислений (или невозможно перемещать столбцы по другим причинам). В таком случае можно использовать сочетание функций ИНДЕКС и ПОИСКПОЗ, которые позволяют находить значения в любом столбце вне зависимости от его позиции в таблице подстановки. (см. следующий раздел).

Попробуйте использовать функции ИНДЕКС и ПОИСКПОЗ

Функции ИНДЕКС и ПОИСКПОЗ можно эффективно применять во многих случаях, когда функция ВПР не позволяет получить нужные результаты. Основное их преимущество заключается в том, что значения можно искать в столбце таблицы в любой позиции в таблице подстановки. Функция ИНДЕКС возвращает значение из указанной таблицы или диапазона в соответствии с его позицией. Функция ПОИСКПОЗ возвращает относительную позицию значения в диапазоне или таблице. Используя функции ИНДЕКС и ПОИСКПОЗ вместе, можно находить значение в таблице или в массиве, указав относительную позицию значения.

Существует несколько преимуществ использования функций ИНДЕКС и ПОИСКПОЗ вместо ВПР.

• При использовании функций ИНДЕКС и ПОИСКПОЗ возвращаемое значение не обязательно должно находиться в том же столбце, что и столбец подстановки. При использовании функции ВПР возвращаемое значение, напротив, должно быть в указанном диапазоне. Почему это важно? При использовании функции ВПР вам нужно знать номер столбца, содержащего значение. Это может показаться не слишком сложным, но это всерьез затрудняет работу, если используется большая таблица, в которой нужно подсчитать количество столбцов. Кроме того, если добавить или удалить столбец. придется пересчитать столбцы и изменить значение аргумента номер_столбца. При использовании функций ИНДЕКС и ПОИСКПОЗ не нужно подсчитывать столбцы.
• При использовании функций ИНДЕКС и ПОИСКПОЗ можно указать строку или столбец (или и строку, и столбец) в массиве. Это означает, что значения можно искать по вертикали и по горизонтали.
• С помощью функций ИНДЕКС и ПОИСКПОЗ можно находить значения в любом столбце. В отличие от функции ВПР, которая находит только значения в первом столбце таблицы, функции ИНДЕКС и ПОИСКПОЗ будут работать вне зависимости от того, в каком столбце находится значение.
• Это позволяет использовать динамические ссылки на столбец, содержащий возвращаемое значение.Таким образом, эти функции будут работать, даже если вы добавлять столбцы в таблицу. С другой стороны, ВПР не сможет найти значение, если добавить столбец в таблицу, поскольку эта функция использует статическую ссылку на таблицу.
• Функции ИНДЕКС и ПОИСКПОЗ обеспечивают более гибкие возможности поиска.Они могут находить точное совпадение, а также значение больше или меньше искомого. ВПР ищет только наиболее близкое (по умолчанию) или точное значение. Кроме того, функция ВПР предполагает, что первый столбец в таблице отсортирован в алфавитном порядке, и возвращает первое наиболее близкое совпадение, поэтому вы можете получить не те данные, которые ожидали.

Синтаксис

Чтобы создать синтаксис для функций ИНДЕКС или ПОИСКПОЗ, необходимо вложить синтаксис функции ПОИСКПОЗ в аргумент массива или ссылки функции ИНДЕКС. Это выглядит следующим образом:

=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;[тип_совпадения])

Заменим функцию ВПР в приведенном выше примере функциями ИНДЕКС и ПОИСКПОЗ. Синтаксис будет выглядеть следующим образом:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(B13;B2:B10;0))

=ИНДЕКС(возвратить значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).

Проблема: не найдено точное совпадение

Если для аргумента диапазон_поиска задано значение ЛОЖЬ, а функции ВПР не удается найти точное совпадение, возвращается ошибка #Н/Д.

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

Также можно использовать функции ПЕЧСИМВ или СЖПРОБЕЛЫ для очистки данных в ячейках.

Проблема: искомое значение меньше, чем наименьшее значение в массиве

Если для аргумента диапазон_поиска задано значение ИСТИНА, а искомое значение меньше наименьшего значения в массиве, возвращается ошибка #Н/Д. Функция ищет приблизительное совпадение в массиве и возвращает ближайшее значение, которое меньше искомого.

В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.

• Исправьте искомое значение.
• Если невозможно изменить искомое значение, а при подстановке значений требуется более высокая гибкость, попробуйте использовать функции ИНДЕКС и ПОИСКПОЗ вместо ВПР — см. раздел выше в этой статье. Они позволяют находить значения больше или меньше искомого, а также равные ему. Дополнительные сведения см. в предыдущем разделе этой статьи.

Проблема: столбец подстановки не отсортирован в порядке возрастания

Если для аргумента диапазон_поиска задано значение ИСТИНА, но один из столбцов не отсортирован по возрастанию (от А до Я), возвращается ошибка #Н/Д.

• Измените функцию ВПР так, чтобы искать точное совпадение. Для этого укажите для аргумента диапазон_поиска значение ЛОЖЬ. Для значения ЛОЖЬ сортировка не требуется.
• Для поиска значения в несортированной таблице можно также использовать функции ИНДЕКС и ПОИСКПОЗ.

Проблема: значение является большим числом с плавающей запятой

При наличии в ячейках значений времени или больших десятичных чисел Excel возвращает ошибку «#Н/Д» из-за точности чисел с плавающей запятой. Числа с плавающей запятой включают цифры после десятичной запятой. (Значения времени хранятся в Excel в виде чисел с плавающей запятой.) Excel не может хранить крупные числа с плавающей запятой, поэтому для правильной работы функции такие числа нужно округлять до 5 десятичных разрядов.

Решение. Округлите числа до 5 десятичных разрядов с помощью функции ОКРУГЛ.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

См. также

• Исправление ошибки #Н/Д
• ВПР: как избавиться от ошибок #Н/Д
• Арифметические операции со значениями с плавающей запятой могут выдавать неточные результаты в Excel
• Краткий справочник: функция ВПР
• Функция ВПР
• Полные сведения о формулах в Excel
• Рекомендации, позволяющие избежать появления неработающих формул
• Поиск ошибок в формулах
• Все функции Excel (по алфавиту)
• Функции Excel (по категориям)

Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

При работе с формулой ВПР в Excel может сложиться ситуация, когда получить данные нужно не из столбца справа от ключевого столбца, а слева. ВПР так делать не умеет, в таких случаях к нам приходит на помощь сцепка из двух функций СМЕЩ и ПОИСКПОЗ, которые мы сегодня и изучим.

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

Как вы видите, мы могли бы по фамилии вытащить год рождения при помощи функции ВПР, но год рождения стоит слева от столбца Фамилия, конечно можно поменять их местами, но в реальной жизни такое решение не всегда возможно, потому будет решать такую задачу. (Кстати, если вы еще не знакомы с функцией ВПР или неуверенно ею пользуетесь, ознакомьтесь со статьей на нашем сайте: «Функция ВПР в Excel»). Для ее решение нам будут нужны функции СМЕЩ и ПОИСКПОЗ. Разберем их синтаксис:

СМЕЩ(ссылка;смещение по строкам;смещение по столбцам)

Ссылка — ячейка, от которой рассчитывается смещение по строкам и столбцам.

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

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

К примеру для таблицы в скриншоте сверху функция СМЕЩ(C3;-1;1) вернет значение Петрович. Почему именно так? В качестве стартовой ячейки мы указали ячейку С3 (в ней содержится имя Иван). Относительно этой ячейки мы сдвигаемся на одну строку вверх (второй параметр -1) и на один столбец вправо (третий параметр 1).

ПОИСКПОЗ(искомое значение; просматриваемый массив; тип сопоставления)

Искомое значение — это то значение, что мы ищем в таблице

Просматриваемый массив — таблица, где мы ищем искомое значение

Тип сопоставления — как будет искать Excel это значение. Используйте значение 0 для поиска точного результата.

К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ(«Петров»;B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.

Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.

Итак, для нашего примера формула будет следующая:

=СМЕЩ(БД!$B$1;ПОИСКПОЗ(B2;БД!$B$1:$B$6;0)-1;-1)

Сначала мы используем формулу СМЕЩ, так как мы будет считать смещение относительно конкретной ячейки. Первый параметр — ссылка на заголовок столбца «Фамилия» на листе с базой данных, далее нам нужно узнать на сколько строк нам надо сдвинуться, для этого мы используем функцию ПОИСКПОЗ. В ней в качестве первого параметра указываем фамилию, которую ищем, вторым параметром наш столбец с фамилиями в листе с базой данных. Из полученного значения мы вычитаем 1, что бы убрать эффект заголовка столбца. Последний параметр — смещение на один столбец влево, т.е. -1.

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

Надеюсь, статья была для вас полезной. Спасибо за внимание.

Функция ПРОСМОТРX (XLOOKUP) вместо ВПР, ГПР и других функций в Excel

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Функция ВПР (VLOOKUP) скоро отпразднует юбилей 10 лет, что для временного цикла программного обеспечения сопоставимо с выходом на пенсию.

Перед разработчиками из Microsoft стояла задача подобрать достойную замену, и они не ударили в грязь лицом, выпустив функцию ПРОСМОТРX (XLOOKUP), которая представляет собой не только улучшенную версию ВПР, но ещё и замену ГПР, ИНДЕКС и ПОИСКПОЗ. Если хотите узнать о других полезных функциях Excel, то рекомендуем записаться на бесплатный онлайн-курс «Аналитика в Excel».

Пару слов про функцию ПРОСМОТРX

Прежде чем приступать к работе с функцией ПРОСМОТРХ, нужно учесть 3 момента:

1. Версию Excel: на данный момент, осень 2020 года, ПРОСМОТРX доступен только пользователям Office 365, хотя поговаривают, что эту брешь залатают в Standalone-версии Microsoft Office 2021.

2. Буква Х в названии функции – английская, поэтому велика вероятность ошибки при дотошном вводе; лучше всего использовать автозавершение ввода функций с помощью клавиши Tab.

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

Про атрибуты и возможности функции мы поговорим уже в боевых условиях, разбираясь с тем, что же всё-таки умеет делать ПРОСМОТРХ.

Левый ВПР? Я могу орудовать, где угодно!

Наверняка большинство из вас знает, что ВПР выдаёт ошибку, когда нужно забрать данные левее искомой ячейки. Для ПРОСМОТРХ это не проблема, ведь функция считает везде, где только заблагорассудится.

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

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

«Загвоздка?!» – удивляется ПРОСМОТРХ. «Да ещё чего!».

Специально используем «Мастер функций» комбинацией Shift+F3 (или кнопочка Fx слева от строки ввода формул), чтобы показать аргументы.

Искомое значение – это ячейка А2, то есть Gary Miller, с него мы начнём выводить бонусы.

=ПРОСМОТРX(A2;Лист2!$C$2:$C$26;Лист2!$B$2:$B$26;0;0)

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

Возвращаемый массив – тот самый столбец бонусов, которые мы хотим достать. Тоже забираем его со второго листа, зафиксировав с помощью F4.

Если ничего не найдено – уникальный и необязательный аргумент, позволяющий задать любое значение при неудачных результатах поиска, например, пробел (“ “) или 0, можно пропустить.

Режим сопоставления – классическая указка Excel для поиска точных или приблизительных значения. В данном случае выбираем 0 – точный поиск.

Протягиваем результат в ячейке ниже и сразу обращаем внимание на 0 – это те сотрудники, которых ПРОСМОТРХ не обнаружил в списке.

Кстати, если изначально функция везде выдаёт вам нули, то нужно проверить формат ячеек и проставить там числовой

Чтобы посчитать годовой оклад, можно умножить наши результаты на показатели столбца В, то есть дописываем в формулу *B2:

=ПРОСМОТРX(A2;Лист2!$C$2:$C$26;Лист2!$B$2:$B$26;0;0)*B2

Протягиваем и получаем максимальный бонус сотрудников.

Иди домой, ГПР!

Раз мы сказали о том, что ПРОСМОТРХ умеет работать в любых направлениях, то и с заменой ГПР, которая считает по строкам, тоже проблем не будет.

Заберём аналогичные данные для бонуса по сотрудникам, как делали ранее, но теперь обратимся к горизонтально развёрнутой табличке на другом листе. Естественно, строка с бонусом находится выше строки с сотрудниками, что в случае с обычным ГПР нам принесло бы ошибку. Сейчас же мы действуем по накатанной схеме:

=ПРОСМОТРX(A2;Лист3!$B$3:$Z$3;Лист3!$B$2:$Z$2;0;0)

Указываем ячейку с сотрудником. Затем выбираем диапазон с сотрудниками, в котором ПРОСМОТРХ находит искомое значение. Фиксируем по нажатию F4.

Далее указываем строку с бонусами, тоже фиксируем через F4.

Если ничего не найдено, ставим 0.

Точность поиска – тоже 0, точный поиск.

Получаем идентичный результат, который мы можем с чистой совестью перемножить на годовое жалованье сотрудников. Добавляем к формуле *B2:

=ПРОСМОТРX(A2;Лист3!$B$3:$Z$3;Лист3!$B$2:$Z$2;0;0)*B2

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

Кажется, на нём были очки…

Представим, что необходимо достать все реквизиты сотрудника, но мы знаем лишь его фамилию (или какие-то другие отличительные знаки). Попробуем извлечь данные человека по фамилии Willard в отдельной ячейке.

Если мы начнём поиск с ячейки, содержащей одну только фамилию Willard, выберем столбец с сотрудниками в качестве искомого и возвращаемого массива, то… получим ошибку. В идеале наша формула должна выглядеть следующим образом, смотрите:

В первый аргумент, перед искомой ячейкой G2, дописываем “*”. Звёздочка – это служебный символ, которого мы сцепляем амперсандом (&) с ячейкой G2 и таким образом говорим программе, чтобы осуществлялся поиск не только Willard, но и всего остального содержимого ячейки до Willard. То есть первый аргумент у нас примет вид: “*”&G2

Второй аргумент – искомый массив, то есть столбец с именами: A2:A19

Третий аргумент – возвращаемый массив, то есть опять столбец с именами: A2:A19

Четвёртый аргумент – пропускаем

Пятый аргумент – крайне важный в нашем случае. Сейчас мы должны выбрать цифру 2, которая учитывает поиск по служебным символам, то есть обращает внимание на введённую звёздочку в первом аргументе

=ПРОСМОТРX("*"&G2;A2:A19;A2:A19;;2)

Вот мы и получили James Willard.

«Двойной ПРОСМОТРХ» или «Зависимые выпадающие списки»

Для создания зависимых выпадающих списков, то есть, когда по значению из одного списка открываются соответствующие значения другого, больше не нужно хитрых комбинаций с формулами. Хватит ПРОСМОТРХ и парочки списков.

Сперва добавим в нашу таблицу новый столбец под названием «Итоговый платёж», в котором будет осуществляться суммирование столбцов B и C.

Теперь добавим выпадающий список в ячейку G7.

Вкладка «Данные» – «Проверка данных». Тип данных – список.

В поле «Источник» указываем диапазон с нашими сотрудниками из столбца А. Жмём ОК.

Проверяем. Список работает.

Ниже, в ячейке G8, создаём второй выпадающий список, вот только в поле «Источник» указываем заголовки столбцов из шапки таблицы, то есть протягиваем строку от Годового оклада до Итогового платежа. Нажимаем ОК.

Тоже работает. Едем дальше.

Всё готово для встраивания двойного ПРОСМОТРХ.

Переходим в ячейку G9, хотя вы можете выбрать абсолютно любую ячейку.

Начинаем вводить формулу.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Мы ищем имя из выпадающего списка выше, то есть ячейка G7 – это первый аргумент.

Затем выбираем диапазон сотрудников – это второй аргумент.

В качестве возвращаемого массива выбираем все остальные столбцы, потому что нам понадобятся все данные. И здесь – внимание!

На место третьего аргумента первой функции ПРОСМОТРХ мы пишем ещё один ПРОСМОТРХ. Уже в нём указываем первым аргументом ячейку F8 (там перебираются заголовки столбцов).

Второй аргумент – это шапка таблицы.

Третий аргумент – уже обозначенный диапазон всех столбцов, кроме сотрудников (разумеется, без шапки).

Обойдёмся без поразительной точности и закрываем формулу.

Готово. Теперь, в зависимости от выбранного сотрудника в первом списке, переключаются его показатели в различных столбцах из второго. Шикарно!

Как насчёт подсветки выбираемых имён?

В качестве бонуса можете прикрутить сюда условное форматирование, чтобы оно подсвечивало строку по выбранному имени. Мы же смотрим отчётность, это было бы крайне удобно!

Если мы должны найти строку по определённому показателю, то, естественно, выбор падёт на функцию ПОИСКПОЗ. Искать мы будем позицию по имени сотрудника в ячейке F7 и подсвечивать ту строку таблицы, в которой это имя/фамилия находится. Получится очень эффектно.

Обводим всю нашу таблицу. Переходим на вкладку «Главная», потом «Условное форматирование», затем «Создать правило».

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

Сперва зададим формат. Зальём каким-нибудь цветом. Окрасим шрифт. Должно выйти неплохо. Теперь переходим к прописыванию формулы.

=ПОИСКПОЗ($A1;$F$7;0)

Обратите внимание, что в первом аргументе (искомое значение) мы фиксируем только столбец, поскольку нам нужно, чтобы функция перемещалась только по строкам.

$F$7 – наша зафиксированная ячейка с именем из выпадающего списка.

0 – просматриваемый массив, то есть ПОИСКПОЗ находит первое значение, равное искомому.

Нажимаем ОК и ОК далее.

Переключаем имена и любуемся подсветкой.

ПРОСМОТРХ вместо задания условий

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

Если зарплата равна 10 000 – никакого бонуса, 30 000 – 5% бонусом, 50 000 – 8% бонусом и так далее. Определим, кому какой бонус полагается с помощью ПРОСМОТРХ.

Создадим новый столбец «Новый бонус» на основном листе.

Пишем в первой ячейке нового столбца формулу:

Первым аргументом выбираем ячейку В2 из столбца с окладами сотрудников.

В качестве второго аргумента указываем столбец с жалованием из бонусной таблички с другого листа, то есть у нас будет Лист2!F13:$F$17.

Возвращаемый массив – уже бонусный проценты, то есть бонусный столбец из бонусной таблички с другого листа – Лист2!G13:$G$17.

Пропускаем аргумент, если мы ничего не находим, сейчас это необязательно.

И самое главное – пятый аргумент, который отвечает за точность поиска. Указываем там -1, то есть точное совпадение или следующее меньшее значение.

=ПРОСМОТРX(B2;Лист2!$F$13:$F$17;Лист2!$G$13:$G$17;;-1)

Теперь нужно проверить результаты.

Гари Миллеру досталось 10% бонусов. Идём на бонусную табличку и смотрим.

Его оклад составляет 60 000 – это 10%. Следующий оклад для бонусов уже 100 000 и 15% соответственно. Что сделал Excel: он нашёл 60 000 и затем отобрал следующее минимальное значение, то есть 10%.

Никаких условий и никаких подборов. Одна только функция ПРОСМОТРХ помогла нам решить целую вереницу задач.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Microsoft представила замену ВПР, ГПР а также ПОИСКПОЗ

Введение динамических массивов, по сути историческое обновление, которые в корне изменяет подход к построению формул, в чем то изменяет мышление работы в Excel.

С введением динамических массивов было вполне очевидно, что анонсом семи новых функций Microsoft не ограничится. В августе 2019, почти год после анонса масштабного обновления (сентябрь 2018), Microsoft представила замену одной из самых популярных функций в Excel.

Да, речь идет об ВПР или VLOOKUP в английской версии, попутно обновилась и ГПР (HLOOKUP, соответственно). На самом деле новая функция ПРОСМОТРХ (XLOOKUP) просто заменяет их обе. Кстати, в конце стоит именно латинский символ «X» (экс), а не русская «Х» (ха), что доставляет неудобств, если вы пользуетесь русской версией электронного процессора.

А вот для функции ПОИСКПОЗ (MATCH) была добавлена своя функция ПОИСКПОЗX XMATCH, которая, использует возможности динамических массивов, как и XLOOKUP.

Представление ПРОСМОТРХ (XLOOKUP)

Наименование функции намекает, на то, что она будет являться заменой, как для вертикального поиска, так и для горизонтального. Соответственно, данная функция будет заменять обе существующие и ВПР, и ГПР.

При всем при этом, синтаксис данной функции чрезвычайно прост:

ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возвращаемый_массив; [режим_сопоставления]; [режим_поиска])

• искомое_значение — значение, которые мы будем искать;
• просматриваемый_массив — массив, где будет осуществляться поиск;
• возвращаемый_массив — массив из которого нужно будет возвращать значения.
• режим_сопоставления и режим_поиска — необязательные параметры, позволяющие более точно настроить использование функции.

Для тех, кто освоил использование ВПР, освоение данной функции не составит никакого труда. Простейший пример представлен на анимированном рисунке.

Почему Microsft представила новую функцию?

Все дело в тех недостатках, которые существуют при использовании ВПР (как и ГПР):

• использование «приблизительного» поиска по умолчанию. По умолчанию (если забыть указать 4й параметр), функции будут осуществлять приблизительный поиск, а если список не будет отсортирован по возрастанию это приведет к неправильному результату;
• номер столбца, с которого следует вернуть результат, является числом, поэтому, если в искомом массиве будут удалятся или добавляться столбцы, данное значение также придется изменять;
• невозможно возвращать значения слева от искомого столбца;
• нет возможности осуществлять поиск «с конца»;
• нельзя возвращать следующий элемент большего размера. При использовании «приблизительного» поиска искомый диапазон должен быть отсортирован по возрастанию, следовательно получится вернуть только первое значение (которое соответствует искомому или наименьшее, которое больше искомого);
• использование большего объема данных, нежели нужно в вычислениях.

Последний пункт означает, что массивы в котором осуществляет поиск искомого значения и возвращаемого должны быть связаны, что самым неблагоприятным образом сказывается на производительности данной функции, при работе с большими таблицами. Также новая функция не требует, чтобы искомый и возвращаемый диапазоны были связаны, так и может работать как в горизонтальной плоскости, так и вертикальной.

Остальные пункты в той или иной мере могут быть решены умелым использованием самой ВПР или связкой функций ИНДЕКС и ПОИСКПОЗ, например, возвращение значения слева от искомого столбца.

О полных возможностях функции ПРОСМОТРХ в нашем справочнике.

Немного об ПОИСКПОЗX (XMATCH)

В дополнении к XLOOKUP была анонсирована и новая функция ПОИСКПОЗX (XMATCH), с похожим на XLOOKUP синтаксисом, однако возвращает индекс искомого значения.

ПОИСКПОЗX(искомое_значение; просматриваемый_массив; [режим_совпадения]; [режим_поиска])

Подробно об использовании данной функции, также можно узнать в справочнике.

— Advertisement —

• Темы
• Dynamic Array Functions
• Excel
• Office 365
• Office Insider