Метод на най-малките квадрати за определяне на коефициентите. Апроксимация на експериментални данни

Таблица 4

x n

y n

Таблица 5

y i

0,705

0,495

0,426

0,357

0,368

0,406

0,549

0,768

Таблица 6

№0

х

0,1

0,2

0,3

0,4

0,5

0,6

0,7

0,8

г

3,030

3,142

3,358

3,463

3,772

3,251

3,170

3,665

№ 1

3,314

3,278

3,262

3,292

3,332

3,397

3,487

3,563

№ 2

1,045

1,162

1,264

1,172

1,070

0,898

0,656

0,344

№ 3

6,715

6,735

6,750

6,741

6,645

6,639

6,647

6,612

№ 4

2,325

2,515

2,638

2,700

2,696

2,626

2,491

2,291

№ 5

1.752

1,762

1,777

1,797

1,821

1,850

1,884

1,944

№ 6

1,924

1,710

1,525

1,370

1,264

1,190

1,148

1,127

№ 7

1,025

1,144

1,336

1,419

1,479

1,530

1,568

1,248

№ 8

5,785

5,685

5,605

5,545

5,505

5,480

5,495

5,510

№ 9

4,052

4,092

4,152

4,234

4,338

4,468

4,599

Пример.

Експериментални данни за стойностите на променливите хИ приса дадени в таблицата.

В резултат на подравняването им се получава функцията

Използвайки метод на най-малките квадрати, апроксимирайте тези данни чрез линейна зависимост y=ax+b(намерете параметри АИ b). Открийте коя от двете линии по-добре (в смисъла на метода на най-малките квадрати) подравнява експерименталните данни. Направете рисунка.

Същността на метода на най-малките квадрати (МНК).

Задачата е да се намерят коефициентите на линейна зависимост, при които функцията на две променливи АИ b приема най-малката стойност. Тоест дадено АИ bсумата от квадратите на отклоненията на експерименталните данни от намерената права линия ще бъде най-малка. Това е целият смисъл на метода на най-малките квадрати.

Така решаването на примера се свежда до намиране на екстремума на функция на две променливи.

Извеждане на формули за намиране на коефициенти.

Съставя се и се решава система от две уравнения с две неизвестни. Намиране на частни производни на функция по променливи АИ b, ние приравняваме тези производни на нула.

Ние решаваме получената система от уравнения, използвайки произволен метод (напр по метода на заместванеили Методът на Крамер) и получете формули за намиране на коефициенти с помощта на метода на най-малките квадрати (LSM).

дадени АИ bфункция приема най-малката стойност. Дадено е доказателство за този факт по-долу в текста в края на страницата.

Това е целият метод на най-малките квадрати. Формула за намиране на параметъра асъдържа сумите ,, и параметър н- количество експериментални данни. Препоръчваме да изчислите стойностите на тези суми отделно. Коефициент bнамерени след изчисление а.

Време е да си припомним оригиналния пример.

Решение.

В нашия пример n=5. Попълваме таблицата за удобство при изчисляване на сумите, които са включени във формулите на необходимите коефициенти.

Стойностите в четвъртия ред на таблицата се получават чрез умножаване на стойностите на 2-ри ред по стойностите на 3-ти ред за всяко число аз.

Стойностите в петия ред на таблицата се получават чрез повдигане на квадрат на стойностите във 2-ри ред за всяко число аз.

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

Използваме формулите на метода на най-малките квадрати, за да намерим коефициентите АИ b. Заменяме съответните стойности от последната колона на таблицата в тях:

следователно y = 0,165x+2,184- желаната апроксимираща права линия.

Остава да разберем коя от линиите y = 0,165x+2,184или приближава по-добре оригиналните данни, тоест прави оценка, използвайки метода на най-малките квадрати.

Оценка на грешката на метода на най-малките квадрати.

За да направите това, трябва да изчислите сумата на квадратите на отклоненията на оригиналните данни от тези редове И , по-малка стойност съответства на линия, която по-добре приближава оригиналните данни по смисъла на метода на най-малките квадрати.

Тъй като , тогава направо y = 0,165x+2,184по-добре приближава оригиналните данни.

Графична илюстрация на метода на най-малките квадрати (LS).

Всичко се вижда ясно на графиките. Червената линия е намерената права линия y = 0,165x+2,184, синята линия е , розовите точки са оригиналните данни.

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

Този вид проблем с апроксимацията на функция често възниква:

при конструиране на приблизителни формули за изчисляване на стойностите на характерните величини на изследвания процес, като се използват таблични данни, получени в резултат на експеримента;

при числено интегриране, диференциране, решаване на диференциални уравнения и др.;

ако е необходимо, изчислете стойностите на функциите в междинните точки на разглеждания интервал;

при определяне на стойностите на характерните величини на процес извън разглеждания интервал, по-специално при прогнозиране.

Ако, за да моделираме определен процес, определен от таблица, конструираме функция, която приблизително описва този процес въз основа на метода на най-малките квадрати, тя ще се нарича апроксимираща функция (регресия), а самата задача за конструиране на апроксимиращи функции ще се нарича проблем с приближението.

Тази статия разглежда възможностите на пакета MS Excel за решаване на този тип проблеми, освен това предоставя методи и техники за конструиране (създаване) на регресии за таблични функции (което е в основата на регресионния анализ).

Excel има две опции за изграждане на регресии.

Добавяне на избрани регресии (трендови линии) към диаграма, изградена на базата на таблица с данни за изследваната характеристика на процеса (достъпно само ако е изградена диаграма);

