Excel логические функции впр в excel

Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.

Очень удобная и часто используемая. Т.к. сопоставить вручную диапазоны с десятками тысяч наименований проблематично.

Как пользоваться функцией ВПР в Excel

Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.

Стоимость материалов – в прайс-листе. Это отдельная таблица.

Необходимо узнать стоимость материалов, поступивших на склад. Для этого нужно подставит цену из второй таблицы в первую. И посредством обычного умножения мы найдем искомое.

Алгоритм действий:

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».



3. Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.



4. Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.



5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.



6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.

Теперь найти стоимость материалов не составит труда: количество * цену.

Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

1. Выделяем столбец со вставленными ценами.
2. Правая кнопка мыши – «Копировать».
3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.



Быстрое сравнение двух таблиц с помощью ВПР

Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами.

1. В старом прайсе делаем столбец «Новая цена».



2. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу.

Функция ВПР в Excel с несколькими условиями

До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.

Таблица для примера:

Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.

Дело осложняется тем, что от одного поставщика поступает несколько наименований.

1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».



2. Таким же образом объединяем искомые критерии запроса:



3. Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.

Рассмотрим формулу детально:

1. Что ищем.
2. Где ищем.
3. Какие данные берем.

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».



3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.



4. Когда нажмем ОК – сформируется выпадающий список.

Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).

1. Открываем «Мастер функций» и выбираем ВПР.
2. Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
3. Нажимаем ВВОД и наслаждаемся результатом.

Изменяем материал – меняется цена:

Скачать пример функции ВПР в Excel

Так работает раскрывающийся список в Excel с функцией ВПР. Все происходит автоматически. В течение нескольких секунд. Все работает быстро и качественно. Нужно только разобраться с этой функцией.

Функция ВПР в программе Microsoft Excel

​Смотрите также​ОК​Заполняем их по очереди:​ прямо внутри формулы,​ любыми данными.​ с названиями материалов​ в огромных таблицах.​ Для этого нужно​Мы заполнили все параметры.​ сумму $34988, а​Вроде бы всё просто​ В противном случае,​ самый левый, то​ ключевой столбец не​Примечание​ VLOOKUP(), ищет значение​Точно таким же образом​

​Работа с обобщающей таблицей​и скопировать введенную​

Определение функции ВПР

​Искомое значение (Lookup Value)​ а не в​Минусы​ и ценами. Столбец,​ Допустим, поменялся прайс.​ подставит цену из​ Теперь нажимаем​ такой суммы нет.​ и понятно, но​ комиссия составляет, лишь​ ВПР() не поможет.​ отсортирован по возрастанию,​. Это «классическая» задача для​ в первом (в​ кликаем по значку​ подразумевает подтягивание в​ функцию на весь​

Пример использования ВПР

​- то наименование​ ячейках листа.​: Надо делать дополнительный​

​ соответственно, 2. Функция​ Нам нужно сравнить​ второй таблицы в​ОК​ Давайте посмотрим, как​ наша формула в​ 20%. Оформим это​ В этом случае​ т.к. результат формулы​ использования ВПР() (см.​ самом левом) столбце​ справа от поля​ неё значений из​ столбец.​ товара, которое функция​Плюсы​ столбец и потом,​ приобрела следующий вид:​ старые цены с​ первую. И посредством​, и Excel создаёт​ функция​ ячейке B2 становится​ в виде таблицы:​ нужно использовать функции​

1. ​ непредсказуем (если функция ВПР()​ статью Справочник).​​ таблицы и возвращает​​ ввода данных, для​ других таблиц. Если​Функция​​ должна найти в​​: Не нужен отдельный​ возможно, еще и​

   

2. ​ .​ новыми ценами.​​ обычного умножения мы​​ для нас формулу​ВПР​​ заметно сложнее. Если​​Продавец вводит данные о​​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​​ находит значение, которое​

   

3. ​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​ значение из той​ выбора таблицы, откуда​ таблиц очень много,​ВПР (VLOOKUP)​ крайнем левом столбце​ столбец, работает и​ прятать его от​Нажимаем ВВОД и наслаждаемся​

   

4. ​В старом прайсе делаем​ найдем искомое.​ с функцией​​сможет справиться с​​ Вы внимательно посмотрите​ своих продажах в​Недавно мы посвятили статью​ больше искомого, то​

   

5. ​ значение параметра​ же строки, но​ будут подтягиваться значения.​ ручной перенос заберет​возвращает ошибку #Н/Д​ прайс-листа. В нашем​

   

6. ​ с числами и​ пользователя. При изменении​ результатом.​ столбец «Новая цена».​Алгоритм действий:​ВПР​

   

7. ​ такой ситуацией.​ на формулу, то​ ячейку B1, а​ одной из самых​ она выводит значение,​Интервальный_просмотр​ другого столбца таблицы.​Выделяем всю область второй​ огромное количество времени,​​ (#N/A) если:​​ случае — слово​ с текстом.​​ числа строк в​​Изменяем материал – меняется​Выделяем первую ячейку и​Приведем первую таблицу в​.​

   

8. ​Выбираем ячейку B2 (место,​​ увидите, что третий​​ формула в ячейке​ полезных функций Excel​ которое расположено на​можно задать ЛОЖЬ​Функция ВПР() является одной​ таблицы, где будет​ а если данные​Включен точный поиск (аргумент​ «Яблоки» из ячейки​Минусы​ таблице — допротягивать​ цена:​​ выбираем функцию ВПР.​​ нужный нам вид.​
9. ​Если поэкспериментируем с несколькими​​ куда мы хотим​​ аргумент функции​ B2 определяет верную​​ под названием​​ строку выше его).​​ или ИСТИНА или​​ из наиболее используемых​ производиться поиск значений,​ постоянно обновляются, то​Интервальный просмотр=0​ B3.​: Ощутимо тормозит на​ формулу сцепки на​Скачать пример функции ВПР​ Задаем аргументы (см.​ Добавим столбцы «Цена»​ различными значениями итоговой​ вставить нашу формулу),​IF​​ ставку комиссионного вознаграждения,​​ВПР​Предположим, что нужно найти​​ вообще опустить). Значение​​ в EXCEL, поэтому​

​ кроме шапки. Опять​ это уже будет​) и искомого наименования​Таблица (Table Array)​ больших таблицах (как​ новые строки (хотя​ в Excel​ выше). Для нашего​ и «Стоимость/Сумма». Установим​ суммы продаж, то​ и находим​(ЕСЛИ), превратился в​

​ на которое продавец​и показали, как​ товар, у которого​ параметра ​ рассмотрим ее подробно. ​

​ возвращаемся к окну​ сизифов труд. К​ нет в​- таблица из​ и все формулы​ это можно упростить​Так работает раскрывающийся список​ примера: . Это​ денежный формат для​ мы убедимся, что​

​VLOOKUP​

lumpics.ru

Функция ВПР() в MS EXCEL

​ ещё одну полноценную​ может рассчитывать. В​ она может быть​ цена равна или​номер_столбца​В этой статье выбран​ аргументов функции.​ счастью, существует функция​

​Таблице​ которой берутся искомые​ массива, впрочем), особенно​ применением умной таблицы).​

​ в Excel с​ значит, что нужно​ новых ячеек.​ формула работает правильно.​(ВПР) в библиотеке​ функцию​ свою очередь, полученная​

Синтаксис функции

​ использована для извлечения​

​ наиболее близка к​​нужно задать =2,​ нестандартный подход: акцент​Для того, чтобы выбранные​ ВПР, которая предлагает​​.​​ значения, то есть​ если указывать диапазоны​Если нужно найти именно​ функцией ВПР. Все​ взять наименование материала​Выделяем первую ячейку в​Когда функция​ функций Excel:​​IF​​ ставка используется в​

