Главная страница Случайная лекция
Мы поможем в написании ваших работ!
Порталы:
БиологияВойнаГеографияИнформатикаИскусствоИсторияКультураЛингвистикаМатематикаМедицинаОхрана трудаПолитикаПравоПсихологияРелигияТехникаФизикаФилософияЭкономика
Мы поможем в написании ваших работ!
Microsoft Excel. Работа с пакетом анализа. Построение простой регрессии
Целью работы является изучение пакета анализа Microsoft Excel.
Задание. С помощью пакета анализа Excel построить простую регрессию, оценить ее параметры, значимость коэффициентов, написать уравнение регрессии.
Необходимо занести данные в рабочий лист MS Excel.
| Год | Произведенный национальный доход (в неизменных ценах млр.руб.) | Среднегодовая численность занятых (млн. чел.) х |
| 69,00 | 34,00 | |
| 75,74 | 34,20 | |
| 78,28 | 34,51 | |
| 80,94 | 34,93 | |
| 83,74 | 35,46 | |
| 86,67 | 36,10 | |
| 82,60 | 36,87 | |
| 82,10 | 37,76 | |
| 79,88 | 38,80 | |
| 83,07 | 39,98 | |
| 86,45 | 41,32 | |
| 90,02 | 42,84 | |
| 93,82 | 44,54 | |
| 97,82 | 46,45 | |
| 102,12 | 48,59 | |
| 106,68 | 50,98 | |
| 111,55 | 53,64 | |
| 116,75 | 56,62 | |
| 122,31 | 59,94 | |
| 128,29 | 63,65 | |
| 134,71 | 67,79 | |
| 141,63 | 72,41 | |
| 149,11 | 77,58 | |
| 157,20 | 83,38 | |
| 165,98 | 89,87 | |
| 175,54 | 97,16 | |
| 185,97 | 105,36 |
Далее необходимо настроить Пакет анализа. Для этого необходимо выбрать пункт меню Сервис → Надстройки и поставить галочку на опции Пакет анализа.
Теперь необходимо выбрать пункт Сервис → Анализ данных → Регрессия.
В появившееся окно занести с помощью мыши:
входной интервал - интервал х (среднегодовая численность населения), например, интервал B2:B28;
выходной интервал - интервал у (произведенный НД), например С2:С28;
Для корректной работы пакета анализа каждый интервал должен содержать не менее 10 данных в каждом столбце (в данном примере их больше).
В параметрах вывода указать выходной интервал в каком-либо из свободных столбцов, например D2:D28.
Указать уровень надежность 95. Проставить галочки V в окнах: Остатки; График остатков.
На рабочем листе получаем таблицу:
| ВЫВОД ИТОГОВ | |||||||||||||||||
| Регрессионная статистика | |||||||||||||||||
| Множественный R | 0,99503076 | ||||||||||||||||
| R-квадрат | 0,990086214 | ||||||||||||||||
| Нормированный R-квадрат | 0,989689662 | ||||||||||||||||
| Стандартная ошибка | 3,428435967 | ||||||||||||||||
| Наблюдения | |||||||||||||||||
| Дисперсионный анализ | |||||||||||||||||
| df | SS | MS | F | Значимость F | |||||||||||||
| Регрессия | 29347,12414 | 29347,12414 | 2496,740834 | 1,42438E-26 | |||||||||||||
| Остаток | 293,8543294 | 11,75417318 | |||||||||||||||
| Итого | 29640,97847 | ||||||||||||||||
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | Нижние 95,0% | Верхние 95,0% | ||||||||||
| Y-пересечение | 23,35331005 | 1,853942615 | 12,59656575 | 2,52097E-12 | 19,53504379 | 27,1715763 | 19,53504379 | 27,17157631 | |||||||||
| Переменная X 1 | 1,595809896 | 0,031937022 | 49,96739771 | 1,42438E-26 | 1,530034368 | 1,66158542 | 1,530034368 | 1,661585424 | |||||||||
| ВЫВОД ОСТАТКА | |||||||||||||||||
| Наблюдение | Предсказанное Y | Остатки | |||||||||||||||
| 77,61084651 | -8,610846506 | ||||||||||||||||
| 77,93000849 | -2,190008486 | ||||||||||||||||
| 78,42470955 | -0,144709553 | ||||||||||||||||
| 79,09494971 | 1,84505029 | ||||||||||||||||
| 79,94072895 | 3,799271046 | ||||||||||||||||
| 80,96204729 | 5,707952712 | ||||||||||||||||
| 82,19082091 | 0,409179093 | ||||||||||||||||
| 83,61109171 | -1,511091715 | ||||||||||||||||
| 85,27073401 | -5,390734006 | ||||||||||||||||
| 87,15378968 | -4,083789683 | ||||||||||||||||
| 89,29217494 | -2,842174944 | ||||||||||||||||
| 91,71780599 | -1,697805985 | ||||||||||||||||
| 94,43068281 | -0,610682808 | ||||||||||||||||
| 97,47867971 | 0,341320291 | ||||||||||||||||
| 100,8937129 | 1,226287114 | ||||||||||||||||
| 104,7076985 | 1,972301463 | ||||||||||||||||
| 108,9525529 | 2,59744714 | ||||||||||||||||
| 113,7080663 | 3,041933651 | ||||||||||||||||
| 119,0061552 | 3,303844797 | ||||||||||||||||
| 124,9266099 | 3,363390083 | ||||||||||||||||
| 131,5332629 | 3,176737115 | ||||||||||||||||
| 138,9059046 | 2,724095396 | ||||||||||||||||
| 147,1562418 | 1,953758235 | ||||||||||||||||
| 156,4119392 | 0,788060839 | ||||||||||||||||
| 166,7687454 | -0,788745384 | ||||||||||||||||
| 178,4021995 | -2,862199525 | ||||||||||||||||
| 191,4878407 | -5,51784067 | ||||||||||||||||
Уравнение регрессии имеет следующий вид:
Y = a*x + b
Коэффициенты находятся в таблице:
a – переменная х1
b – Y-пересечение
По данным приведенным выше, можно написать уравнение регрессии:
Y = 1,6*x + 23,4
Уравнение регрессии является значимым, поскольку Значимость F равна 1,42438E-26, что меньше, чем 0,01 (это число записано в полулогарифмической форме, означает 1,42438*10-2). Проверить значимость уравнения регрессии можно также, посмотрев таблицу Распределения Фишера в любом учебнике статистике. Для этого нужно табличное значение сравнить со значением F, рассчитанным пакетом анализа. Также проверить значимость регрессии можно с помощью функции FРАСПОБР.
Остатки означают разницу между исходным значением Y и прогнозным.
R квадрат (коэффициент детерминации) имеет следующую интерпретацию: значение коэффициента детерминации , равное 1, означает функциональную зависимость между переменными. Модели с коэффициентом детерминации выше 80% можно признать достаточно хорошими (коэффициент корреляции превышает 90%). Предполагается, что коэффициент детерминации должен быть не меньше 50% (0,5).
Стандартная ошибка содержит несмещенное выборочное остаточное стандартное отклонение, при характеристике прогноза нужно обращать внимание на ее величину.
часть 2
| <== предыдущая страница | | | следующая страница ==> |
| ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ УПРАВЛЕНИЯ | | | Microsoft Excel. Работа с макросами. Язык программирования Visual Basic for Application |
Дата добавления: 2014-11-08; просмотров: 1035; Нарушение авторских прав
Мы поможем в написании ваших работ!