Содержание:
- 1 Использование ВПР в программе Excel
- 2 Как сравнить две таблицы: пошаговая инструкция для «чайников»
- 3 Поиск с помощью ВПР по нескольким условиям
- 4 Как сделать выпадающий список через функцию ВПР
- 5 Как пользоваться функцией ВПР в Excel
- 6 Быстрое сравнение двух таблиц с помощью ВПР
- 7 Функция ВПР в Excel с несколькими условиями
- 8 Функция ВПР и выпадающий список
В этом курсе:
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Изучите основы использования функции ВПР.
Использование функции ВПР
В строке формул введите = ВПР ( ).
В скобках введите искомое значение, а после него точку с запятой. Им может быть фактическое значение или пустая ячейка, которая будет содержать значение: (H2;
Укажите табличный массив или таблицу подстановки (диапазон данных, по которому будет выполняться поиск) и введите точку с запятой: (H2;B3:F25;
Введите индекс столбца. Это столбец, в котором предположительно находятся требуемые значения. Он должен находиться справа от диапазона для поиска: (H2;B3:F25;3;
Введите значение подстановки диапазонов ( Истина или ложь ). ИСТИНА находит частичные совпадения, ложь находит точные совпадения. Готовая формула выглядит примерно так: = ВПР (H2, B3: F25, 3, false)
С помощью функции ВПР (в переводе на английский VLOOKUP) пользователи программы Exсel имеют возможность переставлять данные из одной таблицы в другую со схожими параметрами. Эта услуга подойдёт для тех, кому приходится работать с большими списками. Ведь вписывать каждое значение отдельно может занять очень большое количество времени.
Использование ВПР в программе Excel
Для того, чтобы наглядно разобраться как работает функция ВПР в Excel: поможет пошаговая инструкция на конкретном примере.
Допустим, в магазин канцелярских товаров поступил новый привоз, к которому прилагается соответствующая документация. Администратору торгового зала необходимо рассчитать полную стоимость продукции, имея на руках файл Excel, который содержит две таблицы.
Первая – это список предметов, единицы их измерения и количество.
Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.
Чтобы подсчитать сколько стоит продукция, следует информацию из второй вставить в первую, и с помощью простого умножения произвести расчёт.
Этапы работы (инструкция):
- Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
- Отметить верхнее поле в новом.
- Выбрать раздел формулы, и нажать «Вставить функцию».
- Из предложенных категорий Excel отметить «Ссылки и массивы».
- Найти ВПР, и нажать «ОК».
- Заполнить открывшееся окно «Аргументы».
– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.
– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).
Важно! Эти показатели фиксируются, чтобы именно по ним производились расчёты программой Эксель.
Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.
Номер — это строка в которой должна быть информация о том, что будет переноситься из другой таблицы. В рассматриваемом случае – это второй столбец (2).
Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».
В конечном итоге, окно «Аргументы» выглядит так:
Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.
Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.
Как сравнить две таблицы: пошаговая инструкция для «чайников»
Функция ВПР поможет сравнить две таблицы Excel в считанные секунды, даже если данные занимают не один десяток значений. Пошаговая инструкция:
Допустим, что к тому же администратору торгового центра снова привезли товар, но предупредили, что стоимость у некоторых предметов изменились. Как сравнить две таблицы функцией ВПР в Эксель?
Делается это в несколько шагов:
- Открыть первую со старой информацией.
- Добавить дополнительный столбик для новых данных «Новая стоимость».
- Выделить первое пустое поле в созданном столбце (С2).
- Выбрать раздел «ВПР Формулы» и «Вставить функцию».
- Найти категорию Excel «Ссылки и массивы».
- Выбрать ВПР.
- Задать «Аргументы».
– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).
– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.
Номер столбца – второй, так как именно стоимость переносится в новую.
Интервальный просмотр – ЛОЖЬ.
Заполненное окно выглядит так:
После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.
Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.
Поиск с помощью ВПР по нескольким условиям
Если пользователю программы Excel необходимо из большого каталога найти необходимые данные, он может воспользоваться данным способом для чайников (инструкция).
Итак, имеется документ, в котором обозначены: компании, товары и цены.
Нужно найти цену на конкретный товар – гелевая ручка. Но так как каталог может быть огромным, а гелевые ручки быть не у одной компании, поиск стоимости в Эксель лучше проводить через ВПР с несколькими условиями: название компании и предмета.
Чтобы осуществить поиск следует:
- Создать слева новый столбец с объединёнными данными (название компании и товара).
Делается это просто:
- выделить крайнюю левую ячейку (А1);
- щёлкнуть ПКМ и выбрать «Вставить»;
- отметить добавление столбца и нажать «ОК».
- Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2&C2) и нажать кнопку Enter. Чтобы продлить список достаточно растянуть ячейку.
- Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
- Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.
– что нужно найти (щёлкнуть по введенной — ЛасточкаГелевая ручка – А8).
– где искать нужное значение (выделить ячейки от первой до последней — А2 – D5).
Номер столбца – из какого столбца вывести результат (4).
Интервальный просмотр – ЛОЖЬ.
После нажатия команды «ОК», программа отобразит результат.
Как сделать выпадающий список через функцию ВПР
Чтобы сделать выпадающий список из существующего нужно следовать инструкции:
- Выбрать поле, в котором будет сформированы показатели. Например, Е2.
- Зайти в раздел «Данные», и выбрать «Проверка данных».
- Установить тип данных, как список.
- В появившуюся строку «Источник» ввести информацию (выделить с первой до последней ячейки – А2:А5).
Выпадающий список готов.
Теперь с помощью функции ВПР нужно добавить возможность просмотра цены, при выборе товара. Как это работает в Эксель? (Инструкция).
- Создать новое поле с названием «Цена».
- Вставить аргументы.
– ячейка Excel, в которой находится выпадающий список (Е2).
– выделенный фрагмент с предметами и ценами (А2-В5).
Номер столбца – 2 (в нём находятся цены).
Интервальный просмотр – ЛОЖЬ.
После подтверждения команды можно пользоваться списком и просмотром цены.
Таким образом, с помощью несложных инструкций, каждый может разобраться, как пользоваться ВПР. Смотрим видео.
Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.
Очень удобная и часто используемая. Т.к. сопоставить вручную диапазоны с десятками тысяч наименований проблематично.
Как пользоваться функцией ВПР в Excel
Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.
Стоимость материалов – в прайс-листе. Это отдельная таблица.
Необходимо узнать стоимость материалов, поступивших на склад. Для этого нужно подставит цену из второй таблицы в первую. И посредством обычного умножения мы найдем искомое.
- Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
- Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
- Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
- Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
- Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
- В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.
Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.
Теперь найти стоимость материалов не составит труда: количество * цену.
Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».
- Выделяем столбец со вставленными ценами.
- Правая кнопка мыши – «Копировать».
- Не снимая выделения, правая кнопка мыши – «Специальная вставка».
- Поставить галочку напротив «Значения». ОК.
Формула в ячейках исчезнет. Останутся только значения.
Быстрое сравнение двух таблиц с помощью ВПР
Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами.
- В старом прайсе делаем столбец «Новая цена».
- Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.
Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу.
Функция ВПР в Excel с несколькими условиями
До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.
Таблица для примера:
Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.
Дело осложняется тем, что от одного поставщика поступает несколько наименований.
- Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
- Таким же образом объединяем искомые критерии запроса:
- Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.
Рассмотрим формулу детально:
- Что ищем.
- Где ищем.
- Какие данные берем.
Функция ВПР и выпадающий список
Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.
Сначала сделаем раскрывающийся список:
- Ставим курсор в ячейку Е8, где и будет этот список.
- Заходим на вкладку «Данные». Меню «Проверка данных».
- Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
- Когда нажмем ОК – сформируется выпадающий список.
Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).
- Открываем «Мастер функций» и выбираем ВПР.
- Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
- Нажимаем ВВОД и наслаждаемся результатом.
Изменяем материал – меняется цена:
Так работает раскрывающийся список в Excel с функцией ВПР. Все происходит автоматически. В течение нескольких секунд. Все работает быстро и качественно. Нужно только разобраться с этой функцией.