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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Y = 0.9982*X + 1.0418, (3.5)

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

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

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

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

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

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

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

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