Използване на вградените статистически функции на работния лист на Excel, което ви позволява да получавате регресии (линии на тенденции) директно от таблицата с изходни данни.

Добавяне на трендови линии към диаграма

За таблица с данни, която описва процес и е представена чрез диаграма, Excel разполага с ефективен инструмент за регресионен анализ, който ви позволява да:

изградете на базата на метода на най-малките квадрати и добавете пет вида регресии към диаграмата, които моделират изследвания процес с различна степен на точност;

добавете построеното регресионно уравнение към диаграмата;

определяне на степента на съответствие на избраната регресия с данните, показани на диаграмата.

Въз основа на данни от диаграма, Excel ви позволява да получите линейни, полиномиални, логаритмични, степенни, експоненциални типове регресии, които се определят от уравнението:

y = y(x)

където x е независима променлива, която често приема стойностите на поредица от естествени числа (1; 2; 3; ...) и произвежда, например, обратно броене на времето на процеса, който се изследва (характеристики).

1 . Линейната регресия е добра за моделиране на характеристики, чиито стойности се увеличават или намаляват с постоянна скорост. Това е най-простият модел за конструиране на процеса, който се изследва. Той е конструиран в съответствие с уравнението:

y = mx + b

където m е тангенса на наклона на линейната регресия към оста x; b - координата на пресечната точка на линейната регресия с ординатната ос.

2 . Полиномиалната тренд линия е полезна за описание на характеристики, които имат няколко различни крайности (максимуми и минимуми). Изборът на степен на полином се определя от броя на екстремумите на изследваната характеристика. По този начин полином от втора степен може добре да опише процес, който има само един максимум или минимум; полином от трета степен - не повече от два екстремума; полином от четвърта степен - не повече от три екстремума и т.н.

В този случай тренд линията се конструира в съответствие с уравнението:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

където коефициентите c0, c1, c2,... c6 са константи, чиито стойности се определят по време на конструирането.

3 . Логаритмичната тренд линия се използва успешно при моделиране на характеристики, чиито стойности първоначално се променят бързо и след това постепенно се стабилизират.

y = c ln(x) + b

4 . Линията на тенденцията на степенния закон дава добри резултати, ако стойностите на изследваната връзка се характеризират с постоянна промяна в скоростта на растеж. Пример за такава зависимост е графиката на равномерно ускорено движение на автомобил. Ако има нулеви или отрицателни стойности в данните, не можете да използвате линия на тенденция на мощността.

Конструиран в съответствие с уравнението:

y = c xb

където коефициентите b, c са константи.

5 . Трябва да се използва експоненциална тренд линия, когато скоростта на промяна в данните непрекъснато нараства. За данни, съдържащи нулеви или отрицателни стойности, този тип приближение също не е приложим.

Конструиран в съответствие с уравнението:

y = c ebx

където коефициентите b, c са константи.

Когато избира линия на тенденция, Excel автоматично изчислява стойността на R2, която характеризира надеждността на приближението: колкото по-близо е стойността на R2 до единица, толкова по-надеждно линията на тенденция приближава изследвания процес. Ако е необходимо, стойността на R2 винаги може да се покаже на диаграмата.

Определя се по формулата:

За да добавите линия на тенденция към серия от данни:

активирайте диаграма въз основа на поредица от данни, т.е. щракнете в областта на диаграмата. Елементът Диаграма ще се появи в главното меню;

след като щракнете върху този елемент, на екрана ще се появи меню, в което трябва да изберете командата Добавяне на тренд линия.

Същите действия могат лесно да бъдат изпълнени чрез преместване на показалеца на мишката върху графиката, съответстваща на една от сериите данни, и щракване с десния бутон; В контекстното меню, което се показва, изберете командата Добавяне на линия на тенденция. На екрана ще се появи диалоговият прозорец Trendline с отворен раздел Type (фиг. 1).

След това имате нужда от:

Изберете необходимия тип линия на тренда в раздела Тип (типът Линеен е избран по подразбиране). За тип Полином в полето Степен посочете степента на избрания полином.

1 . Полето Изградено върху серия изброява всички серии от данни във въпросната диаграма. За да добавите линия на тенденция към конкретна серия от данни, изберете нейното име в полето Изградено върху серия.

Ако е необходимо, като отидете в раздела Параметри (фиг. 2), можете да зададете следните параметри за линията на тренда:

променете името на тренд линията в полето Име на апроксимиращата (изгладена) крива.

задайте броя на периодите (напред или назад) за прогнозата в полето Прогноза;

показване на уравнението на тренд линията в областта на диаграмата, за което трябва да активирате отметката за показване на уравнение на диаграмата;

покажете стойността на надеждност на приближението R2 в областта на диаграмата, за която трябва да поставите отметка в квадратчето Поставете стойността на надеждност на приближението върху диаграмата (R^2);

задайте пресечната точка на линията на тренда с оста Y, за което трябва да активирате отметката за пресичане на кривата с оста Y в точка;

Щракнете върху бутона OK, за да затворите диалоговия прозорец.

За да започнете да редактирате вече начертана тренд линия, има три начина:

използвайте командата Selected trend line от меню Format, като предварително сте избрали тренд линията;

изберете командата Форматиране на линията на тренда от контекстното меню, което се извиква с щракване с десен бутон върху линията на тренда;

щракнете двукратно върху тренд линията.

