Срок доставки товара в течении 1-3 дней !!!
|
|
МЕНЮ. Впр эксель как работать
ВПР Excel - что это такое? Узнайте, как работает функция ВПР в Excel
ВПР Excel – это функция в соответствующей программе, которая отличается красотой и простотой. Она имеет множество различных применений, её используют в абсолютно разных сферах: начиная от обучения и заканчивая розничной торговлей. Основная концепция функции заключается в том, чтобы искать совпадения в одной или нескольких таблицах. Так можно легко найти интересующую информацию, затратив минимум времени.
Общая информация
Функция ВПР Excel – что это такое? Её также называют VLOOKUP в англоязычной версии. Это одна из самых распространённый функций массивов и ссылок. Специалисты, составляющие шкалу BRP ADVICE, выставили уровень сложности, приравниваемый к 3 или 7.
Рассматриваемая функция позволяет быстро найти в большой таблице те значения из строк или столбцов, которые необходимы пользователю. Первый параметр эта программа находит самостоятельно. Столбец указывается пользователем. Есть другая функция ГПР, где человеком отмечается строчка. Столбец она находит самостоятельно.
Если пользователь уже имел дело со ссылками и массивами, то разобраться в действиях ВПР будет просто. В разных табличных документах можно сделать сноску на конкретную ячейку. Её ставят в пример или, напротив, указывают в качестве исключения. В задании для ВПР обычно указывается ячейка в виде A1, D9, K8 и так далее.
Иногда ссылка подаётся в другом виде (R1C1). Одним словом, отмечается столбец и строчка, на пересечении которых и находится нужная для пользователя информация. Программа получает точное указание, где ей надо искать эти данные.
Параметры функции на примере
В функции ВПР Excel сначала указывается номер строки, затем следует обозначение столбца. Приблизительно должно получиться что-то вроде:
=ВПР(А1;База_данных;2;ЛОЖЬ)
Параметры функции при этом означают следующее:
- А1. Это приблизительная ссылка на ячейку. В ней может указываться любое значение, в зависимости от результата, который пользователь хочет получить.
- База_данных. Имя той области информации, которую предстоит искать. Понятие не настолько обширное, как предыдущее. Его можно использовать только по первым строкам или столбцам.
- 2. Это порядковый номер столбца, откуда программа будет черпать информацию.
- ЛОЖЬ. Указывает на поиск точного совпадения. Иногда указываются другие дополнительные параметры этого слова. И программа при этом будет искать все совпадения и определять ближайшие значения.
К сожалению, функция не может работать более профессионально. Некоторые данные пользователю нужно держать в голове (номер строки или столбца). В противном случае он не сможет найти нужную ему информацию.
Аргументы ВПР
Чтобы говорить о том, как работает функция ВПР в Excel, нужно ознакомиться с её аргументами. Первым является искомое значение. Оно задаёт параметры поиска, который будет искать программа в первом столбце таблицы. Она не может работать со вторым и последующими, функция попросту не найдёт эту информацию. Внутри формулы этот аргумент указывается в кавычках. Исключения составляют наименования функций.
Третий аргумент – номер столбца. Здесь указывается информация, которую ищет пользователь. К примеру, он может посмотреть должность, соответствующую фамилии из первого столбца, его заработную плату, отметки и так далее. Всё зависит от сферы деятельности пользователя.
И последний аргумент – интервальный просмотр. Здесь указывается «1» и «0» либо «Ложь» и «Правда». В первом случае данные будут означать, что заданный поиск является приблизительным. Тогда программа начнёт искать все совпадения. Если применяется второй вариант, тогда функция будет обращать внимание только на точные значения.
Распространённые ошибки
Функция ВПР Excel никогда не будет работать со сбоями, если отмечается неправильная задача. То есть в любых нарушениях виноват только пользователь. Есть три распространённые ошибки. Во-первых, человек часто путается в аргументах «ложь» и «истина». Первый ориентирован на поиск точного совпадения. Если указывать «истина», тогда функция подбирает приблизительные.
Во-вторых, формула ВПР Excel не может обозначаться так, чтобы поиск начинался со второго или последующего столбца. Найти данные можно только по первому. Поэтому если пользователь вводит какую-то другую формулу, отличающуюся от правил, то программа просто не сможет её распознать.
И, в-третьих, нередко неправильно указывается номер столбца, откуда нужна информация. В этом случае нужно перепроверить данные.
Когда используют функцию ВПР?
Об этом стоит поговорить детально. Ни для кого не секрет, что абсолютно в разных сферах используется функция ВПР Excel. Инструкция по её применению может показаться сложной, но только на первый взгляд. Иначе она бы не стала настолько распространённой.
Помните о главном: функция ищет информацию по вертикали, то есть – по столбцам. Её применяют в разных ситуациях:
- Когда надо найти информацию в большой таблице либо отыскать все повторяющиеся данные и совпадения.
- В преподавательской среде. Учитель всегда может по фамилии своих учеников в считаные мгновения найти их посещаемость, успеваемость и другую информацию. В особенности полезной она является, когда список студентов большой, а преподаватель не может запомнить каждого из них.
- В розничной торговле. Если использовать функцию для поиска цены, состава или артикула товара, то человек сможет быстро ответить на расспросы покупателей.
Одним словом, в любой среде, когда нужно найти данные из таблицы, можно использовать ВПР.
Как использовать ВПР в таблице?
Разобраться в этом нетрудно. Для того чтобы формула ВПР Excel действительно работала, первоначально нужно сделать таблицу. Также для полноценного использования функции необходимо минимум два столбца, максимальное количество таковых – не ограничено.
Затем в пустую ячейку нужно ввести эту формулу, куда пользователь задаёт параметры поиска совпадений и информации. Пустая ниша может располагаться где угодно: сверху, снизу, справа. Ячейки потребуется расширять, чтобы найти данные. Так как они располагаются в своих столбцах, то их потребуется минимум две. Если параметров поиска больше, то и количество ячеек увеличивается. Затем осуществляется проверка работы функции и то, насколько верно написана формула. Для этого кликают на «просмотр значений». Можно выявить несоответствия в формуле.
Рекомендации по использованию функции
ВПР на английском Excel и на русском аналоге применяется одинаково. Но есть пара советов от профессионалов. Чтобы функция работала лучше, особенно после изменения данных, рекомендуется вводить значок доллара между массивами. Например, не A1, а А$1$. Когда вбиваются первичные значения, то никаких символов и пробелов между названиями строк и столбцов не нужно ставить.
Также рекомендуется тщательно проверять таблицу, чтобы не было лишних знаков препинания или пробелов. Их наличие не позволит программе нормально заниматься поиском совпадений, особенно когда тот лишь приблизительный (по параметру «Истина»).
Заключение
Функция ВПР Excel (вертикальный просмотр) является простой для опытного пользователя. Но неискушённому в вопросе человеку нетрудно будет ознакомиться с правилами её использования. После их освоения пользователь сможет быстро находить информацию, причём неважно, насколько обширной будет таблица. Если необходимо воспользоваться горизонтальным просмотром, то задействуют функцию ГПР.
fb.ru
Подстановка данных в excel функция ВПР
Довольно распространенная ситуация, когда данные из одной таблицы в excel надо подставить в другую.
Рассмотрим на примере: допустим есть 2 таблицы — продажи и прайс-лист. Задача-подставить цены из таблицы прайс-лист в таблицу продажи, чтобы можно было в итоге посчитать общую сумму продаж.
Предлагаю 2 варианта выполнения этой задачи.
Вариант 1. Использовать функцию ВПР. скачать пример
Функция ВПР ищет заданное значение в крайнем левом столбце указанной таблицы, двигаясь сверху вниз. В нашем примере функция ВПР будет выполнять поиск «корм для кошек» в таблице «прайс-лист» и найдя его, подставит цену корма для кошек в таблицу «продажи». Ровно так, как показано на рисунке выше. Сразу хочу отметить 2 важных условия для удачной работы формулы: 1-столбец «наименование товара» в обоих таблицах должен иметь одинаковый формат, 2-столбец «наименование товара» в таблице прайс-лист должен быть отсортирован по возрастанию.
Все, вызываем функцию ВПР. Щелкаем в той ячейке, куда будет подставляться цена( С5 нашего примера), далее жмем значок fx на панели инструментов (либо Вставка-функция) и в открывшемся окошке выбираем ссылки и массивы и далее ВПР. Как показано на картинке.
и жмем ОК. Откроется следующее окно, в котором и задаются параметры подстановки:
искомое значение — щелкаем по той ячейке, в которой находится искомое значение — у нас это корм для кошек
таблица — это таблица, из которой берутся данные. Щелкаем на квадратик с красной стрелкой и мышкой обводим нашу таблицу прайс-лист, жмем Enter
номер столбца — здесь нужно указать именно порядковый номер столбца таблицы из которой будут браться цены. В нашем примере столбец номер один-наименование, столбец номер 2-цена. Таким образом, мы ставим цифру 2
интервальный просмотр - здесь можно ввести либо ЛОЖЬ либо ИСТИНА. Других вариантов нет. Можно либо словами написать, либо ввести цифру 0 или 1. 0-ЛОЖЬ, 1-ИСТИНА. Если вводим ЛОЖЬ — выполняется поиск точного соответствия заданному параметру, если вы введете ИСТИНА, то таким образом Вы даете разрешение на поиск приблизительно соответствия, то есть поиск максимально похожего заданному параметру. Чтобы было меньше ошибок, лучше всегда указывать ЛОЖЬ, т.е. поиск точного соответствия.
Все, нажимаем ОК и радуемся:)
Для еще большего удобства работы с такой формулой, можно таблице прайс-лист присвоить имя, и потом в формуле указывать не диапазон поиска, и просто имя таблицы. Для этого выделяем таблицу прайс-лист и жмем правой кнопкой мыши, там находим «имя диапазона»
В новом открывшемся окне пишем имя диапазона, например «прайс»
И второй способ решения данной задачи — подстановка данных в excel через функцию СУММЕСЛИ
 | ПРОДВИНУТЫЙ КУРС по работе в excelВ этом курсе Вы узнаете:
|
Popularity: 41% [?]
excelru.ru
Функция ВПР
Функция ВПР одна из наиболее важных функций, любой аналитик должен знать, как она работает, особенно если Вы работаете с большим массивом данных. Данная формула позволяет подтянуть значения из одной таблицы в другую. Например, в отчете (таблице) у Вас наименование отсортировано по названию, а при выгрузке из БД (базы данных) в хаотичном порядке, или количество наименований не совпадает, кода вам нужно подтянуть к Вашему ассортименту цены из прайса… Тут нет возможности скопировать столбцы, иначе данные перемешаются и будут не верными. Вот в этих случаях приходит на помощь формула ВПР.
Как работает функция ВПР?
ВПР(искомое_значение,таблица,номер_столбца,[интервальный_просмотр])Важно! Программа не умеет думать как человек, она четко сопоставляет факты. Если для нас название «Труба1» и «Труба 1» это одно и то же, то для Excel это разные вещи, т.к. в первом случае нет пробела перед 1, а во втором случае есть.
Теперь разжуем что к чему:
- ВПР – название функции
- Искомое значение – по этому значению мы будем подтягивать необходимые данные. Искомое значение, как правило, бывает код товара, который идентичный в любой базе данных. Если наименование номенклатуры может различаться, то код товара всегда одинаков, поэтому лучше всего использовать его. Для выделения диапазона данных не нужно мучатся, и выделять точное количество строк. Выделите весь столбец, нажав на номер или букву столбца.
- Таблица – это диапазон от столбца Искомого значения до подтягиваемых данных со страницы подтягиваемых данных, т.е. с другой страницы или книги. Так же выделяем столбцы. Важно! Искомое значение всегда должно быть справа от подтягиваемых данных.
- Номер столбца – порядковый номер столбца от искомого значения до подтягиваемых данных. Когда Вы выделяете диапазон, обратите внимание, Excel подсказывает Вам какой номер последнего выделенного столбца рядом с курсором мыши.
- Интервальный просмотр, что это такое знать вовсе не обязательно, просто ставьте всегда 0
Теперь рассмотрим эту функцию на примере:
Открываем книгу Excel
Давайте сделаем произвольную таблицу с тремя столбцами код товара, номенклатура, сумма период 1
При создании таблицы можно воспользоваться протягиванием. Данные вносим любые…
Теперь копируем нашу таблицу на другую страницу и меняем столбцы номенклатура (порядок наименований) и сумма период 2. Вот так, например:
Теперь возвращаемся на Лист1 и добавляем столбец Сумма период 2, прописываем формулу в столбце сумма период 2. Ставим курсор на первый товар и пишем =ВПР(, теперь выделяем мышкой столбец «код товара» это есть искомое значение и ставим точку с запятой(;) формула приобретает следующий вид =ВПР(А:А;
Дальше переходим на страницу или книгу с нужными для подтягивания данными и выделяем мышкой диапазон от искомого значения(код товара) до подтягиваемых данных (сумма период 2). Смотрим подсказку – номер столбца (ну или считаем самостоятельно) в нашем конкретном случае это третий столбец. Ставим точку с запятой (;) и пишем 3. Получаем следующий вид формулы:
=ВПР(А:А;Лист2А:С;3;
Проставляем 0 и закрываем скобку =ВПР(А:А;Лист2!А:С;3;0). Протягиваем по всем наименованиям и получаем результат :) Для быстрого протягивания можно воспользоваться комбинациями клавиш:
Ставим курсор на первый столбец, нажимаем Ctrl+стрелочка вниз, оказываемся в низу таблицы. Теперь Ctrl+стрелочка вправо, отпускаем Ctrl и еще один раз вправо. Мы должны оказаться в столбце, где прописана наша формула внизу таблицы. Теперь нажимаем Shift+ Ctrl+стрелочка вверх, таким образом, выделяем диапазон до написанной формулы и нажимаем Ctrl+D. Готово.
Важно! Если вы хотите переслать или скопировать в другую книгу полученный результат, Вам нужно избавиться от формулы. Если этого не сделать в новой книге расчеты могут слететь, т.к. путь для расчетов пишется на Вашем локальном компьютере. Для избавления от формулы нужно скопировать столбец или диапазон таблицы и вставить через специальную вставку как значения.
БОНУС - КНОПКА ВСТАВКИ ЗНАЧЕНИЯ.
Для удобства и ускорения работы, кнопку специальной вставки как значение можно вывести на панель Excel. Как это работает? Выделяем полностью столбец или диапазон мышкой нажимаем комбинацию клавиш Ctrl+C (комбинация копирования), далее нажимаем кнопку специальной вставки, которую мы сейчас выведем в панель Excel. И все ГОТОВО!
Итак, выводим кнопку:
Идем в Файл/Параметры Excel/Панель быстрого доступа/
Выбираем нужную кнопку и добавляем в панел быстрого доступа. Нажимаем Ок и готово
Наша кнопка появилась в левом верхнем углу. Туда же можно добавить кнопку "очистить все". Данная кнопка удаляет и значения и форматы, это кнопка тоже нам понадобиться для создания программы.
СПЕЦИАЛЬНО ДЛЯ ВАШЕГО УДОБСТВА!
Скачать данный файл Функция ВПР
Понравилась статья? Отблагодари автора ;)
Предыдущая глава Формулы Excel
Следующая глава Функция ЕСЛИ
nvweb.ru
ВПР в excel 2018
Функция ВПР позволяет быстро заполнить таблицы, имея в документе искомые данные. Рассмотрим этот процесс на примере Microsoft Excel 2007.
ВПР означает вертикальный поиск решений. Существует еще и ГПР — горизонтальный поиск решений, но остановимся мы именно на ВПР. С помощью этой функции можно найти данные в одной таблице с последующим их выводом в другую ячейку другой таблицы.
Работать ВПР будет при наличии двух условий:
- Если в сопоставляемых элементах есть одинаковые элементы.
- Если сопоставляемые данные в первой таблице будут отсортированы по возрастанию.
Примером будет являться изображение чуть ниже.
Как видно, в первой таблице данные в колонке «Сумма» идут по возрастанию, а во второй таблице присутствуют те же фамилии, что и в первой. Список фамилий во второй таблице может быть неполным, а также они могут располагаться в произвольном порядке.
ВПР в этом случае поможет заполнить пустые ячейки колонки «Сумма» второй таблицы. Конечно, если таблицы небольшие, данные можно прописать и вручную, но с огромными таблицами придется повозиться. ВПР же поможет сократить время заполнения.
Чтобы во вторую таблицу вставить сумму, выбираем нужную ячейку, но первоначально на панели инструментов нужно переключиться со вкладки Главная на Формулы. Исходя из примера, нужно выделить пустую ячейку напротив фамилии «Иванов» во второй таблице (В11). В ней нужно поставить знак равенства и на панели инструментов выбрать кнопку Вставить функцию. Откроется Мастер Функций. В разделе Категория «10 недавно использовавшихся» нужно сменить на «Полный алфавитный перечень», после этого в разделе Выберете функцию найти ВПР.
После нажатия ОК откроется еще одно окно, которое будет называться Аргументы функции. В графу Искомое значение нужно добавить фамилию, по которой будет производиться сопоставление. Для этого нужно выделить ту фамилию, рядом с которой был поставлен знак равенства. В данном случае это фамилия Иванов (А11).
В графу Таблица нужно добавить диапазон первой таблицы. Для этого нужно выделить все данные первой таблицы за исключением шапки.
В графе Номер столбца нужно поставить цифру 2. Это будет означать, что требуется перенести в новую таблицу сумму по фамилии, которая располагается во втором столбце выделенного диапазона первой таблицы. Соответственно, в зависимости от расположения таблицы, это число может быть иным. Кратко говоря, в данном случае «2» означает номер столбца в таблице (столбец В). Если данные располагаются в столбце С, то число будет не 2, а 3, D — 4 и так далее.
Теперь можно нажать ОК. Результат:
Теперь нужно создать абсолютную ссылку. Сделать это очень просто. Требуется обратиться к строке состояния. Для этого необходимо выделить ячейку с суммой, которая была получена через ВПР. В данном случае нужно выделить ячейку В11.
Теперь в строке состояния нужно найти два адреса ячеек и поставить перед ними знак $. Сравните, что было, и что стало после добавления этого знака.
Теперь эту формулу нужно скопировать и вставлять в остальные пустые ячейки. Это позволит быстро заполнить оставшиеся пустые области. Обратите внимание, каждый раз, вставляя формулу, нужно будет менять Искомое значение. 
То есть, если мы скопировали формулу =ВПР(A11;$A$4:$B$6;2), где А11 — фамилия Иванов, то чтобы заполнить ячейку с Сидоровым, нам нужно А11 заменить на А12. Результатом будет =ВПР(A12;$A$4:$B$6;2). Адреса ячеек нужно брать из второй таблицы, а не первой.
faytan.ru
