Задание 18

Для данных из задания 16 найти наилучшее линейное приближение с помощью процедуры Регрессия.

Решение

1. Для построения наилучшей прямой другим способом выберите Сервис – Анализ данных – Регрессия, в поле Входной интервал Y укажите диапазон, содержащих значение функции В2:В6, в поле Входной интервал Х укажите диапазон, содержащих значение независимой переменной А2:А6, установите переключатель в поле Выходной интервал и укажите ячейку А10, нажмите Ok.

2. В выходном диапазоне появятся результаты регрессионного анализа. В строке Регрессия в столбце Значимость F находится уровень значимости критерия Фишера, который должен быть меньше чем 0,05, чтобы модель была значима.

3. В строке Y-пересечение находится коэффициент b, в строке Переменная Х1 – коэффициент а, в столбце Р-значение приводится достоверность отличия соответствующих коэффициентов от нуля, если Р>0,05, коэффициент может считаться нулевым, что означает, что соответствующая независимая переменная не влияет на зависимую переменную.

Задание 19

Имеются данные спроса на продукции малого предприятия за 9 месяцев. Необходимо спрогнозировать значения продаж за 10, 11 и 12 месяцы.

Месяц	1	2	3	4	5	6	7	8	9
Спрос (тыс.руб.)	65	58	72	69	64	75	59	71	68

Решение

1. В диапазон А2:А10 введите порядковые числа месяцев, в диапазон В2:В10 введите данные продаж.

2. Для нахождении каждой точки на линии регрессии, выделите диапазон С2:С10, вызовите Мастер функций, выберите Статистические, функция ТЕНДЕНЦИЯ.

3. В поле Изв_знач_y укажите блок В2:В10, в поле Изв_знач_х – блок А2:А10, третий и четвертый аргумент опустите. Нажмите Ctrl+Shift+Enter.

4. Для вычисления спроса за 10, 11 и 12 месяцы, введите числа от 10 до 12 в ячейки А11:А13. Выделите С11:С13, введите формулу =ТЕНДЕНЦИЯ(В2:В10; А2:А10; А11:А13), и нажмите Ctrl+Shift+Enter.

Задание 20

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

Время после выброса (час)	Концентрация вещества (мг/л)
1	8,0
3	2,8
5	1,0
8	0,3

Необходимо аппроксимировать данные уравнением вида .

Решение

1. В диапазон А2:А5 введите время после выброса, в диапазон В2:В5 ведите соответствующие концентрации вещества.

2. Выделите диапазон С2:D2 под массив результатов, вызовите Мастер функций, выберите Статистические, функция ЛГРФПРИБЛ.

3. В поле Изв_знач_y укажите блок В2:В5, в поле Изв_знач_х – блок А2:А5, Константа – 1, Стат – 0. Нажмите Ctrl+Shift+Enter .

4. В результате в ячейке С2 получите значение коэффициента а – 0,63, а в ячейкеD2 – значение коэффициентаb – 11,84.

Таким образом, искомое аппроксимирующее уравнение имеет вид:

.

5. Построим диаграмму по исходным данным. Вызовите Мастер диаграмм, выберите:

• Тип диаграммы – Точечная, вид – второй с верху, нажмитеДалее;

• В поле Диапазонукажите с помощью мыши диапазон данных – В2:В5, установите переключательРяды в:столбцах,откройте вкладкуРяд, в полеЗначения Х, с помощью мыши укажите диапазон А2:А5,в полеИмявведите –Концентрация, нажмитеДалее;

• Введите название диаграммы – Концентрация вещества, название осей Х иY:ВремяиКонцентрациясоответственно, нажмитеГотово. Получен график экспериментальных данных (рис 3).

Рис 3. Экспериментальные данные, аппроксимируемые экспоненциальной функцией

Упражнения

1. Имеются данные о цене на нефть X и индексе акций нефтяных компаний Y. Постройте зависимость индекса акций нефтяных компаний от цены на нефть.

	A	B
	X	Y
	17,28	537
	17,05	534
	18,30	550
	18,80	555
	19,20	560
	18,50	562

2. Постройте зависимость жизненной емкости в литрах Y от роста в метрах Х₁ и возраста в годах Х₂ для группы мужчин:

	А	В	С
	Х1	Х2	Y
	1,85	18	5,4
	1,8	25	5,7
	1,75	20	4,8
	1,7	24	5,1
	1,68	21	4,5
	1,73	19	4,8
	1,77	22	5,1
	1,81	23	5,6
	1,76	18	4,7

3. Количество вложенных в производство средств и полученная в результате прибыль соотносится следующим образом:

Х	1,6	2,0	2,5	3,0	4,0	7,0
Y	8,5	9,0	11,0	13,0	22,0	70,0

Необходимо аппроксимировать данные уравнением вида и найти неизвестные параметры. Какая будет прибыль, если вложить 10,0 единиц?

4. Постройте зависимость зарплаты от возраста сотрудника отдела по следующим данным:

Возраст	20	50	45	40	25	30
Зарплата	1500	3200	3500	3500	2200	2500

12