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

 

     В данной задаче 5 аномальных единиц наблюдения.

     Для удаления из исходной таблицы 1 строк  с аномальными данными необходимо выделить мышью соответствующую  адресную строку вместе с её номером, нажать Правка – Удалить. В итоге получаем табл. 1 и рис. 2: 

     

     Рис. 2. Диаграмма рассеяния без аномальных единиц. 

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

 
 

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

     2. Для построения интервального  вариационного ряда выполняем  следующие действия:

     1. Сервис - Анализ данных - Гистограмма - ОК

     2. Входной интервал – B4:B29

     3. Выходной интервал – А43

     4. Интегральный процент – Активизировать

     5. Вывод графика – Активизировать

     6. ОК

     В итоге получаем (табл. 3): 

 

     Предварительно  перейдя от нижних границ к верхним (табл. 4): удалим значение ячейки A44, а вместо «Ещё» введем максимальное значение первого признака. 

     Таблица 4. Интервальный ряд распределения  областей по степени износа основных фондов в отрасли - строительство

 

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

     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. ОК.