Задание 5. Прогнозирование с помощью функций линейн() и лгрфприбл(),

В таблице (рис. 3.1.) приведены данные по продаже электробытовых товаров (холодильники, пылесосы, обогреватели и др.)1997 - 2004гг., полученные на основе данных фирмы "Бета"

Рис. 3. 1.. Данные для начала расчета по фирме "Бета"

Необходимо найти уравнение регрессии наилучшим образом описывающее результаты хозяйственной деятельности за 1997 – 2004 годы, полагая, что процессы, определяющие спрос населения на электробытовые товары остается неизменным в течение всего последующего времени.

F Внимание. В том случае, если в качестве независимой переменной используется дата, то введите дополнительную строку с номером этой даты. В нашем примере это ячейки A2:I2 и используйте ее в дальнейшем в качестве независимой переменной. Вновь напоминаем, что русифицированные версии Excel очень часто дают ошибку при использовании даты в качестве независимой переменной.

· В ячейку А5 введите текст: Линейная статистика.

· На рабочем листе Excel выделите область ячеек A6:B10.

· Вызовите мастер функций ЛИНЕЙН() – рис. 3.2.

· Для этого выделите команду ВСТАВКА / ФУНКЦИЯ / выберите Категорию Статистические и в ней выберите функцию ЛИНЕЙН().

Рис. 3. 2. Выбор функции ЛИНЕЙН().для расчета линейной статистики

(Для вызова функции можно также щелкнуть на кнопке Вставка функций строки формул) В открывшемся диалоговом окне (рис. 3.3):

Рис. 3. 3. Диалоговое окно Аргументы функции ЛИНЕЙН()

· В поле "Известные значения Y" введите адреса ячеек, содержащих эти значения (B4:I4).

· В поле "Известные значения Х" введите адреса ячеек, содержащих эти значения - (B2:I2).

· В поле "Статистика" введите "1".

· Нажмите клавиши Ctrl + Shift + Enter.

F Внимание. В результате выполненных действий, в выделенный интервал ячеек будут возвращены значения статистики уравнения линейной регрессии.

В ячейке А6 будет помещено значение коэффициента А, а в ячейке В6 – коэффициента В линейной регрессии. Таким образом, теоретические значения величин товарооборота можно подсчитать в ячейках B5:I5 по формуле:

Y = 0.9982*X + 1.0418, (3.5)

где X это Порядковый номер года.

· Постройте график, в который включите значения фактического и теоретического товарооборота фирмы в зависимости от номера года. Значение фактического товарооборота представьте в точечном виде, теоретического товарооборота – в пунктирном. Для фактического товарооборота постройте линию тренда, как это описано в задании 1 и результаты расчетов запишите в ячейках В5:I5 (рис. 3.4).

Рис. 3. 4. Фрагмент рабочего листа Excel с примером расчета функции ЛИНЕЙН()

F Внимание. Значения коэффициентов и теоретической зависимости и уравнения регрессии совпали. Таким образом, вы увидели, что для линейных процессов оба варианта поиска зависимости приводят к одинаковому результату.

Подобным же образом найдите уравнение логарифмического приближения - функция ЛГРФПРИБЛ() (рис. 3.5).

Рис. 3. 5. Фрагмент рабочего листа Excel с примером расчета функции ЛГРФПРИБЛ ()

· Найдите линию тренда и подсчитайте теоретические значения, оформите график, в соответствии с рис. 3.5.

Как видите, результаты расчетов достаточно близки.