Функция ВПР в Excel

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

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

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

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

Для решения задачи нам необходимо подтянуть цены на товары из таблицы "Прайс-лист" в столбец "Цена за кг" таблицы "Заказы". Т.е. подставить цены, которые соответствуют каждому товару из нижней таблицы в верхнюю.

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

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

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

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

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

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

Последний параметр называется "Интервальный просмотр" - это логический аргумент и может принимать только 2 значения: 0 или 1 (включено/выключено). Данным значением мы определяем, точно, цифра 0, или приблизительно, цифра 1, мы ищем наименование в прайс-листе. В случае с текстовыми наименованиями лучше использовать точный поиск (цифра 0), потому что приблизительный более менее точно работает в случае ячеек с числами. Вводим интервальный просмотр как 0 и закрываем скобку. После этого нажимаем клавишу Enter, чтобы формула сработала. В итоге мы получим ячейку с ценой, подтянутой из таблицы прайс-листа.

В колонку "Итоговая стоимость" прописываем простую формулу умножения веса партии на цену за кг.

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

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

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

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

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

Вернемся к нашей таблице с заказами и попробуем определить размер партии по ее весу. У нас будет таблица с заказами и таблица с параметрами партии.

Дополнительно у нас появляется критерий "от и до", который говорит о размере партии. В примере мы будет отталкиваться от ее веса. Например, если Excel показать вес партии 15 кг, то он будет понимать, что это мелкая партия.

Давайте разбираться как это работает. Начинаем писать функция ВПР, но в конце формулы ставим не 0, а 1. В аргументе, где нужно указать таблицу с ячейками, мы не будем указывать столбец с параметром "От и до", так как Excel такую запись не поймет. Взамен этого мы вводим поле критерий, который и будет определять начало и конец диапазона весов. В конце нажимаем клавишу Enter и протягиваем на все ячейки.

Есть некоторые особенности работы, которые необходимо пояснить, чтобы было понятно, что здесь произошло. Мы видим, что при определенном весе функция вытащила из второй таблицы определенную партию. Чтобы более точно понять логику, давайте посмотрим на вес, к примеру, в 15 кг. В таблице с размерами партий, такого критерия нет. Но когда мы ставим интервальный просмотр в значение 1, функция будет вытаскивать значение, которое является ближайшим меньшим. При весе партии в 15 кг, ВПР вытащит из таблицы значение с критерием 10. Если вес партии будет 47 кг, то функция вытащит значение с критерием 30, которое будет ближайшим и меньшим.

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

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

Лучшая инвестиционная игра в Рунете, Регистрируйся !

Оставайтесь в курсе обновлений сайта, обязательно подписывайтесь на канал Youtube и группу ВКонтакте .

Ошибка в тексте? Выделите фрагмент и нажмите Ctrl + Enter

Функции ВПР2 ГПР2 в Excel

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

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

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

Синтаксис: =ВПР2 (Ключ; Таблица; Столбец_поиска; [Столбец_Ключа]), где:

• Ключ – обязательный аргумент. Значение либо ссылка на ячейку, содержащую значение для поиска. Когда необходимо определить несколько ключей, они должны быть записаны через символ амперсанда – &;
• Таблица – обязательный аргумент. Диапазон, содержащий искомые значения и ключи;
• Столбец_поиска – обязательный аргумент. Целое число, указывающее на порядковый номер столбца в таблице, из которого необходимо возвратить значение;
• Столбец_Ключа – необязательный аргумент. Значение по умолчанию 1. Целое число, указывающее на порядковый номер столбца в таблице, в котором находится значение ключа. В случаях использования нескольких ключей, номера их столбцов записываются в виде массива (внутри фигурных скобок) через точку с запятой – <1; 3; 7>. Имейте в виду, что порядок указания столбцов должен совпадать с порядком задания ключей.

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

В следующей примере осуществляется поиск значений для составного ключа. Если значение не найдено, то возвращается ошибка #Н/Д.