На екрана ще се появи диалоговият прозорец Формат на линията на тренда (Фиг. 3), съдържащ три раздела: Изглед, Тип, Параметри, като съдържанието на последните два напълно съвпада с подобни раздели на диалоговия прозорец Линия на тренда (Фиг. 1 -2). В раздела Изглед можете да зададете вида на линията, нейния цвят и дебелина.

За да изтриете линия на тенденция, която вече е била начертана, изберете линията на тенденция, която ще бъде изтрита, и натиснете клавиша Delete.

Предимствата на разглеждания инструмент за регресионен анализ са:

относителната лекота на конструиране на тренд линия върху диаграми, без да се създава таблица с данни за нея;

доста широк списък от видове предложени линии на тренд, като този списък включва най-често използваните видове регресия;

способността да се прогнозира поведението на изследвания процес чрез произволен (в рамките на здравия разум) брой стъпки напред и назад;

способността да се получи уравнението на тренд линията в аналитична форма;

възможността, ако е необходимо, да се получи оценка на надеждността на приближението.

Недостатъците включват следното:

изграждането на тренд линия се извършва само ако има диаграма, изградена върху серия от данни;

процесът на генериране на серия от данни за изследваната характеристика въз основа на уравненията на тренд линията, получени за нея, е донякъде претрупан: необходимите регресионни уравнения се актуализират с всяка промяна в стойностите на оригиналната серия от данни, но само в областта на диаграмата , докато серията от данни, формирана на базата на старото линейно уравнение, тенденцията остава непроменена;

В отчетите с обобщена диаграма промяната на изгледа на диаграма или свързан отчет с обобщена таблица не запазва съществуващите линии на тенденции, което означава, че преди да начертаете линии на тенденция или по друг начин да форматирате отчет с обобщена диаграма, трябва да се уверите, че оформлението на отчета отговаря на необходимите изисквания.

Линиите на тенденциите могат да се използват за допълване на серии от данни, представени на диаграми като графики, хистограми, плоски нестандартизирани площни диаграми, стълбовидни диаграми, точкови диаграми, балонови диаграми и борсови диаграми.

Не можете да добавяте линии на тенденции към серии от данни в 3D, нормализирани, радарни, кръгови и кръгови диаграми.

Използване на вградените функции на Excel

Excel също има инструмент за регресионен анализ за начертаване на линии на тенденции извън областта на диаграмата. Има редица функции за статистически работен лист, които можете да използвате за тази цел, но всички те ви позволяват да създавате само линейни или експоненциални регресии.

Excel има няколко функции за конструиране на линейна регресия, по-специално:

ТЕНДЕНЦИЯ;

• НАКЛОН и РЕЗ.

Както и няколко функции за конструиране на експоненциална тренд линия, по-специално:

LGRFPRIBL.

Трябва да се отбележи, че техниките за конструиране на регресии с помощта на функциите TREND и GROWTH са почти еднакви. Същото може да се каже и за двойката функции LINEST и LGRFPRIBL. За тези четири функции създаването на таблица със стойности използва функции на Excel като формули за масиви, което донякъде пречи на процеса на изграждане на регресии. Нека също така да отбележим, че конструкцията на линейна регресия, според нас, се осъществява най-лесно с помощта на функциите SLOPE и INTERCEPT, където първата от тях определя наклона на линейната регресия, а втората определя сегмента, пресечен от регресията върху у-оста.

Предимствата на инструмента за вградени функции за регресионен анализ са:

сравнително прост, еднообразен процес на генериране на поредици от данни на изследваната характеристика за всички вградени статистически функции, които определят линии на тенденция;

стандартна методология за конструиране на трендови линии въз основа на генерирани серии от данни;

способността да се предвиди поведението на изследвания процес чрез необходимия брой стъпки напред или назад.

Недостатъците включват факта, че Excel няма вградени функции за създаване на други (освен линейни и експоненциални) типове линии на тренд. Това обстоятелство често не позволява да се избере достатъчно точен модел на изследвания процес, както и да се получат прогнози, които са близки до реалността. Освен това, когато използвате функциите TREND и GROWTH, уравненията на линиите на тренда не са известни.

Трябва да се отбележи, че авторите не си поставиха за цел да представят хода на регресионния анализ с някаква степен на пълнота. Основната му задача е да покаже на конкретни примери възможностите на пакета Excel при решаване на апроксимационни задачи; демонстрират какви ефективни инструменти има Excel за изграждане на регресии и прогнозиране; илюстрират как такива проблеми могат да бъдат решени относително лесно дори от потребител, който няма задълбочени познания за регресионен анализ.

Примери за решаване на конкретни проблеми

Нека да разгледаме решаването на конкретни проблеми с помощта на изброените инструменти на Excel.

Проблем 1

С таблица с данни за печалбата на автотранспортно предприятие за 1995-2002 г. трябва да направите следното:

Изградете диаграма.

Добавете линейни и полиномни (квадратични и кубични) трендови линии към диаграмата.

Използвайки уравненията на линията на тренда, вземете таблични данни за печалбите на предприятието за всяка линия на тренда за 1995-2004 г.

Направете прогноза за печалбата на предприятието за 2003 и 2004 г.

Решението на проблема

В диапазона от клетки A4:C11 на работния лист на Excel въведете работния лист, показан на фиг. 4.

След като избрахме диапазона от клетки B4:C11, изграждаме диаграма.

