Функция предсказ в excel

Содержание:

Метод прогнозируемого экспоненциального роста c использованием функции РОСТ

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

Таблица с исходными данными:

Выделяем диапазон ячеек B10:B11 и используем следующую функцию:

  • B2:B9 – диапазон известных значений количества теплоты, полученные в результате проведения опытов;
  • A2:A9 – диапазон температур, для которых проводились опыты;
  • A10:A11 – диапазон температур, для которых необходимо вычислить предполагаемые значения количества теплоты.

Для ввода формулы используем комбинацию клавиш CTRL+SHIFT+Enter так как формула должна выполняться в массиве. В результате получим:

Визуально заметно явное несоответствие найденных величин диапазону уже известных значений. В Excel существует еще одна функция для прогнозирования на основе известных значений – ТЕНДЕНЦИЯ. Воспользуемся ей и сравним полученные результаты. Для этого выделяем диапазон ячеек C10:C11 и снова в массиве вводим функцию ТЕНДЕНЦИЯ:

Как видно, синтаксические записи функций РОСТ и ТЕНДЕНЦИЯ идентичны, однако они используют различные алгоритмы для вычислений. Для ввода функции ТЕНДЕНЦИЯ снова используем комбинацию клавиш CTRL+SHIFT+Enter. В результате получим:

То есть, в данном примере функция ТЕНДЕНЦИЯ дает более точный прогноз и целесообразно использовать именно ее.

Метод экспертных оценок

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

Как рассчитать прогноз по методу экспоненциального сглаживания в Excel?

Формула расчета прогноза проста:

Ŷt+1=k*Yt +(1-k)* Ŷt

Где:

  • Ŷt+1 – прогноз на следующий период t+1;
  • Yt – данные для прогноза за текущий период t (например, продажи по месяцам);
  • k – коэффициент сглаживания ряда , k задается вами вручную и находится в диапазоне от 0 до 1, 0 < k < 1
  • Ŷt – значение прогноза на текущий период t. Причем в первый период (месяц, день…) Ŷ1=Y1, т.е. Ŷt в первый период равны продажам в этот период.

Прогноз по методу экспоненциального сглаживания = коэффициент сглаживания * последнее фактическое значение продаж + (1- коэффициент сглаживания)*предыдущий прогноз по методу экспоненциального сглаживания.

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

Депозитный калькулятор со сложным процентом в Excel

Пример 2. Клиент банка внес депозит на сумму 50000 рублей с процентной ставкой 14,5% (сложные проценты). Определить, сколько времени потребуется на удвоение вложенной суммы?

Интересный факт! Для быстрого решения данной задачи можно воспользоваться эмпирическим способом приблизительной оценки сроков (в годах) на удвоение инвестиций, вложенных под сложный процент. Так называемое правило 72 (или 70 или правило 69). Для этого нужно воспользоваться простой формулой – число 72 разделить на процентную ставку: 72/14,5 = 4,9655 лет. Главный недостаток правила «магического» числа 72 заключается в погрешности. Чем выше процентная ставка, тем выше погрешность в правиле 72. Например, при процентной ставки 100% годовых погрешность в годах достигает до 0,72 (а в процентах это аж 28%!).

Для точного расчета сроков удвоения инвестиций будем использовать функцию LOG. За одно и проверим величину погрешности правила 72 при процентной ставке 14,5% годовых.

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

Для расчета будущей стоимости инвестиции при известной процентной ставке можно использовать следующую формулу: S=A(100%+n%) t , где:

  • S – ожидаемая сумма по истечению срока;
  • A – размер депозита;
  • n – процентная ставка;
  • t – срок хранения депозитных средств в банке.

Для данного примера эту формулу можно записать как 100000=50000*(100%+14,5%) t или 2=(100%+14,5%) t . Тогда для нахождения t можно переписать уравнение как t=log(114,5%)2 или t=log1,1452.

Для нахождения значения t запишем следующую формулу сложного процента по депозиту в Excel:

  • B4/B2 – соотношение ожидаемой и начальной сумм, которое является показателем логарифма;
  • 1+B3 – прирост процентов (основание логарифма).

В результате расчетов получим:

Депозит удвоится спустя немного более чем 5 лет. Для точного определения лет и месяцев воспользуемся формулой:

