Как пользоваться впр в экселе
Даже, если Вы не часто открываете Excel, будет очень полезно знать впр эксель и как пользоваться этой функцией. Статья рассказывает, как работает функция впр в excel для чайников. Здесь Вы найдете как работает впр в excel пошагово с примерами, их скриншотами и описанием.
Для более комфортного пользования функций ВПР будет полезным знать, как работают ссылки в Excel, и какие существуют правила закрепления ссылок.
Функция впр в excel для чайников
Функция ВПР – расшифровывается, как вертикальный просмотр. Это, казалось бы мало практичная деталь, позволяет разобраться в принципе работы функции ВПР.
Если Вы знаете принцип работы функции, то можно пролистать до следующего блока.
Представьте, что Вам нужно заполнить таблицу с ведомостью по зарплатам сотрудников конкретного отдела.
У Вас уже есть шаблон ведомости, в котором заполнены табельные номера и ФИО.
Осталось внести суммы зарплат и собрать подписи.
Шаблон зарплатной ведомости
Сведения о заработной плате находятся в другой таблице, например в общем реестре предприятия, где есть табельный номер и зарплата каждого сотрудника.
Без функции Вы бы искали каждый табельный номер в общем списке сверху вниз, потом переписывали или копировали бы з/п напротив нужного табельного в свою таблицу.
Заполнение таблицы вручную
Собственно, по такому же принципу и работает ВПР, делает просмотр сверху вниз (вертикально) до требуемого значения идентификатора и забирает значение напротив, как результат вычисления.
ВПР эксель как пользоваться
Функция ВПР находится в разделе функций ссылок и массивов.
Как найти ВПР
Функцию ВПР обычно вставляют в ячейку, в которой будет итоговый результат, в нашем случае зарплата конкретного сотрудника в ведомости.
Функция впр в excel примеры: где писать функцию
Теперь нужно последовательно заполнить четыре аргумента функции ВПР.
Искомое значение – значение для поиска. Значение, которое вы хотите найти. Поиск этого значения будет в первом столбце диапазона ячеек, указанного в аргументе — Таблица.
В нашем примере с зарплатной ведомостью – это табельный номер первого сотрудника в ведомости. Поэтому мы в этом аргументе можем просто указать адрес ячейки табельного в таблице ведомости.
Функция впр в excel примеры: Искомое значение.
Таблица – диапазон ячеек, в котором будет выполнен поиск искомого значения и возвращаемого значения с помощью функции ВПР. Первый столбец в диапазоне ячеек должен содержать искомое значение. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.
В нашем случае – это таблица общего реестра предприятия. При этом первым мы будем делать столбец, в котором функция будет искать табельный номер. Последний столбец не так важен, главное, чтобы в таблицу входил столбец, из которого мы будем забирать значение.
Если Ваша формула будет протягиваться, то обязательно закрепите диапазон таблицы используя клавишу F4.
Функция впр в excel примеры: Таблица
Номер столбца – номер столбца начиная с 1 для самого левого столбца Таблицы, содержащего возвращаемое значение.
В текущем примере у нас таблица начинается со столбца «Табельный номер» и заканчивается столбцом «Зарплата», поэтому номер столбца будет 2. Т.к. он второй в нашей таблице.
Функция впр в excel примеры: Номер столбца.
Интервальный просмотр – число 0 или 1.
0 (Ложь [False]) Точное совпадение–осуществляет поиск точного значения в первом столбце(если не находит –#Н/Д [#N/A]).
1 (Истина [True]) Приближенное соответствие–если нет совпадения, то выдает max_значение
В примере с ведомостью нам нужно строгое соответствие табельного номера, как и в большинстве случаев на практике. Мы обязательно сделаем отдельную статью, про то, как правильно использовать 1 в этом аргументе.
А пока просто запомните, что здесь всегда ставим 0.
Что такое впр в excel
Вопросы по покупке sales@onlyoffice.com
Запросы на партнерство partners@onlyoffice.com
Запросы от прессы press@onlyoffice.com
Следите за нашими новостями:
© Ascensio System SIA 2023. Все права защищены
© Ascensio System SIA 2023. Все права защищены
Не пропустите обновление!
Получайте последние новости ONLYOFFICE на ваш email
Имя не указано.
Email не указан.
На ваш адрес электронной почты отправлено сообщение с подтверждением.
В Справочном центре ONLYOFFICE используются файлы cookie для обеспечения максимального удобства работы пользователей. Продолжая использовать этот сайт, вы соглашаетесь с тем, что мы можем сохранять файлы cookie в вашем браузере.
ВПР в Excel: пошаговая инструкция для чайников
Функция ВПР существенно упрощает обработку данных в Excel. Она предназначена для автоматического переноса информации из одного файла в другой. Предлагаем подробный обзор того, как работает функция ВПР, какие у нее есть возможности и варианты использования.
Бизнес
Читайте также:
Зачем нужна функция ВПР и когда ее используют
Альтернативное название ВПР в Excel — Vlookup. Инструмент перетаскивает значения в соответствии с заданной формулой. Рассмотрим на примере, как он работает. Компания торгует мебелью. У менеджера две таблицы: в одной приведены характеристики товаров и стоимость, а в другой — контакты покупателей, которые оставили заявки. 
Менеджеру удобнее, чтобы в таблице с заявками отображались цены. Функция ВПР в Excel объединит информацию из двух документов и выведет стоимость каждой позиции в соответствующей ячейке. Область применения формулы ВПР не ограничивается торговлей. Другие примеры использования:
- учет имущества, финансов;
- кадровое делопроизводство;
- сопоставление данных из двух таблиц.
Vlookup ценят за возможность быстро найти и перенести нужные данные — вручную процесс займет в разы больше времени.
Как пользоваться ВПР: руководство с примерами
Опишем, как работает ВПР. Программа исследует обозначенный диапазон до тех пор, пока не обнаружит указанный элемент (до первого совпадения). После этого копирует его в назначенную ячейку.
Вставляем формулу
С ВПР в Excel удобнее работать в одном документе, размещая таблицы на разных листах. Рассмотрим порядок действий.
Добавляем колонку со стоимостью в лист 2 (в примере он назван «Заявки»). Выделяем верхнюю ячейку нового столбца. В представленном образце добавлен еще один параметр — «Стоимость» (программа автоматически рассчитает чек заказа).
В верхней части рабочей панели перейдите в раздел «Формулы». Слева найдите инструмент «fx» (вставка функции). Когда нажмете, появится окно со списком доступных функций — выберите «ВПР».
Заполняем аргументы
Предстоит ввести четыре параметра: искомое значение (модель, цена, характеристика и так далее), таблицу (область поиска), номер столбца (порядковый), интервальный просмотр. Разберем каждый из них подробнее.
Искомое значение. Им в формуле ВПР служит содержимое ячейки, общее для обеих таблиц. Впоследствии программа сканирует диапазон первого листа, пока не найдет указанный идентификатор. В рассматриваемом варианте это «Аэлита». Активируем ячейку — «координаты» появятся в поле ввода. В строке формул автоматически появился фрагмент функции: «=ВПР(D3)».
Таблица. В поле «Таблица» указываем область данных (берем ее из первого листа — у нас это «Каталог»). Функция работает корректно, если элемент, который вы ищете, размещен в первом столбце. Теперь документ отображается так:
Закрепляем диапазон («F4» для Windows, комбинация «Cmd+T» для macOS). Фиксация диапазона дает возможность распространить формулу ВПР в Excel на все строки столбца. Теперь в строке формул отображается выражение «=ВПР(D3; Каталог!$A$2:$E$21)».
Номер столбца. Введем порядковый номер колонки, в которой указана искомая характеристика. «Стоимость» находится в пятом столбце. Теперь вверху отображается формула «=ВПР(D3;Каталог!$A$2:$E$21;5)».
Интервальный просмотр. Поле воспринимает два значения: «0» или «1», и определяет точность поиска. Для абсолютного совпадения установите «0». Если вас устроит приблизительное соответствие, введите «1». Теперь в строке формул отображается функция ВПР в окончательном варианте: «=ВПР(D3;Каталог!$A$2:$E$21;5;0)».
Получаем результат
Как только вы введете четыре аргумента и нажмете «Ок», в ячейке, в которую введена формула, увидите результат поиска.
Выделите ячейку с результатом, установите курсор на квадратик в нижнем правом углу и потяните его вниз, зажав кнопку компьютерной мыши.
Программа автоматически перенесет стоимость заказанной мебели из первого листа во второй. Ошибки исключены.
Аналогичным образом протягивается формула для колонки «Стоимость заказа». На изображении выше в 18-й строке в колонке с ценами отображается «#Н/Д». Это означает, что формула ВПР в ячейке некорректна.
Почему не работает ВПР в Excel
Ошибки возникают по разным причинам. Рассмотрим типичные варианты.
В ячейке F18 выводится «#Н/Д». Разберемся, почему это произошло. Проверим, есть ли искомое значение в листе «Каталог» (для 18-й строки это «Хилтон»). Оно есть, но написано с ошибкой — «Хиллтон». Так как одна буква не совпадает, приложение воспринимает это как отсутствие значения. Также ВПР не выведет результаты, если в ячейках с требуемыми критериями поиска присутствуют лишние пробелы или замена символов (латиница вместо кириллицы).
Часто пользователи забывают зафиксировать диапазон. В этом случае формула нашего примера отобразится в таком виде: «=ВПР(D3;Каталог!A2:E21;5;0)» (отсутствует знак $). Тогда таблица будет выглядеть так:
Вследствие смещения диапазона во многих ячейках отображается ошибка. Решение: установите «$» перед всеми координатами формулы.
Если диапазон обозначен некорректно, ВПР не выведет нужные результаты. Выделяйте все ячейки первого листа, которые несут полезную информацию (кроме заголовков, номеров колонок). Столбец с элементами, по которым проводится поиск, всегда размещайте первым. Перенесите колонку с нужными данными в начало листа или выделяйте диапазон так, чтобы колонка с параметром поиска размещалась слева от первой.
Как использовать формулу ВПР для сравнения двух таблиц
Давайте узнаем, какие названия моделей отсутствуют в первом листе. Удалим из него три строки:
Изменения отразились в листе «Заявки» — в трех строках возникло выражение «#Н/Д». Именно эти модели мы удалили вначале.
Проводить такое сравнение можно по любым другим параметрам: стоимости, цене, характеристикам товара.
Пример
Есть два прайса, менеджер хочет сравнить старые цены с новыми. Данные представлены в таком виде:
Подтянем значения из первой таблицы во вторую с помощью формулы ВПР:
Если обновились не только цены, но и ассортимент, результат получится такой:
Выглядит не очень эстетично. Скорректируем с помощью функции «ЕСЛИОШИБКА». После знака «=» в строке формул введите «ЕСЛИОШИБКА([ВПР — формула]); значение, которое выводится при ошибке)». В нашем случае формула получила вид: «=ЕСЛИОШИБКА(ВПР(D3;$A$3:$B$15;2;0);0)». А таблица стала выглядеть так:
Привлекайте, конвертируйте
и анализируйте ваших клиентов
Платформа омниканального маркетинга
Коротко о главном
- ВПР — функция Excel, предназначенная для поиска и переноса данных из одного листа в другой.
- В общем виде формула выглядит так: ВПР (искомое значение; диапазон; номер столбца, в котором находятся нужные данные; тип интервального просмотра — «1» или «0»). «1» обозначает приблизительное совпадение («истина»), «0» — точное («ложь»).
- Порядок запуска ВПР: перейдите в «Формулы», выберите «вставить функцию», затем — «ВПР». Далее введите аргументы функции, нажмите «Ок». Выделите первую ячейку с введенной формулой, установите курсор на квадратик внизу справа, протяните по всему столбцу, чтобы воспользоваться автозаполнением.
FAQ
Как написать формулу ВПР в Excel?
Существуют два способа:
— Найдите во вкладке «Формулы» опцию «Вставить функцию» и нажмите на нее.
— Кликните на «fx» в строке формул в одной из вкладок таблицы. Справа вы увидите окно «Построитель формул» — найдите в нем функцию ВПР и кликните на «Вставить функцию».
Почему выходит значение «0»?
Самая распространенная причина — ошибка во вводе аргумента «номер_столбца» или указание числа меньше единицы для значения индекса.
Почему ВПР выдает некорректные данные?
Возможно, типы информации не идентичны. Например, если вы используете ВПР в численном значении, а исходные данные сохраняются в виде текста, формула работать не будет.
Функция ВПР в Excel. Что это такое и как пользоваться
ВПР — самая главная функция в программе Excel. Да, большинство пользователей программы чаще используют другие функции — ЕСЛИ, СУММ, СРЕДНЕЕЗНАЧЕНИЕ и другие. Но вся мощь Excel заключена именно в инструменте ВПР.
Кстати, в образовательном центре “РУНО” есть практический курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, на котором можно узнать всё про вычисления в программе с помощью функций, про условное форматирование, функциях ВПР, ГПР и множестве других полезных инструментов.
В этой статье мы подробно рассмотрим как использовать функцию ВПР и чем она может помочь при работе с данными.
1. Что такое ВПР
ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Дословно ВПР расшифровывается как «вертикальный просмотр».
В английском интерфейсе для её обозначения используется термин VLOOKUP, означающий то же самое, или VPR, являющийся калькой русской аббревиатуры. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска должны отображаться в первом столбце таблицы, а столбцы поиска находятся правее.
2. Зачем нужна функция ВПР в Excel
На первый взгляд функции в Excel — это сложно. Но это только поначалу. Ключом к успешному использованию функции ВПР является освоение некоторых основ. Читайте дальше для полного понимания.
По сути, ВПР позволяет искать конкретную информацию в электронной таблице. Например, в списке продуктов с ценами, вы можете автоматически проставить цены товаров, используя отдельный прайс-лист. С помощью алгоритма подстановки с помощью функции ВПР Excel всё сделает за вас.
3. Разбираем на примере
Рассмотрим пример: допустим, клиент разместил заказ на отгрузку овощей. Он указал название товара в столбце “Товар” и желаемое количество в столбце “Вес”.
После чего, ожидаемо, клиент хочет увидеть итоговую стоимость своего заказа. И наша задача как-ра рассчитать эту итоговую стоимость заказа.
Что для этого нужно? В первую, очередь, нам необходимо заполнить столбец “Цена”.
Проблема состоит в том, что цена на каждый товар указана в прайс-листе. И для того, чтобы узнать стоимость мы должны сопоставить каждый вид товара с прайс-листом и заполнить нужные ячейки его стоимостью.
Однако, если мы ходим сэкономить наше время, воспользуемся функцией ВПР. Как же она работает? Функция ВПР производит поиск по первому столбцу и извлечения нужные нам данные из определенного столбца в таблице.
Итак, на вкладке “Формулы” — “Функции и массивы” находим функцию ВПР
Открывается окно “Аргументы функции”, где необходимо заполнить четыре аргумента.
Что же мы будем искать? Допустим, необходимо найти стоимость товара “Мытая морковка”.
Для этого ставим курсор в графу “Искомое значение” и нажимаем на ячейку с названием товара.
Далее, где мы будем искать этот товар? Логично, что нужно обратиться к прайс-листу. Для корректного поиска мы должны обозначить весь диапазон документа для поиска — весь прайс-лист. Итак, выделяем диапазон и видим, что в графе “Таблица” появилось искомое значение. Далее нажимаем клавишу F4, чтобы в аргументе прописался весь диапазон прайс-листа.
ВАЖНО!
Три первых аргумента являются обязательными. Главное требование к организации данных — искомое значение должно находится в первом столбце таблицы для поиска.
Освоив курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, Вы научитесь профессионально работать в программе Excel, будете уверены в точности своих вычислений и сможете не тратить время на операции вручную. Пройдите пробный курс на нашем сайте.
Далее для завершения операции мы должны заполнить номер столбца. Как мы уже говорили, ищем наименование товара мы по первому столбцу, а вот подставить в нашу таблицу нам нужно те значения, которые указаны во втором столбце — “Цена”. Поэтому в этом аргументе пишем цифру два.
Аргумент “Интервальный просмотр” заполняем как “0” и нажимаем ОК. Теперь мы видим цену нашего товара в первой ячейке. Чтобы применить действие, для всех последующих товаров, скопируем формулу вниз, потянув ячейку за правый нижний угол.
Таким образом, мы заполнили всю таблицу.
Полезно знать!
Для того, чтобы освоить самые удобные и полезные функции программы Excel, необходимо получить более полноценную и структурированную обучающую информацию.
Пройдя курсы Excel дистанционно, вы сможете в короткие сроки освоить работу с продуктом Microsoft и успешно применять полученные навыки на практике.
- Применять продвинутые инструменты вычисления;
- Эффективно работать с большими табличными массивами;
- Анализировать данные с помощью сводных таблиц;
- Применять новые диаграммы Excel 2019;
- Применять альтернативные методики форматирования;
- Защищать данные книги.
Итак, подведем итог: мы рассмотрели один из примеров использования функции ВПР. Благодаря грамотному использованию ссылок на ячейки, полученные формулы ВПР можно копировать или перемещать в любой столбец без необходимости обновлять ссылки.
Надеемся, что наши пошаговые инструкции по использованию функции ВПР в таблицах Excel доступны и понятны для наших читателей. Эти несложные рекомендации можно использовать в простых расчетах.
Более сложные ситуации на конкретных примерах мы рассматриваем на дистанционном практическом курсе Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности. Записавшись на наш курс вы овладеете всеми возможными навыками, облегчающими и ускоряющими работу с данными.
СМОТРИТЕ ВИДЕОУРОКИ ПО ТЕМЕ:
каталог курсов по excel:
Рекомендуемые статьи по теме
Как сделать нумерацию в Microsoft Excel
Формулы в Excel
Как сделать список в Excel. Шпаргалка для новичка
Если вы не знаете, что входит в Microsoft Office, то видео уроки по Майкрософт Офис: Эксель и Ворд бесплатно именно для вас. С помощью таких курсов вы с легкостью сможете понять:
- что за программа Microsoft Office
- что такое MS Office и как им пользоваться
- для чего нужен Майкрософт офис
Затрагивая отдельные компоненты MS Office, бесплатные видео курсы по работе в Microsoft Excel помогут вам разобраться в:
- работе с формулами Excel
- назначении программы MS Excel
- для чего используются функции Microsoft Excel
- с какими типами данных работает Excel
Переходя от одного раздела к другому в бесплатных видео курсах по работе в Microsoft Office, вы найдете информацию, полезную представителям практически любой профессии. Бухгалтера освоят пути сверки данных, кадровики найдут формулы для массивов, а логисты смогут найти решения, применимые в складских операциях.
Видео уроки Power Point помогут научиться наглядно доносить информацию до коллег и руководства, через лаконичные презентации и диаграммы.
Бесплатные видео курсы по работе в Microsoft Word станут основой для правильного составления:
- приказов
- внутренних документов организаций
- служебных писем
Сложно оспорить тот факт, что знание Microsoft Office в условиях современного рынка труда является обязательным при трудоустройстве. Работа с данными при помощи этих программ стала основным инструментом любого офисного сотрудника. Поэтому продвижение по карьере может потребовать более продвинутого уровня знаний Microsoft Office.
Видео уроки по Майкрософт Офис: Эксель и Ворд бесплатно могут стать отправной точкой в повышении квалификации новичков. Данный раздел учебного центра РУНО дает возможность почувствовать себя более уверенно перед поступлением на дипломную программу или большой курс, где потребуются данные навыки.
Любому гостю сайта или слушателю программ РУНО предоставлен открытый доступ к бесплатным видео урокам, где можно ознакомиться с преподавателями, примерными программами и форматом уроков.
Если вы до сих пор сомневаетесь, нужен ли вам полный курс по Microsoft Office, открывайте бесплатные уроки. Определите, в какой именно области вам нужно повышение квалификации и далее переходите в раздел нужных вам курсов.
Если вы не знаете, что входит в Microsoft Office, то видео уроки по Майкрософт Офис: Эксель и Ворд бесплатно именно для вас. С помощью таких курсов вы с легкостью сможете понять:
- что за программа Microsoft Office
- что такое MS Office и как им пользоваться
- для чего нужен Майкрософт офис
Затрагивая отдельные компоненты MS Office, бесплатные видео курсы по работе в Microsoft Excel помогут вам разобраться в:
- работе с формулами Excel
- назначении программы MS Excel
- для чего используются функции Microsoft Excel
- с какими типами данных работает Excel
Переходя от одного раздела к другому в бесплатных видео курсах по работе в Microsoft Office, вы найдете информацию, полезную представителям практически любой профессии. Бухгалтера освоят пути сверки данных, кадровики найдут формулы для массивов, а логисты смогут найти решения, применимые в складских операциях.
Видео уроки Power Point помогут научиться наглядно доносить информацию до коллег и руководства, через лаконичные презентации и диаграммы.
Бесплатные видео курсы по работе в Microsoft Word станут основой для правильного составления:
- приказов
- внутренних документов организаций
- служебных писем
Сложно оспорить тот факт, что знание Microsoft Office в условиях современного рынка труда является обязательным при трудоустройстве. Работа с данными при помощи этих программ стала основным инструментом любого офисного сотрудника. Поэтому продвижение по карьере может потребовать более продвинутого уровня знаний Microsoft Office.
Видео уроки по Майкрософт Офис: Эксель и Ворд бесплатно могут стать отправной точкой в повышении квалификации новичков. Данный раздел учебного центра РУНО дает возможность почувствовать себя более уверенно перед поступлением на дипломную программу или большой курс, где потребуются данные навыки.
Любому гостю сайта или слушателю программ РУНО предоставлен открытый доступ к бесплатным видео урокам, где можно ознакомиться с преподавателями, примерными программами и форматом уроков.
Если вы до сих пор сомневаетесь, нужен ли вам полный курс по Microsoft Office, открывайте бесплатные уроки. Определите, в какой именно области вам нужно повышение квалификации и далее переходите в раздел нужных вам курсов.