Алгоритм 1. Расчет параметров уравнения линейной регрессии и проверки адекватности модели исходным данным

1. Сервис => Анализ данных => Регрессия => ОК;

2. Входной интервал Y <= диапазон ячеек таблицы со значениями признакаY–Выпуск продукции (С4:С33);

3. Входной интервал X – диапазон ячеек таблицы со значениями признакаX–Среднегодовая стоимость основных производственных фондов (В4:В33);

4. Метки в первой строке/Метки в первом столбце–НЕ активизировать;

5. Уровень надежности <=68,3 (или 68.3);

6. Константа–ноль – НЕ активизировать;

7. Выходной интервал <=адрес ячейки заголовка первого столбца первой выходной результативной таблицы (А75);

8. Новый рабочий листиНовая рабочая книга– НЕ активизировать;

9. Остатки – Активизировать;

10. Стандартизованные остатки – НЕ активизировать;

11. График остатков–НЕ активизировать;

12. График подбора – НЕ активизировать;

13. График нормальной вероятности – НЕ активизировать;

14. ОК.

В результате указанных действий осуществляется вывод четырех выходных таблиц на Лист 2 Рабочего файла, начиная с ячейки, указанной в поле Выходной интервалдиалогового окна инструмента Регрессия (для демонстрационного примера они имеют следующий вид).

	А	В
	Регрессионная статистика
	Множественный R	0,753661673
	R–квадрат	0,568005917
	Нормированный R-квадрат	0,552577557
	Стандартная ошибка	25,90882817
	Наблюдения

	А	B	C	D	E	F
	Дисперсионный анализ
		df	SS	MS	F	Значимость F
	Регрессия		24713,1801	24713,1801	36,81570256	1,52606E-06
	Остаток		18795,48657	671,2673773
	Итого		43508,66667

	A	B	C	D	E	F	G	H	I
		Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 68,3%	Верхние 68,3%
	Y-пересечение	21,64454934	20,81975413	1,039615992	0,307412837	-21,0028	64,29193	0,432468	42,85664
	Переменная X 1	0,605324507	0,099763508	6,067594462	1,52606E-06	0,400968	0,809681	0,503681	0,706968

	A	B	C
	ВЫВОД ОСТАТКА
	Наблюдение	Предсказанное Y	Остатки
		78,54505301	31,45494699
		86,4142716	14,5857284
		102,7580333	17,24196671
		116,680497	-35,68049696
	…	…	…
		202,0312525	17,96874754

Задание 3

Построение однофакторных нелинейных регрессионных моделей связи признаков с помощью инструмента Мастер диаграмм и выбор наиболее адекватного нелинейного уравнения регрессии

Алгоритмы выполнения Задания 3

Алгоритм 1. Построение уравнений регрессионных моделей для различных видов нелинейной зависимости признаков с использованием средств инструмента Мастер диаграмм

1. Выделить мышью диаграмму рассеяния признаков, расположенную начиная с ячейки Е4, и увеличить диаграмму на весь экран, используя прием "захват мышью";

2. Диаграмма => Добавить линию тренда;

3. В появившемся диалоговом окне Линия трендавыбрать вкладку Типи задать вид регрессионной модели – полином 2-го порядка;

4. Выбрать вкладку Параметрыи выполнить действия:

1. Переключатель Название аппроксимирующей кривой: автоматическое/другое– установить в положение другое и ввести имя тренда– полином 2-го порядка;

2. Поле Прогноз вперед на– НЕ активизировать;

3. ПолеПрогноз назад на– НЕ активизировать;

4. ФлажокПересечение кривой с осью Y в точке–

НЕ активизировать;

5. ФлажокПоказывать уравнение на диаграмме – Активизировать;

6. ФлажокПоместить на диаграмму величину достоверности аппроксимации R2– Активизировать;

7. ОК;

8. Установить курсор на линию регрессии и щелкнуть правой клавишей мыши;

9. В появившемся диалоговом окне Формат линии трендавыбрать по своему усмотрению тип, цвет и толщину линии;

10. ОК;

11. Выделить уравнение регрессии и индекс детерминации R2и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.

5. Действия 2 – 4 (в п.4 шаги 1–11) выполнить поочередно для следующих видов регрессионных моделей:

– полином 3-го порядка,
– степенная функция.

По окончании работы алгоритма 1 выполнить следующие действия:

1. Присвоить полученной диаграмме заголовок "Диаграмма 2.1" и удалить линии сетки по оси Y (алгоритм 2);

2. Снять заливку области построения(алгоритм 3);

3. При необходимости изменить масштаб шкалы осей диаграммы (алгоритм 4).

Алгоритм 2. Присвоение полученной диаграмме заголовка "Диаграмма 2.1" и удаление линий сетки по оси Y

1. Выделить мышью построенную диаграмму;

2. Диаграмма => Параметры диаграммы;

3. В появившемся диалоговом окне Параметры диаграммывыбрать вкладку Заголовкии в поле Название диаграммы ввести заголовок диаграммы "Диаграмма 2.1";

4. Выбрать вкладку Линии сетки, в полях Ось Х и Ось Y все флажки – Не активизировать;

5. ОК.

Алгоритм 3. Снятие заливки области построения

1. Выделить мышью Область построения диаграммы;

2. Формат => Выделенная область построения;

3. В появившемся диалоговом окне Формат области переключатель Заливка установить в положение Обычная;

4. ОК.