Как правильно сделать впр в excel понятная инструкция

В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения. 

В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.

Как создать функцию ВПР в Excel

Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

1. Выделить ячейку и вписать функцию.

2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

=ВПР(A2;

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат. 

1. Формула сканирует таблицу по вертикали.

2. Находит в самом левом столбце совпадение с искомым значением.

3. Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.

4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Автозаполнение

В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.

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

• В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

• Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

ВПР и приблизительный интервальный просмотр

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

Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.

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

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

• Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз. 

• Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

• Функцию можно вписать вручную или в специальном окне (Shift + F3).

• Искомое значение – относительная ссылка, а таблица – абсолютная. 

• Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением. 

• Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

• Порядок работы с функцией подходит для Гугл-таблиц.

Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.

Очень удобная и часто используемая. Т.к. сопоставить вручную диапазоны с десятками тысяч наименований проблематично.

Как пользоваться функцией ВПР в Excel

Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.

Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

Алгоритм действий:

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».



3. Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.



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



5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.



6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.

Теперь найти стоимость материалов не составит труда: количество * цену.

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

1. Выделяем столбец со вставленными ценами.
2. Правая кнопка мыши – «Копировать».
3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.



Быстрое сравнение двух таблиц с помощью ВПР

Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами.

1. В старом прайсе делаем столбец «Новая цена».



2. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу.

Функция ВПР в Excel с несколькими условиями

До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.

Таблица для примера:

Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.

Дело осложняется тем, что от одного поставщика поступает несколько наименований.

1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».



2. Таким же образом объединяем искомые критерии запроса:



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

Рассмотрим формулу детально:

1. Что ищем.
2. Где ищем.
3. Какие данные берем.

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».



3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.



4. Когда нажмем ОК – сформируется выпадающий список.

Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).

1. Открываем «Мастер функций» и выбираем ВПР.
2. Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
3. Нажимаем ВВОД и наслаждаемся результатом.

Изменяем материал – меняется цена:

Скачать пример функции ВПР в Excel

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

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Алгоритм работы функции:

1. Указываем, что искать (абсолютное значение или что-то в ячейке).
2. Указываем, где искать (некий диапазон в другой таблице).
3. Указываем, какое значение возвращать. Это – самая сильная сторона ВПР. Когда функция находит совпадение (например, фамилию работника), она возвращает не саму фамилию, а значение, которое находится в той же строке. То есть мы можем вернуть зарплату этого работника, его фамилию, дату рождения – в общем, все, что захотим, при условии, что все данные в одной строке принадлежат одному работнику (данные упорядочены).
4. Указываем точность совпадения. Эксель позволяет вернуть как точное совпадение, так и частичное. Рекомендуем всегда пользоваться точным совпадением, потому что «частичное совпадение» – вещь весьма расплывчатая, Эксель может взять вообще не те данные, которые вам на самом деле нужны. Как-то повлиять на алгоритм выбора простыми методами нельзя.

На практике это выглядит вот так: ВПР(B2;’Дневная потребность’!$A$3:$B$12;2;0), где:

• B2 – что мы будем искать.
• ‘Дневная потребность’!$A$3:$B$12 – где мы будем искать. Указываем таблицу на другом листе.
• 2 – из какого столбца мы будем возвращать данные, когда найдем соответствие.
• 0 – используем точное соответствие (1 – использовать приблизительное, не рекомендуется).

Примеры

Разберем несколько задач с использованием функции ВПР в Excel.

1. Поиск неизвестного в общей таблице.

Это простейшая задача научит быстро обнаруживать необходимые данные в больших таблицах.

Исходная информация:

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

Задача: найти стоимость препарата Хепилор.

Решение состоит в последовательности следующих действий:

• Выбор критерия: в ячейку В12 вводим название Лекарственного препарата «Хепилор».
• Выбор массива: выбираем диапазон начала и конца таблицы, где должен осуществляться поиск: В3:D10.
• Выбор номера столбца: указываем номер столбца, из которого должна быть считана информация в одной строке с названием препарата. В нашем примере это 3, т. к. столбец №1 расположен вне области нужного нам диапазона.
• Ставим функцию «0» или «Ложь».

Аналогично можно произвести поиск производителя Хепилора. Для этого потребуется заменить номер столбца, где расположены необходимые данные, т. е. 3 на 2.

2. Пример расчета неизвестного показателя из исходных данных.

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

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

  

• Во второй таблице указано количество пакетиков на день в зависимости от веса кота.

  

В строке 2 указаны номера колонок – колонки обязательно нужно нумеровать, без этого функция работать не будет.

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

Нам нужно заполнить аргументы функции:

Искомое значение – это то значение, по которому мы будем искать. В нашем случае это «Вес кота», поэтому указываем B2. Таблица – это место, где мы будем искать. Нужно выбрать всю таблицу за исключением заголовка и номеров колонок.

Кроме того, нужно зафиксировать значения диапазона таблицы, чтобы она не «поехала», когда мы будем распространять формулу на другие ячейки основной таблицы. Для этого выделяем диапазон в конструкторе формулы и жмем F4.

Далее указываем номер столбца, из которого нужно брать данные. В нашем случае – второй столбец, поскольку в нем указано количество пакетиков. В «Интервальный просмотр» ставим 0, чтобы искать по точному соответствию.

Применяем – получаем результат. Растягиваем его на остальные ячейки.

Пакетик корма стоит 60 рублей, поэтому высчитываем стоимость как D2=C2*60 (и растягиваем на остальные пустые ячейки), в C9 пишем =СУММ(C2:C8), в D10 пишем =СУММ(D2:D8).

Получаем 14.5 пакетиков в день на сумму 870 рублей, по факту придется купить 15 на сумму 900 рублей, ибо половину пакетика никто не продаст.

3. Комбинирование таблиц с ВПР.

Исходные данные: имеем 2 таблицы.

«Отчет о количестве товара» и «Отчет о цене за единицу товара».

Задача: объединить данные двух таблиц.

Порядок действий:

Выбираем ячейку для вставления данных (D3) и пишем функцию: ВПР (В3;F3:G14;2;0), где:

• выбор критерия:В3;
• выбор диапазона: F3:G14;
• № столбца: 2;
• стандартно: «0» или «Ложь».

Алгоритм решения:

1. Поиск совпадений с верхней ячейки первого столбца.
2. Поиск соответствия установленному критерию сверху вниз.
3. После того, как найден Хепилор, производится отсчет столбцов вправо.
4. ВПР выдает искомое значение, в нашем случае это цена – 86,90.

Чтобы в столбец D первой таблицы вставить данные не по одной строке, а в целом, нужно скопировать функцию до последнего критерия. Но, для избежания «съезжания» массива вниз, нужно использовать абсолютные ссылки для диапазона в ячейке D3. Для этого нужно выделить диапазон F3:G14 и нажать клавишу F4, далее завершить копирование формулы.

Итоговая таблица будет такая:

Здесь вы сможете скачать примеры применения ВПР Excel

ВПР в Гугл Таблицах

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

Примеры с пошаговыми инструкциями

1. Пример. Осуществляем поиск данных из списка.

Дана таблица с именами и оценками учащихся.

Требуется быстро найти оценку конкретного ученика, например, Martha.

Алгоритм: =VLOOKUP(E2,$A$2:$B$10,2,False)

При введении в ячейку Е2 любого имени, в Е3 будет отображаться значения его оценки.

Это очень удобно, когда исходных данных много, таблицы большие, а информация нужна срочно.

2. Пример.

Создаем 2 таблицы – основную и ту, в которой мы будем искать информацию.

Выбираем первую ячейку, пишем =ВПР(. После этого Гугл предлагает нам ввести или выбрать данные. Данные разделяются знаком ;.

Чтобы указать диапазон таблицы, переходим на нужную страницу и выделяем таблицу. Выделили – не забудьте нажать на F4, чтобы зафиксировать диапазон.

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

Возможные ошибки новичков

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

Что почитать по теме

• Справка от Майкрософт.
• Справка от Гугла.

FAQ

Какой результат выдаст функция, если найдет несколько вхождений в таблице, в которой мы ищем данные?

Функция вернет результат из первого найденного вхождения.

Можно ли писать внутри функции формулы?

Да, вы можете как написать формулу внутри одного из параметров функции, так и передать результаты работы функции в формулу. Например, =ECЛИ(ВПР(“Иванов”;’сотрудники’!$B$3:$B$203;3;0)=1;”Есть”;”Не найден”) будет писать «Есть», если такой сотрудник есть в базе (и в специальной колонке ему присвоено значение 1), и «Не найден» в противном случае.

Подведем итоги 

Тезисно:

• ВПР позволяет вам вставить какие-то данные из другого листа (или с этого же, если они попадают под определенные критерии).
• Чтобы написать функцию, вам нужно указать: какие данные нужно искать; где их искать; из какой колонки таблицы брать результат; искать ли по точному совпадению.
• В Excel и Google Sheets – одинаковый синтаксис для ВПР, единственная разница – Excel позволяет создать функцию через мастера создания функций.

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

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

Допустим, в магазин канцелярских товаров поступил новый привоз, к которому прилагается соответствующая документация. Администратору торгового зала необходимо рассчитать полную стоимость продукции, имея на руках файл Excel, который содержит две таблицы.

Первая – это список предметов, единицы их измерения и количество.

Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.

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

Этапы работы (инструкция):

1. Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
   
2. Отметить верхнее поле в новом.
   
3. Выбрать раздел формулы, и нажать «Вставить функцию».
   
4. Из предложенных категорий Excel отметить «Ссылки и массивы».
   
5. Найти ВПР, и нажать «ОК».
   
6. Заполнить открывшееся окно «Аргументы».
   

– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.

– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).

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

Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.