Функция ОТБР отбрасывает в дробном числе все что после запятой подобно функции ЦЕЛОЕ. Разница между функциями ОТБР и ЦЕЛОЕ заключается лишь в расчетах с отрицательными дробными числами. Кроме того, ОТБР имеет второй аргумент где можно указать количество оставляемых знаков после запятой. Поэтом в данном случаи можно воспользоваться любой из этих двух функций на выбор пользователя.

Получилось 5 лет и 1 месяц и 12 дней. Теперь сравним точные результаты с правилом 72 и определим величину погрешности. Для данного примера формула, следующая:

Мы должны умножить значение ячейки B3 на 100 так как ее текущее значение 0,145, которое отображается в процентном формате. В результате:

После скопируем формулу из ячейки B6 в ячейку B8, а в ячейке B9:

Посчитаем сроки погрешности:

Затем в ячейку B10 снова скопируем формулу из ячейки B6. В результате получим разницу:

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

Теперь для наглядности пропорциональной зависимости роста погрешности и роста уровня процентной ставки повысим процентную ставку до 100% годовых:

На первый взгляд разница погрешности не существенная по сравнению с 14,5% годовых — всего около 2-ух месяцев и 100% годовых — в пределах 3-х месяцев. Но доля погрешности в сроках окупаемости более чем ¼, а точнее 28%.

Составим простой график для визуального анализа как коррелируется зависимость изменения процентной ставки и процента погрешности правила 72 от факта:

Чем выше процентная ставка, тем хуже работает правило 72. В итоге можно сделать следующий вывод: до 32,2% процентов годовых можно смело пользоваться правилом 72. Тогда погрешность составляет менее 10-ти процентов. Вполне сойдет если не требуются точные, но сложные расчеты по срокам окупаемости инвестиций в 2 раза.

Пример функции ПРЕДСКАЗ в Excel

Сначала возьмем для примера условные цифры – значения x и y.

В свободную ячейку введем формулу: =ПРЕДСКАЗ(31;A2:A6;B2:B6). Функция находит значение y для заданного значения x = 31. Результат – 20,9063.

Воспользуемся функцией для прогнозирования будущих продаж в Excel.

Сначала построим график по имеющимся данным.

Выделим график. Щелкнем правой кнопкой мыши – «Добавить линию тренда». В появившемся окне установим галочки напротив пунктов «Показывать уравнение» и «Поместить величину достоверности аппроксимации».

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

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

Подставим значение x (11 месяц) в уравнение. Получим значение y (продажи для искомого месяца). Скопируем формулу до конца второго столбца.

Теперь для расчета будущих продаж воспользуемся функцией ПРЕДСКАЗ. Точка x, для которой необходимо рассчитать значение y, соответствует номеру месяца для прогнозирования (в нашем примере – ссылка на ячейку со значением 11 – А12). Формула: =ПРЕДСКАЗ(A12;$B$2:$B$11;$A$2:$A$11).

Абсолютные ссылки на диапазоны со значениями y и x делают их статичными (не позволяют изменяться, когда мы протягиваем формулу вниз).

Таким образом, для прогнозирования будущих значений на основе имеющихся фактических данных можно использовать функцию ПРЕДСКАЗ в Excel. Она входит в группу статистических функций и позволяет легко получить прогноз параметра y для заданного x.

1.1. Понятие прогнозирования в Excel

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

Существует три метода прогнозирования:

— метод скользящих средних

— метод экспоненциального сглаживания

— метод регрессии

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

В Google Sheets выбираем Редактор диаграмм  -> Дополнительные и ставим галочку возле Линии тренда. В настройках выбираем Ярлык — Уравнение и Показать R^2.

Если вы делаете все в MS Excel, то правой кнопкой мыши кликаем на график и в выпадающем меню выбираем «Добавить линию тренда».

По умолчанию строится линейная функция. Справа выбираем «Показывать уравнение на диаграмме» и «Величину достоверности аппроксимации R^2».

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.

Виды сезонности

Обычно выделяют три вида сезонности, они отличаются по спаду в разнице продаж:

Умеренная: разница в пределах 10-20%, практически не влияет на финансовое самочувствие компании. Характерно для товаров повседневного спроса. Продавцы и поставщики чувствуют себя комфортно на протяжении всего года;

