Функция впр в excel: пошаговая инструкция с 5 примерами

Функция ВПР в Excel – как пользоваться

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

Теперь нужно вызвать функцию. Это можно сделать разными способами:

1. Прописать функцию вручную.
2. Нажать «Вставить функцию», выбрать «Ссылки и массивы», выбрать ВПР.
3. Открыть вкладку «Формулы», выбрать «Ссылки и массивы», выбрать ВПР.

Необходимо заполнить значения для функции ВПР

4. Искомое_значение – это то, что Эксель будет искать в другой таблице. В нашем случае, Иванов, Петров, Сидоров. Кликаем по первой фамилии в списке это ячейка А2. Удерживаем на клавиатуре клавишу Shift, после чего кликаем по последней фамилии в столбце, ячейка А4. В итоге получаем выделенный список фамилий с которым ВПР будет работать.
5. Таблица – место где будет осуществлен поиск. В нашем случае вторая таблица лежит в Лист 2. Переходим на него. Здесь необходимо выделить весь интервал поиска. Кликаем по левой верхней ячейке интервала. Удерживаем на клавиатуре Shift и нажимаем левой кнопкой мыши правую нижнюю ячейку. Нажимаем F4. Интервал поиска задан.
6. Номер_столбца – номер столбца из таблицы, в которой ищутся данные. В нашем случае – 2.
7. Интервальный_просмотр – это логическое значение. В нашем случае необходимо написать «ЛОЖЬ».
8. Нажать «ОК»
9. Теперь необходимо скопировать функцию в каждую пустую ячейку. Можно, например, сделать с помощью копировать — вставить, а можно нажать на ячейку с результатом работы функции, и в правом нижнем углу кнопкой мыши слева нажать на жирный квадратик, не отпуская левую кнопку потянуть вниз.

Результат налицо – в таблице 3 (смотреть выше).

Функция ВПР в Excel с несколькими условиями

До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.

Таблица для примера:

Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.

Дело осложняется тем, что от одного поставщика поступает несколько наименований.

Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
Таким же образом объединяем искомые критерии запроса:
Теперь ставим курсор в нужном месте и задаем аргументы для функции:. Excel находит нужную цену.. Рассмотрим формулу детально:

Рассмотрим формулу детально:

1. Что ищем.
2. Где ищем.
3. Какие данные берем.

Важный момент

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

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

Активация этой опции приведёт к открытию на вашем экране диалогового окна, в котором вам нужно будет поставить флажок рядом с категорией «Значение». Подтвердите совершённое вами действия, кликнув на кнопку «ОК».

Возвращаемся к нашей строке формул и проверяем наличие в столбце «Цена» активной функции VLOOKUP. Если на месте формулы вы видите просто числовые значения, значит, всё получилось, и функция ВПР отключена. То есть связь между двумя файлами Excel разорвана, а угроза незапланированного изменения или удаления прикреплённых из таблицы с прайсом данных нет. Теперь вы можете смело пользоваться табличным документом и не волноваться, что будет, если «Прайс-лист» окажется закрыт или перемещён в другое место.

Ошибки при использовании функции ВПР

Ошибка #Н/Д

Ошибка
#Н/Д может возникать в нескольких случаях:

Если наименования, которое мы ищем, нет в искомом массиве.

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

Для
исправления ошибки #Н/Д можно использовать следующую функцию   ,
выбрать в значении возникновения ошибки в функции, в нашем случае #Н/Д = 0,
тогда все итоги расчетов с ошибками будут равны 0.

Формула
как бы вписывается вокруг формулы ВПР и выглядит следующим образом:

Вызываем функцию  — пишем слово ЕСЛИ после равно, но перед словом ВПР:

Необходимо дописать формулу:

Вставка столбца внутри таблицы, в которой происходит поиск

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

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

Вопросы от новичков

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

Как сравнить таблицы с помощью ВПР?

Сравнение двух и более таблиц проводится почти так же, как добавление нового ряда данных:

Открыть обе таблицы, а при необходимости — перенести их на один лист.

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

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

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

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

Как с помощью ВПР сделать выпадающий список?

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

Открыть исходную таблицу и способом, подробно описанным в первом примере, подготовить «результирующую».

Перейти на вкладку «Данные» и найти в «ленте» инструмент «Проверка данных».

Щёлкнув по нему, вызвать выплывающее меню, а далее — кликнуть по одноимённой строчке.

Выбрать в новом выплывающем меню «Тип данных» пункт «Список».

В строке «Источник» указать требуемый диапазон наименований, после чего нажать «ОК».

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

Почему функция не работает

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

Нужно точное совпадение

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

Поэтому если требуется уникальное значение, то нужно обязательно указывать последний аргумент со значением ЛОЖЬ.

