Математическое моделирование в управлении (стр. 2 из 5)
§ 1.2 Проверка закона распределения
Предварительный анализ статистических данных заключается в проверке соответствия их предположению о нормальном распределении параметров, для чего строю гистограмму и определяю выборочные числовые характеристики. Для построения гистограммы выполняю такую последовательность действий:
размещаю на рабочем листе Excel статистические данные наблюдений (без выбросов);
Сервис – Анализ данных – Гистограмма (рис.1);
Рис.1.Выбор инструмента анализа.
- в появившемся диалоговом окне Гистограмма ввожу в поле Входные данные интервал (диапазон) ячеек, содержащий исходные данные, и отмечаю поле Метки, т.к., таблица данных имеет заголовки;
- ввожу в поле Параметры выхода адрес ячейки, с которой должны размещаться выходные данные (выходной интервал) и щелкаю пункт Вывод графика;
- OK.
Гистограммы строю для всех признаков статистических данных и сравниваю их с кривой нормального распределения с целью убедиться, что закон распределения каждого признака близок к нормальному, как на приведенной гистограмме.
Числовые характеристики для всех признаков оцениваются по выборке с помощью инструмента анализа Описательная статистика., вызов которого осуществляется аналогично (см. рис.1 ). В появившемся диалоговом окне Описательная статистика ввожу таким же образом Входные данные и Параметры вывода, только вместо пункта Вывод графика отмечаю пункт Итоговая статистика.
Результаты применения инструмента Описательная статистика к данным наблюдений по результативному признаку Y1 и выбранным факторным признакам приведены на листе Excel под названием «Проверка закона распределения».
Как видно, результаты Описательной статистики дают возможность оценить справедливость предположения о нормальном распределении признаков: эксцесс и асимметричность невелики, хотя и отличаются от 0. Нормальный закон распределения факторных признаков подтверждается еще и тем, что значения медианы и моды у них совпадают или близки.
§ 1.3 Корреляционный анализ
Предварительный анализ тесноты взаимосвязи параметров многомерной модели осуществляю по оценке корреляционной матрицы генеральной совокупности X по наблюдениям. Для этого использую инструмент Анализ данных в соответствии со следующим алгоритмом:
- размещаю на рабочем листе Excel статистические данные в столбцах с соответствующими заголовками (именами переменных);
- Сервис – Анализ данных – Корреляция;
- в появившемся диалоговом окне Корреляция в соответствующие поля ввожу с помощью мыши входные данные и параметры вывода (см. рис.3 );
- после щелчка мышью по кнопке OK на рабочем листе появится матрица, содержащая оценки парных коэффициентов корреляции.
Отбираю для дальнейшего анализа пары переменных, имеющие наибольшие значения парных коэффициентов корреляции
(
0,4 ), учитывая, что чем меньше коэффициент rij , тем слабее их связь. Такими парами в приведенном примере (рис.3) являются: Y1–X11; Y1–X12; X11–X13; X12–X13; X17–X8.Проверяю значимость коэффициентов корреляции на уровне
= 0,05. Поскольку объем выборки для всех признаков одинаков и равен 53, критическое значение rкр для всех пар будет одинаково и в соответствии с таблицей Фишера–Иейтса rкр = rтабл (0,05;53)< rтабл(0,05;50) = 0,273. Поскольку для всех коэффициентов выполняется неравенство 
> rкр , коэффициенты корреляции всех отобранных пар признаков значимо отличаются от нуля, что подтверждает связь между ними.Дальнейший анализ статистических данных зависит от размерности принимаемой модели. Простейший вариант – двумерная модель. Учитывая, что в приведенном примере Y1 –результирующий признак, определяющий индекс производительность труда, входит в две пары , следует рассмотреть трехмерную модель Y1–X11–X12,
где X11 – среднегодовая численность ППП, а X12 – среднегодовая стоимость основных производственных фондов(ОПФ). В остальных парах следует определить зависимости между X11 и X13, X12 и X13 , X17 и X8. Здесь X5 –удельный вес рабочих в составе промышленно–производственного персонала, X6 –удельный вес покупных изделий, X7 – коэффициент сменности оборудования.
Таким образом, для математической модели задачи выбора оптимального управления деятельностью предприятия с учетом указанных показателей следует установить зависимости: Y2 = F( X4,X8) – целевая функция;
X6 = φ(X4); X8 = φ(X7); X5 = φ(X7) – ограничения.
Рис.3.Анализ парной корреляции.
§1.4 Регрессионный анализ двумерной модели
В среде Excel для двумерного случая линейной регрессии предусмотрено несколько инструментов : статистические функции (КОРРЕЛ, ЛИНЕЙН, ТЕНДЕНЦИЯ и др.) ; инструмент Регрессия надстройки Пакет анализа ; графические средства при работе с диаграммой – построение линии тренда.
С помощью Пакета анализа можно получить искомую информацию , следуя такому алгоритму:
- разместить на рабочем листе Excel в двух смежных столбцах с соответствующими заголовками статистические данные по двум признакам, подлежащим исследованию (например, X4 и X6);
- Сервис – Анализ данных – Регрессия ;
- в появившемся диалоговом окне Регрессия ввести входные данные в поля Входной интервал Y (X6) и Входной интервал X (X4) и щелкнуть по полю Метки, чтобы заголовки не вошли в интервалы данных;
- ввести параметры вывода в поле Выходной интервал : адрес левого верхнего угла таблицы результатов или щелкнуть поле Новый рабочий лист для вывода на другой лист (см. рис.4);
- для наглядности можно вывести график, щелкнув по полю График подбора ;
- OK.
Рис.4.Работа с диалоговым окном Регрессия.
Результат работы инструмента Регрессия приведен на рис.5. Итак, выборочное уравнение линейной регрессии X6 на X4 имеет вид:
Выходная таблица содержит коэффициент детерминации R2 = 0,368802, что означает, что полученная модель приблизительно на 37% отражает зависимость удельного веса покупных изделий от трудоемкости единицы продукции. Стандартная ошибка (отклонение результата)
= 0,118415 означает, что 68% реальных значений результирующего признака x6 находится в диапазоне 
0,118415 от линии регрессии. Это следует из того, что условные распределения нормально распределенной генеральной совокупности при фиксировании различных подмножеств компонент являются нормальными. | ВЫВОД ИТОГОВ | |||||||
| Регрессионная статистика | |||||||
| Множественный R | 0,607291 | ||||||
| R-квадрат | 0,368802 | ||||||
| Нормированный R-квадрат | 0,35592 | ||||||
| Стандартная ошибка | 0,118415 | ||||||
| Наблюдения | 51 | ||||||
| Дисперсионный анализ | |||||||
| df | SS | MS | F | Значимость F | |||
| Регрессия | 1 | 0,401452 | 0,401452 | 28,63014 | 2,3E-06 | ||
| Остаток | 49 | 0,687078 | 0,014022 | ||||
| Итого | 50 | 1,088529 | |||||
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | ||
| Y-пересечение | 0,557512 | 0,051111 | 10,90789 | 1,04E-14 | 0,45480 | 0,66022 | |
| X4 | -0,85062 | 0,158973 | -5,35071 | 2,3E-06 | -1,1701 | -0,5312 | |
Рис.5. Результаты регрессионного анализа .
В разделе Дисперсионный анализ приведены значения таких величин:
df – число степеней свободы ; SS –сумма квадратов отклонений ; MS – дисперсия ; F – расчетное значение F–критерия. Поскольку критическое значение критерия Фишера Fкр = 4,03 (m1=1; m2=50;
) Fрасч =28,63 > Fкр , и, следовательно с вероятностью 
гипотеза об отсутствии связи между рассматриваемыми признаками отвергается. Это означает, что уравнение в целом статистически значимо, т.е. хорошо соответствует данным наблюдений.Нижняя часть таблицы содержит такие сведения :
Коэффициенты – оценки параметров
уравнения регрессии;Стандартная ошибка – стандартные отклонения
;t–статистика – расчетное значение . Таким образом , можно оценить значимость коэффициентов уравнения регрессии, сравнив расчетное значение t – статистики с критическим значением, найденным по распределению Стьюдента при уровне значимости
и m=50 : tкр =2,009 . Поскольку 
> tкр для обоих коэффициентов , то они являются статистически значимыми при уровне доверительной вероятности 0,95.