Название: Применение встроенных функций табличного редактора excel для решения прикладных статистических задач
Вид работы: реферат
Рубрика: Информатика и программирование
Размер файла: 301.21 Kb
Скачать файл: referat.me-134790.docx
Краткое описание работы: Проведение анализа динамики валового регионального продукта и расчета его точечного прогноза при помощи встроенных функций Excel. Применение корреляционно-регрессионного анализа с целью выяснения зависимости между основными фондами и объемом ВРП.
Применение встроенных функций табличного редактора 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 + 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 – Результат расчета "линейн"
Также в ячейке А14 находится значение коэффициента множественной детерминации. Так как этот коэффициент положителен и близок к единице наша гипотеза подтверждена, зависимость факторных признаков и результативного действительно является линейной. Следовательно, по полученному уравнению y = - 23315,437 + 0,305485*x1 + 14,281849*x2 + 984,02794*t можно рассчитывать прогнозы на следующие периоды.
Похожие работы
-
Работа с финансовыми функциями Excel
Функции для проведения финансово-экономических расчетов в пакете Excel. Будущая и текущая стоимость вклада. Экономический регрессионный анализ на основе собранных статистических данных. Модель Леонтьева многоотраслевой экономики (балансовый анализ).
-
Разработка и применение пакетов прикладных программ
МОСКОВСКАЯ СЕЛЬСКОХОЗЯЙСТВЕННАЯ АКАДЕМИЯ ИМЕНИ К. А. ТИМИРЯЗЕВА Кафедра экономической кибернетики КУРСОВОЙ ПРОЕКТ ПО ДИСЦИПЛИНЕ: «РАЗРАБОТКА И ПРИМЕНЕНИЕ ПАКЕТОВ ПРИКЛАДНЫХ ПРОГРАММ»
-
Microsoft Exel
ВОПРОСЫ К ЭКЗАМЕНУ ПО ИНФОРМАТИКЕ 2 семестр Табличные процессоры. Классификация. Табличный процессор Excel. Назначение. Основные приемы работы в Excel: ведение рабочей книги.
-
Розробка відомості "Прибутки-Збитки по реалізації" в Access i Excel
Інтерфейсна форма "Реалізація товарів та збитки". Облік реалізації продукції підприємством. Інформаційне забезпечення. Схема даних БД "Реалізація товарів". Аналіз даних засобами табличного процесору Excel. Оформлення засобами текстового редактору Word.
-
Табличный процессор Excel
Понятие табличного процессора, при работе с которым на экран выводится прямоугольная таблица, в клетках которой могут находиться числа, пояснительные тексты и формулы для расчета значений в клетке по имеющимся данным. Краткая характеристика MS Excel.
-
Построение и анализ простой эконометрической модели
Проверка наличия линейной связи между соответствующими показателями деятельности коммерческих банков Украины в модуле Multiple Regression ППП Statistica. Расчет теоретических значений зависимой переменной и ошибки модели, вид графика линейной функции.
-
Проведение АВС анализа в среде MS EXCEL
Сортировка исходных данных по среднему запасу за квартал. Сумма среднего запаса за квартал по всем объектам. Распределение объектов управления по группам. Построение кривой АВС с помощью мастера диаграмм. Решение задачи XYZ параллельно с АВС анализом.
-
Расчет затрат на технологические инновации в Excel. Построение графиков и диаграмм
Изучение этапов создания диаграмм с помощью Мастера диаграмм. Методы построения диаграммы, отображающей снижение затрат на инновационную деятельность предприятия. Создания совмещенного графика по видам затрат. Определение роста инновационных затрат.
-
Информационно-вычислительная система федеральной службы государственной статистики: проблемы и перспективы развития
Начало 90-х годов совпало с прекращением существования СССР и созданием на его территории самостоятельных государств. С тех пор Госкомстат России стал преемником накопленного советской статистикой опыта в методологии и практике статистических работ.
-
Экспорт в Excel
Рано или поздно практически каждый программист сталкивается с необходимостью организовать экспорт данных в MS Office. При этом каждое "поколение" программистов натыкается на одни и те же вилы.