Исследовать характер изменения с течением времени уровня производства некоторой продукции и подобрать
аппроксимирующую функцию, располагая следующими данными:
Год Производство
продукции
1997
17,1
1998
18,0
1999
18,9
2000
19,7
2001
19,7
Решение
1 Для
построения диаграммы, прежде всего, необходимо ввести данные в рабочую
таблицу. Вводим в ячейку А1 слово Год. Затем в
ячейки А2:А6 последовательно
вводим годы, начиная с 1997. Далее в ячейку В1 заносим
слово Продукция и устанавливаем табличный курсор в ячейку 82. Здесь
должно оказаться значение 17,1 соответствующее значению года в ячейке А2. Аналогично заполняем ячейки ВЗ:В6.
2 Далее по
введенным в рабочую таблицу данным необходимо построить диаграмму. Поскольку
здесь необходимо строить динамику изменений производства продукции, не
привязываясь к конкретному году, а от отвлеченных переменных — выберем
диаграмму График.
Щелчком указателя мыши на кнопке на
панели инструментов вызываем Мастер диаграмм. В появившемся диалоговом окне
выбираем тип диаграммы График, вид — левый средний. После нажатия кнопки Далее указываем диапазон данных — В1:Вб (с помощью мыши).
Проверяем положение переключателя Ряды в: столбцах. Выбираем вкладку Ряд и с
помощью мыши вводим диапазон подписей оси X: А2:А6.
Нажав кнопку Далее, вводим название диаграммы — Производство
продукции, название осей X и У: Годы и
Условные единицы, соответственно. Нажимаем кнопку Готово.
Получен график экспериментальных
данных.
3. Осуществим аппроксимацию
полученной кривой полиномиальной функцией второго порядка, поскольку кривая
довольно гладкая и не сильно отличается от прямой линии. Для этого указатель
мыши устанавливаем на одну из точек графика и щелкаем правой кнопкой. В
появившемся контекстном меню выбираем пункт Добавить
линию тренда. Появляется диалоговое окно Линия тренда (рис. 1).
Рис. 1. Вкладка Тип диалогового окна Линия тренда
В этом окне на вкладке Тип выбираем
тип линии тренда — Полиномиальная и устанавливаем степень — 2.
Затем открываем вкладку Параметры (рис. 2) и устанавливаем флажки в поля показывать уравнение на диаграмме и поместить на диаграмму
величину достоверности аппроксимации (R^2). После чего нужно щелкнуть на кнопке ОК.
Рис.
2. Вкладка Параметры диалогового окна
Линия тренда
В результате получим на диаграмме
аппроксимирующую кривую (рис. 3).
Рис.
3Экспериментальные данные,
аппроксимированные полиномиальной кривой
Как видно из
рис. 3, уравнение наилучшей полиномиальной аппроксимирующей
функции для некоторых отвлеченных значений х
(1, 2, 3, ...) выглядит как
При этом точность аппроксимации
достаточно высока — R2 = 0,986.
4. Попробуем улучшить качество аппроксимации выбором другого типа функции
(возможно более адекватного). Здесь возможным вариантом представляется
логарифмическая функция. Для этого повторяем операции п. 3. за исключением
того, что в окне Линия тренда на вкладке Тип выбираем тип линии тренда —
Логарифмическая.
В результате получим другой вариант аппроксимации — логарифмической кривой (рис. 4).
Рис.
4. Экспериментальные данные,
аппроксимированные логарифмической кривой,
Как можно видеть из рис. 4, уравнение наилучшей логарифмической аппроксимирующей функции несколько уступает по точности аппроксимации полиномиальной кривой — R2 = 0,9716 < 0,986. Поэтому если нет каких-либо теоретических соображений, то можно считать, что наилучшей аппроксимацией является аппроксимация полиномиальной функцией второй степени (из двух рассмотренных вариантов).
Замечание