Remontnouta.ru

ПК Ремонт техники
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция ВПР() в EXCEL

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

Искомое_значение — это значение, которое Вы пытаетесь найти в столбце с данными. Искомое_значение может быть числом или текстом, но чаще всего ищут именно число. Искомое значение должно находиться в первом (самом левом) столбце диапазона ячеек, указанного в таблице .

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

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

Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение ближайшее к критерию или совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с критерием). Значение ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по возрастанию. Это способ используется в функции по умолчанию, если не указан другой.

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

Использование дополнительной колонки

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

Рассмотрим классический пример. У нас имеется таблица с продажами по месяцам и городам. И нам необходимо определить значение продаж, соответствующее двум условиям: месяц – Февраль и город – Самара.

Исходная-таблица

Использование функции ВПР в классическом виде нам не поможет, так как она сможет вернуть значение, соответствующее только одному условию. Из положения нам поможет выйти дополнительный столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Теперь мы сможем использовать значения столбца А, чтобы вернуть необходимое значение. Прописываем в ячейке G3 формулу:

Данная формула объединяет два условия ячеек G1 и G2 в одну строку и просматривает его в столбце А. После того, как нужное условие было обнаружено, формула возвращает значение с четвертого столбца таблицы A1:D13, т.е. столбца Продажи.

Дополнительный-столбец

Пример 2

Рассмотрим еще один пример. На рисунке ниже представлены те же 10 фамилий, что и раньше, вот только номера идут с пропусками.

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

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

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

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

Читайте так же:
Можно ли запаролить жесткий диск

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

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

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

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

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

Для тех, кто любит создавать не вертикальные, а горизонтальные таблицы, в Excel существует аналог ВПР, но для горизонтального поиска.

Пример использования функции

Пример таблицы

У нас есть две таблицы — товары и накладная. Необходимо, используя формулу, заполнить накладную. Используем для этого следующую формулу:

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

В нашем случае формула приобретает следующее значение:

ВПР(A3;F3:H13;3;0)
Где,

  • A3 – искомое значение, это надпись «деревянные кольца», именно ее мы ищем в таблице «Товары»;
  • F3:H13 – границы таблицы в которой мы ищем данные;
  • 3 – номер столбца;
  • 0 – параметр, отвечающий за точность совпадения. Для получения точного совпадения ставим значение 0.

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

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

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

Если у Вас не работает функция ВПР, то значит скорее всего Вы неверно задали её параметры. Это бывает чаще всего, а не «глючит Excel».

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

Читайте так же:
Можно ли ставить желтые лампочки в габариты

Рассмотрим ВПР в подробностях.

По ходу статьи мы:

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

1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;

Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе 1 и 2 с помощью Forecast4AC PRO (Как самостоятельно рассчитать коэффициенты сезонности к 3-м месяцам можете прочитать в статье «Расчет прогноза по методу скользящей средней!»)

Для этого установим курсор в начало продаж по товарным группам:

впр +в excel

Выберите в настройках «Сезонность» «к 3-м месяцам»:

функция впр

Нажимаем кнопку «Рассчитать». Получаем в продолжении ряда коэффициенты сезонности к 3-м месяцам:

функция впр +в excel

Копируем сезонность на отдельный лист «к 3-м» получаем табличку, в которой в первом столбце названия товарных групп, а в столбцах со 2-го по 13-й — коэффициенты сезонности для 1 — 12 месяцев:

впр пример

2. Рассчитаем скользящую среднюю к 3-м месяцам по позициям.

Используем стандартную функцию =срзнач(продажи за 3 последних месяца):

впр +в excel пример

Протянем среднюю на все позиции на 24 месяца вперед:

функция впр примеры

3. Скорректируем скользящую среднюю сезонностью по группе и разберем ВПР.

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

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

1. В искомое значение передаем название товарной группы и фиксируем столбец:

=СРЗНАЧ(BD3:BF3)*впр($C3 ( передаем название товарной группы и фиксируем столбец с помощью значка «$» );’к 3-м’!$A$3:$M$4;данные!BG$2+1;0)

Подробнее о фиксировании ссылок читайте в статье «Как зафиксировать ссылку в Excel».

впр формула

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

=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4( передаем таблицу с товарными группами и фиксируем таблицу с помощью значка «$» );данные!BG$2+1;0)

функция впр +в excel примеры

В первом столбце таблицы содержатся искомые значения — названия товарных групп. Фиксируем таблицу, чтобы формула имела такой вид ‘к 3-м’!$A$3:$M$4 и ссылки не поехали, когда мы будем протягивать формулу.

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

=СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1( передаем номер столбца в котором содержится искомый коэффициент сезонности для соответствующего месяца и фиксируем строку с номерами столбцов месяца с помощью значка «$» );0)

эксель впр

впр +в экселе

Т.к. номер столбца в таблице с сезонностью для первого месяца будет вторым, то прибавляем «1»

=СРЗНАЧ(BD3:BF3)*впр( $ C3;’к 3-м’!$A$3:$M$4;данные!BG$2 +1 ( прибавляем 1, т.к. номер столбца в таблице с сезонностью для первого месяца 2, в первом столбце название товарных групп );0)

Читайте так же:
В какую сторону закрывается водопроводный кран

формула впр +в excel

4. =СРЗНАЧ(BD3:BF3)*впр($C3;’к 3-м’!$A$3:$M$4;данные!BG$2+1;0 ( ищем точное соответствие названий товарных групп ))

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

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

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

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Синтаксис

Аргументы

