Лабораторная работа №2 (2 часа)

Лабораторная работа №2 (2 часа)

Цель работы: Приобретение навыков определения уравнения регрессии на основе метода наименьших квадратов и использования функций EXCEL

1. Функции рабочего листа для уравнения регрессии.

Для определения коэффициентов эмпирической функции регрессии используются функции Excel, которые находятся в категории «Статистические»

а) НАКЛОН (известные значения_у; известные значения_х) – определяет коэффициент наклона (m).

б) ОТРЕЗОК (известные значения_у; известные значения_х) – определяет точку пересечения линейного уравнения с осью ординат (в)

в) ЛИНЕЙН (известные значения_у; известные значения_х; константа; статистика) – функция определяет коэффициенты для прямой линии имеющей следующий вид:

¨ y = mx + b или

¨ y = m1 x1 + m2 x2 +... + b (в случае нескольких диапазонов значений x) т. е. для многофакторной задачи,

г) ЛГРФПРИБЛ (известные значения_у; известные значения_х; константа; статистика) – функция определяет коэффициенты для уравнения вида для однофакторной зависимости или для многофакторной задачи

Здесь:

¨ известные значения_у – массив известных значений зависимой наблюдаемой величины;

¨ известные значения_х – массив известных значений независимой наблюдаемой величины;

¨ константа – логическое значение, которое указывает, требуется ли, чтобы константа «в» была равна нулю.

Если константа = истина или опущена, то «в» вычисляется обычным образом;

Если константа – ложь, то «в» считается равной нулю(ЛИНЕЙН), единице (ЛГРФПРИБЛ)

¨ статистика – логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии.

Если статистика = истина, то функция возвращает дополнительную регрессионную зависимость.

Если статистика = ложь или опущена, то функция возвращает только значение коэффициентов.

Для работы с функцией ЛИНЕЙН и функцией ЛГРФПРИБЛ необходимо выделить диапазон ячеек, состоящий из числа строк = 5 (обязательно всегда) и числа столбцов = N+1, где N – количество неизвестных параметров Х

Т.е. для однофакторной задачи y=f(x) – число строк = 5. а число столбцов = 2, т.к. N=1.

Если задача многофакторная, т.е. e=f(x₁,x₂,x₃), то нужно выделить 5 строк и (3+1) столбцов.

В приведенной ниже таблице видно, в каком порядке возвращается дополнительная регрессионная статистика.

	A	B	C	D	E
	mn	mn-1	…	m2	m1	b
	sen	sen-1		se2	se1	seb
	r2	sey
	F	df
	ssper	ssjcn

Поскольку функции ЛИНЕЙН И ЛГРФПРИБ возвращают массив значений, функции должны задаваться в виде формулы массива, т.е. для завершения ввода функций ЛИНЕЙН, ЛГРФПРИБЛ необходимо нажать комбинацию клавиш Ctrl+Shift+Enter.

ЗАДАНИЕ №1.

1. Используя метод наименьших квадратов (МНК), к заданному варианту подобрать одну из эмпирических функций регрессии (y=a*Ln(x)+b, y=a*x ^b , y=a*e ^b*x , y=a*b ^x ).

2. Используя функции рабочего листа (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБЛ) к заданному варианту подобрать эмпирическую функцию регрессии

3. Объяснить, какая из выбранных функций подходит лучше и почему.

2. Построить графики экспериментальных и полученных теоретических данных.

Варианты заданий.

.

№ варианта
№ задач	1,2	3,4	5,6	7,1	2,3	4,5	6,7	7,1	6,2	5,3	4,1	5,7	3,5	2,3	4,6

Задачи:

1. Плотность асфальтобетона в зависимости от времени уплотнения

Время уплотнения (мин)	0,5	0,8		1,5	1,8		2,4	2,7
Плотность асфальтобетона (т/м^3)	1,5	1,8	2,07	2.18	2,39	2,45	2,46	2,48	2,5

2.Плотность грунта от количества проходов катка

Количество проходов катка
Плотность грунта (т/м^3)	1,5	1,7	1,78	1,9	1,99	2,01	2,09	2,09	2,1	2,07	2,09	2,1

3 Дальность отбрасывания снега в зависимости от угла наклона ротора снегоочистителя

Наклон ротора.(градусы)
Дальность отбрасывания.(м)

4.Сила сопротивления перемещению транспортной машины в зависимости от угла подъема дороги.

Угол подъема (градусы)
Сила сопротив- ления.(кН)

5.Время цикла экскаватора от угла поворота экскаватора.

Угол поворота. (градусы)^о
Время цикла.(сек)

6. Время погрузки самосвала в зависимости от вместимости ковша экскаватора.

Вместимость ковша.м^3	0,2	0,4	0,5	0,7	0,8		1,1	1,3	1,4	1,5	1,6
Время погрузки.c

7. Зависимость удельной производительности сит от размера ячеек.

Размер сит	0,417	1,5	3,4	10,2	15,3	17,6	20,3	23,7	25,4
Удельная производительность, T/		15,3					57,5	62,4	64,5

ЗАДАНИЕ №2.

1. Для многофакторной задачи, приведенной ниже, подобрать лучшую регрессионную зависимость.

Предположим, что застройщик оценивает стоимость группы небольших офисных зданий в традиционном деловом районе.

Застройщик может использовать множественный регрессионный анализ для оценки цены офисного здания в заданном районе на основе следующих переменных.

Переменная	Смысл переменной
y	Оценочная цена здания под офис
x1	Общая площадь в квадратных метрах
x2	Количество офисов
x3	Количество входов
x4	Время эксплуатации здания в годах

Общая площадь (x1)	Количество офисов (x2)	Количество входов (x3)	Время эксплуатации (x4)	Оценочная цена (y)
				142 000
				144 000
		1,5		151 000
				150 000
				139 000
				169 000
		1,5		126 000
				142 900
				163 000
				169 000
				149 000

Дата добавления: 2015-11-04