Активираме изградената диаграма и, съгласно описания по-горе метод, след като изберем вида на линията на тренда в диалоговия прозорец Линия на тренда (виж Фиг. 1), добавяме последователно линейни, квадратни и кубични линии на тренд към диаграмата. В същия диалогов прозорец отворете раздела Параметри (вижте фиг. 2), в полето Име на апроксимиращата (изгладена) крива въведете името на добавяния тренд и в полето Прогноза напред за: периоди задайте стойност 2, тъй като се планира да се направи прогноза за печалба за две години напред. За да покажете уравнението на регресията и стойността на надеждност на приближението R2 в областта на диаграмата, активирайте квадратчетата за отметка на показване на уравнение на екрана и поставете стойността на надеждност на приближението (R^2) върху диаграмата. За по-добро визуално възприятие променяме вида, цвета и дебелината на изградените линии на тренда, за което използваме раздела Изглед на диалоговия прозорец Формат на линията на тренда (виж Фиг. 3). Получената диаграма с добавени линии на тенденция е показана на фиг. 5.

Да се ​​получат таблични данни за печалбите на предприятията за всяка тренд линия за 1995-2004 г. Нека използваме уравненията на трендовата линия, представени на фиг. 5. За целта в клетките на диапазона D3:F3 въведете текстова информация за вида на избраната тренд линия: Линеен тренд, Квадратичен тренд, Кубичен тренд. След това въведете формулата за линейна регресия в клетка D4 и, като използвате маркера за запълване, копирайте тази формула с относителни препратки към диапазона от клетки D5:D13. Трябва да се отбележи, че всяка клетка с формула за линейна регресия от диапазона от клетки D4:D13 има като аргумент съответстваща клетка от диапазона A4:A13. По същия начин, за квадратична регресия, попълнете диапазона от клетки E4:E13, а за кубична регресия, попълнете диапазона от клетки F4:F13. Така е съставена прогноза за печалбата на предприятието за 2003 и 2004 г. използвайки три тенденции. Получената таблица със стойности е показана на фиг. 6.

Проблем 2

Изградете диаграма.

Добавете логаритмични, степенни и експоненциални тренд линии към диаграмата.

Изведете уравненията на получените трендови линии, както и стойностите на надеждност на апроксимацията R2 за всяка от тях.

Използвайки уравненията на тренд линията, получете таблични данни за печалбата на предприятието за всяка тренд линия за 1995-2002 г.

Направете прогноза за печалбата на компанията за 2003 и 2004 г., като използвате тези линии на тенденция.

Решението на проблема

Следвайки методологията, дадена при решаването на задача 1, получаваме диаграма с добавени към нея логаритмични, степенни и експоненциални линии на тренда (фиг. 7). След това, използвайки получените уравнения на линията на тенденцията, попълваме таблица със стойности за печалбата на предприятието, включително прогнозираните стойности за 2003 и 2004 г. (фиг. 8).

На фиг. 5 и фиг. може да се види, че моделът с логаритмичен тренд отговаря на най-ниската стойност на надеждност на приближението

R2 = 0,8659

Най-високите стойности на R2 съответстват на модели с полиномен тренд: квадратичен (R2 = 0,9263) и кубичен (R2 = 0,933).

Проблем 3

С таблицата с данни за печалбата на автомобилно транспортно предприятие за 1995-2002 г., дадена в задача 1, трябва да изпълните следните стъпки.

Получавайте серии от данни за линейни и експоненциални трендови линии с помощта на функциите TREND и GROW.

Използвайки функциите TREND и GROWTH, направете прогноза за печалбата на предприятието за 2003 и 2004 г.

Изградете диаграма за оригиналните данни и получената поредица от данни.

Решението на проблема

Нека използваме работния лист за задача 1 (виж Фиг. 4). Нека започнем с функцията TREND:

изберете диапазона от клетки D4: D11, който трябва да бъде попълнен със стойностите на функцията TREND, съответстващи на известните данни за печалбата на предприятието;

Извикайте командата Функция от менюто Вмъкване. В диалоговия прозорец на съветника за функции, който се появява, изберете функцията TREND от категорията Statistical и след това щракнете върху бутона OK. Същата операция може да се извърши чрез щракване върху бутона (Вмъкване на функция) в стандартната лента с инструменти.

В диалоговия прозорец Аргументи на функцията, който се появява, въведете диапазона от клетки C4:C11 в полето Known_values_y; в поле Известни_стойности_x - диапазонът от клетки B4:B11;

За да направите въведената формула формула за масив, използвайте клавишната комбинация + + .

Формулата, която въведохме в лентата с формули, ще изглежда така: =(ТРЕНД(C4:C11,B4:B11)).

В резултат на това диапазонът от клетки D4:D11 се запълва със съответните стойности на функцията TREND (фиг. 9).

Да се ​​направи прогноза за печалбата на предприятието за 2003 и 2004 г. необходимо:

изберете диапазона от клетки D12:D13, където ще бъдат въведени стойностите, предвидени от функцията TREND.

извикайте функцията TREND и в появилия се диалогов прозорец Arguments на функцията въведете в полето Known_values_y - диапазона от клетки C4:C11; в поле Известни_стойности_x - диапазонът от клетки B4:B11; а в полето New_values_x - диапазона от клетки B12:B13.

превърнете тази формула във формула за масив, като използвате клавишната комбинация Ctrl + Shift + Enter.

Въведената формула ще изглежда така: =(TREND(C4:C11;B4:B11;B12:B13)), а диапазонът от клетки D12:D13 ще бъде запълнен с прогнозираните стойности на функцията TREND (вижте фиг. 9).

Сериите от данни се попълват по подобен начин с помощта на функцията GROWTH, която се използва при анализа на нелинейни зависимости и работи по абсолютно същия начин като нейния линеен аналог TREND.

