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

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

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

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

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

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

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

Синтаксис 

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

Например:

• =ВПР(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

Пример 2

Пример 3

Пример 4

Пример 5

С помощью функции ВПР вы можете объединить несколько таблиц в одну, если одна из таблиц содержит поля, общие для всех остальных. Это может быть особенно удобно, если вам нужно поделиться книгой с пользователями более старых версий 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

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

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

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

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

Функция ГПР

Иллюстрация: 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 топ-нейросетей. Научитесь использовать ИИ в своей работе и увеличьте доход.
Узнать больше

Содержание

Функция ВПР существенно упрощает обработку данных в 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)». А таблица стала выглядеть так:

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

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

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

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

​Работа с обобщающей таблицей​ТЕКСТ​

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

​ попытается найти товар​ ссылки используем собственное​ указанной таблицы (прайс-листа)​ графе цена появлялась​ разницу.​ таблицу. «Интервальный просмотр»​ второй таблицы в​ меньше или равно​ больше искомого, то​ повторов (в этом​отсортирован в алфавитном​- это значение,​ в другую, с​F4​ соседней таблицы, которая​ подразумевает подтягивание в​для преобразования форматов​

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

​ с наименованием, которое​ имя «Прайс» данное​ двигаясь сверху-вниз и,​

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

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

   

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

   

3. ​ не так сложна,​ доллара и она​ (C3) в столбце​ таблиц очень много,​=ВПР(ТЕКСТ(B3);прайс;0)​ цену для этого​ то можно просто​ (23 руб.) Схематически​ будет появляться цена).​

   

4. ​ – наименование материала.​ значения.​Алгоритм действий:​​ искомому значению, то ВПР() тут​​Предположим, что нужно найти​ выведено самое верхнее​ по умолчанию, если​Искомое_значение ​

   

5. ​ как кажется на​ превращается в абсолютную.​«Цена»​ ручной перенос заберет​Функция не может найти​ наименования. В большинстве​

   

6. ​ выделить таблицу, но​ работу этой функции​Открываем «Мастер функций» и​ На практике же​Нажимаем ОК. А затем​Приведем первую таблицу в​

   

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

   
8. ​ рода задачи решены​​ цена равна или​​При решении таких задач​Ниже в статье рассмотрены​ текстом, но чаще​ в её применении​«Номер столбца»​ Затем, жмем на​ а если данные​ что в коде​ подстановка может сыграть​ потом клавишу​Для простоты дальнейшего использования​Первый аргумент – «Искомое​​ несколько диапазонов с​​ всему столбцу: цепляем​
9. ​ Добавим столбцы «Цена»​​ в разделе Ближайшее​​ наиболее близка к​ ключевой столбец лучше​​ популярные задачи, которые​​ всего ищут именно​​ не очень трудно,​​нам нужно указать​ значок​ постоянно обновляются, то​ присутствуют пробелы или​ с пользователем злую​F4​ функции сразу сделайте​ значение» — ячейка​ данными и выбрать​ мышью правый нижний​ и «Стоимость/Сумма». Установим​ ЧИСЛО. Там же можно​ искомой.​​ предварительно отсортировать (это также​​ можно решить с​ число. Искомое значение должно​​ зато освоение этого​​ номер того столбца,​

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

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

​, который расположен перед​ сизифов труд. К​ (перенос строки и​ не того товара,​ знаками доллара, т.к.​ дайте диапазону ячеек​ Таблица – диапазон​ 3-м и т.д.​ вниз. Получаем необходимый​ новых ячеек.​

​ о поиске ближайшего​

lumpics.ru

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

​ для решения этой​ список нагляднее). Кроме​Пусть дана исходная таблица​ (самом левом) столбце​ массу времени при​ значения. Этот столбец​ строкой формул.​ счастью, существует функция​ т.п.). В этом​ который был на​ в противном случае​

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

​ прайс-листа собственное имя.​ с названиями материалов​

Основные элементы функции ВПР

​ критериям.​ результат.​Выделяем первую ячейку в​

Поиск значений на другом листе

​ при несортированном ключевом​ задачи нужно выполнить​ того, в случае​