Номер — это строка в которой должна быть информация о том, что будет переноситься из другой таблицы. В рассматриваемом случае – это второй столбец (2).

Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».

В конечном итоге, окно «Аргументы» выглядит так:

1. Нажать «ОК».

Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.

Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

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

Делается это в несколько шагов:

1. Открыть первую со старой информацией.
   
2. Добавить дополнительный столбик для новых данных «Новая стоимость».
   
3. Выделить первое пустое поле в созданном столбце (С2).
   
4. Выбрать раздел «ВПР Формулы» и «Вставить функцию».
   
5. Найти категорию Excel «Ссылки и массивы».
   
6. Выбрать ВПР.
7. Задать «Аргументы».

Инструкция:

– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).

– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.

Номер столбца – второй, так как именно стоимость переносится в новую.

Интервальный просмотр – ЛОЖЬ.

Заполненное окно выглядит так:

После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.

Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.

Узнайте также:

• Как перевести файл Excel в PDF,
• Как перевести файл Pdf в Ворд онлайн,
• Как делать ссылки в Word: пошаговая инструкция.

Поиск с помощью ВПР по нескольким условиям

Если пользователю программы Excel необходимо из большого каталога найти необходимые данные, он может воспользоваться данным способом для чайников (инструкция).

Итак, имеется документ, в котором обозначены: компании, товары и цены.

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

Чтобы осуществить поиск следует:

1. Создать слева новый столбец с объединёнными данными (название компании и товара).

Делается это просто:

• выделить крайнюю левую ячейку (А1);
• щёлкнуть ПКМ и выбрать «Вставить»;
• отметить добавление столбца и нажать «ОК».

1. Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2&C2) и нажать кнопку Enter. Чтобы продлить список достаточно растянуть ячейку.
   
   
2. Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
   
3. Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.

Инструкция:

– что нужно найти (щёлкнуть по введенной — ЛасточкаГелевая ручка – А8).

– где искать нужное значение (выделить ячейки от первой до последней — А2 – D5).

Номер столбца – из какого столбца вывести результат (4).

Интервальный просмотр – ЛОЖЬ.

После нажатия команды «ОК», программа отобразит результат.

Как сделать выпадающий список через функцию ВПР

Чтобы сделать выпадающий список из существующего нужно следовать инструкции:

1. Выбрать поле, в котором будет сформированы показатели. Например, Е2.
   
2. Зайти в раздел «Данные», и выбрать «Проверка данных».
   
3. Установить тип данных, как список.

1. В появившуюся строку «Источник» ввести информацию (выделить с первой до последней ячейки – А2:А5).
   

Выпадающий список готов.

Теперь с помощью функции ВПР нужно добавить возможность просмотра цены, при выборе товара. Как это работает в Эксель? (Инструкция).

1. Создать новое поле с названием «Цена».
   
2. Вставить аргументы.

– ячейка Excel, в которой находится выпадающий список (Е2).

– выделенный фрагмент с предметами и ценами (А2-В5).

Номер столбца – 2 (в нём находятся цены).

Интервальный просмотр – ЛОЖЬ.

После подтверждения команды можно пользоваться списком и просмотром цены.

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

Что это? Функция ВПР в Еxcel (или иначе VLOOKUP, то есть вертикальный просмотр) позволяет работать с двумя таблицами одновременно, оперируя их данными путем переноса значений из одной в другую.

Зачем нужна? К примеру, вам нужно оценить бюджет предстоящего маркетингового мероприятия, когда в одной таблице прайс-лист, а в другой – предполагаемое количество единиц разного мерча. Как же в этом случае использовать ВПР в Еxcel?

Допустим, вы занимаетесь продажей автомобилей. У вас есть две таблицы: в одной указаны характеристики и цены каждой модели, в другой находится список клиентов с контактами, которые желают приобрести ту или иную машину.

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

Однако сделать это не так просто. Нужного результата вы не достигнете, если просто скопируете и вставите столбец. А поиск и перенос цен вручную – долгая и кропотливая работа.

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

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

Пошаговая инструкция по работе с ВПР в Excel

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

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

Скачать файл

Шаг 1. Построение функции

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

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

Приступаем к построению функции. Сделать это можно двумя способами: во вкладке «Формулы» выбрать пункт «Вставить функцию» либо в строке ссылок нажать на значок «fx».

