9.7. Использование пакета Анализ данных

В состав пакета Excel входит набор способов анализа данных, который называется Пакетом анализа и предназначен для решения различных заданий. Для ознакомления с этим пакетом, следует в меню окна Excel выбрать опцию Сервис и в появившемся меню нужно выбрать опцию Анализ данных. В результате получим окно (рисунок 9.32).

Рис. 9.32

С помощью клавиш прокрутки можно выбрать любую из приведенных функций анализа.

9.7.1. Нахождение корреляционной матрицы

Пример 9.4. Найти корреляционную матрицу по следующим данным:

Y	X1	X2	X3
9,9	0,43	0,3	3,9
5,5	0,38	0,42	5,65
4,3	0,34	0,9	8,52
6,6	0,37	0,55	5,38
9,4	0,23	0,52	4,36
5,2	0,41	0,38	3,13
10	0,22	0,36	5,82

Решение задания будет проводиться так:

1. Ввести данные на Лист 1.

После ввода данных, получим таблицу в окне электронной таблицы Excel, изображенную на рисунке 9.33:

Рис.9.33

2. Выбираем опцию Сервис.

3. Выбираем опцию Анализ данных.

В результате появится окно, изображенное на рисунке 9.32.

4. Выбираем опцию Корреляция.

В результате появится окно, изображенное на рисунке 9.34:

Рис. 9.34

5. Активизируем окно Входной интервал (установить стрелку мыши в окне и нажать левую клавишу)

6. Выбираем ячейку А2 и при нажатой левой клавише мыши передвигаемся к ячейке D8.

Номера ячеек, из которых будут взяты исходные данные, автоматически заносятся в окно Входной интервал.

7. Ставим флажок в окне Новый рабочий лист.

8. Нажимаем кнопку .

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

Рис.9.35

9.7.2. Нахождение линейной регрессии

Пример 9.5. По данным примера 14 найти коэффициент корреляции, индекс детерминации, уравнения множественной линейной регрессии, F-статистику, t-статистику, доверительные интервалы для параметров уравнения регрессии.

Алгоритм решения будет таким:

1. Введем данные на Лист 1.

2. Выбираем опцию Сервис.

3. Выбираем опцию Анализ данных.

4. Выбираем опцию Регрессия.

В результате появится окно, изображенное на рисунке 9.36:

Рис.9.36

5. Активизируем окно Входной интервал Y.

6. Выбираем ячейку А2 и при нажатой левой клавише мыши передвигаемся к клетке A8.

7. Активизируем окно Входной интервал Х.

8. Выбираем ячейку В2 и при нажатой левой клавише мыши передвигаемся к клетке D8 (если участвуют все переменные).

Номера ячеек, из которых будут взяты исходные данные, автоматически заносятся в окно Входной интервал.

9. Ставим маркер в окне Новый рабочий лист.

10. Ставим маркер в окне Остатки.

11. Нажимаем кнопку .

В результате появится таблица, изображенная на рисунке 9.37:

Рис.9.37

Из таблицы, представленной на рис. 29 находим:

• множественный коэффициент корреляции – 0,791;

• индекс детерминации – 0,626;

• уравнения множественной линейной регрессии

• F-статистику – 1,674;

• t-статистики:

для коэффициента при переменной : -1,486;

для коэффициента при переменной : -1,062;

для коэффициента при переменной : -0,008;

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

для коэффициента при переменной : (-49,96;18,14);

для коэффициента при переменной : (-31,28;15,62);

для коэффициента при переменной : (-2,77; 2,75).