Что такое впр excel. Четыре способа использования впр с несколькими условиями.

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

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

Как работает функция ВПР в Excel: пример

Функция ВПР предназначена для выборки данных из таблицы Excel по определенным критериям поиска. Например, если таблица состоит из двух колонок: «Наименование товара» и «Цена». Рядом находится другая таблица, которая будет искать в первой таблице по наименованию товара и получать значение соответствующей цены.

В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.

Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.

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

Функция ВПР в Excel и две таблицы

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

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

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

1. Исходное значение: G3.
2. Таблица: A2:E7. Диапазон нашей таблицы расширен.
3. Номер столбца: {3;4;5}. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
4. Интервальный просмотр: ЛОЖЬ.
5. Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).
6. После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно . В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «{}», что свидетельствует о выполнении формулы в массиве.

Теперь вводите в ячейку G3 наименование товара, в ячейке H3 получаем сумму продаж в первом квартале по данному товару.

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

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

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

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

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

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

Вводная часть: Синтаксис

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

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

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

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

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

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

Простой пример: сводим данные двух таблиц

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

VLOOKUP (Vertical Lookup) – это еще одно название функции, которое можно встретить в англоязычной версии табличного процессора. Сама аббревиатура ВПР означает «вертикальный просмотр». Анализ данных и их поиск в таблице осуществляется с помощью постепенного перебора элементов от строки к строке в каждой колонке.

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

Как выглядит синтаксис ВПР?

Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций. Посмотреть правильный вид опции можно, открыв табличный процессор :

• Используйте уже созданный документ, либо откройте новый пустой лист;
• Кликните на клавишу «Формулы» , как показано на рисунке ниже;
• В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
• Настройте категорию «Полный перечень» ;
• Кликните на «Найти» .

Рис.2 – поиск формул в Эксель

В результате поиска формулы вы увидите её нахождение в перечне. Нажав на элемент, внизу экрана отобразится его формула. За скобками указывается название функции, а внутри скобок – её параметры. Внутри формулы каждый отдельный параметр прописывается в угловых <> скобах. Общий вид описания для ВПР выглядит так:

Рис.3 – перечень параметров

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

• <ЧТО> - первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
• <НОМЕР_СТОЛБЦА> - тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
• <ГДЕ> - здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
• <ОТСОРТИРОВАНО> - этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР <ОТСОРТИРОВАНО> можно пропустить, и тогда его значение по умолчанию принимается как истина.

Как работает ВПР. Полезный пример

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

После заполнения таблицы кликнем на пустую ячейку и выпишем в неё формулу и результат ВПР. Кликните на вкладку «Формулы» и выберите VLOOKUP. Затем введите все необходимые параметры в окно, которое изображено на рисунке 3. Подтвердите действие. В ячейке отобразится результат выполнения команды.

Рис.4 – пример поиска в простой таблице

На рисунке выше в цветных ячейках указывается значение для товара. Если вы не ввели значение для сортировки, то функция автоматически воспринимает это как единицу. Далее программа «думает», что элементы первого столбика вашей таблицы идут по возрастанию сверху-вниз. Благодаря этому, процедура поиска будет остановлена только когда будет достигнута строка со значением, номер которого уже превышает искомый объект.

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

Если в таблице нет введенного вами идентификатора для имени продукции, в результате выполнения поиска VLOOKUP будет получено значение «Н/Д», что означает отсутствие элемента для заданного номера.

Рис.5 – второй пример для ВПР

Когда использовать ВПР?

Выше описаны два варианта применения VLOOKUP.

Первая вариация VLOOKUP подойдет для следующих случаев:

• Когда необходимо разделить значения объекта табличного процессора по его диапазонам;
• Для тех таблиц, в которых параметр ГДЕ может содержать несколько идентичных значений. В таком случае, формула вернет только то, которое находится в последней строчке относительно массива;
• Когда нужно искать значения, которые больше того, которое может содержаться в первом столбике. Так вы найдете последнюю строку таблицы практическим мгновенно.

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

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

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

Рис.6 – пример поиска текстового значения

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

Рис.7 – ВПР при удалении пробелов

Быстродействие VLOOKUP

Большинство пользователей предпочитать не вписывать параметр <СОРТИРОВКА> во время работы с функцией . Конечно же, ноль вписать проще, но игнорирование оператора замедляет поиск. При работе с большими массивами данных Эксель может работать слишком медленно. На старых устройствах табличный процессор иногда даже зависает из-за слишком медленного поиска с ВПР.

Если на одном листе вашего документа представлено сразу несколько тысяч формул, лучше позаботиться о сортировке первого столбца. Это позволяет увеличить общую производительность поиска на целых 400%-500%.

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

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

Таблица с ценами 2015 года:

Таблица с ценами 2016 года:

Казалось бы, что сложного в этой задаче – просто упорядочим обе таблицы по имени запчасти и скопируем данные из одной таблицы в другую. Но проблема в том в 2016 году появились новые запчасти и данные просто не совпадут. Может появиться желание вручную перенести данные. В данном примере это легко выполнимо, а представьте, что таких строк у вас не 10, а 10 000, к примеру. Именно для решения таких задач функция ВПР подходит просто идеально.

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

Скорее всего, ваши таблицы будут в разных файлах. Для удобства, скопируйте их на разные листы одной книги, как в примере выше. Нам нужно чтобы в таблице 2016 года в третьем столбце появились данные из 2015 года. Выбираем ячейку С2 и пишем «=ВПР(» без кавычек, Excel сразу выведет подсказку:

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

Искомое значение – значения, которые мы будем искать в другой таблица, в нашем случае это ячейки А2-А10.

Таблица – часть второй таблицы (в нашем случае 2015 года) где мы будем искать искомые значения. В нашем случае переходим на вкладку 2015 и выделяем столбцы A и B.

Номер столбца – столбец из которого будут подставляться данные, в нашем случае это «2».

Интервальный просмотр – искать точное совпадение или примерное. Нам нужно точное, поэтому указываем «ЛОЖЬ».

Итого, конечная формула получается такая: «=ВПР(A2;"2015"!A:B;2;ЛОЖЬ)» . То есть, еще раз, как работает формула на конкретном примере: берется слово МОТОР из таблицы 2015 и ищется в таблице 2016, после чего берется число 1000 из второго столбца и подставляется в таблицу 2016.

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

У двух значений написано #Н/Д – это значит «Нет данных», это как раз те позиции, которых не было в 2015 году.

Вот и все! Если будут вопросы – ! Удачи!

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

Задача1. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.

Функция ВПР. Решение задачи

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

Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как:) Но на самом деле все проще. Для этого нам нужно понять из чего состоит ВПР

Переменные. Функция ВПР

• Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1. Номер ТС в Таблице2.
• Таблица — все столбцы Таблицы1, приче первый столбец, должен быть который мы ищем(Номер ТС)
• Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения (ФИО)
• [интервальный_просмотр] — может принимать только два значения Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.

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

Итак напишем функцию для нашей задачи:

ВПР(E:E;A:B;2;ЛОЖЬ)

E:E — это диапазон значений по которым Excel будет сравнивать условия с Таблицей 1. A:B — вся Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот по счету столбец в Таблице 1, который мы будем переносить в ячейку F1. ЛОЖЬ — смотрите выше.

Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично, значит люди экономят время, зная о простых и действенных методах. Не отставайте!

Не забудьте растащить функцию до конца диапазона. Не знаете как это сделать быстро?

А так можно сделать ту же функцию с удобными названными диапазонами (на картинке ниже)? Нравится? тут.