Корреляционно-регрессионный анализ экономических показателей с помощью компьютерных технологий

МИНИСТЕРСТВО ОБРАЗОВАНИЯ  РЕСПУБЛИКИ БЕЛАРУСЬ

УО «ВИТЕБСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНОЛОГИЧЕСКИЙ УНИВЕРСИТЕТ»

КОНТРОЛЬНАЯ РАБОТА № 2

по дисциплине «Компьютерные информационные технологии»

ВИТЕБСК 2011

Задание 1. Используя компьютерные технологии, провести корреляционно-регрессионный анализ исследуемых экономических показателей и построить регрессионную модель

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

функции категории «Статистические» ТП MS Excel;

инструменты надстройки Пакет Анализа ТП MS Excel;

встроенные функции библиотеки Stats СКМ Maple.

Вариант 3. Зависимость между показателями Х1, Х2, Х3 реализованной продукции и балансовой прибылью Y предприятий одной из отраслей промышленности характеризуется данными, представленными в таблице ниже.

По выборочным данным исследовать влияние факторов X1, X2 и X3 на результативный признак Y.

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

Оценив тесноту связи между исследуемыми факторами, построить многофакторную (однофакторную) линейную регрессионную модель вида Y=f(X1,X2,X3) или вида Y=f(X).

Оценить:

адекватность уравнения регрессии по значению коэффициента детерминированности R²;

значимость коэффициентов уравнения регрессии по t-критерию Стьюдента при заданном уровне доверительной вероятности p=0,05;

степень случайности связи между каждым фактором X и признаком Y (критерий Фишера).

Решение

Вводим исходные данные (рис. 1.1).

Рис. 1.1. Исходные данные

регрессия план детерминированность

Нанеся пары (X1;Y), (X2;Y), (X3;Y) на координатную плоскость, получаем так называемое корреляционное облако, вид которого позволяет предположить, что линейная зависимость между переменными не лишена оснований (рис. 1.2).

Рис. 1.2. Корреляционное облако

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

Меню Сервис, Анализ данных… В появившемся окошке выбираем Корреляция.

В диалоговом окне Корреляция (рис. 1.3) вводим Входной интервал - $A$1:$D$12. Отмечаем флажок Метки в первой строке, т.к. во входной интервал мы включили и заголовки столбцов. Параметры вывода укажем Выходной интервал $A$16.

Рис. 1.3. Диалоговое окно Корреляция

В результате в ячейках $A$16:$E$20 получим матрицу коэффициентов парной корреляции (см. рис. 1.4).

Рис. 1.4. Матрица коэффициентов парной корреляции.

Видно, что зависимая переменная Y имеет тесную прямую связь с переменными X1, X2 и X3.

Построим регрессионную модель. Меню Сервис, Пакет анализа… В появившемся окошке выбираем Регрессия. В диалоговом окне Регрессия (рис. 1.5) вводим: Входной интервал Y - $D$1:$D$12, Входной интервал X - $A$1:$C$12, Выходной интервал - $G$1. Результаты регрессии, оформленные в виде трех таблиц, будут представлены на текущем рабочем листе. Отмечаем флажок Метки и флажок График остатков.

Рис. 1.5. Диалоговое окно Регрессия

Рассмотрим полученные результаты регрессии (рис. 1.6). Первая таблица - Регрессионная статистика - содержит показатели регрессии. Коэффициент детерминации (R-квадрат), ячейка H5, показывает долю вариации результативного признака Y под действием изучаемых факторов X1, Х2, X3. Следовательно, около 85,5% вариации зависимой переменной учтено в модели и обусловлено влиянием изучаемых факторов.

Рис. 1.6. Результаты регрессии

Вторая таблица - Дисперсионный анализ - позволяет осуществить проверку значимости уравнения регрессии. Регрессионная и остаточная суммы квадратов показаны в ячейках I12 и I13 соответственно. Регрессионная сумма квадратов довольно существенно (в 6 раз) превосходит остаточную. Это говорит о том, что большая часть вариации производительности труда Y связана с факторами X1, Х2, X3.

В ячейке K12 показано расчетное значение F-критерия Фишера, а в ячейке H13 число степеней свободы, которое определяется как количество наблюдений (11) за вычетом параметров линейной регрессии (3) и единицы.

Проведем оценку значимости связи, сравнив табличное и расчетное значения F-критерия, который показывает, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет. Для этого можно использовать функцию =FРАСПОБР(0,05;H12;H13).

