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

ПРИМЕР 9.2.В результате эксперимента была определена некоторая табличная зависимость. Построить график экспериментальных значений. Выбрать и построить график аппроксимирующей зависимости (линия тренда). С помощью формулы, подобранной аппроксимирующей зависимостью вычислить ожидаемое значение в указанных точках и добавить рассчитанные значения на диаграмму. Настроить график.

x1 = 0,1539; x2 = 0,1769; x3 = 0,1825; x4=0,1911; x5=0,22
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

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

  1. Ввод исходных данных и построение точечного графика. Строим и настраиваем график, как показано на рис. 9.9. Обратите внимание, что мы изменили минимальное и максимальное значение для оси OX.

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

Рис. 9.9

  1. Добавление к этому графику линии тренда. Рассмотрим этот процесс подробно:
 

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

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

o Название аппроксимирующей зависимости (Квадратичная зависимость).

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

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

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

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

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

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

   
Рис. 9.10
Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы - линия тренда - student2.ru
Рис. 9.11

· Для расчета ожидаемых значений в точках x1 = 0,1539; x2 = 0,1769; x3 = 0,1825; x4=0,1911; x5=0,22введем эти значения в ячейки D2:D6. В ячейку E2 введем формулу подобранной аппроксимирующей зависимости (=371.6*D2^2-68.093*D2+6.189) и скопируем ее в ячейки E3:E6. Фрагмент рабочего листа примет вид (рис. 9.11а).

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

Рис. 9.11

· Добавим полученные расчетные значения на диаграмму. Для этого на диаграмме выделим экспериментальные значения, щелкнем правой кнопкой мыши и выберем команду Выбрать данные….и кнопка«Добавить»,появится окно Изменение ряда (рис. 9.12). Добавим туда имя ряда Рассчитанные значенияи введем значения для X и Y.

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

 
Рис. 9.12

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

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

ПРИМЕР 9.3. В результате эксперимента получена зависимость z(t):

t 0,66 0,9 1,17 1,47 1,7 1,74 2,08 2,63 3,12
Z 38,9 68,8 64,4 66,5 64,95 59,36 82,6 90,63 113,5

Построить экспериментальные точки. Подобрать коэффициенты зависимости заданного уравнения Z(t)=At4+Bt3+Ct2+Dt+K методом наименьших квадратов, сведя задачу к задаче оптимизации. Определить суммарную ошибку, рассчитать точки для построения и построить график. Настроить график.

Эта задача эквивалентна задаче нахождения минимума функции пяти переменных:

Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы - линия тренда - student2.ru (9.13)

· Введем табличную зависимость в рабочий лист MS Excel и построим график функции (см. рис.9.13а)

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

Рис. 9.13а

· Рассмотрим процесс решения задачи оптимизации (формула 9.13). Пусть значения А, В, С, D и К хранятся в ячейках F1:F5(рис. 9.13б). В ячейку C2введем значение функции At4+Bt3+Ct2+Dt+K для первой точки (ячейка A2): C2 = $F$1* A2^4 + $F$2* A2^3 + $F$3* A2^2 + $F$4* A2 + $F$5.

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

Рис. 9.13б

· Получим ожидаемое значение (в начале 0) в точке C2. Затем растянем эту формулу на весь диапазон C2:C10. У нас получились нули, т.к. коэффициенты зависимости еще не определены. В ячейку D2введем формулу, вычисляющую квадрат разности между экспериментальными и расчетными точками:

D2 = (B2-C2)^2,

и растянем ее на диапазон D2:D10. В ячейке В12 будем хранить суммарную квадратичную ошибку (см. формулу 9.13). Для этого введем формулу: В12 = СУММ(D2:D10)

· В идеальном варианте это ошибка должна быть минимальной. Теперь осталось с помощью решающего блока (Данные Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы - линия тренда - student2.ru Поиск решения) решить задачу оптимизации без ограничений, заполнив соответствующим образом появившееся диалоговое окно (рис. 9.14).

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

 
Рис. 9.14

· Результатом работы решающего блока будет вывод в ячейки F1:F5 значений параметров A, B, C, D, K функции At4+Bt3+Ct2+Dt+K. В ячейках C2:C10 получим ожидаемые значение функции в исходных точках. В ячейке B12 будет храниться суммарная квадратичная ошибка.

· Поместим эти точки в виде отдельной линии на графике. Для этого на диаграмме выделим экспериментальные значения, щелкнем правой кнопкой мыши и выберем команду Выбрать данные….и кнопка«Добавить»,появится окно Изменение ряда (рис. 9.15).

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

Рис. 9.15

· После заполнения окна Изменение ряда,нажимаем ОК, выделяем полученный график и настраиваем его. На рис. 9.16 отображает внешний вид рабочего листа MS Excel после проведенных вычислений.

По мнению авторов, использование решающего блока - это один из эффективных способов реализации метода наименьших квадратов с помощью MS Excel.

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

ВАРИАНТЫ ЗАДАНИЙ

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

2.На втором рабочем листе ввести табличную зависимость и средствами MS Excel построить график экспериментальных значений. Добавить к этому графику график аппроксимирующей зависимости(линия тренда). С помощью формулы, подобранной аппроксимирующей зависимостью, вычислить ожидаемое значение в заданных точках и добавить рассчитанные значения на диаграмму. Настроить диаграмму.

3.На третьем рабочем листе ввести табличную зависимость и средствами MS Excel построить график экспериментальных значений. Подобрать для своего варианта уравнения коэффициенты зависимости, методом наименьших квадратов, сведя задачу к задаче оптимизации. Определить суммарную ошибку. Рассчитать точки для построения и построить график. Настроить диаграмму.

Наши рекомендации