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

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

Добрый день читатель!

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

Если вы знаете принцип работы функции ВПР, то можете догадаться что функция ГПР, отличается только диапазоном поиска, «Г» — означает горизонтальный, а значит и поиск будет горизонтальным. Функция ГПР производит поиск нужных значений в первой строчке вашей таблицы (или же диапазона данных который вы поименовали) и возвращает значение, которое находится в указанной строке таблицы в том же столбике. То есть функцию ГПР нужно использовать в том случае, когда значение, которые нам нужно сравнить располагаются в первой строчке вашей таблицы данных, а значение, которые нужно возвращать находятся в строках ниже. Как видите с описания, поиск идет по строкам, то есть в горизонтальной плоскости.

Теперь рассмотрим, как работает функция ГПР в Excel более пристально, и начнем эту процедуру с изучения синтаксиса функции:

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

  • Искомое нами значение – это то значение, поиском которого мы будем заниматься в первой строчке нашей таблицы (этим аргументом может быть текстовая строка, ссылка или любое значение);
  • Таблица поиска – это наша таблица, где мы будем производить поиск нужных данных (данный диапазон стоит закрепить абсолютными ссылками или же просто вместо адресов указать имя диапазона), аргументы в первой строчке могут быть логическими, простыми числами или текстовые;
  • Номер строки – мы должны указать номер той строчки в таблице, из которой функция вытянет определенное значение. То есть когда мы укажем аргумент 3. в таком случае будет возвращено значение с третьей строчки, а вот в случае, когда значение аргумента будет меньше 1, тогда функция ГПР будет получать значение ошибки #ЗНАЧ!, а если же аргумент будет больше, нежели строк в диапазоне будет возвращено значение ошибки #ССЫЛ!.
  • Интервальный просмотр – в случае кода этот аргумент имеет значение «ИСТИНА», тогда значения первой строки нужно располагать в порядке возрастания: 2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА, иначе функция ГПР выдаст неправильный результат. А вот если аргумент будет «ЛОЖЬ», то таблица скорее всего не отсортирована.

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

А теперь рассмотрим на примере, как используется функция ГПР в Excel, что бы рассмотреть принцип работы этой функции. Итак, у нас есть таблица с видами мебели и их вариациями, вот на основе таких данных и будет экспериментировать: =ГПР(«Диван»;A1:C4;2;ИСТИНА), производим поиск слова «Диван» в строчке 1 и возвращает значение из строки 2, которая находится в том же столбике, результат получаем «Амур». =ГПР(«Кровать»;A1:C4;3;ЛОЖЬ), производим поиск в первой строке и возвращаем значение из 3 строчки, которая находится в том же столбце, в результате получаем «Марроко».

=ГПР(«К»;A1:C4;3;ИСТИНА), производим поиск буквы «К» в первой строке и возвращаем значение из 3 строчки того же столбца. Поскольку эту букву найти не удалось, вернулось ближайшее из меньших значений «Диван», результат получаем «Кром». =ГПР(«Спальня»;A1:C4;4), производим поиск слова «Спальня» в первой строке и возвращаем значение из 4 строчки, которая находится в том же столбике, наш результат будет «Касар». А на этом у меня всё! Я очень надеюсь, что всё о работе с функцией ГПР в Excel вам понятно. С другими функциями вы можете познакомится в «Справочнике функций». Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

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

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

Источник: topexcel.ru

ГПР (функция ГПР)

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

В этой статье описаны синтаксис формулы и использование функции ГПР в Microsoft Excel.

Описание

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

Буква Г в аббревиатуре “ГПР” означает “горизонтальный”.

Синтаксис

Аргументы функции ГПР описаны ниже.

Искомое_значение — обязательный аргумент. Значение, которое требуется найти в первой строке таблицы. “Искомое_значение” может быть значением, ссылкой или текстовой строкой.

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

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

Если аргумент “интервальный_просмотр” имеет значение ИСТИНА, то значения в первой строке аргумента “таблица” должны быть расположены в возрастающем порядке: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если аргумент “интервальный_просмотр” имеет значение ЛОЖЬ, таблица может быть не отсортирована.

В текстовых строках регистр букв не учитывается.

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

Номер_строки — обязательный аргумент. Номер строки в аргументе “таблица”, из которой будет возвращено соответствующее значение. Если значение аргумента “номер_строки” равно 1, возвращается значение из первой строки аргумента “таблица”, если оно равно 2 — из второй строки и т. д. Если значение аргумента “номер_строки” меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе “таблица”, возвращается значение ошибки #ССЫЛ!.

Читайте также:  Как создать таблицу в excel пошаговая инструкция с формулами

Интервальный_просмотр — необязательный аргумент. Логическое значение, которое определяет, какое соответствие должна искать функция ГПР — точное или приблизительное. Если этот аргумент имеет значение ИСТИНА или опущен, возвращается приблизительное соответствие; при отсутствии точного соответствия возвращается наибольшее из значений, меньших, чем “искомое_значение”. Если этот аргумент имеет значение ЛОЖЬ, функция ГПР ищет точное соответствие. Если найти его не удается, возвращается значение ошибки #Н/Д.

Примечание

Если функция ГПР не может найти “искомое_значение” и аргумент “интервальный_просмотр” имеет значение ИСТИНА, используется наибольшее из значений, меньших, чем “искомое_значение”.

Если значение аргумента “искомое_значение” меньше, чем наименьшее значение в первой строке аргумента “таблица”, функция ГПР возвращает значение ошибки #Н/Д.

Если аргумент “интервальный_просмотр” имеет значение ЛОЖЬ и аргумент “искомое_значение” является текстом, в аргументе “искомое_значение” можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одному знаку; звездочка — любой последовательности знаков. Чтобы найти какой-либо из самих этих знаков, следует указать перед ним знак тильды (

Пример

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

Источник: support.office.com

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

Функция ВПР ( В ертикальный ПР осмотр) для многих (но, надеюсь, не для вас) является вершиной эволюции в Excel. Что ж, есть много ситуаций, когда эта формула может оказаться полезной. ВПР имеет следующий синтаксис:

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

искомое_значение – константа или ссылка на ячейку, значение которой вы собираетесь искать

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

номер_столбца – номер столбца, указанного масива, из которого будут возвращаться данные

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

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

Схема

Некоторые замечания о ВПР

Пример Замечание
Искомое значение ищется в ПЕРВОМ столбце массива. Это фундаментальное ограничение ВПР , которое серьёзно вредит универсальности данной функции. Многие пользователи, прикипев к ВПР , и, не зная более гибких альтернатив, вынужденно подстраивают свои таблицы под её возможности, делая нужный столбец первым.
Поиск оснанавливается на первом совпадении результата, если тип поиска = 0 (точное совпадение) и на последнем, если тип поиска = 1 (примерное совпадение). Таким образом у вас есть возможность при помощи ВПР возвращать либо первую совпавшую строку, либо последнюю. Однако имейте в виду, что при нечётком поиске (тип поиска = 1) первый столбец массива должен быть отсортирован по возрастанию .
ВПР поддерживает использование символов подстановки ( * и ? ) в параметре искомого значения. ” ? ” – заменяет собой любой один символ, а ” * ” – заменяет любое количество любых символов. Если используете символы подстановки, то четвёртый параметр должен быть 0 (ЛОЖЬ).
Нечёткий поиск часто применяют для распределения значений по каким либо диапазонам. Например, рассмотрим классический пример вычисления значения скидки для клиента в зависимости от заказанного количества товара. Как видите, эта задача идеально вписывается в возможности нечёткого поиска ВПР . Не забывайте только про сортировку.
При поиске текстовых строк ВПР не видит разницы в регистре букв.
Если значение не найдено, то формула возвращает код ошибки #Н/Д (#N/A). При помощи дополнительной функции ЕСЛИОШИБКА (IFERROR) эти ошибки из эстетических или иных соображений можно перехватывать и подставлять, как в этом примере, пустую строку (или что угодно другое) – в этом случае пользователи не тревожатся попусту, разглядывая загадочные #Н/Д , и, как следствие значительно реже, отвлекают квалифицированных людей от важного.

Функция ГПР (HLOOKUP)

Функция ГПР ( Г оризонтальный ПР осмотр) – это тот же самый ВПР , в котором строки заменили на столбцы и наоборот. ГПР имеет следующий синтаксис:

= ГПР ( искомое_значение ; массив ; номер_строки ; тип_поиска )

искомое_значение – константа или ссылка на ячейку, значение которой вы собираетесь искать

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

номер_строки – номер строки, указанного масива, из которой будут возвращаться данные

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

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

Источник: perfect-excel.ru

Примеры функции ГПР в Excel пошаговая инструкция для чайников

Функция ГПР в Excel используется для поиска значения, указанного в качестве одного из ее аргументов, которое содержится в просматриваемом массиве или диапазоне ячеек, и возвращает соответствующее значение из ячейки, расположенной в том же столбце, на несколько строк ниже (число строк определяется в качестве третьего аргумента функции).

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

Например, в таблице с полями «Имя» и «Дата рождения» необходимо получить значение даты рождения для сотрудника, запись о котором является третьей сверху. В этом случае удобно использовать следующую функцию: =ГПР(“Дата рождения”;A1:B10;4), где “Дата рождения” – наименование столбца таблицы, в котором будет выполнен поиск, A1:B10 – диапазон ячеек, в котором расположена таблица, 4 – номер строки, в которой содержится возвращаемое значение (поскольку таблица содержит шапку, номер строки равен номеру искомой записи +1.

Пошаговые примеры работы функции ГПР в Excel

Пример 1. В таблице содержатся данные о клиента и их контактных номерах телефонов. Определить номер телефона клиента, id записи которого имеет значение 5.

Читайте также:  Как посчитать количество ячеек в excel формула

Вид таблицы данных:

Для расчета используем формулу:

  • F1 – ячейка, содержащая название поля таблицы;
  • A1:C11 – диапазон ячеек, в которых содержится исходная таблица;
  • E2+1 – номер строки с возвращаемым значением (для – шестая строка, поскольку первая строка используется под шапку таблицы).

В ячейке F2 автоматически выводится значение соответствующие номеру id в исходной таблице.

ГПР для выборки по нескольких условиях в Excel

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

Создадим заготовку таблицы:

Для удобного использования в ячейке E2 создадим выпадающий список. Для этого выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В появившемся окне «Проверка вводимых значений» выберите из секции «Тип данных:» в выпадающем меню опцию «Список». А в поле «Источник:» укажите адрес ссылки на диапазон исходных ячеек первого столбца таблицы =$A$2:$A$11, как показано выше на рисунке.

Для выбора клиента используем следующую формулу в ячейке F2:

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

Функция ЕСЛИ выполняет проверку возвращаемого значения. Если искомая ячейка не содержит данных, будет возвращена строка «Не указан».

Интерактивный отчет для анализа прибыли и убытков в Excel

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

Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):

В ячейку F2 запишем следующую формулу:

Функция ABS возвращает абсолютное число, равное разнице возвращаемых результатов функций ГПР.

В ячейке G2 запишем формулу:

Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.

Особенности использования функции ГПР в Excel

Функция имеет следующую синтаксическую запись:

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

  • искомое_значение – обязательный аргумент, принимающий данные любого типа, характеризующие элемент первой строки константы массива или ячейку, расположенную в первой строке диапазона ячеек. При работе с именованными областями данных (списки, таблицы) в качестве данного аргумента указывают название поля (столбца), в котором содержится возвращаемое значение.
  • таблица – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек, в котором находится список или таблица. Если первой строкой диапазона является шапка таблицы с текстовыми названиями полей, при поиске требуемого названия поля не учитывается регистр символов. Для корректной работы функции ГПР необходимо, чтобы содержащиеся в первой строке значения были отсортированы слева направо.
  • номер_строки – обязательный аргумент, характеризующийся числовым значением из диапазона целых положительных чисел, которое соответствует номеру строки относительно первой строки массива или диапазона ячеек, переданных в качестве аргумента таблица. Функция ГПР возвращает результат, взятый из строки таблицы с номером, указанным данным аргументом. Ошибки могут возникать в следующих случаях:
  1. В качестве аргумента номер_строки передано число Примечания:

    1. Если аргумент [интервальный_просмотр] явно не указан или имеет значение ИСТИНА, для получения корректных результатов работы функции ГПР первая строка таблицы или массива, переданных в качестве аргумента таблица, должна быть отсортирована по возрастанию значений слева направо. Для поиска точного совпадения (значение аргумента – ЛОЖЬ) сортировку можно не выполнять.
    2. Если все данные, хранящееся в первой строке массива или таблицы, превышают по значению данные, переданные в качестве аргумента искомое_значение, функция ГПР вернет код ошибки #Н/Д.
    3. При поиске точного соответствия (аргумент [интервальный_просмотр] = ЛОЖЬ) в таблицах с именованными столбцами (полями) можно использовать подстановочные знаки: «?» – замена одного символа, «*» – замена нескольких символов.

    Источник: exceltable.com

    30 функций Excel за 30 дней: ГПР (HLOOKUP)

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

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

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

    Функция 10: HLOOKUP (ГПР)

    Функция HLOOKUP (ГПР) ищет значение в первой строке таблицы и возвращает другое значение из того же столбца таблицы.

    Как можно использовать функцию HLOOKUP (ГПР)?

    Поскольку функция HLOOKUP (ГПР) может найти точное или приближенное значение в строке, то она сможет:

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

    Синтаксис HLOOKUP (ГПР)

    Функция HLOOKUP (ГПР) имеет следующий синтаксис:

    HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
    ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)

    • lookup_value (искомое_значение): значение, которое требуется найти. Может быть значением или ссылкой на ячейку.
    • table_array (таблица): таблица поиска. Может быть ссылкой на диапазон или именованным диапазоном, содержащим 2 строки или более.
    • row_index_num (номер_строки): строка, содержащая значение, которое должно быть возвращено функцией. Задается номером строки внутри таблицы.
    • range_lookup (интервальный_просмотр): для поиска точного совпадения используйте FALSE (ЛОЖЬ) или 0; для приблизительного поиска – TRUE (ИСТИНА) или 1. В последнем случае строка, в которой функция выполняет поиск, должна быть отсортирована в порядке возрастания.

    Ловушки HLOOKUP (ГПР)

    Как и VLOOKUP (ВПР), функция HLOOKUP (ГПР) может работать медленно, особенно когда выполняет поиск точного совпадения текстовой строки в несортированной таблице. По мере возможности, используйте приблизительный поиск в таблице, отсортированной по первой строке по возрастанию. Вы можете сначала применить функцию MATCH (ПОИСКПОЗ) или COUNTIF (СЧЁТЕСЛИ), чтобы убедиться, что искомое значение вообще существует в первой строке.

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

    Пример 1: Найти значения продаж для выбранного региона

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

    • Имя региона введено в ячейке B7.
    • Таблица поиска по региону имеет две строки и занимает диапазон C2:F3.
    • Итоги продаж находятся в строке 2 нашей таблицы.
    • Последний аргумент имеет значение FALSE (ЛОЖЬ), чтобы найти точное совпадение при поиске.

    Формула в ячейке C7 такая:

    Если название региона в первой строке таблицы не найдено, результатом функции HLOOKUP (ГПР) будет #N/A (#Н/Д).

    Пример 2: Найти показатель для выбранной даты

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

    • Дата записана в ячейке C5.
    • Таблица поиска показателя имеет две строки и расположена в диапазоне C2:F3.
    • Таблица поиска отсортирована по строке с датами по возрастанию.
    • Показатели записаны в строке 2 нашей таблицы.
    • Последний аргумент функции имеет значение TRUE (ИСТИНА), чтобы искать приближенное совпадение.

    Формула в ячейке D5 такая:

    Если дата не найдена в первой строке таблицы, функция HLOOKUP (ГПР) найдет ближайшее наибольшее значение, которое меньше аргумента lookup_value (искомое_значение). В данном примере искомое значение – 15 марта. Его в строке с датами нет, поэтому формула возьмет значение 1 января и вернет 0,25.

    Источник: office-guru.ru

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

    Цель данной статьи – показать, как работает функция Excel ГПР. Рассмотрим работу функций на примере расчета прогноза с логарифмическим трендом и сезонностью.

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

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

    1. Рассчитаем значения логарифмического тренда и продлим тренд в будущее.

    Выделим из объема продаж по месяцам значения логарифмического тренда и продлим тренд в будущее.

    Для этого воспользуемся функцией Excel =Предсказ:

    Разберем формулу Excel =ПРЕДСКАЗ(

    • LN(D2) – X- ссылка но номер периода X, причем делаем логарифм Х;
    • $D$4:$BE$4 – известные значения y – зафиксированная ссылка на ряд с объемом продаж за весь период;
    • LN($D$2:$BE$2) – известные значения x – зафиксированная ссылка на диапазон с номерами периодов – X под логарифмом;
    • Обратите внимание, что ряд “известные значения y” = “известные значения x”

    Как зафиксировать ссылку читайте в статье “Как зафиксировать ссылку в Excel”

    Способы расчета логарифмического тренда в Excel в статье “5 способов расчета логарифмического тренда”

    Для чего мы пронумеровываем периоды и о временных рядах читайте статью “О временных рядах”

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

    2. Выделим сезонность

    1. Определим отношение Объема продаж к тренду – объем продаж разделим на логарифмический тренд:

    2. Рассчитаем среднее отношение объема продаж к тренду для каждого месяца в году.

    2.1. Определим номер месяца для каждого периода в ряду:

    2.2. Пронумеруем номера месяцев для сезонности:

    2.3. Воспользовавшись функцией Excel=СУММЕСЛИ и =СЧЕТЕСЛИ (подробнее о работе с этими функциями читайте в статье “Формулы Excel “СУММЕСЛИ” и “СЧЕТЕСЛИ””), рассчитаем сезонность для каждого месяца – среднее отклонение отношений объема продаж от логарифмического тренда:

    2.3. Рассчитаем нормирующий коэффициент – среднее значений полученной сезонности

    В среднем сезонность должна быть равна 1. А у нас получилась 0,995027

    2.4. Рассчитаем сезонность.

    Для того, чтобы сезонность стала равной 1, разделим полученную сезонность на нормирующий коэффициент, который у нас равен 0,995027:

    Получили месячные коэффициенты сезонности приведенные в среднем к 1.

    3. Прогнозный тренд скорректируем сезонностью, для этого воспользуемся функцией Excel =ГПР – получим прогноз.

    • Значения логарифмического тренда и продлили тренд в будущее на 12 месяцев
    • Сезонность.

    Теперь рассчитаем прогноз.

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

    Для поиска нужного коэффициента воспользуемся функцией Excel ГПР.

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

    • Искомое значение – BF5 – номер прогнозного месяца, для которого нам необходимо найти коэффициент сезонности в таблице и вернуть его значение в ячейку:

    • Таблица – $D$12:$O$13 – зафиксированная ссылка на таблицу с сезонностью, в которой в первой строке находятся номера месяцев, а во второй коэффициенты сезонности:

    Формула ГПР ищет номер месяца в первой строке таблицы и возвращает коэффициент сезонности из второй, но для этого еще надо указать строку из которой возвращаем сезонность:

    • 2 – номер строки – номер строки в таблице, из которого возвращаем коэффициент сезонности. В первой строке таблице с сезонностью номера месяцев, во второй коэффициенты сезонности

    • интервальный просмотр ставим = 0

    Нажимаем ввод и получаем прогноз – логарифмический тренд умноженный на коэффициент сезонности:

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

    Коллеги, для автоматизации прогнозирования предлагаем воспользоваться нашей программой Forecast4AC PRO.

    Forecast4AC PRO умеет автоматически подбирать подходящую модель прогноза и рассчитает прогноз в Excel для большого массива данных быстро и легко!

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

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

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

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

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

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

    Источник: 4analytics.ru