Функции ВПР2 ГПР2

Является модифицированной версией стандартной функции ВПР Excel. Она разработана таким образом, чтобы позволять:

  • Возвращать для ключа все его совпавшие значения в отличии от встроенной функции, которая возвращает только одно значение;
  • Производить поиск по ключу вне зависимости от его расположения в таблице;
  • Производить поиск по 2 и более условиям (составной ключ).

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

Синтаксис: =ВПР2(Ключ; Таблица; Столбец_поиска; [Столбец_Ключа]), где:

  • Ключ – обязательный аргумент. Значение либо ссылка на ячейку, содержащую значение для поиска. Когда необходимо определить несколько ключей, они должны быть записаны через символ амперсанда – &;
  • Таблица – обязательный аргумент. Диапазон, содержащий искомые значения и ключи;
  • Столбец_поиска – обязательный аргумент. Целое число, указывающее на порядковый номер столбца в таблице, из которого необходимо возвратить значение;
  • Столбец_Ключа – необязательный аргумент. Значение по умолчанию 1. Целое число, указывающее на порядковый номер столбца в таблице, в котором находится значение ключа. В случаях использования нескольких ключей, номера их столбцов записываются в виде массива (внутри фигурных скобок) через точку с запятой – {1; 3; 7}. Имейте в виду, что порядок указания столбцов должен совпадать с порядком задания ключей.

Пример использования:

На приведенном скриншоте функция ВПР2 возвращает для ключей 1 и 4 по два значения, разделенных точкой с запятой. При включенном переносе текста в ячейках, каждое последующее значение начинается с новой строки.

Все значения ключа

В следующей примере осуществляется поиск значений для составного ключа. Если значение не найдено, то возвращается ошибка #Н/Д.

Использование составного ключа

Для того, чтобы использовать описанные в данной категории функции, скачайте и установите нашу надстройку.
Работа надстройки была успешно протестирована на версиях Excel: 2007, 2010 и 2013. В случае возникновения проблем с ее использованием, сообщайте Администрации сайта.

Скачать надстройку

Комментарии  

0 # Софья 19.06.2016 20:32
Здравствуйте! Скажите, пожалуйста, ВПР2 не работает с именными диапазонами?
=ВПР2($A3&E$1;Таблица1;5;{1;4})
Не хочет считать... При этом, когда тут диапазон формата А3:С16 все работает...
Ответить | Ответить с цитатой | Цитировать
0 # Андрей 19.06.2016 22:27
Софья, добрый день!
Я так понимаю, что номера столбцов в третьем и четвертом аргументах выходят за пределы таблицы, и функция возвращает ошибку.

В диапазоне А3:С16 3 стоблца, а Ваша функция ссылается на 5 и 4, которые отсутствуют.

Проверил работу функции, именные диапазоны распознаются (за их распознание отвечает Excel).
Ответить | Ответить с цитатой | Цитировать
0 # Софья 19.06.2016 22:41
Андрей, спасибо за скорый ответ!
Я указала А3:С16 для обозначения формата указания диапазона, в таблице, конечно более 3-х столбцов.
Если у Вас работает функция с именными диапазонами, проверю у себя ещё раз). Нужный результат я уже получила через ИндексПоискпоз, но из спортивного интереса все равно попробую.
В любом случае надстройка очень полезная! Спасибо Вам огромное!
Отпишусь дополнительно по результату))
А ещё, не могли бы Вы подсказать, как отредактировать надстройку таким образом, чтобы при написании формулы был виден синтаксис с подсказками?
Ответить | Ответить с цитатой | Цитировать
0 # Андрей 19.06.2016 23:24
Подсказки можно получить, если открыть окно функции. При написании в строке функции подсказки не видны. Реализовать это посредством VBA у меня не получилось.
Если снова будут проблемы с функцией, то скиньте Ваш пример на почту, указанную в контактах и укажите версию офиса.
Ответить | Ответить с цитатой | Цитировать
0 # Софья 21.06.2016 17:54
Андрей, спасибо!
Только что проверила с именным диапазоном, все работает! Вероятно, в силу позднего времени в прошлый раз чего-то намудрила.
Ответить | Ответить с цитатой | Цитировать
0 # Роман 03.03.2015 11:50
Добрый день!
Спасибо за ответ.
Возможно мы не поняли друг друга.
Посм.влож.файл.
В D8 при вычислении ВПР2 возникает #Н/Д
Стандартная же ф-ция ВПР в аналогичном случае (яч.D7) возвращает 0
Возможно Вы так задумывали это реализовать.
Но на мой взгляд с точки зрения совместимости со стд.функцией – логично было бы возвращать 0 (ноль)

Спасибо за интересные и нужные дополнения функций Excel.)

С уважением,
Паршин Роман
Ответить | Ответить с цитатой | Цитировать
0 # Андрей 03.03.2015 20:15
Роман, поровел тест по Вашему примеру. Функция надстройки также возвращает 0.
Функция выводит #НД только в случае отсутствия ключа в указанном диапазоне.
Подозреваю, что у Вас расхождения форматов ячеек. Поробуйте настроить числовые форматы для столбца ключа и ячейки со значением ключа и повторно ввести формулу.
Если не сработает, то отправьте сообщение c вложением файла на электронную почту администрации сайта (пункт контакты в подвале).
Ответить | Ответить с цитатой | Цитировать
0 # Роман 27.02.2015 08:59
Почему если в столбце поиска ПУСТО то ф. возвр. знач.#Н/Д?
Ответить | Ответить с цитатой | Цитировать
0 # Андрей 02.03.2015 22:32
Роман, по такому же принципу работает стандартная функция ВПР.
Но описанная Вами ситуация теперь предусмотрена в функциях. Скачайте надстройку повторно.
Спасибо за комментарий.
Ответить | Ответить с цитатой | Цитировать

Добавить комментарий