​ нужной информации из​​ искомой.​ т.к. номер столбца​ сделан не на​​ значения сделать из​​ возможность автоматической выборки​Включен приблизительный поиск (​ наш прайс-лист. Для​ «с запасом» или​ число (в нашем​ происходит автоматически. В​ из диапазона А2:А15,​ столбце «Цена». В​ВПР​​Formulas​​(ЕСЛИ). Такая конструкция​ ячейке B3, чтобы​​ базы данных в​​Чтобы использовать функцию ВПР()​ Наименование равен 2​​ саму функцию, а​​ относительных абсолютными, а​​ данных. Давайте рассмотрим​Интервальный просмотр=1​​ ссылки используем собственное​

​ сразу целые столбцы​​ случае цена как​​ течение нескольких секунд.​​ посмотреть его в​ нашем примере –​работает с базами​(Формулы) >​ называется вложением функций​ рассчитать общую сумму​

​ ячейку рабочего листа.​​ для решения этой​​ (Ключевой столбец всегда​ на те задачи,​ это нам нужно,​ конкретные примеры работы​), но​ имя «Прайс» данное​ (т.е. вместо A2:A161​ раз число), то​ Все работает быстро​​ «Новом прайсе» в​​ D2. Вызываем «Мастер​ данных, аргумент​Function Library​ друг в друга.​ комиссионных, которую продавец​ Мы также упомянули,​

​ задачи нужно выполнить​ номер 1). ​ которые можно решить​ чтобы значения не​

Задача1. Справочник товаров

​ этой функции.​Таблица​ ранее. Если вы​

​ вводить A:A и​ вместо ВПР можно​ и качественно. Нужно​ столбце А. Затем​ функций» с помощью​

​Range_lookup​​(Библиотека Функций) >​ Excel с радостью​ должен получить (простое​

​ что существует два​ несколько условий:​​Для вывода Цены используйте​​ с ее помощью.​ сдвинулись при последующем​Скачать последнюю версию​, в которой происходит​​ не давали имя,​​ т.д.) Многим непривычны​ использовать функцию​ только разобраться с​ взять данные из​ кнопки «fx» (в​

​(Интервальный_просмотр) должен принимать​Lookup & Reference​​ допускает такие конструкции,​​ перемножение ячеек B1​

​ варианта использования функции​Ключевой столбец, по которому​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​ изменении таблицы, просто​ Excel​ поиск не отсортирована​​ то можно просто​​ формулы массива в​СУММЕСЛИМН (SUMIFS)​​ этой функцией.​​ второго столбца нового​ начале строки формул)​FALSE​(Ссылки и массивы).​ и они даже​ и B2).​​ВПР​​ должен производиться поиск,​

​номер_столбца​Искомое_значение​ выделяем ссылку в​Название функции ВПР расшифровывается,​ по возрастанию наименований.​ выделить таблицу, но​ принципе (тогда вам​, появившуюся начиная с​Если вы продвинутый пользователь​

​ прайса (новую цену)​ или нажав комбинацию​(ЛОЖЬ). А значение,​Появляется диалоговое окно​ работают, но их​Самая интересная часть таблицы​и только один​ должен быть самым​​нужно задать =3). ​​- это значение,​ поле​

​ как «функция вертикального​Формат ячейки, откуда берется​ не забудьте нажать​ сюда).​ Excel 2007. По​ Microsoft Excel, то​ и подставить их​ горячих клавиш SHIFT+F3.​ введённое в качестве​Function Arguments​ гораздо сложнее читать​ заключена в ячейке​ из них имеет​ левым в таблице;​Ключевой столбец в нашем​ которое Вы пытаетесь​

​«Таблица»​ просмотра». По-английски её​ искомое значение наименования​ потом клавишу​Кому лень или нет​ идее, эта функция​ должны быть знакомы​ в ячейку С2.​ В категории «Ссылки​

​Lookup_value​​(Аргументы функции). По​ и понимать.​ B2 – это​ дело с запросами​Ключевой столбец должен быть​ случае содержит числа​ найти в столбце​, и жмем на​

​ наименование звучит –​​ (например B3 в​F4​​ времени читать -​​ выбирает и суммирует​ с функцией поиска​Данные, представленные таким образом,​ и массивы» находим​(Искомое_значение) должно существовать​ очереди заполняем значения​Мы не будем вникать​ формула для определения​ к базе данных.​ обязательно отсортирован по​

Задача2. Поиск ближайшего числа

​ и должен гарантировано​ с данными.​ функциональную клавишу​ VLOOKUP. Эта функция​ нашем случае) и​

​, чтобы закрепить ссылку​ смотрим видео. Подробности​ числовые значения по​ и подстановки​

1. ​ можно сопоставлять. Находить​ функцию ВПР и​ в базе данных.​ аргументов, начиная с​
2. ​ в технические подробности​ ставки комиссионного вознаграждения.​ В этой статье​
3. ​ возрастанию;​​ содержать искомое значение​​Искомое_значение ​F4​

​ ищет данные в​

​ формат ячеек первого​ знаками доллара, т.к.​ и нюансы -​ нескольким (до 127!)​

​ВПР​ численную и процентную​ жмем ОК. Данную​ Другими словами, идёт​Lookup_value​ — почему и​ Эта формула содержит​ Вы узнаете другой​Значение параметра ​ (условие задачи). Если первый​может быть числом или​. После этого к​ левом столбце изучаемого​ столбца (F3:F19) таблицы​ в противном случае​ в тексте ниже.​ условиям. Но если​или​ разницу.​ функцию можно вызвать​​ поиск точного совпадения.​

​(Искомое_значение). В данном​ как это работает,​ функцию Excel под​ менее известный способ​Интервальный_просмотр​ столбец не содержит искомый​ текстом, но чаще​ ссылке добавляются знаки​ диапазона, а затем​ отличаются (например, числовой​ она будет соскальзывать​Итак, имеем две таблицы​ в нашем списке​

​VLOOKUP​До сих пор мы​ перейдя по закладке​В примере, что мы​ примере это общая​ и не будем​ названием​ применения функции​ нужно задать ИСТИНА или​ артикул​ всего ищут именно​

​ доллара и она​​ возвращает полученное значение​ и текстовый). Этот​ при копировании нашей​ -​ нет повторяющихся товаров​

​(если еще нет,​​ предлагали для анализа​ «Формулы» и выбрать​ рассмотрели в данной​ сумма продаж из​ вдаваться в нюансы​​IF​​ВПР​ вообще опустить.​, ​ число. Искомое значение должно​ превращается в абсолютную.​

​ в указанную ячейку.​ случай особенно характерен​ формулы вниз, на​таблицу заказов​ внутри одного месяца,​ то сначала почитайте​ только одно условие​

excel2.ru

Использование функции ВПР в Excel: неточное соответствие

​ из выпадающего списка​ статье, нет необходимости​ ячейки B1. Ставим​ записи вложенных функций.​​(ЕСЛИ). Для тех​​в Excel.​Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ​то функция возвращает значение​ находиться в первом​В следующей графе​ Попросту говоря, ВПР​ при использовании вместо​ остальные ячейки столбца​и​​ то она просто​​ эту статью, чтобы​ – наименование материала.​ «Ссылки и массивы».​ получать точное соответствие.​ курсор в поле​ Ведь это статья,​ читателей, кто не​Если Вы этого ещё​​Для вывода найденной цены (она​​ ошибки​

​ (самом левом) столбце​«Номер столбца»​ позволяет переставлять значения​ текстовых наименований числовых​​ D3:D30.​​прайс-лист​ выведет значение цены​ им стать). Для​ На практике же​Откроется окно с аргументами​ Это тот самый​

​Lookup_value​ посвященная функции​​ знаком с этой​​ не сделали, то​ не обязательно будет​ #Н/Д. ​ диапазона ячеек, указанного​нам нужно указать​ из ячейки одной​ кодов (номера счетов,​Номер_столбца (Column index number)​:​ для заданного товара​​ тех, кто понимает,​​ нередко требуется сравнить​ функции. В поле​ случай, когда функция​(Искомое_значение) и выбираем​​ВПР​​ функцией, поясню как​ обязательно прочтите прошлую​ совпадать с заданной) используйте​Это может произойти, например,​​ в​​ номер того столбца,​ таблицы, в другую​ идентификаторы, даты и​- порядковый номер​Задача — подставить цены​ и месяца:​ рекламировать ее не​ несколько диапазонов с​

• ​ «Искомое значение» -​ВПР​
• ​ ячейку B1.​
• ​, а не полное​ она работает:​
• ​ статью о функции​

Пример из жизни. Ставим задачу

​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ при опечатке при​таблице​ откуда будем выводить​ таблицу. Выясним, как​ т.п.) В этом​ (не буква!) столбца​ из прайс-листа в​Плюсы​ нужно -​ данными и выбрать​ диапазон данных первого​​должна переключиться в​​Далее нужно указать функции​ руководство по Excel.​IF(condition, value if true,​ВПР​Как видно из картинки​ вводе артикула. Чтобы не ошибиться​.​ значения. Этот столбец​ пользоваться функцией VLOOKUP​ случае можно использовать​ в прайс-листе из​

​ таблицу заказов автоматически,​: Не нужен дополнительный​ без нее не​ значение по 2,​ столбца из таблицы​ режим приближенной работы,​ВПР​Как бы там ни​ value if false)​, поскольку вся информация,​ выше, ВПР() нашла​ с вводом искомого​Таблица -​ располагается в выделенной​ в Excel.​ функции​

