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

0
255

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

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

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

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

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

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

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

ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возвращаемый_массив;
[режим_сопоставления]; [режим_поиска])
  • искомое_значение — значение, которые мы будем искать;
  • просматриваемый_массив — массив, где будет осуществляться поиск;
  • возвращаемый_массив — массив из которого нужно будет возвращать значения.
  • режим_сопоставления и режим_поиска — необязательные параметры, позволяющие более точно настроить использование функции.

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

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

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

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

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

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

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

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

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

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

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