Меню

Функция ВПР VLOOKUP в 6 различных сценариях



Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

31 октября 2014

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

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

В Telegram-канале « Лайфхакер » только лучшие тексты о технологиях, отношениях, спорте, кино и многом другом. Подписывайтесь!

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

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

ВПР — это аббревиатура от в ертикального пр осмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

Функция ВПР имеет четыре параметра:

— искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра ;

— номер столбца в диапазоне, из которого будет возвращено значение;

— это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Источник

Функция ВПР (VLOOKUP) в 6 различных сценариях

Научитесь использовать все прикладные инструменты из функционала MS Excel.

В этой статье мы поговорим, наверное, о самой популярной и удобной функции Excel – ВПР().

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

1. Автономный ВПР

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

Тем, кто знаком с SQL, функция ВПР напоминает запрос SELECT c оператором WHERE, где указывается условие поиска.

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

= ВПР (искомое значение; таблица для поиска; номер столбца, содержащего возвращаемое значение; интервальный просмотр (1/истина или 0/ложь)).

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

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

На рассмотренном простом примере мы видим, как работает функция: в выбранном диапазоне в первом столбце она ищет значение, совпадающее со значением из ячейки F2 (ID = 3). Затем, выбирает значение из второго столбца найденной строки. В результате мы получаем цвет, соответствующий заданному ID.

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

2. Как найти несколько совпадений, используя ВПР

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

И что же делать, если нужно найти значения первого, второго или n-ого соответствия?

Для этой задачи нужно использовать сочетание функций СЧЕТЕСЛИ (COUNTIF) и ВПР (VLOOKUP).

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

Для этого воспользуемся формулой:

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

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

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

2. Создаем формулу для поиска значений в таблице:

