Средствами MS Excel

Построение различных аппроксимирующих зависимостей средствами MS Excel реализуется в виде свойства диаграммы – линия тренда. Это демонстрируется следующими примерами.

ПРИМЕР 22.2. В результате эксперимента была определена некоторая табличная зависимость.

X	0.15	0.16	0.17	0.18	0.19	0.20
Y	4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Выбрать и построить аппроксимирующую зависимость. Построить графики табличной и подобранной аналитической зависимости. Вычислить ожидаемое значение в точках: x₁=0.1539, x₂=0.2569, x₃=0.28.

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

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

2. Добавление к этому графику линии тренда.

Рис. 22.9

Порядок решения задачи.

– Выделяются экспериментальные точки на графике,

–щелчком правой кнопкой мыши по графику вызывается появление контекстного меню, в котором выбирается команда Добавитьлинию тренда. Появившееся диалоговое окно (Рис. 22.10) позволяет построить аппроксимирующую зависимость. На первой вкладке этого окна указывается вид аппроксимирующей зависимости (в рассматриваемом случае необходимо выбрать полиномиальную зависимость второй степени). На второй вкладке определяются параметры построения:

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

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

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

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

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

Рис. 22.10

На рис. 22.11 изображена полученная диаграмма.

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

 

Рис. 22.11