При уровне значимости 0,05 и числе степеней свободы 3 и 7 табличное значение F-критерия составляет около 4,34683. Расчетное значение 13,7761 больше, что свидетельствует о статистической значимости связи и, значит, уравнение регрессии следует считать адекватным.

Третья таблица - Регрессионная модель - показывает коэффициенты уравнения регрессии - b, m1, ... m3 (ячейки H17:H20 соответственно) и позволяет оценить из значимость. Оценка значимости этих коэффициентов производится с использованием t-критерия Стьюдента (ячейки J17:J20) и уровня значимости p<0,05 (ячейки K17:R20). Табличное значение t-критерия при уровне значимости 5% и степенях свободы n = 7 (функция =СТЬЮДРАСПОБР(0,05;H13)) составляет 2,364623, что меньше фактического значения t-критерия Стьюдента коэффициента при X3 и больше значений t-критерия коэффициентов при X1 и X2, т. е. не все коэффициенты существенны. Следовательно, факторы X1 и X2 из модели можно исключить.

Рассчитанное уравнение регрессии может быть представлено в виде:

Y = 3,358*X1 - 4,102*X2 + 7,115*X3 + 19,282

Рассчитать уравнение регрессии можно и с помощью функции ЛИНЕЙН.

Введем в ячейки A1:D12 данные. В ячейки A15:D19 рабочего листа Excel введем функцию в формате =ЛИНЕЙН(D2:D12;A2:C12;;ИСТИНА) и получим результат (см. рис. 1.6). Следует учесть, что формула вводится как формула массива. При этом способе расчета будет представлена не вся дополнительная статистика по регрессии. Верхняя строка (ячейки A15:D15) - коэффициенты уравнения регрессии m3 … m1 и b, ячейка A17 - коэффициент детерминированности R-квадрат = 0,855157, ячейка A18 - критерий Фишера F = 13,7761. Разделив значения в ячейках A15:D15 на значения стандартной ошибки (ячейки A16:D16) получим t-статистики.

Рис. 1.6. Результат использования функции ЛИНЕЙН

Таблицы с результатами расчетов и формулами в ячейках приведены на стр. 14-15.

Рассчитать уравнение регрессии можно и с помощью функций библиотеки Stats СКМ Maple.

> stats[fit, leastsquare[[x1, x2, x3, y]]]([[1.2, 2.8, 3.4, 4.6, 5.2, 6.4, 7.8, 8.3, 9.1, 9.9, 10.5], [1.2, 1.8, 2, 2.5, 3, 3.2, 3.5, 4.9, 5, 6.2, 7.3], [2, 3, 4, 3, 2, 6, 5, 7, 8, 12, 9], [20, 50, 57, 63, 22, 75, 60, 81, 87, 102, 95]]); = 19.28215417 + 3.358180454 x1 - 4.101593778 x2 + 7.115326625 x3

Все введенные выражения и полученные в рабочем документе СКМ Maple результаты решений приведены на стр. 16.

Задание 2. Используя компьютерные технологии, решить задачи линейного программирования

а) Задача оптимального планирования производства.

Условие. Для производства двух видов изделий А и В используется три типа технологического оборудования. На производство единицы изделия А оборудование первого типа используется а₁ часов, оборудование второго типа - а₂ часов, оборудование третьего типа - а₃ часов. На производство единицы изделия В оборудование первого типа используется в₁ часов, оборудование второго типа - в₂ часов, оборудование третьего типа - в₃ часов.

На изготовление всех изделий администрация предприятия может предоставить оборудование первого типа не более чем на t₁ часов, оборудование второго типа не более чем на t₂ часов, оборудование третьего типа не более чем на t₃ часов. Прибыль от реализации единицы готового изделия А составляет α руб., а изделия В - β руб.

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

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

надстройку Поиск решения ТП MS Excel;

- библиотеки Simpleх и Optimization СКМ Maple.

Решение

Разместим таблицу с исходными данными в ячейках A1:F7 и выполним необходимые предварительные расчеты (рис. 2.1), т.е. в строке 6 будут формулы: =СУММПРОИЗВ(B3:B4;$F$3:$F$4) - в ячейке B6; =СУММПРОИЗВ(С3:С4;$F$3:$F$4) - в ячейке С6; =СУММПРОИЗВ(D3:D4;$F$3:$F$4) - в ячейке D6; =СУММПРОИЗВ(E3:E4;$F$3:$F$4) - в ячейке E6.

Рис. 2.1. Исходные данные

Необходимо отыскать решение задачи, приняв следующие условия:

итоговая прибыль (ячейка E6) - max;