Яркая: разница спада продаж достигает 30-40%, приходится стимулировать спрос, чтобы не случился кассовый разрыв;

Жёсткая: падение продаж на 50-100%, нет шансов вернуть объёмы на прежние показатели. Есть ли смысл стимулировать спрос на новогодние ёлочные игрушки и валентинки в августе?

Для расчета значений тренда:

  1. Определим коэффициенты уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн(). Для этого в ячейки Excel вводим функцию =линейн(объёмы продаж за 5 лет; номера периодов; 1;0).  Выделяем 2 ячейки, в левой — формула =линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2 + Ctrl+Shift+Enter). Excel рассчитает для нас значение коэффициентов a и b.  
  2. Рассчитываем значения тренда. Для этого в уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y-значения линейного тренда для каждого периода (см. вложенный файл).

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

Шаг 1

Следуя нашему алгоритму, мы должны сгладить временной ряд. Воспользуемся методом скользящей средней. Видим, что в каждом году есть большие пики (май-июнь 2016 и апрель 2017), поэтому возьмем период сглаживания пошире, например, месячную динамику, т.е. 12 месяцев.

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

yi — фактическое значение i-го уровня ряда,

yt — значение скользящей средней в момент времени t,

2p+1 — длина интервала сглаживания.

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

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

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Функции Excel — простые примеры для начинающего

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

Сразу хотелось бы отметить, что все примеры будем рассматривать в Microsoft office 2010.

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

Прогнозирование в Excel сроков изготовления заказов.

Переходим непосредственно к рассмотрению примера.

Небольшой участок завода производит строительные металлоконструкции. Входным сырьем является листовой и профильный металлопрокат. Мощность участка в рассматриваемом периоде времени неизменна. В наличии есть статистические данные о сроках изготовления 13-и заказов (k=13) и количестве использованного металлопроката. Попробуем найти зависимость срока изготовления заказа от суммарной длины и массы профильного проката и суммарной площади и массы листового проката.

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

Примечательно, что найденная зависимость связывает в одной формуле параметры с различными единицами измерения. Это нормально. Найденные коэффициенты не являются безразмерными. Например, размерность коэффициента b – рабочие дни, а коэффициента m1– рабочие дни/м.

1. Запускаем MS Excel и заполняем ячейки B4…F16 таблицы Excel исходными статистическими данными. В столбцы пишем значения переменных xi и фактические значения функции y, располагая данные, относящиеся к одному заказу в одной строке.

2. Так как функции ЛИНЕЙН и ЛГРФПРИБЛ — функции выводящие результаты в виде массива, то их ввод имеет некоторые особенности. Выделяем область размером 5×5 ячеек — ячейки I9…M13. Количество выделенных строк всегда — 5, а количество столбцов должно быть равно количеству переменных xплюс 1. В нашем случае это: 4+1=5.

3. Нажимаем на клавиатуре клавишу F2 и набираем формулу

в ячейках I9…M13: =ЛИНЕЙН(F4:F16;B4:E16;ИСТИНА;ИСТИНА)

4. После набора формулы необходимо для ее ввода нажать сочетание клавиш Ctrl+Shift+Enter. (Знак «+» нажимать не нужно, в записи он означает, что клавиши нажимаются последовательно при удержании нажатыми всех предыдущих.)

5. Считываем результаты работы функции ЛИНЕЙН в ячейках I9…M13.

Карту, поясняющую значения каких параметров в каких ячейках выводятся, я расположил в ячейках I4…M8 для удобства чтения сверху над массивом значений.

Общий вид уравнения аппроксимирующей функции y, представлен в объединенных ячейках I2…M2.

Значения коэффициентов b, m1, m2, m3, m4 считываем соответственно

в ячейке M9: b=4,38464164

в ячейке L9: m1=0,002493053

в ячейке K9: m2=0,000101103

в ячейке J9: m3=-0,084844006

в ячейке I9: m4=0,002428953

6. Для определения расчетных значений функции y — срока изготовления заказа — вводим формулу

в ячейку G4: =$L$9*B4+$K$9*C4+$J$9*D4+$I$9*E4+$M$9 =5,0

y=b+m1*x1+m2*x2+m3*x3+m4*x4

