Как использовать функцию впр (vlookup) в excel

Статья-инструкция: «Функция ВПР в Excel для чайников».

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

Функция ВПР в Excel: инструкция для чайников

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

Таблица в «Excel»

В другой таблице мы имеем тот же самый список с указанием стоимости этих материалов:

Прайс-лист в «Excel»

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

Приступим к делу:

• В первой таблице нам не хватает двух столбцов – «Цена» (то есть стоимость за 1 кв.м) и «Стоимость» (то есть общая стоимость завезенного материала). Добавим эти столбцы. Теперь выделите в столбце «Цена» верхнюю первую ячейку, запустите «Мастер функций» (нажать одновременно «F3» и «Shift»), во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР».

Запустите «Мастер функций», во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР»

• Далее в новом открывшемся окне напротив пункта «Искомое значение» наблюдаем следующие показатели: A1:А15. То есть программа фиксирует диапазон наименований материалов в соответствующем столбе «Материалы». То же самое программа должна показать и во второй таблице.

Наблюдаем следующие показатели: A1:А15

• Теперь разберемся со вторым пунктом – «Таблица» (вторая таблица со стоимостью материалов). Нажмите на этот пункт, затем выделите во второй таблице диапазон наименований материалов вместе с ценами. В итоге, результат должен быть следующим.

Нажмите на пункт «Таблица»

• Далее выделите результаты в пункте «Таблица» и нажмите на «F4», после чего в них появится новый символ «$» — таким образом программа будет ссылаться на показатели в «Таблица».

Выделите результаты в пункте «Таблица» и нажмите на «F4»

• Далее переходим к третьему пункту – «Номер столбца». Укажите здесь «2». В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ». После всех этих манипуляций нажмите на «ОК».

В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ»

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

Курсором мышки нажмите на нижний правый угол таблицы и потяните вниз

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

• Выделите все показатели в столбце «Цена», нажмите на нем правой кнопкой мышки и далее – на «Копировать».
• Снова нажмите правой кнопкой мышки по уже выделенным ценам и затем – на «Специальная вставка».
• В открывшемся окошке поставьте галку, как показано на скриншоте, и нажмите на «ОК»

Поставьте галку на «Значения»

Сравниваем две таблицы при помощи функции ВПР в Excel

Предположим, что прайс-лист изменился, и нам необходимо сравнить две таблицы – новые и старые цены:

Прайс-лист

Для этого воспользуемся соответствующей функцией в «Excel»:

• В старой таблице добавьте столбец «Новая цена»

Добавляем столбец «Новая цена»

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

в пункте «Таблица» получаем следующие результаты

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

Полученная таблица

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

Выше мы работали с одним условием – наименованием материалов. Но в реальности обстоятельства могут сложиться иначе. Мы можем получить таблицу с двумя и более условиями, например, как с наименованием материалов, так и с наименованием их поставщиков:

Таблица с наименованиями поставщиков и материалов

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

Попробуем решить эту задачу:

• Добавьте в таблицу левый крайний столбец и объедините два столбца – «Материалы» и «Поставщики».

Объедините два столбца – «Материалы» и «Поставщики»

• Точно так же объедините искомые критерии запроса

Объедините искомые критерии запроса

• Снова в пункте «Таблица» задайте соответствующие показатели

В пункте «Таблица» задайте такие показатели

• На скриншоте выше мы видим следующую формулу (1 – объект поиска; 2 – место поиска; 3 – взятые нами данные).

1 – объект поиска; 2 – место поиска; 3 – взятые нами данные

Работаем с выпадающим списком

Предположим, что у нас определенные данные (например, «Материалы») показываются в раскрывающимся списке. Наша задача, чтобы при этом было доступно видеть еще и стоимость.

Для этого предпримем следующие шаги:

• Нажимаем мышкой на ячейку «E8» и во вкладке «Данные» выбираем «Проверку данных»

Нажмите на «Проверка данных»