количество изделий (ячейки F3:F4) >= 0, целое значение;

затраты времени по типам оборудования (ячейки B6:D6 <= B7:D7);

изменяемые ячейки - F3:F4 - количество изделий для оптимального производства.

Окончательный вид формулировки задачи представлен на рис. 2.2.

Рис. 2.2. Формулировка задачи

Итоговый результат показан на рис. 2.3.

Рис. 2.3. Результаты Поиска решения

Все без исключения требования задачи выполнены.

Дополнительно можно сформировать отчет по результатам (рис. 2.4).

Рис. 2.4. Отчет по результатам

Таблица с результатами расчетов и формулами в ячейках приведена на стр. 17.

Решим задачу, используя библиотеку СКМ Maple Simplex.

Экономико-математическая модель задачи имеет следующий вид.

Целевая функция:

f := 6*A + 2*B.

Ограничения:

A, B - неотрицательные (> =0);

A, B - целочисленные;

6*А + 2*В <= 600;

4*A + 3*B <= 520;

3*A + 4*B <=600.

Решение в СКМ Maple будет таким:

> restart;

> with(simplex);

[basis, convexhull, cterm, define_zero, display, dual, feasible, maximize, minimize, pivot, pivoteqn, pivotvar, ratio, setup, standardize]

> f := 6*A+3*B;

A + 3 B

> limity := {3*A+4*B <= 600, 4*A+3*B <= 520, 6*A+2*B <= 600};

{3 A + 4 B <= 600, 4 A + 3 B <= 520, 6 A + 2 B <= 600}

> result := maximize(f, limity, NONNEGATIVE);

{A = 76, B = 72}

> TCell := subs(result, f);

672

Все введенные выражения и полученные в рабочем документе СКМ Maple результаты решений приведены на стр. 18.

Решение в системе СКМ Maple с использованием библиотеки Optimization будет таким:

> restart;

> with(Optimization);

[ImportMPS, Interactive, LPSolve, LSSolve, Maximize, Minimize, NLPSolve, QPSolve]

> f := 6*A+3*B;

A + 3 B

> limity := {3*A+4*B <= 600, 4*A+3*B <= 520, 6*A+2*B <= 600};

{3 A + 4 B <= 600, 4 A + 3 B <= 520, 6 A + 2 B <= 600}

> LPSolve(f, limity, assume = {integer, nonnegative}, maximize);

[672, [A = 76, B = 72]]

Все введенные выражения и полученные в рабочем документе СКМ Maple результаты решений приведены на стр. 19.

б) Задача оптимизации плана перевозок (транспортная задача).

Условие. Имеются n пунктов производства и m пунктов распределения продукции. Стоимость перевозки единицы продукции с i-го пункта производства в j-й центр распределения c_ij приведена в таблице, где под строкой понимается пункт производства, а под столбцом - пункт распределения. Кроме того, в этой таблице в i-й строке указан объем производства в i-м пункте производства, а в j-м столбце указан спрос в j-м центре распределения. Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.

Вариант 3.

В качестве инструментария решения использовать (на выбор из перечисленных ниже):

надстройку Поиск решения ТП MS Excel;

библиотеку Simplex СКМ Maple;

библиотеку Optimization СКМ Maple.

Решение

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

Разместим исходные данные (рис. 2.5). Ячейки B2:E5 содержат стоимость перевозки единицы продукции. Ячейки B8:E11 отведены под значения неизвестных. Ячейки G8:G11 содержат объемы производства, а ячейки B13:E13 - объемы потребления.

В ячейке B15 находится функция цели =СУММПРОИЗВ(B2:E5;B8:E11). Ячейки F8:F11 содержат формулы для расчета объемов производства: =СУММ(B8:E8); =СУММ(B9:E9); =СУММ(B10:E10); =СУММ(B11:E11). А ячейки B12:E12 содержат формулы для расчета объема потребления: =СУММ(B8:B11); =СУММ(C8:C11); =СУММ(D8:D11); =СУММ(E8:E11).

Рис. 2.5. Исходные данные

В диалоговом окне Поиск решения (рис. 2.6) задаем целевую ячейку, изменяемые ячейки и ограничения.

Рис. 2.6. Диалоговое окно Поиск решения

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

Рис. 2.7. Результаты решения.

Таблица с результатами расчетов и формулами в ячейках приведена на стр. 20.

регрессия оптимизация план детерминированность

Приложения

Задание 1. Результаты регрессии, полученные с помощью Анализа данных

Задание 1. Результаты регрессии, полученные с помощью функции ЛИНЕЙН.