7. Копируем эту формулу во все ячейки столбца от G5 до G17 «протягиванием» и сверяем расчетные значения с фактическими. Совпадение очень хорошее!

8. Предварительные действия все выполнены. Уравнение аппроксимирующей функции y найдено. Пробуем выполнить прогнозирование в Excel срока изготовления нового заказа. Вписываем исходные данные.

8.1. Длину прокатных профилей по проекту x1 в метрах пишем

в ячейку B17: 2820

8.2. Массу прокатных профилей x2 в килограммах пишем

в ячейку C17: 62000

8.3. Площадь листового проката, используемого в новом заказе по проекту, x3 в метрах квадратных заносим

в ячейку D17: 110,0

8.4. Общую массу листового проката x4 в  килограммах вписываем

в ячейку E17: 7000

9. Расчетный срок изготовления заказа y в рабочих днях считываем

в ячейке G17: =$L$9*B17+$K$9*C17+$J$9*D17+$I$9*E17+$M$9 =25,4

Прогнозирование в Excel выполнено. На основе статистических данных мы рассчитали предположительный срок выполнения нового заказа — 25,4 рабочих дней. Остается выполнить заказ и сверить фактическое время с прогнозным.

Примеры функции ТЕНДЕНЦИЯ в Excel для прогнозирования данных

Функция ТЕНДЕНЦИЯ в Excel используется при расчетах последующих значений для рассматриваемого события и возвращает данные в соответствии с линейным трендом. Функция выполняет аппроксимацию (упрощение) прямой линией диапазона известных значений независимой и зависимой переменных с использованием метода наименьших квадратов и прогнозирует будущие значения зависимой переменной Y для указанных последующих значений независимой переменной X. Рассматриваемая функция не используется для получения статистической характеристики модели тренда и математического описания.

Линейным трендом называется распределение величин в изучаемой последовательности, которое может быть описано функцией типа y=ax+b. Поскольку функция ТЕНДЕНЦИЯ выполняет аппроксимацию прямой линией, точность результатов ее работы зависит от степени разброса значений в рассматриваемом диапазоне.

Метод по Шрайбфедеру, или метод средней взвешенной

Если в прошлом методе мы считали спрос по средним продажам, то в этом появляются различные веса разных месяцев. Что мы делаем?

Рассчитываем продажи на один рабочий день прошедших месяцев

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

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

Разберём на примере:

В первом столбце исходные данные по месяцам, и мы хотим построить на их основе прогноз на декабрь. Продажи в ноябре – 560 штук. Рабочих дней 28. Считаем потребление за один рабочий день – 20 штук. 

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

Вес для ноября – 3, для октября – 2,5 и т.д. Самый большой вес у прошлого периода, наиболее ближнему к тому, к которому мы проводим расчёт. В данном случае это ноябрь. Дальше каждый месяц умножаем на его вес. 20х3 = 60. После считаем сумму всех месячных потреблений, умноженных на вес – 143. Общая сумма весов – 10.

Прогноз на декабрь = 143/10* 28(число рабочих дней) = 400 штук

В книге «Эффективное управление запасами» Шрайбфедер предлагает множество схем весов, которые могут подходить для разных товаров. Например:

  • Простая шестимесячная (или трёхмесячная) средняя. Это расчёт среднего с окном 6. То есть необходимо взять 6 месяцев и посчитать среднее за этот период.
  • Для сезонных товаров он выделял либо простую сезонную среднюю, либо сезонную взвешенную среднюю. Если мы строим прогноз на декабрь, то берём данные за последнюю зиму, либо сезонную взвешенную среднюю. В данном случае декабрь значит для нас больше, т.к. мы делаем прогноз на месяц. 
  • Коэффициенты взвешенной средней. Это то, что было в нашем примере – 3, 2,5 и на убыль.

То есть здесь есть какой-то предполагаемый набор весов. На нашей практике встречалось, что компания разрабатывает собственный набор весов. Работать так можно, но независимо от того, как ответственно мы походим к расчётам этих весов, данный метод построения прогнозов имеют довольно большие ограничения.

Посмотрим пример расчёта по средневзвешенным продажам в Excel.

