Интерполирование функций средствами Excel.

В Microsoft Excel имеются различные способы решения задачи интерполирования. Некоторые из них рассмотрим на следующем примере. Пусть, в результате проведения эксперимента получены 10 пар значений независимой переменной и зависимой переменной , которые представлены в следующей таблице:

Использование надстройкиПоиск решения. Применение надстройки Поиск решения для аппроксимации экспериментальных данных полиномами различной степени начнем с самого простого – поиска параметров и уравнения линейной регрессии:

Для этого необходимо:

1. Заполнить исходную таблицу следующим образом:

1. Зарезервировать ячейки для параметров уравнения регрессии, например, B2:C2.

2. Занести исходные данные, например, в диапазон A10:B19.

3. В ячейку, например, C10, занести уравнение линейной регрессии =$B$2+$C$2*$A10, и скопировать его в диапазон ячеек C11:C19.

4. В ячейку, например, C20, занести функцию =СУММКВРАЗН($B10:$B19;C10:C19), которая возвращает сумму квадратов разностей между наблюдаемыми значениями зависимой переменной и вычисленными по уравнению регрессии при одних и тех же значениях независимой переменной .

2. Активизировать окно диалога Поиск решения, в котором:

1. В поле редактирования Установить целевую ячейку указать адрес ячейки, которая содержит функцию цели, – $C$20.

2. Переключатель Равной установить в положение минимальному значению.

3. В поле редактирования Изменяя ячейки занести ссылки на ячейки, содержащие параметры уравнения регрессии, – $B$2:$C$2.

4. Поскольку метод наименьших квадратов представляет задачу минимизации без ограничений, поле Ограничения не заполняется.

5. Нажать кнопку Выполнить.

В результате поиска решения в изменяемых ячейках B2:C2 будут получены значения параметров, соответствующие следующему уравнению регрессии:

При этом общая погрешность аппроксимации (ячейка C20) составит 30.69090909.

Аналогичным образом можно определить параметры уравнений регрессии для полиномов второй, третьей и т.д. степеней. При этом необходимо:

1. зарезервировать больше ячеек под параметры уравнений регрессии, например, B3:I8, а

2. в ячейки с уравнениями регрессии (со 2-й по 7-ю степени), например, D20:I20, записать следующие формулы:

=$B$3+$C$3*$A10+$D$3*$A10^2

=$B$4+$C$4*$A10+$D$4*$A10^2+$E$4*$A10^3

=$B$5+$C$5*$A10+$D$5*$A10^2+$E$5*$A10^3+$F$5*$A10^4

=$B$6+$C$6*$A10+$D$6*$A10^2+$E$6*$A10^3+$F$6*$A10^4+$G$6*$A10^5

=$B$7+$C$7*$A10+$D$7*$A10^2+$E$7*$A10^3+$F$7*$A10^4+$G$7*$A10^5+
+$H$7*$A10^6

=$B$8+$C$8*$A10+$D$8*$A10^2+$E$8*$A10^3+$F$8*$A10^4+$G$8*$A10^5+
+$H$8*$A10^6+$I$8*$A10^7

Тогда, сформированная и заполненная, в соответствии с изложенным выше, таблица будет выглядеть следующим образом.

В ней строчки 1-я и 9-я выделены под заголовки столбцов. Под параметры уравнений регрессии зарезервирован блок ячеек B2:I8. Значения независимой переменной занесены в диапазон ячеек A10:A19, а зависимой – в B10:B19. В диапазоне ячеек C10:I19 записаны уравнения регрессии (полиномы с 1-й по 7-ю степени) для различных значений независимой переменной , а в C20:I20 – функции цели СУММКВРАЗН() – суммы квадратов разности между наблюдаемым значением зависимой переменной и вычисленным, согласно уравнениям регрессии, при одних и тех же значениях независимой переменной . В диапазоне ячеек C23:I25 сохранены модели поиска решения для полиномов различной степени, чтобы, затем, каждой из них легко можно было воспользоваться.

Анализ содержимого ячеек функций цели (диапазон C20:I20) показывает следующую закономерность – с повышение порядка уравнения полиномиальной регрессии погрешность аппроксимации все время уменьшается. График, приведенный далее, так же подтверждает этот вывод – линия уравнения регрессии более высокого порядка проходит более близко к исходным значениям.

На нем точками изображены данные, полученные в результате наблюдения (или эксперимента). Уравнение линейной регрессии изображено пунктирной линией, а полиномом 4-й степени – сплошной.

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

