Vlookup excel формула: Краткий справочник: функция ВПР — Служба поддержки Майкрософт

Содержание

Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

Василий

16:24:00

ВПР

,

формула

,

функция

,

Эксель

,

Excel

,

Excel для новичков

,

Excel для чайников

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

Рассмотрим такой учебный пример. У нас есть база данных с фамилиями, именами, отчествами сотрудников компании, а также годами их рождения. Нам необходимо по части сотрудников сделать сводку, где указать их фамилии и года рождения. Структурно лист с базой данных выглядит вот так:

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

СМЕЩ(ссылка;смещение по строкам;смещение по столбцам)

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

Смещение по строкам — указываем число, на какое количество строк надо сдвинуться. Положительное число — количество строк вниз, отрицательное число — количество строк вверх.

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

К примеру для таблицы в скриншоте сверху функция СМЕЩ(C3;-1;1) вернет значение Петрович. Почему именно так? В качестве стартовой ячейки мы указали ячейку С3 (в ней содержится имя Иван). Относительно этой ячейки мы сдвигаемся на одну строку вверх (второй параметр -1) и на один столбец вправо (третий параметр 1).

ПОИСКПОЗ(искомое значение;  просматриваемый массив; тип сопоставления)

Искомое значение — это то значение, что мы ищем в таблице

Просматриваемый массив — таблица, где мы ищем искомое значение

Тип сопоставления — как будет искать Excel это значение. Используйте значение 0 для поиска точного результата.

К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ(«Петров»;B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.

Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.

Итак, для нашего примера формула будет следующая:

=СМЕЩ(БД!$B$1;ПОИСКПОЗ(B2;БД!$B$1:$B$6;0)-1;-1)

Сначала мы используем формулу СМЕЩ, так как мы будет считать смещение относительно конкретной ячейки. Первый параметр — ссылка на заголовок столбца «Фамилия» на листе с базой данных, далее нам нужно узнать на сколько строк нам надо сдвинуться, для этого мы используем функцию ПОИСКПОЗ. В ней в качестве первого параметра указываем фамилию, которую ищем, вторым параметром наш столбец с фамилиями в листе с базой данных. Из полученного значения мы вычитаем 1, что бы убрать эффект заголовка столбца. Последний параметр — смещение на один столбец влево, т.е. -1.

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

Надеюсь, статья была для вас полезной. Спасибо за внимание.

[Решено:] функция ВПР в excel не работает при протягивании формулы что делать?

  • О сайте
  • Контакты

войти в систему

Добро пожаловат!Войдите в свой аккаунт

Ваше имя пользователя

Ваш пароль

Вы забыли свой пароль?

завести аккаунт

Зарегистрироваться

Добро пожаловат!Зарегистрируйтесь для создания учетной записи

Ваш адрес электронной почты

Ваше имя пользователя

Пароль будет выслан Вам по электронной почте.

восстановление пароля

Восстановите свой пароль

Ваш адрес электронной почты

Изменена:

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

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

История:

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

Я нашел способ как эти прайсы можно легко загружать на сайт под управление Opencart

Да

68.75%

Нет

31.25%

Проголосовало: 16

Вступление закончено) Так вот для работы с такими прайсами мне мой программист по Excel посоветовал воспользоваться функций ВПР и тут моя жизнь разделилась на ДО и ПОСЛЕ. Хоть об этой функции есть целая книга я в данной заметке должен лишь рассказать вам как я столкнулся с ошибкой в ВПР и на которою потратил много времени чтобы разобраться почему она у меня не работает.

1 Функция ВПР Excel не работает

1.1 Решение проблемы с неработающей ВПР

2 видео инструкция по ВПР

Функция ВПР Excel не работает

Давайте для начала перейду к описанию что именно не работала в ВПР

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

Решение проблемы с неработающей ВПР

чтобы понять в чем проблема я чуть не снес сам Microsoft Office т.к. не понимал в чем проблема и потратил на это почти час, но потом начал отматывать назад и вспомнил, что до этого делал так, чтобы у меня в excel при протягивании не увеличивалось число в ячейки.

