Подбор параметра

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

1. Установить указатель на ячейке с формулой.

2. Выбрать пункт /Сервис/Подбор параметра.

3. Задать значение, которое нужно получить по формуле и адрес ячейки, меняя, которую нужно подобрать указанное значение, затем <ОК>.

Поиск решения

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

1. Устанавливаем указатель на ячейку с формулой;

2. Выбираем, что нужно достигнуть при подборе параметров ;

3. Задаем ячейки, в которых находятся величины ;

4. Задаем ограничения на параметры и <ОК>.

Анализ данных

Операции анализа данных сосредоточены в пункте меню /Сервис/Анализ данных. Этот пункт содержит подпункты: /Экспоненциальное сглаживание; /Анализ Фурье; /Гистограмма; /Скользящее среднее; /Корреляция; /Генерация случайных чисел. Поясним некоторые из них.

Экспоненциальное сглаживание

Предположим, у нас имеются экспериментальные данные, заданные в виде таблицы y(x_i), которые получены при влиянии помехи или ошибки измерения (см. рисунок). Сгладить данные такого типа можно путем использования выражения , где - сглаженное значение в i-ой точке, - сглаженное значение в точке i-1, - коэффициент сглаживания.

Скользящее среднее

Э тот алгоритм также используется для сглаживания экспериментальных данных. Сглаженное значение в i-ой точке рассчитывается как среднее значение функции из диапазона от до по формуле . Величина n задает количество точек усреднения.

Корреляция - вычисление степени зависимости двух случайных величин

Коэффициент корреляции вычисляется по формуле:

Фурье анализ

Пусть функция f(x) на отрезке кусочно-непрерывна и имеет кусочно–непрерывные производные до -го порядка включительно. В этом случае во всех точках непрерывности можно представить тригонометрическим рядом Фурье:

где и - коэффициенты ряда Фурье, определяемые по формулам

Использование электронной таблицы Excel для определения аппроксимирующих функций по методу наименьших квадратов.

Общий алгоритм

Если набор экспериментальных данных получен со значительной погрешностью, то не имеет смысла использовать интерполяцию Лагранжа полиномами и сплайнами для обработки результатов. В этом случае необходимо провести аппроксимирующую кривую, которая не проходит через экспериментальные точки, но в то же время отражает исследуемую зависимость, сглаживает возможные выбросы за счет погрешности эксперимента.

Обозначим узлы исходной таблицы данных через , где - номер узла. Считаем известными значения экспериментальных данных в узловых точках . Введем непрерывную функцию для аппроксимации дискретной зависимости . В узлах функции и будут отличаться на величину . Отклонения могут принимать положительные и отрицательные значения. Чтобы не учитывать знаки, возведем каждое отклонение в квадрат и просуммируем квадраты отклонений по всем узлам .

(1)

Метод построения аппроксимирующей функции из условия минимума величины называется методом наименьших квадратов (МНК).

Наиболее распространен способ выбора функции в виде линейной комбинации.

(2)

где

-базисные функции;

- коэффициенты, определяемые при минимизации величины .

Математически условия минимума суммы квадратов отклонений запишем, приравнивая нулю частные производные от по коэффициентам :

(3)

Из системы линейных алгебраических уравнений (3) определяются все коэффициенты . Система (3) называется системой нормальных уравнений. Матрица этой системы имеет следующий вид:

(4)

и называется матрицей Грама. Элементы матрицы Грама являются скалярными произведениями базисных функций.

(5)

Расширенная матрица системы уравнений (3) получится добавлением справа к матрице Грама столбца свободных членов

(6)

где скалярные произведения, являющиеся элементами столбца, определяются аналогично (6)

(7)

Отметим основные свойства матрицы Грама, полезные при программной реализации алгоритмов МНК:

1. Матрица симметрична, т.е. ,что позволяет сократить объем вычислений при заполнении матрицы;

2. Матрица является положительно определенной, следовательно, при решении системы нормальных уравнений методом исключения Гаусса можно отказаться от процедуры выбора главного элемента;

3. Определитель матрицы будет отличен от нуля, если в качестве базиса выбраны линейно независимые функции , при этом система (3) имеет единственное решение.

При обработке экспериментальных данных, определенных с погрешностью в каждой узловой точке, обычно начинают с аппроксимации функцией , представимой одной-двумя базисными функциями. После определения коэффициентов вычисляют величину по формуле (1). Если получится, что , то необходимо расширить базис добавлением новых функций . Расширение базиса необходимо осуществлять до тех пор, пока не выполнится условие .

Выбор конкретных базисных функций зависит от свойств аппроксимируемой функции , таких, как периодичность, экспоненциальный или логарифмический характер, свойства симметрии, наличие асимптотики и т.д.