У нас есть ряд продаж, известно число рабочих дней в каждом периоде, и мы хотим построить прогнозы. Имеются прогнозы на 5 месяцев с весами от 3 до 1. И на три месяца с весами от 5 до 1. Продажи делятся на число рабочих дней, умножаются на вес этого месяца. Получившийся показатель делим на сумму весов и умножаем на число рабочих дней. Реализовать всё это в Excel достаточно просто. Логика такая: мы выбираем какую-то формулу весов, либо разработанную нами, либо предложенную в теории. И исходя из этой системы весов строим прогнозы. 

Функция РОСТ

Дата добавления: 2013-12-23 ; просмотров: 9081 ; Нарушение авторских прав

ПРЕДСКАЗ(х; изв_знач_y; изв_знач_x)

Функция ПРЕДСКАЗ

Прогнозирование с помощью функций

Прогнозирование экономических показателей

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

В Excel для прогнозирования используются ряд функций (ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ) и диаграммы.

Функция ПРЕДСКАЗ позволяет сделать прогноз, применяя линейную регрессию диапазона известных данных или массивов (x,y). Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям.

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

х — это точка данных, для которой предсказывается значение.

изв_знач_y — это зависимый массив или интервал данных.

изв_знач_x — это независимый массив или интервал данных.

В качестве примера выполним расчет ожидаемой прибыли за 2006 год на основе данных о полученной прибыли в целом за год за 1999-2005 годы, используя функцию ПРЕДСКАЗ (рис. 92).

Рис. 92. Исходные данные для прогнозирования прибыли предприятия

Для расчета прибыли за 2006 год установите курсор в ячейку С12, выберите команду Функция в меню Вставка. В раскрывшемся окне Мастера функций выберите категориюфункцийСтатистическиеи затем вызовите функцию ПРЕДСКАЗ. На экране появится диалоговое окно функции ПРЕДСКАЗ. (рис. 93).

Рис. 93. Окно диалога функции ПРЕДСКАЗ

в появившемся окне введите исходные данные и получите результат (рис. 94).

Рис. 94. Результаты прогнозирования с помощью функции ПРЕДСКАЗ

Функция РОСТ рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и y-значений. Функция рабочего листа РОСТ может применяться также для аппроксимации существующих x- и y-значений экспоненциальной кривой.

РОСТ(изв_знач_y; изв_знач_x; нов_знач_x; константа),

изв_знач_y — это множество значений y, которые уже известны для соотношения y = b*m^x.

изв_знач_x — это необязательное множество значений x, которые уже известны для соотношения y = b*m^x.

нов_знач_x — это новые значения x, для которых РОСТ возвращает соответствующие значения y.

константа — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1.

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

Если константа имеет значение ЛОЖЬ, то b полагается равном 1, а значения m подбираются так, чтобы y = m^x.

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

Установите курсор в ячейку С12, выберите команду Функция в меню Вставка, а затем выберите функцию РОСТ. На экране появится диалоговое окно функции РОСТ (рис. 95).

Рис. 95. Окно диалога функции РОСТ

в появившемся окне введите исходные данные и получите результат (рис. 96).

Рис. 96. Результаты прогнозирования с помощью функции РОСТ

Реализация функции MS Excel «ПРЕДСКАЗ»

У кого-нибудь имеется готовая реализация функции на С#? Знаю как использовать функцию в своей программе через Microsoft.Office.Interop.Excel и WorksheetFunction. Но, хотелось бы, целиком на C#. Сам еще написать не пробовал, формулы не сложные вроде, но, вдруг у кого готовое решение завалялось.

Экспорт в Excel: Прекращена работа программы «Microsoft Excel»Файл сохраняется успешно, но поле строки excelApp.Quit(); Появляется окно ошибки Excel: .

