1 1 Расчеты прогнозных данных в ms excel




Скачать 176.28 Kb.
Название1 1 Расчеты прогнозных данных в ms excel
Дата публикации03.08.2013
Размер176.28 Kb.
ТипДокументы
lit-yaz.ru > Математика > Документы


1.1.1.1.Расчеты прогнозных данных в MS Excel


Использование линии тренда. Для того, чтобы построить линию тренда необходимо сначала построить точечную диаграмму того же самого процесса. Возвратимся к задаче 2.4 (рис.2.100) и построим точечную диаграмму (рис. 2.106).

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




а)



б)

Рис. 2.107
На вкладке Тип (рис. 2.107а) выбирается тип апроксимации данных (в нашем случае – линейная, так как и разделе 2.2.5.2), а на вкладыше Параметры (рис. 2.107б) – необходимо установить флажки Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R2) (коефициент детерминации R2). Вид построенной линии тренда показан на рис. 2.108. Из рисунка видно, что уравнение регрессии не отличается от полученного в разделе 2.2.5.2.



Рис. 2.108

Если в качестве типа апроксимации избрать полином второй степени (параболу), то построенная линия тренда будет иметь вид рис. 2.109.



Рис. 2.109

Из этого рисунка видно, что полученные в данном случае результаты будут более адекватны, чем полученные ранее.

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



Рис. 2.110

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

ПРЕДСКАЗ(X;изв_значY;изв_значX),

где X – элемент независимой переменной для которой вычисляется прогнозное значение; изв_значY – известные значения зависимой переменной; изв_значX - известные значения независимой переменной. Пример заполнения параметров функции приведен на рис. 2.111, а пример расчета на рис. 2.112.



Рис. 2.111



а)



б)

Рис. 2.112

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

ТЕНДЕНЦИЯ(изв_значY; изв_значX; нов_значX;константа),

где изв_значYмножество известных значений зависимой переменной; изв_значX – множество известных значений независимой переменной; нов_значX – новые значения независимой переменной; константа – логическая константа, если она принимает значения ИСТИНА, то свободный член уравнения регрессии рассчитывается обычным образом, в противоположном случае он равен 0, то есть график функции линейной регрессии будет проходить через начало координат. Пример использования функции в случае линейной зависимости приведен на рис. 2.113 и 2.114.



Рис. 2.113

а)



б)



Рис. 2.114

Внимание! Функция ТЕНДЕНЦИЯ() может быть использована не только для расчета одного прогнозного значения, но и для расчета массива значений, как это приведено на рис. 2.114. Необходимо помнить, что после заполнения одной ячейки (C10 на рис. 2.114) ее надо перевести в режим редактирования (клавиша F2), потом выделить массив ячеек (C10:C13) и заполнить его комбинацией клавиш CTRL-SHIFT-ENTER.

Пример использования функции ТЕНДЕНЦИЯ() в случае неявной линейной регрессии показан на рис. 2.115, 2.116. Обратите внимание, что столбец E (рис. 2.116) содержит квадраты независимой величины, а сама независимая величина расположена в столбце D, так как они должны быть расположены рядом. Выбор массивов для заполнения параметров функции ясен из рис. 2.115. Порядок работы с массивами данных пояснен выше. Если сравнить полученные результаты с полученными раньше – ясно, что они совпадают с результатами рис. 2.110 (использование линии тренда).


Рис. 2.115

Выше было подчеркнуто, что использования всех описанных в данном разделе методов и функций возможно лишь тогда, когда проверена адекватность принятых апроксимаций. Проведение проверки адекватности с помощью раздела Регрессия окна Анализ данных (рис. 2.92) в случае линейной апроксимации уже было приведено выше. Если функция ТЕНДЕНЦИЯ() используется для неявной линейной регрессии (рис. 2.115, 2.116), то проверка адекватности должна проводится также, как в случае множественной линейной регрессии, причем в нашем случае есть две независимые переменные X и X2 (X2). Результаты построения уравнения регрессии и проверки адекватности приведенны на рис. 2.117 и 2.118.

