Функция ВПР() , английский вариант 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) .
Примечание : Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА - последнее (см. картинку ниже).
Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ() +ИНДЕКС() или ПРОСМОТР() .
ВПР (VLOOKUP) – одна из полезнейших функций Excel, равно как и одна из наименее знакомых пользователям. В этой статье мы поднимем завесу тайны с функции ВПР с помощью примера из реальной жизни. Мы создадим имеющий практическую ценность шаблон счёта для вымышленной компании.
Немного о функции ВПР
Итак, что же такое ВПР ? Думаю, Вы уже догадались, что это одна из множества функций Excel.
Данная статья рассчитана на читателя, который владеет базовыми знаниями о функциях Excel и умеет пользоваться такими простейшими из них как SUM (СУММ), AVERAGE (СРЗНАЧ) и TODAY(СЕГОДНЯ).
По своему основному назначению, ВПР - это функция баз данных, т.е. она работает с таблицами или, проще говоря, со списками объектов в таблицах Excel. Что это могут быть за объекты? Да что угодно! Ваша таблица может содержать список сотрудников, товаров, покупателей, CD-дисков или звёзд на небе. На самом деле, это не имеет значения.
Вот пример списка или базы данных. В данном случае, это список товаров, которые продаёт вымышленная компания:
Обычно в списках вроде этого каждый элемент имеет свой уникальный идентификатор. В данном случае уникальный идентификатор содержится в столбце Item Code .
Чтобы функция ВПР могла работать со списком, этот список должен иметь столбец, содержащий уникальный идентификатор (его называют Ключ или ID), и это должен быть первый столбец таблицы. Таблица, представленная в примере выше, полностью удовлетворяет этому требованию.
Самое трудное в работе с функцией ВПР – это понять, для чего она вообще нужна. Давайте попробуем разобраться с этим в первую очередь.
Функция ВПР извлекает из базы данных информацию, основываясь на уникальном идентификаторе.
Другими словами, если Вы введёте в ячейку функцию ВПР и передадите ей в качестве аргумента один из уникальных идентификаторов Вашей базы данных, то в результате в ячейке появится какой-то кусок информации, связанный с этим уникальным идентификатором. Применительно к примеру, приведенному выше: если бы мы ввели в качестве аргумента значение из столбца Item Code , то как результат могли бы получить соответствующее ему описание товара (Description), его цену (Price), или наличие (In Stock). Какую именно информацию должна вернуть формула, Вы сможете решить в процессе её создания.
Если всё, что Вам нужно, это один раз найти какую-то информацию в базе данных, то создавать ради этого формулу с использованием функции ВПР – слишком сложный путь. Подобные функции, обычно, применяются в таблицах для многократного использования, например, в шаблонах. Каждый раз, когда кто-либо введёт определенный код, система будет извлекать всю необходимую информацию в соответствующие позиции листа.
Создаем шаблон
Давайте создадим шаблон счёта, который мы сможем использовать множество раз в нашей вымышленной компании.
Для начала, запустим Excel…
… и создадим пустой счёт.
Вот как это должно работать: пользователь шаблона будет вводить коды товаров (Item Code) в столбец А. После чего система будет извлекать для каждого товара описание и цену, а далее рассчитывать итог по каждой строке. Количество необходимо указать самостоятельно.
Для простоты примера, мы расположим базу данных с товарами в той же книге Excel, но на отдельном листе:
В реальной жизни базы данных чаще хранятся в отдельном файле. Это мало беспокоит функцию ВПР , поскольку для неё нет разницы, где находится база данных - на том же листе, на другом листе книги или вообще в отдельном файле.
Вставляем функцию ВПР
Чтобы протестировать функцию ВПР , которую мы собираемся записать, сначала введём корректный код товара в ячейку A11:
Далее делаем активной ту ячейку, в которой должна появиться информация, извлекаемая функцией ВПР из базы данных. Любопытно, что именно на этом шаге многие путаются. Поясню, что мы будем делать далее: мы создадим формулу, которая извлечёт из базы данных описание товара, код которого указан в ячейке A11. Куда мы хотим поместить это описание? Конечно, в ячейку B11. Следовательно, и формулу мы запишем туда же.
Итак, выделите ячейку B11:
Нам требуется открыть список всех существующих функций Excel, чтобы найти в нём ВПР и получить некоторую помощь в заполнении формулы. Для этого зайдите на вкладку Formulas (Формулы) и выберите команду Insert Function (Вставить функцию).
Появляется диалоговое окно, в котором можно выбрать любую существующую в Excel функцию. Чтобы найти то, что нам необходимо, мы можем ввести в поле Search for a function (Поиск функции) слово lookup (или поиск в русскоязычной версии), поскольку нужная нам функция – это функция поиска. Система покажет список всех связанных с этим понятием функций Excel. Найдите в списке VLOOKUP (ВПР), выберите её мышкой и нажмите ОК .
Появится диалоговое окно Function Arguments (Аргументы Функции), предлагающее ввести все необходимые аргументы для функции ВПР . Представьте себе, что это сама функция задаёт Вам следующие вопросы:
- Какой уникальный идентификатор Вы ищите в этой базе данных?
- Где находится база данных?
- Какую информацию Вы бы хотели извлечь из базы данных?
Первые три аргумента выделены жирным шрифтом, чтобы напомнить нам, что они являются обязательными (функция ВПР без любого из них является не полной и не сможет вернуть корректное значение). Четвёртый аргумент не выделен жирным, поскольку он необязателен и используется по необходимости.
Заполняем аргументы функции ВПР
Первый аргумент, который надо указать, это Lookup_value (Искомое_значение). Функция просит нас указать, где искать значение уникального кода товара, описание которого надо извлечь. В нашем случае, это значение в столбце Item code , которое мы ввели раньше в ячейку A11.
Нажмите на иконку выбора справа от строки ввода первого аргумента.
Затем кликните один раз по ячейке, содержащей код товара и нажмите Enter .
Значение ячейки A11 взято в качестве первого аргумента.
Теперь нужно задать значение аргумента Table_array (Таблица). Другими словами, надо объяснить функции ВПР, где находится база данных, в которой необходимо выполнять поиск. Кликните по иконке выбора рядом со вторым аргументом:
Теперь найдите базу данных и выберите весь диапазон без строки заголовков. Поскольку база данных находится на отдельном листе, то сначала перейдите на нужный лист, кликнув по вкладке листа:
… и нажимаем Enter . В строке для ввода второго аргумента автоматически отобразится диапазон ячеек, в котором содержится вся база данных. В нашем случае это "Product Database"!A2:D7 .
Теперь займёмся третьим аргументом Col_index_num (Номер_столбца). С помощью этого аргумента мы указываем функции ВПР , какой именно кусок информации из базы данных мы хотим извлечь. В данном случае нам необходимо извлечь описание товара (Description). Если Вы посмотрите на базу данных, то увидите, что столбец Description это второй столбец в таблице. Это значит, что для аргумента Col_index_num (Номер_столбца) мы вводим значение 2:
Важно заметить, что мы указываем значение 2 не потому, что столбец Description находится во втором по счету столбце от начала листа Excel, а потому, что он второй по счёту в диапазоне, который указан в качестве аргумента Table_array (Таблица) функции ВПР (первым является столбец с уникальным идентификатором). Если наша база данных будет начинаться где-то со столбца K листа Excel, то мы всё равно укажем значение 2 в этом поле.
В завершение, надо решить, нужно ли нам указывать значение для последнего аргумента ВПР – Range_lookup (Интервальный_просмотр). Значение этого аргумента может быть либо TRUE (ИСТИНА), либо FALSE (ЛОЖЬ), либо вообще может быть не указано. Используя функцию ВПР в работе с базами данных, в 90% случаев принять это решение помогут следующие два правила:
- Если первый столбец базы данных (содержащий уникальные значения) отсортирован по возрастанию (по алфавиту или по численным значениям), то в этом поле можно ввести значение TRUE (ИСТИНА) или оставить его пустым.
- Если первый столбец базы данных не отсортирован или отсортирован по убыванию, тогда для этого аргумента необходимо установить значение FALSE (ЛОЖЬ).
Так как первый столбец нашей базы данных не отсортирован, мы вводим для этого аргумента значение FALSE (ЛОЖЬ):
Последний штрих…
Вот и всё! Мы ввели всю информацию, которая требуется функции ВПР , чтобы предоставить нам то значение, которое нас интересует. Жмите ОК и обратите внимание, что описание товара, соответствующее коду R99245 , появилось в ячейке B11.
Добрый день, многоуважаемые читатели блога сайт.
В данной публикации рассмотрим крайне полезною функцию 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 и извлечь их в другую, а также открывает все секреты функции вертикального просмотра.
Использование функции СТОЛБЕЦ для указания колонки извлечения
Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.
Создание составного ключа через &»|»&
Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.
Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт , а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Спасибо. :)
При работе с табличными документами, которые могут иметь довольно разную структуру данных, пользователь часто сталкивается с потребностью отождествления данных одной таблицы с другой. Иными словами, появляется необходимость найти данные в одной таблице (например, прайс лис) и перенести их в другую таблицу (проданный товар за определенный период времени, цены и наименование которого указаны как раз в первой таблице). Дабы не ввергать вас, уважаемый читатель, в своеобразную путаницу слов и иносказательного винегрета, давайте рассмотрим практический пример, как работает функция впр в excel.
Сегодня мы рассмотрим:
Вводная часть: Синтаксис
Данная функция имеет четыре параметра:
- «ЧТО» - редко использующееся значение, указывающее на объект поиска или же конкретная ссылка на ячейку с искомым значением. Последнее можно смело причислить к самому используемому параметру при работе с функцией ВПР.
- «ГДЕ» - ссылка на диапазон ячеек (массив двумерный), в первом столбце которого и будет происходить поиск значения параметра «ЧТО».
- «НОМЕР СТОЛБЦА» - номер столбца в диапазоне, из которого будет возвращено значение;
- «ОТСОРТИРОВАНО» - весьма важный параметр, так как от правильности выбранного условия: «1-ИСТИНА» - «2-ЛОЖЬ», будет зависеть конечный результат работы примененной функции ВПР (осуществляться выборка данных относительно вопроса: отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>). Стоит отметить, что в случае, если вы проигнорируете процесс установки нужного значения, параметр автоматически примет условие «1-ИСТИНА».
Так, надеемся, здесь мы разобрались. Теперь перейдем к практической части повествования, так сказать, самой ожидаемой.
Простой пример: сводим данные двух таблиц
Итак, к вашему вниманию классический пример: таблица «Проданный товар», в которой необходимо подставить значения из таблицы «Прайс лист».
- Становимся на ячейку «D6».
- Вызываем служебное окно консоли «fx», нажатием соответствующей клавиши, и в заданном окне мастера функций активируем чек бокс «Категории».
- Выбираем пункт «Ссылки и массивы».
- В боксе выбора функции устанавливаем значение «ВПР».
- Нажимаем кнопку «ОК» и переходим к следующему шагу - вводу аргументов этой функции.
- Используя левую кнопку мышки, сделайте клик по первой ячейки вашего списка наименований, в нашем примере этому действию назначается активация ячейки «B6». Итак, пункту «Искомое значение» соответствует значение «B6».
- Во втором чек боксе «Таблица» указываем аргумент, который мы ищем, то есть указываем откуда именно будут браться столь необходимые нам значения: Зажимаем левую кнопку мыши и выделяем весь прайс лист. Вернее, его главную часть - данные, избегая моментов выделения названий столбцов и, разумеется, шапки.
- Теперь требуется превратить ссылку на таблицу, так сказать, в абсолютную - выделяем аргумент из примера «G6:I10» и жмем клавишу «F4».
- В итоге мы видим, что прежняя ссылка изменилась: исходные символы стали окружены долларовыми знаками «$G$6:$I$10», чего и требовалось достигнуть.
- Третье поле служебного окна «Номер столбца» требует указания числа два (2), так как именно со второго столбца первой таблицы нужно соотнести значения к данным первой таблицы «наименование».
- Ну и наконец, четвертый параметр, который нам необходимо указать - это «нуль», в графе «Интервальный просмотр». Так как значение «1» соответствует числовым параметрам данных, в нашем же случае используется поиск искомого объекта, так сказать, в текстовом виде, поэтому наш выбор очевиден - «нуль».
Что ж, итогом наших манипуляций стало появившееся значение в столбце «Цена», первой таблицы «Проданный товар» - число «10», что соответствует указанному значению из второй таблицы.
Теперь осталось одно - ввести формулу умножения цены на количество и наслаждаться конечным результатом.
- В ячейке «E6» ставим знак равенства.
- Перемещаем маркер на позицию «С6».
- Далее нажимаем знак умножения.
- Переходим на ячейку «D6» и жмем клавишу «Enter».
- Все что нам необходимо сделать, дабы редактор Exel отобразил финальный результат наших действий, так это, копировать формулу, путем протягивания двух последних столбцов (область с данными), сверху вниз - появятся актуальные значения согласно произведенным операциям.
На этом, все - точных расчетов вам, уважаемый читатель!
