Статистическое изучение основных фондов (стр. 6 из 7)
2. Для построения интервального вариационного ряда выполняем следующие действия:
1. Сервис - Анализ данных - Гистограмма - ОК
2. Входной интервал – B4:B29
3. Выходной интервал – А43
4. Интегральный процент – Активизировать
5. Вывод графика – Активизировать
6. ОК
В итоге получаем (табл. 3):
| Карман | Частота | Интегральный % |
| 1 | 3.85% | |
| 580.694 | 3 | 15.38% |
| 1092.848 | 12 | 61.54% |
| 1605.002 | 6 | 84.62% |
| 2117.156 | 1 | 88.46% |
| 2629.31 | 3 | 100.00% |
Предварительно перейдя от нижних границ к верхним (табл. 4): удалим значение ячейки A44, а вместо «Ещё» введем максимальное значение первого признака.
Таблица 4. Интервальный ряд распределения областей по степени износа основных фондов в отрасли - строительство
| Группа областей по степени износа основных фондов в отрасли - строительство | Число областей в группе | Накопленная частость группы.% |
| 68.54-580.69 | 4 | 19.23% |
| 580.69-1092.85 | 12 | 61.54% |
| 1092.85-1605.00 | 6 | 84.62% |
| 1605.00-2117.16 | 1 | 88.46% |
| 2117.16-2629.31 | 3 | 100.00% |
| Итого | 26 |
В процессе статистического исследования необходимо решить ряд задач:
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. Исходные данные
| Регионы России | Степень износа основных фондов в отрасли – строительство, млн. руб. | Стоимость основных фондов в отрасли - строительство, млн. руб. |
| Республика Ингушетия | 68.54 | 149.00 |
| Курская область | 216.90 | 1446.00 |
| Калининградская область | 384.71 | 747.00 |
| Ивановская область | 386.60 | 1065.00 |
| Новгородская область | 674.08 | 1314.00 |
| Республика Карелия | 700.74 | 1530.00 |
| Калужская область | 733.04 | 1870.00 |
| Брянская область | 758.01 | 1919.00 |
| Липецкая область | 797.87 | 1797.00 |
| Тверская область | 814.43 | 1642.00 |
| Тамбовская область | 836.61 | 1855.00 |
| Мурманская область | 899.16 | 1905.00 |
| Псковская область | 907.41 | 2030.00 |
| Костромская область | 953.56 | 1516.00 |
| Тульская область | 989.33 | 2413.00 |
| Владимирская область | 1092.50 | 2595.00 |
| Вологодская область | 1098.59 | 3319.00 |
| Орловская область | 1109.12 | 1733.00 |
| Смоленская область | 1195.29 | 2330.00 |
| Республика Адыгея | 1361.26 | 2284.00 |
| Республика Дагестан | 1364.97 | 2630.00 |
| Воронежская область | 1479.63 | 3182.00 |
| Ленинградская область | 1971.00 | 4380.00 |
| Архангельская область | 2168.33 | 6285.00 |
| Ярославская область | 2411.03 | 5044.00 |
| Республика Коми | 2629.31 | 6351.00 |
А далее заполняем таблицу 2.2. формулами: в ячейку D44 вводим: =СУММ(C4:C7). Аналогично со следующими ячейками D45 - D48; в ячейку E44: =D44/C44.
Таблица 2.2. Зависимость стоимости основных фондов от степени износа основных фондов
| Номер группы | Группы областей по степени износа основных фондов в отрасли - строительство | Число областей | Стоимость основных фондов вотрасли - строительство | |
| Всего | В среднем на одну область | |||
| 1 | 68.54-580.69 | 4 | 3407.00 | 851.75 |
| 2 | 580.69-1092.85 | 12 | 22386.00 | 1865.50 |
| 3 | 1092.85-1605.0 | 6 | 15478.00 | 2579.67 |
| 4 | 1605.00-2117.16 | 1 | 4380.00 | 4380.00 |
| 5 | 2117.16-2629.31 | 3 | 17680.00 | 5893.33 |
| Итого | 26 | 63331.00 | 2435.81 | |
2. Оценить тесноту связи признаков Х и Y на основе:
а) эмпирического корреляционного отношения η;
б) линейного коэффициента корреляции r.
а)для вычисления эмпирического корреляционного отношения необходимо вычислить факторную и общую дисперсию, используя функции инструмента Мастер функций: ДИСПР, СУММПРОИЗВ, КОРЕНЬ.
В ячейку А66 вводим формулу =ДИСПР(C4:C29); в ячейку В66: =СУММПРОИЗВ(D55:D59;C55:C59)/C49; в ячейку С66: =A66-B66. Теперь находим эмпирического корреляционного отношения η: в ячейку D66 вводим формулу: =КОРЕНЬ(C66/A66). В итоге получаем таблицу 2.4:
Показатели дисперсии и эмпирического корреляционного отношения
| Общая дисперсия | Средняя из внутригрупповых | Факторная дисперсия | Эмпирическое корреляционное отношение |
| 2266566.771 | 200894.76 | 2065672.01 | 0.954654939 |
Получаем η= 0.954654939.
б) для нахождения линейного коэффициента корреляции rиспользуем инструмент Корреляция надстройки Пакет анализа.
1.Сервис => Анализ данных => Корреляция => ОК.
2.Входной интервал В4:С29;
3.Группирование – по столбцам;
4.Метки в первой строке – не активизировать;
5.Выходной интервал (А71);
6.Новый рабочий лист и Новая рабочая книга – не активизировать;
7.ОК.
В результате работы алгоритма Excel выдает оценку тесноты связи факторного и результативного признаков (табл. 2.5):
Таблица 2.5. Линейный коэффициент корреляции признаков
| Столбец 1 | Столбец 2 | |
| Столбец 1 | 1 | |
| Столбец 2 | 0.946358973 | 1 |
Сравним значения η и 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. ОК.
В результате указанных действий осуществляется вывод в заданный диапазон рабочего файла четырех выходных таблиц и одного графика, начиная с ячейки, указанной в поле Выходной интервал:
Регрессионная статистика
| Регрессионная статистика | |||||
| Множественный R | 0.946358973 | ||||
| R-квадрат | 0.895595305 | ||||
| Нормированный R-квадрат | 0.891245109 | ||||
| Стандартная ошибка | 506.3202843 | ||||
| Наблюдения | 26 | ||||
| Дисперсионный анализ | |||||
| df | SS | MS | F | Значимость F | |
| Регрессия | 1 | 52778090.51 | 52778090.51 | 205.8747195 | 2.84426E-13 |
| Остаток | 24 | 6152645.527 | 256360.2303 | ||
| Итого | 25 | 58930736.04 | |||
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | |
| Y-пересечение | -32.80047442 | 198.6470804 | -0.165119338 | 0.870232989 |
| Переменная X 1 | 2.292113652 | 0.159747709 | 14.34833508 | 2.84426E-13 |
| Нижние95% | Верхние 95% | Нижние 68.3% | Верхние 68.3% |
| -442.7878952 | 377.1869463 | -235.8061414 | 170.2051925 |
| 1.962410588 | 2.621816716 | 2.128860862 | 2.455366443 |
| Наблюдение | Предсказанное Y | Остатки |
| 1 | 124.3009953 | 24.69900469 |
| 2 | 464.3589768 | 981.6410232 |
| 3 | 848.9985688 | -101.9985688 |
| 4 | 853.3306636 | 211.6693364 |
| 5 | 1512.267496 | -198.2674963 |
| 6 | 1573.375246 | -43.37524631 |
| 7 | 1647.410517 | 222.5894827 |
| 8 | 1704.644595 | 214.3554048 |
| 9 | 1796.008245 | 0.991754634 |
| 10 | 1833.965647 | -191.9656474 |
| 11 | 1884.804728 | -29.80472826 |
| 12 | 2028.176437 | -123.1764372 |
| 13 | 2047.086375 | -17.08637484 |
| 14 | 2152.86742 | -636.8674199 |
| 15 | 2234.856325 | 178.1436748 |
| 16 | 2471.333691 | 123.6663092 |
| 17 | 2485.292663 | 833.7073371 |
| 18 | 2509.42862 | -776.4286197 |
| 19 | 2706.940053 | -376.9400531 |
| 20 | 3087.362156 | -803.362156 |
| 21 | 3095.865898 | -465.8658976 |
| 22 | 3358.679649 | -176.679649 |
| 23 | 4484.955534 | -104.9555343 |
| 24 | 4937.258321 | 1347.741679 |
| 25 | 5493.554305 | -449.5543048 |
| 26 | 5993.876873 | 357.1231272 |