1.2.Заключение


Таким образом, ППП Statistica и MS Excel являются мощными программными средствами для осуществления статистического анализа и прогноза в решении экономических, технических, математических задач, и т.п.. Если Statistica - более специализированная и профессиональная программа, то MS Excel более распространен и включает, кроме того, много средств, реализующих не только функции статистического анализа, о чем речь пойдет в следующем разделе.

а)



б)



Рис. 2.116



Рис. 2.117


Рис. 2.118
^

2.РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ В MS EXCEL

2.1. Постановка задачи оптимизации


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

Z=f(x1, x2, ..., xn), ( 3.1 )

при выполнении некоторой системы ограничений:

gi(x1, x2, ..., xn), и=1,…,m... ( 3.2 )

Такие задачи называют оптимизационными задачами. Если и функция и ограничения имеют линейный характер, то задачу относят к задачам линейного программирования и формулируют следующим образом: найти минимум (или максимум) функции цели

( 3.3 )

при выполнении ограничений

( 3.4 )

где

F(x)

- функция цели;




ci

- коэфициенты функции цели;




xi

- аргументы функции цели;




n

- количество аргументов функции цели;




aij

- коэфициенты ограничений;




bj

- правые части ограничений;




m

- количество ограничений.

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

Известны n поставщиков какого-нибудь товара и m потребителей того же самого товара. Запасы товаров у поставщиков составляют PS1, PS2, …, PSn, а потребность у потребителей составляет PD1, PD2, …, PDm... Известны также цены aij на перевозку этого товара от i-го поставщика к j-го потребителю. Необходимо найти такие объемы перевозок i-го поставщика к j-му потребителю, которые бы минимизировали затраты на перевозку.

Если выполняется равенство

, ( 3.5 )

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

Рассмотрим порядок решения этих типов задач с помощью MS Excel.
^

2.2.Задача линейного программирования

2.2.1.Постановка задачи


Задача 3.1. Предприятие планирует производство продукции трех артикулов. Для производства необходимы: сырье, газ, вода, электроэнергия. Цены реализации продукции и цены ресурсов приведены в таблице 3.1. Технологический процесс состоит из ручной и машинной обработки. Доля ручного и машиного труда для каждого артикула приведены в таблице 3.2. Потребность в ресурсах на 1 тонну продукции приведены в таблице 3.3. Стоимость рабочего времени рабочего составляет 2 грн/час, механика – 3 грн/час. Рабочее время в день составляет 8 часов, в месяц – 22 рабочих дня. Найти оптимальный план производства и приобретения ресурсов, который доставляет максимум прибыли, с учетом того, что предприятием согласованы контракты на производство 100 т продукции 1-го артикула, 200 т продукции 2-го артикула и 270 т продукции 3-го артикула.




Таблиця 3.1

Наименование

Цена, грн.

Продукция 1-го артикула (за т.)

580,00




Продукция 2-го артикула (за т.)

590,00




Продукция 3-го артикула (за т.)

400,00




Сырье (за т.)

74,60




Электроэнергия (за квт.час)

0,123




Газ (за тыс. куб.м)

4,50




Вода (за тыс.л.)

1,67







Таблица 3.2

Продукция

^ Ручной труд

Машинный труд

Продукция 1-го артикула

50%

50%

Продукция 2-го артикула

40%

60%

Продукция 3-го артикула

60%

40%




Таблица 3.3

Ресурс

^ 1-й артикул

2-й артикул

3-й артикул

Сырье, т.

1,30




1,45




1,40




Электроэнергия, квт.час

870,00




960,00




930,00




Газ, тыс. куб.г

9,65




8,89




7,96




Вода, тыс. л

0,65




0,87




0,78




Машинное время, час

12,00




11,00




9,00



^

2.2.2.Математическая модель


В первую очередь, определим перечень переменных математической модели:

x1 – количество продукции 1-го артикула, т;

x2 – количество продукции 2-го артикула, т;

x3 – количество продукции 3-го артикула, т;

x4 – количество сырья, т;

x5 – количество элекроэнергии, квт.час;

x6 – объем газа, тыс. куб.м;

x7 – объем воды, тыс. л;

x8 – количество рабочих, люд.;

x9 – количество механиков, чел.

Прибыль ( F ) определяется как разница между доходами ( Д ) и затрата-
ми ( В ).

Д = 580x1 + 590x2 + 400x3 ( 3.6 )

В = 74,6x4 + 0,123x5 + 4,5x6 + 1,67x7 + 8222x8 + 8223x9 (3.7 )

Вычитая ( 3.7 ) из ( 3.6 ) получим функцию цели ( 3.8 ):

F=580x1 + 590x2 + 400x3 - 74,6x4 - 0,123x5 -4,5x6 -1,67x7 – 352x8 – 528x9 ( 3.8 )

Система ограничений состоит из следующих неравенств:

1) Ограничения по сырью

1,30x1 + 1,45x2 + 1,40x3x4 ( 3.9 )

2) Ограничения по электроэнергии

870x1 + 960x2 + 930x3x5 ( 3.10 )

3) Ограничения по газу

9,65x1 + 8,89x2 + 7,96x3x6 ( 3.11 )

4) Ограничения по воде

0,65x1 + 0,87x2 + 0,78x3x7 ( 3.12 )

5) Ограничения по машиному времени

12x1 + 11x2 + 9x3  176x9 ( 3.13 )

6) Ограничения по времени ручной работы

12x1 + 4011/60x2 + 609/40x3  176x8* ( 3.14 )

7) Ограничения по производству продукции 1- артикула

x1 = 100 ( 3.15 )

8) Ограничения по производству продукции 2- артикула

x2 = 200 ( 3.16 )

9) Ограничения по производству продукции 3- артикула

x3 = 270 ( 3.17 )

10) xi  0, i=1…9 ( 3.18)
Преобразуя неравенства ( 3.9 ) – ( 3.14 ) получим следующую задачу линейного программирования: максимизировать функцию цели

F=580x1+590x2+400x3-74,6x4-0,123x5-4,5x6-1,67x7 – 352x8 – 528x9

при выполнении системы ограничений

( 3.19)

и неотрицательности переменных xi  0, i=1...9.
^

2.2.3.Размещение математической модели в MS Excel и решение
задачи


На рис. 3.1 приведен вид таблицы с введенной моделью.

Ячейки A13:I13 содержат переменные задачи, в ячейках A2:I10 коэффициенты системы ограничений, а в ячейках K2:K10 – правые части системы ограничений.

Далее в ячейку J15 вводится формула, отображающая функцию цели, а в ячейки J2:J10 – формулы, отображающие левые части системы ограничений (3.19).

После ввода модели вызывается окно Поиск решения из меню Сервис (рис.3.2). В качестве целевой ячейки выбирается J15 и устанавливается селектор Равной максимальному значеню, так как решается задача максимизации. В поле Изменяя ячейки вводятся адреса ячеек, которые содержат переменные задачи, то есть A13:I13. Кнопка Добавить разрешает ввести соотношения системы ограничений (рис. 3.2, 3.3). Кнопка Параметры вызовет окно (рис. 3.4), в котором надо установить опции линейности модели и неотрицательности переменных. После этого надо нажать кнопку Выполнить. Если задача имеет решение, то выводится окно, приведенное на рис. 3.5.

а)



б)



Рис. 3.1


Рис. 3.2



Рис. 3.3



Рис.3.4



Рис.3.5

А таблица с задачей имеет вид рис.3.6.



Рис.3.6

Таким образом, имеем следующие результаты (табл. 3.4).

Таблица 3.4

Продукция и ресурсы

Значения

Количество продукции 1-го артикула

100




Количество продукции 1-го артикула

200