Фигура 10 показва таблицата в режим на показване на формула.

За първоначалните данни и получените серии от данни, диаграмата, показана на фиг. единадесет.

Проблем 4

С таблицата с данни за получаване на заявления за услуги от диспечерската служба на автомобилно транспортно предприятие за периода от 1 до 11 число на текущия месец трябва да извършите следните действия.

Получаване на серии от данни за линейна регресия: използване на функциите SLOPE и INTERCEPT; с помощта на функцията LINEST.

Получете поредица от данни за експоненциална регресия с помощта на функцията LGRFPRIBL.

Използвайки горните функции, направете прогноза за получаването на заявления в диспечерската служба за периода от 12 до 14 число на текущия месец.

Създайте диаграма за оригиналната и получената серия от данни.

Решението на проблема

Обърнете внимание, че за разлика от функциите TREND и GROWTH, нито една от изброените по-горе функции (SLOPE, INTERCEPT, LINEST, LGRFPRIB) не е регресия. Тези функции играят само спомагателна роля, определяйки необходимите регресионни параметри.

За линейни и експоненциални регресии, изградени с помощта на функциите SLOPE, INTERCEPT, LINEST, LGRFPRIB, външният вид на техните уравнения винаги е известен, за разлика от линейните и експоненциалните регресии, съответстващи на функциите TREND и GROWTH.

1 . Нека изградим линейна регресия с уравнението:

y = mx+b

с помощта на функциите SLOPE и INTERCEPT, като регресионният наклон m се определя от функцията SLOPE, а свободният член b от функцията INTERCEPT.

За целта извършваме следните действия:

въведете оригиналната таблица в диапазона от клетки A4:B14;

стойността на параметъра m ще бъде определена в клетка C19. Изберете функцията Slope от категорията Statistical; въведете диапазона от клетки B4:B14 в полетоknown_values_y и диапазона от клетки A4:A14 в полетоknown_values_x. Формулата ще бъде въведена в клетка C19: =SLOPE(B4:B14,A4:A14);

С помощта на подобна техника се определя стойността на параметър b в клетка D19. И съдържанието му ще изглежда така: =СЕГМЕНТ(B4:B14,A4:A14). По този начин стойностите на параметрите m и b, необходими за конструиране на линейна регресия, ще бъдат съхранени съответно в клетки C19, D19;

След това въведете формулата за линейна регресия в клетка C4 във формата: =$C*A4+$D. В тази формула клетки C19 и D19 са записани с абсолютни препратки (адресът на клетката не трябва да се променя при евентуално копиране). Абсолютният референтен знак $ може да бъде въведен или от клавиатурата, или с помощта на клавиша F4, след поставяне на курсора върху адреса на клетката. С помощта на манипулатора за попълване копирайте тази формула в диапазона от клетки C4:C17. Получаваме необходимите серии от данни (фиг. 12). Поради факта, че броят на заявките е цяло число, трябва да зададете числовия формат с броя на десетичните знаци на 0 в раздела Число на прозореца Формат на клетката.

2 . Сега нека изградим линейна регресия, дадена от уравнението:

y = mx+b

с помощта на функцията LINEST.

За това:

Въведете функцията LINEST като формула за масив в диапазона от клетки C20:D20: =(LINEST(B4:B14,A4:A14)). В резултат на това получаваме стойността на параметър m в клетка C20 и стойността на параметър b в клетка D20;

въведете формулата в клетка D4: =$C*A4+$D;

копирайте тази формула с помощта на маркера за запълване в диапазона от клетки D4:D17 и вземете желаната поредица от данни.

3 . Изграждаме експоненциална регресия с уравнението:

с помощта на функцията LGRFPRIBL се изпълнява по подобен начин:

В диапазона от клетки C21:D21 въвеждаме функцията LGRFPRIBL като формула за масив: =( LGRFPRIBL (B4:B14,A4:A14)). В този случай стойността на параметър m ще бъде определена в клетка C21, а стойността на параметър b ще бъде определена в клетка D21;

формулата се въвежда в клетка E4: =$D*$C^A4;

използвайки маркера за запълване, тази формула се копира в диапазона от клетки E4:E17, където ще бъдат разположени серията данни за експоненциална регресия (вижте Фиг. 12).

На фиг. Фигура 13 показва таблица, в която можете да видите функциите, които използваме с необходимите диапазони от клетки, както и формули.

величина Р 2 Наречен коефициент на детерминация.

Задачата за изграждане на регресионна зависимост е да се намери векторът на коефициентите m на модела (1), при който коефициентът R придобива максимална стойност.

За да се оцени значимостта на R, се използва F-тестът на Фишер, изчислен по формулата

Където н- размер на извадката (брой експерименти);

k е броят на коефициентите на модела.

Ако F надвиши някаква критична стойност за данните нИ ки приетата вероятност за доверие, тогава стойността на R се счита за значима. Таблици с критични стойности на F са дадени в справочници по математическа статистика.

По този начин значимостта на R се определя не само от неговата стойност, но и от съотношението между броя на експериментите и броя на коефициентите (параметрите) на модела. Наистина, съотношението на корелация за n=2 за прост линеен модел е равно на 1 (една права линия винаги може да бъде начертана през 2 точки на равнина). Въпреки това, ако експерименталните данни са случайни променливи, на такава стойност на R трябва да се вярва с голяма предпазливост. Обикновено, за да получат значително R и надеждна регресия, те се стремят да гарантират, че броят на експериментите значително надвишава броя на коефициентите на модела (n>k).

