Во время работы в Эксель нередко требуется перенести или скопировать определенную информацию из одной таблицы в другую. Выполнить подобную процедуру, конечно же, можно вручную, когда речь идет о небольших объемах данных. Но что делать, если нужно обработать большие массивы данных? В программе Microsoft Excel на этот случай предусмотрена специальная функция ВПР, которая автоматически все сделает в считанные секунды. Давайте посмотрим, как это работает.
Описание функции ВПР
Смотрите также: “Как убрать колонтитулы в Экселе”
ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.
С помощью данного инструмента Excel можно отыскать совпадение значения в заданном столбце и “подтянуть” необходимые данные в выбранную ячейку из другой таблицы. Чтобы было понятнее, давайте разберем, как работает функция на практическом примере.
Применение функции ВПР на практике
Допустим, у нас есть таблица с продажами товаров. В ней отсутствуют данные по цене каждой позиции, однако, они есть в другой таблице рядом.
Функция ВПР excel за 5 минут ► VLOOKUP in Excel for 5 minutes
Наша задача – заполнить недостающие в основной таблице цены, чтобы мы могли посчитать итоговые суммы по продажам.
Смотрите также: “Автофильтр в Excel: как сделать, использовать, отменить”
Порядок действий в данном случае следующий:
- Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
- В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
- Теперь предстоит правильно заполнить аргументы функции:
- в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
- переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
- Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
- в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
- в значении аргумента “Интервальный_просмотр” можно указать два значения:
- ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
- ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
- мы выбираем первый вариант, так как нам важна предельная точность.
- Когда все готово, нажимаем OK.
- В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.
Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
- Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
- В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.
Как использовать функцию ВПР (VLOOKUP) в Excel
Заключение
Смотрите также: “Дубликаты в Excel: как найти, выделить или удалить”
Таким образом, функция ВПР – один из самых полезных инструментов при работе в Excel, когда нужно сопоставить данные двух таблиц и перенести значения из одной в другую при совпадении заданных значений. Правильное использование данной функции позволит сэкономить немало времени и сил, позволив автоматизировать процесс заполнения данных и исключив возможные ошибки из-за опечаток и т.д.
Источник: microexcel.ru
Функция ВПР в Excel: как сделать и для чего нужна
ВПР в Excel – поисковая функция. С её помощью можно последовательно просматривать информацию по вертикали и «подтягивать» данные из одной таблицы в другую. Рассказываем, что такое ВПР и как пользоваться VLOOKUP в Excel.
Зачем нужна функция ВПР и когда её применяют
Функция ВПР в Excel (на английском VLOOKUP) служит для поиска нужных данных – номеров телефонов, цен, артикулов товаров и др. После нахождения эти данные можно объединить из нескольких таблиц.
Для примера: в таблицу занесли данные о заказе – наименование товаров, артикулы, количество. Прайс размещён в другой. Наша задача – объединить, не перенося цифры вручную, данные из таблицы заказа со стоимостью товаров так, чтобы каждая цена соответствовала определённому артикулу.
Аргументы функции ВПР
Формула ВПР в Excel имеет 4 аргумента:
- искомое значение в формате текстовой строки, ссылки на какую-либо ячейку, значения – это параметр в первом столбце слева;
- таблица – диапазон для поиска в виде именного диапазона или ссылки на определенный диапазон (это бывает и вся таблица, и отдельные столбцы);
- номер столбца – отсюда берется искомая величина, указывается номер столбца из диапазона;
- интервальный просмотр – 0 или 1.
С ВПР поиск может происходить в разных форматах и иметь разную точность результатов. Если установить значение «0», можно найти точное соответствие. При установке «1» поиск будет приблизительным, он же включен по умолчанию – функция ВПР будет искать схожие значения, если не изменить значение вручную.
Как создать функцию ВПР в Excel: пошаговая инструкция
Предлагаем простую инструкцию по созданию функции ВПР в Excel для чайников с пошаговым описанием действий.
Создаем шаблон
Для примера будем использовать функцию VPR, чтобы найти и подставить необходимые данные из одной таблицы в другую. Допустим, в магазин привезли большой объём товаров – в одной таблице указано количество товаров, а во второй – стоимость каждого товара. Наша задача – подставить значение из прайса в первую таблицу, чтобы узнать, на какую сумму привезли товаров в магазин.
Вставляем функцию ВПР
Чтобы построить функцию ВПР в Эксель:
- Выделите ячейку, куда будут перенесены найденные значения. Поскольку мы будем добавлять стоимость товаров из прайса, в таблице, где указано количество товаров, необходимо справа без пропусков добавить пустой столбец, соответствующий по высоте другим столбцам.
- Дайте столбцу название. В нашем случае он может называться «Стоимость, рублей».
- Выделите ячейку напротив наименования первого товара (это будет строка 2) в столбце с ценой.
- Откройте окно, куда вводится формула ВПР. Это можно сделать через вкладку формулы, в ней есть строка «Вставить функцию». Но проще нажать на «fx» – кнопка расположена слева над буквами, обозначающими столбцы (строка ссылок).
- При нажатии «fx» справа откроется вкладка «Построитель формул». В строке поиска нужно набрать ВПР, а затем нажать «Вставить функцию».
В построителе формул появятся 4 строки, куда можно вводить аргументы функции.
Заполняем аргументы функции ВПР
Функция VLOOKUP в Excel есть на русском языке – это облегчает заполнение аргументов:
- «Искомое значение» используется для того, чтобы обнаружить одинаковые данные для переноса в двух таблицах – в нашем случае это будет наименование товара. Чтобы указать его, нужно поставить курсор в верхнюю графу в окне построителя формул и прописать ячейку, стоящую на пересечении столбца с ценами и первого товара в списке. Это же значение автоматически появится в строке «fx».
- «Таблица» – диапазон, откуда будем получать данные. В нашем случае необходимо отметить диапазон, в котором будут наименования товаров (искомое, всегда должно находиться в первом столбце нашего диапазона) и их стоимость (переносимое). Нужно поставить курсор во вторую строку построителя формул, перейти в первую таблицу с наименованием товаров, выбрать диапазон, в который попадут названия товаров и их цены. После закрепите его – выберите значение диапазона в строке ссылок и нажмите F4 в Microsoft, а для MacOS вместо F4 жмите Cmd + T. Затем протяните функцию до последней нужной строки. При переносе диапазона в построитель формул он должен отразиться и в строке «fx» примерно в таком виде: fx=ВПР(A2;’товарная накладная’!$A$2:$E$49).
- Номер столбца – в нашем случае это столбец со стоимостью товаров. Необходимо поставить курсор в третью строчку построителя формул, ввести значение столбца – считается слева направо. Номер столбца должен отразиться в строке ссылок после точки с запятой.
- Интервальный просмотр определяет точность работы функции. Поскольку мы ищем точную стоимость товаров, вводим 0 в 4-й строке построителя формул. Одновременно ноль должен появиться в строке ссылок после номера столбца.
Если используете приблизительный поиск, желательно сортировать данные, иначе после нахождения нужного значения поиск прекратится.
Завершаем создание шаблона
Когда указан последний аргумент, функция имеет оконченный вид. После этого нужно нажать кнопку «Готово», расположенную в построителе формул внизу справа. В нашем случае в столбце с ценами товаров напротив первого наименования должна появиться цена. Чтобы стоимость подтянулась к остальным товарам из списка, зажмите первую заполненную ячейку столбца с ценой и протяните её до последнего товара в списке. Так функция найдет и перенесёт стоимость оставшихся товаров, но для этого проверьте, закреплён ли интервал при заполнении аргументов.
ВПР и приблизительный интервальный просмотр
Функция вертикального поиска позволяет получать приблизительные данные. Для этого в графе «Интервальный просмотр» достаточно поставить единицу. Такая подстановка не даст найти конкретный товар или конкретную цену, а только приблизительные данные, например, наименования на букву Б, размеры скидки в определённом диапазоне и др.
Чтобы приблизительный поиск был корректным, данные необходимо отсортировать – составить список по возрастанию или по алфавиту в зависимости от исходных данных. Без предварительной сортировки поиск прекращается, как только функция находит первое искомое, хотя ниже могут находиться и другие искомые.
Например, если будете искать товары на букву Б, поиск остановится, как только функция найдёт любое слово, в составе которого есть буква Б, необязательно стоящая первой.
Быстрое сопоставление двух таблиц с ВПР в Эксель
Функция «влукап» позволяет провести анализ двух таблиц в программе Эксель, причём их можно разместить на одном листе. Это удобно, когда, например, надо сверить отгруженные в магазин и проданные товары по количеству позиций и стоимости, записанные в формате xls.
Что нужно сделать:
- в таблице с отгруженными товарами дублируем столбцы – должно получиться два столбца с количеством и два столбца с ценой (поступления и продажи, один заполненный, а второй – пустой);
- затем с использованием ВПР перетягиваем отдельно данные с количеством товаров, отдельно – с ценами (из проданных в поставленные);
- закрепите диапазон ячеек;
- третьей и шестой строками соответственно добавьте по одному столбцу – это будет разница в количестве и в цене;
- в первом столбце с разницей вычитаем от поставленного количества товара количество проданных товаров;
- во втором столбце с разницей делаем то же с ценой.
Как интерпретировать результаты:
- если в столбце с разницей появляется ноль, значит, количество товаров или их цена соответствуют;
- если появляется другое число, например 2, значит, двух позиций не найдено или не соответствуют цены;
- значение может быть отрицательным – значит, есть лишние позиции.
Ошибка #Н/Д означает, что искомая позиция/цена в таблице с проданными товарами отсутствует.
Источник: www.vbr.ru
Как работать с функцией ВПР в Эксель: инструкция для новичков
С помощью функции ВПР (в переводе на английский VLOOKUP) пользователи программы Exсel имеют возможность переставлять данные из одной таблицы в другую со схожими параметрами. Эта услуга подойдёт для тех, кому приходится работать с большими списками. Ведь вписывать каждое значение отдельно может занять очень большое количество времени.
Использование ВПР в программе Excel
Для того, чтобы наглядно разобраться как работает функция ВПР в Excel: поможет пошаговая инструкция на конкретном примере.
Допустим, в магазин канцелярских товаров поступил новый привоз, к которому прилагается соответствующая документация. Администратору торгового зала необходимо рассчитать полную стоимость продукции, имея на руках файл Excel, который содержит две таблицы.
Первая – это список предметов, единицы их измерения и количество.
Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.
Чтобы подсчитать сколько стоит продукция, следует информацию из второй вставить в первую, и с помощью простого умножения произвести расчёт.
Этапы работы (инструкция):
- Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
- Отметить верхнее поле в новом.
- Выбрать раздел формулы, и нажать «Вставить функцию».
- Из предложенных категорий Excel отметить «Ссылки и массивы».
- Найти ВПР, и нажать «ОК».
- Заполнить открывшееся окно «Аргументы».
– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.
– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).
Важно! Эти показатели фиксируются, чтобы именно по ним производились расчёты программой Эксель.
Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.
Номер — это строка в которой должна быть информация о том, что будет переноситься из другой таблицы. В рассматриваемом случае – это второй столбец (2).
Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».
В конечном итоге, окно «Аргументы» выглядит так:
Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.
Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.
Как сравнить две таблицы: пошаговая инструкция для «чайников»
Функция ВПР поможет сравнить две таблицы Excel в считанные секунды, даже если данные занимают не один десяток значений. Пошаговая инструкция:
Допустим, что к тому же администратору торгового центра снова привезли товар, но предупредили, что стоимость у некоторых предметов изменились. Как сравнить две таблицы функцией ВПР в Эксель?
Делается это в несколько шагов:
- Открыть первую со старой информацией.
- Добавить дополнительный столбик для новых данных «Новая стоимость».
- Выделить первое пустое поле в созданном столбце (С2).
- Выбрать раздел «ВПР Формулы» и «Вставить функцию».
- Найти категорию Excel «Ссылки и массивы».
- Выбрать ВПР.
- Задать «Аргументы».
– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).
– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.
Номер столбца – второй, так как именно стоимость переносится в новую.
Интервальный просмотр – ЛОЖЬ.
Заполненное окно выглядит так:
После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.
Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.
- Как перевести файл Excel в PDF,
- Как перевести файл Pdf в Ворд онлайн,
- Как делать ссылки в Word: пошаговая инструкция.
Поиск с помощью ВПР по нескольким условиям
Если пользователю программы Excel необходимо из большого каталога найти необходимые данные, он может воспользоваться данным способом для чайников (инструкция).
Итак, имеется документ, в котором обозначены: компании, товары и цены.
Нужно найти цену на конкретный товар – гелевая ручка. Но так как каталог может быть огромным, а гелевые ручки быть не у одной компании, поиск стоимости в Эксель лучше проводить через ВПР с несколькими условиями: название компании и предмета.
Чтобы осуществить поиск следует:
- Создать слева новый столбец с объединёнными данными (название компании и товара).
Делается это просто:
- выделить крайнюю левую ячейку (А1);
- щёлкнуть ПКМ и выбрать «Вставить»;
- отметить добавление столбца и нажать «ОК».
- Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2https://virtualniy-mir.ru/kak-rabotat-s-funkciej-vpr-v-eksel-instrukciya-dlya-novichkov/» target=»_blank»]virtualniy-mir.ru[/mask_link]