Функция ВПР — это один из самых полезных компьютерных трюков не только в Excel. Кто узнал о нем, тот без нее жить не может, серьезно. Итак, предположим, что у нас есть две таблицы с текстом. Нужно значения одного списка (Фамилии) передать в ячейки другого, в зависимости от текста-условий (Номера ТС). Если конкретнее пример ниже:
Задача1. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.
Содержание
Функция ВПР. Решение задачи
Прежде чем решать задачу, я познакомлю вас подробнее с этой мощнейшей функцией.
Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как :) Но на самом деле все проще. Для этого нам нужно понять из чего состоит ВПР
Переменные. Функция ВПР
- Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1. Номер ТС в Таблице2.
- Таблица — все столбцы Таблицы1, причем первый столбец должен быть, который мы ищем (Номер ТС)
- Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения (ФИО)
- [интервальный_просмотр] — может принимать только два значения — Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.
Использование ВПР в Excel и решение примера
Итак, напишем функцию для нашей задачи:
=ВПР(E:E;A:B;2;ЛОЖЬ)
E:E — это диапазон значений, по которым Excel будет сравнивать условия с Таблицей 1. A:B — вся Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот по счету столбец в Таблице 1, который мы будем переносить в ячейку F1. ЛОЖЬ — смотрите выше.
Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично! Значит люди экономят время, зная о простых и действенных методах. Не отставайте!
Не забудьте растащить функцию до конца диапазона. Не знаете как это сделать быстро? Читайте тут.
А как можно сделать ту же функцию с удобными названными диапазонами (на картинке ниже)? Нравится? Читайте тут.
Комментарии по ВПР
- Если для одной ячейки в Таблице2 есть несколько значений, в Таблице1 будет выдаваться первое значение.
- Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#). Чтобы ее не отображать, используйте формулы =ЕСЛИОШИБКА()
- Чтобы определить какой именно номер столбца записать в формулу, при выделении диапазона показывается, номер столбца.
- Чтобы посчитать сумму для нескольких значений из одной таблицы в другой, воспользуйтесь СУММЕСЛИ
Что в итоге:
Полезнейшая функция, которая позволяет совмещать данные из нескольких таблиц, проста в использовании и легко запоминается. Просто находка, я считаю!
Пишите комментарии, буду рад помочь!
3 кит Excel — это сводные таблицы. Подробнее здесь.