Статистическое изучение основных фондов

 

     В процессе статистического  исследования необходимо решить ряд задач:

     1. Установить наличие корреляционной связи между признаками Х и Y методом аналитической группировки.

     Построив  аналитическую таблицу, отражающую результаты аналитической группировки  совокупности по факторному признаку Степень износа основных фондов в отрасли – строительство, можно установить наличие корреляционной связи.

     Ранжируем исходные данные.

     1.Выделить  исходные данные табл. 2.1 (А4:С33);

     2.Данные => Сортировка;

     3.Сортировать по <= Степень износа основных фондов в отрасли – строительство

     4.по возрастанию/по убыванию – устанавливается в положение по возрастанию;

     5.Затем и В последнюю очередь по – не активизируются;

     6.Идентифицировать поля по подписям/обозначениям столбцов листа – устанавливается в положение подписям

     7.ОК.

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

     Распределяем  регионы по группам.

     1.Из  всего диапазона отсортированных  данных A4:C33 выделить мышью диапазон  ячеек первой группы, для чего необходимо отсчитать в ранжированном ряду количество строк, соответствующее числу предприятий первой группы (графа 3 табл.2.2),

     2.Нажать  на панели инструментов кнопку  ;

     3.Выбрать  цвет по собственному усмотрению;

     4.Выполнить  действия 1–3 для всех групп, выбирая контрастные цвета для цветовой заливки очередной группы.

     В итоге получаем таблицу 2.1. следующего вида: 

 

      Таблица 2.1. Исходные данные

 

     А далее заполняем таблицу 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. ОК.

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

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