Как правильно использовать впр в экселе пошаговая инструкция

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

Совет: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.

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

Самая простая функция ВПР означает следующее:

=ВПР(искомое значение; место для его поиска; номер столбца в диапазоне с возвращаемым значением; возврат приблизительного или точного совпадения — указывается как 1/ИСТИНА или 0/ЛОЖЬ).

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

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

Синтаксис 

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

Например:

  • =ВПР(A2;A10:C20;2;ИСТИНА)

  • =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ)

  • =ВПР(A2;’Сведения о клиенте’!A:F;3;ЛОЖЬ)

Имя аргумента

Описание

искомое_значение    (обязательный)

Значение для поиска. Искомое значение должно находиться в первом столбце диапазона ячеек, указанного в аргументе таблица.

Например, если таблица охватывает диапазон ячеек B2:D7, искомое_значение должно находиться в столбце B.


Искомое_значение
может являться значением или ссылкой на ячейку.

таблица    (обязательный)

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

Первый столбец в диапазоне ячеек должен содержать искомое_значение. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.

Узнайте, как выбирать диапазоны на листе .

номер_столбца    (обязательный)

Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение.

интервальный_просмотр    (необязательный)

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

  • Вариант Приблизительное совпадение — 1/ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по номерам, а затем выполняет поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВПР(90;A1:B100;2;ЛОЖЬ).

  • Вариант Точное совпадение — 0/ЛОЖЬ осуществляет поиск точного значения в первом столбце. Например, =ВПР(«Иванов»;A1:B100;2;ЛОЖЬ).

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

  1. Значение, которое вам нужно найти, то есть искомое значение.

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

  3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.

  4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)).

Примеры

Вот несколько примеров использования функции ВПР.

Пример 1

=ВПР (B3,B2:E7,2,ЛОЖЬ)

ВПР ищет "Иванов" в первом столбце (столбец B) в таблице B2:E7 и возвращает "Григорий" из второго столбца (столбец C) таблицы.  Значение ЛОЖЬ возвращает точное совпадение.

Пример 2

=ВПР (102,A2:C7,2,ЛОЖЬ)

ВПР ищет точное совпадение (ЛОЖЬ) фамилии для 102 (искомое_значение) во втором столбце (столбец B) в диапазоне A2:C7 и возвращает "Григорий".

Пример 3

=ЕСЛИ(ВПР(103; А1:E7;2;ЛОЖЬ)="Кузьмина","Найдено","Не найдено")

ЕСЛИ проверяет, возвращает ли ВПР значение "Кузьмина" как фамилию сотрудника, соответствующую 103 (искомое_значение) в A1:E7 (таблица). Так как фамилия сотрудницы под номером 103 на самом деле "Сазонова", возвращается результат "Не найдено".

Пример 4

=ЦЕЛОЕ(ДОЛЯГОДА(ДАТА(2014,6,30),ВПР(105,A2:E7,5,ЛОЖЬ),1))



ВПР ищет дату рождения сотрудника под номером 109 (искомое_значение) в диапазоне A2:E7 (таблица), и возвращает 04.03.1955. Функция ДОЛЯГОДА вычитает эту дату рождения из даты 30.06.2014 и возвращает значение, которое с помощью функции ЦЕЛОЕ преобразуется в целое число 59.

Пример 5

ЕСЛИ(ЕНД(ВПР(105;A2:E7;2;ЛОЖЬ)) = ИСТИНА,"Сотрудник не найден",ВПР(105;A2:E7;2;ЛОЖЬ)) 



ЕСЛИ проверяет, возвращает ли ВПР фамилию из столбца B для сотрудника 105 (искомое_значение). Если ВПР находит фамилию, то функция ЕСЛИ отображает фамилию, в противном случае ЕСЛИ возвращает "Сотрудник не найден". ЕНД гарантирует, что если функция ВПР возвращает #Н/Д, то вместо #Н/Д отображается "Сотрудник не найден".