Ошибка CS0019: Оператор «*» не может применяться к операндам типа «decimal» и «float»Здравствуйте! Писал приложение и наткнулся на интересную ошибку (честно говоря, я не совсем понимаю.

Переопределить операции «+» «=» «-» для экземпляров моего классаДобрый день. Мне нужно переопределить операции «+» «=» «-» для экземпляров моего класса. Я вижу это.

Функции ссылки и поиска

К данной категории формул относятся:

  • ВЫБОР – выбор какого-нибудь значения из списка (массива) данных;
  • СТОЛБЕЦ – вывод номера колонки указанной ячейки;
  • ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ – отображение данных, которые хранятся в отчёте сводной таблицы;
  • ГПР – поиск в массиве данных;
  • ИНДЕКС – выбор какого-нибудь значения согласно дополнительному индексу в указанном диапазоне ячеек;
  • ДВССЫЛ – получение ссылки, которая изначально была задана текстовым значением;
  • ПРОСМОТР – поиск значений в массиве;
  • ПОИСКПОЗ – поиск позиции указанного текста или значения в определенном диапазоне ячеек;
  • СМЕЩ – смещение ссылки относительно указанной ссылки;
  • СТРОКА – возвращает номер строки в указанной ссылке;
  • ТРАНСП – транспонирование массива данных;
  • ВПР – поиск значения в одном массиве и получение данных из ячейки в найденной строке в определенном списке данных.

Формула расчета метода экспоненциального сглаживания в Excel

Ниже на рисунке изображен отчет спроса на определенный продукт за 26 недель. Столбец «Спрос» содержит информацию о количестве проданного товара. В столбце «Прогноз» – формула:

В столбце «Скользящая средняя» определяется прогнозируемый спрос, рассчитанный с помощью обычного вычисления скользящей средней с периодом 6 недель:

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

Коэффициент «Альфа:» вводится в ячейке G1, он значит вес присвоения наиболее актуальным данным. В данном примере он имеет значение 30%. Остальные 70% веса распределяется на остальные данные. То есть второе значение с точки зрения актуальности (с право на лево) имеет вес равный 30% от оставшихся 70% веса – это 21%, третье значение имеет вес равен 30% от остальной части 70% веса – 14,7% и так далее.

ПРЕДСКАЗ и ТЕНДЕНЦИЯ в Excel

При добавлении линейного тренда на график Excel, программа может отображать уравнение прямо на графике (смотри рисунок ниже). Вы можете использовать это уравнение для расчета будущих продаж. Функции FORECAST (ПРЕДСКАЗ) и TREND (ТЕНДЕНЦИЯ) дают тот же результат.

Пояснение: Excel использует метод наименьших квадратов, чтобы найти линию, которая соответствует точкам наилучшим образом. Значение R 2 равно 0.9295, что является очень хорошим значением. Чем оно ближе к 1, тем лучше линия соответствует данным.

  1. Используйте уравнение для расчета будущих продаж:


Используйте функцию FORECAST (ПРЕДСКАЗ), чтобы рассчитать будущие продажи:

Примечание: Когда мы протягиваем функцию FORECAST (ПРЕДСКАЗ) вниз, абсолютные ссылки ($B$2:$B$11 и $A$2:$A$11) остаются такими же, в то время как относительная ссылка (А12) изменяется на A13 и A14.

  1. Если вам больше нравятся формулы массива, используйте функцию TREND (ТЕНДЕНЦИЯ) для расчета будущих продаж:

Примечание: Сначала выделите диапазон E12:E14. Затем введите формулу и нажмите Ctrl+Shift+Enter. Строка формул заключит ее в фигурные скобки, показывая, что это формула массива . Чтобы удалить формулу, выделите диапазон E12:E14 и нажмите клавишу Delete.

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

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

=EXP(число)

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

Примечания 1:

  1. Функции LN и EXP являются противоположными друг другу по возвращаемому результату. Логарифм указывает, в какую степень необходимо возвести основание (в случае натурального логарифма lnx показатель равен примерно 2,718), чтобы получить показатель x. Функция EXP определяет показатель x.
  2. Аргумент число может быть задан любым числом из диапазона действительных чисел (целые и дробные отрицательные, положительные значения и 0). Результат выполнения =EXP(0) равен 1.
  3. В качестве аргумента EXP могут быть переданы логические значения ИСТИНА и ЛОЖЬ, которые будут автоматически преобразованы к числовым значениям 1 и 0 соответственно.
  4. Если в качестве аргумента число были переданы не преобразуемые к числовому значению имя или текстовая строка, функция EXP вернет код ошибки #ЗНАЧ!.
  5. Функцию можно использовать в качестве формулы массива.

Примечания 2:

  1. Как известно, число e является показателем степени натурального логарифма, который записывается, например, так: ln10, то есть, логарифм с основанием 2,718 из 10. Само число e является показателем роста для любого процесса, зависимые величины которого изменяются непрерывно с изменением независимых. В качестве примеров могут служить такие процессы, как деление живых клеток организма (через определенный период времени одна клетки делится на две, затем каждая из этих двух делится еще на две и так далее) или распад радиоактивных веществ (зная коэффициент распада можно узнать, сколько радиоактивного вещества уже распалось на более простые элементы).
  2. Число e используется для аппроксимации (создания упрощенной модели) систем, величины которых изменяются неравномерно.
  3. Чтобы понять физический смысл числа e, рассмотрим процесс роста капиталовложений в банке. Например, банк предложил 100%-е увеличение капитала по истечению определенного периода, например, 12 месяцев. То есть, прибыль вкладчика удвоится. Предположим, что процесс роста капитала является непрерывным на протяжении года. Тогда для расчета суммы капитала по истечению 6 месяцев можно использовать формулу R=(1+100%/2)2, где R – рост капитала, 2 – количество полупериодов роста. Если мы решим определить рост за 4 месяца, формула примет вид R=(1+100%/3)3, за 3 месяца — R=(1+100%/4)4 и т. д. В общем случае имеем формулу R=(1+100%/x)x. Если x→∞ (стремится к бесконечности) R (рост) примет значение 2,718. Из этого следует, что максимально возможный 100%-й рост за мельчайший период времени не может превысить значение 2,718, которое и является числом e (числом Эйлера). В общем случае любой рост может быть выражен формулой R=ep*t, где p – прирост величины (например, не 100%, как в рассмотренном выше примеров, а 30%, то есть 0,3), а t – время (например, если депозитный договор рассчитан на 5 лет, то t=5). Тогда для расчета в Excel достаточно ввести формулу =EXP(0,3*5).

Прогноз эффективности использования рекламного бюджета по функции РОСТ

Пример 2. За 10 дней до окончания 30-дневного месяца было решено определить общую прогнозируемую прибыль сети магазинов в месяц на основании имеющихся данных за прошедшие 20 дней, на протяжении которых прибыль за день в целом постоянно увеличивалась благодаря использованию эффективной рекламы. Необходимо рассчитать, превысит ли прибыль значение в 3 млн. рублей.

Для решения используем следующую формулу:

  • B2:B21 – массив известных значений прибыли за день для первых 20 дней;
  • A2:A21 – массив дней, для которых размер прибыли уже известен;
  • A22:A31 – массив дней, для которых выполняется прогнозирование прибыли.

В результате имеем:

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

=3000000;»Превысит 3 млн. руб»;»Менее 3 млн. руб»)’ class=’formula’>

