Pavel Istomin
· Маркетинг

Функция ВПР в Экселе – пошаговая инструкция

Eugen Sandow

Функция ВПР в Экселе – это то, что позволит вам экономить десятки часов рабочего времени. При помощи функции ВПР вы ускоритесь буквально в разы и будете вспоминать с ужасом о сравнении таблиц вручную.

Так было со мной. На моей первой официальной работе, мне приходилось раз в несколько дней сравнивать два списка. Списки были относительно небольшие, около 100 строк каждый. Но сравнивал я их при помощи ручки и линейки. Это было мучение, более того это было неэффективное мучение, которое каждый раз, занимало у меня (по меньшей мере) пол рабочего дня.

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

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

Сказать, что я был удивлён – это значит ничего не сказать. Я лицезрел настоящее чудо.

Это была потрясающая демонстрации силы автоматизации.

Функция ВПР в Экселе одинаково нужна и маркетологом, и логистам, и закупщикам – всем тем, кто работает с таблицами данных, это просто Must Have.

Функция ВПР в Экселе – быстрый перенос данных

Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.

Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).

Вам нужно быстро найти цены на эти 50 позиций. Разумеется, можно отдельно искать каждую позицию в большом прайсе и потратить на это 30 – 60 минут, а можно сделать это менее чем за минуту при помощи функции ВПР.

Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.

Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.

Обозначим задачу.

В ООО «ЫкэА» пришел запрос от «Петровича».

Петрович человек простой, любит всё делать быстро, но не очень чётко. Поэтому его запросы отличаются особым сумбуром в позициях.

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

Вот собственно и сам запрос:

Функция ВПР в Экселе-1

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

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Функция ВПР в Экселе-2

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

Это как раз то что необходимо, осталось только перенести цены из прайса в запрос.

Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:

Функция ВПР в Экселе-3

Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

Функция ВПР в Экселе-4

В аргументах функции вы говорите Экселю что и где нужно искать:

Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».

Далее, сразу переходите в «Прайс»:

Функция ВПР в Экселе-5

Теперь в аргументах функции заполните следующие поля:

Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.

Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».

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

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.

Вам нужно протянуть цены на оставшиеся ячейки:

Функция ВПР в Экселе-6

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

Когда работа идёт в двух разных книгах, Эксель автоматически закрепляет таблицу в функции ВПР:

Функция ВПР в Экселе-7

Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.

Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

Функция ВПР в Экселе-8

Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:

Функция ВПР в Экселе-9

#Н/Д значит что функция ВПР не смогла найти цену Стул_13 в прайсе и это не мудрено, ведь диапазон таблицы в формуле ВПР уехал ниже этого значения:

Функция ВПР в Экселе-10

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

Очень важное замечание №2

Как вы заметили, формулы ссылаются на определённые ячейки, другими словами между формулами и исходными данными есть связь. Стоит изменить исходные данные и значения в формулах сразу изменятся.

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

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

Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».

Функция ВПР в Экселе-18

Для тех кто не любит изучать картинки, я записал небольшое видео в котором показываю всё то, что мы проговорили выше (кроме вставки значений):

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.

Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.

Функция ВПР в Экселе – сравнение двух таблиц

Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).

Для удобства восприятия я разместил их на одном листе:

Функция ВПР в Экселе-11

Ваша задача сверить количество позиций и их цены.

Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.

Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.

Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:

«Кол/во в счёте» и «Цена в счёте»

Функция ВПР в Экселе-12

После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:

Функция ВПР в Экселе-13

Функция ВПР в Экселе-14

Обратите внимание, я закрепил диапазоны ячеек.

Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):

Функция ВПР в Экселе-15

В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).

В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).

Таким образом мы сможем увидеть разницу и в количестве и в цене.

Если значение «0», то значит всё хорошо и данные одинаковые.

Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.

Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.

Если значение #Н/Д — это значит, что в счёте вообще нет такой позиции.

Тоже самое и с ценами, если значения в плюсе, значит нам сделали скидку, а если значения в минусе — значит нам пытаются продать по завышенной цене.

Таким образом нужно просканировать всю таблицу и выяснить о причинах расхождения у поставщика:

Функция ВПР в Экселе-16

Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.

Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.

Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».

Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.

И если в столбце «Количество в заказе» мы вдруг увидим значения #Н/Д это значит, что позиции с таким наименование не было в нашем заказе поставщику.

Функция ВПР в Экселе-18

Это не всегда означает, что поставщик хочет нас «нагреть», чаще всего просто может быть ошибка в названии товара. Стоит например поставить лишний пробел, как Эксель не задумываясь выдаст #Н/Д. Для него это разные названия.

Теперь всё тоже самое продемонстрирую в небольшом видео.

Видео — «Сравнение двух таблиц с помощью функции ВПР в Экселе»

Эпилог

Коллеги, поздравляю, с этого момента ваша работа с данными значительно упростится и ускорится, ведь теперь вы «почтигуру» по применению функции ВПР в Экселе.

Автор: Павел Истомин
Комментарии
avatar
АлексейАлексей
11 месяцев назад
Ответить

оч полезная статья, спсб.

MaryMary
11 месяцев назад
Ответить

Раньше никогда не пользовалась этой формулой, действительно очень удобно и значительно сокращает трудозатраты, и бережет глаза )), спасибо!

БорисБорис
11 месяцев назад
Ответить

Сколько лишнего времени потрачено у меня, оказывается...

Огромное спасибо!!!!

Alex SandroAlex Sandro
11 месяцев назад
Ответить

👍

ДарьяДарья
11 месяцев назад
Ответить

Жаль что мне не попалась эта статья буквально пару дней назад!

Впредь буду пользоваться!

Спасибо,оч полезно!!)

ЕленаЕлена
10 месяцев назад
Ответить

Одно из самых лучших изложений информации на эту тему. Молодец

ОлегОлег
10 месяцев назад
Ответить

Благодарю за урок!, будут ли у вас ещё полезные статьи по Экселю?

ДимонДимон
10 месяцев назад
Ответить

Практически полезно! Круто!

AleksAleks
10 месяцев назад
Ответить

Отличная статья , доходчиво написано , спасибо!

АлексейАлексей
9 месяцев назад
Ответить

Годная статья!

Стоит внедрить это в свои проекты. В них зачастую нужно быстро что-то сравнить.

АннаАнна
5 месяцев назад
Ответить

Отличная статья! Спасибо. Поможет сэкономить много рабочего времени. С функцией «ВПР» я стану продуктивнее. Очень рада, что нашла ваш сайт.

WilliamraddyWilliamraddy
5 месяцев назад
Ответить

Лучшие приключения и квесты в подарок для любой тематики.

АнастасияАнастасия
5 месяцев назад
Ответить

Где же Вы были раньше?! ) неделю назад мне попался как раз такой Петрович) теперь буду знать про ВПР, спасибо за такую работу!)

АннаАнна
5 месяцев назад
Ответить

Павел, спасибо Вам большое за такое подробное разъяснение. Очень полезно! Теперь при работе с таблицами я сэкономлю огромное количество времени и нервов!

KevinserKevinser
4 месяцев назад
Ответить

ДАДАДА! НАДО СМОТРЕТЬ ВСЕМ ОБЯЗАТЕЛЬНО!