Применение встроенных функций табличного редактора excel для решения прикладных статистических задач

ПРИМЕНЕНИЕ ВСТРОЕННЫХ ФУНКЦИЙ ТАБЛИЧНОГО РЕДАКТОРА EXCEL ДЛЯ РЕШЕНИЯ ПРИКЛАДНЫХ СТАТИСТИЧЕСКИХ ЗАДАЧ

Выполнили студентки гр. Э-2728 Ермакова П. А. и Студентова Е. А.

Научные руководители: канд. экон наук Уварова И.А. и старший преподаватель каф. АБА Шумакова Н. В

 

Актуальность работы

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

Цель работы

Продемонстрировать применение встроенных функций Excel при решении прикладных статистических задач, таких как:

1.         анализ ряда динамики и точечный прогноз

2.         регрессионный анализ

Задачи

-           собрать статистические данные о динамике валового регионального продукта Курганской области (ВРП) и о факторах влияющих на его изменение

-           на основе собранных данных произвести анализ динамики ВРП и регрессионный анализ с использованием встроенных функций Excel

-           проанализировать эффективность использования указанных функций с точки зрения точности расчетов и времени их проведения

1.         Анализ ряда динамики

Данные о ВРП взяты с официального сайта Федеральной службы государственной статистики на www.gks.ru (таблица 1).

Таблица 1 - ВРП Курганской области

ВРП Курганской области в текущих основных ценах на душу населения
№ пер.	1	2	3	4	5	6	7	8	9
период	2000	2001	2002	2003	2004	2005	2006	2007	2008
руб	17758,7	24429,3	29253,3	36675,8	42554,4	50959,1	70217,6	84029,0	111277,4

По данным таблицы 1 в Excel построен график с маркерами (рисунок 1).

Если щелкнуть правой клавишей мыши по одному из маркеров, появится список, в котором нас интересует функция "Добавить линию тренда".

Рисунок 1 – График с маркерами

При выборе функции "Добавить линию тренда" открывается диалоговое окно (рисунок 2), в котором необходимо выбрать линию тренда, наиболее точно отражающую характер изменений.

Определить оптимальный вариант можно экспериментально, построив несколько линий тренда. Выбрать необходимо ту линию, у которой величина достоверности аппроксимации - R наибольшая.

Для определения величины достоверности аппроксимации – R в том же диалоговом окне (рисунок 2) на вкладке параметры необходимо отметить "поместить на диаграмму величину достоверности аппроксимации (R^2)" (рисунок 3).

Рисунок 2 – Диалоговое окно функции "Добавить линию тренда"

При построении выбранной оптимальной линии тренда в диалоговом окне (рисунок 2) на вкладке параметры необходимо отметить еще один пункт – "показать уравнение на диаграмме" (рисунок 3). Это позволит нам получить уравнение, характеризующее изменение рассматриваемого параметра.

Уравнение линии тренда (рисунок 4) необходимо для расчета точечного прогноза, поэтому оно должно быть максимально точным. Для точности прогноза и построение линии тренда должно быть точным, что обеспечивается компьютерными технологиями.

Рисунок 3 – Изображение необходимых параметров линии тренда

Если мы все сделали правильно, то получим прогнозную модель, представленную на рисунке 4

Чтобы рассчитать точечный прогноз необходимо в уравнение линии тренда вместо х подставить номер периода, например 10, и рассчитать прогнозное значение на 2010 год.

Рисунок 4 – Прогнозная модель и уравнение линии тренда

2.         Регрессионный анализ

Для выявления зависимости между показателями применяется корреляционно-регрессионный анализ.

В качестве примера корреляционно-регрессионного анализа с помощью встроенных функций Excel проведем анализ влияния наличия основных фондов и численности экономически активного населения на объемы ВРП.

Данные для анализа были взяты с официального сайта Федеральной службы государственной статистики на www.gks.ru (таблица 2,3).

Таблица 2 – Наличие ОФ по полному кругу организаций Курганской области по полной учетной стоимости на конец года

№ пер.	1	2	3	4	5	6	7	8	9
период	2000	2001	2002	2003	2004	2005	2006	2007	2008
млн.руб.	105205	103644	146404	177444	186289	213335	242085	325899	368000

Номер периода при расчете в Excel будет заменен на показатель t – показатель времени, что также удобно для расчета прогноза.

Таблица 3 – Численность экономически активного населения по Курганской области

№ пер.	1	2	3	4	5	6	7	8	9
период	2000	2001	2002	2003	2004	2005	2006	2007	2008
тыс.чел.	519,5	475,0	480,2	419,9	489,0	490,5	477,8	445,2	470,0

Для расчета тесноты связи между факторами переносим данные в Excel, вызываем статистическую функцию "коррел" и, следуя указаниям в диалоговом окне, вводим данные (рисунок 5).

Рисунок 5 – Изображение функции "коррел"

Применив функцию дважды мы получим два значения коэффициента корреляции, которые показывают тесноту факторных признаков с результативным. В нашем случае наличие ОФ прямо и очень тесно связано с объемом ВРП, а численность экономически активного населения связана с объемом ВРП несколько меньше.

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

Предположим самый простой вариант, что связь между показателями линейная (y = b + m₁*x₁ + m₂*x₂ + m₃*t). Проверим гипотезу.

Для этого используем функцию "линейн" (рисунок 6), выделив свободную область в Excel в пять строк и в четыре столбца (b, m₁, m₂, m₃).

Рисунок 6 – Изображение функции "линейн"

Диалоговое окно заполняется в соответствии с инструкцией, по завершении ввода данных необходимо нажать сочетание клавиш "Ctrl+Shift+Enter".

Читать получившийся результат нужно с права на лево. Для прочтения всей информации необходимо обратиться к функции "линейн" еще раз и вызвать "справку по этой функции" (рисунок 7).

Рисунок 7 – Справка по функции "линейн"

В нашем случае получились следующие данные (рисунок 8):

b= - 23315,437

m₁= 0,305485

m₂= 14,281849

m₃= 984,02794

Рисунок 8 – Результат расчета "линейн"

Также в ячейке А14 находится значение коэффициента множественной детерминации. Так как этот коэффициент положителен и близок к единице наша гипотеза подтверждена, зависимость факторных признаков и результативного действительно является линейной. Следовательно, по полученному уравнению y = - 23315,437 + 0,305485*x₁ + 14,281849*x₂ + 984,02794*t можно рассчитывать прогнозы на следующие периоды.