Задачи по логистики. Решение задач с помощью ms excel 46

• Название: Решение задач с помощью ms excel 46
• Анкор: Задачи по логистики
• Дата: 14.05.2022
• Размер: 7.4 Mb.
• Имя файла: d0bcd0b5d182d0bed0b4d0b8d187d0bad0b0-d0b4d0bbd18f-d0bfd180d0bed0.docx
• Тип: Решение; #528805
• страница: 12 из 15

КРАТКОСРОЧНОЕ ПРОГНОЗИРОВАНИЕ В MICROSOFT EXCEL

С помощью Excel можно осуществлять прогнозирование объемов сбыта регулярно потребляемых товаров. Целям прогнозирования служат статистические функции Excel: ПРЕДСКАЗ, ТЕНДЕНЦИЯ, РОСТ, ЛИНЕЙН, ЛГРФПРИБЛ.

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

Y = а + bX + с^х.

Пусть а = 3, b = 4, с = 1,4. Таким образом функция будет иметь вид:

Y = 3 + 4Х + 1,4^х.

Таблица значений функции и ее график представлены на рис. 10.1.



Рис. 10.1. Таблица значений и график модельной функции.

ПРОГНОЗИРОВАНИЕ С ПОМОЩЬЮ ФУНКЦИЙ «ПРЕДСКАЗ» И «ТЕНДЕНЦИЯ»

Функция ПРЕДСКАЗ, основываясь на известных значе­ниях величин X и Y, вычисляет будущее значение величи­ны Y для заданного значения X. Для вычисления будуще­го значения в функции ПРЕДСКАЗ применяется модель линейной регрессии: Y=аХ+b. Воспользуемся функцией ПРЕДСКАЗ, чтобы вычислить значения Y для значений X 11-15. Мастер функции имеет вид, как показано на рис. 11.1. Первый параметр — это значение X, для ко­торого будет вычисляться прогнозное значение Y. В на­шем примере — это ячейка «А12».



Рис. 11.1. Мастер функции «ПРЕДСКАЗ».

Параметр «Известные_значения_у» — это массив или диапазон известных значений Y. В примере — это диапа­зон «В2»:«В11».

Параметр «Известные_значения_х» — это массив или диапазон значений X, для которых известны значения Y. В примере — это диапазон «А2»:«А11».

Таким образом, функция будет иметь вид:

ПРЕДСКАЗ(А12;$В$2:$В$11; $А$2:$А$11). Ячей­ки границ диапазонов заморожены, чтобы облегчить ко­пирование формулы. Результат прогноза и его графичес­кий вид представлены на рис. 11.2.



Рис. 11.2. Таблица значений и график фактических значений и прогнозных.

Такой же результат будет получен с помощью функ­ции ТЕНДЕНЦИЯ. Мастер функции имеет вид, как показано на рис. 11.3. Первый параметр функции — «Известные_ значения_у». В это поле следует внести диапазон извест­ных значений Y. Следующий параметр — «Известные_ значения_х». В поле этого параметра необходимо внести диапазон значений X, для которых известны значения Y. Значения X, для которых необходимо рассчитать прогноз­ные значения Y, указываются в поле «Новые_значения_х». В этом поле можно указать как одно значение X, так и ди­апазон значений. Последний параметр, «Константа», ука­зывает какого вида строить прямую аппроксимации. Если в этом поле указать значение «Истина» либо оставить его пустым, функция ТЕНДЕНЦИЯ вычислит значение по­стоянной «b». То есть прогнозная прямая будет иметь вид Y = аХ+b. Если в поле «Константа» указать значение «Ложь», функция ТЕНДЕНЦИЯ присвоит постоянной «b» значение «0», и прогнозная прямая будет иметь вид Y = аХ. В случае нашего примера функция будет иметь вид:

=ТЕНДЕНЦИЯ($В$2:$В$11;$А$2:$А$11;А12;ИСТИНА).



Рис. 11.3. Мастер функции «ТЕНДЕНЦИЯ».

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

Чтобы внести функцию как функцию массива необхо­димо проделать следующие действия:

■ Выделить в таблице диапазон ячеек для отображе­ния значений Y. Этот диапазон должен быть такой же длин­ны, как и соответствующий ему диапазон ячеек со значе­ниями X. В нашем примере это ячейки «С12»:«С16».

■ Внести в ячейку функцию. Функция вносится в этом случае вручную. То есть, не снимая выделение с ячеек, вам необходимо нажать клавишу =, затем набрать имя функции и ее параметры. После ввода функции кнопку Enter НЕ нажимать.

■ Завершить ввод функции, нажав на клавиатуре CTRL+SHIFT+ENTER. Выделенный диапазон ячеек бу­дет заполнен прогнозными значениями Y. Функция в ячей­ках будет заключена в фигурные скобки:

=ТЕНДЕНЦИЯ(В2:B11;А2:A11;А12:А16;ИСТИНА).
Внимание! При вводе параметров функции границы диапазонов сле­дует разделять знаком «двоеточие». Параметры функции разделяются знаком «точка с запятой».
ПРОГНОЗИРОВАНИЕ С ПОМОЩЬЮ ФУНКЦИИ «РОСТ»
Функция РОСТ вычисляет будущее значение величины Y, основываясь на предположении об их экспоненциальном росте. Для вычисления будущего значения функции РОСТ применяет модель вида: Y = dс^х.

Мастер функции РОСТ представлен на рис. 12.1. Па­раметры функции полностью идентичны параметрам функции ТЕНДЕНЦИЯ:

• 
  «Известные_значения_у» — поле для ввода диапазо­на известных значений Y.
  
• 
  «Известные_значения_х» — поле для ввода диапазо­на значений X, для которых известны значения У.
  

• 
  «Новые_значения_х» — поле для ввода диапазона зна­чений X, для которых необходимо рассчитать прогнозные значения Y.
  
• 
  «Конст» — поле, в котором указывается какого вида строить кривую аппроксимации. Если в этом поле указать значение «Истина», функция РОСТ вычислит значение по­стоянной «d». То есть прогнозная кривая будет иметь вид У = dс^х. Если в поле «Конст» указать значение «Ложь» либо оста­вить его пустым, функция РОСТ присвоит постоянной «d» значение «1», и прогнозная прямая будет иметь вид У = с^х.
  

Рис. 12.1. Мастер функции «РОСТ».

Функцию РОСТ, лучше вводить как формулу масси­ва. В нашем примере она имеет вид:

{=РОСТ($В$2:$В$11;$А$2:$А$11;А12:А16;ИСТИНА)}.

Таблица прогнозных значений Y, полученных с помо­щью функции РОСТ, и соответствующий им график пред­ставлены на рис. 12.2. Как видите, отклонения прогнозных значений от фактических довольно существенны. Точность прогноза можно повысить двумя способами. Во-первых, уменьшить горизонт прогнозирования, т.е. прогнозировать значения меньшего числа точек. Во-вторых, использовать для прогноза меньшее количество известных значений Y. Например, только три последние значения.



Рис. 12.2. Таблица значений и график фактических значений и прогнозных.