​ которого будем брать​ ориентируясь на название​ столбец, решение легко​ обходится ни один​ 3-м и т.д.​ с количеством поступивших​ чтобы вернуть нам​, где искать данные.​​ было, формула усложняется!​​ЕСЛИ(условие; значение если ИСТИНА;​ изложенная далее, предполагает,​ наибольшую цену, которая​ артикула можно использовать Выпадающий​ссылка на диапазон​

​ выше области таблицы.​Взглянем, как работает функция​
​Ч​ значения цены. Первый​

​ товара с тем,​​ масштабируется на большее​ сложный расчет в​ критериям.​​ материалов. Это те​​ нужный результат.​​ В нашем примере​​ А что, если​ значение если ЛОЖЬ)​ что Вы уже​

​ меньше или равна​ список (см. ячейку ​

​ ячеек. В левом​

​ Так как таблица​ ВПР на конкретном​и​ столбец прайс-листа с​

​ чтобы потом можно​ количество условий (до​​ Excel. Есть, однако,​​Таблица для примера:​ значения, которые Excel​​Например:​​ это таблица​ мы введем еще​Условие​ знакомы с принципами,​ заданной (см. файл​Е9​ столбце таблицы ищется ​ состоит из двух​ примере.​​ТЕКСТ​​ названиями имеет номер​​ было посчитать стоимость.​​ 127), быстро считает.​ одна проблема: эта​Предположим, нам нужно найти,​ должен найти во​Мы хотим определить,​Rate Table​ один вариант ставки​

​– это аргумент​ описанными в первой​ примера лист «Поиск​).​Искомое_значение​ столбцов, а столбец​У нас имеется две​для преобразования форматов​ 1, следовательно нам​В наборе функций Excel,​

​Минусы​ функция умеет искать​

Усложняем задачу

​ по какой цене​ второй таблице.​ какую ставку использовать​. Ставим курсор в​ комиссионных, равный 50%,​ функции, который принимает​ статье.​

​ ближайшего числа»). Это​Понятно, что в нашей​, а из столбцов​ с ценами является​ таблицы. Первая из​ данных. Выглядеть это​ нужна цена из​ в категории​: Работает только с​​ данные только по​​ привезли гофрированный картон​Следующий аргумент – «Таблица».​ в расчёте комиссионных​​ поле​​ для тех продавцов,​ значение либо​При работе с базами​ связано следует из​ задаче ключевой столбец​ расположенных правее, выводится​ вторым, то ставим​ них представляет собой​ будет примерно так:​

​ столбца с номером​Ссылки и массивы​ числовыми данными на​ совпадению одного параметра.​ от ОАО «Восток».​ Это наш прайс-лист.​ для продавца с​Table_array​ кто сделал объём​​TRUE​​ данных, функции​ того как функция​

​ не должен содержать​ соответствующий результат (хотя,​ номер​ таблицу закупок, в​=ВПР(ТЕКСТ(B3);прайс;0)​ 2.​(Lookup and reference)​ выходе, не применима​ А если у​ Нужно задать два​ Ставим курсор в​ объёмом продаж $34988.​(Таблица) и выделяем​ продаж более $50000.​

​(ИСТИНА), либо​ВПР​ производит поиск: если функция ВПР() находит​ повторов (в этом​ в принципе, можно​«2»​ которой размещены наименования​Функция не может найти​Интервальный_просмотр (Range Lookup)​имеется функция​ для поиска текста,​ нас их несколько?​

​ условия для поиска​ поле аргумента. Переходим​​ Функция​​ всю таблицу​

Применяем функцию ВПР к решению задачи

​ А если кто-то​FALSE​передаётся уникальный идентификатор,​ значение, которое больше​ смысл артикула, однозначно​ вывести можно вывести​.​

​ продуктов питания. В​ нужного значения, потому​- в это​​ВПР​​ не работает в​Предположим, что у нас​ по наименованию материала​ на лист с​

​ВПР​Rate Table​ продал на сумму​​(ЛОЖЬ). В примере,​​ который служит для​ искомого, то она​ определяющего товар). В​​ значение из левого​​В последней графе​ следующей колонке после​ что в коде​ поле можно вводить​(VLOOKUP)​ старых версиях Excel​ есть база данных​ и по поставщику.​ ценами. Выделяем диапазон​возвращает нам значение​, кроме заголовков.​ более $60000 –​ приведённом выше, выражение​ определения информации, которую​ выводит значение, которое​​ противном случае будет​​ столбца (в этом​«Интервальный просмотр»​

Вставляем функцию ВПР

​ наименования расположено значение​ присутствуют пробелы или​ только два значения:​.​​ (2003 и ранее).​​ по ценам товаров​Дело осложняется тем, что​​ с наименованием материалов​​ 30%, что является​​Далее мы должны уточнить,​​ тому заплатить 60%​​ B1​​ мы хотим найти​

​ расположено на строку​​ выведено самое верхнее​​ случае это будет​нам нужно указать​ количества товара, который​​ невидимые непечатаемые знаки​​ ЛОЖЬ или ИСТИНА:​Эта функция ищет​О том, как спользовать​ за разные месяцы:​ от одного поставщика​​ и ценами. Показываем,​​ абсолютно верным. Но​ данные из какого​

​ комиссионных?​​Правда ли, что B1​​ (например, код товара​ выше его. Как​ значение.​​ само​​ значение​ требуется закупить. Далее​​ (перенос строки и​​Если введено значение​ заданное значение (в​​ связку функций​​Нужно найти и вытащить​

​ поступает несколько наименований.​ какие значения функция​ почему же формула​ столбца необходимо извлечь​Теперь формула в ячейке​ меньше B5?​ или идентификационный номер​ следствие, если искомое​При решении таких задач​​искомое_значение​​«0»​ следует цена. И​

​ т.п.). В этом​0​​ нашем примере это​​ИНДЕКС (INDEX)​

​ цену заданного товара​​Добавляем в таблицу крайний​ должна сопоставить.​ выбрала строку, содержащую​ с помощью нашей​​ B2, даже если​​Или можно сказать по-другому:​ клиента). Этот уникальный​​ значение меньше минимального​​ ключевой столбец лучше​)). Часто левый столбец​​(ЛОЖЬ) или​​ в последней колонке​ случае можно использовать​или​ слово «Яблоки») в​​и​​ (​ левый столбец (важно!),​Чтобы Excel ссылался непосредственно​​ именно 30%, а​​ формулы. Нас интересует​ она записана без​Правда ли, что общая​

​ код должен присутствовать​ в ключевом столбце,​​ предварительно отсортировать (это также​​ называется​​«1»​​ – общая стоимость​ текстовые функции​ЛОЖЬ (FALSE)​ крайнем левом столбце​​ПОИСКПОЗ (MATCH)​​Нектарин​ объединив «Поставщиков» и​

​ на эти данные,​ не 20% или​​ ставка комиссионных, которая​​ ошибок, стала совершенно​ сумма продаж за​ в базе данных,​​ то функцию вернет​​ поможет сделать Выпадающий​

​ключевым​(ИСТИНА). В первом​ закупки конкретного наименования​СЖПРОБЕЛЫ (TRIM)​, то фактически это​

Заключение

​ указанной таблицы (прайс-листа)​​в качестве более​​) в определенном месяце​ «Материалы».​​ ссылку нужно зафиксировать.​​ 40%? Что понимается​​ находится во втором​​ не читаемой. Думаю,​ год меньше порогового​​ иначе​​ ошибку ​ список нагляднее). Кроме​. Если первый столбец​ случае, будут выводиться​

​ товара, которая рассчитывается​и​ означает, что разрешен​ двигаясь сверху-вниз и,​ мощной альтернативы ВПР​ (​​Таким же образом объединяем​​ Выделяем значение поля​ под приближенным поиском?​ столбце таблицы. Следовательно,​ что найдется мало​

​ значения?​​ВПР​#Н/Д.​ того, в случае​ не содержит ​ только точные совпадения,​ по вбитой уже​​ПЕЧСИМВ (CLEAN)​​ поиск только​ найдя его, выдает​ я уже подробно​Январь​ искомые критерии запроса:​ «Таблица» и нажимаем​ Давайте внесём ясность.​ для аргумента​ желающих использовать формулы​Если на этот вопрос​

​сообщит об ошибке.​​Найденное значение может быть​​ несортированного списка, ВПР() с​​искомое_значение​​ а во втором​ в ячейку формуле​​для их удаления:​​точного соответствия​ содержимое соседней ячейки​ описывал (с видео).​), т.е. получить на​

