Прогнозирование в Ехсеl с помощью линий тренда.

Задача 1. Имеются условные данные динамики выпуска продукции предприятия за 10 лет, представленные в табл. 1.1. Где: Х – порядковый номер года с 2001 по 2012 год; У – объем валовой продукции предприятия (ден. ед.). Необходимо:
1. Получить прогноз выпуска валовой продукции предприятия на 2011 год с использованием следующих функций: линейная, логарифмическая, полиномиальная второй степени, степенная, экспоненциальная.
2. С использованием коэффициента детерминации R^2 определить лучшую модель, по которой произвести прогноз на 2011 год.

Решение.
1. Загрузите «Ехсеl» .
2. Введите данные в таблицу. В первом листе «Ехсеl» по колонкам ввести значения Х и У, взятые из табл. 1.1. Размещение таблицы смотрите на рис. 1.1.
3. Постройте график зависимости У от Х.
• Выделите всю таблицу с названиями колонок (Курсор мышки поставьте на ячейку А1, нажмите левую клавишу мышки и протяните курсор до ячейки В11, отпустите кнопку мышки).
• Постройте диаграмму «Точечная» (В главном меню выберите Вставка, на строке инструментов Диаграммы щелкните пиктограмму Точечная, выберите вид: «Точечная с гладкими кривыми и маркерами»; нажмите на кнопку «Готово»). Результат действий представлен на рис. 1.1.

Рис. 1.1. Таблица данных и график зависимости У от Х.
4. Ознакомьтесь с меню «Линия тренда».
• Вызовите на рабочее поле электронной таблицы окно "Линия тренда".(Для этого поставте курсор мыши на плюс и выбире пунк линия тренда.)

• Ознакомьтесь с содержанием надписей на окне «Линия тренда».(Для этого пкм по линии тренда и выбираем пунк формат линии тренда.)

Рис. 1.2. Окно «Линия тренда»
5. Получите прогноз по линейной функции на одну дату вперед. (Выберите тип тренда «Линейная», установите параметры, показанные на рис. 1.3, нажмите на кнопку «ОК»). На экране появится изображение, представленное на рис. 1.4
Рис. 1.3 Параметры «Линия тренда»
6. Красиво оформите диаграмму на свой художественный вкус. При наличии времени можно воспользоваться всеми средствами красочного оформления диаграммы.
7. Произведите расчеты теоретических значений Ур по линейной функции. Ур вычисляется по формуле Урi = ао+а1*Хi при i = 1, 2, …, 10, где значения а0 и а1 можно взять из изображения на диаграмме. Дополните таблицу расчетными значениями Урi для каждого значения Хi. При Х =11 получите прогнозное значение У на 2011 год. Результаты расчетов и содержимое расчетной формулы изображены на рис. 1.5.
Рис.1.5. Расчет теоретических значений Ур и прогнозного значения У на 2011 г.
Пояснения к проведению расчетов по формуле Ур = а0+а1*Х. • В ячейку В14 и В15 введите соответственно значения коэффициентов а0 и а1, взятых из диаграммы рис. Результаты действия изображены на рис. 1.5. • В ячейку С2 введите формулу =В14+В15*А2, адреса ячеек: В14 и В15 сделайте абсолютными, то есть формула должна иметь следующий вид =$B$14+$B$15*A2 (для того, чтобы при копировании полученной формулы адреса коэффициентов не изменялись). Эти действия можно выполнить непосредственной вставкой знака $ в необходимое место или автоматизировать расстановку знака $ c использованием клавиши F4 в следующей последовательности: активизируйте ячейку С2, введите формулу =В14+В15*А2, клавишу Enter не нажимайте, (если уже нажали на клавишу Enter, то вызовите содержимое ячейки С2 для редактирования), поставьте курсор мышки в начале или в конце адреса В14 (перед буквой В или после цифры 4), нажмите на клавиатуре клавишу F4. В результате этого действия формула примет следующий вид =$B$14+B15*A2. Аналогичным образом сделайте абсолютным адрес В15, завершите ввод формулы нажатием клавиши Enter. В результате формула должна принять следующий вид =$B$14+$B$15*A2. Эта формула изображена на рис. 1.5 в строке ввода. • Произведите копирование ячейки С2 в диапазон С3:С11. Активизируйте ячейку С2, поставьте курсор мышки на маркер заполнения, нажмите левую кнопку мышки и перетащите маркер заполнения через заполняемые ячейки до ячейки С11, отпустите левую кнопку мышки. Результат расчетов представлен на рис. 1.5. 8. Выполните самостоятельную работу. Самостоятельно повторите пункты 3, 4, 5 для получения прогноза по следующим функциям: логарифмическая, полиномиальная второй степени, степенная, экспоненциальная.
8. Выполните самостоятельную работу. Самостоятельно повторите пункты 3, 4, 5 для получения прогноза по следующим функциям: логарифмическая, полиномиальная второй степени, степенная, экспоненциальная.
9. Выбирите лучшую тенденцию. По критерию R^2 выберите функцию, которая лучше всех описывает тенденцию зависимости У от Х. Сделайте выводы о прогнозе по лучшей функции.
Примечание. R^2 – коэффициент детерминации равен доле объясненной вариации за счет выбранной тенденции или упрощенно – R^2 равен доле исходных данных, которые подчиняются выбранной тенденции.

Исходные данные индивидуальных заданий.

Имеются исходные данные прибыли Райпотребсоюзов РФ за 11 месяцев, представленные в таблице .
Необходимо получить прогноз на 12-й месяц с использованием различных инструментальных средств Ехсеl, где:
Х – порядковый номер месяца;
Уi – прибыль соответствующих потребсоюзов.
Для примера приведем рисунки по вариантуY6.