В этом примере возвращается значение "Егоров" — то есть фамилия под номером 105.

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

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

Здесь столбцы A–F и H содержат значения или формулы, которые используют значения только на этом листе, а в остальных столбцах используется функция ВПР и значения столбца А (код клиента) и столбца B (адвокат) для получения данных из других таблиц.

  1. Скопируйте таблицу с общими полями на новый лист и присвойте имя.

  2. Щелкните Данные > Работа с данными > Отношения, чтобы открыть диалоговое окно «Управление отношениями».

    Диалоговое окно "Управление связями".

  3. Для каждого отношения в списке обратите внимание на следующее.

    • Поле, которое связывает таблицы (указано в скобках в диалоговом окне). Это искомое_значение для вашей формулы ВПР.

    • Имя связанной таблицы подстановки. Это таблица в вашей формуле ВПР.

    • Поле (столбец) в связанной таблице подстановки, содержащее данные, которые вам нужны в новом столбце. Эта информация не отображается в диалоговом окне «Управление отношениями». Чтобы увидеть, какое поле нужно получить, посмотрите на связанную таблицу подстановки. Обратите внимание на номер столбца (A=1) — это номер_столбца в вашей формуле.

  4. Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце, используя сведения, собранные на шаге 3.

    В нашем примере столбец G использует адвоката (искомое_значение) для получения данных ставки из четвертого столбца (номер_столбца = 4) из таблицы листа «Адвокаты», тблАдвокаты (таблица), с помощью формулы =ВПР([@Адвокат];тбл_Адвокаты;4;ЛОЖЬ).

    Формула также может использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это =ВПР(A2;’Адвокаты’!A:D;4;ЛОЖЬ).

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

Проблема

Возможная причина

Неправильное возвращаемое значение

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

#Н/Д в ячейке

  • Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.

  • Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.

#ССЫЛКА! в ячейке

Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.

Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.

#ЗНАЧ! в ячейке

Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.

#ИМЯ? в ячейке

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате «Иванов» и никак иначе.

Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.

Ошибки #ПЕРЕНОС! в ячейке

Эта конкретная ошибка #ПЕРЕНОС! обычно означает, что формула использует неявное пересечение для искомого значения и применяет весь столбец в качестве ссылки. Например, =ВПР(A:A;A:C;2;ЛОЖЬ). Вы можете устранить эту проблему, привязав ссылку подстановки с помощью оператора @, например: =ВПР(@A:A;A:C;2;ЛОЖЬ). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку вместо целого столбца: =ВПР(A2;A:C;2;ЛОЖЬ).

Действие

Примечания

Используйте абсолютные ссылки в аргументе интервальный_просмотр

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

Узнайте, как использовать абсолютные ссылки на ячейки.

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

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

Используйте подстановочные знаки

Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.

Например, с помощью функции =ВПР(«Ивано?»;B2:E7;2;ЛОЖЬ) будет выполнен поиск всех случаев употребления Иванов с последней буквой, которая может меняться.

Убедитесь, что данные не содержат ошибочных символов.

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

Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

См. также

Функция ПРОСМОТРX

Видео: когда и как использовать ВПР

Краткий справочник: функция ВПР

Исправление ошибки #Н/Д в функции ВПР

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Функция ГПР

Нужна дополнительная помощь?

Нужны дополнительные параметры?

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

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

В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения. 

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

Таблица Excel

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

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

Как создать функцию ВПР в Excel

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

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

  1. Выделить ячейку и вписать функцию.

  2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

После этого открывается окно, где можно заполнить ячейки аргументов формулы.

аргументы функции ВПР

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы ВПР в Эксель

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

=ВПР(A2;

Функция ВПР, искомое значение

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

Функция ВПР, таблица

Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат. 

  1. Формула сканирует таблицу по вертикали.

  2. Находит в самом левом столбце совпадение с искомым значением.

  3. Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.

  4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Функция ВПР, принцип работы

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Автозаполнение

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

Автозаполнение ячеек в Excel

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

  • В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

  • Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

ВПР и приблизительный интервальный просмотр

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

Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.

Задача для Excel, ВПР

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

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Окно для формулы ВПР в Excel

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

Сортировка в Excel с помощью ВПР

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Некорректная работа функции ВПР

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

  • Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз. 

  • Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

  • Функцию можно вписать вручную или в специальном окне (Shift + F3).

  • Искомое значение – относительная ссылка, а таблица – абсолютная. 

  • Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением. 

  • Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

  • Порядок работы с функцией подходит для Гугл-таблиц.

#Руководства


  • 0

Как перенести данные из одной таблицы в другую, если строки идут не по порядку? Разбираемся на примере каталога авто — переносим цены.

Иллюстрация: Meery Mary для Skillbox Media

Ксеня Шестак

Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.

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

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

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

Это каталог автомобилей с ценами

Это список клиентов: указаны забронированные авто, но нет цен

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

Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.

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

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

Важно!

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


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

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

Сначала нужно построить функцию. Для этого выделяем ячейку, куда функция перенесёт найденное значение.

В нашем случае нужно перенести цены на авто из каталога в список клиентов. Для этого добавим пустой столбец «Цена, руб.» в таблицу с клиентами и выберем ячейку напротив первого клиента.

Выделяем ячейку, в которую нужно перенести данные

Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».

Нажимаем сюда, чтобы открыть окно построения

Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.

Справа появляется окно «Построитель формул». В нём через поисковик находим функцию ВПР и нажимаем «Вставить функцию».

Нажимаем сюда, чтобы открылась функция ВПР

Появляется окно для ввода аргументов функции. Как их заполнять — разбираемся ниже.

Так выглядит окно для ввода аргументов

Последовательно разберём каждый аргумент: искомое значение, таблица, номер столбца, интервальный просмотр.

Искомое значение — название ячейки с одинаковыми данными для обеих таблиц, по которым функция будет искать данные для переноса. В нашем примере это модель авто. Функция найдёт модель в таблице с каталогом авто, возьмёт оттуда стоимость и перенесёт в таблицу с клиентами.

Порядок действий, чтобы указать значение, выглядит так:

  1. Ставим курсор в окно «Искомое значение» в построителе формул.
  2. Выбираем первое значение столбца «Марка, модель» в таблице с клиентами. Это ячейка A2.

Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2).

Указать номер ячейки можно и вручную, но проще нажать на неё

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

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

Важно!

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

Порядок действий для указания диапазона:

  1. Ставим курсор в окно «Таблица» в построителе формул.
  2. Переходим в таблицу «Каталог авто».
  3. Выбираем диапазон, в который попадают столбцы «Марка, модель» и «Цена, руб.». Это A2:E19.
  4. Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.

Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).

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

Номер столбца — порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 — самый левый столбец, 2 — столбец правее и так далее.

В нашем случае значение для переноса — цена — находится в пятом столбце слева.

Если столбцы не пронумерованы, посчитайте их вручную

Чтобы задать номер, установите курсор в окно «Номер столбца» в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).

Интервальный просмотр — условное значение, которое настроит, насколько точно сработает функция:

  • Если нужно точное совпадение при поиске ВПР, вводим 0.
  • Если нужно приближённое соответствие при поиске ВПР, вводим 1.

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

Ставим курсор в окно «Интервальный просмотр» в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0). Это окончательный вид функции.

Так выглядят настроенные аргументы функции

Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул. В выбранной ячейке появляется нужное значение. В нашем случае — цена первой модели авто.

Формула сработала для одной строки. 

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

Получилась таблица с ценами — можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.

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

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

Например, у нас несколько одинаковых моделей с разным цветом.

Таблица с ценами на модели разных цветов

И по традиции есть таблица с клиентами, которые эти модели забронировали.

Сюда нужно перенести цены автомобилей

Если идти по классическому пути ВПР, получится такая функция: fx=ВПР(A29;’каталог авто’!$A$29:$E$35;5;0). В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.

Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester — 3 190 000 рублей, у всех Toyota C-HR — 2 365 000 рублей.

Такой результат получится, если использовать обычную функцию ВПР

Поэтому в этом варианте нужно искать стоимость авто сразу по двум критериям — модель и цвет. Для этого нужно изменить формулу вручную. В строке ссылок ставим курсор сразу после искомого значения.

Дописываем в формулу фразу ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29, где:

  • ‘каталог авто’!$B$29:$B$35 — закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.
  • B29 — искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом — дополнительным параметром для поиска.

Итоговая функция такая: fx=ВПР(A29;ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29;’каталог авто’!$A$29:$E$35);5;0). Теперь значения цен переносятся верно.

Так выглядит таблица, в которую ВПР переносит данные на основе двух совпадений

Как использовать ВПР в «Google Таблицах»? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).

Как зарабатывать больше с помощью нейросетей?
Бесплатный вебинар: 15 экспертов, 7 топ-нейросетей. Научитесь использовать ИИ в своей работе и увеличьте доход.

Узнать больше

author__photo

ВПР в Excel: пошаговая инструкция для чайников

Содержание

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

Зачем нужна функция ВПР и когда ее используют

Альтернативное название ВПР в Excel — Vlookup. Инструмент перетаскивает значения в соответствии с заданной формулой. Рассмотрим на примере, как он работает. 

Компания торгует мебелью. У менеджера две таблицы: в одной приведены характеристики товаров и стоимость, а в другой — контакты покупателей, которые оставили заявки.

Таблица с контактами покупателей

Таблица с контактами покупателей

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

Область применения формулы ВПР не ограничивается торговлей. Другие примеры использования: 

  • учет имущества, финансов;
  • кадровое делопроизводство;
  • сопоставление данных из двух таблиц. 

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

Как пользоваться ВПР: руководство с примерами

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

Вставляем формулу

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

Добавляем колонку со стоимостью в лист 2 (в примере он назван «Заявки»). Выделяем верхнюю ячейку нового столбца. В представленном образце добавлен еще один параметр — «Стоимость» (программа автоматически рассчитает чек заказа).

В верхней части рабочей панели перейдите в раздел «Формулы». Слева найдите инструмент «fx» (вставка функции). Когда нажмете, появится окно со списком доступных функций — выберите «ВПР».

Заполняем аргументы

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

Искомое значение. Им в формуле ВПР служит содержимое ячейки, общее для обеих таблиц. Впоследствии программа сканирует диапазон первого листа, пока не найдет указанный идентификатор. В рассматриваемом варианте это «Аэлита». Активируем ячейку — «координаты» появятся в поле ввода. В строке формул автоматически появился фрагмент функции: «=ВПР(D3)».

Таблица. В поле «Таблица» указываем область данных (берем ее из первого листа — у нас это «Каталог»). Функция работает корректно, если элемент, который вы ищете, размещен в первом столбце. Теперь документ отображается так:

Закрепляем диапазон («F4» для Windows, комбинация «Cmd+T» для macOS). Фиксация диапазона дает возможность распространить формулу ВПР в Excel на все строки столбца. Теперь в строке формул отображается выражение «=ВПР(D3; Каталог!$A$2:$E$21)».

Номер столбца. Введем порядковый номер колонки, в которой указана искомая характеристика. «Стоимость» находится в пятом столбце. Теперь вверху отображается формула «=ВПР(D3;Каталог!$A$2:$E$21;5)».

Интервальный просмотр. Поле воспринимает два значения: «0» или «1», и определяет точность поиска. Для абсолютного совпадения установите «0». Если вас устроит приблизительное соответствие, введите «1». Теперь в строке формул отображается функция ВПР в окончательном варианте: «=ВПР(D3;Каталог!$A$2:$E$21;5;0)».

Получаем результат

Как только вы введете четыре аргумента и нажмете «Ок», в ячейке, в которую введена формула, увидите результат поиска.

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

Программа автоматически перенесет стоимость заказанной мебели из первого листа во второй. Ошибки исключены.

Аналогичным образом протягивается формула для колонки «Стоимость заказа». На изображении выше в 18-й строке в колонке с ценами отображается «#Н/Д». Это означает, что формула ВПР в ячейке некорректна.

Почему не работает ВПР в Excel

Ошибки возникают по разным причинам. Рассмотрим типичные варианты. 

В ячейке F18 выводится «#Н/Д». Разберемся, почему это произошло. Проверим, есть ли искомое значение в листе «Каталог» (для 18-й строки это «Хилтон»). Оно есть, но написано с ошибкой — «Хиллтон». Так как одна буква не совпадает, приложение воспринимает это как отсутствие значения. Также ВПР не выведет результаты, если в ячейках с требуемыми критериями поиска присутствуют лишние пробелы или замена символов (латиница вместо кириллицы).

Часто пользователи забывают зафиксировать диапазон. В этом случае формула нашего примера отобразится в таком виде: «=ВПР(D3;Каталог!A2:E21;5;0)» (отсутствует знак $). Тогда таблица будет выглядеть так:

Вследствие смещения диапазона во многих ячейках отображается ошибка. Решение: установите «$» перед всеми координатами формулы. 

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

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

Давайте узнаем, какие названия моделей отсутствуют в первом листе. Удалим из него три строки:

Изменения отразились в листе «Заявки» — в трех строках возникло выражение «#Н/Д». Именно эти модели мы удалили вначале.

Проводить такое сравнение можно по любым другим параметрам: стоимости, цене, характеристикам товара.

Пример

Есть два прайса, менеджер хочет сравнить старые цены с новыми. Данные представлены в таком виде:

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

Если обновились не только цены, но и ассортимент, результат получится такой:

Выглядит не очень эстетично. Скорректируем с помощью функции «ЕСЛИОШИБКА». После знака «=» в строке формул введите «ЕСЛИОШИБКА([ВПР — формула]); значение, которое выводится при ошибке)». В нашем случае формула получила вид: «=ЕСЛИОШИБКА(ВПР(D3;$A$3:$B$15;2;0);0)». А таблица стала выглядеть так:

Оптимизируйте маркетинг и увеличивайте продажи вместе с Calltouch

Узнать подробнее

Коротко о главном

  • ВПР — функция Excel, предназначенная для поиска и переноса данных из одного листа в другой. 
  • В общем виде формула выглядит так: ВПР (искомое значение; диапазон; номер столбца, в котором находятся нужные данные; тип интервального просмотра — «1» или «0»). «1» обозначает приблизительное совпадение («истина»), «0» — точное («ложь»).
  • Порядок запуска ВПР: перейдите в «Формулы», выберите «вставить функцию», затем — «ВПР». Далее введите аргументы функции, нажмите «Ок». Выделите первую ячейку с введенной формулой, установите курсор на квадратик внизу справа, протяните по всему столбцу, чтобы воспользоваться автозаполнением.

FAQ

Как написать формулу ВПР в Excel?

Существуют два способа:
— Найдите во вкладке «Формулы» опцию «Вставить функцию» и нажмите на нее.
— Кликните на «fx» в строке формул в одной из вкладок таблицы. Справа вы увидите окно «Построитель формул» — найдите в нем функцию ВПР и кликните на «Вставить функцию».

Почему выходит значение «0»?

Самая распространенная причина — ошибка во вводе аргумента «номер_столбца» или указание числа меньше единицы для значения индекса.

Почему ВПР выдает некорректные данные?

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

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

ВПР (Функция вертикального просмотра) – это функция, которая позволяет сделать «объединение» таблиц по какому-то значению столбца. Например, в одной таблице есть столбец код товара, а во втором мы можем понять его количество и цену. В английской версии ВПР имеет название VLOOKUP.

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

Читаем – как сделать умную таблицу в Excel.

Содержание

  1. Инструкция
  2. Видео
  3. Задать вопрос автору статьи

Инструкция

Формула ВПР в Excel для чайников

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

Функция ВПР в Excel: пошаговая инструкция, пример

В нашем примере у нас есть две таблицы, взятые из разных источников. Одну таблицу мы взяли на складе, где у нас расположен товар. Кладовщик его подсчитал, создал табличку и отправил бухгалтеру. У бухгалтера есть вторая таблица с ценой на каждый товар.

И тут сразу же встает вопрос – а как на теперь совместить эти таблички, чтобы цены из второй подставились к первой таблице?

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

  1. Так как нам нужно вставить цену из второй таблицы в первую – выделяем первую пустую ячейку столбца «Цена» и жмем по кнопке вставки функции, которая находится рядом со строкой значений.

Функция ВПР в Excel: пошаговая инструкция, пример

  1. Выбираем «Категорию» – «Ссылки и массивы» и находим заветную функцию. Чтобы её вставить, выделите её левой кнопкой мыши и кликните по кнопке «ОК».

Функция ВПР в Excel: пошаговая инструкция, пример

  1. А теперь нам нужно её заполнить. Кликните сначала на «Искомое значение», так чтобы там начал отображаться мигающий текстовый курсор для ввода. Далее выберите ячейку с названием товара из первого столбца. В строке «Искомое значение» обычно указывается ячейка со значением, которое есть, как в первой, так и во второй таблице. Именно по этому значению мы и должны получить цену из второй таблицы.

Функция ВПР в Excel: пошаговая инструкция, пример

  1. Теперь кликните по строке «Таблица» и полностью выделите вторую табличку, из которой мы хотим получить цену.

Функция ВПР в Excel: пошаговая инструкция, пример

  1. Очень важный момент – выделите все адреса ячеек строки «Таблица» и нажмите по клавише:

F4

  1. Мы сделаем адреса абсолютными. Но зачем? – это нужно для того, чтобы в дальнейшем использовать автозаполнение для остальных товаров. Сейчас же мы работаем только с «Шоколадом» (из моего примера). Если вы пока ничего не понимаете, ничего страшного – далее я покажу все на примере.

Функция ВПР в Excel: пошаговая инструкция, пример

  1. «Номер столбца» – здесь ставим цифру два (2), так как мы хотим получить цену из второго столбца правой таблички.
  2. «Интервальный просмотр» – ставим 0 (то есть ЛОЖЬ), так как в первом столбце наименованием товаров может иметь только точное значение, ведь это текст, а не приблизительное, как это бывает у чисел.

Функция ВПР в Excel: пошаговая инструкция, пример

  1. В конце жмем «ОК». Как видите теперь мы имеем цену для шоколада из второй таблицы. А теперь применяем маркер автозаполнения.

Читаем – что такое маркер автозаполнения и как им пользоваться.

Функция ВПР в Excel: пошаговая инструкция, пример

А вот и разъяснение нашего предыдущего вопроса – зачем мы ставили абсолютные ссылки для второй таблицы. Если бы мы этого не сделали, то при использовании маркера, все адреса ячеек начали бы сдвигаться, и мы получили неправильную цену. Ради интереса попробуйте убрать знаки доллара ($) из аргумента «Таблица» прям в формуле ВПР.

Функция ВПР в Excel: пошаговая инструкция, пример

Вот такой вот классный пример мы с вами разобрали. Теперь вы знаете, как использовать формулу ВПР в Excel, и вы теперь не чайник, а настоящий гуру в этом вопросе. Если вы столкнулись с какой-то трудностью – напишите об этом в комментариях, и преподаватели портала WiFiGiD.RU постараются вам помочь.

Видео

Понравилась статья? Поделить с друзьями:
  • Инструкции для сборки лего из разных деталей
  • Урал бк 4100 бензиновый триммер инструкция по эксплуатации
  • Cobra laser superwide инструкция на русском
  • Нистатин 250 таблетки инструкция по применению цена
  • Лекарство антистен мв инструкция по применению