© Сорокин Б.Б. 2016 г.

Варианты задания параметров

Бывают случаи, когда в области данных нет столбца с уникальными данными, т.е. значения в столбце, по которому производится поиск, могут повторяться. В этом случае функция ВПР, просматривая данные по столбцу поиска, остановится на первом найденном значении A1. Обычно в этом случае необходимо произвести группировку данных по всем неуникальным значениям столбца "A" с целью добиться их уникальности. Если это невозможно или не целесообразно по структуре и содержимому данных то для решения этой проблемы можно использовать различные варианты задания параметров функции ВПР. Например, создать дополнительный столбец с уникальными значениями, объединив значения нескольких столбцов с помощью функции =СЦЕПИТЬ($A1;$B1). Тогда искомое значение можно задать следующим образом:

формула

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

Следует помнить, что функция =СЦЕПИТЬ($A1;$B1) возвращает значения в текстовом формате поэтому, и искомое значение должно иметь текстовый формат. Эту ошибку неопытные пользователи допускают достаточно часто, коварство ее заключается в том, что "на глаз" вроде бы значения совершенно одинаковы а функция его не находит и возвращает #НД. Если такая ситуация возникает с отдельными записями базы данных, а сама база достаточна большая и на глаз искать ошибку трудно, рекомендуется предварительно обработать ключевые значения функцией СЦЕПИТЬ(), чтобы гарантированно задать им текстовый формат и избавится от возможных ошибок.

Для того чтобы производить поиск по фрагменту А1 возможно задать параметры через функции обработки текста ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ() и т.д. Например: если A1 имеет значение 018508102 а искать надо только по седьмому и восьмому знаку этого значения (т.е. 10), это можно решить следующим образом образом:

формула

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

формула

Здесь в ТАБЛИЦА1 по значению А1 находят искомое значение для области данных База_данных и на основании его выбирают значение из столбца 2.

Копирование функции ВПР

Применяя функцию ВПР, ссылку на столбец лучше делать абсолютной, а на строку оставлять относительной. Это позволит, без ручной правки, скопировать функцию по всем ячейкам таблицы. Но вот номер столбца, откуда необходимо брать данные, остается без изменений, т.е. во все ячейки будут возвращены данные из заданного первоначально (в нашем примере второго столбца). Конечно, можно вручную изменить номер столбца, если табличка небольшая, а если в ней больше сотни столбцов, что делать? В этом случае возможно заменить номер столбца, функцией =СТОЛБЕЦ(B:B). Например:

формула

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

формула