2015-05-18
1155
1. Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии y=a+bx. Порядок вычисления следующий:
1) введите исходные данные:
| А | В | С | |
| Территория региона | Прожиточный минимум, х | Среднемесячная зарплата, у | |
2) выделите область пустых ячеек 5´2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1´2 – для получения только оценок коэффициентов регрессии;
3) активизируйте Мастер функций любым из способов:
а) в главном меню выберите Вставка/Функция;
б) на панели инструментов Стандартная щелкните по кнопке Вставка функции;
4) в окне Категория выберите Статистические, в окне Функция – ЛИНЕЙН. Щелкните по кнопке ОК;
5) заполните аргументы функции следующим образом:
Известные_значения_y – диапазон, содержащий данные результативного признака (С2:С13);
|
|
|
Известные_значения_x – диапазон, содержащий данные факторов независимого признака (В2:В13);
Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении: если Константа=1, то свободный член рассчитывается обычным образом, если Константа=0, то свободный член равен 0 (указать 1);
Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет: Статистика=1 - дополнительная информация выводится, Статистика=0 - выводятся только оценки параметров уравнения (указать 1).
Щелкните по кнопке ОК;
6) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу F2, а затем – на комбинацию клавиш CTRL + SHIFT + ENTER.
Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:
| Значение коэффициента b | Значение коэффициента a |
| Среднеквадратическое отклонение b | Среднеквадратическое отклонение a |
| Коэффициент детерминации R2 | Среднеквадратическое отклонение y |
| F - статистика | Число степеней свободы |
| Регрессионная сумма квадратов | Остаточная сумма квадратов |
Для вычисления параметров экспоненциальной кривой y=a×bx в MS Excel применяется встроенная статистическая функция ЛГРФПРИБЛ. Порядок вычисления аналогичен применению функции ЛИНЕЙН.
2. С помощью инструмента анализа данных Регрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Порядок действий следующий:
|
|
|
1) проверьте доступ к пакету анализа. В главном меню последовательно выберите Сервис / Настройки. Установите флажок Пакет анализа (должен стоять флажок);
2) в главном меню выберите Сервис / Анализ данных / Регрессия. Щелкните по кнопке ОК;
3) заполните диалоговое окно ввода данных и параметров вывода следующим образом:
Входной интервал Y – диапазон, содержащий данные результативного признака ($C$1:$C$13);
Входной интервал X – диапазон, содержащий данные факторов независимого признака ($B$1:$B$13);
Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет (установить флажок);
Константа – ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении (без флажка);
Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;
Новый рабочий лист – можно задать произвольное имя нового листа.
Если необходимо получить информацию и графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке ОК.
Варианты индивидуальных заданий
Задача 1. По территориям региона приводятся данные за 199X г. (см. таблицу своего варианта).
Требуется:
1. Построить линейное уравнение парной регрессии 
от 
.
2. Рассчитать линейный коэффициент парной корреляции и среднюю ошибку аппроксимации.
3. Оценить статистическую значимость параметров регрессии и корреляции с помощью 
-критерия Фишера и 
-критерия Стьюдента.
4. Выполнить прогноз заработной платы при прогнозном значении среднедушевого прожиточного минимума , составляющем 107% от среднего уровня.
5. Оценить точность прогноза, рассчитав ошибку прогноза и его доверительный интервал.
6. На одном графике построить исходные данные и теоретическую прямую.
Вариант 1
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб., 
| Среднедневная заработная плата, руб., 
|
Вариант 2
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
Вариант 3
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
Вариант 4
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
Вариант 5
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
Вариант 6
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
Вариант 7
|
|
|
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
Вариант 8
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
Вариант 9
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
Вариант 10
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб.,
| Среднедневная заработная плата, руб.,
|
