Excel формула vlookup: инструкция на примере / Skillbox Media

Функция VLOOKUP | Справочные статьи Smartsheet

Ищет значение и возвращает соответствующее значение в той же строке, но из другого столбца.

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

VLOOKUP(«Задача E»; [Название задачи]1:Done5; 2; ложь)

Синтаксис

VLOOKUP(

• search_value

• lookup_table

• column_num

• [

  match_type

  ]

)

• search_value

  —

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

• lookup_table

  —

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

• column_num

  —

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

• match_type

  —(необязательно)

  По умолчанию принимает значение «Истина». Указывает, какое совпадение искать: точное («Ложь») или приблизительное («Истина»).

Примечания об использовании

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

• С помощью функции VLOOKUP можно искать значения из таблицы в другой таблице. Сведения о том, как ссылаться на ячейки в других таблицах, см. в статье Формулы: ссылки на данные из других таблиц.
• Если поиск с помощью функции VLOOKUP не даст результата, появится сообщение об ошибке #NO MATCH. Сведения о том, как её устранить, см. в статье Сообщения об ошибках формул.
• Ошибка #NO MATCH появляется, если в диапазоне нет числа, которое было бы больше значения search_value или равно ему.
• Если массив table_array не отсортирован по возрастанию значений в первом столбце, функция VLOOKUP возвращает неправильные результаты.
• Значение search_value должно находиться в крайнем левом столбце (положение 1) таблицы lookup_table.
• Для поиска строк текста необходимо заключить искомое значение в кавычки (например, «Задача E»).
• С использованием аргумента match_type:
  • Если таблица lookup_table не отсортирована, задайте для аргумента match_type значение «Ложь».
  • Если задано значение «Истина» (значение по умолчанию), предполагается, что диапазон отсортирован по возрастанию, и возвращается ближайшее совпадение, которое меньше значения search_value или равно ему (<=).
  • Если задано значение «Ложь», возвращается первое точное совпадение.
• Чтобы указать столбец, из которого извлекается значение, можно вставить в формулу номер столбца.
   
  Например, формула =VLOOKUP([Складская позиция]3, [Складская позиция]1:[Кол-во позиций в наличии]4, 3, ложь) вернёт значение 4. Формула извлекает значение из третьего столбца («Кол-во позиций в наличии») в таблице ниже.

Примеры

В примере ниже используется информация из этой таблицы.

Вот несколько примеров использования функции VLOOKUP применительно к таблице выше.

Остались вопросы?

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

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

Обратиться к Сообществу

Функция ВПР (VLOOKUP) в Excel для чайников

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

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

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

Во втором — цены:

Если список товаров в обеих таблицах один и тот же, то зная волшебное сочетание Ctrl+C и Ctrl+V, данные о цене можно легко подставить на данные о количестве. Однако порядок позиций в обеих таблицах не совпадает. Тупо копировать цены и подставлять количество не получится.

Поэтому мы не можем прописать формулу умножения и «растянуть» на все позиции.

Сделать? Нужно как-то заменить цены во второй таблице соответствующим количеством в первой, то есть цену товара А на количество товара А, цену товара В на количество В и т.д.

Мне это нравится.

Функция ВПР в Excel легко справляется с поставленной задачей.

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

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

Щелкаем по надписи «ВПР». Откроется следующий диалог.

Теперь нужно заполнить предложенные поля. В первом поле «Искомое_значение» нужно указать критерий для ячейки, в которую мы вводим формулу. В нашем случае это ячейка с наименованием товара «А».

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

Следующее поле «Колонка_номер» — это номер, которым отделяется колонка с требуемыми данными (ценами) от колонки с критериями (название товара) включительно. То есть отсчет начинается со столбца с самим критерием. Если в нашей второй таблице оба столбца стоят рядом, то нужно указать цифру 2 (первый — критерии, второй — цены). Часто бывает, что данные отстают от критериев на 10 или 20 столбцов. Не беда, Excel все рассчитает.

Последнее поле — «Interval_lookup», в котором указывается тип поиска: точное (0) или приблизительное (1) соответствие критериям. Установите его на 0 (или FALSE) на данный момент. Второй вариант рассмотрен ниже.

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

Теперь легко рассчитать стоимость, просто умножив количество на цену.

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

Особенности использования формулы ВПР в Excel

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

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

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

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

4. Необходимо настроить последний параметр формулы, который равен 0 (ноль). В противном случае формула может работать криво.

5. После использования ВПР лучше сразу удалить формулу, оставив только возвращаемые значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и вставляем значения на то же место с помощью специальной пасты. Если таблицы расположены в разных книгах Excel, то очень удобно разбивать внешние ссылки (оставляя на своих местах только значения) с помощью специальной команды, которая находится в пути Data Change Links.

После вызова функции разрыва внешних ссылок появится диалоговое окно, в котором необходимо нажать кнопку «Разорвать ссылку», а затем «Закрыть».

Это удалит все внешние ссылки сразу.  

Примеры функции ВПР в Excel

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

Нужно поднять цены второго стола до первого. Код используется здесь как критерий. Шаги для расчета ВПР показаны ниже.

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

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

Конструкция с функцией ЕСЛИОШИБКА

Наряду с функцией ВПР часто используется функция ЕСЛИОШИБКА, которая «замалчивает» ошибки #Н/Д и вместо этого возвращает некоторое значение. Обычно это 0 или пусто. 

