Пример решения задач с применением электронных таблиц EXСEL.

1. Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии y=a+bx. Порядок вычисления следующий:

1.1. Введите исходные данные или откройте существующий файл, содержащий анализируемые данные.

1.1.1. В нашей задаче в колонку А заносим параметры Х, в колонку В заносим параметры У (Рис.1):

Рис.1

1.2. Выделите область пустых ячеек –2 столбца и 5 строк для результатов регрессионной статистики.

1.2.1. На Рис.1 это область E1:F5.

1.3. Активизируйте Мастер функций любым из способов:

1.3.1. В главном меню выберите Вставка/Функция.

1.3.2. На панели инструментов Стандартная щелкнуть по кнопке Вставка функции.

1.4. В появившемся Мастере функций (Рис.2) в окне Категории выбрать Статистические, а в окне Функция – ЛИНЕЙН. Щелкните по кнопке ОК.

Рис.2 Рис.3

1.5. Заполните аргументы функции (Рис.3):

1.5.1. Известные значения у – диапазон, содержащий данные результативного признака;

Известные значения х – диапазон, содержащий данные факторов независимого признака;

Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа=1, то свободный член рассчитывается обычным образом, если Константа=0, то свободный член равен 0;

Статистика – логическое значение, которое указывает выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика=1, то дополнительная информация выводится, если Статистика=0, то выводятся только оценки параметров уравнения.

Щелкните по кнопке ОК.

1.5.2. Заполняя аргументы функции рассматриваемой задачи, получаем следующие данные (Рис.3):

Известные значения у – B1:B7;

Известные значения х – A1:A7;

Константа – 1;

Статистика – 1.

1.6. В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть таблицу необходимо нажать <F2>, а затем – комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>.

1.6.1.

b	36,84211	а	-5,78947
S_b	2,201737	S_a	7,443229
r²	0,982456	S_y	7,254763
F		N
RSS	14736,84	ESS	263,1579

2. С помощью инструмента анализа данных Регрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линий регрессии, остатков и нормальной вероятности. Порядок действий следующий:

2.1. Необходимо проверить доступ к пакету анализа. В главном меню последовательно выберите Сервис/Надстройки. Установите флажок Пакет анализа (Рис.4);

Рис.4

2.2. В главном меню необходимо выбрать Сервис/Анализданных/Регрессия. Щелкнуть по кнопке ОК;

2.3. Заполнение диалогового окна ввода данных и параметров вывода (Рис.5):

2.3.1. Входной интервал у – диапазон, содержащий данные результативного признака;

Входной интервал х – диапазон, содержащий данные факторов независимого признака;

Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Константа—ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

Новый рабочий лист – можно задать произвольное имя нового листа.

Если необходимо получить информацию и графики остатков, необходимо установить соответствующие флажки в диалоговом окне.

Рис.5

2.3.2. Данные для рассматриваемой задачи представлены на Рис.5.

2.4. На Рис.6 показаны результаты, получившиеся вследствие применения инструмента Анализа данных Регрессия.

Рис.6

Если студент не имеет возможности работать на персональном компьютере дома или на работе, такая возможность ему будет предоставлена в Университете.

Варианты контрольных заданий.

Вариант

В таблице представлены статистические данные о размере товарооборота Х и суммы издержек обращения Y по девяти магазинам:

Товарооборот Х
Издержки обращения Y

Составить уравнение линии регрессии. Предсказать сумму издержек обращения при товарообороте 500 и 640, а также изменение суммы издержек обращения при изменении товарооборота не единицу. Найти коэффициент корреляции.

Вариант

Обозначим через Х цену оптовой продажи некоторого товара, через Y—цену его розничной продажи. Составить уравнение прямой линии регрессии, вычислить коэффициент корреляции и предсказать цену розничной продажи товара при оптовой цене 70 и 80. Найти среднее изменение в Y, соответствующее единичному изменению в Х.

Вариант

В таблице приводится связь между валовым национальным продуктом (ВНП) на душу населения (Х) и процентным показателем (Y) грамотности взрослого населения для 22 стран и территорий в середине 1970-х гг. Написать уравнение прямой линии регрессии, вычислить коэффициент корреляции, установить изменение показателя грамотности, соответствующее единичному изменению ВНП. Предсказать уровень грамотности населения для стран с ВНП, равным 40 и 1000.

	Х	Y		Х	Y
