Microsoft Excel — отличный инструмент для работы с данными. Возможности огромны, потенциал, на самом деле, мало изучен, так как правила написания команд вызывают сложности даже у профессионалов. Но если потрудиться, оказывается, что здесь можно делать невероятные вещи. Функция Excel ВПР — один из инструментов для обработки данных, о ней мы сейчас и поговорим.
Что такое функция ВПР в Эксель – область применения
При обработке нескольких таблиц, часто находящихся в разных книгах, возникает необходимость перенести данные из одной в другую, при этом сделать так, чтобы значения не потеряли смысл, и процесс был автоматизирован. Работать механизм в excel должен просто и быстро.
Например, есть у нас предприятие. Там работает Иван Иванович. На одном листе хранится значение его зарплаты, в другой сумма денег, которую бухгалтерия удерживает из зарплаты за штраф. Требуется свести все значения в один документ. Другой пример, есть две таблицы: цен и склада. В одной указана стоимость носовых платков, в другой их количество. Необходимо количество и цену свести в одном месте.
В случаях, когда работников предприятия всего два-три, или товаров – до десятка, можно сделать все вручную. При должной внимательности работать человек будет без ошибок. Но если значений для обработки, например, тысяча, требуется автоматизация работы. Для этого в Excel существует ВПР (анг. VLOOKUP).
Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.
Исходные данные таблица 1
| Ф. И. О. | Штраф |
| Петров | 12 000,00 ₽ |
| Сидоров | 200,00 ₽ |
| Иванов | 38 000,00 ₽ |
Исходные данные таблица 2
| Ф. И. О. | З.П. |
| Иванов | 20 000 ₽ |
| Петров | 19 000 ₽ |
| Сидоров | 21 000 ₽ |
Объединенные данные таблица 3
| Ф. И. О. | З.П. | Штраф |
| Иванов | 20 000 ₽ | 38 000 ₽ |
| Петров | 19 000 ₽ | 12 000 ₽ |
| Сидоров | 21 000 ₽ | 200 ₽ |
Если вдруг Вы еще не знали, то советуем прочитать о том, как объединить ячейки в Exсel.
Функция ВПР в Excel – как пользоваться
Для того чтобы таблица 1 пришла к конечному виду, в ней вписываем заголовок столбца, например «Штраф». На самом деле, это необязательно, можно написать любой текст, или оставить его незаполненным. Работать функция будет также по клику мыши в поле, где должно появиться найденное в другой таблице значение.
Теперь нужно вызвать функцию. Это можно сделать разными способами:
Прописать функцию вручную.- Нажать «Вставить функцию», выбрать «Ссылки и массивы», выбрать ВПР.
- Открыть вкладку «Формулы», выбрать «Ссылки и массивы», выбрать ВПР.
Рекомендуется для простоты не набирать весь синтаксис функции вручную, а воспользоваться формой, открывающейся по пунктам п. 2–3. Откроется диалоговое окно для заполнения значений.
Необходимо заполнить значения для функции ВПР
Искомое_значение – это то, что Эксель будет искать в другой таблице. В нашем случае, Иванов, Петров, Сидоров. Кликаем по первой фамилии в списке это ячейка А2. Удерживаем на клавиатуре клавишу Shift, после чего кликаем по последней фамилии в столбце, ячейка А4. В итоге получаем выделенный список фамилий с которым ВПР будет работать.
Таблица – место где будет осуществлен поиск. В нашем случае вторая таблица лежит в Лист 2. Переходим на него. Здесь необходимо выделить весь интервал поиска. Кликаем по левой верхней ячейке интервала. Удерживаем на клавиатуре Shift и нажимаем левой кнопкой мыши правую нижнюю ячейку. Нажимаем F4. Интервал поиска задан.- Номер_столбца – номер столбца из таблицы, в которой ищутся данные. В нашем случае – 2.
Интервальный_просмотр – это логическое значение. В нашем случае необходимо написать «ЛОЖЬ».- Нажать «ОК»
- Теперь необходимо скопировать функцию в каждую пустую ячейку. Можно, например, сделать с помощью копировать — вставить, а можно нажать на ячейку с результатом работы функции, и в правом нижнем углу кнопкой мыши слева нажать на жирный квадратик, не отпуская левую кнопку потянуть вниз.
Результат налицо – в таблице 3 (смотреть выше).
ВПР – инструкция для работы с двумя условиями
В ситуациях, когда необходимо задать критерии поиска не по одному условию, а сразу по нескольким – двум столбцам, функцию ВПР также можно использовать для работы. Для этого исходные данные необходимо доработать.
Пример, необходимо в таблицу 4, вставить цену из таблицы 5.
Характеристики телефонов таблица 4
| Название | ОЗУ | Камера |
| ZTE | 0,5 | 5 |
| ZTE | 1 | 5 |
| DNS | 1 | 8 |
| DNS | 0,5 | 8 |
| Alcatel | 1 | 8 |
| Alcatel | 256 | 2 |
Характеристики телефонов таблица 5
| Название | ОЗУ | Цена |
| ZTE | 0,5 | 1 990 ₽ |
| ZTE | 1 | 3 099 ₽ |
| DNS | 1 | 3 100 ₽ |
| DNS | 0,5 | 2 240 ₽ |
| Alcatel | 1 | 4 500 ₽ |
| Alcatel | 256 | 450 ₽ |
Пример выбран на телефонах, но понятно, что данные могут быть совершенно любыми. Как видно из таблиц, марки телефонов не отличаются, а отличаются ОЗУ и Камера. Для создания сводных данных нам нужно выбрать телефоны по марке и ОЗУ. Для работы функции ВПР по нескольким условиям нужно столбцы с условиями объединить.
Добавляем крайний левый столбец. Например, называем его «Объединение». В первую ячейку значений, у нас B 2, пишем конструкцию «= B 2& C 2». Размножаем с помощью мыши. Получается, как в таблице 6.
Характеристики телефонов таблица 6
| Объединение | Название | ОЗУ | Цена |
| ZTE 0,5 | ZTE | 0,5 | 1 990 ₽ |
| ZTE 1 | ZTE | 1 | 3 099 ₽ |
| DNS1 | DNS | 1 | 3 100 ₽ |
| DNS 0,5 | DNS | 0,5 | 2 240 ₽ |
| Alcatel 1 | Alcatel | 1 | 4 500 ₽ |
| Alcatel 256 | Alcatel | 256 | 450 ₽ |
Таблицу 5 обрабатываем точно так же. После чего функцию ВПР применяем для поиска по одному условию. Условием являются данные из объединенных столбцов. Не забывайте, что номер столбца, откуда берутся данные в функции ВПР изменится. После применения функции получится выборка по двум условиям. Можно объединить не соседние столбцы, а столбцы с маркой телефона и камерой.
Функция ВПР в Экселе не представляет ничего сложного в работе, а открывающиеся возможности для обработки данных огромны.