За да изградите линеен регресионен модел, имате нужда от:

1) подгответе списък от n реда и m колони, съдържащи експериментални данни (колона, съдържаща изходната стойност Yтрябва да е първи или последен в списъка); Например, нека вземем данните от предишната задача, като добавим колона, наречена „Номер на период“, номерирайте номерата на периодите от 1 до 12. (това ще бъдат стойностите х)

2) отидете в менюто Данни/Анализ на данни/Регресия

Ако елементът „Анализ на данни“ в менюто „Инструменти“ липсва, тогава трябва да отидете на елемента „Добавки“ в същото меню и да поставите отметка в квадратчето „Пакет за анализ“.

3) в диалоговия прозорец "Регресия" задайте:

· входен интервал Y;

· входен интервал X;

· изходен интервал - горната лява клетка на интервала, в който ще бъдат поставени резултатите от изчислението (препоръчително е да ги поставите на нов работен лист);

4) щракнете върху "Ok" и анализирайте резултатите.

Същността на метода е, че критерият за качеството на разглежданото решение е сумата от квадратите на грешките, които се стремят да минимизират. За да се приложи това, е необходимо да се извършат възможно най-много измервания на неизвестната случайна променлива (колкото повече, толкова по-висока е точността на решението) и определен набор от оценени решения, от които трябва да се избере най-доброто. Ако наборът от решения е параметризиран, тогава трябва да намерим оптималната стойност на параметрите.

Защо се минимизират грешките на квадрат, а не самите грешки? Факт е, че в повечето случаи грешките са в двете посоки: оценката може да бъде повече от измерването или по-малко от него. Ако сумираме грешки с различни знаци, те ще се компенсират взаимно и в резултат сумата ще ни даде неправилна представа за качеството на оценката. Често, за да може крайната оценка да има същото измерение като измерените стойности, се взема корен квадратен от сумата на квадратите на грешките.

LSM се използва в математиката, по-специално в теорията на вероятностите и математическата статистика. Този метод се използва най-широко при проблеми с филтрирането, когато е необходимо да се отдели полезният сигнал от шума, насложен върху него.

Използва се и в математическия анализ за приблизително представяне на дадена функция чрез по-прости функции. Друга област на приложение на най-малките квадрати е решаването на системи от уравнения с брой неизвестни, по-малък от броя на уравненията.

Измислих още няколко много неочаквани области на приложение на MNC, за които бих искал да говоря в тази статия.

OLS и правописни грешки

Бичът на автоматичните преводачи и търсачките са печатни и правописни грешки. Наистина, ако една дума се различава само с 1 буква, програмата я третира като друга дума и я превежда/търси неправилно или не я превежда/въобще не я намира.

Имах подобен проблем: имах две бази данни с адреси на къщи в Москва и трябваше да ги комбинирам в една. Но адресите бяха написани в различни стилове. Една база данни съдържаше стандарта KLADR (Всеруски класификатор на адреси), например: „УЛ. БАБУШКИНА ЛЕЧИКА, D10K3.“ А в друга база данни имаше пощенски стил, например: „Св. Пилот Бабушкина, сграда 10, сграда 3. Изглежда, че няма грешки и в двата случая, но автоматизирането на процеса е невероятно трудно (всяка база данни има 40 хиляди записа!). Въпреки че имаше и много правописни грешки... Как да накарам компютъра да разбере, че горните 2 адреса принадлежат на една и съща къща? Това е мястото, където MNC ми беше полезен.

Какво съм направил? След като намерих следващата буква в първия адрес, потърсих същата буква във втория адрес. Ако и двете бяха на едно и също място, тогава задавах грешката за тази буква да бъде 0. Ако бяха в съседни позиции, тогава грешката беше 1. Ако имаше изместване с 2 позиции, грешката беше 2 и т.н. Ако изобщо нямаше такава буква в друг адрес, тогава грешката се приемаше равна на n+1, където n е броят на буквите в първия адрес. Така изчислих сумата на квадратите на грешките и комбинирах онези записи, в които тази сума беше минимална.

Разбира се, номерата на къщи и сгради се обработват отделно. Не знам дали съм измислил друг „велосипед“ или наистина беше, но проблемът беше решен бързо и ефективно. Чудя се дали този метод се използва в търсачките? Може би е така, защото всяка уважаваща себе си търсачка, когато срещне непозната дума, предлага замяна от познати думи („може би имахте предвид ...“). Те обаче могат да направят този анализ по някакъв друг начин.

OLS и търсене по снимки, лица и карти

Този метод може да се използва и за търсене с помощта на снимки, рисунки, карти и дори лица на хора.

снимка:

Сега всички търсачки, вместо да търсят по снимки, по същество използват търсене по надписи към снимки. Това несъмнено е полезна и удобна услуга, но предлагам да я допълните с истинско търсене на изображения.

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

Чрез сравняване на тези параметри можете да намерите лицето, което е най-сходно с извадката. Вече видях сайтове, където тази услуга работи и можете да намерите знаменитостта, която е най-подобна на предложената от вас снимка, и дори да създадете анимация, която ви превръща в знаменитост и обратно. Със сигурност същият метод работи в базите данни на МВР, съдържащи идентификационни изображения на престъпници.

Снимка: pixabay.com

Да, и можете да търсите с пръстови отпечатъци по същия метод. Търсенето на карти е фокусирано върху естествените неравности на географски обекти - завои на реки, планински вериги, очертания на брегове, гори и полета.

