Подбор зависимости экспериментальных данных методом наименьших квадратов

В ряде случаев, когда имеется набор экспериментальных данных, являющийся функ­цией некоторого аргумента X, необходимо описать зависимость какой-либо простой фун­к­цией y = f(x). При этом не требуется совпадение f(X_i) и Y_i. Критерием наилучшей по­до­б­ран­ной функции является (минимум суммы квадратов расстояний меж­ду линиями). Excel содержит средства для расчетов коэффициентов некоторых аппроксими­рую­щих функций.

Задача: Для заданного набора пар значений независимой переменной и функции оп­ре­де­лить наилучшее линейное приближение в виде прямой y = ax + b и в виде экспонен­ты .

Задайте в ячейках, начиная с A5:B5, несколько пар значений «экс­пе­ри­ментальных данных» (значения Y должны быть неотрицательными). (Задайте в качестве разделителя целой и дроб­ной части точку.)

В ячей­ках С3:F3 задайте фор­му­лы для расчета коэффициентов.

Выделите ячейку C3 и щелкните по кнопке “вставить функцию” в строке формул. В по­явившемся окне “Мастер функций” выберите категорию “ссылки и массивы”.

Выберите функцию ИНДЕКС() и нажмите OK.

На втором шаге мастера функций в списке Аргументы должна быть выбрана первая строка. Щелкните по кнопке OK.

Появится окно “Аргументы функции”. Курсор должен стоять в строке Массив.

В качестве аргумента функции ИНДЕКС() нужно задать другую функцию. В строке формул выберите в списке функций Другие функции. В окне мастера функций вберите категорию Статистические и функцию ЛИНЕЙН. Появится окно, в котором в качестве первого параметра (Известные значения Y) нужно указать диапазон, содержащий значе­ния Y (B5:B9).

Переведите курсор во второе поле (Известные значения X) и выделите диапазон, содержащий значения X (A5:A9).

В поле Конст задайте 1.

Не закрывая окно Аргументы функции, перейдите в строку формул и перенесите од­ну из последних скобок так, чтобы она стояла перед последней точкой с запятой. Закройте окно Аргументы функции. Формула должна иметь вид:

=ИНДЕКС(ЛИНЕЙН(B5:B9;A5:A9);1)

Аналогично вставьте в ячейку D3 формулу:

=ИНДЕКС(ЛИНЕЙН(B5:B9;A5:A9);2)

В результате в ячейках С3 и D3 Вы получите коэффициенты аппроксимирующей пря­мой.

В ячейки E3 и F3 вставьте формулы:

=ИНДЕКС(ЛГРФПРИБЛ(B5:B9;A5:A9);1)

=ИНДЕКС(ЛГРФПРИБЛ(B5:B9;A5:A9);2)

В результате в ячейках E3 и F3 Вы получите коэффициенты экспоненты.

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

Задайте в ячейке C5 формулу =$C$3*A5+$D$3 (=ax+b ) и скопируйте ее вниз (в ячей­ки С6:С9).

Задайте в ячейке D5 формулу =$F$3*$E$3^A5 () и скопируйте ее вниз (в ячейки D6:F9).

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

Поставьте курсор в пустую ячейку и нажмите кнопку точечная на вкладке Вставка. Выберите вид диаграммы: Точечная с гладкими кривыми и маркерами. Передвиньте появившийся прямоугольник так, чтобы он не закрывал данные в таблице.

Нажмите кнопку «Выбрать данные». Щелкните по кнопке со стрелочкой справа от поля «Диапазон данных для диаграммы». Выделите ячейки с данными для диаграммы (A5: D9) и опять щелкните по кнопке справа от поля с данными (окно закроется).

В оставшемся окне измените названия рядов данных как показано на рисунке и наж­мите OK.