Необходима фиксация ссылок на таблицу

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

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

Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать

Очень хорошо это видно на примере ниже. Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать.

19

Чтобы решить эту проблему, достаточно просто нажать на клавишу F4, чтобы зафиксировать адрес ссылки.

Простыми словами, формула должна обрести следующий вид.

=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

Вставлена колонка

Для чего нужен аргумент «номер столбца»? Для того, чтобы задать функции, какие именно данные должны быть извлечены. 

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

20

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

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

Увеличение размеров таблицы

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

21

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

Функция не умеет анализировать данные слева

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

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

Дублирование данных

Еще один недостаток функции в том, что она умеет извлекать лишь первое найденное значение, нужно удалить дубликаты с помощью кнопки «Удалить дубликаты», располагаемой на вкладке «Данные».

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

Кратко о том, что такое функция ВПР в Excel

ВПР или VLOOKUP — это базовая функция Excel и Google Sheets, которая позволяет искать информацию в больших наборах данных, соединять данные из разных таблиц или листов, генерировать отчеты и обновлять данные в таблице. Синтаксис функции выглядит так:

Fx = ВПР(искомое_значение; таблица_диапазон; номер_столбца; )

Таблица (диапазон) — диапазон ячеек, в котором будет проведен поиск искомого значения.

Номер столбца — столбец в таблице (диапазоне), из которого будет возвращен результат. 

Искомое значение — значение, которое функция ищет в первом столбце диапазона. 

Интервальный просмотр (необязательный аргумент) — определяет, нужно ли искать точное совпадение. По умолчанию значение аргумента — ЛОЖЬ.

ГПР

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

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

В примере выше мы ищем выручку за сентябрь в помесячном отчете по выручке. В формуле ГПР(A5;B1:M2;2;0) первый параметр (А5) — ссылка на месяц, по которому мы хотим получить выручку; второй параметр (B1:M2) — ссылка на таблицу, где в первой строке указаны месяцы, среди которых нам нужно найти выбранный; третий параметр «2» — из какой строки ниже мы будем получать данные; четвертый параметр «0» — ищем точное совпадение.

Если вы хотите более подробно изучить, как пользоваться функцией ГПР — прочитайте статью на нашем сайте «Функция ГПР в Excel».

Функция ВПР в Excel примеры

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

Функция ВПР на разных листах

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

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 («Продукт 3») в столбце A (1-й столбец диапазона поиска A2:B9) на листе «Цены»:

=ВПР(A2;Цены!$A$2:$B$8;2;ЛОЖЬ)

Функция ВПР в Excel – Функция ВПР на разных листах

Обратите внимание, что рекомендуется использовать на ячейки (со знаком $) в аргументе таблица. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки

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

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

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

Функция ВПР в Excel – Присвоение имени диапазону

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

=ВПР(«Продукт 1»;Продукты;2)

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

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

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

=ВПР(«Продукт 1»;Таблица6:];2)

или даже =ВПР(«Продукт 1»;Таблица6;2).

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

Синтаксис

Синтаксис функции ВПР
ВПР (заданное значение, таблица, номер столбца, )

Аргументы: 

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

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

Таблица – область поиска, содержащая и ключевой столбец; 

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

Необязательный «интервальный просмотр» относится к диапазону просмотра. Он может иметь два логических значения – ИСТИНА или ЛОЖЬ. В первом случае ищется приблизительное совпадение в диапазоне, а во втором – точное. При отсутствии заполнения этого параметра по умолчанию предполагается значение ИСТИНА.  

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

Функция ВПР не чувствительна к регистру символов. 

Сравнение данных двух таблиц Excel

ВПР в Excel может быть использована для сравнения данных двух таблиц. Например, пусть у нас есть два листа с данными о выпуске продукции двумя цехами. Мы можем сопоставить фактический выпуск для обоих. Напомним, что для переключения между листами служат их ярлыки в нижней части окна. На двух листах мы имеем одинаковые таблицы с разными данными. Как видим, план выпуска у них одинаков, а вот фактический отличается. Переключаться и сравнивать построчно даже для небольших объемов данных очень неудобно. На третьем листе создадим таблицу с тремя столбцами. В ячейку B2 введем функцию ВПР. В качестве первого аргумента укажем ячейку с месяцем на текущем листе, а диапазон выберем с листа «Цех1». Чтобы при копировании диапазон не смещался, нажмите F4 после выбора диапазона. Это сделает ссылку абсолютной. Растяните формулу на весь столбец. Аналогично введите формулу в следующий столбец, только диапазон выделяйте на листе «Цех2». После копирования Вы получите сводный отчет с двух листов.

Составляем ключ поиска с помощью «амперсанда» (&”|”&)