Для того, чтобы использовать описанные в данной категории функции, скачайте и установите нашу надстройку.
Работа надстройки была успешно протестирована на версиях Excel: 2007, 2010 и 2013. В случае возникновения проблем с ее использованием, сообщайте Администрации сайта .

Добрый день!
Спасибо за ответ.
Возможно мы не поняли друг друга.
Посм.влож.файл.
В D8 при вычислении ВПР2 возникает #Н/Д
Стандартная же ф-ция ВПР в аналогичном случае (яч.D7) возвращает 0
Возможно Вы так задумывали это реализовать.
Но на мой взгляд с точки зрения совместимости со стд.функцией – логично было бы возвращать 0 (ноль)

Спасибо за интересные и нужные дополнения функций Excel.)

С уважением,
Паршин Роман

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

Функция впр в excel

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

Сегодня мы рассмотрим:

Данная функция имеет четыре параметра:

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

• «ГДЕ» — ссылка на диапазон ячеек (массив двумерный), в первом столбце которого и будет происходить поиск значения параметра «ЧТО».

• «НОМЕР СТОЛБЦА» — номер столбца в диапазоне, из которого будет возвращено значение;

• «ОТСОРТИРОВАНО» — весьма важный параметр, так как от правильности выбранного условия: «1-ИСТИНА» — «2-ЛОЖЬ», будет зависеть конечный результат работы примененной функции ВПР (осуществляться выборка данных относительно вопроса: отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>). Стоит отметить, что в случае, если вы проигнорируете процесс установки нужного значения, параметр автоматически примет условие «1-ИСТИНА».

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

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

• Становимся на ячейку «D6».

• Вызываем служебное окно консоли «fx», нажатием соответствующей клавиши, и в заданном окне мастера функций активируем чек бокс «Категории».

• Выбираем пункт «Ссылки и массивы».

• В боксе выбора функции устанавливаем значение «ВПР».

• Нажимаем кнопку «ОК» и переходим к следующему шагу — вводу аргументов этой функции.

• Используя левую кнопку мышки, сделайте клик по первой ячейки вашего списка наименований, в нашем примере этому действию назначается активация ячейки «B6». Итак, пункту «Искомое значение» соответствует значение «B6».

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

• Теперь требуется превратить ссылку на таблицу, так сказать, в абсолютную — выделяем аргумент из примера «G6:I10» и жмем клавишу «F4».

• В итоге мы видим, что прежняя ссылка изменилась: исходные символы стали окружены долларовыми знаками «$G$6:$I$10», чего и требовалось достигнуть.

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

• Ну и наконец, четвертый параметр, который нам необходимо указать — это «нуль», в графе «Интервальный просмотр». Так как значение «1» соответствует числовым параметрам данных, в нашем же случае используется поиск искомого объекта, так сказать, в текстовом виде, поэтому наш выбор очевиден — «нуль».

Что ж, итогом наших манипуляций стало появившееся значение в столбце «Цена», первой таблицы «Проданный товар» — число «10», что соответствует указанному значению из второй таблицы.

Теперь осталось одно — ввести формулу умножения цены на количество и наслаждаться конечным результатом.

• В ячейке «E6» ставим знак равенства.

• Перемещаем маркер на позицию «С6».

• Далее нажимаем знак умножения.

• Переходим на ячейку «D6» и жмем клавишу «Enter».

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

На этом, все — точных расчетов вам, уважаемый читатель!

Можно ли перевести Excel в Word

Как умножать в экселе: элементарный пример в картинках

Как работает условное форматирование в Excel 2010

Поиск данных в таблицах Эксель: ВПР и другие функции

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

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

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

Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

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

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2. т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10. а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10. Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10. Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D. т.е. содержимое D8. Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д .

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА). Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

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

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения ) :

• Искомое значение – данные или ссылка на данные, которые нужно искать;
• Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
• Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10). Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления] ). Аргументы функции:

• Искомое значение – обязательный аргумент
• Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
• Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца] ) :

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com. В следующем посте мы будем рассматривать расчеты кредита в Эксель. будет сложно и интересно!