А далее заполняем таблицу 2.2. формулами: в ячейку D44 вводим: =СУММ(C4:C7). Аналогично со следующими ячейками D45 - D48; в ячейку E44: =D44/C44.

Таблица 2.2. Зависимость стоимости основных фондов от степени износа основных фондов

2. Оценить тесноту связи признаков Х и Y на основе:

а) эмпирического корреляционного отношения η;

б) линейного коэффициента корреляции r.

а)для вычисления эмпирического корреляционного отношения необходимо вычислить факторную и общую дисперсию, используя функции инструмента Мастер функций: ДИСПР, СУММПРОИЗВ, КОРЕНЬ.

В ячейку А66 вводим формулу =ДИСПР(C4:C29); в ячейку В66: =СУММПРОИЗВ(D55:D59;C55:C59)/C49; в ячейку С66: =A66-B66. Теперь находим эмпирического корреляционного отношения η: в ячейку D66 вводим формулу: =КОРЕНЬ(C66/A66). В итоге получаем таблицу 2.4:

Показатели дисперсии и эмпирического корреляционного отношения

Получаем η= 0.954654939.

б) для нахождения линейного коэффициента корреляции rиспользуем инструмент Корреляция надстройки Пакет анализа.

1.Сервис => Анализ данных => Корреляция => ОК.

2.Входной интервал В4:С29;

3.Группирование – по столбцам;

4.Метки в первой строке – не активизировать;

5.Выходной интервал (А71);

6.Новый рабочий лист и Новая рабочая книга – не активизировать;

7.ОК.

В результате работы алгоритма Excel выдает оценку тесноты связи факторного и результативного признаков (табл. 2.5):

Таблица 2.5. Линейный коэффициент корреляции признаков

Сравним значения η и r и сделаем вывод о возможности линейной связи между признаками Х и Y: так как они располагаются в диапазоне 0,9-0,99, то связь весьма тесная (по шкале Чэддока).

3. Построить однофакторную линейную регрессионную модель связи признаков Х и Y, используя инструмент Регрессия надстройки Пакет анализ.

1. Сервис => Анализ данных => Регрессия => ОК;

2. Входной интервал Y С4:С29;

3. Входной интервал X В4:В29;

4. Метки в первой строке/Метки в первом столбце –не активизировать;

5. Уровень надежности <= 68,3;

6. Константа–ноль – не активизировать;

7. Выходной интервал А81;

8. Новый рабочий лист и Новая рабочая книга – не активизировать;

9. Остатки – активизировать;

10. Стандартизованные остатки – не активизировать;

11. График остатков –неактивизировать;

12. График подбора – активизировать;

13. График нормальной вероятности – не активизировать;

14. ОК.

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

Регрессионная статистика