Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ
В начале разъясним, что мы подразумеваем под выражением «Динамическая подстановка данных из разных таблиц», чтобы убедиться правильно ли мы понимает друг друга.
Бывают ситуации, когда есть несколько листов с данными одного формата, и необходимо извлечь нужную информацию с определенного листа в зависимости от значения, которое введено в заданную ячейку. Думаю, проще это объяснить на примере.
Представьте, что имеются отчеты по продажам для нескольких регионов с одинаковыми товарами и в одинаковом формате. Требуется найти показатели продаж для определенного региона:
Если у Вас всего два таких отчета, то можно использовать до безобразия простую формулу с функциями ВПР и ЕСЛИ (IF), чтобы выбрать нужный отчет для поиска:
Где:
$D$2 – это ячейка, содержащая название товара
Обратите внимание, здесь мы используем абсолютные ссылки, чтобы избежать изменения искомого значения при копировании формулы в другие ячейки.
$D3 – это ячейка с названием региона. Используем абсолютную ссылку для столбца и относительную ссылку для строки, поскольку планируем копировать формулу в другие ячейки того же столбца.
FL_Sales и CA_Sales – названия таблиц (или именованных диапазонов), в которых содержаться соответствующие отчеты о продажах
Вы, конечно же, можете использовать обычные названия листов и ссылки на диапазоны ячеек, например ‘FL Sheet’!$A$3:$B$10, но именованные диапазоны гораздо удобнее.
Однако, когда таких таблиц много, функция ЕСЛИ – это не лучшее решение. Вместо нее можно использовать функцию ДВССЫЛ (INDIRECT), чтобы возвратить нужный диапазон поиска.
Как Вы, вероятно, знаете, функция ДВССЫЛ используется для того, чтобы вернуть ссылку, заданную текстовой строкой, а это как раз то, что нам сейчас нужно. Итак, смело заменяем в представленной выше формуле выражение с функцией ЕСЛИ на ссылку с функцией ДВССЫЛ. Вот такая комбинация ВПР и ДВССЫЛ отлично работает в паре:
Где:
- $D$2 – это ячейка с названием товара, она неизменна благодаря абсолютной ссылке.
- $D3 – это ячейка, содержащая первую часть названия региона. В нашем примере это FL.
- _Sales – общая часть названия всех именованных диапазонов или таблиц. Соединенная со значением в ячейке D3, она образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто не имеет опыта работы с функцией ДВССЫЛ.
Как работают ДВССЫЛ и ВПР
Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):
Первый аргумент может быть ссылкой на ячейку (стиль A1 или R1C1), именем диапазона или текстовой строкой. Второй аргумент определяет, какого стиля ссылка содержится в первом аргументе:
- A1, если аргумент равен TRUE (ИСТИНА) или не указан;
- R1C1, если FALSE (ЛОЖЬ).
В нашем случае ссылка имеет стиль A1, поэтому можно не указывать второй аргумент и сосредоточиться на первом.
Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: CA_Sales, FL_Sales, TX_Sales и так далее. Как видите, во всех именах присутствует «_Sales».
Функция ДВССЫЛ соединяет значение в столбце D и текстовую строку «_Sales», тем самым сообщая ВПР в какой таблице искать. Если в ячейке D3 находится значение «FL», формула выполнит поиск в таблице FL_Sales, если «CA» – в таблице CA_Sales и так далее.
Результат работы функций ВПР и ДВССЫЛ будет следующий:
Если данные расположены в разных книгах Excel, то необходимо добавить имя книги перед именованным диапазоном, например:
Если функция ДВССЫЛ ссылается на другую книгу, то эта книга должна быть открытой. Если же она закрыта, функция сообщит об ошибке #REF! (#ССЫЛ!).
ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.
ВПР: работа с функцией ЕСЛИОШИБКА
Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:
То есть, для первого аргумента Вы вставляете значение, которое нужно проверить на предмет ошибки, а для второго аргумента указываете, что нужно возвратить, если ошибка найдётся.
Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:
Если Вы хотите показать собственное сообщение вместо стандартного сообщения об ошибке функции ВПР, впишите его в кавычках, например, так:
ВПР: работа с функцией ЕОШИБКА
Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:
Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:
На сегодня всё. Надеюсь, этот короткий учебник поможет Вам справиться со всеми возможными ошибками ВПР и заставит Ваши формулы работать правильно.
Для чего используется ВПР?
Эти действия подобны розыску нужного номера в телефонном справочнике. Для этого вы сначала листаете справочник, чтобы обнаружить искомую фамилию и, только обнаружив ее, получаете номер телефона. Или же вы просматриваете прайс-лист, и напротив нужного товара видите его цену.
Таким образом, ВПР нужна, чтобы найти в Экселе определенную информацию в одной таблице и вставить ее в определенное место в другую.
Еще раз сделаем важное замечание: поиск всегда происходит в первом (крайнем левом) столбце. Именно это ограничение и не позволяет считать ее универсальным решением
Но все же возможности применения очень широки
Именно это ограничение и не позволяет считать ее универсальным решением. Но все же возможности применения очень широки.
Синтаксис ВПР позволят нам применять ее для очень большого круга задач, при котором необходимо найти и вернуть определённое значение.
Мы начнем с самого простого и постепенно будем рассматривать все более сложные способы ее использования.
Возможность работы с несколькими условиями
Ещё одним несомненным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтобы найти товар по двум или более характеристикам, необходимо:
- Создать два (или, при необходимости, более) условий для поиска.
- Добавить новый столбец, в который в процессе работы функции добавятся все прочие столбцы, по которым происходит поиск товара.
- В полученном столбце, по вышеописанному алгоритму, вводим уже знакомую нам формулу функции VLOOKUP.
В заключение стоит сказать, что поддержание Excel такой функции, как ВПР, значительно упрощает работу с табличной информацией. Не бойтесь использовать VLOOKUP в работе с огромным количеством данных, ведь как бы они не были оформлены, принцип работы функции всегда один и тот же. Всё, что вам необходимо сделать — правильно определить её аргументы.
Кто и как готовит учеников
Чиновники от образования в один голос повторяют:
Бесполезно спрашивать, есть ли ВПР в 5 классе или любом другом, чтобы заранее пройти все варианты. Лучшая подготовка — просто добросовестно учиться в течение всего года. Это было бы идеально — тогда и ученики, и учителя действительно показали бы реальный уровень знаний. Они сами могли бы понимать, к чему стремиться и что усовершенствовать. Но и школьное руководство, и учителя боятся, что итоги контроля повлияют на рейтинг школы, и всеми правдами и неправдами стремятся его поднять — начинается «натаскивание» на вопросы.
Готовиться к ВПР нужно, но делать это не судорожно зазубривая правила, ответы на вопросы и решения задач. Прежде всего учителю необходимо выявить слабые места в знаниях и сосредоточиться на них. Тем более что свежие демоверсии тестов выкладывают на официальном сайте «Всероссийские проверочные работы». Выбирайте учебные пособия, направленные на систематизацию и обобщение усвоенных понятий. За неделю перед работой уделите время и подробно разберите, как заполнять коды и индексы на бланке ВПР — это отнимает много времени у учеников, особенно у четвероклашек.
Как готовиться самостоятельно
Необходимо научить ребенка не ждать готовых ответов, а мыслить самостоятельно, принимать решения, пусть и ошибочные. Не пугайте школьника контрольными работами — от того, как проходит ВПР в 4 классе, зависит его дальнейшее отношение к подобным тестам. Ребенок остается один на один с заданием и насколько он сумеет успокоиться и сконцентрироваться, настолько он сможет выполнить задачи. Научите детей готовиться самостоятельно — реализованы интересные онлайн-тренажеры, которые пошагово разъясняют решения, показывают ошибки. Дайте задания по какой-либо теме, и пусть школьник сам определит слабые места. Объясните родителям, что будет, если пропустить ВПР — ничего не будет, и не надо отправлять сына или дочь в школу с температурой.
Что такое ВПР?
Прежде всего, функция ВПР позволяет искать определённую информацию в таблицах Excel. Например, если есть список товаров с ценами, то можно найти цену определённого товара.
Сейчас мы найдём при помощи ВПР цену товара Photo frame. Вероятно, Вы и без того видите, что цена товара $9.99, но это простой пример. Поняв, как работает функция ВПР, Вы сможете использовать ее в более сложных таблицах, и тогда она окажется действительно полезной.
Мы вставим формулу в ячейку E2, но Вы можете использовать любую свободную ячейку. Как и с любой формулой в Excel, начинаем со знака равенства (=). Далее вводим имя функции. Аргументы должны быть заключены в круглые скобки, поэтому открываем их. На этом этапе у Вас должно получиться вот что:
Возможные ошибки.
#Н/Д — столбец таблицы, по которому происходит поиск, не крайний левый в диапазон поиска.
#Н/Д — диапазон таблицы, в которой происходит поиск, не закреплен. Нужно использовать либо абсолютные ссылки ($), либо присвоить диапазону Заданное имя.
#Н/Д – функция ВПР не находить точного совпадения в диапазоне поиска по заданному значению поиска.
#Н/Д – возможно необходимо отсортировать диапазон, в котором происходит поиск, по возрастанию.
#ССЫЛКА! – возможно номер столбца, который указан в строке Номер_столбца, указан неверно, и функция не находит данные, которые должна перенести.
Синтаксис и описание функции ВПР в Excel
Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.
Как работает функция ВПР? Функция ВПР в Excel выполняет поиск по вашим спискам данных на основе уникального идентификатора и предоставляет вам часть информации, связанную с этим уникальным идентификатором.
Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).
Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.
Синтаксис функции ВПР выглядит следующим образом:
ВПР(искомое_значение;таблица;номер_столбца;)
Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний — необязательный.
- искомое_значение – это значение для поиска.
Это может быть либо значение (число, дата или текст), либо ссылка на ячейку (ссылка на ячейку, содержащую значение поиска), или значение, возвращаемое некоторой другой функцией Excel. Например:
Поиск числа: =ВПР(40; A2:B15; 2) — формула будет искать число 40.
Поиск текста: =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки»
Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
Поиск значения из другой ячейки: =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.
- таблица — это два или более столбца данных.
Помните, что функция ВПР всегда ищет искомое значение в первом столбце таблицы. Ваш таблица может содержать различные значения, такие как текст, дата, числа или логические значения. Значения нечувствительны к регистру, что означает, что прописные и строчные буквы считаются идентичными.
Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.
- номер_столбца — номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.
Самый левый столбец в указанной таблице равен 1, второй столбец — 2, третий — 3 и т. д.
Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).
4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.
Функция ВПР в Excel на простых примерах
заключается в том, в поле имени вкладке меню «Правка»Номер столбца — его исключительно по столбцам, точное соответствие. Например,среди них самая то можем простопервом левом столбце функций
Обратите внимание ссылке добавляются знаки Попросту говоря, ВПР функции. – «Не найдено». первичные значения, то нужно перепроверить данные. профессионально. Некоторые данные чтобы искать совпадения
(слева под панелью — «Найти» вставить порядковое число, в а не по на рисунке ниже распространенная. В этом изменить первый аргумент:указанного диапазона. В на раздел доллара и она позволяет переставлять значения
Пример 1
У нас есть данныеЕсли «бананы» сменить на никаких символов иОб этом стоит поговорить пользователю нужно держать в одной или вкладок) присваивается ей
данную запись, запустить котором располагается сумма строкам. Для применения формула вернет ошибку,
уроке мы познакомимся=VLOOKUP(«T-shirt»,A2:B16,2,FALSE) этом примере функцияФормулы и функции превращается в абсолютную. из ячейки одной о продажах за «груши», результат будет пробелов между названиями детально. Ни для в голове (номер нескольких таблицах. Так название. поиск. Если программа продаж, то есть функции требуется минимальное поскольку точного соответствия с функцией
Пример 2
=ВПР(«T-shirt»;A2:B16;2;ЛОЖЬ) будет искать внашего самоучителя поВ следующей графе таблицы, в другую январь и февраль. «Найдено»
строк и столбцов кого не секрет, строки или столбца). можно легко найтиДругой вариант — озаглавить не находит его, результат работы формулы. количество столбцов -
не найдено.ВПРили: столбце Microsoft Excel.«Номер столбца» таблицу. Выясним, как Эти таблицы необходимоКогда функция ВПР не не нужно ставить. что абсолютно в В противном случае интересующую информацию, затратив
— подразумевает выделение значит оно отсутствует.Интервальный просмотр. Он вмещает два, максимальное отсутствует.Если четвертый аргумент функции, а также рассмотрим=VLOOKUP(«Gift basket»,A2:B16,2,FALSE)AВПРнам нужно указать пользоваться функцией VLOOKUP сравнить с помощью может найти значение,Также рекомендуется тщательно проверять разных сферах используется
он не сможет минимум времени. диапазона данных, потомФорматы ячеек колонок А значение либо ЛОЖЬ,Функция ВПР производит поискВПР ее возможности на=ВПР(«Gift basket»;A2:B16;2;ЛОЖЬ)значение
работает одинаково во номер того столбца, в Excel. формул ВПР и она выдает сообщение таблицу, чтобы не функция ВПР Excel. найти нужную емуФункция ВПР Excel – переход в меню и С (искомых либо ИСТИНА. Причем
заданного критерия, которыйсодержит значение ИСТИНА простом примере.Следующий пример будет чутьPhoto frame всех версиях Excel, откуда будем выводитьВзглянем, как работает функция
Горизонтальный ВПР в Excel
ГПР. Для наглядности об ошибке #Н/Д. было лишних знаков Инструкция по её информацию. что это такое? «Вставка»- «Имя»- «Присвоить». критериев) различны, например, ЛОЖЬ возвращает только может иметь любой или опущен, тоФункция потруднее, готовы? Представьте,. Иногда Вам придётся она работает даже значения. Этот столбец ВПР на конкретном мы пока поместим Чтобы этого избежать, препинания или пробелов.
применению может показатьсяЧтобы говорить о том, Её также называютДля того чтобы использовать
у одной - точное совпадение, ИСТИНА
формат (текстовый, числовой, крайний левый столбецВПР что в таблице менять столбцы местами, в других электронных располагается в выделенной примере. их на один используем функцию ЕСЛИОШИБКА. Их наличие не сложной, но только как работает функция VLOOKUP в англоязычной
данные, размещенные на текстовый, а у — разрешает поиск денежный, по дате должен быть отсортирован(вертикальный просмотр) ищет
появился третий столбец,
office-guru.ru>
Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ
Далее речь у нас пойдет уже не о функции ВПР, но задачу мы будем решать ту же самую. В качестве критерия поиска нам опять нужно использовать несколько условий.
Существуют, пожалуй, даже более гибкие решения, нежели функция ВПР. Это комбинация функций ИНДЕКС + ПОИСКПОЗ.
Область их применения очень велика, о чем бы также будем рассказывать на сайте mister-office.ru.
А пока вернемся вновь к нашей задаче.
Формула в С4 теперь выглядит так:
И не забываем при вводе нажать CTRL+Shift+Enter! Это формула массива.
Теперь давайте разбираться, как это работает.
Функция ИНДЕКС в нашем случае позволяет извлечь элемент из списка по его порядковому номеру. Список – это диапазон D7:D20, где записаны суммы выручки. А вот порядковый номер, который нужно извлечь, мы определяем при помощи ПОИСКПОЗ.
Синтаксис здесь следующий:
Тип поиска ставим 0, то есть точное совпадение. В нашем случае мы будем искать 1. Далее мы определим массив, в котором будем работать.
Выражение (A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3) позволит создать виртуальную таблицу примерно такого вида:
Как видите, первоначально мы последовательно сравниваем каждое значение с нашим критерием отбора. В столбце А у нас записаны месяцы – сравниваем их с месяцем-критерием из ячейки C1. В случае совпадения получаем ИСТИНА, иначе – ЛОЖЬ. Аналогично последовательно проверяем год и название магазина. А затем просто перемножаем значения. Поскольку логические переменные для Excel – это либо 0, либо 1, то произведение их может быть равно 1 только в том случае, если мы имеем по каждой колонке ИСТИНА (то есть,1). Во всех остальных случаях получаем 0.
Убеждаемся, что цифра 1 встречается только единожды.
При помощи ПОИСКПОЗ определяем, на какой позиции она находится. На какой позиции находится 1, на той же позиции находится в массиве и искомая сумма выручки. В нашем случае это 10-я.
Далее при помощи ИНДЕКС извлекаем 10-ю по счету выручку.
Таким образом мы выбрали значение по нескольким условиям без использования функции ВПР.
Точное или приближенное совпадение в функции ВПР
И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).
Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.
-
Если аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4:
- Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value (искомое_значение).
Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР может вернуть ошибочный результат.
Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты
Пример 1: Поиск точного совпадения при помощи ВПР
Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).
Давайте вновь обратимся к таблице из самого первого примера и выясним, какое животное может передвигаться со скоростью 50 миль в час. Я верю, что вот такая формула не вызовет у Вас затруднений:
Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6. Формула возвращает значение из ячейки B5
Почему? Потому что при поиске точного совпадения функция ВПР использует первое найденное значение, совпадающее с искомым.
Пример 2: Используем ВПР для поиска приблизительного совпадения
Когда Вы используете функцию ВПР для поиска приблизительного совпадения, т.е. когда аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.
Это очень важно, поскольку функция ВПР возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A (#Н/Д)
Вот теперь можно использовать одну из следующих формул:
Как видите, я хочу выяснить, у какого из животных скорость ближе всего к 69 милям в час. И вот какой результат мне вернула функция ВПР:
Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.
Надеюсь, эти примеры пролили немного света на работу с функцией ВПР в Excel, и Вы больше не смотрите на неё, как на чужака. Теперь не помешает кратко повторить ключевые моменты изученного нами материала, чтобы лучше закрепить его в памяти.
Задача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)
В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.
Примечание. Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием. (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке).
Примечание. Никогда не используйте ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) если ключевой столбец не отсортирован по возрастанию, т.к. результат формулы непредсказуем (если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его).
Вставка столбца может «сломать» ваши вычисления.
Если вы вставите дополнительную колонку внутрь таблицы, из которой вы извлекаете данные, то ваши расчеты могут «сломаться». Дело в том, что в результате изменятся порядковые номера некоторых колонок с данными. А вы по-прежнему ссылаетесь на старые номера. В результате извлекать данные вы уже будете не из тех адресов.
Здесь вы видите результаты для отдела, когда код сотрудника и даты стали неверными, когда был вставлен новый столбец D между именем и отделом. Имя же человека по-прежнему было взято правильное, потому что находится слева от места вставки.
Чтобы избежать этой проблемы, вы можете рассчитать и использовать меняющийся индекс столбца, как описано в следующих двух советах.
Функция ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:
Функция ВПР в Excel – Таблица исходных данных
Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.
- Для того чтобы использовать функцию ВПР с несколькими условиями необходимо вставить в начало дополнительный столбец, который будет хранить информацию с названием и типом товара.
Итак на листе «Цены» вставляем столбец и в ячейке А2 вводим следующую формулу:
=B2&C2
При помощи этой формулы мы сцепляем значение столбца «Продукт» и «Тип». Заполняем все ячейки.
Теперь таблица для поиска выглядит следующим образом:
Функция ВПР в Excel – Добавление вспомогательного столбца
- Теперь в ячейке С2 на листе «Продажи» напишем следующую формулу ВПР:
=ВПР(A2&B2;Цены!$A$1:$D$8;4;ЛОЖЬ)
Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:
Функция ВПР в Excel – Пример ВПР с несколькими условиями
Теперь разберем ошибки функции ВПР.
Как расшифровывается ВПР в Excel?
Запомнить назначение формулы несложно: ВПР (VLOOKUP) переводится как сокращение «Вертикальный ПРосмотр» или на английском — “Vertical Look Up”.
Такая расшифровка аббревиатуры ВПР указывает, что она последовательно просматривает содержимое определенного диапазона по вертикали и ищет нужное нам значение. Это одна из самых часто используемых функций отбора данных.
Термин «вертикальный», означает, что показатели в таблице должны быть расположены вертикально, а данные — по строкам. А для горизонтально структурированных данных используется ГПР (HLOOKUP на английском). Как вы, думаю, догадываетесь, ГПР расшифровывается как «горизонтальный просмотр». В общем, то же самое действие, но по горизонтали.
Что такое ВПР и как ею пользоваться?
Итак, в начале нашей работы по преобразованию данных из одной таблицы в другую будет уместным сделать небольшой обзор функции ВПР. Как вы, наверное, уже успели понять, vlookup позволяет переносить данные из одной таблицы в другую, заполняя тем самым необходимые нам ячейки автоматически
Для того чтобы функция ВПР работала корректно, обратите внимание на наличие в заголовках вашей таблицы объединённых ячеек. Если таковые имеются, вам необходимо будет их разбить
Итак, перед нами стоит задача — перенести цены имеющихся товаров в таблицу с их наименованиями и рассчитать общую стоимость каждого товара. Чтобы это сделать, нам предстоит выполнить следующий алгоритм:
Для начала приведите таблицу Excel в необходимый вам вид. Добавьте к заготовленной матрице данных два столбца с названиями «Цена» и «Стоимость». Выберите для ячеек, находящихся в диапазоне новообразовавшихся столбцов, денежный формат.
Теперь активируйте первую ячейку в блоке «Цена» и вызовите «Мастер функций». Сделать это можно, нажав на кнопку «fx», расположенную перед строкой формул, или зажав комбинацию клавиш «Shift+F3». В открывшемся диалоговом окне отыщите категорию «Ссылки и массивы». Здесь нас не интересует ничего кроме функции ВПР. Выберите её и нажмите «ОК». Кстати, следует сказать, что функция VLOOKUP может быть вызвана через вкладку «Формулы», в выпадающем списке которой также находится категория «Ссылки и массивы».
После активации ВПР перед вами откроется окно с перечнем аргументов выбранной вами функции. В поле «Искомое значение» вам потребуется внести диапазон данных, содержащийся в первом столбце таблицы с перечнем поступивших товаров и их количеством. То есть вам нужно сказать Excel, что именно ему следует найти во второй таблице и перенести в первую.
После того как первый аргумент обозначен, можно переходить ко второму. В нашем случае в роли второго аргумента выступает таблица с прайсом. Установите курсор мыши в поле аргумента и переместитесь в лист с перечнем цен. Вручную выделите диапазон с ячейками, находящимися в области столбцов с наименованиями товарной продукции и их ценой
Укажите Excel, какие именно значения необходимо сопоставить функции VLOOKUP.
Для того чтобы Excel не путался и ссылался на нужные вам данные, важно зафиксировать заданную ему ссылку. Чтобы сделать это, выделите в поле «Таблица» требуемые значения и нажмите клавишу F4
Если всё выполнено верно, на экране должен появиться знак $.
Теперь мы переходим к полю аргумента «Номер страницы» и задаём ему значения «2». В этом блоке находятся все данные, которые требуется отправить в нашу рабочую таблицу, а потому важно присвоить «Интервальному просмотру» ложное значение (устанавливаем позицию «ЛОЖЬ»). Это необходимо для того, чтобы функция ВПР работала только с точными значениями и не округляла их.
Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов — «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».
Можно использовать только один критерий.
ВПР может искать только на основе одного критерия, который указывается в качестве значения для поиска в первом столбце (предназначенном для поиска).
Это означает, что вы не можете легко сделать такие вещи, как поиск сотрудника с фамилией «Петров» в «Бухгалтерии» или поиск сотрудника на основе имени и фамилии, если они записаны в отдельных столбиках.
Однако существуют способы преодоления этого ограничения. Одним из простых обходных путей является создание вспомогательного столбца, который объединяет значения из разных колонок для создания новых значений поиска.
Об этом и других способах борьбы с этим ограничением читайте на нашем сайте в специальных инструкциях. Ссылки вы можете найти в конце этого материала.
Используйте ВПР + ПОИСКПОЗ для полностью динамического индекса столбца
Усовершенствовав предыдущий совет, вы можете использовать ПОИСКПОЗ для определения позиции столбца и возврата полностью динамического его индекса (порядкового номера).
Иногда это называется двусторонним поиском, поскольку вы просматриваете как по вертикали, так и по горизонтали.
Пригодиться это может при вычислении продаж для продавца в конкретном месяце или выборе цены на конкретный продукт от конкретного поставщика.
Предположим, что у вас есть продажи за месяц, с разбивкой по менеджерам.
ВПР может легко найти нужного сотрудника, но у нее нет возможности автоматически обрабатывать еще и название месяца. Хитрость заключается в том, чтобы использовать функцию ПОИСКПОЗ вместо статического индекса. С ее помощью мы вычислим номер нужной нам колонки.
При этом давайте еще попробуем использовать именованные диапазоны. Так нам будет проще разобраться.
Итак, A7:D17 присвоим имя «данные». A6:D6 назовем «месяц».
В результете в G8 мы можем записать:
Примечание: подобный двусторонний поиск с помощью ИНДЕКС и ПОИСКПОЗ, который предлагает большую гибкость и лучшую производительность для больших наборов данных, мы рассмотрим в дальнейшем.