Теоретически самая высокая возможная степень аппроксимирующего полинома на единицу меньше числа наблюдаемых точек данных. В рассматриваемом примере – это полином степени не более 9‑й. На практике, однако, нет необходимости стремиться к полному устранению погрешности, поскольку сами исходные данные никогда не являются точными. Наоборот, необходимо стремиться, конечно, без потери качества, ограничиться полиномом как можно меньшей степени. Применение описанной выше методики позволяет в качестве уравнения регрессии использовать не только степенные полиномы, но и другие функции.

Использование диаграмм. Средства деловой графики Microsoft Excel позволяют найти уравнения регрессии, не прибегая к вычислениям. Линия уравнения регрессии в Microsoft Excel называется линией тренда и показывает тенденцию изменения данных, а также может служить для составления прогнозов. Для построения линии тренда необходимо:

1. На основании экспериментальных данных построить диаграмму. При этом наиболее наглядной является Точечная диаграмма.

2. Выделить диаграмму, а в ней – необходимый ряд данных.

3. Активизировать окно диалога Линия тренда по команде Добавить линию тренда из контекстного, или основного меню Диаграмма.

4. В появившемся окне диалога Линия тренда на вкладке Тип выбрать один из 6-ти типов линии тренда, и указать его параметры (если необходимо).

5. На вкладке Параметры окна диалога Линия тренда указать:

r С помощью группового переключателя Название аппроксимирующей (сглаженной) кривой, и соответствующего поля редактирования, – название тренда, которое будет отображаться в легенде.

r С помощью счетчиков вперед на и назад на группы Прогноз – на сколько единиц продлить линию тренда относительно исходного диапазона изменения независимой переменной.

r С помощью флажка пересечение кривой с осью Y в точке – точку пересечения линии тренда с осью Y – только если она известна.

r С помощью флажка показывать уравнение на диаграмме – отображать ли на диаграмме уравнение линии тренда.

r С помощью флажка поместить на диаграмму величину достоверности аппроксимации (R^2) – отображать ли на диаграмме величину достоверности аппроксимации. Чем она ближе к 1, тем точнее уравнение регрессии описывает зависимость между наблюдаемыми величинами. Если же она лежит близко к –1, то это говорит об обратной зависимости между ними.

6. Нажать кнопку OK.

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

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

Для одного ряда данных можно последовательно построить (не убирая при этом предыдущие) различные линии трендов, с различными параметрами. При этом с помощью такой диаграммы достаточно легко произвести «грубый отсев» неподходящих уравнений регрессии. Остальные же, затем, можно будет подвергнуть более тонкому анализу. Построенные таким образом линии трендов связываются с соответствующими рядами данных, и поэтому при изменении значения любой точки ряда данных линии трендов автоматически пересчитываются и обновляются на диаграмме.

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

Инструмент Регрессия надстройки Пакет анализа данных позволяет выполнить достаточно детальный анализ параметров линейной регрессии. Его возможности рассмотрим на исходных данных приведенного ранее примера. Для этого необходимо выполнить следующую последовательность действий.

1. По команде Анализ данных из основного меню Сервис активизировать окно диалога Анализ данных, в котором в списке Инструменты анализа выбрать элемент Регрессия и нажать кнопку OK.

2. В раскрывшемся окне диалога Регрессия с помощью его элементов управления задать параметры выполнения линейного регрессионного анализа, которые имеют следующие назначения:

Входной интервал Y – диапазон анализируемых зависимых данных. Он должен состоять из одного столбца. В случае нашего примера – это B10:B19.

Входной интервал X – диапазон независимых данных, подлежащих анализу. Microsoft Excel располагает независимые переменные этого диапазона слева направо в порядке возрастания. Максимальное число входных диапазонов равно 16. В нашем случае это A10:A19.

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

Уровень надежности – установить флажок, чтобы включить в выходной диапазон дополнительный уровень надежности. В соответствующее поле ввести уровень надежности, который будет использован дополнительно к уровню 95%, применяемому по умолчанию.

Константа - ноль – установить флажок, чтобы линия регрессии прошла через начало координат.

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

r результаты дисперсионного анализа,

r коэффициенты регрессии,

r стандартную погрешность вычисления Y,

r среднеквадратичные отклонения,

r число наблюдений,

r стандартные погрешности для коэффициентов.

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

Новая рабочая книга – установить переключатель, чтобы открыть новую рабочую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге.

Остатки – установить флажок, чтобы включить остатки в выходной диапазон.

Стандартизированные остатки – установить флажок, чтобы включить стандартизированные остатки в выходной диапазон.

График остатков – установить флажок, чтобы построить диаграмму остатков для каждой независимой переменной.

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

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

3. Нажать кнопку OK.

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