Количество продукции 1-го артикула

270




Сырье, т

798




Электроэнергия, квт.ч

530100




Газ, тыс. куб.г

4892,2




Вода, тыс.л

449,6




Количество рабочих

36




Количество механиков

33




Прибыль будет составлять 104667 гр.од.

Кроме вывода результатов в таблице при решении задачи линейного программирования имеют значение отчеты (см. рис. 3.5, 3.7). Для задачи 3.1 анализ отчетов не даст много информации, так как, в силу условий, задачи фиксированными являются объемы производства, цены, не имеется ограничений на количество ресурсов. Для рассмотрения значения информации, включаемой в отчеты, рассмотрим задачу 3.2.



Рис.3.7.
^

2.2.4.Анализ результатов


Задача 3.2. Фирма вырабатывает офисные кресла двух типов. Цена реализации составляет для первого типа 550 грн, для второго типа 620 грн. Нормы росхода материалов и их запасы приведены в таблице 3.5. Рассчитать план производства, доставляющий максимум дохода.

Таблица 3.5

Материал

Кресло 1-го типа

^ Кресло 2-го типа

Запас

Лист стальной

0,7 м2

0,65 м2

15 м2

Прокат стальной

12,6 кг

10,9 кг

300 кг

Ткань

5 г

2 г

100 г

Кожа

0 г

4 г

70 г

Фанера

1,5 м2

1,5 м2

40 м2

Наполнитель

5 кг

6 кг

150 кг

Математическая модель задачи 3.2 достаточно простая и имеет следующий вид:

Найти максимум функции цели

F = 550x1 + 620 x2 ( 3.20 )

при ограничениях
( 3.21 )

и неотрицательных переменных xi, i=1,…,2

Ограничения соответствуют таблице 3.5. На рис. 3.8 представлен вид модели, введенной в рабочем листе MS Excel. Окно Поиск решения приведено на рис. 3.9. После получения решения рабочая таблица имеет вид рис. 3.10, а листы отчетов по результатам и устойчивости решения приведены на рис. 3.11.

В соответствии с рис. 3.10 и 3.11а оптимальный план производства составляет 5 кресел 1-го типа и 18 кресел 2-го типа. Доход при этом будет составлять 13698 грн.

Рассмотрим некоторую информацию выводимую в отчете об устойчивости решения (рис.3.11б):

Для оптимального значения каждой переменной приводятся параметры ^ Допустимое увеличение и Допустимое уменьшение, которые показывают допустимые границы изменения коэффициентов функции цели (в данном случае цены на продукцию). В этих границах оптимальный план (значения переменных) будет неизменным. Так, если цену на кресла 1-го типа увеличить до 667 грн, все равно оптимальный план производства этой продукции будет составлять 5 кресел. Если цена будет выше, то необходимо решить задачу еще раз.

Таблица анализа ограничений включает столбец ^ Теневая цена. Этот параметр показывает, как изменится оптимальное значение функции цели при изменении запаса ресурса (правой части ограничения) на единицу. Теневая цена возникает только для тех ресурсов, которые полностью исчерпаны. Иначе говоря, теневая цена показывает максимально возможную цену, по которой можно покупать дополнительное количество ресурса. А количество дополнительной покупаемого ресурса показано в столбце Допустимое увеличение. Это вытекает из того, что такие значения теневой цены допустимы лишь в диапазоне, обозначенном столбцами Допустимое увеличение и Допустимое уменьшение. Так из рис. 3.11а следует, что можно дополнительно купить 2 м2 листа стального по цене не выше 785 грн, а также 22 м2 фанеры по цене не выше 27 грн.


а)



б)



Рис. 3.8



Рис.3.9


Рис.3.10

а)



б)



Рис. 3.11

^

2.3. Транспортная задача

2.3.1.Постановка задачи


