Впр программа что это

Использование функции ВПР в программе Excel

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

Описание функции ВПР

Смотрите также: “Как убрать колонтитулы в Экселе”

ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.

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

Применение функции ВПР на практике

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

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

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

Смотрите также: “Автофильтр в Excel: как сделать, использовать, отменить”

Порядок действий в данном случае следующий:

1. Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
2. В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
3. Теперь предстоит правильно заполнить аргументы функции:
4. в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
5. переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
   Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
6. Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
7. в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
8. в значении аргумента “Интервальный_просмотр” можно указать два значения:
9. ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
10. ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
11. мы выбираем первый вариант, так как нам важна предельная точность.
12. Когда все готово, нажимаем OK.
13. В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
14. Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
15. В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.

Как использовать функцию ВПР (VLOOKUP) в Excel

Заключение

Смотрите также: “Дубликаты в Excel: как найти, выделить или удалить”

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

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

Как работать с функцией ВПР в Эксель: инструкция для новичков

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

Использование ВПР в программе Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е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, ввести формулу объединения (=B2https://virtualniy-mir.ru/kak-rabotat-s-funkciej-vpr-v-eksel-instrukciya-dlya-novichkov/» target=»_blank»]virtualniy-mir.ru[/mask_link]

   Функция ВПР в программе Microsoft Excel

   

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

   Определение функции ВПР

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

   Пример использования ВПР

   Взглянем, как работает функция ВПР на конкретном примере.

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

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

   

   Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.
2. В открывшемся окне мастера функций выбираем категорию «Ссылки и массивы». Затем, из представленного набора функций выбираем «ВПР». Жмем на кнопку «OK».
3. После этого открывается окно, в которое нужно вставить аргументы функции. Жмем на кнопку, расположенную справа от поля ввода данных, чтобы приступить к выбору аргумента искомого значения.
4. Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.
5. Точно таким же образом кликаем по значку справа от поля ввода данных, для выбора таблицы, откуда будут подтягиваться значения.
6. Выделяем всю область второй таблицы, где будет производиться поиск значений, кроме шапки. Опять возвращаемся к окну аргументов функции.
7. Для того, чтобы выбранные значения сделать из относительных абсолютными, а это нам нужно, чтобы значения не сдвинулись при последующем изменении таблицы, просто выделяем ссылку в поле «Таблица», и жмем на функциональную клавишу F4. После этого к ссылке добавляются знаки доллара и она превращается в абсолютную.
8. В следующей графе «Номер столбца» нам нужно указать номер того столбца, откуда будем выводить значения. Этот столбец располагается в выделенной выше области таблицы. Так как таблица состоит из двух столбцов, а столбец с ценами является вторым, то ставим номер «2».
9. В последней графе «Интервальный просмотр» нам нужно указать значение «0» (ЛОЖЬ) или «1» (ИСТИНА). В первом случае, будут выводиться только точные совпадения, а во втором — наиболее приближенные. Так как наименование продуктов – это текстовые данные, то они не могут быть приближенными, в отличие от числовых данных, поэтому нам нужно поставить значение «0». Далее, жмем на кнопку «OK».



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



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



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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Функция ВПР в Excel

ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований.

2020-07-26 23:49:57 368



• Синтаксис
• Как работает функция?
• С одним условием
• С несколькими условиями
• Поиск по нескольким столбцам
• Сравнение двух таблиц
• Поиск в выпадающем списке
• Перенос данных
• Ошибки
• Вместо заключения

ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований. Является разновидностью поиска, но только вертикального (сверху вниз) – извлекает информацию из таблицы или определенного диапазона по строкам. Работает во всех версиях Excel и даже Google Sheets.

Синтаксис

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

Важно! Искомое значение должно находиться слева (в первом столбце) от возвращаемого элемента.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Как работает функция?

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

С одним условием

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



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





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



1. Нажать «Enter» – и появится результат поиска.



Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».





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

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.



Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

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



1. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).



1. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «<>», в которые взята вся формула).



Поиск по нескольким столбцам

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



1. Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;;ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «<>».



1. Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «<>».



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

Важно! Самостоятельно писать фигурные скобки «<>» при вводе формулы нельзя, поскольку это не приведет к какому-либо результату.

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;;ЛОЖЬ)).



Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.





Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

1. В таблице с зарплатой за март добавить еще один столбец.



1. Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.



1. При помощи маркера заполнения протянуть полученное значение вниз.



По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

Для создания раскрывающегося списка:

1. Поставить курсор в ячейку, где он будет располагаться.
2. Перейти в раздел книги «Данные» – «Проверка данных».



1. В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).



1. Нажать «Ок». Отобразится список.



1. В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».



Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).



1. Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)



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



Ошибки

Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.

Ошибка #Н/Д появляется, если:

1. Указанный диапазон не содержит искомый элемент.
2. Искомый элемент меньше, чем минимальный в массиве.
3. Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
4. Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
5. Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
6. В коде есть пробелы или невидимые непечатаемые знаки.
7. Используются значения времени или большие десятичные числа.

Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».







Ошибка #ЗНАЧ появляется, если:

1. В качестве номера столбца указано число 0.
2. Длина первого аргумента превышает 255 знаков.

Ошибка #ССЫЛКА появляется, если третий аргумент больше количества столбцов в таблице.

Вместо заключения

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

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

В программах OpenOffice и LibreOffice используется VLOOKUP, ее синтаксис =VLOOKUP(lookupvalue; datatable; columnindex; mode), а работает функция точно так же, как описано выше.

Источник: sysadmin-note.ru