Функции в excel впр
Функция ВПР в программе Microsoft Excel
Работа с обобщающей таблицей подразумевает подтягивание в неё значений из других таблиц. Если таблиц очень много, ручной перенос заберет огромное количество времени, а если данные постоянно обновляются, то это уже будет сизифов труд. К счастью, существует функция ВПР, которая предлагает возможность автоматической выборки данных. Давайте рассмотрим конкретные примеры работы этой функции.
Определение функции ВПР
Название функции ВПР расшифровывается, как «функция вертикального просмотра». По-английски её наименование звучит – VLOOKUP. Эта функция ищет данные в левом столбце изучаемого диапазона, а затем возвращает полученное значение в указанную ячейку. Попросту говоря, ВПР позволяет переставлять значения из ячейки одной таблицы, в другую таблицу. Выясним, как пользоваться функцией VLOOKUP в Excel.
Пример использования ВПР
Взглянем, как работает функция ВПР на конкретном примере.
У нас имеется две таблицы. Первая из них представляет собой таблицу закупок, в которой размещены наименования продуктов питания. В следующей колонке после наименования расположено значение количества товара, который требуется закупить. Далее следует цена. И в последней колонке – общая стоимость закупки конкретного наименования товара, которая рассчитывается по вбитой уже в ячейку формуле умножения количества на цену. А вот цену нам как раз и придется подтянуть с помощью функции ВПР из соседней таблицы, которая представляет собой прайс-лист.
-
Кликаем по верхней ячейке (C3) в столбце «Цена» в первой таблице. Затем, жмем на значок «Вставить функцию», который расположен перед строкой формул.
После этого открывается окно, в которое нужно вставить аргументы функции. Жмем на кнопку, расположенную справа от поля ввода данных, чтобы приступить к выбору аргумента искомого значения.
Так как у нас искомое значение для ячейки C3, это «Картофель», то и выделяем соответствующее значение. Возвращаемся к окну аргументов функции.
Точно таким же образом кликаем по значку справа от поля ввода данных, для выбора таблицы, откуда будут подтягиваться значения.
Для того, чтобы выбранные значения сделать из относительных абсолютными, а это нам нужно, чтобы значения не сдвинулись при последующем изменении таблицы, просто выделяем ссылку в поле «Таблица», и жмем на функциональную клавишу F4. После этого к ссылке добавляются знаки доллара и она превращается в абсолютную.
Как видим, цена картофеля подтянулась в таблицу из прайс-листа. Чтобы не проделывать такую сложную процедуру с другими товарными наименованиями, просто становимся в нижний правый угол заполненной ячейки, чтобы появился крестик. Проводим этим крестиком до самого низа таблицы.
Таким образом мы подтянули все нужные данные из одной таблицы в другую, с помощью функции ВПР.
Как видим, функция ВПР не так сложна, как кажется на первый взгляд. Разобраться в её применении не очень трудно, зато освоение этого инструмента сэкономит вам массу времени при работе с таблицами.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Источник: lumpics.ru
ВПР (функция ВПР)
Совет: Попробуйте использовать новую функцию кслукуп — улучшенную версию функции ВПР, которая работает в любом направлении и возвращает точные совпадения по умолчанию, упрощая и удобную в использовании, чем его предшественник.
ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.
Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!
Самая простая функция ВПР означает следующее:
= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
= ВПР (A2; A10: C20; 2; ИСТИНА)
= ВПР (a2; сведения о клиенте)! А:Ф, 3, ЛОЖЬ)
Значение для поиска. Значение, которое вы хотите найти, должно находиться в первом столбце диапазона ячеек, указанного в аргументе table_array .
Например, если Таблица-массив охватывает ячейки B2: D7, то lookup_value должен находиться в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Вы можете использовать именованный диапазон или таблицу, а вместо ссылок на ячейки можно использовать имена в аргументе.
Первый столбец в диапазоне ячеек должен содержать lookup_value. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.
Номер столбца (начиная с 1 для самого левого столбца table_array), содержащего возвращаемое значение.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).
Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР (“Смит”; a1: B100; 2; ложь).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — “C” и т. д.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Вы можете использовать функцию ВПР для объединения нескольких таблиц в одну, если одна из них имеет общие поля. Это может быть особенно удобно, если вам нужно предоставить доступ к книге пользователям более ранних версий Excel, которые не поддерживают функции работы с данными с несколькими таблицами в качестве источников данных — путем объединения источников в одну таблицу и изменения источника данных компонента данных на новый. Таблица, функция данных может использоваться в более ранних версиях Excel (при условии, что сама функция данных поддерживается в более ранней версии).
![]() |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Ф. И. О. | Штраф |
Петров | 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 ₽ |
Функция ВПР в 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 обрабатываем точно так же. После чего функцию ВПР применяем для поиска по одному условию. Условием являются данные из объединенных столбцов. Не забывайте, что номер столбца, откуда берутся данные в функции ВПР изменится. После применения функции получится выборка по двум условиям. Можно объединить не соседние столбцы, а столбцы с маркой телефона и камерой.
Смотрите видеоурок как пользоваться функцией ВПР в Эксель для чайников:
Функция ВПР в Экселе не представляет ничего сложного в работе, а открывающиеся возможности для обработки данных огромны.
Источник: komp.guru
detector