Если значение аргумента номер_столбца :

  • меньше 1, функция ВПР возвращает значение ошибки #ЗНАЧ!;
  • больше, чем число столбцов в аргументе таблица, функция ВПР возвращает значение ошибки #ССЫЛ!.

Если аргумент интервальный_просмотр имеет значение ИСТИНА или опущен, то возвращается точное или приблизительное совпадение. Если точное совпадение не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение.

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

Если аргумент интервальный_просмотр имеет значение ЛОЖЬ , функция ВПР ищет только точное совпадение. Если в первом столбце аргумента таблица имеется несколько значений, соответствующих аргументу искомое_значение, используется первое найденное значение. Если точное совпадение не найдено, возвращается значение ошибки #Н/Д.

Замечания

  • При поиске в большом массиве, особенно, если на листе Excel много формул, у пользователя могут возникать проблемы с производительностью. Первое, что приходит на ум — это произвести апгрейд компьютера. Да, функция ВПР считается достаточно медленной, однако, мало кто знает, что ее скорость работы можно увеличить десятикратно, если использовать поставить аргумент равным ИСТИНА (или 1). Да, здесь есть нюанс в виде обязательной сортировки, поэтому такое ускорение будет работать не всегда. Кстати скорость увеличивается из-за сортировки, в этом случае нет необходимости сравнивать каждое значение из столбца.
  • Функция ВПР осуществляет поиск всегда в самом первом (левом) столбце диапазона. Данное ограничение можно обойти, там где возможно, конечно, уменьшив аргумент таблица , чтобы искомый столбец стал самым первым
    Поиск ВПР всегда в самом левом столбце Поиск ВПР всегда в самом левом столбце
  • При поиске текстовых значений в первом столбце аргумента таблица убедитесь, что данные в этом столбце таблицы не содержат начальных пробелов, конечных пробелов, используемых не по правилам прямых ( ‘ или » ) и «парных» ( ‘ или “ ) кавычек или непечатаемых символов. В этих случаях функция ВПР может вернуть неправильное или непредвиденное значение
    Возвращение ошибки функцией ВПР Возвращение ошибки функцией ВПР
  • При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. В этом случае функция ВПР может вернуть неправильное или непредвиденное значение
  • Если значение аргумента «интервальный_просмотр» — ЛОЖЬ , а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (
Читайте так же:
Госуслуги авторизация личного кабинета

Нюансы использования ВПР

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

Поиск в первом столбце

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

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

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

Поиск до первого совпадения

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

Демонстрация поиска ВПР до первого совпадения

Демонстрация поиска ВПР до первого совпадения

Данное ограничение можно обойти, решение не самое простое, но детально описано в курсе Excel.

Независимость от регистра

Для Excel и ВПР не важно в каком регистре (заглавные или прописные буквы) записано искомое значение и как оно записано в самом диапазоне.

Независимость от регистра

Независимость от регистра

Лишние пробелы

В замечаниях мы показали, как незаметный пробел может вызывать ошибку работы данной функции. Если существует риск загромождения ячеек чрезмерным количеством пробелов, следует очистить ячейки с помощью функции СЖПРОБЕЛЫ (TRIM).

Различие в формате данных

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

Читайте так же:
Можно ли вставлять флешку в планшет

Все можно исправить посредством приведения параметров в одинаковый формат. К примеру:

— если в D5 буквы, а в таблице — цифры;

Приведение формата в самой формуле

Приведение формата в самой формуле

Существует несколько вариаций, как изменить формат текста на цифру:

  • Возвести в степень: G2^1;
  • Двойное отрицание: —G2;
  • Прибавить ноль: G2+0;
  • Умножить на один: G2*1.

Прекращение проявления ошибки #Н/Д

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

Не зафиксирован массив

В примерах выше мы не использовали закрепление массива, т.к. всегда искали одно значение, т.е. не пользовались автозаполнением.

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

Массив можно оформить как умную таблицу. Команда «Форматировать как таблицу» из вкладки «Главная», либо горячее сочетание Ctrl+T. В этом случае ссылки на такой диапазон будут автоматически становиться абсолютными. Но при работе с такими таблицами в Excel есть свои нюансы и, не всегда это подойдет

Фиксирование диапазона в ВПР

Фиксирование диапазона в ВПР

Относительный поиск

При использовании ВПР пользователи, как правило всегда ставят аргумент интервальный_просмотр равным «0» (ЛОЖЬ), что означает, что нужно выполнить точный поиск и значения в первом столбце не отсортированы. Однако, если поиск осуществляется числовых значений, в некоторых случаях, хорошим решением будет отсортировать значения и выполнить не точный поиск. А в некоторых случаях неточный поиск вообще будет единственным решением. Рассмотрим подробнее.

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

  • 0 — 60: F
  • 60 — 70: D
  • 70 — 80: C
  • 80 — 90: B
  • 90 — 100: A

Вот так выглядит формула, если для ее построения использовать логическую функцию ЕСЛИ (да-да, с использованием ПЕРЕКЛЮЧ или ЕСЛИМН решение тоже будет лучше, но, допустим, у вас не самый свежий Excel, да и ВПР здесь выигрывает и у новых логических функций тоже):

Изящное решение с ВПР по поиску оценки

Изящное решение с ВПР по поиску оценки

посмотрите на рисунке на формуле с ЕСЛИ и сравните с таковой ВПР и неточным совпадением, последняя выглядит значительно лучше:

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

ВПР для поиска максимального значения

ВПР для поиска максимального значения

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector