Решение. 1. Открыть файл Регрессия 1

1. Открыть файл Регрессия 1.

2. Для осуществления прогноза нужно построить базовую линию. Для этого:

а) ввести в ячейку А4 заголовок «Базовая линия»;

б) выделить ячейки В4:К4;

в) выполнить команды:

Вставка Þ Функция Þ Статистические Þ ТЕНДЕНЦИЯ Þ ОК.

г) ввести параметры:

известные_значения_y В3:К3

известные_значения_x В2:К2

д) для ввода формулы во все выделенные ячейки (В4:К4) одновременно нажать клавиши Ctrl+Shift+Enter. В результате получим табл. 4.3.4.

Таблица 4.3.4

	A	B	C	D	E	F	G	H	I	J	K
	Вычисление коэффициентов регрессии
	Число автомобилей
	Реальные затраты, у.е.				6,5		8,5
	Базовая линия	3,127	4,188	5,248	6,309	7,370	8,430	9,491	10,552	11,612	12,673

В четвертой строке распечатаны значения у, вычисленные функцией ТЕНДЕНЦИЯ. Так как число наблюдений невелико, имеются отклонения от экспериментальных значений у. Можно видеть, что значение у* в табл. 4.3.4 практически совпадает со значением; у* из табл. 4.3.2.

3. Прогнозирование затрат для 11 автомашин.

3.1.Ввести в ячейку L2 число 11.

3.2. Активизировать ячейку L4.

3.3 Выполнить команды:

Вставка Þ Функция Þ Статистические Þ ТЕНДЕНЦИЯ Þ ОК.

3.4. Ввести:

известные_значения_y В3:К3

известные_значения_x В2:К2

новые_значения_x L2

ОК.

В ячейке L4 появится прогноз затрат для 11 машин (13,733 у.е).

4. Прогнозирование затрат для 13 машин.

4.1. Вводим в ячейку М2 число 12, в N2 - число 13.

4.2.Формулу в ячейке L4 подготовим к копированию. Запишем ее в виде =ТЕНДЕНЦИЯ($ВЗ: $КЗ;$В2: $K2;L2).

4.3. Скопируем формулу в ячейки M4:N4, получаем значения 14,794 у.е. и 15,855 у.е.

Составление нелинейного прогноза. Функция РОСТ

Синтаксис этой функции аналогичен синтаксису функции ТЕНДЕНЦИЯ:

=РОСТ(известные_значения_y;известные_значения_x;новые_значения_x;конст)

Задание 3

Менеджер по продажам книг, анализируя продажу одной из книг, получил следующую статистику (табл. 4.3.5).

Ему нужно принять решение, сколько книг следует заказать на 3 следующих недели.

Таблица 4.3.5

Неделя	Число проданных книг

Характер изменения спроса за последние недели показывает, что функция спроса имеет нелинейный характер. Воспользуемся функцией РОСТ.

Решение

1. Заполнить ячейки А3:В12 табл. 4.3.6.

Таблица 4.3.6

	А	В	С
	Анализ спроса на книги
	Фактический спрос	Неделя	Прогноз
			0,95
			1,67
			2,95
			5,20
			9,17
			16,17
			28,51
			50,28
			88,67
			156,37
			275,75
			486,28
			857,54

2. Построить базовую линию:

2.1. Выделить С3:С12

2.2. Вставка Þ Функция Þ Статистические Þ РОСТ Þ ОК.

2.3. Ввести:

известные_значения_y А3:А12

известные_значения_x В3:В12

2.4. Ctrl+Shift+Enter.

3. Прогнозирование

Функция РОСТ позволяет осуществить прогнозирование для массива данных. Поэтому:

3.1. Ввести в ячейки В12:В15 числа 11, 12, 13;

3.2. Выделить ячейки С13:С15;

3.3. Ввести формулу =РОСТ(АЗ:А12;ВЗ:В12;В13:В15);

3.4. Ctrl+Shift+Enter