Программа Excel содержит много полезных функций, одна из которых называется ВПР. Это поисковая функция, позволяющая находить и сопоставлять значения двух разных таблиц. Рассмотрим, что такое ВПР функция в Excel, как она работает и как правильно ее настроить.
Зачем нужен ВПР
Функция ВПР (второе ее название — Vlookup) в Excel предназначена для сопоставления данных из разных таблиц и их копирования. Эта функция облегчает работу с большими массивами данных, поскольку нет необходимости сопоставлять и копировать сотни значений вручную. Достаточно настроить функцию VPR, и она все сделает автоматически.
Функцию можно использовать для разных нужд — поиска номеров телефонов, цен, артикулов товаров, фамилий людей
Если вы начнете копировать фамилии заказчиков и переносить их в таблицу с товарами, это займет очень много времени, особенно если в вашем списке несколько сотен позиций. ВПР поможет вам подставить фамилии к наименованиям товаров автоматически. И вы будете видеть, какому покупателю нужно отдать тот или иной товар.
Как работает ВПР
Работу этой функции можно сравнить с телефонным справочником или любым алфавитным указателем. Например, вам нужно найти в телефонном справочнике номер определенного человека. Вы открываете справочник на разделе с нужной буквой и начинаете искать фамилию, просматривая одну строку за другой сверху вниз. Найдя нужную фамилию, переводите взгляд на колонку с номерами телефонов и смотрите на номер, который находится напротив нужной фамилии.
По такому же принципу работает ВПР. Она сканирует первый столбец и ищет заданный вами критерий поиска. Когда критерий найден, функция передвигается на указанное вами количество столбцов вправо и выдает значение ячейки из этого столбца.
Таким образом, функция сканирует ячейки в пределах указанного диапазона одной таблицы, находит совпадение и переносит его в другую таблицу. Формула ВПР в Эксель включает в себя четыре аргумента:
- Искомое значение — это столбец с нужными значениями в той таблице, куда требуется перенести данные.
- Таблица — диапазон, в котором функция ищет значения, которые необходимо сопоставить с первой таблицей.
- Номер столбца — порядковый номер столбца, в котором находятся искомые значения.
- Интервальный просмотр — это условие поиска. Условием может быть точное совпадение данных или неточное. Неточное предполагает примерное значение и используется в основном при поиске цифровых данных.
Как настроить функцию: пошаговая инструкция для чайников
Рассмотрим, как настроить эту функцию, чтобы сопоставить между собой данные из двух таблиц. Для этого возьмем две таблицы. В одной указаны фамилии и даты рождения сотрудников компании, а во второй — фамилии и должности. В первой таблице фамилии записаны в алфавитном порядке, а во второй — вразброс. Нам нужно объединить данные обеих таблиц таким образом, чтобы напротив фамилии каждого сотрудника указывалась не только дата его рождения, но и должность, которую он занимает в компании.
Шаг 1
Берем первую таблицу с фамилиями и датами и добавляем в нее новый столбец. Даем ему название «Должность». Теперь вызываем функцию ВПР. Для этого нужно нажать кнопку Fx, которая находится слева над столбцами таблицы. Откроется окошко со списком функций. Выбираем из списка ВПР.
Шаг 2
После того как вы выберем ВПР, откроется другое окошко с полями, в которые нужно вписать аргументы функции: искомое значение, таблицу, номер столбца и интервальный просмотр.
Шаг 3
Задаем искомое значение — это будут координаты первой ячейки столбца, для которого необходимо найти значения. Вводим значение: А2. Здесь А — это буква, обозначающая первый столбец с фамилиями, 2 — это первая ячейка этого столбца.
Шаг 4
Теперь задаем аргумент «Таблица». Во второе поле в окошке вписываем интервал, в котором функция будет искать нужные данные. Для этого переводим курсор мышки на второе поле и отрываем другой лист таблицы, где находится список должностей сотрудников. В этом списке выделяем интервал. У нас значение интервала будет A2-B15.
Теперь закрепляем этот интервал, нажав на клавиатуре клавишу F4. В окошке, куда мы вписали интервал, появятся значки доллара ($). Теперь формула закреплена, и мы снова можем переходить с одного листа на другой, не боясь ее потерять.
Шаг 5
Теперь вводим номер столбца в интервале, из которого будем брать значения. Номер столбца читается внутри диапазона. У нас это второй столбец, потому что в этой таблице всего два столбца и должности вписаны во второй.
Шаг 6
Мы дошли по последнего аргумента — «Интервальный просмотр». Здесь есть два условия поиска: ЛОЖЬ (0 или FALSE) или ИСТИНА (1 или TRUE). ЛОЖЬ позволяет найти точное совпадение, поэтому выбираем именно это условие. Знак, который нужно поставить в формуле, подскажет сама программа Excel, поскольку в разных версиях эти знаки могут отличаться.
Конечная формула у нас будет выглядеть следующим образом: =ВПР (A2;Лист2!$A$2:$B$15;2;0). Она автоматически пропишется в поисковой строке Excel. Теперь нажимаем «ОК» в окошке, куда мы вписывали аргументы, чтобы применить функцию.
Как видим, формула работает. Напротив первой фамилии в списке появилась должность сотрудника. Теперь протягиваем формулу через весь столбец, чтобы заполнить остальные ячейки.
Готово. Мы подтянули данные из одной таблицы в другую, как и требовалось. Теперь напротив фамилии каждого сотрудника указывается его должность.
Типичные ошибки и способы их исправления
Если вы подставили значения, но формула не работает, значит где-то была допущена ошибка.
Ошибка | Как исправить |
В итоговом столбце появляется ошибка #Н/Д. Она означает, что требуемые данные во второй таблице не найдены. Это происходит, когда в аргументе «Интервальный просмотр» задается точный поиск, но точных совпадений функция найти не может. | Внимательно посмотрите, нет ли в строках лишних пробелов или каких-то других знаков, которые искажают точное значение ячейки. |
Значения ячеек дублируются, поэтому функция находит только одну из ячеек и не видит другой. | Удалите повторяющиеся данные, поскольку функция не умеет их распознавать. |
Искомое значение ниже самого низкого в таблице. Такое возникает, если в аргументе «Интервальный просмотр» задано условие ИСТИНА, что делает поиск неточным. | Исправьте искомое значение. |
Главное, что нужно знать
- Функция VPR предназначена для объединения данных из двух таблиц в одну, а также для сравнения таблиц. Ее можно использовать в бухгалтерии, продажах
и т. д. - Чтобы применить функцию, нужно, чтобы обе таблицы находились в одном Excel-файле, но на разных листах. Использовать функцию для объединения таблиц, расположенных в разных файлах, невозможно.
- Задавая значения аргументов, нужно внимательно проверять данные, чтобы не было пробелов и лишних знаков. В противном случае функция не сработает.
- Если функция выдает ошибку #Н/Д, значит данные были введены некорректно.
