Excel лабораторная работа линии тренда

Рассмотрим механизм использования
линии тренда на примере следующей
задачи.

В результате эксперимента
была определена некоторая табличная
зависимость. Выбрать и построить
аппроксимирующую зависимость. Построить
графики табличной и подобранной
аналитической зависимости. Вычислить
ожидаемое значение в указанных точках.

Решение задачи можно разбить на следующие
этапы.

1. Ввод исходных данных и
   построение точечного графика (см. рис.
   12).

Рисунок 12

1. Выделяем экспериментальные
   точки, щелкаем правой кнопкой мыши и
   выбираем команду Добавить
   линию
   тренда. Появляется
   диалоговое окно (см. рис. 13), позволяющее
   построить аппроксимирующую зависимость.

Рисунок 13

На первой вкладке выбираем
вид аппроксимирующей зависимости
(выбираем полиномиальную зависимость
второй степени). На второй необходимо
определить параметры построения:

a. Название
аппроксимирующей зависимости;

b. Прогноз
вперед (назад) на n
единиц, этот параметр определяет, на
сколько единиц вперед (назад) необходимо
продлить линию тренда.

c. Показывать
ли точку пересечения кривой с прямой
Y=const;

d. Показывать аппроксимирующую
функцию на диаграмме или нет (параметр
показывать уравнение на диаграмме);

e. Помещать ли на диаграмму
величину среднеквадратичного отклонения
или нет (параметр поместить на диаграмму
величину достоверности аппроксимации);

После щечка по кнопке OK
диаграмма примет вид (см. рис. 14):

Рисунок 14

1. Для расчета ожидаемых значений
   в точках 0.1539, 0.2569, 0.28 введем эти значения
   в ячейки B4:D4.
   В ячейку B5 введем
   формулу подобранной аппроксимирующей
   зависимости (=371.62*B4^2–68.093*B4+6.1891)
   и скопируем ее в ячейки C5,D5.
   Рабочий листпримет вид:

1. Добавим полученные расчетные
   значения на диаграмму. Для этого на
   диаграмме выделим экспериментальные
   значения, щелкнем правой кнопкой мыши
   и выберем команду Исходные
   данные. Добавим туда
   Рассчитанные значения
   (см. рис. 15).

Рисунок 15

5. В результате диаграмма
примет вид (см. рис. 16).

Рисунок 13

Аналогично с помощью линии
тренда можно подобрать и параметры
других типов зависимостей (линейной,
логарифмической и экспоненциальной и
т. д.).

Практические задания

1. Реализовать в Excel
   Пример описанный в методических
   указаниях.

2. Построить линию тренда с
   использованием полиномиальной
   зависимости для экспериментальных
   данных в соответствии со своим вариантом
   данных для многочлена k-ой
   степени. Сравнить коэффициенты многочлена
   k-ой степени с полученными
   в заданиях №1 и №2.

3. Для второго набора экспериментальных
   данных построить линии тренда всех
   видов зависимостей и определить ту
   которая наилучшим образом согласуется
   с экспериментом.

Варианты заданий

Вариант №1

Вариант №2

Вариант №3

Вариант №4

Вариант №5

Вариант №6

Вариант №7

Вариант №8

Вариант №9

Вариант №10

Вариант №11

Вариант №12

Вариант №13

Вариант №14

Вариант №15

Вариант №16

Вариант №17

Вариант №18

Вариант №19

Вариант №20

Вариант №21

Вариант №22

Вариант №23

Вариант №24

Вариант №25

Вариант №26

Вариант №27

Вариант №28

Соседние файлы в папке Методички

• #
• #
• #
• #
• #
• #
• #

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

Прогнозирование по обычному графику невозможно, так как его коэффициент детерминированности (R^2) будет близок к нулю.

Именно поэтому применяются специальные функции.

Сейчас мы их построим, настроим и проанализируем.

Легкая версия построения

Процесс построения линии тренда состоит из трех этапов: ввод в excel исходных данных, построение графика, выбор линии тренда и ее параметров.

Начнем с ввода данных.

1. Создаем в Excel таблицу с исходными данными.

(Рисунок 1)

2. Выделяем ячейки B3:B17 и перейдя на закладку «Вставка» выбираем «График».

(Рисунок 2)

3. После того как график построен, можно добавить подписи и заголовок.

Для начала кликнем левой кнопкой мыши по границе графика, чтобы выделить его.

Затем перейдем на закладку «Конструктор» и выберем «Макет 1».

Далее печатаем текст заголовка.

(Рисунок 3)

4. Переходим к построению линии тренда. Для этого снова выделяем график и переходим на закладку «Макет».

(Рисунок 4)

5. Нажимаем на кнопку «Линия тренда» и выбираем «линейное приближение» или «экспоненциальное приближение».

(Рисунок 5)

Так мы построили первичную Линию тренда, которая может мало соответствовать действительности.

Это наш промежуточный результат.

(Рисунок 6)

И поэтому потребуется настроить параметры нашей линии тренда или выбрать другую функцию.

Профессиональная версия: выбор линии тренда и настройка параметров

6. Нажимаем на кнопку «Линия тренда» и выбираем «Дополнительные параметры и линии тренда».

(Рисунок 7)

7. В окне «Формат линии тренда», мы ставим флажок напротив «поместить на диаграмму величину достоверности аппроксимации R^2 и нажимаем кнопку «закрыть».

Видим на диаграмме коэффициент R^2= 0,6442

(Рисунок

8. Отменяем изменения. Выделяем график, нажимаем на закладку «Макет», кнопку «линия тренда» и выбираем «Нет».

9. Переходим в окно «Формат линии тренда», но уже для того, чтобы выбрать «Полиноминальную» линию тренда, меняем степень, добиваясь показателей коэффициента R^2= 0,8321

(Рисунок 9)

Прогноз

Если нам нужно предположить, какие данные могли бы быть получены в следующем измерении, в окне «Формат линии тренда», указываем количество периодов на которые делается прогноз.

(Рисунок 10)

На основе прогноза мы можем предположить, что 25 января количество набранных баллов было бы от 60 до 70.

• Мне нравится