Программа впр в эксель пошагово для начинающих

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

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

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

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

Зачем нужна функция ВПР

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

Объединение данных из разных таблиц

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

В английском варианте функция называется VLOOKUP ( V ertical Look Up ), что, собственно, и отразилось в переводе — ВПР является своеобразным сокращением от « В ертикальный ПР осмотр».

Функция ВПР в Excel. от А до Я

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

Описание функции ВПР в окне «Вставка функции»

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

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

Ссылка на цену

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

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

Использование функции ВПР для «подтягивания» цены из прайс-листа

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

Критерий, объединяющий данные из двух таблиц

Ну а чтобы корректно применять функцию ВПР стоит разобраться в том, как она работает.

Как работает функция ВПР

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

Телефонный справочник

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

Проходим все названия на странице и доходим до нужной организации.

Далее обращаем внимание на соседнюю колонку, в которой отражен телефон. Задача выполнена.

Поиск номера телефона в справочнике

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

Функция ВПР excel за 5 минут ► VLOOKUP in Excel for 5 minutes

Давайте рассмотрим синтаксис функции ВПР и ее аргументы.

Синтаксис функции ВПР

Функция имеет четыре аргумента. Рассмотрим их на примере простейшей телефонной книги.

Телефонная книга в Excel

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

В этом массиве данных нужно найти телефон Ивана.

Воспользуемся функцией ВПР.

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

В подсказке через точку с запятой указаны четыре аргумента.

Первый — искомое_значение . Это то, ЧТО мы должны искать в массиве данных, то есть в нашем случае имя « Иван ».

Второй аргумент — таблица , но фактически здесь мы должны указать диапазон значений в котором будет производиться поиск, а не таблицу целиком. То есть это то, ГДЕ будет производиться поиск.

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

Диапазон для функции ВПР

Но почему мы выбираем не только столбец с именами, в котором ищем нужный критерий?

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

Но как указать функции, какой именно столбец нас интересует? То есть, что нам нужен телефон, а не адрес?

В этом поможет третий аргумент функции — номер_столбца .

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

В рассматриваемом примере был выделен диапазон, состоящий из трех столбцов, поэтому здесь первым будет столбец с именами, второй с адресами, а третий с телефонами.

Источник: dzen.ru

Функция ВПР в 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)
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Итоги

• Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
• Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
• Функцию можно вписать вручную или в специальном окне (Shift + F3).
• Искомое значение – относительная ссылка, а таблица – абсолютная.
• Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
• Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
• Порядок работы с функцией подходит для Гугл-таблиц.

Источник: timeweb.com

ВПР в Excel и Гугл Таблицах – функция VLOOKUP

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

ВПР в Excel
ВПР в Гугл Таблицах
Возможные ошибки новичков
Что почитать по теме
Подведем итоги

ВПР в Excel

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

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, далее завершить копирование формулы.

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

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

У Гугла все работает абсолютно так же. Функция называется 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 позволяет создать функцию через мастера создания функций.

Источник: www.sravni.ru