В качестве условия поиска указываем склеенное значение двух ячеек (Имя и номер покупки), чтобы получить значение, как в первом столбце таблицы (#Имя).

Функция ЕСНД необходима для того, чтобы у покупателей, совершивших немного покупок в ячейках стоял 0, а не сообщение об ошибке (#Н/Д).

Замечание! Не забывайте закреплять ячейки и диапазон. Иначе при растягивании формулы все значения съедут и дадут неправильные результаты. К сожалению, не всегда это можно быстро заметить.

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

Для решения данной задачи также можно было воспользоваться функциями ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH):

= ИНДЕКС($A$3:$E$12; ПОИСКПОЗ($G$3&H2;$A$3:$A$12;0);5)

3. Как смотреть налево с VLOOKUP

VLOOKUP — одна из самых мощных функций в Excel. Тем не менее, он имеет несколько недостатков. Одним из них является то, что эта функция возвращает значения, находящиеся справа от столбца поиска и не ищет слева. Но для решения этой проблемы есть обходной путь: комбинация функций VLOOKUP и ВЫБОР (CHOOSE).

Обратимся к таблице из первого примера. Допустим, мы знаем название цвета (Синий), но нам нужно найти его код.

Хитрость здесь состоит в том, чтобы виртуально изменить порядок столбцов в таблице, используя функцию CHOOSE. Многие думают, что эта функция работает только с массивами и одиночными индексами. Но это не так. В нашем случае в качестве диапазона для поиска мы укажем следующее выражение: ВЫБОР(<1;2>;C3:C7;B3:B7) или ВЫБОР(<2;1>;B3:B7;C3:C7).

Это выражение выдаст ссылку на два объединенных столбца в порядке Colour; ID. А это именно то, что нам нужно.

В результате мы получим следующую формулу:

Обратите внимание, что в функции ВПР в качестве номера столбца, из которого берется результат мы указываем 2, несмотря на то, в таблице, которая у нас перед глазами, столбец ID является первым. Это все потому, что функция ВЫБОР меняет местами столбцы.

В итоге получаем нужный нам результат:

Также для решения этой задачи можно использовать комбинация функций ИНДЕКС и ПОИСКПОЗ:

=ИНДЕКС(B3:B7; ПОИСКПОЗ(F3;C3:C7;0))

4. Как сделать двусторонний поиск с ВПР

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

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

Но с помощью функции ПОИСКПОЗ можно сделать номер столбца также изменяемым параметром.

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

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

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

=ВПР (искомое значение1; таблица; ПОИСКПОЗ (искомое значение2, столбец, 0), 0)

Читайте также:  Пример расчета экономической добавленной стоимости EVA

Функция ПОИСКПОЗ сопоставляет требуемое значение из указанного диапазона и возвращает ссылку на нужный столбец.

В нашем случае формула примет вид:

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

В результате получим:

В качестве альтернативы можно использовать функции ИНДЕКС и ПОИСКПОЗ:

=ИНДЕКС(B3:H7; ПОИСКПОЗ(K2;B3:B7;0); ПОИСКПОЗ(K3;B2:H2;0))

5. Как найти частичное совпадение в Excel с VLOOKUP

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

В таком случае можно сделать это с помощью подстановочных знаков в VLOOKUP.

Важное примечание: при частичном сопоставлении (4-тый аргумент — интервальный просмотр) всегда должен быть равен 0, чтобы совпадение с подстановочными знаками работало должным образом.

Мы хотим узнать подробности о сотруднике, чье имя начинается с «Ил».

Для решения этой задачи мы воспользуемся классической функцией ВПР, но преобразуем первый аргумент – искомое значение. В этом случае мы предоставляем значение поиска как part_name&”*”. Part_name — это тот кусочек, по которому нужно найти соответствие, а «*» — подстановочный знак. Машина переведет выражение part_name&”*” как «начинается с part_name». В нашем случае part_name = Ил.

В SQL такую задачу можно решить с помощью оператора LIKE “Ил%”

Вот, какая формула у нас получилась:

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

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

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

Если присоединить подстановочный знак (“*”), то функция будет искать ячейку со значением, заканчивающимся на part_name.

Если Вам нужно совпадение «содержит part_name», то нужно использовать два подстановочных знака, например, ”*” & part_name & ”*”.

6. Как использовать ВПР с несколькими критериями

VLOOKUP – мощная функция, без сомнения, но в случае поиска на основе двух или более критериев ВПР не справится.

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

Давайте рассмотрим пример:

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

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

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

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

Чтобы сделать Вспомогательный столбец более читабельным, мы добавили пробел после имени, используя “ ”.

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

Посмотрим, что у нас получилось:

Важно, чтобы функция была настроена на точное совпадение.

Новости

Для подписчиков Microsoft 365, начиная с 2020 июля, доступна новая функция ПРОСМОТРХ (XLOOKUP), которая призвана заменить сразу функции ВПР и ГПР. XLOOKUP отличается от функции ВПР тем, что использует отдельные массивы уточняющих запросов и возвращаемых значений там, где функция ВПР использует один массив таблиц, за которым следует порядковый номер столбца.

Например, старая запись ВПР (В3; B3:D7; 3; ложь) будет соответствовать новой: ПРОСМОТРХ (В3; B2:B7; D2:D7) для следующей таблицы:

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

Заключение

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

EXCEL ACADEMY FULL PACK

Автор: Андрон Алексанян, эксперт SF Education

Источник

Функция ВПР (VLOOKUP) — как работает и почему, примеры, типовые ошибки и чем заменить

Общая информация о ВПР (VLOOKUP)

Функция ВПР — это одна из наиболее популярных функций ссылок и массивов. В англоязычном Excel, а также в Google Sheets, LibreOffice, OpenOffice, эта функция называется VLOOKUP.

Уровень сложности по шкале BRP ADVICE — 3 из 7 .

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

Чтобы разобраться с ВПР (VLOOKUP), сначала надо разобраться с тем, как работают все функции ссылок и массивов.

Как работают функции ссылок и массивов

В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах вы можете ссылаться на ячейку, чтобы получить ее значение или использовать в расчетах. Обычно, в функциях вы указываете ссылки на ячейки вида A1, B17, G34, Z52. Некоторые привыкли работать со ссылками вида R1C1, R17C2, R34C7, R52C26. При этом вы указываете номер строки и букву/номер столбца с начала листа. Указав ячейку, вы даете программе точное указание, что вам нужно значение именно этой ячейки, находящейся на пересечении нужной строки и столбца. То есть выглядит все это примерно так:

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

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

Как указать, где находится внутренняя таблица? Для этого вам понадобится указать ее адрес при помощи стандартных ссылок вида A1 или R1C1. На картинке выше адрес внутренней таблицы — это диапазон J17:O25.

Что же происходит в этой внутренней таблице? Каждая ячейка получает новый адрес, который состоит из номера строки и номера столбца. Именно так: сначала номер строки, затем номер столбца.

Что же делают функции ссылок и массивов? Их конечная цель — получить значение по его внутреннему адресу. При этом функции ссылок и массивов могут как найти нужную строку и нужный столбец сами, так и использовать введенные пользователем значения. Для разных задач используются разные функции. Но в конечном счете получается примерно так, Excel , Google Sheets, LibreOffice, OpenOffice определяют, что вам нужна пятая строка и третий столбец и выдают значение из такой ячейки:

Как работает ВПР (VLOOKUP)

Самый простой способ разобраться с ВПР (VLOOKUP) — это рассмотреть его на примерах. Рассмотрим один пример с точным поиском, второй пример — с приблизительным поиском.

Пример 1 — точный поиск

Ссылка на файл-пример приведена в конце описания этого примера.

Допустим, есть таблица по сотрудникам вашей организации и их окладам. В этой таблице указан каждый сотрудник (его ФИО) и его оклад. Первый столбец этой таблицы — ФИО сотрудника, второй — оклад. То есть ваша таблица выглядит так:

ВПР (VLOOKUP) позволит вам найти оклад, указывая ФИО сотрудника. Конечно, когда у вас в таблице 5 строк и задача разовая, глазами вы найдете нужное значение очень быстро. А если у вас сотрудников 200? Или 5000? ВПР (VLOOKUP) поможет упростить вам жизнь.

Что же нужно сделать, чтобы найти оклад, например, Иванова С.А.?

Надо написать формулу

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

Кстати, в некоторых версиях Excel, вместо «;» должна использоваться «,».

После этого программа вернет вам ответ 21 000.

Что значат все аргументы ВПР (VLOOKUP)?

1. Искомое значение. В нашем примере это «Иванов С.А.» — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.

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

2. Таблица. В нашем примере это C3:D9. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

Читайте также:  Закончите составление таблиц короли франции короли англии

3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае — оклад). Посчитать вам его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый — это ФИО, второй — оклад. Значит ставим цифру 2.