Как вы поняли я сейчас уже описываю решение проблемы с ВПР:

  1. открываем в excel меню Формулы
  2. переходим в Параметры вычислений
  3. ставим галочку на Автоматически

После этого наша формула в ВПР сразу заработала и на выходе показало, то что и должна была а именно:

Да

72. 06%

Нет

27.94%

Проголосовало: 68

Если Вы хотите узнать почему в ВПР появляются ошибки:

  • Ошибки #ЗНАЧ в функции ВПР
  • Ошибки #Н/Д в ВПР

в след статье я опишу как это исправить.

видео инструкция по ВПР

Popular

Как использовать функцию ВПР (WS)

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

Описание

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

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

Подписаться

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

Пример загрузки

Синтаксис

Синтаксис функции ВПР в Microsoft Excel:

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

Параметры или аргументы

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

Возвращает

Функция ВПР возвращает любой тип данных, такой как строка, число, дата и т. д.
Если вы укажете ЛОЖЬ для параметра приблизительное_соответствие и точное совпадение не будет найдено, то функция ВПР вернет #Н/Д .
Если вы укажете TRUE для приблизительного_соответствия и точное совпадение не найдено, возвращается следующее меньшее значение.
Если index_number меньше 1, функция ВПР вернет #ЗНАЧ!.
Если номер_индекса больше, чем количество столбцов в таблице , функция ВПР вернет #ССЫЛКА!.

Пример (как функция рабочего листа)

Давайте рассмотрим, как использовать ВПР в качестве функции рабочего листа в Microsoft Excel.

На основе приведенной выше электронной таблицы Excel будут возвращены следующие примеры ВПР:

 =ВПР(10251, A1:B6, 2, ЛОЖЬ)
  Результат:  "Груши" 'Возвращает значение во 2-м столбце
=ВПР(10251, A1:C6, 3, ЛОЖЬ)
  Результат:  $18,60 'Возвращает значение в 3-м столбце
=ВПР(10251, A1:D6, 4, ЛОЖЬ)
  Результат:  9 'Возвращает значение в 4-м столбце
=ВПР(10248, A1:B6, 2, ЛОЖЬ)
  Результат:  #N/A 'Возвращает ошибку #N/A (нет точного совпадения)
=ВПР(10248, A1:B6, 2, ИСТИНА)
  Результат:  "Яблоки" 'Возвращает приблизительное совпадение

Теперь давайте рассмотрим пример =VLOOKUP(10251, A1:B6, 2, FALSE) , который возвращает значение «Груши», и подробнее рассмотрим, почему.

Первый параметр

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

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

 =ВПР("10251", A1:B6, 2, ЛОЖЬ)

Второй параметр

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

В этом примере вторым параметром является A1:B6, который дает нам два столбца данных для использования в вертикальном поиске — A1:A6 и B1:B6. Первый столбец в диапазоне (A1:A6) используется для поиска значения Order, равного 10251. Второй столбец в диапазоне (B1:B6) содержит возвращаемое значение, которое является значением Product.

Третий параметр

Третий параметр — это номер позиции в таблице , где можно найти возвращаемые данные. Значение 1 указывает на первый столбец в таблице. Второй столбец равен 2 и так далее.

В этом примере третий параметр равен 2. Это означает, что во втором столбце таблицы мы найдем возвращаемое значение. Поскольку для диапазона таблицы задано значение A1:B6, возвращаемое значение будет находиться во втором столбце где-то в диапазоне B1:B6.

Четвертый параметр

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

В этом примере четвертый параметр равен FALSE. Параметр FALSE означает, что функция VLOOKUP ищет ТОЧНОЕ совпадение для значения 10251. Параметр TRUE означает, что будет возвращено «близкое» совпадение. Поскольку функция ВПР может найти значение 10251 в диапазоне A1:A6, она возвращает соответствующее значение из диапазона B1:B6, то есть груши.

Точное совпадение и приблизительное совпадение

Чтобы найти точное совпадение, используйте FALSE в качестве конечного параметра. Чтобы найти приблизительное совпадение, используйте TRUE в качестве конечного параметра.

Давайте найдем значение, которого нет в наших данных, чтобы продемонстрировать важность этого параметра!

Точное совпадение

