Функция ВПР в Excel позволяет переупорядочивать данные из одной таблицы в соответствующие ячейки во второй. Его английское название — VLOOKUP.
Очень удобно и часто используется. Потому что сопоставление диапазонов с десятками тысяч имен вручную проблематично.
Допустим, на склад компании по производству тары и упаковки поступило определенное количество материалов.
Стоимость материалов указана в прайс-листе. Это отдельная таблица.
вам необходимо знать стоимость материалов, поступающих на склад. Для этого нужно подставить цену из второй таблицы в первую. И с помощью обычного умножения мы найдем то, что ищем.
Щелкните ОК. А затем «умножаем» функцию на весь столбец: берем мышкой правый нижний угол и перетаскиваем его вниз. Получаем желаемый результат.
Теперь узнать стоимость материалов не составит труда: количество * цена.
ВПР связывает две таблицы. При изменении прайс-листа изменится и стоимость материалов, полученных на складе (полученных сегодня). Чтобы этого избежать, используйте «Специальная вставка».
Формула в ячейках исчезнет. Только ценности останутся.
Функция помогает сопоставить значения в огромных таблицах. Допустим, цена изменилась. Мы должны сравнить старые цены с новыми ценами.
Представленные таким образом данные можно сравнивать. Найдите числовую и процентную разницу.
Пока что мы предложили только одно условие для анализа: название материала. На практике часто бывает необходимо сравнивать разные диапазоны с данными и выбирать значение на основе критериев 2, 3 и так далее.
Пример таблицы:
Допустим, нам нужно найти цену, по которой профнастил привез ОАО «Восток». Вам необходимо установить два условия для поиска по названию материала и по поставщику.
Дело осложняется тем, что несколько позиций поступает от одного поставщика.
Рассмотрим подробнее формулу:
Допустим, у нас есть некоторые данные в виде раскрывающегося списка. В нашем примере — «Материалы». Вам нужно настроить функцию так, чтобы при выборе имени отображалась цена.
Сначала сделаем выпадающее меню:
Теперь нужно убедиться, что при выборе определенного материала в столбце цены появлялась соответствующая цифра. Ставим курсор в ячейку E9 (где должна появиться цена).
Меняем материал — меняется цена:
Вот как работает раскрывающийся список в Excel с функцией ВПР. Все происходит автоматически. За считанные секунды. Все работает быстро и качественно. Вам просто нужно понять эту функцию.
Допустим, у вас есть магазин. Магазин предлагает скидки при оптовых закупках, причем процент скидки зависит от количества купленного товара. Например, при покупке от 5 до 20 штук предоставляется скидка 2%, при покупке от 20 до 50 штук — 6%, а при покупке партии от 50 и более штук — 10%. Как быстро и красиво рассчитать процент скидки при вводе количества купленного товара?
Можно было бы пойти классическим путем и использовать вложенные функции ЕСЛИ, чтобы проверить, находится ли значение ячейки в определенном диапазоне, но это, во-первых, будет очень громоздкой формулой, а во-вторых, поскольку вы не можете вложить ячейки. ЕСЛИ функционируют друг в друге более 7 раз (в новых версиях Excel — 64 раза), поэтому мы можем проверить максимум 7 (64) условий. Что, если есть еще?
Есть другой способ. Просто и красиво.
Нам понадобится таблица скидок следующего вида (диаграмму создавать не нужно — здесь для наглядности):
Для расчета процента скидки используем следующую формулу:
ФУНКЦИЯ ВПР используется, когда вам нужно найти элементы в таблице или диапазоне за строкой. Например, вы можете узнать цену на автомобильную деталь по номеру детали или узнать имя сотрудника по его коду.
Совет. Чтобы получить дополнительную помощь по ВПР, посмотрите эти видео на 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 — вторым и т.д.
При желании вы можете указать слово ИСТИНА, если приблизительное совпадение достаточно, или слово ЛОЖЬ, если требуется точное совпадение возвращаемого значения. Если ничего не указано, всегда предполагается, что значение по умолчанию — ИСТИНА, что является приблизительным совпадением.
Теперь объедините все предыдущие аргументы следующим образом:
= ВПР (значение поиска; диапазон значений поиска; номер столбца в диапазоне с возвращаемым значением, приблизительное совпадение (истина) или точное совпадение (ложь)).
Вот несколько примеров использования функции ВПР.
У нас есть список заказов с номерами и наименованиями товаров. Например, я хотел бы взять из таблицы по номеру заказа все продукты, которые в нее входят. Как это:
Замечательная функция ВПР поможет в такой ситуации лишь частично, так как умеет извлекать данные только для первого найденного совпадения, т.е даст нам только яблоки. Лучше всего использовать формулу массива для поиска и извлечения всех элементов из таблицы. Вот один из них:
Его следует вводить следующим образом:
Вычитание единицы во фрагменте 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)))
Многие из вас сталкивались с этой полезной функцией ВПР в MS Excel. Это, безусловно, очень полезный инструмент для агрегирования и преобразования данных. К сожалению, эта функция имеет ряд ограничений. Ниже мы рассмотрим некоторые приемы, которые позволят нам преодолеть эти ограничения.
Первым ограничением функции ВПР является обязательный параметр «column_number» («col_index_num«).
Определяет, какие данные будет возвращать функция ВПР в результате. Использование числа в качестве номера столбца в функции может отрицательно повлиять на удобство дальнейшего использования и изменения формул и вычислений. Например, добавление столбца к желаемому массиву приведет к «поломке» формулы, поскольку параметр «номер_столбца» в ней остается неизменным.
Как видно из приведенного выше примера, вместо данных из столбца «Данные» функция вернула данные из столбца «Класс», поскольку теперь он является вторым по отношению к столбцу «Имя».
также следует отметить, что если несколько элементов данных агрегируются с помощью ВПР, если вы вносите какие-либо изменения в массив данных, вам нужно будет вручную изменить все параметры «номер_столбца».
Чтобы избежать этих длительных манипуляций, вы можете использовать функцию СРАВНЕНИЕ. Определяет позицию обязательного поля в поисковом массиве.
где бы это ни было.
Дополнительным преимуществом использования функции ПОИСКПОЗ в этом случае также будет легко редактируемое желаемое поле данных. Вам просто нужно изменить первый параметр lookup_value функции ПОИСК (в примере это ячейка B1).
Второе ограничение функции ВПР — обязательное требование, чтобы столбец с нужными значениями находился в массиве поиска строго слева от столбцов данных. Если столбец с желаемыми значениями (столбец Name в примере) не является первым слева направо, функция ВПР не вернет никаких данных из столбцов данных (столбцы данных класса в примере).
Чтобы обойти это ограничение, вам нужно использовать функцию СМЕЩЕНИЕ вместо функции ВПР. Эта функция состоит из трех обязательных параметров: ссылка, смещение строки (строки), смещение столбца (столбцы). СМЕЩЕНИЕ возвращает значение ячейки, расположенной в X строках (смещение строки) и Y столбцах (смещение столбца) из указанной ячейки (ссылка). Однако, чтобы получить максимальную отдачу от этой функции, ее следует использовать вместе с двумя функциями СРАВНЕНИЕ вместо параметров смещения строки и смещения столбца.
ВАЖНЫЙ! Не забывайте, что функция ПОИСКПОЗ возвращает порядковый номер ячейки с желаемым значением в массиве, тогда как, как и в функции СМЕЩЕНИЕ, смещение строки и смещение столбца — это количество шагов от начальной ячейки. Те обязательно вычесть 1 из результатов, полученных с помощью функций ПОИСКПОЗ.
Итак, получаем следующую формулу:
СМЕЩЕНИЕ (начальная ячейка; ПОИСКПОЗ (значение поиска; матрица поиска; тип соответствия) — 1; ПОИСКПОЗ (значение поиска; матрица поиска; тип соответствия) — 1)
В результате мы имеем достаточно гибкое решение, не зависящее от положения столбцов в массиве данных. Даже с этим решением мы можем легко переключаться между столбцами данных.
Чтобы сделать эту формулу сверхгибкой и легко изменять не только данные с результатами (класс и данные в примере), но и желаемые данные (имя в примере), вам необходимо вставить другую функцию СМЕЩЕНИЕ в первую функцию ПОИСК (вместо желаемого значения) с функцией СРАВНЕНИЕ внутри. Эта формула независимо определит столбец с желаемыми значениями на основе значения в ячейке A1. Таким образом, модифицированная формула будет выглядеть так:
СМЕЩЕНИЕ (начальная ячейка; ПОИСК (значение поиска; СМЕЩЕНИЕ (начальная ячейка; 0, ПОИСК (значение поиска; массив поиска; тип соответствия) — 1); массив поиска; тип соответствия) — 1; ПОИСКПОЗ (поиск значения, массив поиска, сравнение Тип 1)
Звучит сложно, но при создании сложных гибких решений это очень удобно.