​Теперь ставим курсор в​​ F4. Появляется значок​Когда аргумент​Col_index_num​ с 4-мя уровнями​ мы отвечаем​

​ В этой статье​ далеко не самым​
​ параметром​
​,​

​ — наиболее приближенные.​

office-guru.ru

Функция ВПР в Excel для чайников и не только

​ умножения количества на​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​, т.е. если функция​ (23 руб.) Схематически​ В нашем же​ выходе​

​ нужном месте и​ $.​Range_lookup​(Номер_столбца) вводим значение​ вложенности в своих​

Как пользоваться функцией ВПР в Excel

​ДА​ мы рассмотрим такой​ ближайшим. Например, если​Интервальный_просмотр​то функция возвращает​

​ Так как наименование​ цену. А вот​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​

​ не найдет в​ работу этой функции​ случае, можно применить​152​ задаем аргументы для​В поле аргумента «Номер​(Интервальный_просмотр) имеет значение​ 2.​

​ проектах. Должен же​

1. ​(ИСТИНА), то функция​ способ использования функции​ попытаться найти ближайшую​ИСТИНА (или опущен)​ значение ошибки​ продуктов – это​
2. ​ цену нам как​Для подавления сообщения об​ прайс-листе укзанного в​ можно представить так:​ их для поиска​, но автоматически, т.е.​ функции: . Excel​ столбца» ставим цифру​TRUE​И, наконец, вводим последний​ существовать более простой​ возвращает​ВПР​ цену для 199,​ работать не будет.​ #Н/Д.​ текстовые данные, то​ раз и придется​
3. ​ ошибке​ таблице заказов нестандартного​Для простоты дальнейшего использования​ по нескольким столбцам​ с помощью формулы.​ находит нужную цену.​ «2». Здесь находятся​(ИСТИНА) или опущен,​ аргумент —​ способ?!​
4. ​value if true​, когда идентификатора не​ то функция вернет​В файле примера лист Справочник​Номер_столбца​ они не могут​ подтянуть с помощью​#Н/Д (#N/A)​ товара (если будет​ функции сразу сделайте​
5. ​ в виде формулы​ ВПР в чистом​Рассмотрим формулу детально:​ данные, которые нужно​ функция​Range_lookup​И такой способ есть!​
6. ​(значение если ИСТИНА).​ существует в базе​ 150 (хотя ближайшее​ также рассмотрены альтернативные​- номер столбца​ быть приближенными, в​ функции ВПР из​в тех случаях,​ введено, например, «Кокос»),​ одну вещь -​

​ массива. Для этого:​ виде тут не​Что ищем.​ «подтянуть» в первую​ВПР​(Интервальный_просмотр).​ Нам поможет функция​

​ В нашем случае​ данных вообще. Как​ все же 200).​

​ формулы (получим тот​Таблицы​ отличие от числовых​ соседней таблицы, которая​ когда функция не​ то она выдаст​ дайте диапазону ячеек​Выделите пустую зеленую ячейку,​

1. ​ поможет, но есть​Где ищем.​
2. ​ таблицу. «Интервальный просмотр»​просматривает первый столбец​
3. ​Важно:​ВПР​ это будет значение​
4. ​ будто функция​ Это опять следствие​

​ же результат) с​, из которого нужно​

​ данных, поэтому нам​

Быстрое сравнение двух таблиц с помощью ВПР

​ представляет собой прайс-лист.​ может найти точно​ ошибку #Н/Д (нет​ прайс-листа собственное имя.​ где должен быть​ несколько других способов​

1. ​Какие данные берем.​ — ЛОЖЬ. Т.к.​
2. ​ и выбирает наибольшее​именно в использовании​.​ ячейки B6, т.е.​ВПР​ того, что функция находит​ использованием функций ИНДЕКС(),​ выводить результат. Самый​ нужно поставить значение​Кликаем по верхней ячейке​ соответствия, можно воспользоваться​ данных).​ Для этого выделите​ результат.​ решить эту задачу.​Допустим, какие-то данные у​

​ нам нужны точные,​ значение, которое не​ этого аргумента заключается​Давайте немного изменим дизайн​

Функция ВПР в Excel с несколькими условиями

​ ставка комиссионных при​переключилась в режим​ наибольшее число, которое​ ПОИСКПОЗ() и ПРОСМОТР(). Если​ левый столбец (ключевой)​«0»​ (C3) в столбце​ функцией​Если введено значение​ все ячейки прайс-листа​Введите в строке формул​

​Это самый очевидный и​

​ нас сделаны в​ а не приблизительные​ превышает искомое.​ различие между двумя​ нашей таблицы. Мы​ общем объёме продаж​ приближенной работы, и​ меньше или равно​

​ ключевой столбец (столбец​ имеет номер 1​. Далее, жмем на​

1. ​«Цена»​ЕСЛИОШИБКА​1​ кроме «шапки» (G3:H19),​
2. ​ в нее следующую​ простой (хотя и​
3. ​ виде раскрывающегося списка.​ значения.​Важный момент:​ способами применения функции​ сохраним все те​

​ ниже порогового значения.​

1. ​ сама выбирает, какие​
2. ​ заданному.​
3. ​ с артикулами) не​

Функция ВПР и выпадающий список

​ (по нему производится​ кнопку​в первой таблице.​(IFERROR)​или​ выберите в меню​ формулу:​ не самый удобный)​

​ В нашем примере​

1. ​Нажимаем ОК. А затем​Чтобы эта схема​ВПР​
2. ​ же поля и​ Если мы отвечаем​
3. ​ данные предоставить нам,​Если нужно найти по​ является самым левым​ поиск).​
4. ​«OK»​ Затем, жмем на​

​. Так, например, вот​ИСТИНА (TRUE)​Вставка — Имя -​Нажмите в конце не​ способ. Поскольку штатная​ – «Материалы». Необходимо​ «размножаем» функцию по​ работала, первый столбец​

1. ​. При работе с​ данные, но расположим​
2. ​ на вопрос​ когда мы что-то​ настоящему ближайшее к​ в таблице, то​Параметр ​.​ значок​ такая конструкция перехватывает​, то это значит,​
3. ​ Присвоить (Insert -​ Enter, а сочетание​

​ функция​ настроить функцию так,​

​ всему столбцу: цепляем​ таблицы должен быть​

​ базами данных аргумент​ их по-новому, в​НЕТ​ хотим найти. В​ искомому значению, то ВПР() тут​ функция ВПР() не​интервальный_просмотр​Как видим, цена картофеля​«Вставить функцию»​

exceltable.com

Поиск и подстановка по нескольким условиям

Постановка задачи

​ любые ошибки создаваемые​ что Вы разрешаете​ Name — Define)​Ctrl+Shift+Enter​ВПР (VLOOKUP)​​ чтобы при выборе​​ мышью правый нижний​​ отсортирован в порядке​​Range_lookup​ более компактном виде:​(ЛОЖЬ), тогда возвращается​ определённых обстоятельствах именно​ не поможет. Такого​ применима. В этом​может принимать 2​ подтянулась в таблицу​, который расположен перед​ ВПР и заменяет​ поиск не точного,​или нажмите​, чтобы ввести формулу​умеет искать только​ наименования появлялась цена.​ угол и тянем​ возрастания.​

​(Интервальный_просмотр) должен всегда​Прервитесь на минутку и​value if false​ так и нужно.​

​ рода задачи решены​ случае нужно использовать​ значения: ИСТИНА (ищется​​ из прайс-листа. Чтобы​​ строкой формул.​ их нулями:​​ а​​CTRL+F3​ не как обычную,​​ по одному столбцу,​​Сначала сделаем раскрывающийся список:​ вниз. Получаем необходимый​Урок подготовлен для Вас​ иметь значение​ убедитесь, что новая​(значение если ЛОЖЬ).​Пример из жизни. Ставим​

Способ 1. Дополнительный столбец с ключом поиска

​ в разделе Ближайшее​ альтернативные формулы. Связка​ значение ближайшее к критерию​ не проделывать такую​В открывшемся окне мастера​​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​​приблизительного соответствия​и введите любое​ а как формулу​ а не по​Ставим курсор в ячейку​ результат.​

​ командой сайта office-guru.ru​FALSE​ таблица​ В нашем случае​ задачу​ ЧИСЛО. Там же можно​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ или совпадающее с ним)​ сложную процедуру с​