Това е толкова прекрасен и универсален метод на най-малките квадрати. Сигурен съм, че вие, скъпи читатели, ще можете сами да намерите много необичайни и неочаквани области на приложение на този метод.

Има много приложения, тъй като позволява приблизително представяне на дадена функция от други по-прости. LSM може да бъде изключително полезен при обработката на наблюдения и се използва активно за оценка на някои количества въз основа на резултатите от измервания на други, съдържащи случайни грешки. В тази статия ще научите как да прилагате изчисления на най-малките квадрати в Excel.

Изложение на проблема с помощта на конкретен пример

Да предположим, че има два индикатора X и Y. Освен това Y зависи от X. Тъй като OLS ни интересува от гледна точка на регресионния анализ (в Excel неговите методи се изпълняват с помощта на вградени функции), трябва незабавно да преминем към разглеждане на специфичен проблем.

И така, нека X е търговската площ на магазин за хранителни стоки, измерена в квадратни метри, а Y е годишният оборот, измерен в милиони рубли.

Необходимо е да се направи прогноза какъв оборот (Y) ще има магазинът, ако има тази или онази търговска площ. Очевидно функцията Y = f (X) нараства, тъй като хипермаркетът продава повече стоки от щанда.

Няколко думи за коректността на първоначалните данни, използвани за прогнозиране

Да кажем, че имаме таблица, изградена с помощта на данни за n магазина.

Според математическата статистика резултатите ще бъдат повече или по-малко правилни, ако се изследват данни за поне 5-6 обекта. Освен това не могат да се използват „аномални“ резултати. По-специално, елитен малък бутик може да има оборот, който е няколко пъти по-голям от оборота на големите търговски обекти от класа „masmarket“.

Същността на метода

Данните от таблицата могат да бъдат изобразени на декартова равнина под формата на точки M 1 (x 1, y 1), ... M n (x n, y n). Сега решението на проблема ще се сведе до избора на апроксимираща функция y = f (x), която има графика, минаваща възможно най-близо до точките M 1, M 2, .. M n.

Разбира се, можете да използвате полином с висока степен, но тази опция е не само трудна за изпълнение, но и просто неправилна, тъй като няма да отразява основната тенденция, която трябва да бъде открита. Най-разумното решение е да се търси правата линия y = ax + b, която най-добре приближава експерименталните данни или по-точно коефициентите a и b.

Оценка на точността

При всяко приближение оценката на неговата точност е от особено значение. Нека обозначим с e i разликата (отклонението) между функционалните и експерименталните стойности за точка x i, т.е. e i = y i - f (x i).

Очевидно, за да оцените точността на приближението, можете да използвате сумата от отклонения, т.е. когато избирате права линия за приблизително представяне на зависимостта на X от Y, трябва да дадете предпочитание на тази с най-малката стойност на sum e i във всички разглеждани точки. Но не всичко е толкова просто, тъй като наред с положителните отклонения ще има и отрицателни.

Проблемът може да бъде решен с помощта на модули за отклонение или техните квадрати. Последният метод е най-широко използваният. Използва се в много области, включително регресионен анализ (имплементиран в Excel с помощта на две вградени функции), и отдавна е доказал своята ефективност.

Метод на най-малките квадрати