• Далее во вкладке «Параметры» выберите «Список» и нажмите на «ОК»

Выберите «Список» и нажмите на «ОК»

• Теперь в выпадающем списке мы видим пункт «Цена»

Получаем пункт «Цена»

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

В «Таблица» должны быть следующе показатели

• Нажмите на «Ок» и получите результат

Получаем результаты

Видео: Функция ВПР в Excel от А до Я

Источник: https://mobilkoy.ru/kak-polzovatsya-funkciej-vpr-v-excel-ponyatnaya-instrukciya-dlya-chajnikov-video-sravnenie-dvuh-tablic-vypadayushhij-spisok-primery-formula-funkcii-vpr-v-excel-primery-neskolko-uslovij-rasshi

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

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

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

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

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

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

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

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

У функции ВПР 4 аргумента. Первым аргументом, который мы подставим в формулу, является то самое искомое значение, цену для которого нужно найти во второй таблице «Прайс-лист». После написания названия функции и добавления открывающей скобки, щелкаем по искомой ячейке и добавляем ее аргументом. В моем случае это ячейка «E4» со значением «Яблоки». Далее ставим разделитель — точка с запятой.

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

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

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

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

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

Последний параметр называется «Интервальный просмотр» — это логический аргумент и может принимать только 2 значения: 0 или 1 (включено/выключено). Данным значением мы определяем, точно, цифра 0, или приблизительно, цифра 1, мы ищем наименование в прайс-листе.

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

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

В колонку «Итоговая стоимость» прописываем простую формулу умножения веса партии на цену за кг.

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

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

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

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

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

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

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

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

Вернемся к нашей таблице с заказами и попробуем определить размер партии по ее весу. У нас будет таблица с заказами и таблица с параметрами партии.

Дополнительно у нас появляется критерий «от и до», который говорит о размере партии. В примере мы будет отталкиваться от ее веса. Например, если Excel показать вес партии 15 кг, то он будет понимать, что это мелкая партия.

Давайте разбираться как это работает. Начинаем писать функция ВПР, но в конце формулы ставим не 0, а 1.

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

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

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

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

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

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

Не забудьте поделиться ссылкой на статью ⇒

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

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

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

В этом уроке расскажу как закрепить строку или столбец в Excel. Закрепленные области будут всегда видны на экране при вертикальной или горизонтальной прокрутке.

Сейчас приведу пример. Допустим, у вас есть ячейка «A», в которой находится имя, фамилия и отчество.

Вам необходимо сделать так, чтобы в первой ячейке «A» была только фамилия, в ячейке «B» — имя, ну и в ячейке «C» отчество.

Источник: https://4upc.ru/materials/show/funkciya-vpr-v-excel

Функция ВПР в Excel для чайников и не только

Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.

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

Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.

Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

Алгоритм действий:

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».



3. Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.



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



5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.



6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

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

Теперь найти стоимость материалов не составит труда: количество * цену.

Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

1. Выделяем столбец со вставленными ценами.
2. Правая кнопка мыши – «Копировать».
3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.



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

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

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

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

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

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

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

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

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

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».
3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
4. Когда нажмем ОК – сформируется выпадающий список.

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

Изменяем материал – меняется цена:

Скачать пример функции ВПР в Excel

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

Источник: https://exceltable.com/funkcii-excel/funkciya-vpr-v-excel

Как работает функция ВПР

     Здравствуй уважаемый читатель!

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

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

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

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

Функция ВПР в Excel имеет следующий синтаксис:

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

