Функция ВПР — это один из самых полезных компьютерных трюков не только в 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 — будет выдаваться первое значение.
- Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#). Чтобы ее не отображать, используйте формулы
- Чтобы определить какой именно номер столбца записать в формулу, при выделение диапазона показывается, номер столбца.
Здравствуй уважаемый читатель!
В этой статье я хочу описать, наверное, одну из наиболее полезных функций в Excel – это функция ВПР (VLOOKUP). О функции можно сказать многое, но самое первое, что стоить отметить, это то, что функция относится к сложным и наименее понятной.
В статье я постараюсь простым и доступным языком описать, как работает функция ВПР, а также на примерах показать ее особенности, описание и синтаксис.
Итак, что же в сущности это за функция, что она делает, и как она пишется. Самое простое объяснение, о работе функции ВПР, она ищет в перечне значений, по заданному вами критерию, уникальному идентификатору, определенную информацию, которая с ним связана.
Если розшифровать название функции ВПР, то с первой буквой станет понятно, как работает, эта функция, В – означает «вертикальная»
, то есть она ищет значения в , а вот для горизонтальных списков у нас будет . Впервые функция ВПР стала доступна нам с Excel 2000.
Функция ВПР в Excel имеет следующий синтаксис:
=ВПР(_искомое значение_;_таблица_; _номер столбика_; _[интервальный просмотр]_) , где:
- искомое_значение – это именно то значение, которое нам нужно искать, а это может быть любое значение: число, дата, текст, ссылка на ячейку, которая содержит нужное значение или значение, получаемое другой формулой;
- таблица – это два и больше столбика с разнообразными данными, кстати, регистр символов в поиске, функцией не учитывается;
- номер_столбца – это номер столбика в указанном диапазоне, из которого будет получено значение, которое находится в найденной строке;
- интервальный_просмотр – этот параметр определяет, что же именно будем искать, для точного совпадения аргумент будет равен «ЛОЖЬ» (FALSE) или приблизительное совпадение, аргумент станет равным ИСТИНА (TRUE) . Этот параметр не является обязательным, но тем не менее он важен. В примерах, далее, я продемонстрирую, как создать формулы для точного и приблизительного совпадения.
Теперь на примере рассмотрим, как произвести поиск, каких-либо данных на другом листе, так как на практике, функцию ВПР очень редко используют для поиска на текущем листе:
ВПР(«GM»;$A$5:$B$10;2)
Формула ищет текст «GM», в столбике А на текущем листе.
Совет!
При использовании аргумента «таблица», желательно использовать, такой вариант, как (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.
Рассмотрим пример поиска значений, как работает функция ВПР в другой рабочей книге:
ВПР(”GM”;[Путь к файлу]База!А2:В10;2)
Как видите ничего сложного, просто усложнился аргумент «таблица» , теперь к нему прикреплена ссылка на файл с его именем.
Совет! Ввод формулы лучше делать при включённых двух файлах, на одном вы вводите функцию ВПР, а когда дойдете до аргумента «таблица», перейдете на второй файл и мышкой выделите нужный вам диапазон для поиска.
Еще можно рассмотреть пример, когда точное значение нам неизвестно мы будем использовать в формуле ВПР символы подстановки. Иногда нам нужно найти не что-то конкретное, а нечто обладающее общими признаками, как вариант у нас есть много диванов разных моделей, как и прочей мебели, но выбрать нам надо только их.
Функция ВПР, при подстановке,может использовать такие символы:
- «?» (знак вопроса ) – позволит заменить один любой символ;
- «*» (звёздочка) – заменит любое количество и последовательность символов.
ВПР(“A*”;$A$2:$B$10;1;ЛОЖЬ)
Совет!
Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ».
Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:
- если аргумент «интервальный просмотр» равен «ЛОЖЬ» , в таком случае формула ищет точное совпадение с аргументом «искомое значение». Если формула встретит два и более значения, отвечающих аргументу «искомое значение», то будет выбрано первое из списка, в случае, когда совпадение не найдены, формула вернет ;
- если же аргумент «интервальный просмотр» имеет значение «ИСТИНА» формула будет искать приблизительное совпадение, точнее будет, что функция сначала поищет точное совпадение, а уже потом, не найдя его, выберет приблизительное.
Совет! В случае, когда аргумент «интервальный просмотр» ровняется «ИСТИНА» или же не указан, значение в первом столбике нужно , от меньшего к большому. Иначе имеется возможность ошибочного результата от функции ВПР.
Рассмотрим, как работает функция ВПР, когда производится поиск по точному. Для примера, попытаемся найти, какой автомобиль движется со скоростью 200 км/час. Я думаю, такая формула не будет для вас сложной:
=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)
Несмотря на то, что значений 200 у нас несколько, получили только одно, так как при точном совпадении функции ВПР, система использует только первое значение которое было найдено в указанном диапазоне.
Теперь испытаем, работу функции ВПР для приблизительного совпадения значений. Поищем, какой автомобиль ездит со скоростью 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр»
равняется «ИСТИНА»
— вы выполняете сортировку вашего диапазона значений по первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее наибольшее значение от заданного условия, а после поиск прекращается. Если же вы не последуете совету по сортировке, итогом будет сообщение об или другие странные результаты.
Для поиска используем ВПР следующего вида:
=ВПР(260;$A$2:$B$15;2;ИСТИНА)
Итак, наша формула вернула найденное значение 240, хотя у нас есть и значение 270 и это значение вроде как ближе, но особенность того, как работает функция ВПР, это то что при использовании точного совпадения она ищет самое большое значение в списке, но не превышающие его.
Я думаю примеры, которые я рассматривал в статье, помогут вам понятьвсё о работе функции ВПР, как ее используют и для чего ее нужно использовать.
Хотя есть и множество более продвинутых примеров, которые мы рассмотрим в других статьях.
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
В Excel множество замечательных и удобных функций и возможностей. Сегодня мы поговорим о функции ВПР (в английской версии Excel она называется VLOOKUP).
Для чего нужна функция ВПР в Excel
Допустим, у вас есть две таблицы с ценами на запчасти, первая за 2015 год, вторая за 2016. Вы хотите сравнить цены каждой запчасти по прошествии года.
Таблица с ценами 2015 года:
Таблица с ценами 2016 года:
Казалось бы, что сложного в этой задаче – просто упорядочим обе таблицы по имени запчасти и скопируем данные из одной таблицы в другую. Но проблема в том в 2016 году появились новые запчасти и данные просто не совпадут. Может появиться желание вручную перенести данные. В данном примере это легко выполнимо, а представьте, что таких строк у вас не 10, а 10 000, к примеру. Именно для решения таких задач функция ВПР подходит просто идеально.
Как использовать функцию ВПР в Excel
Скорее всего, ваши таблицы будут в разных файлах. Для удобства, скопируйте их на разные листы одной книги, как в примере выше. Нам нужно чтобы в таблице 2016 года в третьем столбце появились данные из 2015 года. Выбираем ячейку С2 и пишем «=ВПР(» без кавычек, Excel сразу выведет подсказку:
Рассмотрим синтаксис команды «ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])» подробнее.
Искомое значение – значения, которые мы будем искать в другой таблица, в нашем случае это ячейки А2-А10.
Таблица – часть второй таблицы (в нашем случае 2015 года) где мы будем искать искомые значения. В нашем случае переходим на вкладку 2015 и выделяем столбцы A и B.
Номер столбца – столбец из которого будут подставляться данные, в нашем случае это «2».
Интервальный просмотр – искать точное совпадение или примерное. Нам нужно точное, поэтому указываем «ЛОЖЬ».
Итого, конечная формула получается такая: «=ВПР(A2;"2015"!A:B;2;ЛОЖЬ)» . То есть, еще раз, как работает формула на конкретном примере: берется слово МОТОР из таблицы 2015 и ищется в таблице 2016, после чего берется число 1000 из второго столбца и подставляется в таблицу 2016.
Остается только растянуть формулу на всю длину столбца, ну и в нашем случае добавить столбец с разницей.
У двух значений написано #Н/Д – это значит «Нет данных», это как раз те позиции, которых не было в 2015 году.
Вот и все! Если будут вопросы – ! Удачи!
Что такое функция ВПР в Excel и как с ней работать? Для работы с таблицами в Excel существует ряд специальных функций. Одна из самых распространённых это ВПР, используемая для извлечения данных одной таблицы в другую. У нее есть английское наименование - VLOOKUP.
Пример использования функции
У нас есть две таблицы - товары и накладная. Необходимо, используя формулу, заполнить накладную. Используем для этого следующую формулу:
ВПР(искомое значение; таблица; номер_столбца; [интервальный просмотр])Важно! Аргументы функции отделяются друг от друга символом «;».
В нашем случае формула приобретает следующее значение:
ВПР(A3;F3:H13;3;0)Где,
- A3 – искомое значение, это надпись «деревянные кольца», именно ее мы ищем в таблице «Товары»;
- F3:H13 – границы таблицы в которой мы ищем данные;
- 3 – номер столбца;
- 0 – параметр, отвечающий за точность совпадения. Для получения точного совпадения ставим значение 0.
Варианты заполнения формулы
Есть два варианта. Первый, используемый в основном опытными пользователями - это написание формулы в ручную. Им вы сможете воспользоваться, когда поймете, как формируется функция ВПР. Либо вариант с использованием всплывающих окон Excel.
Сравнение двух таблиц
С помощью функции можно быстро сопоставить значения, например, сравнивать изменения в ценах, для этого нужно разместить при помощи ВПР в соседней, новой колонке значение цен из другой таблицы. Таким образом, две колонки цен окажутся рядом для наглядного сравнения.
С несколькими условиями
С несколькими условиями ВПР используется для нахождения товара по двум или более параметрам. Для этого:
- Создайте два условия для поиска.
- Добавьте новый столбец, в нем будут объединятся столбцы с теми данными по которым нам нужно осуществить поиск.
- Таким образом, мы получим один нужный нам столбец и сможем воспользоваться формулой как обычно.
Выпадающий список
Функцию также используют для работы с данными оформленными в виде выпадающего списка. В этом случае в качестве искомого значения выбирается ячейка с выпадающим списком.
Совет! Все варианты работы с функцией ВПР аналогичны, а сама работа производится автоматически, главное правильно определить ее аргументы.
Если у вас есть вопрос по работе с продуктами Microsoft, об этом. Вместе мы найдем ответ.
Используя функцию ВПР при работе в Excel, Вы можете извлекать требуемую информацию из электронных таблиц. Для этих целей Excel предлагает несколько функций, но ВПР среди них самая распространенная. В этом уроке мы познакомимся с функцией ВПР , а также рассмотрим ее возможности на простом примере.
Функция ВПР (вертикальный просмотр) ищет значение в крайнем левом столбце исследуемого диапазона, а затем возвращает результат из ячейки, которая находится на пересечении найденной строки и заданного столбца.
Пример 1
Например, на рисунке ниже приведен список из 10 фамилий, каждой фамилии соответствует свой номер. Требуется по заданному номеру извлечь фамилию.
С помощью функции ВПР сделать это достаточно просто:
Из формулы видно, что первым аргументом функции ВПР является ячейка С1, где мы указываем искомый номер. Вторым выступает диапазон A1:B10, который показывает, где следует искать. И последний аргумент – это номер столбца, из которого необходимо возвратить результат. В нашем примере это второй столбец. Нажав Enter , мы получим нужный результат:
Пример 2
Рассмотрим еще один пример. На рисунке ниже представлены те же 10 фамилий, что и раньше, вот только номера идут с пропусками.
Если попробовать найти фамилию для несуществующего номера (например, 007), то формула вместо того, чтобы выдать ошибку, благополучно вернет нам результат. Как такое может быть?
Дело в том, что функция ВПР имеет еще и четвертый аргумент, который позволяет задавать так называемый интервальный просмотр. Он может иметь два значения: ИСТИНА и ЛОЖЬ. Причем, если аргумент опущен, то это равносильно истине.
В случае, когда четвертый аргумент имеет значение ИСТИНА, функция сначала ищет точное соответствие, а если такого нет, то ближайшее, которое меньше чем заданное. Именно поэтому функция ВПР возвратила фамилию “Панченко”. Если бы мы задали “008”, то формула также вернула бы “Панченко”.
В случае, когда четвертый аргумент функции ВПР имеет логическое значение ЛОЖЬ, функция ищет точное соответствие. Например, на рисунке ниже формула вернет ошибку, поскольку точного соответствия не найдено.
Если четвертый аргумент функции ВПР содержит значение ИСТИНА или опущен, то крайний левый столбец должен быть отсортирован в порядке возрастания. Если этого не сделать, функция ВПР может вернуть неправильный результат.
Для тех, кто любит создавать не вертикальные, а горизонтальные таблицы, в Excel существует аналог ВПР , но для горизонтального поиска.
Горизонтальный ВПР в Excel
В Microsoft Excel существует функция ГПР (горизонтальный просмотр), которая очень похожа на ВПР , разница лишь в том, что диапазон просматривается не по вертикали, а по горизонтали. ГПР ищет заданное значение в верхней строке исследуемого диапазона и возвращает результат из ячейки, которая находится на пересечении найденного столбца и заданной строки.
Если представить вышеприведенный пример в горизонтальной форме, то формула будет выглядеть следующим образом:
Как видите, все достаточно просто!
На этом наш урок завершен. Сегодня мы познакомились, наверное, с самым популярным инструментом Microsoft Excel – функцией ВПР и разобрали ее возможности на нескольких простых примерах. Надеюсь, что этот урок был для Вас полезным. Всего Вам доброго и успехов в изучении Excel.
