Calltouch
Member
Функция ВПР существенно упрощает обработку данных в 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-й строке в колонке с ценами отображается «#Н/Д». Это означает, что формула ВПР в ячейке некорректна.
Ошибки возникают по разным причинам. Рассмотрим типичные варианты.
В ячейке F18 выводится «#Н/Д». Разберемся, почему это произошло. Проверим, есть ли искомое значение в листе «Каталог» (для 18-й строки это «Хилтон»). Оно есть, но написано с ошибкой — «Хиллтон». Так как одна буква не совпадает, приложение воспринимает это как отсутствие значения. Также ВПР не выведет результаты, если в ячейках с требуемыми критериями поиска присутствуют лишние пробелы или замена символов (латиница вместо кириллицы).
Часто пользователи забывают зафиксировать диапазон. В этом случае формула нашего примера отобразится в таком виде: «=ВПР(D3;Каталог!A2:E21;5;0)» (отсутствует знак $). Тогда таблица будет выглядеть так:
Вследствие смещения диапазона во многих ячейках отображается ошибка. Решение: установите «$» перед всеми координатами формулы.
Если диапазон обозначен некорректно, ВПР не выведет нужные результаты. Выделяйте все ячейки первого листа, которые несут полезную информацию (кроме заголовков, номеров колонок). Столбец с элементами, по которым проводится поиск, всегда размещайте первым. Перенесите колонку с нужными данными в начало листа или выделяйте диапазон так, чтобы колонка с параметром поиска размещалась слева от первой.
Давайте узнаем, какие названия моделей отсутствуют в первом листе. Удалим из него три строки:
Изменения отразились в листе «Заявки» — в трех строках возникло выражение «#Н/Д». Именно эти модели мы удалили вначале.
Проводить такое сравнение можно по любым другим параметрам: стоимости, цене, характеристикам товара.
Есть два прайса, менеджер хочет сравнить старые цены с новыми. Данные представлены в таком виде:
Подтянем значения из первой таблицы во вторую с помощью формулы ВПР:
Если обновились не только цены, но и ассортимент, результат получится такой:
Выглядит не очень эстетично. Скорректируем с помощью функции «ЕСЛИОШИБКА». После знака «=» в строке формул введите «ЕСЛИОШИБКА([ВПР — формула]); значение, которое выводится при ошибке)». В нашем случае формула получила вид: «=ЕСЛИОШИБКА(ВПР(D3;$A$3:$B$15;2;0);0)». А таблица стала выглядеть так:
Оптимизируйте маркетинг и увеличивайте продажи вместе с Calltouch
Существуют два способа:
- Найдите во вкладке «Формулы» опцию «Вставить функцию» и нажмите на нее.
- Кликните на «fx» в строке формул в одной из вкладок таблицы. Справа вы увидите окно «Построитель формул» — найдите в нем функцию ВПР и кликните на «Вставить функцию».
Почему выходит значение «0»?
Самая распространенная причина — ошибка во вводе аргумента «номер_столбца» или указание числа меньше единицы для значения индекса.
Почему ВПР выдает некорректные данные?
Возможно, типы информации не идентичны. Например, если вы используете ВПР в численном значении, а исходные данные сохраняются в виде текста, формула работать не будет.
You do not have permission to view link please Вход or Регистрация
Читайте также:
You do not have permission to view link please Вход or Регистрация
You do not have permission to view link please Вход or Регистрация
Зачем нужна функция ВПР и когда ее используют
Альтернативное название ВПР в Excel — Vlookup. Инструмент перетаскивает значения в соответствии с заданной формулой. Рассмотрим на примере, как он работает.
Компания торгует мебелью. У менеджера две таблицы: в одной приведены характеристики товаров и стоимость, а в другой —
You do not have permission to view link please Вход or Регистрация
, которые оставили заявки.
You do not have permission to view link please Вход or Регистрация
You do not have permission to view link please Вход or Регистрация
Менеджеру удобнее, чтобы в таблице с заявками отображались цены. Функция ВПР в Excel объединит информацию из двух документов и выведет стоимость каждой позиции в соответствующей ячейке.
Область применения формулы ВПР не ограничивается торговлей. Другие примеры использования:
- учет имущества, финансов;
- кадровое делопроизводство;
- сопоставление данных из двух таблиц.
Vlookup ценят за возможность быстро найти и перенести нужные данные — вручную процесс займет в разы больше времени.
Как пользоваться ВПР: руководство с примерами
Опишем, как работает ВПР. Программа исследует обозначенный диапазон до тех пор, пока не обнаружит указанный элемент (до первого совпадения). После этого копирует его в назначенную ячейку.
Вставляем формулу
С ВПР в Excel удобнее работать в одном документе, размещая таблицы на разных листах. Рассмотрим порядок действий.
Добавляем колонку со стоимостью в лист 2 (в примере он назван «Заявки»). Выделяем верхнюю ячейку нового столбца. В представленном образце добавлен еще один параметр — «Стоимость» (программа автоматически рассчитает чек заказа).
You do not have permission to view link please Вход or Регистрация
В верхней части рабочей панели перейдите в раздел «Формулы». Слева найдите инструмент «fx» (вставка функции). Когда нажмете, появится окно со списком доступных функций — выберите «ВПР».
You do not have permission to view link please Вход or Регистрация
Заполняем аргументы
Предстоит ввести четыре параметра: искомое значение (модель, цена, характеристика и так далее), таблицу (область поиска), номер столбца (порядковый), интервальный просмотр. Разберем каждый из них подробнее.
Искомое значение. Им в формуле ВПР служит содержимое ячейки, общее для обеих таблиц. Впоследствии программа сканирует диапазон первого листа, пока не найдет указанный идентификатор. В рассматриваемом варианте это «Аэлита». Активируем ячейку — «координаты» появятся в поле ввода. В строке формул автоматически появился фрагмент функции: «=ВПР(D3)».
You do not have permission to view link please Вход or Регистрация
Таблица. В поле «Таблица» указываем область данных (берем ее из первого листа — у нас это «Каталог»). Функция работает корректно, если элемент, который вы ищете, размещен в первом столбце. Теперь документ отображается так:
You do not have permission to view link please Вход or Регистрация
Закрепляем диапазон («F4» для Windows, комбинация «Cmd+T» для macOS). Фиксация диапазона дает возможность распространить формулу ВПР в Excel на все строки столбца. Теперь в строке формул отображается выражение «=ВПР(D3; Каталог!$A$2:$E$21)».
You do not have permission to view link please Вход or Регистрация
Номер столбца. Введем порядковый номер колонки, в которой указана искомая характеристика. «Стоимость» находится в пятом столбце. Теперь вверху отображается формула «=ВПР(D3;Каталог!$A$2:$E$21;5)».
You do not have permission to view link please Вход or Регистрация
Интервальный просмотр. Поле воспринимает два значения: «0» или «1», и определяет точность поиска. Для абсолютного совпадения установите «0». Если вас устроит приблизительное соответствие, введите «1». Теперь в строке формул отображается функция ВПР в окончательном варианте: «=ВПР(D3;Каталог!$A$2:$E$21;5;0)».
You do not have permission to view link please Вход or Регистрация
Получаем результат
Как только вы введете четыре аргумента и нажмете «Ок», в ячейке, в которую введена формула, увидите результат поиска.
You do not have permission to view link please Вход or Регистрация
Выделите ячейку с результатом, установите курсор на квадратик в нижнем правом углу и потяните его вниз, зажав кнопку компьютерной мыши.
You do not have permission to view link please Вход or Регистрация
Программа автоматически перенесет стоимость заказанной мебели из первого листа во второй. Ошибки исключены.
You do not have permission to view link please Вход or Регистрация
Аналогичным образом протягивается формула для колонки «Стоимость заказа». На изображении выше в 18-й строке в колонке с ценами отображается «#Н/Д». Это означает, что формула ВПР в ячейке некорректна.
Почему не работает ВПР в Excel
Ошибки возникают по разным причинам. Рассмотрим типичные варианты.
В ячейке F18 выводится «#Н/Д». Разберемся, почему это произошло. Проверим, есть ли искомое значение в листе «Каталог» (для 18-й строки это «Хилтон»). Оно есть, но написано с ошибкой — «Хиллтон». Так как одна буква не совпадает, приложение воспринимает это как отсутствие значения. Также ВПР не выведет результаты, если в ячейках с требуемыми критериями поиска присутствуют лишние пробелы или замена символов (латиница вместо кириллицы).
You do not have permission to view link please Вход or Регистрация
Часто пользователи забывают зафиксировать диапазон. В этом случае формула нашего примера отобразится в таком виде: «=ВПР(D3;Каталог!A2:E21;5;0)» (отсутствует знак $). Тогда таблица будет выглядеть так:
You do not have permission to view link please Вход or Регистрация
Вследствие смещения диапазона во многих ячейках отображается ошибка. Решение: установите «$» перед всеми координатами формулы.
Если диапазон обозначен некорректно, ВПР не выведет нужные результаты. Выделяйте все ячейки первого листа, которые несут полезную информацию (кроме заголовков, номеров колонок). Столбец с элементами, по которым проводится поиск, всегда размещайте первым. Перенесите колонку с нужными данными в начало листа или выделяйте диапазон так, чтобы колонка с параметром поиска размещалась слева от первой.
Как использовать формулу ВПР для сравнения двух таблиц
Давайте узнаем, какие названия моделей отсутствуют в первом листе. Удалим из него три строки:
You do not have permission to view link please Вход or Регистрация
Изменения отразились в листе «Заявки» — в трех строках возникло выражение «#Н/Д». Именно эти модели мы удалили вначале.
You do not have permission to view link please Вход or Регистрация
Проводить такое сравнение можно по любым другим параметрам: стоимости, цене, характеристикам товара.
Пример
Есть два прайса, менеджер хочет сравнить старые цены с новыми. Данные представлены в таком виде:
You do not have permission to view link please Вход or Регистрация
Подтянем значения из первой таблицы во вторую с помощью формулы ВПР:
You do not have permission to view link please Вход or Регистрация
Если обновились не только цены, но и ассортимент, результат получится такой:
You do not have permission to view link please Вход or Регистрация
Выглядит не очень эстетично. Скорректируем с помощью функции «ЕСЛИОШИБКА». После знака «=» в строке формул введите «ЕСЛИОШИБКА([ВПР — формула]); значение, которое выводится при ошибке)». В нашем случае формула получила вид: «=ЕСЛИОШИБКА(ВПР(D3;$A$3:$B$15;2;0);0)». А таблица стала выглядеть так:
You do not have permission to view link please Вход or Регистрация
Оптимизируйте маркетинг и увеличивайте продажи вместе с Calltouch
You do not have permission to view link please Вход or Регистрация
Коротко о главном
- ВПР — функция Excel, предназначенная для поиска и переноса данных из одного листа в другой.
- В общем виде формула выглядит так: ВПР (искомое значение; диапазон; номер столбца, в котором находятся нужные данные; тип интервального просмотра — «1» или «0»). «1» обозначает приблизительное совпадение («истина»), «0» — точное («ложь»).
- Порядок запуска ВПР: перейдите в «Формулы», выберите «вставить функцию», затем — «ВПР». Далее введите аргументы функции, нажмите «Ок». Выделите первую ячейку с введенной формулой, установите курсор на квадратик внизу справа, протяните по всему столбцу, чтобы воспользоваться автозаполнением.
FAQ
Как написать формулу ВПР в Excel?Существуют два способа:
- Найдите во вкладке «Формулы» опцию «Вставить функцию» и нажмите на нее.
- Кликните на «fx» в строке формул в одной из вкладок таблицы. Справа вы увидите окно «Построитель формул» — найдите в нем функцию ВПР и кликните на «Вставить функцию».
Почему выходит значение «0»?
Самая распространенная причина — ошибка во вводе аргумента «номер_столбца» или указание числа меньше единицы для значения индекса.
Почему ВПР выдает некорректные данные?
Возможно, типы информации не идентичны. Например, если вы используете ВПР в численном значении, а исходные данные сохраняются в виде текста, формула работать не будет.
