Результаты коэффициентов экспоненциальной аппроксимации.

Таблица 6.

В таблице 6 в ячейках A42:B43 записана формула {=МОБР(A38:B39)}.

В ячейках F42:E43 записана формула {=МУМНОЖ(A42:B43,C38:C39)}.

В ячейке E41 записана формула =EXP(F41).

Вычислим среднее арифметическое и по формулам:

Результаты расчета и средствами Microsoft Excel представлены в

таблице 7.

Вычисление средних значений X и y.

Таблица 7.

В ячейке B44 записана формула =B17/15.

В ячейке B45 записана формула =C17/15.

Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминированности данные целесообразно расположить в виде таблицы 8, которая является продолжением таблицы 3.

Вычисление остаточных сумм.

Таблица 8.

Поясним как таблица 8 составляется.

Ячейки B1:B16 и C1:C16 уже заполнены (см. табл. 2).

Далее делаем следующие шаги.

Шаг 1. В ячейку K2 вводим формулу =(B2-$B$46)*(C2-$B$47).

Шаг 2. В ячейки K3:K16 эта формула копируется.

Шаг 3. В ячейку L2 вводим формулу =(B2-$B$46)^2.

Шаг 4. В ячейки L3:L16 эта формула копируется.

Шаг 5. В ячейку M2 вводим формулу =(C2-$B$47)^2.

Шаг 6. В ячейки M3:M16 эта формула копируется.

Шаг 7. В ячейку N2 вводим формулу =($F$24+$F$25*B2-C2)^2.

Шаг 8. В ячейки N3:N16 эта формула копируется.

Шаг 9. В ячейку O2 вводим формулу =($F$33+$F$34*B2+$F$35*B2^2-C2)^2.

Шаг 10. В ячейки O3:N16 эта формула копируется.

Шаг 11. В ячейку P2 вводим формулу =($F$41*EXP($F$43*B2)-C2)^2.

Шаг 12. В ячейки P3:P16 эта формула копируется.

Последующие шаги делаем с помощью автосуммирования .

Шаг 13. В ячейку K17 вводим формулу =СУММ(K2:K16).

Шаг 14-18. Аналогично, шагу 13 для ячеек L17, M17, N17, O17P17.

Теперь проведем расчеты коэффициента корреляции по формуле

(только для линейной аппроксимации) и коэффициента детерминированности по формуле:

.

Результаты расчетов средствами Microsoft Excel представлены в таблице 9.

Результаты расчета.

Таблица 9.

В таблице 9 в ячейке E50 записана формула =K17/КОРЕНЬ(L17*M17).

В ячейке E51 записана формула =1-N17/M17.

В ячейке E53 записана формула =1-O17/M17.

В ячейке E55 записана формула =1-P17/M17.

Анализ результатов расчетов показывает, что квадратичная аппроксимация наилучшим образом описывает экспериментальные данные.

4. Построение графиков в Excel .

Исследуем характер зависимости в три этапа:

• Построим график зависимости.

• Построим линию тренда.

• Получим числовые характеристики коэффициентов этого уравнения.

Рис 2. График линейной аппроксимации.

Рис 3. График квадратичной аппроксимации.

Рис 4. График экспоненциальной аппроксимации.

Полученное при построении линии тренда значение коэффициента детерминированности для экспоненциальной зависимости не совпадает с истинным значением (это значение было сосчитано вручную выше) поскольку при вычислении коэффициента детерминированности с помощью функции ЛИНЕЙН используются не истинные значения , а преобразованные значения с дальнейшей линеаризацией.