Как видите, ошибок больше нет, а вместо них пустые ячейки.

Разные форматы критерия в таблицах

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

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

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

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

Автоматически изменить формат критерия во второй таблице нельзя, потому что ссылка ведет на полный диапазон. Придется мешать ссылку на критерий в первой таблице. Для этого нужно добавить функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ требует указания формата. Просто установите формат #. Ниже изображение с готовой формулой.

Есть еще две ошибки, связанные с тем, что этих товаров нет во второй таблице. Чтобы заставить их замолчать, вы снова можете использовать функцию ЕСЛИОШИБКА.

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

Как и в прошлый раз, внесем коррективы в функцию ВПР. Преобразование «текста» в «число» еще проще. Просто добавьте 0 к ссылке на критерий «текст» или умножьте на 1.

Существует и третья смешанная ситуация. Это гораздо реже. Это когда в первой и второй таблицах критерии хранятся как число и как текст, смешанные вместе. Здесь вам нужно будет использовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Сначала мы пишем ЕСЛИОШИБКА и в качестве первого аргумента этой функции мы пишем ВПР с некоторой конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (то есть того, что должно быть при ошибке) пишем вторую ВПР с +0. Итак, если ВПР с функцией ТЕКСТ не выдает ошибки, то все в порядке. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА заменяет вторую конструкцию ВПР на +0. Другими словами, сначала мы делаем все критерии текстовыми, а затем числовыми. Таким образом, ВПР проверяет оба формата. Один из них будет соответствовать формату второй таблицы. Это немного громоздко, но в целом это работает.

Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В этом случае всю формулу можно «обернуть» обратно в ЕСЛИОШИБКА.

Функция СЖПРОБЕЛЫ для чистки текстового критерия

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

Подсчет номера столбца в большой таблице

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

Отличная экономия времени.

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

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

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

Легче понять на примере. По результатам выполнения плана продаж каждый торговый агент должен получить заслуженную премию (в процентах от оклада). При выполнении плана менее чем на 100% премия не начисляется, при выполнении плана от 100% до 110% (не включая 110%) — премия 20%, от 110% до 120% (не включая 120%). — 40%, 120% и более — 60% надбавка. Данные имеют следующий вид.

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

=ВПР(B2,$E$2:$F$5,2,1)

и скопируйте вниз.

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

Джеки Чан выполнил план на 124%. Это означает, что ВПР в качестве критерия ищет ближайшее наименьшее значение во второй таблице. Это 120%. Затем он считает 2 столбца и возвращает 60% надбавки. Брюс Ли не выполнил план, поэтому его следующий самый низкий критерий — 0%.

Предлагаю посмотреть видеоурок по работе ВПР из курса «Основные функции Excel».

Функция ВПР — служба поддержки Майкрософт

Excel

Формулы и функции

Ссылка

Ссылка

Функция ВПР

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 Дополнительно. .. Меньше

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

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

В простейшей форме функция ВПР говорит:

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

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

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

Синтаксис 

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

Например:

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

• =ВПР(«Фонтана»,B2:E7,2,ЛОЖЬ)

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

С чего начать

Для построения синтаксиса ВПР вам понадобятся четыре элемента информации:

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

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

   .

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

4. Дополнительно можно указать TRUE, если требуется приблизительное соответствие, или FALSE, если требуется точное соответствие возвращаемого значения. Если вы ничего не укажете, значением по умолчанию всегда будет TRUE или приблизительное совпадение.

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

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

Примеры

Вот несколько примеров ВПР:

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

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

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

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

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

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

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

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

   В нашем примере столбец G использует Attorney ( lookup_value ) для получения данных о ставке счета из четвертого столбца ( col_index_num = 4) из таблицы рабочего листа Attorneys, tblAttorneys ( table_array ), с формулой =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE) .

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

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

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

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

См. также

Функция XLOOKUP

Видео: когда и как использовать функцию ВПР

Краткая справочная карта: освежение VLOOKUP

Как исправить ошибку #Н/Д в функции ВПР

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

Функция ГПР

Как исправить #ЗНАЧ! ошибка в функции ВПР

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Больше. ..Меньше

Если вы работаете с функцией ВПР, есть большая вероятность, что вы столкнулись с ошибкой #ЗНАЧ! ошибка несколько раз. В этом разделе перечислены наиболее распространенные проблемы, которые могут возникнуть при использовании функции ВПР, и возможные решения.

Проблема: аргумент

lookup_value содержит более 255 символов.

Решение : Сократите значение или используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ в качестве временного решения.

Это формула массива. Поэтому либо нажмите ENTER (только если у вас Microsoft 365), либо CTRL+SHIFT+ENTER .

Примечание. Если у вас текущая версия Microsoft 365, вы можете просто ввести формулу в ячейку вывода, а затем нажать ENTER , чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходную ячейку, введя формулу в выходную ячейку, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массива см. в разделе Рекомендации и примеры формул массива.

Проблема: Аргумент

col_index_num содержит текст или меньше 0.

Индекс столбца — это количество столбцов справа от столбца поиска, которое вы хотите вернуть, если найдено совпадение.

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

Минимальное значение для аргумента col_index_num равно 1, где 1 — это столбец поиска, 2 — это первый столбец справа от столбца поиска и так далее. Так что, если вы ищете в столбце A, то 1 указывает на него, 2 — это столбец B, 3 — столбец C и так далее.