Задача 3.3. Некоторая торговая фирма “Компаньон” получила заказ на доставку товаров со складов в городах Донецк, Харьков, Днепропетровск на предприятия в городах Луганск, Запорожье, Мариуполь и Херсон. Запасы на складах составляют: Донецк – 120т, Харьков – 100т, Днепропетровск – 180т. Потребности на предприятиях составляют: Луганск – 150т, Запорожье – 100т, Мариуполь –110т и Херсон – 40т. Стоимость перевозки составляет: из Донецка в Луганск - 5грн/т, у Запорожье - 7 грн/т, Мариуполь – 4 грн/т и Херсон - 10 грн/т; из Харькова в Луганск – 7 грн/т, у Запорожье - 7 грн/т, Мариуполь – 9 грн/т и Херсон - 12 грн/т; из Днепропетровска в Луганск – 12 грн/т, в Запо-рожье - 3 грн/т, Мариуполь – 6 грн/т и Херсон - 7 грн/т. Найти оптимальный план перевозок.

Так как 120 + 100 + 180 = 150 + 100 +110 + 40, то задача является сбалансированной, т.е закрытой. Поэтому для ее решения достаточно ввести модель в рабочую таблицу MS Excel и решить с помощью Поиска решения.
^

2.3.2.Математическая модель задачи


Построим математическую модель задачи, для чего введем следующие обозначения: xij – объем перевозки со склада i на предприятие j; cij – стоимость перевозки из склада i на предприятие j; ai – запас товара на складе i; bj – потребность в товарах на предприятии j. Математическая модель формулируется следующим образом:

минимизировать функцию цели

( 3.22 )

при ограничениях

( 3.23 )

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

2.3.3.Размещение математической модели в MS Excel и решение
задачи


Вид модели, введенной в рабочую таблицу приведен на рис.3.12а. Формулы, отображающие математическую модель показаны на рис. 3.12б.

На рис. 3.13. показанное окно Поиск решения с введенными параметрами поиска. Порядок решения задачи и окно параметров поиска решения не отличаются от задачи линейного программирования (см. выше). Результат решения задачи показан на рис. 3.14.

Из рис. 3.14 видно, что минимальные затраты на перевозку составляют
2050 грн. Оптимальные объемы перевозок: из Донецка в Луганск – 50т, в Мариуполь – 70т; из Харькова в Луганск – 100 т; из Днепропетровска в Запорожье – 100т, в Мариуполь – 40т, в Херсон – 40т.

Таким образом, со всех складов товары вывезены, потребности всех потребителей удовлетворены.


а)









б)



Рис.3.12




Рис. 3.13


Рис. 3.14
^

2.3.4.Несбалансированная модель


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

В первом случае потребности фиктивного потребителя равняются перегрузке складов, а стоимость перевозки – стоимости складирования.

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

Рассмотрим модификацию задачи 3.3.

Задача 3.3а. Некоторая торговая фирма “Компаньон” получила заказ на доставку товаров со складов в городах Донецк, Харьков, Днепропетровск на предприятия в городах Луганск, Запорожье, Мариуполь и Херсон. Запасы на складах составляют: Донецк – 120т, Харьков – 100т, Днепропетровск – 180т. Потребности на предприятиях составляют: Луганск – 150т, Запорожье – 100т, Мариуполь –110т и Херсон – 100т. Стоимость перевозки составляет: из Донецка в Луганск - 5грн/т, у Запорожье - 7 грн/т, Мариуполь – 4 грн/т и Херсон - 10 грн/т; из Харькова в Луганск – 7 грн/т, у Запорожье - 7 грн/т, Мариуполь – 9 грн/т и Херсон - 12 грн/т; из Днепропетровска в Луганск – 12 грн/т, в Запорожье - 3 грн/т, Мариуполь – 6 грн/т и Херсон - 7 грн/т. В контракте фирмы с предприятиями предусмотрены штрафные санкции за недопоставку товаров: в Луганск – 5 грн/т, в Запорожье - 4 грн/т, Мариуполь – 3 грн/т и Херсон - 3 грн/т. Найти оптимальный план перевозок.