В случаях, когда нужно произвести поиск в нескольких столбиках сразу, необходимо использовать составной ключ для формирования поиска. В случаях, когда поиск проводится по числовым значениям значительно удобнее использовать функцию СУММЕСЛИМН и придумывать составной ключ нет необходимости, а вот текстовые значения придётся искать с помощью склеенного ключа для функции ВПР. А на этом у меня всё! Я очень надеюсь, что всё описанные тонкости использования функции ВПР в Excel вам понятны. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Как употреблять функцию «ВПР» для сопоставления данных

Показывать эту возможность будем с помощью 2-ух таблиц. Представим, что у нас на втором листе возникли обновлённые данные о сотрудниках. Наша задачка заключается в том, чтоб выяснить, что конкретно поменялось. Для этого необходимо будет создать последующее.

1. Добавим 2-ой лист с буквально таковой же таблицей (копировали с помощью жарких кнопок Ctrl+C и Ctrl+V).
2. Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сопоставления.

1. Добавим ещё один столбец в нашу старенькую таблицу.

1. Перебегаем в первую клеточку новейшего столбца и вводим там последующую формулу.

• $B$3:$B$11 – для поиска употребляются все значения первой колонки (используются абсолютные ссылки);
• Лист2! – эти значения необходимо находить на листе с обозначенным заглавием;
• $B$3:$E$11 – таблица, в какой необходимо находить (спектр ячеек);
• 4 – номер столбца в обозначенной области данных;
• ЛОЖЬ – находить четкие совпадения.

1. Новенькая информация выведется в том месте, где мы указали формулу.
2. Итог будет последующим.

1. Сейчас продублируйте эту формулу в другие ячейки. Для этого необходимо потянуть мышкой за правый нижний угол начальной клеточки.

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

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

В схожих вариантах приходится употреблять разные доп столбцы, в каких объединяют информацию с нескольких колонок. А это смотрится безобразно и не совершенно комфортно.

ВПР – инструкция для работы с двумя условиями

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

Пример, необходимо в таблицу 4, вставить цену из таблицы 5.

Характеристики телефонов таблица 4

Название	ОЗУ	Камера
ZTE	0,5	5
ZTE	1	5
DNS	1	8
DNS	0,5	8
Alcatel	1	8
Alcatel	256	2

Характеристики телефонов таблица 5

Название	ОЗУ	Цена
ZTE	0,5	1 990 ₽
ZTE	1	3 099 ₽
DNS	1	3 100 ₽
DNS	0,5	2 240 ₽
Alcatel	1	4 500 ₽
Alcatel	256	450 ₽

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

Добавляем крайний левый столбец. Например, называем его «Объединение». В первую ячейку значений, у нас B 2, пишем конструкцию «= B 2& C 2». Размножаем с помощью мыши. Получается, как в таблице 6.

Характеристики телефонов таблица 6

Объединение	Название	ОЗУ	Цена
ZTE 0,5	ZTE	0,5	1 990 ₽
ZTE 1	ZTE	1	3 099 ₽
DNS1	DNS	1	3 100 ₽
DNS 0,5	DNS	0,5	2 240 ₽
Alcatel 1	Alcatel	1	4 500 ₽
Alcatel 256	Alcatel	256	450 ₽

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

Смотрите видеоурок как пользоваться функцией ВПР в Эксель для чайников:

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

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

Пришло время обсудить последний аргумент функции ВПР. Как правило, я указываю 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.

Данная статья рассчитана на читателя, который владеет и умеет пользоваться такими простейшими из них как SUM (СУММ), AVERAGE (СРЗНАЧ) и TODAY(СЕГОДНЯ).

По своему основному назначению, ВПР — это функция баз данных, т.е. она работает с таблицами или, проще говоря, со списками объектов в таблицах Excel. Что это могут быть за объекты? Да что угодно! Ваша таблица может содержать список сотрудников, товаров, покупателей, CD-дисков или звёзд на небе. На самом деле, это не имеет значения.

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

Обычно в списках вроде этого каждый элемент имеет свой уникальный идентификатор. В данном случае уникальный идентификатор содержится в столбце Item Code.

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

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

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

Другими словами, если Вы введёте в ячейку функцию ВПР и передадите ей в качестве аргумента один из уникальных идентификаторов Вашей базы данных, то в результате в ячейке появится какой-то кусок информации, связанный с этим уникальным идентификатором. Применительно к примеру, приведенному выше: если бы мы ввели в качестве аргумента значение из столбца Item Code, то как результат могли бы получить соответствующее ему описание товара (Description), его цену (Price), или наличие (In Stock). Какую именно информацию должна вернуть формула, Вы сможете решить в процессе её создания.

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

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

Наступило время обсудить последний аргумент функции ВПР. Как правило, указываю 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».