4.1. Использование встроенных функций

Вычисление коэффициентов регрессии осуществляется с помощью функции

ЛИНЕЙН (Значения_y ; Значения_x ; Конст ; статистика ),

Значения_y - массив значений y,

Значения_x - необязательный массив значений x , если массив х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и Значения_y ,

Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения a подбираются так, чтобы выполнялось соотношение y=ax.

Статистика - логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент Статистика имеет значение ИСТИНА , то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент Статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициент a и постоянную b .

Необходимо помнить, что результатом функций ЛИНЕЙН() является множество значений – массив.

Для расчета коэффициента корреляции используется функция

КОРРЕЛ (Массив1 ;Массив2 ),

возвращающая значения коэффициента корреляции, где Массив1 - массив значений y , Массив2 - массив значений x . Массив1 и Массив2 должны быть одной размерности.

ПРИМЕР 1 . Зависимость y (x ) представлена в таблице. Построить линию регрессии и вычислить коэффициент корреляции .

Введем таблицу значений в лист MS Excel и построим точечный график. Рабочий лист примет вид изображенный на рис. 2.

Для того чтобы рассчитать значения коэффициентов регрессии а и b выделимячейки A7:B7, обратимся к мастеру функций и в категории Статистические выберем функцию ЛИНЕЙН . Заполним появившееся диалоговое окно так, как показано на рис. 3 и нажмем ОK .

В результате вычисленное значение появится только в ячейке A6 (рис.4). Для того чтобы значение появилось и в ячейке B6 необходимо войти в режим редактирования (клавиша F2) , а затем нажать комбинацию клавиш CTRL+SHIFT+ENTER .

Для расчета значения коэффициента корреляции в ячейку С6 была введена следующая формула:

С7=КОРРЕЛ(B3:J3;B2:J2) .

Зная коэффициенты регрессии а и b вычислим значения функции y =ax +b для заданных x . Для этого введем формулу

B5=$A$7*B2+$B$7

и скопируем ее в диапазон С5:J5 (рис. 5).

Изобразим линию регрессии на диаграмме. Выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и выберем команду Исходные данные . В появившемся диалоговом окне (рис. 5) выберем вкладку Ряд и щелкнем по кнопке Добавить . Заполним поля ввода, так как показано на рис. 6 и нажмем кнопку ОК . К графику экспериментальных данных будет добавлена линия регрессии. По умолчанию ее график будет изображен в виде точек, не соединенных сглаживающими линиями.

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

Тип линии, ее цвет и толщину можно изменить следующим образом. Выделить линию на диаграмме, нажать правую кнопку мыши и в контекстном меню выбрать команду Формат рядов данных… Далее сделать установки, например, так как показано на рис. 8.

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

4.2. Использование линии тренда.

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

ПРИМЕР 2 . В результате эксперимента была определена некоторая табличная зависимость.

Выбрать и построить аппроксимирующую зависимость. Построить графики табличной и подобранной аналитической зависимости.

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

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

Появившееся диалоговое окно позволяет построить аппроксимирующую зависимость.

На первой вкладке (рис. 11) этого окна указывается вид аппроксимирующей зависимости.

На второй (рис. 12) определяются параметры построения:

· название аппроксимирующей зависимости;

· прогноз вперед (назад) на n единиц (этот параметр определяет, на какое количество единиц вперед (назад) необходимо продлить линию тренда);

· показывать ли точку пересечения кривой с прямой y=const ;

· показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме);

· помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму величину достоверности аппроксимации).

Выберем в качестве аппроксимирующей зависимости полином второй степени (рис. 11) и выведем уравнение, описывающее этот полином на график (рис. 12). Полученная диаграмма представлена на рис. 13.

Аналогично с помощью линии тренда можно подобрать параметры таких зависимостей как

· линейная y =a∙x +b ,

· логарифмическая y =a∙ln (x )+b ,

· экспоненциальная y =a∙e b ,

· степенная y =a∙x b ,

· полиномиальная y =a∙x 2 +b∙x +c , y =a∙x 3 +b∙x 2 +c∙x+d и так далее, до полинома 6-й степени включительно,

· линейная фильтрация.

4.3. Использование инструмента анализа вариантов: Поиск решения.

Значительный интерес представляет реализация в MS Excel подбора параметров функциональной зависимости методом наименьших квадратов с использованием инструмента анализа вариантов: Поиск решения. Эта методика позволяет подобрать параметры функции любого вида. Рассмотрим эту возможность на примере следующей задачи.

