Аналогичные формулы заносятся (копируются) во все нижележащие ячейки (область С9:С17). В ячейке С18 вычисляется сумма погрешностей для всех точек С18=СУММ(С8:С17).
Нашей целью является приведение этой погрешности к минимуму путем изменения значений коэффициентов уравнения прямой (ячеек ВЗ и СЗ). В исходном состоянии они пустые. Для поиска оптимальных значений в окне Поиск решенияв качестве целевой ячейки следует установить ячейку С18, а в качестве изменяемых параметров - область ВЗ:СЗ. Результаты, полученные в изменяемых ячейках, соответствуют уравнению вида
Общая погрешность приближения (ячейка С18) составила 30,69. Аналогичным образом заполняется столбец D8:D18 погрешностей для полинома второй степени (параболы). Здесь:
D8=($B8-($B$4+$C$4*$A8+$D$4*$A8^2))^2.
В окне Поиск решенияцелевая ячейка - D18, изменяемые параметры - область B4:D4. Полученный результат соответствует уравнению
у = 4,05-0,4886х +0,1023x2.
Аналогично для уравнения третьей степени (гиперболы)
В окне Поиск решенияцелевая ячейка - Е18, изменяемые параметры - область В4:Е5. Результат описывается уравнением
у = - 2,0333 + 4,907х - 1,0676x2 + 0,0709x3.
Точно так же может быть сформировано уравнение четвертой степени
у = -10,083 + 15,227х-4,844x2+ 0,5869x3-0,0235x4. С повышением порядка уравнения регрессии погрешность приближения все время уменьшается
30,6909 => 25,1682 => 9,6408 = 0,5775.
Графическое отображение результатов вычислений приведено на рисунке (исходные точки обозначены прямоугольниками). Оно также подтверждает этот вывод - линии уравнений более высокой степени находятся ближе к исследуемым точкам.
Увеличение степени аппроксимирующего полинома снижает погрешность. Самая высокая возможная степень такого уравнения на единицу меньше числа точек (в рассмотренном примере теоретически возможен полином девятой степени). При этом аппроксимирующая кривая пройдет в точности через все наши точки. На практике, однако, нет необходимости стремиться к полному устранению погрешности, поскольку и сами данные никогда не являются точными. Наоборот, нужно стараться, конечно, без потери качества, ограничиться как можно меньшей степенью кривой. Применение описанной методики позволяет в качестве уравнения регрессии использовать не только степенные полиномы, но и другие функции.
Отметим, что средства деловой графики позволяют найти уравнения регрессии (до 6 степени включительно) и не прибегая к вычислениям. Если, после того как была построена кривая функции Y(X), щелкнуть на ней правой кнопкой мыши, в появившемся контекстном меню можно выбрать пункт Добавить линию тренда,который предъявляет окно Линия тренда.Здесь можно выбрать вид уравнения аппроксимации и его степень, а если во вкладке Параметрыустановить флаг Показывать уравнение на диаграмме,то на графике мы увидим не только линию тренда, но и его уравнение. Здесь же можно визуально оценить поведение анализируемого процесса в будущем/прошлом, если установить Прогноз вперед/назад назаданное число единиц независимого аргумента X. К сожалению, предъявляемая функция отображается кактекст, и не может быть непосредственно использована в вычислениях.
Если требуется периодическое выполнение задачи в Microsoft Excel, можно автоматизировать задачу с помощью макроса. Макрос – последовательность команд и функций, записанных в модуле VBA. Ее можно выполнять всякий раз, когда необходимо выполнить данную задачу.
Например, если в ячейки часто вводятся длинные текстовые строки, можно создать макрос для форматирования этих ячеек для охватывания ими текста.
При записи макроса Microsoft Excel сохраняет информацию о каждом шаге выполнения последовательности команд. Последующий запуск макроса вызывает повторение («воспроизведение») команд. Если при записи макроса была допущена ошибка, сделанные исправления также будут записаны. Visual Basic хранит каждый записанный макрос в отдельном модуле, присоединенном к книге.
В Microsoft Office 2000 включена последняя версия языка Visual Basic for Applications (VBA). Кроме VBA в Excel 2000 существует возможность создавать собственные макросы и таким образом автоматизировать выполнение основных операций. Наиболее простым способом создания макрокоманды является ее запись с помощью макрорекордера.