Используйте ЛОЖЬ, чтобы найти точное совпадение:

 =ВПР(10248, A1:B6, 2, ЛОЖЬ)
  Результат:  #Н/Д

Если точное совпадение не найдено, возвращается #Н/Д.

Приблизительное совпадение

Используйте ИСТИНА, чтобы найти приблизительное совпадение:

 =ВПР(10248, A1:B6, 2, ИСТИНА)
  Результат:  "Яблоки"

Если совпадений не найдено, возвращается следующее меньшее значение, в данном случае это «Яблоки».

ВПР с другого листа

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

Мы могли бы переписать исходный пример, в котором мы ищем значение 10251, следующим образом:

 =ВПР(10251, Лист2!A1:B6, 2, ЛОЖЬ)

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

ВПР с другого листа с пробелами в имени листа

Добавим еще одно усложнение. Что произойдет, если имя вашего листа содержит пробелы? Если в имени листа есть пробелы, вам нужно будет изменить формулу дальше.

Предположим, что таблица находится на листе под названием «Тестовый лист» в диапазоне A1:B6, теперь нам нужно заключить имя листа в одинарные кавычки следующим образом:

 =ВПР(10251, 'Тестовый лист'!A1 :B6, 2, FALSE)

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

ВПР из другой книги

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

 =VLOOKUP(10251, 'C:\[data. xlsx]Sheet1'!$A$1:$B$6, 2, FALSE)

Это будет искать значение 10251 в файле C:\data.xlxs в Лист 1, где данные таблицы находятся в диапазоне $A$1:$B$6.

Зачем использовать абсолютную привязку?

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

Итак, если бы у вас была следующая формула в ячейке G1:

 =ВПР(10251, A1:B6, 2, ЛОЖЬ)

А затем вы скопировали эту формулу из ячейки G1 в ячейку h3, она изменила бы формулу ВПР на это:

 =VLOOKUP(10251, B2:C7, 2, FALSE)

Поскольку ваша таблица находится в диапазоне A1:B6, а не B2:C7, ваша формула вернет ошибочные результаты в ячейке h3. Чтобы убедиться, что ваш диапазон не изменился, попробуйте сослаться на диапазон вашей таблицы, используя абсолютную ссылку следующим образом:

 = ВПР(10251, $A$1:$B$6, 2, ЛОЖЬ)

Теперь, если вы скопируете эту формулу в другую ячейку, диапазон вашей таблицы останется $A$1:$B$6.

Как обрабатывать ошибки #Н/Д

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

Например, если у вас есть следующая формула:

 =ВПР(10248, $A$1:$B$6, 2, ЛОЖЬ)

Вместо отображения ошибки #Н/Д, если совпадение не найдено, можно вернуть значение «Не найдено». Для этого можно изменить формулу ВПР следующим образом:

 =ЕСЛИ(ИСНА(ВПР(10248, $A$1:$B$6, 2, ЛОЖЬ)), "Не найдено", ВПР(10248, $A$1 :$B$6, 2, ЛОЖЬ))

ИЛИ

 =ЕСЛИОШИБКА(ВПР(10248, $A$1:$B$6, 2, ЛОЖЬ), "Не найдено")

ИЛИ

 =ЕСЛИНА(ВПР( 10248, $A$1:$B$6, 2, ЛОЖЬ), "Не найдено")

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

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

Часто задаваемые вопросы

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

Часто задаваемые вопросы

Как использовать функцию ВПР в Excel для чайников (учебник 2023 г.)

Перейти к содержимому Как использовать функцию ВПР в Excel для чайников (учебник 2023 г.)

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

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

Это позволяет искать данные в одной электронной таблице и переносить их в другую таблицу за несколько секунд⏱️

И это делает ВПР одной из самых экономящих время функций в Excel.

Позвольте мне показать вам, как использовать функцию ВПР за 4 простых шага.

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

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

Справа у вас есть небольшой список идентификаторов заказов, которые вам прислал ваш босс, но без каких-либо цен.

Она хочет, чтобы вы узнали цену этих конкретных заказов.

И они нужны ей через 5 минут. Вам лучше поторопиться…

ВПР спешит на помощь 🚑