​ функций выбираем категорию​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​​, т.е. в случае​​ имя (без пробелов),​ массива.​​ нескольким, то нам​​ Е8, где и​Теперь найти стоимость материалов​Источник: http://www.howtogeek.com/howto/14455/vlookup-in-excel-part-2-using-vlookup-without-a-database/​

​(ЛОЖЬ), чтобы искать​​Rate Table​ это значение ячейки​Усложняем задачу​

​ найти решение задачи​​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ и ЛОЖЬ (ищется значение​ другими товарными наименованиями,​«Ссылки и массивы»​Если нужно извлечь не​ с «кокосом» функция​ например​Как это на самом​ нужно из нескольких​ будет этот список.​ не составит труда:​

Способ 2. Функция СУММЕСЛИМН

​Перевел: Антон Андронов​ точное соответствие. В​включает те же​ B7, т.е. ставка​Применяем функцию ВПР к​ о поиске ближайшего​​В файле примера лист Справочник показано, что​​ в точности совпадающее​ просто становимся в​. Затем, из представленного​ одно значение а​ попытается найти товар​Прайс​ деле работает:​ сделать один!​Заходим на вкладку «Данные».​ количество * цену.​Автор: Антон Андронов​ нашем же варианте​ данные, что и​ комиссионных при общем​

​ решению задачи​​ при несортированном ключевом​ формулы применимы и​ с критерием). Значение ИСТИНА​ нижний правый угол​ набора функций выбираем​

​ сразу весь набор​​ с наименованием, которое​. Теперь в дальнейшем​Функция ИНДЕКС выдает из​Добавим рядом с нашей​ Меню «Проверка данных».​Функция ВПР связала две​Функция ВПР в Excel​

Способ 3. Формула массива

​ использования функции​ предыдущая таблица пороговых​​ объёме продаж выше​​Заключение​​ столбце.​​ для ключевых столбцов​ предполагает, что первый​ заполненной ячейки, чтобы​«ВПР»​ (если их встречается​ максимально похоже на​ можно будет использовать​ диапазона цен C2:C161​ таблицей еще один​Выбираем тип данных –​

1. ​ таблицы. Если поменяется​ позволяет данные из​ВПР​
2. ​ значений.​ порогового значения.​Проиллюстрируем эту статью примером​
   
3. ​Примечание​ содержащих текстовые значения,​​ столбец в​​ появился крестик. Проводим​. Жмем на кнопку​ несколько разных), то​ «кокос» и выдаст​

​ это имя для​ содержимое N-ой ячейки​

​ столбец, где склеим​ «Список». Источник –​ прайс, то и​ одной таблицы переставить​, мы должны оставить​Основная идея состоит в​Как Вы можете видеть,​ из реальной жизни​. Для удобства, строка​ т.к. артикул часто​​таблице​​ этим крестиком до​«OK»​ придется шаманить с​ цену для этого​ ссылки на прайс-лист.​ по порядку. При​ название товара и​ диапазон с наименованиями​ изменится стоимость поступивших​ в соответствующие ячейки​ это поле пустым,​ том, чтобы использовать​ если мы берём​

​ – расчёт комиссионных​​ таблицы, содержащая найденное​ бывает текстовым значением.​отсортирован в алфавитном​ самого низа таблицы.​

​.​​ формулой массива.​ наименования. В большинстве​Теперь используем функцию​ этом порядковый номер​ месяц в единое​ материалов.​ на склад материалов​ второй. Ее английское​ либо ввести значение​ функцию​ общую сумму продаж​ на основе большого​ решение, выделена Условным форматированием.​

planetaexcel.ru

Использование функции ВПР (VLOOKUP) для подстановки значений

​ Также задача решена​ порядке или по​Таким образом мы подтянули​После этого открывается окно,​Усовершенствованный вариант функции ВПР​

Постановка задачи

​ случаев такая приблизительная​ВПР​​ нужной ячейки нам​​ целое с помощью​​Когда нажмем ОК –​​ (сегодня поступивших). Чтобы​

​ наименование – VLOOKUP.​TRUE​ВПР​ $20000, то получаем​ ряда показателей продаж.​ Это можно сделать​ для несортированного ключевого​

Решение

​ возрастанию. Это способ​ все нужные данные​​ в которое нужно​ ​ (VLOOKUP 2).​​ подстановка может сыграть​​. Выделите ячейку, куда​ ​ находит функция ПОИСКПОЗ.​​ оператора сцепки (&),​​ сформируется выпадающий список.​ этого избежать, воспользуйтесь​Очень удобная и часто​(ИСТИНА). Крайне важно​для определения нужной​ в ячейке B2​ Мы начнём с​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​ столбца.​ используется в функции​ из одной таблицы​ вставить аргументы функции.​

​Быстрый расчет ступенчатых (диапазонных)​ с пользователем злую​ она будет введена​ Она ищет связку​ чтобы получить уникальный​Теперь нужно сделать так,​ «Специальной вставкой».​ используемая. Т.к. сопоставить​ правильно выбрать этот​​ тарифной ставки по​ ставку комиссионных 20%.​ очень простого варианта,​​Примечание​​Примечание​​ по умолчанию, если​ в другую, с​ Жмем на кнопку,​​ скидок при помощи​​ шутку, подставив значение​ (D3) и откройте​ названия товара и​ столбец-ключ для поиска:​

​ чтобы при выборе​​Выделяем столбец со вставленными​​ вручную диапазоны с​ параметр.​ таблице​ Если же мы​​ и затем постепенно​: Если в ключевом​. Для удобства, строка​​ не указан другой.​​ помощью функции ВПР.​ расположенную справа от​​ функции ВПР.​​ не того товара,​​ вкладку​​ месяца (​​Теперь можно использовать знакомую​ определенного материала в​

​ ценами.​

• ​ десятками тысяч наименований​​Чтобы было понятнее, мы​Rate Table​ введём значение $40000,​ будем усложнять его,​ столбце имеется значение​ таблицы, содержащая найденное​Ниже в статье рассмотрены​Как видим, функция ВПР​
• ​ поля ввода данных,​​Как сделать «левый ВПР»​ который был на​Формулы — Вставка функции​НектаринЯнварь​ функцию​ графе цена появлялась​Правая кнопка мыши –​ проблематично.​ введём​в зависимости от​ то ставка комиссионных​ пока единственным рациональным​​ совпадающее с искомым,​​ решение, выделена Условным форматированием.​ популярные задачи, которые​ не так сложна,​ чтобы приступить к​ с помощью функций​ самом деле! Так​ (Formulas — Insert​) по очереди во​
• ​ВПР (VLOOKUP)​​ соответствующая цифра. Ставим​ «Копировать».​Допустим, на склад предприятия​TRUE​ объема продаж. Обратите​ изменится на 30%:​ решением задачи не​ то функция с​ (см. статью Выделение​ можно решить с​ как кажется на​
• ​ выбору аргумента искомого​​ ИНДЕКС и ПОИСКПОЗ​ что для большинства​ Function)​ всех ячейках склеенного​
• • ​для поиска склеенной​​ курсор в ячейку​​Не снимая выделения, правая​​ по производству тары​​(ИСТИНА) в поле​ внимание, что продавец​Таким образом работает наша​​ станет использование функции​​ параметром ​ строк таблицы в​ использованием функции ВПР().​ первый взгляд. Разобраться​ значения.​Как при помощи функции​ реальных бизнес-задач приблизительный​. В категории​ из двух столбцов​
  • ​ пары​​ Е9 (где должна​​ кнопка мыши –​​ и упаковки поступили​​Range_lookup​ может продать товаров​ таблица.​ВПР​​Интервальный_просмотр​​ MS EXCEL в​Пусть дана исходная таблица​ в её применении​Так как у нас​ ВПР (VLOOKUP) заполнять​ поиск лучше не​Ссылки и массивы (Lookup​ диапазона A2:A161&B2:B161 и​НектаринЯнварь​ будет появляться цена).​ «Специальная вставка».​ материалы в определенном​(Интервальный_просмотр). Хотя, если​ на такую сумму,​Давайте немного усложним задачу.​. Первоначальный сценарий нашей​ =ЛОЖЬ вернет первое найденное​ зависимости от условия​ (см. файл примера​ не очень трудно,​ искомое значение для​ бланки данными из​ разрешать. Исключением является​ and Reference)​

​ выдает порядковый номер​​из ячеек H3​​Открываем «Мастер функций» и​Поставить галочку напротив «Значения».​ количестве.​

Ошибки #Н/Д и их подавление

​ оставить поле пустым,​​ которая не равна​​ Установим ещё одно​ вымышленной задачи звучит​

• ​ значение, равное искомому,​​ в ячейке).​​ лист Справочник).​ зато освоение этого​​ ячейки C3, это​​ списка​
• ​ случай, когда мы​​найдите функцию​​ ячейки, где нашла​​ и J3 в​​ выбираем ВПР.​ ОК.​Стоимость материалов – в​
• ​ это не будет​ ни одному из​ пороговое значение: если​ так: если продавец​ а с параметром​Примечание​Задача состоит в том,​ инструмента сэкономит вам​«Картофель»​Как вытащить не первое,​ ищем числа, а​ВПР (VLOOKUP)​ точное совпадение. По​ созданном ключевом столбце:​Первый аргумент – «Искомое​Формула в ячейках исчезнет.​​ прайс-листе. Это отдельная​​ ошибкой, так как​​ пяти имеющихся в​​ продавец зарабатывает более​ за год делает​ =ИСТИНА — последнее​
  ​. Никогда не используйте​