Excel, както знаете, има вградена функция AutoSum, която ви позволява да изчислявате стойностите на всички стойности, разположени в избрания диапазон. Така нищо няма да ни попречи да изчислим стойността на израза (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

В математическа нотация това изглежда така:

Тъй като първоначално беше взето решение за приблизително използване на права линия, имаме:

По този начин задачата за намиране на правата линия, която най-добре описва конкретната зависимост на величините X и Y, се свежда до изчисляване на минимума на функция от две променливи:

За да направите това, трябва да приравните частните производни по отношение на новите променливи a и b на нула и да решите примитивна система, състояща се от две уравнения с 2 неизвестни от вида:

След някои прости трансформации, включително деление на 2 и манипулиране на суми, получаваме:

Решавайки го, например, използвайки метода на Крамер, получаваме стационарна точка с определени коефициенти a * и b *. Това е минимумът, т.е. за да се предвиди какъв оборот ще има даден магазин за определен район, е подходяща правата линия y = a * x + b *, която е регресионен модел за въпросния пример. Разбира се, това няма да ви позволи да намерите точния резултат, но ще ви помогне да получите представа дали закупуването на конкретна зона на кредит от магазина ще се изплати.

Как да внедрите най-малките квадрати в Excel

Excel има функция за изчисляване на стойности с помощта на най-малките квадрати. Той има следната форма: „ТЕНДЕНЦИЯ“ (известни Y стойности; известни X стойности; нови X стойности; константа). Нека приложим формулата за изчисляване на OLS в Excel към нашата таблица.

За да направите това, въведете знака “=” в клетката, в която трябва да се покаже резултатът от изчислението по метода на най-малките квадрати в Excel и изберете функцията “TREND”. В прозореца, който се отваря, попълнете съответните полета, като маркирате:

• диапазон от известни стойности за Y (в този случай данни за търговския оборот);
• диапазон x 1 , …x n , т.е. размерът на търговската площ;
• както известни, така и неизвестни стойности на x, за които трябва да разберете размера на оборота (за информация относно тяхното местоположение в работния лист вижте по-долу).

Освен това формулата съдържа логическата променлива „Const“. Ако въведете 1 в съответното поле, това ще означава, че трябва да извършите изчисленията, като приемете, че b = 0.

Ако трябва да разберете прогнозата за повече от една стойност x, тогава след въвеждане на формулата не трябва да натискате „Enter“, а трябва да въведете комбинацията „Shift“ + „Control“ + „Enter“ на клавиатурата.

Някои функции

Регресионният анализ може да бъде достъпен дори за манекени. Формулата на Excel за прогнозиране на стойността на масив от неизвестни променливи - TREND - може да се използва дори от тези, които никога не са чували за най-малките квадрати. Достатъчно е само да знаете някои от характеристиките на работата му. В частност:

• Ако подредите диапазона от известни стойности на променливата y в един ред или колона, тогава всеки ред (колона) с известни стойности на x ще се възприема от програмата като отделна променлива.
• Ако в прозореца TREND не е зададен диапазон с известно x, тогава при използване на функцията в Excel програмата ще го третира като масив, състоящ се от цели числа, чийто брой съответства на диапазона с дадените стойности на променлива y.
• За да изведете масив от „предсказани“ стойности, изразът за изчисляване на тенденцията трябва да бъде въведен като формула за масив.
• Ако не са посочени нови стойности на x, тогава функцията TREND ги счита за равни на известните. Ако те не са посочени, тогава масив 1 се приема като аргумент; 2; 3; 4;…, което е съизмеримо с диапазона с вече посочени параметри y.
• Диапазонът, съдържащ новите x стойности, трябва да има същите или повече редове или колони като диапазона, съдържащ дадените y стойности. С други думи, трябва да е пропорционален на независимите променливи.
• Масив с известни x стойности може да съдържа множество променливи. Ако обаче говорим само за един, тогава се изисква диапазоните с дадените стойности на x и y да бъдат пропорционални. В случай на няколко променливи е необходимо диапазонът с дадените стойности на y да се побере в една колона или един ред.

Функция PREDICTION

Изпълнява се с помощта на няколко функции. Един от тях се нарича „ПРЕДВИДЕНИЕ“. Той е подобен на „TREND“, т.е. дава резултат от изчисления, използвайки метода на най-малките квадрати. Но само за един X, за който стойността на Y е неизвестна.

Вече знаете формули в Excel за манекени, които ви позволяват да предвидите бъдещата стойност на определен индикатор според линейна тенденция.

Методът на най-малките квадрати е математическа процедура за конструиране на линейно уравнение, което най-добре отговаря на набор от подредени двойки чрез намиране на стойностите за a и b, коефициентите в уравнението на правата. Целта на най-малките квадрати е да се минимизира общата квадратна грешка между стойностите на y и ŷ. Ако за всяка точка определим грешката ŷ, методът на най-малките квадрати минимизира:

където n = брой подредени двойки около линията. възможно най-близо до данните.

Тази концепция е илюстрирана на фигурата

Въз основа на фигурата, линията, която най-добре отговаря на данните, регресионната линия, минимизира общата квадратна грешка на четирите точки на графиката. Ще ви покажа как да определите това с помощта на най-малките квадрати със следния пример.

Представете си млада двойка, която наскоро се е преместила заедно и споделя тоалетна масичка в банята. Младият мъж започна да забелязва, че половината от масата му неумолимо се свива, губейки почва пред пяните за коса и соевите комплекси. През последните няколко месеца човекът наблюдаваше внимателно скоростта, с която се увеличаваше броят на предметите от нейната страна на масата. Таблицата по-долу показва броя на предметите, които момичето е натрупало в тоалетната си за баня през последните няколко месеца.

Тъй като целта ни е да разберем дали броят на елементите се увеличава с течение на времето, „Месец“ ще бъде независимата променлива, а „Брой елементи“ ще бъде зависимата променлива.

Използвайки метода на най-малките квадрати, ние определяме уравнението, което най-добре пасва на данните, като изчисляваме стойностите на a, пресечната точка с y, и b, наклона на правата:

a = y ср. - bx ср

където x avg е средната стойност на x, независимата променлива, y avg е средната стойност на y, независимата променлива.

Таблицата по-долу обобщава изчисленията, необходими за тези уравнения.

Кривата на ефекта за нашия пример с вана ще бъде дадена от следното уравнение:

Тъй като нашето уравнение има положителен наклон от 0,976, човекът има доказателства, че броят на елементите на масата се увеличава с течение на времето със средна скорост от 1 елемент на месец. Графиката показва кривата на ефекта с подредени двойки.

Очакванията за броя на елементите през следващите шест месеца (месец 16) ще бъдат изчислени, както следва:

ŷ = 5,13 + 0,976x = 5,13 + 0,976(16) ~ 20,7 = 21 елемента

И така, време е нашият герой да предприеме нещо.

Функция TREND в Excel

Както вероятно вече се досещате, Excel има функция за изчисляване на стойности по метод на най-малките квадрати.Тази функция се нарича TREND. Синтаксисът му е както следва:

ТЕНДЕНЦИЯ (известни Y стойности; известни X стойности; нови X стойности; константа)

известни Y стойности - масив от зависими променливи, в нашия случай броят на обектите в таблицата

известни стойности X - масив от независими променливи, в нашия случай това е месецът

нови X стойности ​​– нови X стойности (месеци), за които Функция TRENDвръща очакваната стойност на зависимите променливи (брой елементи)

const - по избор. Булева стойност, която указва дали константата b трябва да бъде 0.

Например, фигурата показва функцията TREND, използвана за определяне на очаквания брой артикули върху тоалетна за баня за 16-ия месец.