Функции ВПР2 ГПР2
Является модифицированной версией стандартной функции ВПР Excel. Она разработана таким образом, чтобы позволять:
- Возвращать для ключа все его совпавшие значения в отличии от встроенной функции, которая возвращает только одно значение;
- Производить поиск по ключу вне зависимости от его расположения в таблице;
- Производить поиск по 2 и более условиям (составной ключ).
В надстройке также имеется функция ГПР2, ее описание аналогично данному, но последняя осуществляет горизонтальный поиск.
Синтаксис: =ВПР2(Ключ; Таблица; Столбец_поиска; [Столбец_Ключа]), где:
- Ключ – обязательный аргумент. Значение либо ссылка на ячейку, содержащую значение для поиска. Когда необходимо определить несколько ключей, они должны быть записаны через символ амперсанда – &;
- Таблица – обязательный аргумент. Диапазон, содержащий искомые значения и ключи;
- Столбец_поиска – обязательный аргумент. Целое число, указывающее на порядковый номер столбца в таблице, из которого необходимо возвратить значение;
- Столбец_Ключа – необязательный аргумент. Значение по умолчанию 1. Целое число, указывающее на порядковый номер столбца в таблице, в котором находится значение ключа. В случаях использования нескольких ключей, номера их столбцов записываются в виде массива (внутри фигурных скобок) через точку с запятой – {1; 3; 7}. Имейте в виду, что порядок указания столбцов должен совпадать с порядком задания ключей.
Пример использования:
На приведенном скриншоте функция ВПР2 возвращает для ключей 1 и 4 по два значения, разделенных точкой с запятой. При включенном переносе текста в ячейках, каждое последующее значение начинается с новой строки.
В следующей примере осуществляется поиск значений для составного ключа. Если значение не найдено, то возвращается ошибка #Н/Д.
Для того, чтобы использовать описанные в данной категории функции, скачайте и установите нашу надстройку.
Работа надстройки была успешно протестирована на версиях Excel: 2007, 2010 и 2013. В случае возникновения проблем с ее использованием, сообщайте Администрации сайта.
Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.
Рекламный блок
Комментарии
=ВПР2($A3&E;$1;Таблица1;5;{1;4})
Не хочет считать... При этом, когда тут диапазон формата А3:С16 все работает...
Я так понимаю, что номера столбцов в третьем и четвертом аргументах выходят за пределы таблицы, и функция возвращает ошибку.
В диапазоне А3:С16 3 стоблца, а Ваша функция ссылается на 5 и 4, которые отсутствуют.
Проверил работу функции, именные диапазоны распознаются (за их распознание отвечает Excel).
Я указала А3:С16 для обозначения формата указания диапазона, в таблице, конечно более 3-х столбцов.
Если у Вас работает функция с именными диапазонами, проверю у себя ещё раз). Нужный результат я уже получила через ИндексПоискпоз, но из спортивного интереса все равно попробую.
В любом случае надстройка очень полезная! Спасибо Вам огромное!
Отпишусь дополнительно по результату))
А ещё, не могли бы Вы подсказать, как отредактировать надстройку таким образом, чтобы при написании формулы был виден синтаксис с подсказками?
Если снова будут проблемы с функцией, то скиньте Ваш пример на почту, указанную в контактах и укажите версию офиса.
Только что проверила с именным диапазоном, все работает! Вероятно, в силу позднего времени в прошлый раз чего-то намудрила.
Спасибо за ответ.
Возможно мы не поняли друг друга.
Посм.влож.файл.
В D8 при вычислении ВПР2 возникает #Н/Д
Стандартная же ф-ция ВПР в аналогичном случае (яч.D7) возвращает 0
Возможно Вы так задумывали это реализовать.
Но на мой взгляд с точки зрения совместимости со стд.функцией – логично было бы возвращать 0 (ноль)
Спасибо за интересные и нужные дополнения функций Excel.)
С уважением,
Паршин Роман
Функция выводит #НД только в случае отсутствия ключа в указанном диапазоне.
Подозреваю, что у Вас расхождения форматов ячеек. Поробуйте настроить числовые форматы для столбца ключа и ячейки со значением ключа и повторно ввести формулу.
Если не сработает, то отправьте сообщение c вложением файла на электронную почту администрации сайта (пункт контакты в подвале).
Но описанная Вами ситуация теперь предусмотрена в функциях. Скачайте надстройку повторно.
Спасибо за комментарий.