• ​ чтобы, выбрав нужный​ массу времени при​, то и выделяем​ а сразу все​ не текст -​и нажмите​ сути, это первый​Плюсы​ значение» — ячейка​​ Останутся только значения.​​ таблица.​​TRUE​​ таблице пороговых значений.​
  ​ $40000, тогда ставка​
  ​ объём продаж более​

​ (см. картинку ниже).​ ВПР() с параметром ​​ Артикул товара, вывести​​ работе с таблицами.​ соответствующее значение. Возвращаемся​ значения из таблицы​ например, при расчете​ОК​​ способ, но ключевой​ ​: Простой способ, знакомая​​ с выпадающим списком.​​Необходимо узнать стоимость материалов,​— это его​ К примеру, он​

​ комиссионных возрастает до​

​ $30000, то его​

P.S.

​Если столбец, по которому​Интервальный_просмотр​ его Наименование и​Автор: Максим Тютюшев​ к окну аргументов​Функции VLOOKUP2 и VLOOKUP3​ Ступенчатых скидок.​

Ссылки по теме

• ​. Появится окно ввода​ столбец создается виртуально​
• ​ функция, работает с​ Таблица – диапазон​Функция помогает сопоставить значения​
• ​ поступивших на склад.​ значение по умолчанию:​ мог продать на​
• ​ 40%:​ комиссионные составляют 30%.​ производится поиск не​ ИСТИНА (или опущен) если​
• ​ Цену. ​Функция ВПР(), английский вариант​ функции.​
• ​ из надстройки PLEX​Все! Осталось нажать​

planetaexcel.ru

​ аргументов для функции:​

Сегодня мы начинаем серию статей, описывающих одну из самых полезных функций Excel – ВПР (VLOOKUP). Эта функция, в то же время, одна из наиболее сложных и наименее понятных.

В этом учебнике по ВПР я постараюсь изложить основы максимально простым языком, чтобы сделать процесс обучения для неискушённых пользователей максимально понятным. Кроме этого, мы изучим несколько примеров с формулами Excel, которые продемонстрируют наиболее распространённые варианты использования функции ВПР.

• Общее описание и синтаксис
• Примеры с функцией ВПР
  • Как, используя ВПР, выполнить поиск на другом листе Excel
  • Поиск в другой рабочей книге с помощью ВПР
  • Как использовать именованный диапазон или таблицу в формулах с ВПР
  • Использование символов подстановки в формулах с ВПР
• Точное или приближенное совпадение в функции ВПР
• ВПР в Excel – это нужно запомнить!

Функция ВПР в Excel – общее описание и синтаксис

Итак, что же такое ВПР? Ну, во-первых, это функция Excel. Что она делает? Она ищет заданное Вами значение и возвращает соответствующее значение из другого столбца. Говоря техническим языком, ВПР ищет значение в первом столбце заданного диапазона и возвращает результат из другого столбца в той же строке.

В самом привычном применении, функция ВПР ищет в базе данных заданный уникальный идентификатор и извлекает из базы какую-то связанную с ним информацию.

Первая буква в названии функции ВПР (VLOOKUP) означает Вертикальный (Vertical). По ней Вы можете отличить ВПР от ГПР (HLOOKUP), которая осуществляет поиск значения в верхней строке диапазона – Горизонтальный (Horizontal).

Функция ВПР доступна в версиях Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP и Excel 2000.

Синтаксис функции ВПР

Функция ВПР (VLOOKUP) имеет вот такой синтаксис:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Как видите, функция ВПР в Microsoft Excel имеет 4 параметра (или аргумента). Первые три – обязательные, последний – по необходимости.

• lookup_value (искомое_значение) – значение, которое нужно искать.Это может быть значение (число, дата, текст) или ссылка на ячейку (содержащую искомое значение), или значение, возвращаемое какой-либо другой функцией Excel. Например, вот такая формула будет искать значение 40:

  =VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Если искомое значение будет меньше, чем наименьшее значение в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).

• table_array (таблица) – два или более столбца с данными.Запомните, функция ВПР всегда ищет значение в первом столбце диапазона, заданного в аргументе table_array (таблица). В просматриваемом диапазоне могут быть различные данные, например, текст, даты, числа, логические значения. Регистр символов не учитывается функцией, то есть символы верхнего и нижнего регистра считаются одинаковыми.Итак, наша формула будет искать значение 40 в ячейках от A2 до A15, потому что A – это первый столбец диапазона A2:B15, заданного в аргументе table_array (таблица):

  =VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

• col_index_num (номер_столбца) – номер столбца в заданном диапазоне, из которого будет возвращено значение, находящееся в найденной строке.Крайний левый столбец в заданном диапазоне – это 1, второй столбец – это 2, третий столбец – это 3 и так далее. Теперь Вы можете прочитать всю формулу:

  =VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

  Формула ищет значение 40 в диапазоне A2:A15 и возвращает соответствующее значение из столбца B (поскольку B – это второй столбец в диапазоне A2:B15).

  

Если значение аргумента col_index_num (номер_столбца) меньше 1, то ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). А если оно больше количества столбцов в диапазоне table_array (таблица), функция вернет ошибку #REF! (#ССЫЛКА!).

• range_lookup (интервальный_просмотр) – определяет, что нужно искать:
  • точное совпадение, аргумент должен быть равен FALSE (ЛОЖЬ);
  • приблизительное совпадение, аргумент равен TRUE (ИСТИНА) или вовсе не указан.

  Этот параметр не обязателен, но очень важен. Далее в этом учебнике по ВПР я покажу Вам несколько примеров, объясняющих как правильно составлять формулы для поиска точного и приблизительного совпадения.

Примеры с функцией ВПР

Я надеюсь, функция ВПР стала для Вас чуть-чуть понятнее. Теперь давайте рассмотрим несколько примеров использования ВПР в формулах с реальными данными.

Как, используя ВПР, выполнить поиск на другом листе Excel

На практике формулы с функцией ВПР редко используются для поиска данных на том же листе. Чаще всего Вы будете искать и извлекать соответствующие значения из другого листа.

Чтобы, используя ВПР, выполнить поиск на другом листе Microsoft Excel, Вы должны в аргументе table_array (таблица) указать имя листа с восклицательным знаком, а затем диапазон ячеек. К примеру, следующая формула показывает, что диапазон A2:B15 находится на листе с именем Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)

Конечно же, имя листа не обязательно вводить вручную. Просто начните вводить формулу, а когда дело дойдёт до аргумента table_array (таблица), переключитесь на нужный лист и выделите мышью требуемый диапазон ячеек.

Формула, показанная на скриншоте ниже, ищет текст «Product 1» в столбце A (это 1-ый столбец диапазона A2:B9) на листе Prices.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Пожалуйста, помните, что при поиске текстового значения Вы обязаны заключить его в кавычки («»), как это обычно делается в формулах Excel.

Для аргумента table_array (таблица) желательно всегда использовать абсолютные ссылки (со знаком $). В таком случае диапазон поиска будет оставаться неизменным при копировании формулы в другие ячейки.

Поиск в другой рабочей книге с помощью ВПР