ПРИМЕР 3 . В результате эксперимента получена зависимость z(t) представленная в таблице

Подобрать коэффициенты зависимости Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K методом наименьших квадратов.

Эта задача эквивалентна задаче нахождения минимума функции пяти переменных

Рассмотрим процесс решения задачи оптимизации (рис. 14).

Пусть значения А , В , С , D и К хранятся в ячейках A7:E7 . Рассчитаем теоретические значения функции Z (t )=At 4 +Bt 3 +Ct 2 +Dt+K для заданных t (B2:J2 ). Для этого в ячейку B4 введем значение функции в первой точке (ячейка B2 ):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7 .

Скопируем эту формулу в диапазон С4:J4 и получим ожидаемое значение функции в точках, абсциссы которых хранится в ячейках B2:J2 .

В ячейку B5 введем формулу, вычисляющую квадрат разности между экспериментальными и расчетными точками:

B5=(B4-B3)^2,

и скопируем ее в диапазон С5:J5 . В ячейке F7 будем хранить суммарную квадратичную ошибку (10). Для этого введем формулу:

F7 = СУММ(B5:J5) .

Воспользуемся командой Сервис®Поиск решения и решим задачу оптимизации без ограничений. Заполним соответствующим образом поля ввода в диалоговом окне, показанном на рис. 14 и нажмем кнопку Выполнить . Если решение будет найдено, то появится окно, изображенное на рис. 15.

Результатом работы решающего блока будет вывод в ячейки A7:E7 значений параметров функции Z (t )=At 4 +Bt 3 +Ct 2 +Dt+K . В ячейках B4:J4 получим ожидаемые значение функции в исходных точках. В ячейке F7 будет храниться суммарная квадратичная ошибка .

Изобразить экспериментальные точки и подобранную линию в одной графической области можно, если выделить диапазон B2:J4 , вызвать Мастер диаграмм , а затем отформатировать внешний вид полученных графиков.

Рис. 17 отображает рабочий лист MS Excel после проведенных вычислений.

Ну вот, на работе перед инспекцией отчитались, статья дома для конференции написана — можно теперь и в блог писать. Пока данные свои обрабатывал, понял, что не могу не написать про очень классную и нужную надстройку в Excel, которая называется . Так что статья будет посвящена именно этой надстройке, и расскажу я о ней на примере использования метода наименьших квадратов (МНК) для поиска неизвестных коэффициентов уравнения при описании экспериментальных данных.

Как включить надстройку «поиск решения»

Для начала разберемся, как эту надстройку включить.

1. Идем в меню «Файл» и выбираем пункт «Параметры Excel»

2. В появившемся окне выбираем «Поиск решения» и нажимаем «перейти».

3. В следующем окне ставим галочку напротив пункта «поиск решения» и нажимаем «ОК».

4. Надстройка активирована — теперь ее можно найти в пункте меню «Данные».

Метод наименьших квадратов

Теперь вкратце о методе наименьших квадратов (МНК) и о том, где его можно применять.

Допустим, у нас есть набор данных после совершения нами какого-то эксперимента, где мы изучали влияния величины Х на величину Y.

Мы хотим это влияние описать математически, чтобы потом этой формулой пользоваться и знать, что, если мы поменяем величину Х на столько-то, получим величину Y такую-то...

Возьму супер-простой пример (см. рис.).

Ежу понятно, что точки расположились друг за другом как будто по прямой, а потому мы смело предполагаем, что наша зависимость описывается линейной функцией y=kx+b. При этом мы точно уверены, что при X равном нулю значение Y тоже равно нулю. Значит, функция, описывающая зависимость, будет еще проще: y=kx (вспоминаем школьную программу).

В общем, нам предстоит найти коэффициент k. Вот это мы и сделаем с помощью МНК с применением надстройки «поиск решения».

Метод заключается в том, чтобы (здесь — внимание: нужно вдуматься) сумма квадратов разностей экспериментально полученных и соответствующих расчетных значений была минимальной. То есть когда X1=1 реально измеренное значение Y1=4,6, а расчетное y1=f (x1) равно 4, квадрат разности будет (y1-Y1)^2=(4-4,6)^2=0,36. Со следующими так же: когда X2=2, реально измеренное значение Y2=8,1, а расчетное у2 равно 8, квадрат разности будет (y2-Y2)^2=(8-8,1)^2=0,01. И сумма всех этих квадратов должна быть минимально возможной.

Итак, приступим к тренировке по использованию МНК и надстройки Excel «поиск решения» .

Применение надстройки поиск решения

1. Если не включили надстройку «поиск решения», то возвращаемся к пункту Как включить надстройку «поиск решения» и включаем 🙂

2. В ячейку А1 введем значение «1». Эта единица будет первым приближением к реальному значению коэффициента (k) нашей функциональной зависимости y=kx.

3. В столбце B у нас расположились значения параметра X, в столбце C — значения параметра Y. В ячейках столбца D вводим формулу: «коэффициент k умножить на значение Х». Например, в ячейке D1 вводим «=A1*B1», в ячейке D2 вводим "=A1*B2" и т.д.

4. Мы считаем, что коэффициент к равен единице и функция f (x)=у=1*х – это первое приближение к нашему решению. Можем рассчитать сумму квадратов разностей между измеренными значениями величины Y и рассчитанными по формуле y=1*х. Можем все это сделать вручную, вбивая в формулу соответствующие ссылки на ячейки: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... и т.д. В конце концов ошибаемся и понимаем, что потеряли кучу времени. В Excel для расчета суммы квадратов разностей есть специальная формула, «СУММКВРАЗН», которая все за нас и сделает. Введем ее в ячейку А2 и зададим исходные данные: диапазон измеренных значений Y (столбец C) и диапазон рассчитанных значений Y (столбец D).

4. Сумму разностей квадратов рассчитали – теперь идем во вкладку «Данные» и выбираем «Поиск решения».

5. В появившемся меню в качестве изменяемой ячейки выбираем ячейку A1 (та, что с коэффициентом k).

6. В качестве целевой выбираем ячейку A2 и задаем условие «установить равной минимальному значению». Помним, что это ячейка, где у нас производится расчёт суммы квадратов разностей расчетного и измеренного значений, и сумма эта должна быть минимальной. Нажимаем «выполнить».

7. Коэффициент k подобран. Теперь можно убедиться, что рассчитанные значения теперь очень близки к измеренным.

P.S.

Вообще, конечно, для аппроксимации экспериментальных данных в Excel существуют специальные инструменты, которые позволяют осуществлять описание данных с помощью линейной, экспоненциальной, степенной и полиномиальной функцией, поэтому часто можно обойтись и без надстройки «поиск решения» . Обо всех этих способах апппроксимации я рассказывал в своем , так что если интересно, — посмотрите. А вот когда дело касается какой-нибудь экзотической функции с одним неизвестным коэффициентом или задач оптимизации, то здесь надстройка как нельзя кстати.

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

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

1. выбор формы связи (вида аналитического уравнения регрессии);
2. оценку параметров уравнения;
3. оценку качества аналитического уравнения регрессии.

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

Задача оценивания параметров линейного парного уравнения методом наименьших квадратов состоит в следующем: получить такие оценки параметров , , при которых сумма квадратов отклонений фактических значений результативного признака - y i от расчетных значений – минимальна.
Формально критерий МНК можно записать так: .

Классификация методов наименьших квадратов

1. Метод наименьших квадратов.
2. Метод максимального правдоподобия (для нормальной классической линейной модели регрессии постулируется нормальность регрессионных остатков).
3. Обобщенный метод наименьших квадратов ОМНК применяется в случае автокорреляции ошибок и в случае гетероскедастичности.
4. Метод взвешенных наименьших квадратов (частный случай ОМНК с гетероскедастичными остатками).

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

Математическая запись данной задачи: .
Значения y i и x i =1...n нам известны, это данные наблюдений. В функции S они представляют собой константы. Переменными в данной функции являются искомые оценки параметров - , . Чтобы найти минимум функции 2-ух переменных необходимо вычислить частные производные данной функции по каждому из параметров и приравнять их нулю, т.е. .
В результате получим систему из 2-ух нормальных линейных уравнений:
Решая данную систему, найдем искомые оценки параметров:

Правильность расчета параметров уравнения регрессии может быть проверена сравнением сумм (возможно некоторое расхождение из-за округления расчетов).
Для расчета оценок параметров , можно построить таблицу 1.
Знак коэффициента регрессии b указывает направление связи (если b >0, связь прямая, если b <0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Формально значение параметра а – среднее значение y при х равном нулю. Если признак-фактор не имеет и не может иметь нулевого значения, то вышеуказанная трактовка параметра а не имеет смысла.

Оценка тесноты связи между признаками осуществляется с помощью коэффициента линейной парной корреляции - r x,y . Он может быть рассчитан по формуле: . Кроме того, коэффициент линейной парной корреляции может быть определен через коэффициент регрессии b: .
Область допустимых значений линейного коэффициента парной корреляции от –1 до +1. Знак коэффициента корреляции указывает направление связи. Если r x, y >0, то связь прямая; если r x, y <0, то связь обратная.
Если данный коэффициент по модулю близок к единице, то связь между признаками может быть интерпретирована как довольно тесная линейная. Если его модуль равен единице ê r x , y ê =1, то связь между признаками функциональная линейная. Если признаки х и y линейно независимы, то r x,y близок к 0.
Для расчета r x,y можно использовать также таблицу 1.

Для оценки качества полученного уравнения регрессии рассчитывают теоретический коэффициент детерминации – R 2 yx:

,
где d 2 – объясненная уравнением регрессии дисперсия y ;
e 2 - остаточная (необъясненная уравнением регрессии) дисперсия y ;
s 2 y - общая (полная) дисперсия y .
Коэффициент детерминации характеризует долю вариации (дисперсии) результативного признака y , объясняемую регрессией (а, следовательно, и фактором х), в общей вариации (дисперсии) y . Коэффициент детерминации R 2 yx принимает значения от 0 до 1. Соответственно величина 1-R 2 yx характеризует долю дисперсии y , вызванную влиянием прочих неучтенных в модели факторов и ошибками спецификации.
При парной линейной регрессии R 2 yx =r 2 yx .

4.1. Использование встроенных функций

Вычисление коэффициентов регрессии осуществляется с помощью функции

ЛИНЕЙН (Значения_y ; Значения_x ; Конст ; статистика ),

Значения_y - массив значений y,

Значения_x - необязательный массив значений x , если массив х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и Значения_y ,

Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения a подбираются так, чтобы выполнялось соотношение y=ax.

Статистика - логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент Статистика имеет значение ИСТИНА , то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент Статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициент a и постоянную b .

Необходимо помнить, что результатом функций ЛИНЕЙН() является множество значений – массив.

Для расчета коэффициента корреляции используется функция

КОРРЕЛ (Массив1 ;Массив2 ),

возвращающая значения коэффициента корреляции, где Массив1 - массив значений y , Массив2 - массив значений x . Массив1 и Массив2 должны быть одной размерности.

ПРИМЕР 1 . Зависимость y (x ) представлена в таблице. Построить линию регрессии и вычислить коэффициент корреляции .

Введем таблицу значений в лист MS Excel и построим точечный график. Рабочий лист примет вид изображенный на рис. 2.

Для того чтобы рассчитать значения коэффициентов регрессии а и b выделимячейки A7:B7, обратимся к мастеру функций и в категории Статистические выберем функцию ЛИНЕЙН . Заполним появившееся диалоговое окно так, как показано на рис. 3 и нажмем ОK .

В результате вычисленное значение появится только в ячейке A6 (рис.4). Для того чтобы значение появилось и в ячейке B6 необходимо войти в режим редактирования (клавиша F2) , а затем нажать комбинацию клавиш CTRL+SHIFT+ENTER .

Для расчета значения коэффициента корреляции в ячейку С6 была введена следующая формула:

С7=КОРРЕЛ(B3:J3;B2:J2) .

Зная коэффициенты регрессии а и b вычислим значения функции y =ax +b для заданных x . Для этого введем формулу

B5=$A$7*B2+$B$7

и скопируем ее в диапазон С5:J5 (рис. 5).

Изобразим линию регрессии на диаграмме. Выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и выберем команду Исходные данные . В появившемся диалоговом окне (рис. 5) выберем вкладку Ряд и щелкнем по кнопке Добавить . Заполним поля ввода, так как показано на рис. 6 и нажмем кнопку ОК . К графику экспериментальных данных будет добавлена линия регрессии. По умолчанию ее график будет изображен в виде точек, не соединенных сглаживающими линиями.

Рис. 6

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

Тип линии, ее цвет и толщину можно изменить следующим образом. Выделить линию на диаграмме, нажать правую кнопку мыши и в контекстном меню выбрать команду Формат рядов данных… Далее сделать установки, например, так как показано на рис. 8.

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

4.2. Использование линии тренда.

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

ПРИМЕР 2 . В результате эксперимента была определена некоторая табличная зависимость.

Выбрать и построить аппроксимирующую зависимость. Построить графики табличной и подобранной аналитической зависимости.

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

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

Появившееся диалоговое окно позволяет построить аппроксимирующую зависимость.

На первой вкладке (рис. 11) этого окна указывается вид аппроксимирующей зависимости.

На второй (рис. 12) определяются параметры построения:

· название аппроксимирующей зависимости;

· прогноз вперед (назад) на n единиц (этот параметр определяет, на какое количество единиц вперед (назад) необходимо продлить линию тренда);

· показывать ли точку пересечения кривой с прямой y=const ;

· показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме);

· помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму величину достоверности аппроксимации).

Выберем в качестве аппроксимирующей зависимости полином второй степени (рис. 11) и выведем уравнение, описывающее этот полином на график (рис. 12). Полученная диаграмма представлена на рис. 13.

Аналогично с помощью линии тренда можно подобрать параметры таких зависимостей как

· линейная y =a∙x +b ,

· логарифмическая y =a∙ln (x )+b ,

· экспоненциальная y =a∙e b ,

· степенная y =a∙x b ,

· полиномиальная y =a∙x 2 +b∙x +c , y =a∙x 3 +b∙x 2 +c∙x+d и так далее, до полинома 6-й степени включительно,

· линейная фильтрация.

4.3. Использование решающего блока

Значительный интерес представляет реализация в MS Excel подбора параметров методом наименьших квадратов с использованием решающего блока. Эта методика позволяет подобрать параметры функции любого вида. Рассмотрим эту возможность на примере следующей задачи.

ПРИМЕР 3 . В результате эксперимента получена зависимость z(t) представленная в таблице

Подобрать коэффициенты зависимости Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K методом наименьших квадратов.

Эта задача эквивалентна задаче нахождения минимума функции пяти переменных

Рассмотрим процесс решения задачи оптимизации (рис. 14).

Пусть значения А , В , С , D и К хранятся в ячейках A7:E7 . Рассчитаем теоретические значения функции Z (t )=At 4 +Bt 3 +Ct 2 +Dt+K для заданных t (B2:J2 ). Для этого в ячейку B4 введем значение функции в первой точке (ячейка B2 ):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7 .

Скопируем эту формулу в диапазон С4:J4 и получим ожидаемое значение функции в точках, абсциссы которых хранится в ячейках B2:J2 .

В ячейку B5 введем формулу, вычисляющую квадрат разности между экспериментальными и расчетными точками:

B5=(B4-B3)^2,

и скопируем ее в диапазон С5:J5 . В ячейке F7 будем хранить суммарную квадратичную ошибку (10). Для этого введем формулу:

F7 = СУММ(B5:J5) .

Воспользуемся командой Сервис®Поиск решения и решим задачу оптимизации без ограничений. Заполним соответствующим образом поля ввода в диалоговом окне, показанном на рис. 14 и нажмем кнопку Выполнить . Если решение будет найдено, то появится окно, изображенное на рис. 15.

Результатом работы решающего блока будет вывод в ячейки A7:E7 значений параметров функции Z (t )=At 4 +Bt 3 +Ct 2 +Dt+K . В ячейках B4:J4 получим ожидаемые значение функции в исходных точках. В ячейке F7 будет храниться суммарная квадратичная ошибка .

Изобразить экспериментальные точки и подобранную линию в одной графической области можно, если выделить диапазон B2:J4 , вызвать Мастер диаграмм , а затем отформатировать внешний вид полученных графиков.

Рис. 17 отображает рабочий лист MS Excel после проведенных вычислений.

5. СПИСОК ЛИТЕРАТУРЫ

1. Алексеев Е.Р., Чеснокова О.В., Решение задач вычислительной математики в пакетах Mathcad12, MATLAB7, Maple9. – НТ Пресс, 2006.–596с. :ил. –(Самоучитель)

2. Алексеев Е.Р., Чеснокова О.В., Е.А. Рудченко, Scilab, решение инженерных и математических задач. –М., БИНОМ, 2008.–260с.

3. Березин И.С., Жидков Н.П., Методы вычислений.–М.:Наука, 1966.–632с.

4. Гарнаев А.Ю., Использование MS EXCEL и VBA в экономике и финансах. – СПб.: БХВ - Петербург, 1999.–332с.

5. Демидович Б.П., Марон И А., Шувалова В.З., Численные методы анализа.–М.:Наука, 1967.–368с.

6. Корн Г., Корн Т., Справочник по математике для научных работников и инженеров.–М., 1970, 720с.

7. Алексеев Е.Р., Чеснокова О.В. Методические указания к выполнению лабораторных работ в MS EXCEL. Для студентов всех специальностей. Донецк, ДонНТУ, 2004. 112 с.