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 ) представлена в таблице. Построить линию регрессии и вычислить коэффициент корреляции .

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Введем таблицу значений в лист 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 . В результате эксперимента была определена некоторая табличная зависимость.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

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

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

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

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Подобрать коэффициенты зависимости 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 с.

Которое находит самое широкое применение в различных областях науки и практической деятельности. Это может быть физика, химия, биология, экономика, социология, психология и так далее, так далее. Волею судьбы мне часто приходится иметь дело с экономикой, и поэтому сегодня я оформлю вам путёвку в удивительную страну под названием Эконометрика =) …Как это не хотите?! Там очень хорошо – нужно только решиться! …Но вот то, что вы, наверное, определённо хотите – так это научиться решать задачи методом наименьших квадратов . И особо прилежные читатели научатся решать их не только безошибочно, но ещё и ОЧЕНЬ БЫСТРО;-) Но сначала общая постановка задачи + сопутствующий пример:

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

– торговую площадь продовольственного магазина, кв.м.,
– годовой товарооборот продовольственного магазина, млн. руб.

Совершенно понятно, что чем больше площадь магазина, тем в большинстве случаев будет больше его товарооборот.

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

С гастрономами, думаю, всё понятно: – это площадь 1-го магазина, – его годовой товарооборот, – площадь 2-го магазина, – его годовой товарооборот и т.д. Кстати, совсем не обязательно иметь доступ к секретным материалам – довольно точную оценку товарооборота можно получить средствами математической статистики . Впрочем, не отвлекаемся, курс коммерческого шпионажа – он уже платный =)

Табличные данные также можно записать в виде точек и изобразить в привычной для нас декартовой системе .

Ответим на важный вопрос: сколько точек нужно для качественного исследования?

Чем больше, тем лучше. Минимально допустимый набор состоит из 5-6 точек. Кроме того, при небольшом количестве данных в выборку нельзя включать «аномальные» результаты. Так, например, небольшой элитный магазин может выручать на порядки больше «своих коллег», искажая тем самым общую закономерность, которую и требуется найти!

Если совсем просто – нам нужно подобрать функцию , график которой проходит как можно ближе к точкам . Такую функцию называют аппроксимирующей (аппроксимация – приближение) или теоретической функцией . Вообще говоря, тут сразу появляется очевидный «претендент» – многочлен высокой степени, график которого проходит через ВСЕ точки. Но этот вариант сложен, а зачастую и просто некорректен (т.к. график будет всё время «петлять» и плохо отражать главную тенденцию) .

Таким образом, разыскиваемая функция должна быть достаточно простА и в то же время отражать зависимость адекватно. Как вы догадываетесь, один из методов нахождения таких функций и называется методом наименьших квадратов . Сначала разберём его суть в общем виде. Пусть некоторая функция приближает экспериментальные данные :


Как оценить точность данного приближения? Вычислим и разности (отклонения) между экспериментальными и функциональными значениями (изучаем чертёж) . Первая мысль, которая приходит в голову – это оценить, насколько великА сумма , но проблема состоит в том, что разности могут быть и отрицательны (например, ) и отклонения в результате такого суммирования будут взаимоуничтожаться. Поэтому в качестве оценки точности приближения напрашивается принять сумму модулей отклонений:

или в свёрнутом виде: (вдруг кто не знает: – это значок суммы, а – вспомогательная переменная-«счётчик», которая принимает значения от 1 до ) .

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

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

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

И сейчас мы возвращаемся к другому важному моменту: как отмечалось выше, подбираемая функция должна быть достаточно простА – но ведь и таких функций тоже немало: линейная , гиперболическая , экспоненциальная , логарифмическая , квадратичная и т.д. И, конечно же, тут сразу бы хотелось «сократить поле деятельности». Какой класс функций выбрать для исследования? Примитивный, но эффективный приём:

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

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

А теперь обратите внимание, что в обоих случаях речь идёт о функции двух переменных , аргументами которой являются параметры разыскиваемых зависимостей :

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

Вспомним про наш пример: предположим, что «магазинные» точки имеют тенденцию располагаться по прямой линии и есть все основания полагать наличие линейной зависимости товарооборота от торговой площади. Найдём ТАКИЕ коэффициенты «а» и «бэ», чтобы сумма квадратов отклонений была наименьшей. Всё как обычно – сначала частные производные 1-го порядка . Согласно правилу линейности дифференцировать можно прямо под значком суммы:

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

Составим стандартную систему:

Сокращаем каждое уравнение на «двойку» и, кроме того, «разваливаем» суммы:

