Как пользоваться функцией ВПР в Excel: понятная инструкция для чайников, видео, сравнение двух таблиц, выпадающий список, примеры. Формула функции ВПР в Excel: примеры, несколько условий, расшифровка
Статья-инструкция: «Функция ВПР в Excel для чайников».
Навигация
Функция ВПР в Excel дает возможность переставлять данные одной таблицы в другую по ячейкам. Это очень популярный и удобный способ, и о нем мы поговорим в сегодняшнем обзоре.
Функция ВПР в Excel: инструкция для чайников
Предположим, перед нами стоит следующая задача. На нашу фабрику завезли необходимые нам материалы, которые представлены в следующей таблице:
Таблица в «Excel»
В другой таблице мы имеем тот же самый список с указанием стоимости этих материалов:
Прайс-лист в «Excel»
То есть, в первой таблице мы имеем количество материалов, а во второй – стоимость за штуку (руб/кв.м). Наша задача, вычислить общую стоимость всех материалов, которые были завезены на фабрику. Для этого нам нужно перенести показатели со второй таблицы в первую и при помощи умножения найти ответ.
Приступим к делу:
- В первой таблице нам не хватает двух столбцов – «Цена» (то есть стоимость за 1 кв.м) и «Стоимость» (то есть общая стоимость завезенного материала). Добавим эти столбцы. Теперь выделите в столбце «Цена» верхнюю первую ячейку, запустите «Мастер функций» (нажать одновременно «F3» и «Shift»), во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР».
Запустите «Мастер функций», во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР»
- Далее в новом открывшемся окне напротив пункта «Искомое значение» наблюдаем следующие показатели: A1:А15. То есть программа фиксирует диапазон наименований материалов в соответствующем столбе «Материалы». То же самое программа должна показать и во второй таблице.
Наблюдаем следующие показатели: A1:А15
- Теперь разберемся со вторым пунктом – «Таблица» (вторая таблица со стоимостью материалов). Нажмите на этот пункт, затем выделите во второй таблице диапазон наименований материалов вместе с ценами. В итоге, результат должен быть следующим.
Нажмите на пункт «Таблица»
- Далее выделите результаты в пункте «Таблица» и нажмите на «F4», после чего в них появится новый символ «$» — таким образом программа будет ссылаться на показатели в «Таблица».
Выделите результаты в пункте «Таблица» и нажмите на «F4»
- Далее переходим к третьему пункту – «Номер столбца». Укажите здесь «2». В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ». После всех этих манипуляций нажмите на «ОК».
В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ»
- Последний штрих. Курсором мышки нажмите на нижний правый угол таблицы и потяните вниз то тех пор, пока не увидите полный список наименований материалов вместе с ценами.
Курсором мышки нажмите на нижний правый угол таблицы и потяните вниз
Все, мы связали обе наши таблицы. Вычислить общую стоимость материалов будет проще простого. Теперь нужно учитывать, что если прайс-лист изменится, то изменятся и полученные нами результаты. Поэтому нам нужно избежать этого при помощи следующих действий:
- Выделите все показатели в столбце «Цена», нажмите на нем правой кнопкой мышки и далее – на «Копировать».
- Снова нажмите правой кнопкой мышки по уже выделенным ценам и затем – на «Специальная вставка».
- В открывшемся окошке поставьте галку, как показано на скриншоте, и нажмите на «ОК»
Поставьте галку на «Значения»
Сравниваем две таблицы при помощи функции ВПР в Excel
Предположим, что прайс-лист изменился, и нам необходимо сравнить две таблицы – новые и старые цены:
Прайс-лист
Для этого воспользуемся соответствующей функцией в «Excel»:
- В старой таблице добавьте столбец «Новая цена»
Добавляем столбец «Новая цена»
- Далее повторяем действия, которые мы совершали выше – выделяем первую верхнюю ячейку, выбираем «ВПР», в пункте «Таблица» получаем результаты и нажимаем на «F4».
в пункте «Таблица» получаем следующие результаты
- То есть на скриншоте выше мы наблюдаем, что из таблицы с новыми ценами мы перенесли стоимость каждого материала в старую таблицу и получили следующие результаты.
Полученная таблица
Работаем с несколькими условиями при помощи функция ВПР в Excel
Выше мы работали с одним условием – наименованием материалов. Но в реальности обстоятельства могут сложиться иначе. Мы можем получить таблицу с двумя и более условиями, например, как с наименованием материалов, так и с наименованием их поставщиков:
Таблица с наименованиями поставщиков и материалов
Теперь наша задача усложняется. Допустим, нам необходимо найти, в какую цену поставляет тот или иной материал отдельно взятый поставщик. Еще сложнее окажется ситуация, когда поставщик продает несколько материалов, при том что самих поставщиков тоже может быть несколько.
Попробуем решить эту задачу:
- Добавьте в таблицу левый крайний столбец и объедините два столбца – «Материалы» и «Поставщики».
Объедините два столбца – «Материалы» и «Поставщики»
- Точно так же объедините искомые критерии запроса
Объедините искомые критерии запроса
- Снова в пункте «Таблица» задайте соответствующие показатели
В пункте «Таблица» задайте такие показатели
- На скриншоте выше мы видим следующую формулу (1 – объект поиска; 2 – место поиска; 3 – взятые нами данные).
1 – объект поиска; 2 – место поиска; 3 – взятые нами данные
Работаем с выпадающим списком
Предположим, что у нас определенные данные (например, «Материалы») показываются в раскрывающимся списке. Наша задача, чтобы при этом было доступно видеть еще и стоимость.
Для этого предпримем следующие шаги:
- Нажимаем мышкой на ячейку «E8» и во вкладке «Данные» выбираем «Проверку данных»
Нажмите на «Проверка данных»
- Далее во вкладке «Параметры» выберите «Список» и нажмите на «ОК»
Выберите «Список» и нажмите на «ОК»
- Теперь в выпадающем списке мы видим пункт «Цена»
Получаем пункт «Цена»
- Далее нам нужно, чтобы напротив пункта «Цена» выставлялась стоимость материала
- Нажмите на ячейку «E9», в «Мастере функций» выберите «ВПР». Напротив пункта «Таблица» должны быть выставлены следующе показатели.
В «Таблица» должны быть следующе показатели
- Нажмите на «Ок» и получите результат
Получаем результаты