 |
Проведение корреляционного анализа средствами MS Excel .
|
|
|
|
Построим матрицу коэффициентов парной корреляции.
Выбираем команду меню Сервис/Анализ данных/Корреляция. Откроется следующее диалоговое окно:
Далее следует нажать кнопку OK. После этого будет создана матрица коэффициентов парной корреляции:
| Х1 | Х3 | Х6 | Х8 | Х9 | У | |
| Х1 | ||||||
| Х3 | 0,594 | |||||
| Х6 | 0,708 | 0,51 | ||||
| Х8 | 0,542 | 0,302 | 0,633 | |||
| Х9 | -0,63 | -0,63 | -0,75 | -0,68 | ||
| У | -0,55 | -0,35 | -0,87 | -0,62 | 0,719 |
| Х1 | Х3 | Х6 | Х8 | Х9 | У | |
| Х1 | ||||||
| Х3 | 0,594 | |||||
| Х6 | 0,708 | 0,51 | ||||
| Х8 | 0,542 | 0,302 | 0,633 | |||
| Х9 | -0,63 | -0,63 | -0,75 | -0,68 | ||
| У | -0,55 | -0,35 | -0,87 | -0,62 | 0,719 |
Анализ матрицы коэффициентов парной корреляции показывает, что наиболее существенное влияние на зависимую переменную оказывают все факторы Х6,Х8,Х9 (см. строку Y).
При этом
X9 сильно связана с х6 (из них надо оставить одну, например, х6),
Для исключения явления мультиколлинеарности все факторы кроме X6 и X8 следует исключить из модели.
· Построить уравнение множественной линейной регрессии, используя надстройку MS Excel Пакет анализа (команда Сервис\ Анализ данных\ Регрессия)
Ищем линейное уравнение множественной регрессии в виде:
Y=b0+b6*x6+b8*x8
Параметры данного уравнения найдем с помощью инструмента «Регрессия» надстройки «Анализ данных» приложения MS Excel (результаты вычисления – в Приложении 7):
Для построения линейной регрессионной модели в MS Excel необходимо:
1) подготовить список из n строк и m столбцов, содержащий экспериментальные данные (столбец, содержащий выходную величину y должен быть либо первым, либо последним в списке);
|
|
|
2) обратиться к меню Сервис/Анализ данных/Регрессия
Если пункт "Анализ данных" в меню "Сервис" отсутствует, то следует обратиться к пункту "Надстройки" того же меню и установить флажок "Пакет анализа".
3) в диалоговом окне "Регрессия" задать:
- входной интервал Y;
- входной интервал X;
- выходной интервал - верхняя левая ячейка интервала, в который будут помещаться результаты вычислений (разместим на том же рабочем листе);
Настройки для решения поставленной задачи показаны на рисунке окна "Регрессия".
· нажать "Ok" и проанализировать результаты.
Результаты расчетов размещены в 4-x таблицах
Коэффициенты регрессии находятся в таблице «Дисперсионный анализ» в столбце «Коэффициенты»
| 72,90356 |
| -0,48853 |
| -0,046 |
b0 = 72,90356
b6 = -0,48853
b8=-0,046
Получаем уравнение линейной множественной регрессии:
Y=72,90356-0,48853*x6-0,046*x8
В регрессионном анализе наиболее важными результатами являются:
- коэффициенты при переменных и Y-пересечение, являющиеся искомыми параметрами модели;
- множественный R, характеризующий точность модели для имеющихся исходных данных;
- F-критерий Фишера (в рассмотренном примере он значительно превосходит критическое значение, равное 4,06);
t-статистика – величины, характеризующие степень значимости отдельных коэффициентов модели
3) Определить значения коэффициента множественной корреляции и коэффициента детерминации и сделать выводы об адекватности построенной модели.
Величина множественного коэффициента детерминации R2 =0,763361 рассчитана в таблице “ Регрессионная статистика ”
| Регрессионная статистика | |
| Множественный R | 0,873706 |
| R-квадрат | 0,763361 |
| Нормированный R-квадрат | 0,743642 |
| Стандартная ошибка | 8,931274 |
| Наблюдения |
| значения коэффициента множественной корреляции | 0,873706 | Теснота совместного влияния факторов на результат довольна высокая (больше, чем 0,7) | ||
| значения коэффициента детерминации | 0,763361 | Больше чем 0,5. Модель объясняет более 76% дисперсии зависимой переменной. | ||
|
|
|
Построенную модель на основе этого параметра можно признать достаточно качественной. А изменение результативного показателя примерно на 76 % обусловлено влиянием факторов, включенных в модель.
Задачей построения регрессионной зависимости является нахождение вектора коэффициентов M модели (1) при котором коэффициент R принимает максимальное значение.
Значимость R определяется не только его величиной, но и соотношением между количеством экспериментов и количеством коэффициентов (параметров) модели. Действительно, корреляционное отношение для n=2 для простой линейной модели равно 1 (через 2 точки на плоскости можно всегда провести единственную прямую). Однако, если экспериментальные данные являются случайными величинами, доверять такому значению R следует с большой осторожностью. Обычно для получения значимого R и достоверной регрессии стремятся к тому, чтобы количество экспериментов существенно превышало количество коэффициентов модели (n>>k).
4) Оценить значимость уравнения регрессии в целом (при заданном уровне значимости) (с помощью F-критерия Фишера) (статистическую надежность моделирования)
|
|
|
