Реферат: Применение встроенных функций табличного редактора excel для решения прикладных статистических задач
Рисунок 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 + m1 *x1 + m2 *x2 + m3 *t). Проверим гипотезу.
Для этого используем функцию "линейн" (рисунок 6), выделив свободную область в Excel в пять строк и в четыре столбца (b, m1 , m2 , m3 ).
Рисунок 6 – Изображение функции "линейн"
Диалоговое окно заполняется в соответствии с инструкцией, по завершении ввода данных необходимо нажать сочетание клавиш "Ctrl+Shift+Enter".
Читать получившийся результат нужно с права на лево. Для прочтения всей информации необходимо обратиться к функции "линейн" еще раз и вызвать "справку по этой функции" (рисунок 7).
Рисунок 7 – Справка по функции "линейн"
В нашем случае получились следующие данные (рисунок 8):
b= - 23315,437
m1 = 0,305485
m2 = 14,281849
m3 = 984,02794
Рисунок 8 – Результат расчета "линейн"