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

Синтаксис

=ВПР(элемент_который_нужно_найти;адрес_таблицы_где_находится_искомое_значение;порядковый_номер_столбца;интервальный_просмотр)

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

Важно! Искомое значение должно находиться слева (в первом столбце) от возвращаемого элемента.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Подстановка данных из одной таблицы Excel в другую

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

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

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

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

Ошибки

Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.

Ошибка #Н/Д появляется, если:

1. Указанный диапазон не содержит искомый элемент.
2. Искомый элемент меньше, чем минимальный в массиве.
3. Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
4. Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
5. Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
6. В коде есть пробелы или невидимые непечатаемые знаки.
7. Используются значения времени или большие десятичные числа.

Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».

Ошибка #ЗНАЧ появляется, если:

1. В качестве номера столбца указано число 0.
2. Длина первого аргумента превышает 255 знаков.

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

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

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

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

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

45300 01.05.2015

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

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

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

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

Нужно найти и вытащить цену заданного товара (Нектарин

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

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

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

умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP)

для поиска склеенной пары
НектаринЯнварь
из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы

: Простой способ, знакомая функция, работает с любыми данными.

Минусы

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

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

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS)

, появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы

: Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы

: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

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

О том, как спользовать связку функций ИНДЕКС (INDEX)

и
ПОИСКПОЗ (MATCH)
в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

1. Выделите пустую зеленую ячейку, где должен быть результат.
2. Введите в строке формул в нее следующую формулу:
3. Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter
   , чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь

) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы

: Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы

: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

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

• Как искать и подставлять данные с помощью функции ВПР (VLOOKUP)
• Что такое формулы массива и как их использовать
• Как использовать связку функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
• Как извлечь сразу все значения, а не только первое с помощью ВПР



MCH

21.07.2015 00:27:28

А как же СУММПРОИЗВ:

Либо формула массива:

Где вместо МИН можно использовать МАКС или СУММ (если сочетания Товар/Месяц уникальны)

Ссылка

Влад Куликов

23.07.2015 13:32:15

Еще есть вариант), на тот случай, если массив ниже или выше, или просто в рамках какого-то диапазона. Данную формулу мне подсказал человек с форума под ником Евгений =ИНДЕКС($A$1:$D$173;МАКС(ЕСЛИ($A$1:$D$173=G3&I3;СТРОКА($A$1:$D$173)));МАКС(ЕСЛИ($A$1:$D$173=K2;СТОЛБЕЦ($A$1:$D$173)))) Пример по ссылке ниже: Родитель Ссылка

Николай Павлов

22.08.2015 11:35:56

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

будет их суммировать, а не выдавать первое встретившееся значение, т.е. это скорее для суммирования по двум условиям, чем для выборки. Родитель Ссылка

MCH

23.08.2015 09:29:36

Пример с СУММПРОИЗВ() работает аналогично, как и приведенный «Способ 2» с СУММЕСЛИМН() с теми же самыми ограничениями (работает с числами и необходимы уникальные сочетания товар/месяц иначе получим сумму),но есть и преимущество — работает в 2003 Excel. Я привел эти примеры, как дополнительные варианты «выборки». Ну и еще вариант через ПРОСМОТР:

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

Требует массивного ввода и вернет последнее совпадение. Для первого совпадения:

Но на большом количестве данных эта формула будет работать существенно медленнее , чем предыдущие два варианта.

Родитель Ссылка

Елена Рыжая

07.02.2017 17:12:53

=МИН(ЕСЛИ((B2:B161=H3)*(C2:C161=J3);L3)) Откуда появляется переменная «L3» в формуле, если ее надо вычислить?? Родитель Ссылка

ILYA_

24.07.2015 13:09:52

Привет Николай!

Я часто использую 1-й способ. Приходится анализировать информацию в разные дни и за разные, не равномерные промежутки времени. Чтобы иметь объективную информацию, дату и время я преобразую в вот такую штуку 25.03.2015 2:39:48 = 25_3_2015_2_39_48, далее ВПР вытягиваю значения, в пустых ячейках ставлю формулу =НД() и строю график .

Ссылка

Алексей Ст.

25.07.2015 22:02:52

Приветствую Николай! А нет ли в планах у вас создания видеоурока на тему анализа «что если» ? Ссылка