Так как 120 + 100 + 180 = 400 < 150 + 100 + 110 +100 = 460, то задача является несбалансированной, причем дефицит составляет 60т.

На рис. 3.15 – 3.17 приведена информация, отображающая процесс решения несбалансированной задачи. Из них следует, что объем перевозки не отличаются от задачи 3.3. Но заявка предприятия в Херсоне невыполнена на 60т, оптимальные затраты на перевозку увеличены и составляют 2230 грн, то есть на 180 грн больше, чем в задаче 3.3. Это различие составляет штрафные санкции за недопоставку товара (60·3=180).


Рис.3.16



Рис.3.17

ЛІТЕРАТУРА

  1. Решение прикладных экономических задач с применением специализированного программного обеспечения: Учеб. пособие / Л.Е.Шайхет, Э.И.Славенко, С.А.Тернов / Под общ. ред. Л.Е.Шайхета. – Донецк:ДонГАУ, 2001. – 115с.

  2. Боровиков В.П. Популярное введение в программу Statistica. – М.: Компьютер Пресс, 1998. – 267с.

  3. Карлберг Конрад. Бизнес-анализ с помощью Excel.: Пер. с англ.- К.:Диалектика, 1997.- 448с.

  4. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. – СПб.: БХВ – Санкт-петербург, 1999. – 336с.




* Коэффициэнты неравенства ( 3.14 ) следуют из табл. 3.2

Добавить документ в свой блог или на сайт

Похожие:

1 1 Расчеты прогнозных данных в ms excel iconТеоретическая часть Организация базы данных Excel
...

1 1 Расчеты прогнозных данных в ms excel iconОтчет по учащемуся 225 Подсчет оплаченных месяцев 227
Передача информации о фирмах с листа Microsoft Excel в базу данных на сервере 189

1 1 Расчеты прогнозных данных в ms excel iconКурсовая работа по информатике на тему: «Обзор встроенных функций ms excel»
В microsoft Excel имеется целый ряд встроенных математических функций, позволяющих легко и быстро выполнять различные специализированные...

1 1 Расчеты прогнозных данных в ms excel icon«Использование баз данных и субд для обработки экономической информации»
В состав банка данных входят одна или несколько баз данных, справочник баз данных, субд, а также библиотеки запросов и прикладных...

1 1 Расчеты прогнозных данных в ms excel iconУчебная программа курса или дисциплины «Технологии баз данных»
Цель дисциплины “Технологии баз данных” ознакомление слушателей с организацией, принципами построения и функционирования современных...

1 1 Расчеты прогнозных данных в ms excel iconТема : Поиск и сортировка информации в базах данных
В фрагменте базы данных представлены сведения о родственных отношениях. Определите на основании приведенных данных фамилию и инициалы...

1 1 Расчеты прогнозных данных в ms excel iconМодель динамической оптимизации режимов мониторинга и диагностирования буровой скважины
Тоимостные затраты (Zз) на проведение гис. Выходом модели является скорость V движения скважинного прибора и параметры регистрации...

1 1 Расчеты прогнозных данных в ms excel iconКурсовая работа по дисциплине: «Информатика» на тему: «Настольные субд»
Наиболее популярной формой представления данных в компьютере является база данных – множество взаимосвязанных данных, структурированных...

1 1 Расчеты прогнозных данных в ms excel iconКурсовая работа по Дисциплине: Базы данных Тема: Разработка реляционной базы данных
Необходимо разработать базу данных для web-сайта муниципального образования Фокинское сельское поселение Чайковского муниципального...

1 1 Расчеты прогнозных данных в ms excel iconЛабораторная работа №5 Работа с реляционной базой данных в Microsoft Office Access 2007
Цель работы: Научиться создавать многотабличную реляционную базу данных в субд ms office Access 2007, вносить данные, организовывать...



Образовательный материал



При копировании материала укажите ссылку © 2013
контакты
lit-yaz.ru
главная страница