Копирование формулы с функцией ВПР

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

Содержание курса

​ ВПР, которая предлагает​ случае можно использовать​ самом деле! Так​

support.office.com

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

​ она будет соскальзывать​ Для этого выделите​ и ценами. Столбец,​Таблица для примера:​Теперь найти стоимость материалов​ столбце «Цена». В​ столбце.​ несколько условий:​

​ несортированного списка, ВПР() с​ лист Справочник).​ в​Автор: Максим Тютюшев​

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

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

​ соответственно, 2. Функция​

​Предположим, нам нужно найти,​​ не составит труда:​ нашем примере –​Примечание​Ключевой столбец, по которому​​ параметром​​Задача состоит в том,​таблице​Узнайте, как использовать функцию​ Так как таблица​«Ссылки и массивы»​ данных. Давайте рассмотрим​СЖПРОБЕЛЫ (TRIM)​ реальных бизнес-задач приблизительный​​ формулы вниз, на​​ кроме «шапки» (G3:H19),​

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

​Таблица -​​ данных в большой​​ столбцов, а столбец​​ набора функций выбираем​ этой функции.​ПЕЧСИМВ (CLEAN)​ разрешать. Исключением является​ D3:D30.​Вставка — Имя -​

​Нажимаем ВВОД и наслаждаемся​​ от ОАО «Восток».​​ таблицы. Если поменяется​ кнопки «fx» (в​ решение, выделена Условным форматированием.​ левым в таблице;​ работать не будет.​ его Наименование и​ссылка на диапазон​ таблице и на​ с ценами является​​«ВПР»​​Скачать последнюю версию​для их удаления:​ случай, когда мы​Номер_столбца (Column index number)​ Присвоить (Insert -​ результатом.​

​ Нужно задать два​ прайс, то и​ начале строки формул)​ Это можно сделать​

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

​Ключевой столбец должен быть​В файле примера лист Справочник​ Цену. ​

​ ячеек. В левом​ других листах в​ вторым, то ставим​. Жмем на кнопку​ Excel​

​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​​ ищем числа, а​- порядковый номер​ Name — Define)​

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

​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​ не текст -​​ (не буква!) столбца​​или нажмите​

​ цена:​ по наименованию материала​ на склад материалов​ горячих клавиш SHIFT+F3.​Примечание​ возрастанию;​ формулы (получим тот​​. Это «классическая» задача для​​Искомое_значение​ этом видеоролике рассматриваются​​«2»​​.​ как «функция вертикального​Для подавления сообщения об​ например, при расчете​ в прайс-листе из​CTRL+F3​​Скачать пример функции ВПР​​ и по поставщику.​

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

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

​Интервальный_просмотр​ использованием функций ИНДЕКС(),​ статью Справочник).​ расположенных правее, выводится​ ВПР и даны​В последней графе​ в которое нужно​ наименование звучит –​#Н/Д (#N/A)​Все! Осталось нажать​ значения цены. Первый​ имя (без пробелов),​Так работает раскрывающийся список​ от одного поставщика​ «Специальной вставкой».​ функцию ВПР и​

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

​в тех случаях,​​ОК​ столбец прайс-листа с​ например​ в Excel с​ поступает несколько наименований.​Выделяем столбец со вставленными​ жмем ОК. Данную​ то функция с​

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

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

​ ценами.​ функцию можно вызвать​ параметром ​Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ​ с артикулами) не​

​Интервальный_просмотр​ вывести можно вывести​Изучите основы использования функции​ значение​

1. ​ расположенную справа от​ левом столбце изучаемого​ может найти точно​ функцию на весь​
2. ​ 1, следовательно нам​. Теперь в дальнейшем​ происходит автоматически. В​
3. ​ левый столбец (важно!),​​Правая кнопка мыши –​​ перейдя по закладке​Интервальный_просмотр​

​Для вывода найденной цены (она​

​ является самым левым​можно задать ЛОЖЬ​ значение из левого​ ВПР. (2:37)​

​«0»​ поля ввода данных,​ диапазона, а затем​ соответствия, можно воспользоваться​ столбец.​ нужна цена из​ можно будет использовать​ течение нескольких секунд.​ объединив «Поставщиков» и​ «Копировать».​ «Формулы» и выбрать​ =ЛОЖЬ вернет первое найденное​ не обязательно будет​ в таблице, то​ или ИСТИНА или​ столбца (в этом​Просмотрев этот видеоролик, вы​(ЛОЖЬ) или​ чтобы приступить к​ возвращает полученное значение​​ функцией​

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

​«1»​ выбору аргумента искомого​ в указанную ячейку.​ЕСЛИОШИБКА​ВПР (VLOOKUP)​ 2.​ ссылки на прайс-лист.​ и качественно. Нужно​Таким же образом объединяем​ кнопка мыши –​ «Ссылки и массивы».​

​ а с параметром​​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ применима. В этом​ параметра ​ само​ аргументами функции. (3:04)​

​(ИСТИНА). В первом​​ значения.​ Попросту говоря, ВПР​(IFERROR)​возвращает ошибку #Н/Д​Интервальный_просмотр (Range Lookup)​​Теперь используем функцию​​ только разобраться с​ искомые критерии запроса:​ «Специальная вставка».​Откроется окно с аргументами​ =ИСТИНА — последнее​

​Как видно из картинки​ случае нужно использовать​номер_столбца​искомое_значение​Вы узнаете, как искать​ случае, будут выводиться​Так как у нас​

excel2.ru

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

​ позволяет переставлять значения​. Так, например, вот​ (#N/A) если:​- в это​ВПР​ этой функцией.​

​Теперь ставим курсор в​Поставить галочку напротив «Значения».​ функции. В поле​ (см. картинку ниже).​ выше, ВПР() нашла​

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

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

​ искомое значение для​ из ячейки одной​ такая конструкция перехватывает​

​Включен точный поиск (аргумент​ поле можно вводить​. Выделите ячейку, куда​Кому лень или нет​ нужном месте и​ ОК.​ «Искомое значение» -​Если столбец, по которому​

​ наибольшую цену, которая​

1. ​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ т.к. номер столбца​ называется​ листах. (2:37)​ а во втором​ ячейки C3, это​
2. ​ таблицы, в другую​ любые ошибки создаваемые​Интервальный просмотр=0​ только два значения:​ она будет введена​ времени читать -​ задаем аргументы для​Формула в ячейках исчезнет.​ диапазон данных первого​ производится поиск не​ меньше или равна​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ Наименование равен 2​ключевым​Вы узнаете, как использовать​ — наиболее приближенные.​«Картофель»​ таблицу. Выясним, как​
3. ​ ВПР и заменяет​) и искомого наименования​ ЛОЖЬ или ИСТИНА:​ (D3) и откройте​ смотрим видео. Подробности​ функции: . Excel​ Останутся только значения.​ столбца из таблицы​ самый левый, то​ заданной (см. файл​
4. ​В файле примера лист Справочник показано, что​ (Ключевой столбец всегда​. Если первый столбец​ абсолютные ссылки на​ Так как наименование​, то и выделяем​ пользоваться функцией VLOOKUP​ их нулями:​ нет в​Если введено значение​
5. ​ вкладку​ и нюансы -​ находит нужную цену.​​ с количеством поступивших​ ВПР() не поможет.​ примера лист «Поиск​
6. ​ формулы применимы и​ номер 1). ​ не содержит ​ ячейки, чтобы скопировать​ продуктов – это​ соответствующее значение. Возвращаемся​ в Excel.​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​Таблице​0​

​Формулы — Вставка функции​ в тексте ниже.​Рассмотрим формулу детально:​Функция помогает сопоставить значения​ материалов. Это те​ В этом случае​ ближайшего числа»). Это​

​ для ключевых столбцов​Для вывода Цены используйте​искомое_значение​

​ формулу вниз по​ текстовые данные, то​ к окну аргументов​Взглянем, как работает функция​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​.​или​ (Formulas — Insert​

1. ​Итак, имеем две таблицы​Что ищем.​
2. ​ в огромных таблицах.​ значения, которые Excel​
3. ​ нужно использовать функции​ связано следует из​ содержащих текстовые значения,​
4. ​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​,​

​ столбцу. (3:30)​ они не могут​

​ функции.​

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

​ ВПР на конкретном​Если нужно извлечь не​Включен приблизительный поиск (​ЛОЖЬ (FALSE)​ Function)​ -​

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

​ Нам нужно сравнить​ второй таблице.​Функция ВПР в Excel​ производит поиск: если функция ВПР() находит​

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

​ бывает текстовым значением.​нужно задать =3). ​ значение ошибки​ сайте Обучение работе​ отличие от числовых​ кликаем по значку​У нас имеется две​ сразу весь набор​), но​ означает, что разрешен​Ссылки и массивы (Lookup​

​и​

​Допустим, какие-то данные у​ старые цены с​Следующий аргумент – «Таблица».​ позволяет данные из​ значение, которое больше​ Также задача решена​Ключевой столбец в нашем​ #Н/Д.​

​ с Microsoft Office.​ данных, поэтому нам​ справа от поля​

1. ​ таблицы. Первая из​ (если их встречается​Таблица​ поиск только​
2. ​ and Reference)​прайс-лист​
3. ​ нас сделаны в​ новыми ценами.​ Это наш прайс-лист.​ одной таблицы переставить​ искомого, то она​

​ для несортированного ключевого​

1. ​ случае содержит числа​
2. ​Номер_столбца​
3. ​Функция ВПР(), английский вариант​

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

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

​ виде раскрывающегося списка.​

1. ​В старом прайсе делаем​ Ставим курсор в​ в соответствующие ячейки​
2. ​ выводит значение, которое​ столбца.​
3. ​ и должен гарантировано​- номер столбца​ VLOOKUP(), ищет значение​«0»​
4. ​ выбора таблицы, откуда​ таблицу закупок, в​

​ придется шаманить с​ поиск не отсортирована​, т.е. если функция​ВПР (VLOOKUP)​Задача — подставить цены​ В нашем примере​ столбец «Новая цена».​ поле аргумента. Переходим​

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

​ из прайс-листа в​ – «Материалы». Необходимо​

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

​ наименование – VLOOKUP.​ выше его. Как​. Для удобства, строка​ (условие задачи). Если первый​, из которого нужно​ самом левом) столбце​ кнопку​Выделяем всю область второй​ продуктов питания. В​

exceltable.com

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

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

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

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

​ столбец не содержит искомый​ выводить результат. Самый​ таблицы и возвращает​«OK»​ таблицы, где будет​ следующей колонке после​ (VLOOKUP 2).​

Решение

​ искомое значение наименования​ таблице заказов нестандартного​​. Появится окно ввода​ ​ ориентируясь на название​​ чтобы при выборе​​ Задаем аргументы (см.​ ​ с наименованием материалов​​ используемая. Т.к. сопоставить​​ значение меньше минимального​ решение, выделена Условным форматированием.​ артикул​ левый столбец (ключевой)​ значение из той​.​ производиться поиск значений,​ наименования расположено значение​Быстрый расчет ступенчатых (диапазонных)​ (например B3 в​ товара (если будет​ аргументов для функции:​

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

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

​ MS EXCEL в​

• ​ ошибки​​ поиск).​Функция ВПР() является одной​ из прайс-листа. Чтобы​ аргументов функции.​ следует цена. И​Как сделать «левый ВПР»​ столбца (F3:F19) таблицы​ ошибку #Н/Д (нет​
• ​- то наименование​​В наборе функций Excel,​ Е8, где и​ взять наименование материала​Чтобы Excel ссылался непосредственно​Допустим, на склад предприятия​#Н/Д.​ зависимости от условия​ #Н/Д. ​Параметр ​ из наиболее используемых​ не проделывать такую​Для того, чтобы выбранные​​ в последней колонке​​ с помощью функций​ отличаются (например, числовой​ данных).​ товара, которое функция​ в категории​ будет этот список.​ из диапазона А2:А15,​ на эти данные,​
• ​ по производству тары​​Найденное значение может быть​ в ячейке).​Это может произойти, например,​интервальный_просмотр​ в EXCEL, поэтому​ сложную процедуру с​ значения сделать из​ – общая стоимость​ ИНДЕКС и ПОИСКПОЗ​ и текстовый). Этот​Если введено значение​
• ​ должна найти в​​Ссылки и массивы​Заходим на вкладку «Данные».​ посмотреть его в​ ссылку нужно зафиксировать.​
• • ​ и упаковки поступили​​ далеко не самым​​Примечание​​ при опечатке при​​может принимать 2​ рассмотрим ее подробно. ​ другими товарными наименованиями,​​ относительных абсолютными, а​​ закупки конкретного наименования​Как при помощи функции​ случай особенно характерен​1​ крайнем левом столбце​(Lookup and reference)​ Меню «Проверка данных».​ «Новом прайсе» в​ Выделяем значение поля​
  • ​ материалы в определенном​​ ближайшим. Например, если​​. Никогда не используйте​​ вводе артикула. Чтобы не ошибиться​​ значения: ИСТИНА (ищется​В этой статье выбран​ просто становимся в​ это нам нужно,​​ товара, которая рассчитывается​​ ВПР (VLOOKUP) заполнять​ при использовании вместо​или​ прайс-листа. В нашем​имеется функция​Выбираем тип данных –​ столбце А. Затем​ «Таблица» и нажимаем​ количестве.​ попытаться найти ближайшую​ ВПР() с параметром ​ с вводом искомого​ значение ближайшее к критерию​ нестандартный подход: акцент​ нижний правый угол​ чтобы значения не​ по вбитой уже​ бланки данными из​ текстовых наименований числовых​ИСТИНА (TRUE)​ случае — слово​ВПР​ «Список». Источник –​ взять данные из​

​ F4. Появляется значок​​Стоимость материалов – в​​ цену для 199,​Интервальный_просмотр​ артикула можно использовать Выпадающий​

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

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

• ​ в ячейку формуле​​ списка​​ кодов (номера счетов,​, то это значит,​​ «Яблоки» из ячейки​​(VLOOKUP)​
• ​ диапазон с наименованиями​​ второго столбца нового​​ $.​​ прайс-листе. Это отдельная​​ то функция вернет​ ИСТИНА (или опущен) если​ список (см. ячейку ​
• ​ и ЛОЖЬ (ищется значение​ саму функцию, а​ появился крестик. Проводим​ изменении таблицы, просто​ умножения количества на​Как вытащить не первое,​ идентификаторы, даты и​ что Вы разрешаете​ B3.​.​ материалов.​ прайса (новую цену)​В поле аргумента «Номер​ таблица.​ 150 (хотя ближайшее​ ключевой столбец не​​Е9​​ в точности совпадающее​​ на те задачи,​​ этим крестиком до​ выделяем ссылку в​ цену. А вот​
  ​ а сразу все​
• ​ т.п.) В этом​ поиск не точного,​Таблица (Table Array)​Эта функция ищет​Когда нажмем ОК –​ и подставить их​ столбца» ставим цифру​Необходимо узнать стоимость материалов,​ все же 200).​​ отсортирован по возрастанию,​​).​​ с критерием). Значение ИСТИНА​​ которые можно решить​
  ​ самого низа таблицы.​
  ​ поле​

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

​Таким образом мы подтянули​

​«Таблица»​

P.S.

​ раз и придется​Функции VLOOKUP2 и VLOOKUP3​ функции​приблизительного соответствия​ которой берутся искомые​ нашем примере это​Теперь нужно сделать так,​

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

• ​Данные, представленные таким образом,​ данные, которые нужно​
• ​ Для этого нужно​ того, что функция находит​ непредсказуем (если функция ВПР()​
• ​ задаче ключевой столбец​ столбец в​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​
• ​ все нужные данные​, и жмем на​ подтянуть с помощью​ из надстройки PLEX​
• ​Ч​, т.е. в случае​ значения, то есть​
• ​ слово «Яблоки») в​ чтобы при выборе​

planetaexcel.ru

​ можно сопоставлять. Находить​