• искомое_значение – это именно то значение, которое нам нужно искать, а это может быть любое значение: число, дата, текст, ссылка на ячейку, которая содержит нужное значение или значение, получаемое другой формулой;
• таблица – это два и больше столбика с разнообразными данными, кстати, регистр символов в поиске, функцией не учитывается;
• номер_столбца – это номер столбика в указанном диапазоне, из которого будет получено значение, которое находится в найденной строке;
• интервальный_просмотр – этот параметр определяет, что же именно будем искать, для точного совпадения аргумент будет равен «ЛОЖЬ» (FALSE) или приблизительное совпадение, аргумент станет равным ИСТИНА (TRUE). Этот параметр не является обязательным, но тем не менее он важен. В примерах, далее, я продемонстрирую, как создать формулы для точного и приблизительного совпадения.

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

      =ВПР(«GM»;$A$5:$B$10;2)

     Формула ищет текст «GM», в столбике А на текущем листе.     Совет! При использовании аргумента «таблица», желательно использовать, такой вариант, как абсолютные ссылки (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.

•      Рассмотрим пример поиска значений, как работает функция ВПР в другой рабочей книге:
•      =ВПР(”GM”;[Путь к файлу]База!А2:В10;2)
•     Как видите ничего сложного, просто усложнился аргумент «таблица», теперь к нему прикреплена ссылка на файл с его именем.

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

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

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

• «?» (знак вопроса) – позволит заменить один любой символ;
• «*» (звёздочка) – заменит любое количество и последовательность символов.

=ВПР(“A*”;$A$2:$B$10;1;ЛОЖЬ)

    Совет! Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ».     Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:

• если аргумент «интервальный просмотр» равен «ЛОЖЬ», в таком случае формула ищет точное совпадение с аргументом «искомое значение». Если формула встретит два и более значения, отвечающих аргументу «искомое значение», то будет выбрано первое из списка, в случае, когда совпадение не найдены, формула вернет ошибку #Н/Д;
• если же аргумент «интервальный просмотр» имеет значение «ИСТИНА» формула будет искать приблизительное совпадение, точнее будет, что функция сначала поищет точное совпадение, а уже потом, не найдя его, выберет приблизительное.

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

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

     =ВПР(200;$A$2:$B$15;2;ЛОЖЬ)

    Несмотря на то, что значений 200 у нас несколько, получили только одно, так как при точном совпадении функции ВПР, система использует только первое значение которое было найдено в указанном диапазоне.    Теперь испытаем, работу функции ВПР для поиска приблизительного совпадения значений. Поищем, какой автомобиль ездит со скоростью 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр» равняется «ИСТИНА» — вы выполняете сортировку вашего диапазона значений по первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее наибольшее значение от заданного условия, а после поиск прекращается. Если же вы не последуете совету по сортировке, итогом будет сообщение об ошибке #Н/Д или другие странные результаты.

     Для поиска используем ВПР следующего вида:

    =ВПР(260;$A$2:$B$15;2;ИСТИНА)

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

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

     А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

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

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

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/kak-rabotaet-funkciya-vpr/

Функция ВПР в Excel (VLOOKUP) на примере

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

Используйте пошаговые руководства:

Функция ВПР в Excel наиболее понятна, если разобрать ее на примере.

Менеджмент условной компании «Полет»* решил изменить схему начисления премий работникам. Причин тому было несколько: во-первых, по абсолютной величине средней премии сотрудника отдела продаж компания занимала второе место в своем рыночном сегменте.

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

При этом достичь ключевых показателей эффективности (КПЭ) на 100% сотрудникам удавалось довольно редко.

Новый способ расчета премий формировался с учетом факторов, которые предложила служба по управлению персоналом. Решение о переходе к новой системе определения бонусов приняли в конце I квартала, поэтому корректировки касались данных уже II квартала. Изменения проводились в Excel по нескольким причинам:

• исходные данные службы по управлению персоналом в части правил расчета премий хранились в Excel;
• данные необходимо было свести в единую таблицу по вариантам премий;
• значения премий во всем списке сотрудников удобнее заменить сначала в Excel и затем загрузить в ИТ-систему («1С: Зарплата и Управление персоналом 8»).

Функция ВПР в Excel и ее применение. Исходные данные

Сотрудникам финансового отдела предоставили набор таблиц Excel, в одной из которых – список данных с набором фактических параметров для каждого элемента (строка), полученных из ИТ-системы, в остальных – исходные данные по взаимосвязям групп этих параметров между собой (см. рис. 1 и 2).

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

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

Рисунок 1. Лист «Исходные данные»

Рисунок 2. Лист «Список вариантов премий»

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

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

Данных сотрудников подразделяют по ряду параметров:

• должность (консультант, специалист первого или второго уровня, специалист по распределению консультантов и др.);
• уровень специалиста (консультант-продавец, старший консультант, консультант-эксперт и др.);
• расположение точки продаж (Москва, Санкт-Петербург, другие регионы);
• подразделение, обслуживающее куплю-продажу товаров торговой марки (в нашем условном примере – «Марка 1», «Марка 2» и т.д.);
• уровень заработной платы;
• набор КПЭ для расчета размера премии к выплате.

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

Сумма для каждого сотрудника определялась (в зависимости от результатов) по условной шкале: минимальная (min), средняя (100%) и максимальная (max), детали расчета премий опустим.

Финансистам нужно было подобрать для каждого сотрудника в зависимости от занимаемой им должности набор исходных размеров премий (min, 100%, max), а также добавить эти значения в таблицу с фактическими данными.

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

Поэтому мы ввели новый параметр «Уровень премии» – соответствующий столбец включили в листы «Список вариантов премий» и «Готовое решение».

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

Если же возникнет необходимость пересмотреть размер премии только одному работнику, достаточно заменить значение в поле «Уровень премии» в карточке сотрудника.

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

Функция ВПР в Excel в итоговом варианте

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

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

Чтобы обойти это ограничение, преобразуем параметры «Департамент», «Должность», «Регион», «Зарплата» в один. Сделать это можно либо с помощью функции «Сцепить» (=СЦЕПИТЬ), либо использовать символ амперсанд (&).

Рисунок 3. Технический столбец с объединенным аргументом на листе «Исходные данные»

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

После этого можно использовать ВПР и решить поставленную задачу.

Добавим на лист «Исходные данные» столбец «Уровень премии», в котором как один из вариантов будет использоваться формула =ЕСЛИ(ИЛИ (L11=$L$9;L11=$L$33);L11;ВПР(H11;'Список вариантов премий'!$B$13:$K$699;2;0))».

Рисунок 4. Лист «Готовое решение»

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

После использования функции ВПР в Excel финансистам компании «Полет» оставалось лишь подготовить техническое задание штатному программисту, который должен был создать новый справочник в базе данных, загрузить туда варианты размеров премий и вставить столбцы с этими данными (min, «100%», max) для расчетов выплачиваемых сумм. Затем в карточки работников добавили поле, содержащее уровень премии каждого из них со ссылкой на новый справочник.

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

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

Источник: https://www.fd.ru/articles/39143-netraditsionnyy-podhod-k-funktsii-vpr-v-excel-

Использование функции ВПР (VLOOKUP) для подстановки значений

60654 10.11.2012 Скачать пример

Кому лень или нет времени читать — смотрим видео. Подробности и нюансы — в тексте ниже.

Постановка задачи

Итак, имеем две таблицы — таблицу заказов и прайс-лист:

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

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP).

Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

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

Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function). В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

• Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
• Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4, чтобы закрепить ссылку знаками доллара, т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
• Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
• Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
• Если введено значение или ЛОЖЬ (FALSE), то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
• Если введено значение 1 или ИСТИНА (TRUE), то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст — например, при расчете Ступенчатых скидок.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

Ошибки #Н/Д и их подавление

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

• Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
• Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
• Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так: =ВПР(ТЕКСТ(B3);прайс;0)
• Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления: =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0) =VLOOKUP(TRIM(CLEAN(B3));прайс;0)

• Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR). Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:
• =ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)
• =IFERROR(VLOOKUP(B3;прайс;2;0);0)

P.S

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

Ссылки по теме 

Источник: https://www.planetaexcel.ru/techniques/2/106/