4. Интервальный просмотр. В нашем примере это ЛОЖЬ (FALSE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». То есть ИСТИНА (TRUE) означает, что нам подойдет и Иванов С.А., и Иванов С.И., и Иванова О.П., и может быть кто-то еще (кого из них выберет Excel, смотрите в Примере №2). Сейчас нам нужен именно Иванов С.А., поэтому мы поставили ЛОЖЬ (FALSE).

Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.

Что именно делает ВПР (VLOOKUP) в этом примере?

ВПР (VLOOKUP) просматривает каждую ячейку первого столбца сверху вниз. Он смотрит в первой строке «Газоев И.В.»=»Иванов С.А.»? Видит, что нет. Тогда смотрит вторую: «Ромашкина Б.О.»=Иванов С.А.»? И так далее, пока не доходит до нужной строки. Когда ВПР (VLOOKUP) видит, что «Иванов С.А.»=»Иванов С.А.», он останавливается и запоминает номер строки, в которой это произошло. В нашем примере это 4 строка. Четвертая, потому что подсчет начинается не с начала листа, а с начала внутренней таблицы. И, наконец, ВПР (VLOOKUP) возвращает значение внутренней таблицы, которое находится на пересечении 4 строки (то, что он нашел) и 2 столбца (то, что мы указали ему аргументом). А это значение 21 000. Все, задача решена.

А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим.

Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.

Пример 2 — приблизительный поиск

Ссылка на файл-пример приведена в конце описания этого примера.

Допустим, у вас в компании менеджер по продажам получает премию в зависимости от объема продаж. Если продажи за месяц больше 100, то премия составляет 4% от продаж. Если продажи больше 200, то премия — 5%. Больше 300 — 6%. Больше 400 — 7%. И при любых продажах больше 500 — 8%. При продажах меньше 100, менеджер премию не получает. При помощи ВПР (VLOOKUP) можно быстро узнать, какую премию получит менеджер при его фактических продажах.

Для этого вначале нам потребуется составить таблицу с уровнями плана и ставками премии. Такая таблица будет выглядеть вот так:

Теперь мы можем найти премию Иванова С.А. в зависимости от его фактических результатов.

Основным отличием от предыдущего примера является то, что фактические продажи Иванова могут быть не только ровно 100, 200, 300, 400 или 500. Они могут находиться между указанными нами значениями. Например, фактические продажи Иванова составят 350.

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

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

Не забывайте, что в некоторых версиях Excel, вместо «;» должна использоваться «,».

После этого программа вернет вам ответ 6%. Все что вам остается сделать, это перемножить продажи 350 и ставку премии 6%. Это и будет премия Иванова С.А. при продажах равных 350.

Что значат все аргументы ВПР (VLOOKUP)?

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

Кстати, на этот раз 350 у нас написано без кавычек, потому что 350 — это число, а в кавычки мы берем только текст.

2. Таблица. В нашем примере это C3:D8. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае — ставка премии). Посчитать его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый — это «При продажах больше», второй — «ставка премии». Значит ставим цифру 2.

4. Интервальный просмотр. На этот раз нам нужна ИСТИНА (TRUE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». Мы используем ИСТИНА (TRUE), потому что нам нужно найти не только значения 100, 200 и другие, прямо указанные в таблице, но и все, что находится между ними. То есть ИСТИНА (TRUE) означает, что нам подойдет и 300, и 350, и 380 и так далее, и может быть что-то еще (какое из них выберет Excel, смотрите ниже).

Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.

Что именно делает ВПР (VLOOKUP) в этом примере?

Как и в прошлый раз ВПР (VLOOKUP) последовательно просматривает все ячейки первого столбца нашей таблицы. Но на этот раз он не ищет точного соответствия, а выполняет такие проверки:

100, указанное в таблице, меньше 350? Да, — отвечает ВПР (VLOOKUP), — тогда запоминаем строку 1 и смотрим следующее значение.

200 меньше 350? Да, тогда забываем строку 1, запоминаем 2 и смотрим следующее.

300 меньше 350? Да, тогда забываем строку 2, запоминаем 3 и смотрим следующее.

400 меньше 350? Нет! Тогда возвращаемся к строке 3.

И, наконец, ВПР (VLOOKUP) возвращает значение, указанное на пересечении 3 строки (то, что он нашел) и 2 столбца (то, что мы указали в функции).

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

А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим.

Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.

Какие ошибки мы чаще всего встречаем при работе с ВПР (VLOOKUP)?

1. Это ошибки, связанные с неверной работой с 4 аргументом, с интервальным просмотром. Часто путаются в разнице между ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Запомните такой вопрос: «Мы ищем приблизительно такое же значение, верно?», пусть это будет вам подсказкой.

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

Первый случай, когда вы работаете с ВПР (VLOOKUP) как будто в нем всего 3 аргумента. То есть в формуле вы ставите только две точки с запятой. Например, формула выглядит так:

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

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

Второй случай, когда вы ставите третью точку с запятой, но не указываете сам аргумент ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Например, формула выглядит так:

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

В этом случае ВПР (VLOOKUP) решит, что 4 аргумент равен нулю. А ноль ВПР (VLOOKUP) превратит в ЛОЖЬ (FALSE) и будет искать именно 350 в вашей таблице, а не ближайшее меньшее.

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

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

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

Читайте также:  Температура точки росы таблица при парциальном давлении

3. Иногда неправильно указывают номер столбца (третий аргумент ВПР (VLOOKUP)). Номер столбца не может быть меньше 1 и не может быть больше, чем столбцов во внутренней таблице. Если номер столбца указан неверно, то ВПР (VLOOKUP) возвращает ошибку #ССЫЛКА! (#REF!). Когда вы видите такую ошибку, пересчитайте количество столбцов в таблице, которую вы указали в функции, и убедитесь, что это значение не меньше, чем номер столбца, указанный третьим аргументом функции ВПР (VLOOKUP).

Что происходит, когда ВПР (VLOOKUP) не находит значение?

В случаях, когда Excel , Google Sheets, LibreOffice, OpenOffice не может найти точное совпадение при 4 аргументе ЛОЖЬ (FALSE), ВПР (VLOOKUP) возвращает ошибку #Н/Д ( #N/A). Такую же ошибку ВПР (VLOOKUP) вернет, если вы используете приблизительный поиск и ваша таблица начинается со значения, которое больше искомого.

Как убрать ошибку #Н/Д ( #N/A)

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

Во-вторых, проверьте правильно ли вы указали тип интервального просмотра: ИСТИНА (TRUE) или ЛОЖЬ (FALSE).

В-третьих, если вы все сделали правильно, но во внутренней таблице нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так:

=ЕСЛИОШИБКА(ВПР(«Иванова А.О.»;C3:D9;2);»нет такого сотрудника»)

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

=IFERROR(VLOOKUP(«Иванова А.О.»;C3:D9;2);»нет такого сотрудника») .

В этом случае Excel , Google Sheets, LibreOffice, OpenOffice вместо #Н/Д (#N/A) будут писать, что такого сотрудника нет. Это поможет сделать ваши расчеты более информативными и надежными.

Чем дополнить и заменить ВПР (VLOOKUP)?

Дополнить ВПР (VLOOKUP) можно функцией ЕСЛИОШИБКА (IFERROR) и функцией ПОИСКПОЗ (MATCH). В особо сложных случаях возможно использование в комбинации с функцией СМЕЩ (OFFSET).

Основные варианты замены функции ВПР (VLOOKUP): функция ПРОСМОТР (LOOKUP), комбинация функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), а также в некоторых случаях — функция ГПР (HLOOKUP), функция СУММЕСЛИ (SUMIF).

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

Быстрые ссылки на файлы-примеры:

Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel.

Понравилась статья? Узнайте больше раньше других: заходите на нашу страницу в ВКонтакте и подписывайтесь на новости.

Источник

Функция ВПР в Excel

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

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

Допустим, у нас имеются две таблицы – Заказы и прайс-лист:

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

Примеры использования ВПР:
— подставить из штатного расписания данные о сотруднике (адрес, оклад, телефон) по его ФИО;
— подставить из каталога продукции подробную информацию о товаре по его артикулу;
— подставить из реестра договоров по номеру договора все подробности его заключения (с кем заключен, реквизиты, сумму и т.д.);
— и так далее.

Выделяем первую ячейку (D3), куда будем вводить функцию ВПР, на вкладке «Формулы» нажимаем «Вставить функцию». В категории «Ссылки и массивы» (Lookup and Reference) находим ВПР (VLOOKUP) и жмём ОК

Появится окно ввода аргументов для функции:

Заполняем поля по очереди:
— Искомое значение – наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа (слово «Вода» из ячейки B3).
— Таблица – таблица, из которой берутся искомые значения, т.е. наш прайс-лист. Чтобы при копировании функции вниз на весь столбец ссылка на прайс не сбилась, ее нужно сделать абсолютной, нажав клавишу F4.
— Номер_столбца – порядковый номер (не буква!) столбца в прайс-листе, из которого берём значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно, нам нужна цена из столбца с номером 2.
— Интервальный_просмотр – ЛОЖЬ (0) или ИСТИНА (1):
— ЛОЖЬ КЛАДИ — поиск точного соответствия. Если товар отсутствует в прайс-листе или написан с ошибкой, то функция выдаст ошибку #Н/Д.
— ИСТИНА — поиск приблизительного соответствия. Функция попытается найти товар с максимально похожим наименованием и выдаст цену для этого товара.
Делать это не рекомендуется т.к. функция может подставить значения цены не того предмета, которая должна быть.
После ввода всех аргументов нажимаем ОК и протягиваем введенную функцию на весь столбец.

Функция ВПР выдаёт ошибку #Н/Д если:
1. Включен точный поиск (Интервальный просмотр=0) и искомого наименования нет в Таблице.
2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск, не отсортирована по возрастанию наименований.
3. Форматы ячеек первого столбца и образцовой таблицы отличаются (числовой и текстовый). Можно использовать функции Ч и ТЕКСТ для преобразования форматов данных.
4. Функция не может найти нужного значения, потому что в коде есть пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));$G$3:$H$19;0)
=VLOOKUP(TRIM(CLEAN(B3));$G$3:$H$19;0)
Для подавления сообщения об ошибке #Н/Д в тех случаях, когда функция не может найти точного соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR). В результате любые ошибки, создаваемые ВПР, заменятся на нули или пустые строки:

Итого получаем, 5 минут на заполнение формулы и куча сэкономленного времени и нервов, в которое имитируем бурную деятельность и читаем Пикабу)) Всем здоровья, берегите себя!

Источник

Функция ВПР в Excel

ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований. Является разновидностью поиска, но только вертикального (сверху вниз) – извлекает информацию из таблицы или определенного диапазона по строкам. Работает во всех версиях Excel и даже Google Sheets.

Синтаксис

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Как работает функция?

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

С одним условием

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

  1. Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
  2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.

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

  • Нажать «Enter» – и появится результат поиска.
  • Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

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

    Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

    Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

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

    Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).

  • Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «<>», в которые взята вся формула).
  • Поиск по нескольким столбцам

    Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

      Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;<2;3;4>;ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «<>».

  • Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «<>».
  • Программа сравнивает данные в таблице и, как только определяет точное совпадение, суммирует их.

    Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;<2;3;4>;ЛОЖЬ)).

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

    ВПР помогает сопоставить значения в таблицах.

    Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

    Источник