vikttur

01.09.2015 16:34:39

еще один вариант: =СРЗНАЧЕСЛИМН(C:C;A:A;G3;B:B;I3) соответственно при дубляже строк он выдаст их среднюю. Николай такой вопрос, как вы выбираете функцию без мышки и её завершение? в ролике в набрали «=ин» и что то нажали (время 8:55), и сразу появилось «=Индекс(» Ссылка

Василий Алибабаевичъ

23.10.2015 11:11:55

Ролик не видел… но нажат был TAB Родитель Ссылка

Zoynels

18.09.2015 16:52:30

Использую чаще всео слияние всех трёх вариантов, только в качестве ключа использую IndexID=строка(). Затем суммирую по условию колонку IndexID, и уже через ИНДЕКС нахожу нужную колонку. Формула получается:

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

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

Ссылка

talot

25.11.2015 12:01:55

А почему бы для этого не использовать функцию СРЗНАЧЕСЛИМН()? СРЗНАЧЕСЛИМН(C:C;A:A;G3;B:B;I3) и все.

Родитель Ссылка

IgorF

02.12.2015 17:54:05

Николай, для 3-го случая можно сделать формулу, очень похожую на Вашу, но без использования массива: =ИНДЕКС(C:C;ПОИСКПОЗ(G3&I3;ИНДЕКС(A:A&B:B;0);0)) =INDEX(C:C;MATCH(G3&I3;INDEX(A:A&B:B;0);0)) Если в функции индекс в качестве второго параметра ввести 0, то он отрабатывает как массив Ссылка

Николай Павлов

06.12.2015 14:32:57

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

Natalia

22.09.2016 11:13:45

Игорь, скажите, пожалуйста, если в связке столбцов А и В формула не находит связку G3&I3, как сделать так чтобы результат в ячейке был «0»… Пробую вашу формулу, и в этом случае в ячейке выпадает «#Н/Д»…. Заранее спасибо.. Родитель Ссылка

IgorF

22.09.2016 11:16:48

Наталья, воспользуйтесь функцией =ЕСЛИОШИБКА(ИНДЕКС(C:C;ПОИСКПОЗ(G3&I3;ИНДЕКС(A:A&B:B;0);0));0) =IFERROR(INDEX(C:C;MATCH(G3&I3;INDEX(A:A&B:B;0);0));0) Родитель Ссылка

Natalia

22.09.2016 11:37:06

Урааааа!!!! Заработало…. Спасибо огромное! Родитель Ссылка

Трофим Кожемякин

21.05.2018 19:05:28

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

Игорь Яковлев

08.12.2015 12:55:09

А как посчитать сумму к определённой дате. Например есть в 1м столбце даты (всегда разные) а во втором суммы. Нужно посчитать какая сумма выходит на 25 число каждого месяца. Пример: Дата Оплатили Выставили счёт 30.12.13 15000 руб. 02.01.14 12000 руб. 15.01.14 16000 руб 31.01.14 45000 руб 05.02.14 50000 руб 08.02.14 65000 руб 12.02.14 1000 руб 25.02.14 12000 руб 28.02.14 20000 руб 03.03.14 12000 руб 21.03.14 17000 руб 24.05.14 40000 руб 24.05.14 20000 руб Нужно знать долг на 25 число каждого месяца. Помогите пожалуйста. Ссылка

Ульяна Максимова

08.05.2016 00:57:14

Здравствуйте, помогите плиз: необходимо чтобы автоматически выбирался месяц планирования (январь или февраль и т.д.) если есть значение в этом месяце? Спасибо Ссылка

Natalia

22.09.2016 00:08:18

Николай, добрый день! Пыталась применить способ №3, для меня он наиболее подходит, но формулу применить не смогла, выдает ошибку (#ЗНАЧ!)… Создала аналог вашего примера, но формула продолжает выдавать ошибку… Не понимаю в чем проблема:| Ссылка

Aнна

11.01.2017 14:42:21

Наталья, Вы точно проставили фигурные скобки массива после написания формулы? (Ctrl+shift+Enter). Еще проверьте форматы столбцов -условий. У меня все работает. Большое спасибо за формулу) Родитель Ссылка

Кирилл Гибизов

23.01.2017 18:11:04

Спасибо, Николай. Спасибо Игорь. Все заработало отлично. Жить стало легче, жить стало веселее! =) Ссылка

Дмитрий Болтянский

26.02.2017 20:39:30

+1. Просто спасибо. А то я был «уверен, что ВПР (VLOOKUP) — самая сложная функция в Excel, а я ее уже освоил — значит ничему новому меня не научить.» Ссылка

вольк

03.04.2017 19:45:31

а если столбы находяться не рядом , получмиться их склеить ? 1

Ссылка
вольк

03.04.2017 19:59:33

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

Pavel Minaev

07.06.2017 13:40:38

Подскажите, пожалуйста. !!! Никак не могу победить подсчет формулы с ИНДЕКС и ПОИСПОЗ… по первому параметру. Задача такая.. Есть ряд параметров — по ним в массиве данных нужно найти соответствие по 2-м или нескольким совпадениям. Причем первый параметр повторяющийся расположен в колонке, второй меняется и расположен в строке. ПОИСКПОЗ — находит первое совпадение по первому параметру, а второе совпадение игнорирует… Ссылка

Али

22.06.2017 02:18:46

Добрый день. У меня несколько иная задача. Имеется страница «Реализация» в которую при заполнении товара и даты продажи должна подставляться рекомендуемая цена со страницы «Цены». При этом на странице «Цены» один и тот-же товар может встречаться несколько раз (в том случае если он оприходовался более одного раза и цена закупки была разной). Так-же на странице «Цены» у каждого товара указывается дата установки цен, которая совпадает с датой оприходования. Мне нужно что-бы подставлялась последняя цена. Вариант при котором в разделе «Цены» цена закупки будет динамически меняться при поступлении новой партии товара меня не устраивает, т.к. при этом данные по прибыли за ранее проданный товар будут постоянно меняться, что будет ошибкой. Алгоритм, как должна выглядеть подстановка цены мне в общем-то понятен, но как должна выглядеть формула я сообразить не могу. Алгоритм (как мне кажется) должен быть следующий: Берём дату реализации и ищем ближайшую (раннюю) к ней дату установки цены со страницы «Цены» для выбранного товара, и вытаскиваем цену. Я конечно понимаю что для этой цели нужен 1С, но товарищ (который попросил меня написать данную таблицу) не может забивать свои товары в общую базу. Помогите пожалуйста. Данные по ценам нужно брать из столбцов I, J, K (страница «Цены»;) и подставлять в столбцы J, K, L (страница «Реализация). Пример по ссылке: Пример Ссылка

Маракуйя

23.08.2017 16:01:55

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

Маракуйя

23.08.2017 14:11:18

Всем привет! Подскажите, пожалуйста, как можно показать в одной строке в каких месяцах продавался, например, картофель? Какую формулу следует использовать? Возможно ли это вообще осуществить стандартными формулами без допмакросов? В моем случае требуется по одному артикулу обуви отобразить В ОДНОЙ СТРОЧКЕ все размеры, какие есть в наличии (столбец артикул обуви, столбец размер обуви). Ссылка

Владимир Бутырирн

01.06.2018 11:53:00

Формула с массивом помогла, первые два варианта более сложные задачи, чем в примерах, не решают. Спасибо! Ссылка

Ирина Ирина

24.08.2018 14:26:12

Добрый день! Помогите, пжл, я видимо на каком то этапе не верно делаю. Есть список привязки номер телефона — должность, также есть таблица привязка должность -тариф , так мне нужно увязать телефон-должность-тариф. индекс _поискпоз делаю не верное знач. Помогите, пжл,.. спасибо Ссылка

sp a

27.11.2018 16:08:39

Добрый день! подскажите, возможно ли использование формулы на 3м примере при поиске в разных книгах? Ссылка

Александр З

26.09.2019 09:04:55

помогите как это в экселе прописать В1=С1 В2=С2 если В3< В1 то В3=0 если В3 если В3 ≥В2 то В3=С2 Ссылка

Дарья Гайдукова

28.05.2020 14:22:10

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

Anna Matushkina

01.10.2020 13:07:59

А если условия — это даты и надо вынести название, если оно попало внутрь условия дипазона между 2 датами? Ссылка