Функция ВПР в Excel для чайников и не только
Функция ВПР в Excel позволяет переупорядочивать данные из одной таблицы в соответствующие ячейки во второй. Его английское название — VLOOKUP.
Очень удобно и часто используется. Потому что сопоставление диапазонов с десятками тысяч имен вручную проблематично.
Как пользоваться функцией ВПР в Excel
Допустим, на склад компании по производству тары и упаковки поступило определенное количество материалов.
Стоимость материалов указана в прайс-листе. Это отдельная таблица.
вам необходимо знать стоимость материалов, поступающих на склад. Для этого нужно подставить цену из второй таблицы в первую. И с помощью обычного умножения мы найдем то, что ищем.
- Придаем первую таблицу в нужном нам виде. Добавим столбцы «Цена» и «Стоимость / Сумма». Установим валютный формат для новых ячеек.
- Выберите первую ячейку в столбце «Цена». В нашем примере D2. Мы вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или путем нажатия комбинации горячих клавиш SHIFT + F3. Найдите функцию ВПР в категории «Ссылки и массивы» и нажмите «ОК». Эту функцию можно вызвать, щелкнув вкладку «Формулы» и выбрав «Ссылки и массивы» из раскрывающегося списка».
- Откроется окно с аргументами функции. В поле «Lookup value» — диапазон данных первого столбца таблицы с количеством полученных материалов. Это значения, которые Excel должен найти во второй таблице.
- Следующий аргумент — «Таблица». Это наш прайс-лист. Ставим курсор в поле темы. Перейти к прайс-листу. Подбираем ассортимент с наименованием материалов и ценами. Мы показываем, каким значениям должна соответствовать функция.
- Чтобы Excel мог напрямую ссылаться на эти данные, ссылка должна быть заблокирована. Выберите значение поля «Таблица» и нажмите F4. Отображается значок $.
- В поле аргумента «Номер столбца» введите цифру «2». Вот данные, которые необходимо ввести в первую таблицу. Отображение диапазона — ЛОЖЬ. Потому что нам нужны точные значения, а не приблизительные.
Щелкните ОК. А затем «умножаем» функцию на весь столбец: берем мышкой правый нижний угол и перетаскиваем его вниз. Получаем желаемый результат.
Теперь узнать стоимость материалов не составит труда: количество * цена.
ВПР связывает две таблицы. При изменении прайс-листа изменится и стоимость материалов, полученных на складе (полученных сегодня). Чтобы этого избежать, используйте «Специальная вставка».
- Выберите столбец с введенными ценами.
- Правая кнопка мыши — «Копировать».
- Не снимая выделения, правой кнопкой мыши — «Специальная вставка».
- Установите флажок «Значения». OK.
Формула в ячейках исчезнет. Только ценности останутся.
Быстрое сравнение двух таблиц с помощью ВПР
Функция помогает сопоставить значения в огромных таблицах. Допустим, цена изменилась. Мы должны сравнить старые цены с новыми ценами.
- В старом прайс-листе создаем столбец «Новая цена».
- Выделите первую ячейку и выберите функцию ВПР. Зададим аргументы (см. Выше). Для нашего примера:. Это означает, что вам нужно взять название материала из диапазона A2: A15, посмотреть его в «Новая цена» в столбце A. Затем взять данные из второго столбца новой цены (новая цена) и заменить их в ячейке C2.
Представленные таким образом данные можно сравнивать. Найдите числовую и процентную разницу.
Функция ВПР в Excel с несколькими условиями
Пока что мы предложили только одно условие для анализа: название материала. На практике часто бывает необходимо сравнивать разные диапазоны с данными и выбирать значение на основе критериев 2, 3 и так далее.
Пример таблицы:
Допустим, нам нужно найти цену, по которой профнастил привез ОАО «Восток». Вам необходимо установить два условия для поиска по названию материала и по поставщику.
Дело осложняется тем, что несколько позиций поступает от одного поставщика.
- Добавьте крайний левый столбец в таблицу (важно!), Объединив «Поставщики» и «Материалы».
- Точно так же комбинируем необходимые критерии запроса:
- Теперь поместим курсор в нужное место и зададим аргументы функции:. Excel найдет нужную цену.
Рассмотрим подробнее формулу:
- Что мы ищем.
- Где мы ищем.
- Какие данные берем.
Функция ВПР и выпадающий список
Допустим, у нас есть некоторые данные в виде раскрывающегося списка. В нашем примере — «Материалы». Вам нужно настроить функцию так, чтобы при выборе имени отображалась цена.
Сначала сделаем выпадающее меню:
- Поместим курсор в ячейку E8, где и будет этот список.
- Переходим во вкладку «Данные». Меню управления данными».
- Выбираем тип данных — «Список». Источник: ряд с названиями материалов.
- Когда мы нажимаем ОК, будет сформирован выпадающий список.
Теперь нужно убедиться, что при выборе определенного материала в столбце цены появлялась соответствующая цифра. Ставим курсор в ячейку E9 (где должна появиться цена).
- Откройте «Мастер функций» и выберите ВПР.
- Первый аргумент — «Подстановочное значение» — ячейка с выпадающим списком. Таблица: ассортимент с названиями материалов и ценами. Столбец соответственно 2. Функция приобрела следующий вид: .
- Жмем ENTER и радуемся результату.
Меняем материал — меняется цена:
Вот как работает раскрывающийся список в Excel с функцией ВПР. Все происходит автоматически. За считанные секунды. Все работает быстро и качественно. Вам просто нужно понять эту функцию.
ВПР (VLOOKUP) с интервальным просмотром
Допустим, у вас есть магазин. Магазин предлагает скидки при оптовых закупках, причем процент скидки зависит от количества купленного товара. Например, при покупке от 5 до 20 штук предоставляется скидка 2%, при покупке от 20 до 50 штук — 6%, а при покупке партии от 50 и более штук — 10%. Как быстро и красиво рассчитать процент скидки при вводе количества купленного товара?
Можно было бы пойти классическим путем и использовать вложенные функции ЕСЛИ, чтобы проверить, находится ли значение ячейки в определенном диапазоне, но это, во-первых, будет очень громоздкой формулой, а во-вторых, поскольку вы не можете вложить ячейки. ЕСЛИ функционируют друг в друге более 7 раз (в новых версиях Excel — 64 раза), поэтому мы можем проверить максимум 7 (64) условий. Что, если есть еще?
Есть другой способ. Просто и красиво.
Нам понадобится таблица скидок следующего вида (диаграмму создавать не нужно — здесь для наглядности):
Для расчета процента скидки используем следующую формулу:
- ВПР — это функция, которая проверяет значение первого столбца таблицы скидок и ищет в нем значение, максимально приближенное к количеству приобретенных товаров (C1)
- B6: C9 — ссылка на таблицу скидок (без заголовка»)
- 2 — порядковый номер столбца в таблице скидок, из которого мы хотим получить значение скидки
- ИСТИНА — здесь похоронена «собака». Если вы укажете FALSE или 0 в качестве последнего аргумента функции VLOOKUP, функция будет искать строгое совпадение в столбце количества (и в случае, показанном на рисунке, она выдаст ошибку, так как в таблица скидок). Но если вместо ЛОЖЬ вы напишете ИСТИНА или 1, функция будет искать не точное значение, а ближайшее меньшее значение и давать вам процент скидки для него. Что требуется!
ВПР (функция ВПР)
ФУНКЦИЯ ВПР используется, когда вам нужно найти элементы в таблице или диапазоне за строкой. Например, вы можете узнать цену на автомобильную деталь по номеру детали или узнать имя сотрудника по его коду.
Совет. Чтобы получить дополнительную помощь по ВПР, посмотрите эти видео на YouTube от экспертов сообщества Excel!
Более простая функция ВПР означает следующее:
= ВПР (условия поиска, номер столбца в диапазоне, содержащем возвращаемое значение, возвращаемое приблизительное или точное совпадение — указано как 1 / истина или 0 / ложь).
Совет: Секрет функции ВПР состоит в том, чтобы организовать ваши данные так, чтобы желаемое значение (Fruit) отображалось слева от возвращаемого значения, которое вы хотите найти (Count).
Используйте функцию ВПР, чтобы найти значение в таблице.
ВПР (lookup_value, table, column_number, [range_view])
= ВПР (LA2; A10: C20; 2; ИСТИНА)
Ценность исследования. Значение, которое вы хотите найти, должно находиться в первом столбце диапазона ячеек, указанного в info_table .
Например, если таблица массивов охватывает ячейки B2: D7, значение lookup_value должно быть в столбце B.
Lookup_value может быть значением или ссылкой на ячейку.
Диапазон ячеек, в которых будет выполняться поиск lookup_value и возвращаемого значения с помощью функции VLOOKUP.
Первый столбец в диапазоне ячеек должен содержать Look up_value. Диапазон ячеек также должен содержать возвращаемое значение для поиска.
Номер столбца (начиная с 1 для крайнего левого столбца массива info_table), который содержит возвращаемое значение.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР — приблизительное или точное.
Приблизительное совпадение: 1 / истина предполагает, что первый столбец таблицы отсортирован по цифрам или алфавиту, затем выполняется поиск ближайшего значения. Это метод по умолчанию, если не указано иное. Например, = ВПР (90; A1: B100; 2; истина).
Точное совпадение: 0 / false выполняет поиск точного значения в первом столбце. Например, = ВПР («Смит»; A1: B100; 2; ложь).
Начало работы
Чтобы создать синтаксис функции ВПР, вам потребуется следующая информация:
Значение, которое вам нужно найти, т.е значение, которое вы ищете.
Подробнее: как скрыть формулы в Excel
Диапазон, в котором найдено искомое значение. Помните, что значение поиска всегда должно быть в первом столбце диапазона, чтобы функция ВПР работала правильно. Например, если значение, которое вы ищете, находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, который содержит возвращаемое значение. Например, если диапазон — B2: D11, B должен быть первым столбцом, C — вторым и т.д.
При желании вы можете указать слово ИСТИНА, если приблизительное совпадение достаточно, или слово ЛОЖЬ, если требуется точное совпадение возвращаемого значения. Если ничего не указано, всегда предполагается, что значение по умолчанию — ИСТИНА, что является приблизительным совпадением.
Теперь объедините все предыдущие аргументы следующим образом:
= ВПР (значение поиска; диапазон значений поиска; номер столбца в диапазоне с возвращаемым значением, приблизительное совпадение (истина) или точное совпадение (ложь)).
Вот несколько примеров использования функции ВПР.
Многоразовый ВПР (VLOOKUP)
У нас есть список заказов с номерами и наименованиями товаров. Например, я хотел бы взять из таблицы по номеру заказа все продукты, которые в нее входят. Как это:
Замечательная функция ВПР поможет в такой ситуации лишь частично, так как умеет извлекать данные только для первого найденного совпадения, т.е даст нам только яблоки. Лучше всего использовать формулу массива для поиска и извлечения всех элементов из таблицы. Вот один из них:
Его следует вводить следующим образом:
- выберите ячейки, в которых должны отображаться результаты (в нашем примере это диапазон D6: D20)
- введите (скопируйте формулу в первую ячейку) диапазона
- нажмите Ctrl + Shift + Enter
Вычитание единицы во фрагменте LINE (B2: B16) -1 выполняется из-за заголовка таблицы. По той же причине, чтобы компенсировать смещение результирующего диапазона от оригинала, вычитается число пять во фрагменте LINE () — 5
Чтобы скрыть ошибку #NUM!, Которая появится в пустых ячейках результирующего диапазона D6: D20, вы можете использовать функции проверки ошибок IF и EON, заменив нашу формулу на более сложную:
= ЕСЛИ (EOSH (ИНДЕКС ($ B $ 2: $ B $ 16; МАЛЫЙ (ЕСЛИ ($ E $ 2 = A2: A16; СТРОКА (B2: B16) -1; «»), СТРОКА () — 5))); «»; ИНДЕКС ($ B $ 2: $ B $ 16, МАЛЫЙ (ЕСЛИ ($ E $ 2 = A2: A16, СТРОКА (B2: B16) -1, «»), СТРОКА () — 5)))
Excelling at Excel вып.1: Преодолеваем ограничения функции ВПР
Многие из вас сталкивались с этой полезной функцией ВПР в MS Excel. Это, безусловно, очень полезный инструмент для агрегирования и преобразования данных. К сожалению, эта функция имеет ряд ограничений. Ниже мы рассмотрим некоторые приемы, которые позволят нам преодолеть эти ограничения.
Первым ограничением функции ВПР является обязательный параметр «column_number» («col_index_num«).
Определяет, какие данные будет возвращать функция ВПР в результате. Использование числа в качестве номера столбца в функции может отрицательно повлиять на удобство дальнейшего использования и изменения формул и вычислений. Например, добавление столбца к желаемому массиву приведет к «поломке» формулы, поскольку параметр «номер_столбца» в ней остается неизменным.
Как видно из приведенного выше примера, вместо данных из столбца «Данные» функция вернула данные из столбца «Класс», поскольку теперь он является вторым по отношению к столбцу «Имя».
также следует отметить, что если несколько элементов данных агрегируются с помощью ВПР, если вы вносите какие-либо изменения в массив данных, вам нужно будет вручную изменить все параметры «номер_столбца».
Чтобы избежать этих длительных манипуляций, вы можете использовать функцию СРАВНЕНИЕ. Определяет позицию обязательного поля в поисковом массиве.
где бы это ни было.
Дополнительным преимуществом использования функции ПОИСКПОЗ в этом случае также будет легко редактируемое желаемое поле данных. Вам просто нужно изменить первый параметр lookup_value функции ПОИСК (в примере это ячейка B1).
Второе ограничение функции ВПР — обязательное требование, чтобы столбец с нужными значениями находился в массиве поиска строго слева от столбцов данных. Если столбец с желаемыми значениями (столбец Name в примере) не является первым слева направо, функция ВПР не вернет никаких данных из столбцов данных (столбцы данных класса в примере).
Чтобы обойти это ограничение, вам нужно использовать функцию СМЕЩЕНИЕ вместо функции ВПР. Эта функция состоит из трех обязательных параметров: ссылка, смещение строки (строки), смещение столбца (столбцы). СМЕЩЕНИЕ возвращает значение ячейки, расположенной в X строках (смещение строки) и Y столбцах (смещение столбца) из указанной ячейки (ссылка). Однако, чтобы получить максимальную отдачу от этой функции, ее следует использовать вместе с двумя функциями СРАВНЕНИЕ вместо параметров смещения строки и смещения столбца.
ВАЖНЫЙ! Не забывайте, что функция ПОИСКПОЗ возвращает порядковый номер ячейки с желаемым значением в массиве, тогда как, как и в функции СМЕЩЕНИЕ, смещение строки и смещение столбца — это количество шагов от начальной ячейки. Те обязательно вычесть 1 из результатов, полученных с помощью функций ПОИСКПОЗ.
Итак, получаем следующую формулу:
СМЕЩЕНИЕ (начальная ячейка; ПОИСКПОЗ (значение поиска; матрица поиска; тип соответствия) — 1; ПОИСКПОЗ (значение поиска; матрица поиска; тип соответствия) — 1)
В результате мы имеем достаточно гибкое решение, не зависящее от положения столбцов в массиве данных. Даже с этим решением мы можем легко переключаться между столбцами данных.
Чтобы сделать эту формулу сверхгибкой и легко изменять не только данные с результатами (класс и данные в примере), но и желаемые данные (имя в примере), вам необходимо вставить другую функцию СМЕЩЕНИЕ в первую функцию ПОИСК (вместо желаемого значения) с функцией СРАВНЕНИЕ внутри. Эта формула независимо определит столбец с желаемыми значениями на основе значения в ячейке A1. Таким образом, модифицированная формула будет выглядеть так:
СМЕЩЕНИЕ (начальная ячейка; ПОИСК (значение поиска; СМЕЩЕНИЕ (начальная ячейка; 0, ПОИСК (значение поиска; массив поиска; тип соответствия) — 1); массив поиска; тип соответствия) — 1; ПОИСКПОЗ (поиск значения, массив поиска, сравнение Тип 1)
Звучит сложно, но при создании сложных гибких решений это очень удобно.