Вам нужно найти конкретные идентификаторы ордеров в столбце А и вернуть соответствующую цену из столбца С.

Итак, как использовать ВПР? Я покажу вам, шаг за шагом.

Шаг 1: Искомое значение

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

=ВПР(

Теперь появится всплывающая подсказка, показывающая синтаксис ВПР. Функция Excel должна возвращать требуемый результат. Каждый ввод называется «аргументом».0002 Первый аргумент lookup_value .

Искомое значение — это то, что вы ищете.

✔️Нажмите или введите ссылку на ячейку с искомым значением.

Вы ищете конкретный идентификатор заказа. Щелкните идентификатор заказа (из списка, который вам прислал начальник), чтобы сделать ссылку на него.

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

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

=VLOOKUP(E3,

Пока довольно просто, не так ли? 😊

Давайте двигаться дальше.

Шаг 2. Массив таблиц

Теперь Excel попросит вас записать второй аргумент в функцию VLOOKUP: table_array .

Здесь вы ищете искомое значение (из шага 1, помните?)

Это немного сложнее, но не волнуйтесь, через минуту все закончится диапазон (группа ячеек)

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

✔️Выберите или введите диапазон – как любую другую группу ячеек.

Массив таблиц должен содержать столбцы A (столбец поиска), B и C.

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

Поставьте запятую, чтобы завершить шаг 2.

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

=ВПР(E3, A:C,

ВПР ищет слева направо!
ВПР ищет искомое значение в крайнем левом столбце массив таблицы. Это означает, что все, что вы хотите вернуть, должно располагаться где-то справа от столбца, в котором вы ищете.
Если это не так, вам нужно либо изменить порядок столбцов, либо вместо этого использовать ПОИСКПОЗ ИНДЕКС.

Каспер Лангманн , Специалист по Microsoft Office

Трудная часть позади👍

Теперь вам нужно решить, из какого столбца вы хотите что-то вернуть.

Шаг 3: Индексный номер столбца

Третьим аргументом синтаксиса ВПР является номер_индекса_столбца .

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

Если мы уменьшим масштаб, в первую очередь это причина, по которой вы используете функцию ВПР! Так скажи мне, почему так?

«Потому что я хочу вернуть цену идентификатора заказа, который я ищу».

Хорошо! Сосредоточьтесь на первой части этого предложения.

«Потому что»

Нет, не то…😑

«Хочу вернуть цену»

БИНГО!

✔️Напишите номер столбца, из которого вы хотите что-то вернуть.

Цена находится в столбце C. А столбец C является третьим столбцом в table_array. Итак, напишите 3.

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

Теперь ваша функция ВПР должна выглядеть так:

=ВПР(E3,A:C,3,

Индекс столбца следует за массивом таблицы. Если массив таблицы состоит из столбцов B, C и D, первый столбец (индекс столбца = 1) будет B, а третий столбец (индекс столбца = 3) будет D.

Шаг 4: точное или приблизительное соответствие

Четвертый аргумент функции ВПР — это range_lookup , который определяет «режим» поиска.

Большую часть времени вам придется использовать «режим точного совпадения». К сожалению, это не значение по умолчанию, поэтому вам нужно сообщить об этом Excel с помощью аргумента поиска диапазона.

Точное совпадение означает, что функция ВПР будет искать исключительно точные значения. Только значения, которые на 100% идентичны искомому значению (из шага 1), считаются точным совпадением.

✔️ Напишите слово: ЛОЖЬ, чтобы использовать точное соответствие.

Теперь завершите формулу концевой скобкой, и все готово🏆

Ваша формула должна выглядеть так:

=ВПР(E3,A:C,3,ЛОЖЬ)

Нажмите Ввод!

Приблизительное совпадение

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

Чтобы использовать «режим приблизительного совпадения»:

  • Напишите TRUE в аргументе поиска диапазона
  • Отсортируйте крайний левый столбец массива таблиц от низшего к высшему

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

Вот и все – Что теперь?

Отлично, вы научились использовать функцию ВПР — самую популярную расширенную функцию Excel всех времен.

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

В конце концов, это было не так уж и страшно, правда?

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

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

Читайте также: