Формулы впр в excel самоучитель. Как работает функция впр

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

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

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

Синтаксис функции

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

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

Таблица - ссылка на диапазон ячеек. В левом столбце таблицы ищется Искомое_значение , а из столбцов расположенных правее, выводится соответствующий результат (хотя, в принципе, можно вывести можно вывести значение из левого столбца (в этом случае это будет само искомое_значение )). Часто левый столбец называется ключевым . Если первый столбец не содержит искомое_значение , #Н/Д.

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

Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение ближайшее к критерию или совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с критерием). Значение ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по возрастанию. Это способ используется в функции по умолчанию, если не указан другой.

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

Задача1. Справочник товаров

Пусть дана исходная таблица (см. файл примера лист Справочник ).

Задача состоит в том, чтобы, выбрав нужный Артикул товара, вывести его Наименование и Цену .

Примечание . Это "классическая" задача для использования ВПР() (см. статью ).

Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ или ИСТИНА или вообще опустить). Значение параметра номер_столбца нужно задать =2, т.к. номер столбца Наименование равен 2 (Ключевой столбец всегда номер 1).

Для вывода Цены используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра номер_столбца нужно задать =3).

Ключевой столбец в нашем случае содержит числа и должен гарантировано содержать искомое значение (условие задачи). Если первый столбец не содержит искомый артикул, то функция возвращает значение ошибки #Н/Д. Это может произойти, например, при опечатке при вводе артикула. Чтобы не ошибиться с вводом искомого артикула можно использовать (см. ячейку Е9 ).

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

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

В также рассмотрены альтернативные формулы (получим тот же результат) с использованием функций ИНДЕКС() , ПОИСКПОЗ() и ПРОСМОТР() . Если ключевой столбец (столбец с артикулами) не является самым левым в таблице, то функция ВПР() не применима. В этом случае нужно использовать альтернативные формулы. Связка функций ИНДЕКС() , ПОИСКПОЗ() образуют так называемый "правый ВПР": =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)

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

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

Задача2. Поиск ближайшего числа

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

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

Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
Ключевой столбец должен быть обязательно отсортирован по возрастанию;
Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.

Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)

Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)

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

Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.

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

Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена . Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10) .

Примечание : Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА - последнее (см. картинку ниже).

Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ() +ИНДЕКС() или ПРОСМОТР() .

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

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

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

Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.

Следующее, что мы делаем – прописываем аргументы в предложенные поля.

Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.

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

Чтобы после того, как мы напишем формулу и растянем ее по всему столбцу, выбранный диапазон не смещался вниз, нужно сделать ссылки абсолютными: выделите данные в поле и нажмите «F4» . Теперь адрес на ячейки стал абсолютным, к ним добавился знак «$» , и диапазон смещаться не будет.

Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда нужно переносить. У меня прайс состоит из фруктов и цены, мне нужно второе, поэтому ставлю цифру «2» .

В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать нужно точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «ОК» .

Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина» , то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.

Как же работает ВПР? Она берет искомое значение (яблоко) и ищет его в крайнем левом столбце указанного диапазона (перечень фруктов). При совпадении берется значение из этой же строки, только того столбца, который указан в аргументах (2 ), и переносится в нужную нам ячейку (С2 ). Формула выглядит так:

ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)

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

Все цены перенесены из прайса в таблицу закупок в соответствии с названиями фруктов.

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

При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 теперь $I$2:$J$14 .

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

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

Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1 ).

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

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

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

Жмем по любой ячейке в столбце D и вставляем один новый.

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

Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2 ). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1» .

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

Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12» . Выделите строчку и нажмите «F4» , чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2 ) и пишем «ЛОЖЬ» .

В конце нажмите кнопку «ОК» .

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

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

Оценить статью:

(15 оценок, среднее: 5,00 из 5)

Вебмастер. Высшее образование по специальности "Защита информации".. Автор большинства статей и уроков компьютерной грамотности

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

Пример использования функции

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

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

Важно! Аргументы функции отделяются друг от друга символом «;».

В нашем случае формула приобретает следующее значение:

ВПР(A3;F3:H13;3;0)

A3 – искомое значение, это надпись «деревянные кольца», именно ее мы ищем в таблице «Товары»;
F3:H13 – границы таблицы в которой мы ищем данные;
3 – номер столбца;
0 – параметр, отвечающий за точность совпадения. Для получения точного совпадения ставим значение 0.

Варианты заполнения формулы

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

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

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

С несколькими условиями

С несколькими условиями ВПР используется для нахождения товара по двум или более параметрам. Для этого:

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

Выпадающий список

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

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

Если у вас есть вопрос по работе с продуктами Microsoft, спросите об этом. Вместе мы найдем ответ.

Добрый день, многоуважаемые читатели блога сайт.

В данной публикации рассмотрим крайне полезною функцию Excel — ВПР . Формула настолько полезна, что может служить индикатором навыков работы в Excel. Кто не умеет пользоваться ВПР, тот находится на низшей ступени развития, питекантроп фактически. Шутка. На самом деле функция ВПР не очень известная новичкам, но в то же время очень распространена среди бывалых юзеров (и лузеров) Excel. И вот почему.

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

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

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

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

Во второй – данные о ценах:

Если бы перечень товаров в обеих таблицах совпадал, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно было бы легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится – наименования товаров не совпадут.

Данные по многим товарам не соответствуют друг другу. Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

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

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

Формулу ВПР, как и любую другую, можно вызвать с помощью Мастера функций, а можно и вручную прописать. Рассмотрим оба варианта, хотя конечный результат будет одинаковым. Каждый выбирает, как ему удобно. Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f(x) в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР.

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

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

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле « », где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Сейчас ставим 0 (или ЛОЖЬ).

Все, поля заполнены и выглядят примерно так.

Нажимаем ОК или «Ввод». Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.

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

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

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

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

Особенности использования функции ВПР

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

1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на . Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот диапазон должен иметь абсолютную ссылку (адрес диапазона прописывается через $, как на скриншоте Мастера функций выше). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

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

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

4. Если в качестве критерия используются числовые значения (числовые коды, артикулы и проч.), то формула ВПР чувствительна к формату ячейки! Если в одной таблице критерий будет в числовом формате, а в другой в текстовом, то даже при полном совпадении значений функция выдаст ошибку #Н/Д. Данное обстоятельство многих ставит в тупик и заставляет думать, что ВПР не работает. На самом деле пугаться не нужно. Часто достаточно проверить совпадение формата полей с критерием и, если надо, сделать их одинаковыми. Либо опять же использовать функцию СУММЕСЛИ – ей формат по барабану.

5. Еще одно замечание на счет поля с критерием. Практика подсказывает, что значения критерия должны быть не очень длинными, что бы максимально уменьшить вероятность «случайных» различий. К примеру, лишний пробел между словами или одна неправильная буква делает, казалось бы, одинаковые значения критерия несопоставимыми. Поэтому артикул или штрихкод товара, вполне годятся, а вот название в несколько слов для критерия лучше не использовать.

6. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество. Остальные проигнорируются.

7. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

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

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

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

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

Использование функции

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

ВПР содержит 4 аргумента .

Функция ведет поиск искомого значения в крайнем левом столбце и производит возврат значения в той же строке из указанного столбца.

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

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

Последний аргумент – интервальный просмотр, здесь может быть 2 значения: 0 – ЛОЖЬ , 1 — ИСТИНА . отвечает за точный поиск (совпадения при просмотре сверху вниз). Если ничего не находит, то возвращается ошибка Н/Д (нет данных), 1 – приблизительный .

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

Примеры использования

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

После знака равно вводим ВПР , затем Enter и Fx для ввода аргументов.

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

Искомыми будут значения, по которым будем искать совпадения. В данном случае это ячейка E1 .

Для второго аргумента выделяем диапазон таблицы .

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

– то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар ). Для точного поиска 4 аргумент – .

Введя все значения, жмём кнопку ОК .

Теперь при изменении в фильтре номера ID будет изменяться наименование товара.

Теперь посмотрим другой пример .

Теперь нужно получить партию для каждого наименования товара по критерию Количество .

Например, для мелкой партии количество должно быть от 100 до 200 , средней – 200-300 и т.д.

Искомым значением в данном случае будет количество , Таблицу выбираем диапазон Критерий – Партия (фиксируем F4). Номер столбца 2, интервальный просмотр в этом случае должен быть 1 (позволит получить ближайшее меньшее значение к искомому значению).

Как видим в полученной таблице для количества, например, 110, партию выдало Мелкая (ближайшее меньшее 100) и т.д. Обязательно сортировка критериев должна быть от большего к меньшему , иначе ВПР не сработает.