Общий принцип методов классического прогнозирования

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

Внутри классического прогнозирования могут использоваться разные модели прогнозирования спроса от простых до сложных. Например:

  • по средним продажам (SMA и т.д.)
  • экспоненциальное сглаживание (простое и двойное) — ES
  • авторегрессия (1 и 2 порядка) – AR
  • Arima (AR+MA)
  • Метод Хольт-Винтерса
  • Нейронные сети и генетические алгоритмы (NN+GA)

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

Рассмотрим основные методы.

Примеры использования функции ПРЕДСКАЗ в Excel

Функция ПРЕДСКАЗ использует метод линейной регрессии, а ее уравнение имеет вид y=ax+b, где:

  1. Коэффициент a рассчитывается как Yср.-bXср. (Yср. и Xср. – среднее арифметическое чисел из выборок известных значений y и x соответственно).
  2. Коэффициент b определяется по формуле:

Пример 1. В таблице приведены данные о ценах на бензин за 23 дня текущего месяца. Согласно прогнозам специалистов, средняя стоимость 1 л бензина в текущем месяце не превысит 41,5 рубля. Спрогнозировать стоимость бензина на оставшиеся дни месяца, сравнить рассчитанное среднее значение с предсказанным специалистами.

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

Пример 1.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel145-2.png» class=»screen»>

Чтобы определить предполагаемую стоимость бензина на оставшиеся дни используем следующую функцию (как формулу массива):

  • A26:A33 – диапазон ячеек с номерами дней месяца, для которых данные о стоимости бензина еще не определены;
  • B3:B25 – диапазон ячеек, содержащих данные о стоимости бензина за последние 23 дня;
  • A3:A25 – диапазон ячеек с номерами дней, для которых уже известна стоимость бензина.

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector