Построение эмпирических формул методом наименьших квадратов (МНК) средствами пакета Microsoft Excel, страница 6

Таким образом, квадратичная аппроксимация имеет вид:

 .                                       (14)

Решение системы (13) проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 5.

                                                                                                     Таблица 5

Таблица 5 построена в 4 шага:

Шаг 1 В ячейках A40:С42 записана формула {=МУМНОЖ(ТРАНСП(C2:E26);C2:E26)}.

Шаг 2 В ячейках D40:D42 записана формула {=МУМНОЖ(ТРАНСП(C2:E26);B2:B26)}.

Шаг 3 В ячейках A44:C46 записана формула {=МОБР(A40:C42)}.

Шаг 4 В ячейках F44:F46 записана формула {=МУМНОЖ(A44:C46;D40:D42)}.

Расчет коэффициентa детерминированности.

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

Для его описания рассмотрим следующий метод:

                                                           (15)

                                                    (16)

Формула (16) характеризует разброс данных. Несложно доказать равенство (17):

                          (17)

где выражение (17а) характеризует отклонение эмпирических данных от теоретических и называется остаточной суммой квадратов, а выражение (17б) характеризует разброс данных и называется регрессивной суммой.

                                                  (17а)

                                                   (17б)

Коэффициент детерминированности рассчитывается по формуле (18) ,причём  чем ближе он к единице тем лучше установлена теоретическая зависимость:

                                                             (18)

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

таблица 6

В ячейке I30 записана формула =СУММ(B2:B26)/25.

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

Таблица 7

Таблица 7 составлялась.

Ячейки A2:A26 и B2:B26 уже заполнены (см. табл. 3).

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

Шаг 1. В ячейку G2 вводим формулу =(B2-$Е$35*А2-$Е$34)^2.

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

Шаг 3. В ячейку H2 вводим формулу =(В1-$H$30)^2.

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

Шаг 5. В ячейку I2 вводим формулу =(B2-A2*A2*$F$48-A2*$F$47-$F$46)^2.

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

Шаг 7. В ячейку G28 вводим формулу =CУММ(G2:G26).

Шаг 8. В ячейку H28 вводим формулу =CУММ(H2:H26).

Шаг 9. В ячейку I28 вводим формулу=CУММ(I2:I26).

Составим таблицу 8 коэффициентов детерминированная

Таблица 8