Непал		5,0	Брит.Гвиана		74,0
Бирма		47,5	Гонконг		57,5
Уганда		27,5	Панама		65,7
Южн.Вьетнам		17,5	Ливан		47,5
Таиланд		68,0	Сингапур		50,0
Гаити		10,5	Аргентина		86,4
Индонезия		17,5	Исландия		98,5
Южн.Корея		77,0	Чехословакия		97,5
Гана		22,5	Франция		96,4
Перу		47,5	Новая Зеландия		98,5
Сальвадор		39,4	Канада		97,5

Вариант

Таблица содержит данные о росте (Х) и массе (Y) 25 выбранных наугад студентов. Найти линию регрессии и коэффициент корреляции, предсказать массу студентов, имеющих рост 175 и 180, а также среднее изменение массы студента при изменении роста на единицу.

Вариант

На основе данных о годовой производительности труда в расчете на одного рабочего (Y) и энерговооруженности труда (Х) на предприятиях одной отрасли построить линию регрессии, найти коэффициент корреляции, предсказать производительность труда при энерговооруженности 9 и 10, а также изменение производительности труда при единичном изменении энерговооруженности.

Х	6,7	6,9	7,2	7,3	8,4	8,8	9,1	9,8	10,6	10,7	11,1	11,8	12,1	12,4
Y	2,8	2,2	3,0	3,5	3,2	3,7	4,0	4,8	6,0	5,4	5,2	5,4	6,0	9,0

Вариант

Случайная выборка 10 фармацевтических фирм показала следующее соотношение между прибылью (Y) и затратами на научные исследования (Х):

Х
Y

Составить уравнение прямой линии регрессии, найти коэффициент корреляции. Спрогнозировать прибыль, если затраты на научные исследования составили 50 и 45, а также изменение прибыли при увеличении затрат на единицу.

Вариант

На 10 территориях были измерены процентный показатель перенаселенности (Х) и показатель детской смертности (Y). По представленным в таблице данным определить уравнение прямой линии регрессии, коэффициент корреляции, а также предсказать показатели детской смертности для территорий с показателями перенаселенности 5 и 10.

Х
Y

Вариант

Найти уравнение прямой линии регрессии и коэффициент корреляции для фондоотдачи оборудования (Х) и удельного веса продукции высшей категории качества (Y):

Х	1,47	1,25	1,82	1,45	1,75	1,37	1,61	1,93	1,68	1,66
Y	34,08	35,89	36,93	32,31	34,91	30,20	31,23	48,13	30,08	42,86

Вариант

В таблице содержатся данные, показывающие связь между количеством дней (Х), проведенных пациентами в больнице, и затратами больницы (Y), которые компенсируются страховой компанией.

Х
Y

Вычислить коэффициент корреляции. По уравнению линии регрессии спрогнозировать затраты больницы при количестве дней 5 и 10. каково изменение затрат при увеличении дней госпитализации больного на единицу.

9. Вариант

Крупная корпорация проверяет соотношение между прибылью (Y) и процентом используемых производственных мощностей (Х) для каждого из 12 заводов, входящих в корпорацию. Найти уравнение линии регрессии, коэффициент корреляции. Предсказать прибыль при проценте использования производственных мощностей 60 и 70.

Х

Y 2,5 6,2 3,1 4,6 7,3 4,5 6,1 11,6 10,0 14,2 16,1 19,5

Литература

1.Айвазян С.А., Мхитарян В.С. Прикладная статистика. Основы эконометрики. В 2 т.- М. ЮНИТИ. 2001 - Т.1 - 656 с., Т.2 - 432 с.

2.Гмурман В.Е. Теория вероятностей и математическая статистика. М. Высшая школа, 1999 480 с.

3.Доугерти К. Введение в эконометрику. М. Инфра-М. 2001. - 402 с.

4.Елисеева И.И., Курышева С.В., Костеева Т.В. и др.Эконометрика. Под ред. И.И.Елисеевой.- М. «Финансы и статистика», 2001.- 344 с.

5.Елисеева И.И., Курышева С.В., Гордиенко Н.М.и др. Практикум по эконометрике. Под ред. И.И.Елисеевой - М. «Финансы и статистика», 2001 - 192 с.

6.Кремер Н.Ш., Путко Б.А. Эконометрика. - М. ЮНИТИ-ДАНА, 2002 - 311 с.

7.Магнус Я.Р., Катышев П.К., Пересецкий А.А. Эконометрика. Начальный курс- М. Дело, 2001 - 400 с.

8.Орлов А.И. Эконометрика. Начальный курс- М. Изд-во "Экзамен", 2002 - 576 с.