EXCEL для чайников.1.ВПР⁠⁠

Добрый день!

Решил запилить пост про любимый Excel. Работают в нем многие, также и многие пользуются лишь минимальным набором функций, а это не правильно, поскольку в Excel‘е можно решить широкий спектр задач. Мне нравится автоматизировать некоторые рутинные процессы. Если тема получит положительный фитбэк буду продолжать писать, если есть какие то вопросы не стесняйтесь и задавайте. Тема сегодняшнего поста функция ВПР и еще немного вспомогательных функций. Итак начнем. Скажу, что самое сложное было придумать задачу… Допустим у нас есть некий реестр товаров и ID менеджеров, которые этот товар реализовали, а также есть реестры менеджеров 1 отдела и 2 отдела. для интереса пусть в реестрах будут только фамилии, а имена и отчества будут еще в одном реестре

Реестр товаров и ID менеджеров

Реестр менеджеров 1 отдела

Реестр менеджеров 2 отдела

Реестр имен отчеств

Все менеджеры являются вымышленными, любое совпадение с реальными людьми чистой воды случайность. Итак, задача - нам нужно добавить в первый реестр ФИО менеджера. У меня все эти реестры на одном листе для наглядности, но они могут быть на разных листах или в разных файлах. Как выглядят аргументы функции ВПР можно узнать из справки, вообще в Excel неплохая справка, так что не стесняемся пользоваться. В ячейке D2 пишем

=ВПР(C2;G:H;2;0), протягиваем до конца листа

у нас подтянулись фамилии из реестра первого отдела, идем дальше

в той же D2 пишем

=ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0))

можно для начала делать ВПР в разных ячейках, потом их значения объединять в третьей ячейке при помощи функции ЕСЛИОШИБКА. на пример так

результат будет таким же, но по началу может быть более понятно.

Так, теперь в столбце Е нужно указать Имя Отчество, снова ВПР… В ячейке E2 пишем

=D2&" "&ВПР(D2;M:N;2;0).

Здесь мы использовали символ & чтобы объединить 2 ячейки и поставить пробел между ними. При желании можно все забубенить в одну формулу для ячеек в столбце D

=ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0))&" "&ВПР(ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0));M:N;2;0)

как видите она трудночитаема, если нужно будет что то переделать то будет трудно понять что откуда берется, так что рекомендуется так делать в самом конце, когда все уже работает как надо. И рассмотрим ситуацию когда функция не нашла не в одном реестре нужного ID

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0))&" "&ВПР(ЕСЛИОШИБКА(ВПР(C2;G:H;2;0);ВПР(C2;J:K;2;0));M:N;2;0);"менеджер не найден")

в случаи отсутствия ID в наших двух реестрах функция ЕСЛИОШИБКА вернет фразу «менеджер не найден»

результат нашего труда

Интервальный просмотр и с чем его едят:

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

формулу в ячейке B2 =ВПР(A2;D:E;2;1) протянуть до конца таблицы.

Почему в меня не ВПРится ?!.

Распространённые ошибки: ВПР ищет по первому столбцу диапазона, ВПРу не нравится числа сохраненные как текст, ВПРу не нравятся пробелы вначале и в конце ячеек, а также английские буквы вместо русских (было и такое), непечатные символы (было и такое тоже, это когда выглядит как пробел, а по факту не разу не пробел и поиском и заменой не вычищается), ВПР работает до пустых строк, дальше поиск не ведется, ВПР подставляет первое найденное совпадение, игнорируя последующие (если есть такие задачи с одинаковыми значениями нужно добавить в сцеп с ячейкой другое уникальное значение и ВПРить по сцепу.) Уф, вроде бы все…

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