Примечание : самостоятельно проанализируйте, почему «а» и «бэ» можно вынести за значок суммы. Кстати, формально это можно проделать и с суммой

Перепишем систему в «прикладном» виде:

после чего начинает прорисовываться алгоритм решения нашей задачи:

Координаты точек мы знаем? Знаем. Суммы найти можем? Легко. Составляем простейшую систему двух линейных уравнений с двумя неизвестными («а» и «бэ»). Систему решаем, например, методом Крамера , в результате чего получаем стационарную точку . Проверяя достаточное условие экстремума , можно убедиться, что в данной точке функция достигает именно минимума . Проверка сопряжена с дополнительными выкладками и поэтому оставим её за кадром (при необходимости недостающий кадр можно посмотреть ) . Делаем окончательный вывод:

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

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

Я разберу всего лишь одну задачу с «реальными» числами, поскольку никаких трудностей в ней нет – все вычисления на уровне школьной программы 7-8 класса. В 95 процентов случаев вам будет предложено отыскать как раз линейную функцию, но в самом конце статьи я покажу, что ничуть не сложнее отыскать уравнения оптимальной гиперболы, экспоненты и некоторых других функций.

По сути, осталось раздать обещанные плюшки – чтобы вы научились решать такие примеры не только безошибочно, но ещё и быстро. Внимательно изучаем стандарт:

Задача

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

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

Заметьте, что «иксовые» значения – натуральные, и это имеет характерный содержательный смысл, о котором я расскажу чуть позже; но они, разумеется, могут быть и дробными. Кроме того, в зависимости от содержания той или иной задачи как «иксовые», так и «игрековые» значения полностью или частично могут быть отрицательными. Ну а у нас дана «безликая» задача, и мы начинаем её решение :

Коэффициенты оптимальной функции найдём как решение системы:

В целях более компактной записи переменную-«счётчик» можно опустить, поскольку и так понятно, что суммирование осуществляется от 1 до .

Расчёт нужных сумм удобнее оформить в табличном виде:


Вычисления можно провести на микрокалькуляторе, но гораздо лучше использовать Эксель – и быстрее, и без ошибок; смотрим короткий видеоролик:

Таким образом, получаем следующую систему :

Тут можно умножить второе уравнение на 3 и из 1-го уравнения почленно вычесть 2-е . Но это везение – на практике системы чаще не подарочны, и в таких случаях спасает метод Крамера :
, значит, система имеет единственное решение.

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

Получены правые части соответствующих уравнений, значит, система решена правильно.

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

В отличие от прямой зависимости товарооборота магазина от его площади, найденная зависимость является обратной (принцип «чем больше – тем меньше») , и этот факт сразу выявляется по отрицательному угловому коэффициенту . Функция сообщает нам о том, что с увеличение некоего показателя на 1 единицу значение зависимого показателя уменьшается в среднем на 0,65 единиц. Как говорится, чем выше цена на гречку, тем меньше её продано.

Для построения графика аппроксимирующей функции найдём два её значения:

и выполним чертёж:


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

Вычислим сумму квадратов отклонений между эмпирическими и теоретическими значениями. Геометрически – это сумма квадратов длин «малиновых» отрезков (два из которых настолько малы, что их даже не видно) .

Вычисления сведём в таблицу:


Их можно опять же провести вручную, на всякий случай приведу пример для 1-й точки:

но намного эффективнее поступить уже известным образом:

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

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


И снова на всякий пожарный вычисления для 1-й точки:

В Экселе пользуемся стандартной функцией EXP (синтаксис можно посмотреть в экселевской Справке) .

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

Но тут следует отметить, что «хуже» – это ещё не значит , что плохо. Сейчас построил график этой экспоненциальной функции – и он тоже проходит близко к точкам – да так, что без аналитического исследования и сказать трудно, какая функция точнее.

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

Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью линейной функции y = a x + b .

Метод наименьших квадратов (англ. Ordinary Least Squares , OLS ) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным.

Рассмотрим приближение функциями, зависящими только от одной переменной:

• Линейная: y=ax+b (эта статья)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Примечание : Случаи приближения полиномом с 3-й до 6-й степени рассмотрены в этой статье. Приближение тригонометрическим полиномом рассмотрено здесь.

Линейная зависимость

Нас интересует связь 2-х переменных х и y . Имеется предположение, что y зависит от х по линейному закону y = ax + b . Чтобы определить параметры этой взаимосвязи исследователь провел наблюдения: для каждого значения х i произведено измерение y i (см. файл примера ). Соответственно, пусть имеется 20 пар значений (х i ; y i).