После этого откроется окно «Построитель формул», в котором, используя поиск, нужно найти ВПР, а затем нажать кнопку «Вставить функцию».

Далее появится поле для ввода аргументов, которое необходимо заполнить. А как это сделать смотрим ниже.

Шаг 2. Заполнение значений функции

Перед тем, как объяснить выбор аргументов, рассмотрим понятие каждого.

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

Как установить данный параметр?

• Курсор устанавливаем в поле «Искомое значение» в окне «Построитель формул».
• Выбираем ячейку А2, в которой содержится первое значение из столбца моделей автомобилей.
• Значение, которое мы указали, помимо построителя формул, дублируется в формуле строки ссылок, которая выглядит так: fx = ВПР (А2).

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

У нас это стоимость автомобилей. Соответственно, в область работы функции мы включаем столбцы «Модель», которые является искомым значением и «Цена», то есть то, что будет перенесено.

Обращаем внимание! Для корректной работы формулы диапазон должен начинаться с ячейки, с искомым значение, расположенной в первой колонке. В нашем примере это А2.

Последовательность действий при выборе диапазона:

• Устанавливаем курсор в поле «Таблица» в окне построителя формул.
• Возвращаемся к таблице каталога автомобилей.
• Выделяем диапазон с колонками «Модель» и «Цена». У нас получается: А2:Е19
• Теперь выбранную область нужно закрепить. На Windows для этого требуется выбрать значение диапазона в строке ссылок, а затем нажать клавишу F4. На macOS подтверждаем выбранную в строке ссылок область сочетанием клавиш Cmd + T. Это действие необходимо, чтобы функцию можно было протянуть вниз для ее правильного срабатывания во всех строках.

Диапазон, который мы установили, появляется в построителе формул и в строке ссылок. Визуально это выглядит так: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).

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

Если нумерация колонок не проставлена, необходимо сделать это вручную. У нас столбец «Цена» под номером пять.

Устанавливаем курсор в поле «Номер столбца» в окне «Построитель формул», вводим нужное значение. Функция ВПР теперь выглядит следующим образом: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).

Интервальный просмотр – это параметр настройки работы формулы.

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

В нашем примере необходим поиск точных значений цены, поэтому выбираем 0.

Значение вводим в поле «Интервальный просмотр» и функция ВПР в Excel приобретает завершенный вид: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0)

Шаг 3. Получение результатов

После всех проделанных действий и установки требуемых аргументов, жмем кнопку «Готово». В ячейке, выделенной нами в первом шаге, появится соответствующее значение – цена модели машины.

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

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

Поиск по нескольким критериям

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

Давайте попробуем выяснить, например, цену, по которой от ООО «Запад» нам поступил картон. Нужно произвести поиск значений по двум критериям: наименованию материала и поставщику.

Загвоздка в том, что грузоотправитель доставляет товары разных наименований.

Порядок действий таков:

• В первую очередь нужно добавить в таблицу столбец слева, при этом произведя объединение «Материалов» и «Поставщиков».
• Аналогично совмещаем искомые значения.
• Задаем аргументы для функции ВПР: =ВПР(I6;$A$2:$D$15;4;ЛОЖЬ)
• Нажимаем «Готово». Поиск завершен, цена найдена.

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

• Создаем раскрывающийся список: устанавливаем курсор в ячейку Е8.
• Переходим во вкладку «Данные» в меню «Проверка данных».
• Тип данных, нужный нам – «Список», для отбора указываем область с названием материалов.
• Подтверждаем свои действия нажатием клавиши «ОК». Раскрывающийся список создан.

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

Используем «Мастер функций» и устанавливаем аргументы. Искомое значение – ячейка с раскрывающимся списком; таблица – диапазон, включающий в себя «Материалы» и «Цены» (столбец 2). Наша формула должна выглядеть так: =ВПР(E8;A2:B16;2;ЛОЖЬ)

Подтверждаем настройку нажатием соответствующей клавиши.

Быстрое сравнение двух таблиц с помощью ВПР в Excel

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

Пример. В связи с изменением прайса, перед нами поставлена задача: произвести сравнение между старыми и новыми ценами.

Для упрощения воспользуемся формулой ВПР в Excel для сравнения:

• Открываем старый прайс и добавляем колонку «Новая цена».
• Встаем на первую ячейку и через «Мастер функций» выбираем ВПР. Задаем параметры и получаем: =ВПР($A$2:$A$15;’новый прайс’!$A$2:$B$15;2;ЛОЖЬ).
• Таким образом мы сделали следующее: указали диапазон наименований А2:А15 в старом прайсе и сравнили его с новым. После чего значения новых цен подставили в ячейку С2 созданного столба прежнего прайса.

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

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