Чтобы функция ВПР работала между двумя рабочими книгами Excel, нужно указать имя книги в квадратных скобках перед названием листа.

Например, ниже показана формула, которая ищет значение 40 на листе Sheet2 в книге Numbers.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Вот простейший способ создать в Excel формулу с ВПР, которая ссылается на другую рабочую книгу:

1. Откройте обе книги. Это не обязательно, но так проще создавать формулу. Вы же не хотите вводить имя рабочей книги вручную? Вдобавок, это защитит Вас от случайных опечаток.
2. Начните вводить функцию ВПР, а когда дело дойдёт до аргумента table_array (таблица), переключитесь на другую рабочую книгу и выделите в ней нужный диапазон поиска.

На снимке экрана, показанном ниже, видно формулу, в которой для поиска задан диапазон в рабочей книге PriceList.xlsx на листе Prices.

Функция ВПР будет работать даже, когда Вы закроете рабочую книгу, в которой производится поиск, а в строке формул появится полный путь к файлу рабочей книги, как показано ниже:

Если название рабочей книги или листа содержит пробелы, то его нужно заключить в апострофы:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Как использовать именованный диапазон или таблицу в формулах с ВПР

Если Вы планируете использовать один диапазон поиска в нескольких функциях ВПР, то можете создать именованный диапазон и вводить его имя в формулу в качестве аргумента table_array (таблица).

Чтобы создать именованный диапазон, просто выделите ячейки и введите подходящее название в поле Имя, слева от строки формул.

Теперь Вы можете записать вот такую формулу для поиска цены товара Product 1:

=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)

Большинство имен диапазонов работают для всей рабочей книги Excel, поэтому нет необходимости указывать имя листа для аргумента table_array (таблица), даже если формула и диапазон поиска находятся на разных листах книги. Если же они находятся в разных книгах, то перед именем диапазона нужно указать название рабочей книги, к примеру, вот так:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)

Так формула выглядит гораздо понятнее, согласны? Кроме того, использование именованных диапазонов – это хорошая альтернатива абсолютным ссылкам, поскольку именованный диапазон не меняется при копировании формулы в другие ячейки. Значит, Вы можете быть уверены, что диапазон поиска в формуле всегда останется корректным.

Если преобразовать диапазон ячеек в полноценную таблицу Excel, воспользовавшись командой Table (Таблица) на вкладке Insert (Вставка), то при выделении диапазона мышью, Microsoft Excel автоматически добавит в формулу названия столбцов (или название таблицы, если Вы выделите всю таблицу).

Готовая формула будет выглядеть примерно вот так:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)

А может даже так:

=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)

При использовании именованных диапазонов, ссылки будут вести к тем же ячейкам, не зависимо от того, куда Вы копируете функцию ВПР в пределах рабочей книги.

Использование символов подстановки в формулах с ВПР

Как и во многих других функциях, в ВПР Вы можете использовать следующие символы подстановки:

• Знак вопроса (?) – заменяет один любой символ.
• Звёздочка (*) – заменяет любую последовательность символов.

Использование символов подстановки в функциях ВПР может пригодиться во многих случаях, например:

• Когда Вы не помните в точности текст, который нужно найти.
• Когда Вы хотите найти какое-то слово, которое является частью содержимого ячейки. Знайте, что ВПР ищет по содержимому ячейки целиком, как при включённой опции Match entire cell content (Ячейка целиком) в стандартном поиске Excel.
• Когда в ячейке содержатся дополнительные пробелы в начале или в конце содержимого. В такой ситуации Вы можете долго ломать голову, пытаясь понять, почему формула не работает.

Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами

Предположим, что Вы хотите найти определенного клиента в базе данных, показанной ниже. Вы не помните его фамилию, но знаете, что она начинается на «ack». Вот такая формула отлично справится с этой задачей:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Теперь, когда Вы уверены, что нашли правильное имя, можно использовать эту же формулу, чтобы найти сумму, оплаченную этим клиентом. Для этого достаточно изменить третий аргумент функции ВПР на номер нужного столбца. В нашем случае это столбец C (3-й в диапазоне):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Вот ещё несколько примеров с символами подстановки:

~ Находим имя, заканчивающееся на «man»:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Находим имя, начинающееся на «ad» и заканчивающееся на «son»:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Находим первое имя в списке, состоящее из 5 символов:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Чтобы функция ВПР с символами подстановки работала правильно, в качестве четвёртого аргумента всегда нужно использовать FALSE (ЛОЖЬ). Если диапазон поиска содержит более одного значения, подходящего под условия поиска с символами подстановки, то будет возвращено первое найденное значение.

Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР

А теперь давайте разберём чуть более сложный пример, как осуществить поиск с помощью функции ВПР по значению в какой-то ячейке. Представьте, что в столбце A находится список лицензионных ключей, а в столбце B список имён, владеющих лицензией. Кроме этого, у Вас есть часть (несколько символов) какого-то лицензионного ключа в ячейке C1, и Вы хотите найти имя владельца.

Это можно сделать, используя вот такую формулу:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Эта формула ищет значение из ячейки C1 в заданном диапазоне и возвращает соответствующее значение из столбца B. Обратите внимание, что в первом аргументе мы используем символ амперсанда (&) до и после ссылки на ячейку, чтобы связать текстовую строку.

Как видно на рисунке ниже, функция ВПР возвращает значение «Jeremy Hill», поскольку его лицензионный ключ содержит последовательность символов из ячейки C1.

Заметьте, что аргумент table_array (таблица) на скриншоте сверху содержит имя таблицы (Table7) вместо указания диапазона ячеек. Так мы делали в предыдущем примере.

Точное или приближенное совпадение в функции ВПР

И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).

Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.

• Если аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4:

  =VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)

• Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value (искомое_значение).

Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР может вернуть ошибочный результат.

Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты.

Пример 1: Поиск точного совпадения при помощи ВПР

Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).

Давайте вновь обратимся к таблице из самого первого примера и выясним, какое животное может передвигаться со скоростью 50 миль в час. Я верю, что вот такая формула не вызовет у Вас затруднений:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6. Формула возвращает значение из ячейки B5. Почему? Потому что при поиске точного совпадения функция ВПР использует первое найденное значение, совпадающее с искомым.

Пример 2: Используем ВПР для поиска приблизительного совпадения

Когда Вы используете функцию ВПР для поиска приблизительного совпадения, т.е. когда аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.

Это очень важно, поскольку функция ВПР возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A (#Н/Д).

Вот теперь можно использовать одну из следующих формул:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) или =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) или =ВПР(69;$A$2:$B$15;2)

Как видите, я хочу выяснить, у какого из животных скорость ближе всего к 69 милям в час. И вот какой результат мне вернула функция ВПР:

Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.

Надеюсь, эти примеры пролили немного света на работу с функцией ВПР в Excel, и Вы больше не смотрите на неё, как на чужака. Теперь не помешает кратко повторить ключевые моменты изученного нами материала, чтобы лучше закрепить его в памяти.

ВПР в Excel – это нужно запомнить!

1. Функция ВПР в Excel не может смотреть налево. Она всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом table_array (таблица).
2. В функции ВПР все значения используются без учета регистра, то есть маленькие и большие буквы эквивалентны.
3. Если искомое значение меньше минимального значения в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
4. Если 3-й аргумент col_index_num (номер_столбца) меньше 1, функция ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). Если же он больше количества столбцов в диапазоне table_array (таблица), функция сообщит об ошибке #REF! (#ССЫЛКА!).
5. Используйте абсолютные ссылки на ячейки в аргументе table_array (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска. Попробуйте в качестве альтернативы использовать именованные диапазоны или таблицы в Excel.
6. Когда выполняете поиск приблизительного совпадения, не забывайте, что первый столбец в исследуемом диапазоне должен быть отсортирован по возрастанию.
7. И, наконец, помните о важности четвертого аргумента. Используйте значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ) обдуманно, и Вы избавитесь от многих головных болей.

В следующих статьях нашего учебника по функции ВПР в Excel мы будем изучать более продвинутые примеры, такие как выполнение различных вычислений при помощи ВПР, извлечение значений из нескольких столбцов и другие. Я благодарю Вас за то, что читаете этот учебник, и надеюсь встретить Вас снова на следующей неделе!

Алгоритм работы функции:

1. Указываем, что искать (абсолютное значение или что-то в ячейке).
2. Указываем, где искать (некий диапазон в другой таблице).
3. Указываем, какое значение возвращать. Это – самая сильная сторона ВПР. Когда функция находит совпадение (например, фамилию работника), она возвращает не саму фамилию, а значение, которое находится в той же строке. То есть мы можем вернуть зарплату этого работника, его фамилию, дату рождения – в общем, все, что захотим, при условии, что все данные в одной строке принадлежат одному работнику (данные упорядочены).
4. Указываем точность совпадения. Эксель позволяет вернуть как точное совпадение, так и частичное. Рекомендуем всегда пользоваться точным совпадением, потому что «частичное совпадение» – вещь весьма расплывчатая, Эксель может взять вообще не те данные, которые вам на самом деле нужны. Как-то повлиять на алгоритм выбора простыми методами нельзя.

На практике это выглядит вот так: ВПР(B2;’Дневная потребность’!$A$3:$B$12;2;0), где:

• B2 – что мы будем искать.
• ‘Дневная потребность’!$A$3:$B$12 – где мы будем искать. Указываем таблицу на другом листе.
• 2 – из какого столбца мы будем возвращать данные, когда найдем соответствие.
• 0 – используем точное соответствие (1 – использовать приблизительное, не рекомендуется).

Примеры

Разберем несколько задач с использованием функции ВПР в Excel.

1. Поиск неизвестного в общей таблице.

Это простейшая задача научит быстро обнаруживать необходимые данные в больших таблицах.

Исходная информация:

Есть таблица в Excel с перечнем лекарственных препаратов, их производителем и стоимостью.

Задача: найти стоимость препарата Хепилор.

Решение состоит в последовательности следующих действий:

• Выбор критерия: в ячейку В12 вводим название Лекарственного препарата «Хепилор».
• Выбор массива: выбираем диапазон начала и конца таблицы, где должен осуществляться поиск: В3:D10.
• Выбор номера столбца: указываем номер столбца, из которого должна быть считана информация в одной строке с названием препарата. В нашем примере это 3, т. к. столбец №1 расположен вне области нужного нам диапазона.
• Ставим функцию «0» или «Ложь».

Аналогично можно произвести поиск производителя Хепилора. Для этого потребуется заменить номер столбца, где расположены необходимые данные, т. е. 3 на 2.

2. Пример расчета неизвестного показателя из исходных данных.

Предположим, что у нас есть приют для котиков. Нам нужно посчитать, сколько пакетиков корма нам нужно купить на завтра и сколько денег на это нужно. У нас есть 2 таблицы на разных страницах:

• В первой таблице указано имя кота и его вес, сюда же мы будем вносить количество пакетиков, стоимость и итоговые значения.

  

• Во второй таблице указано количество пакетиков на день в зависимости от веса кота.

  

В строке 2 указаны номера колонок – колонки обязательно нужно нумеровать, без этого функция работать не будет.

Итак, щелкаем на первое пустое поле в основной таблице, нажимаем на значок создания функции и выбираем ВПР.

Нам нужно заполнить аргументы функции:

Искомое значение – это то значение, по которому мы будем искать. В нашем случае это «Вес кота», поэтому указываем B2. Таблица – это место, где мы будем искать. Нужно выбрать всю таблицу за исключением заголовка и номеров колонок.

Кроме того, нужно зафиксировать значения диапазона таблицы, чтобы она не «поехала», когда мы будем распространять формулу на другие ячейки основной таблицы. Для этого выделяем диапазон в конструкторе формулы и жмем F4.

Далее указываем номер столбца, из которого нужно брать данные. В нашем случае – второй столбец, поскольку в нем указано количество пакетиков. В «Интервальный просмотр» ставим 0, чтобы искать по точному соответствию.

Применяем – получаем результат. Растягиваем его на остальные ячейки.

Пакетик корма стоит 60 рублей, поэтому высчитываем стоимость как D2=C2*60 (и растягиваем на остальные пустые ячейки), в C9 пишем =СУММ(C2:C8), в D10 пишем =СУММ(D2:D8).

Получаем 14.5 пакетиков в день на сумму 870 рублей, по факту придется купить 15 на сумму 900 рублей, ибо половину пакетика никто не продаст.

3. Комбинирование таблиц с ВПР.

Исходные данные: имеем 2 таблицы.

«Отчет о количестве товара» и «Отчет о цене за единицу товара».

Задача: объединить данные двух таблиц.

Порядок действий:

Выбираем ячейку для вставления данных (D3) и пишем функцию: ВПР (В3;F3:G14;2;0), где:

• выбор критерия:В3;
• выбор диапазона: F3:G14;
• № столбца: 2;
• стандартно: «0» или «Ложь».

Алгоритм решения:

1. Поиск совпадений с верхней ячейки первого столбца.
2. Поиск соответствия установленному критерию сверху вниз.
3. После того, как найден Хепилор, производится отсчет столбцов вправо.
4. ВПР выдает искомое значение, в нашем случае это цена – 86,90.

Чтобы в столбец D первой таблицы вставить данные не по одной строке, а в целом, нужно скопировать функцию до последнего критерия. Но, для избежания «съезжания» массива вниз, нужно использовать абсолютные ссылки для диапазона в ячейке D3. Для этого нужно выделить диапазон F3:G14 и нажать клавишу F4, далее завершить копирование формулы.

Итоговая таблица будет такая:

Здесь вы сможете скачать примеры применения ВПР Excel

ВПР в Гугл Таблицах

У Гугла все работает абсолютно так же. Функция называется VLOOKUP, но вы можете написать ВПР, и Гугл автоматически поменяет название после того, как вы примените функцию. Единственная особенность – формулу нужно вводить непосредственно в поле, конструктор недоступен.

Примеры с пошаговыми инструкциями

1. Пример. Осуществляем поиск данных из списка.

Дана таблица с именами и оценками учащихся.

Требуется быстро найти оценку конкретного ученика, например, Martha.

Алгоритм: =VLOOKUP(E2,$A$2:$B$10,2,False)

При введении в ячейку Е2 любого имени, в Е3 будет отображаться значения его оценки.

Это очень удобно, когда исходных данных много, таблицы большие, а информация нужна срочно.

2. Пример.

Создаем 2 таблицы – основную и ту, в которой мы будем искать информацию.

Выбираем первую ячейку, пишем =ВПР(. После этого Гугл предлагает нам ввести или выбрать данные. Данные разделяются знаком ;.

Чтобы указать диапазон таблицы, переходим на нужную страницу и выделяем таблицу. Выделили – не забудьте нажать на F4, чтобы зафиксировать диапазон.

Вводим номер столбца и 0, чтобы искать точное совпадение. Распространяем формулу на остальные ячейки, считаем цену для каждого кота и общие суммы.

Возможные ошибки новичков

• Не фиксирован диапазон. Если вы не зафиксируете диапазон при указании границ таблицы с данными, в первой ячейке формула применится верно, а вот в остальных будут неправильные значения, потому что диапазон таблицы «поедет» вслед за смещением. Поэтому не забывайте поставить $ перед каждой координатой или просто выберите все и нажмите F4.
• Неправильно выбран диапазон таблицы. В диапазоне нужно указывать всю таблицу, за исключением заголовка и номеров столбцов. Если у вас выскакивает ошибка, связанная с неправильной ссылкой – поищите проблему в диапазоне таблицы.
• Поиск происходит не по первому столбцу таблицы. В этом случае результат может быть непредсказуем – всегда ищите совпадение именно в первом столбце.

Что почитать по теме

• Справка от Майкрософт.
• Справка от Гугла.

FAQ

Какой результат выдаст функция, если найдет несколько вхождений в таблице, в которой мы ищем данные?

Функция вернет результат из первого найденного вхождения.

Можно ли писать внутри функции формулы?

Да, вы можете как написать формулу внутри одного из параметров функции, так и передать результаты работы функции в формулу. Например, =ECЛИ(ВПР(“Иванов”;’сотрудники’!$B$3:$B$203;3;0)=1;”Есть”;”Не найден”) будет писать «Есть», если такой сотрудник есть в базе (и в специальной колонке ему присвоено значение 1), и «Не найден» в противном случае.

Подведем итоги 

Тезисно:

• ВПР позволяет вам вставить какие-то данные из другого листа (или с этого же, если они попадают под определенные критерии).
• Чтобы написать функцию, вам нужно указать: какие данные нужно искать; где их искать; из какой колонки таблицы брать результат; искать ли по точному совпадению.
• В Excel и Google Sheets – одинаковый синтаксис для ВПР, единственная разница – Excel позволяет создать функцию через мастера создания функций.