Примечание: Если шаг изменения по х постоянен, то для построения диаграммы рассеяния можно использовать , если нет, то необходимо использовать тип диаграммы Точечная .

Из диаграммы очевидно, что связь между переменными близка к линейной. Чтобы понять какая из множества прямых линий наиболее «правильно» описывает зависимость между переменными, необходимо определить критерий, по которому будут сравниваться линии.

В качестве такого критерия используем выражение:

где ŷ i = a * x i + b ; n – число пар значений (в нашем случае n=20)

Вышеуказанное выражение представляет собой сумму квадратов расстояний между наблюденными значениями y i и ŷ i и часто обозначается как SSE ( Sum of Squared Errors ( Residuals ), сумма квадратов ошибок (остатков) ) .

Метод наименьших квадратов заключается в подборе такой линии ŷ = ax + b , для которой вышеуказанное выражение принимает минимальное значение.

Примечание: Любая линия в двухмерном пространстве однозначно определяется значениями 2-х параметров: a (наклон) и b (сдвиг).

Считается, что чем меньше сумма квадратов расстояний, тем соответствующая линия лучше аппроксимирует имеющиеся данные и может быть в дальнейшем использована для прогнозирования значений y от переменной х. Понятно, что даже если в действительности никакой взаимосвязи между переменными нет или связь нелинейная, то МНК все равно подберет «наилучшую» линию. Таким образом, МНК ничего не говорит о наличии реальной взаимосвязи переменных, метод просто позволяет подобрать такие параметры функции a и b , для которых вышеуказанное выражение минимально.

Проделав не очень сложные математические операции (подробнее см. ), можно вычислить параметры a и b :

Как видно из формулы, параметр a представляет собой отношение ковариации и , поэтому в MS EXCEL для вычисления параметра а можно использовать следующие формулы (см. файл примера лист Линейная ):

= КОВАР(B26:B45;C26:C45)/ ДИСП.Г(B26:B45) или

= КОВАРИАЦИЯ.В(B26:B45;C26:C45)/ДИСП.В(B26:B45)

Также для вычисления параметра а можно использовать формулу = НАКЛОН(C26:C45;B26:B45) . Для параметра b используйте формулу = ОТРЕЗОК(C26:C45;B26:B45) .

И наконец, функция ЛИНЕЙН() позволяет вычислить сразу оба параметра. Для ввода формулы ЛИНЕЙН(C26:C45;B26:B45) необходимо выделить в строке 2 ячейки и нажать CTRL + SHIFT + ENTER (см. статью про ). В левой ячейке будет возвращено значение а , в правой – b .

Примечание : Чтобы не связываться с вводом формул массива потребуется дополнительно использовать функцию ИНДЕКС() . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);1) или просто = ЛИНЕЙН(C26:C45;B26:B45) вернет параметр, отвечающий за наклон линии, т.е. а . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);2) вернет параметр, отвечающий за пересечение линии с осью Y, т.е. b .

Вычислив параметры, на диаграмме рассеяния можно построить соответствующую линию.

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

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

Примечание : Для того, чтобы параметры совпадали необходимо, чтобы тип у диаграммы был . Дело в том, что при построении диаграммы График значения по оси Х не могут быть заданы пользователем (пользователь может указать только подписи, которые не влияют на расположение точек). Вместо значений Х используется последовательность 1; 2; 3; … (для нумерации категорий). Поэтому, если строить линию тренда на диаграмме типа График , то вместо фактических значений Х будут использованы значения этой последовательности, что приведет к неверному результату (если, конечно, фактические значения Х не совпадают с последовательностью 1; 2; 3; …).

Ну вот, на работе перед инспекцией отчитались, статья дома для конференции написана — можно теперь и в блог писать. Пока данные свои обрабатывал, понял, что не могу не написать про очень классную и нужную надстройку в 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 существуют специальные инструменты, которые позволяют осуществлять описание данных с помощью линейной, экспоненциальной, степенной и полиномиальной функцией, поэтому часто можно обойтись и без надстройки «поиск решения» . Обо всех этих способах апппроксимации я рассказывал в своем , так что если интересно, — посмотрите. А вот когда дело касается какой-нибудь экзотической функции с одним неизвестным коэффициентом или задач оптимизации, то здесь надстройка как нельзя кстати.

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

Метод наименьших квадратов (МНК)

Система m линейных уравнений с n неизвестными имеет вид:

Возможны три случая: mn. Случай, когда m=n, рассматривался в предыдущих параграфах. При m

В случае, если m>nи система является совместной, то матрица А имеет по крайней мере m - nлинейно зависимых строк. Здесь решение может быть получено отбором n любых линейно независимых уравнений (если они существуют)и применением формулы Х=А -1 ЧВ, то есть, сведением задачи к ранее решенной. При этом полученное решение всегда будет удовлетворять и остальным m - nуравнениям.

Однако при применении компьютера удобнее использовать более общий подход - метод наименьших квадратов.

Алгебраический метод наименьших квадратов

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

путем минимизации евклидовой нормы

Ax ? b? > inf . (1.2)

Анализ данных эксперимента

Рассмотрим некоторый эксперимент, в ходе которого в моменты времени

производится, например, измерение температуры Q(t). Пусть результаты измерений задаются массивом

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

P(t) = + + + ... +,

определяя неизвестные коэффициенты, ..., из тех соображений, чтобы величина E(, ...,), определяемая равенством

гаусс алгебраический exel аппроксимация

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

Если заменить P(t) его выражением, то получим

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

Если ввести m Ч n матрицу A = (), i = 1, 2..., m; j = 1, 2, ..., n, где

I = 1, 2..., m; j = 1, 2, ..., n,

то выписанное равенство примет вид

Перепишем написанное равенство в терминах операций с матрицами. Имеем по определению умножения матрицы на столбец

Для транспонированной матрицы аналогичное соотношение выглядит так

Введем обозначение: i -ую компоненту вектора Ax будем обозначать В соответствии с выписанными матричными равенствами будем иметь

В матричной форме это равенство перепишется в виде

A T x=A T B (1.3)

Здесь A - прямоугольная mЧ n матрица. Причем в задачах аппроксимации данных, как правило, m > n. Уравнение (1.3) называется нормальным уравнением.

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

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

2A T B + 2A T Ax

и поэтому решение должно удовлетворять системе линейных уравнений

(A T A)x = (A T B).

Эти уравнения называются нормальными уравнениями. Если A - mЧ n матрица, то A>A - n Ч n - матрица, т.е. матрица нормального уравнения всегда квадратная симметричная матрица. Более того, она обладает свойством положительной определенности в том смысле, что (A>Ax, x) = (Ax, Ax) ? 0.

Замечание. Иногда решение уравнения вида (1.3) называют решением систе- мы Ax = В, где A прямоугольная m Ч n (m > n) матрица методом наименьших квадратов.

Задачу наименьших квадратов можно графически интерпретировать как минимизацию вертикальных расстояний от точек данных до модельной кривой (см. рис.1.1). Эта идея основана на предположении, что все ошибки в аппроксимации соответствуют ошибкам в наблюдениях. Если имеются также ошибки в независимых переменных, то может оказаться более уместным минимизировать евклидово расстояние от данных до модели.

МНК в Excel

Приведенный ниже алгоритм реализации МНК в Excel подразумевает, что все исходные данные уже известны. Обе части матричного уравнения AЧX=B системы умножаем слева на транспонированную матрицу системы А Т:

А Т АХ=А Т В

Затем обе части уравнения умножаем слева на матрицу (А Т А) -1 . Если эта матрица существует, то система определена. С учетом того, что

(А Т А) -1 *(А Т А)=Е, получаем

Х=(А Т А) -1 А Т В.

Полученное матричное уравнение является решением системы m линейных уравнений с nнеизвестными при m>n.

Рассмотрим применение вышеописанного алгоритма на конкретном примере.

Пример. Пусть необходимо решить систему

В Excelлист с решением в режиме отображения формул для данной задачи выглядит следующим образом:

Результаты расчетов:

Искомый вектор Х расположен в диапазоне Е11:Е12.

При решении заданной системы линейных уравнений использовались следующие функции:

1. МОБР - возвращает обратную матрицу для матрицы, хранящейся в массиве.

Синтаксис: МОБР(массив).

Массив -- числовой массив с равным количеством строк и столбцов.

2. МУМНОЖ - возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2.

Синтаксис: МУМНОЖ(массив1;массив2).

Массив1, массив2 -- перемножаемые массивы.

После введения функции в левую верхнюю ячейку диапазона массива следует выделить массив, начиная с ячейки, содержащей формулу, нажать клавишу F2, а затем нажать клавиши CTRL+SHIFT+ENTER.

3. ТРАНСП - преобразует вертикальный набор ячеек в горизонтальный, или наоборот. В результате использования этой функции появляется массив с числом строк, равным числу столбцов исходного массива, и числом столбцов, равным числу строк начального массива.