обучение функции впр в excel
Функция ВПР в Excel для чайников — все по делу, без воды!
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 ₽ |
Функция ВПР в Excel – как пользоваться
Для того чтобы таблица 1 пришла к конечному виду, в ней вписываем заголовок столбца, например «Штраф». На самом деле, это необязательно, можно написать любой текст, или оставить его незаполненным. Работать функция будет также по клику мыши в поле, где должно появиться найденное в другой таблице значение.
Теперь нужно вызвать функцию. Это можно сделать разными способами:
Рекомендуется для простоты не набирать весь синтаксис функции вручную, а воспользоваться формой, открывающейся по пунктам п. 2–3. Откроется диалоговое окно для заполнения значений.
Необходимо заполнить значения для функции ВПР
Результат налицо – в таблице 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 обрабатываем точно так же. После чего функцию ВПР применяем для поиска по одному условию. Условием являются данные из объединенных столбцов. Не забывайте, что номер столбца, откуда берутся данные в функции ВПР изменится. После применения функции получится выборка по двум условиям. Можно объединить не соседние столбцы, а столбцы с маркой телефона и камерой.
Функция ВПР в Экселе не представляет ничего сложного в работе, а открывающиеся возможности для обработки данных огромны.
Функция ВПР в Excel
В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения.
В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.
Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.
Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:
Выделить ячейку и вписать функцию.
Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».
После этого открывается окно, где можно заполнить ячейки аргументов формулы.
Синтаксис функции ВПР выглядит так:
В нашем случае получится такая формула:
Аргументы функции ВПР
Сейчас разберемся что и куда писать.
Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.
Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.
В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.
Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.
В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.
Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.
Формула сканирует таблицу по вертикали.
Находит в самом левом столбце совпадение с искомым значением.
Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.
Передает данные в ячейку с формулой.
В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.
Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.
Автозаполнение
В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.
Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной.
В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.
Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.
Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.
Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.
Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:
Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.
В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.
Итоги
Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
Функцию можно вписать вручную или в специальном окне (Shift + F3).
Искомое значение – относительная ссылка, а таблица – абсолютная.
Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
Порядок работы с функцией подходит для Гугл-таблиц.
Функция ВПР
Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Самая простая функция ВПР означает следующее:
=В.ПРОСМОТР(то, что вы хотите найти, где ее искать; номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
=ВLOOKUP(A2;’Сведения о клиенте’! A:F;3;ЛОЖЬ)
Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в table_array.
Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должны быть в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Можно использовать именуемый диапазон или таблицу, а не ссылки на ячейки, а имена в аргументе.
Номер столбца (начиная с 1 в левом большинстве столбцов table_array),содержащий возвращаемую величину.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приблизительное совпадение: 1/ИСТИНА предполагает, что первый столбец таблицы отсортжен в алфавитном или числовом порядке, а затем будет выполнять поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВКП(90;A1:B100;2;ИСТИНА).
Точное совпадение: 0/ЛОЖЬ ищет точное значение в первом столбце. Например, =ВКП(«Кузнецов»;A1:B100;2;ЛОЖЬ).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
