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

Таблица 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приближава по-добре оригиналните данни.

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

Всичко изглежда страхотно в класациите. Червената линия е намерената линия 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 - координата на пресечната точка на линейната регресия с оста y.

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

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

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

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

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

y = c ln(x) + b

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

Изгражда се в съответствие с уравнението:

y = cxb

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

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

Изгражда се в съответствие с уравнението:

y=cebx

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На екрана ще се появи диалоговият прозорец Format Trendline (фиг. 3), съдържащ три раздела: View, Type, Parameters, като съдържанието на последните два напълно съвпада с подобни раздели на диалоговия прозорец Trendline (фиг. 1-2). В раздела Изглед можете да зададете вида на линията, нейния цвят и дебелина.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ТЕНДЕНЦИЯ;

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

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

LGRFPприбл.

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

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

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

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

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

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

Трябва да се отбележи, че авторите не са поставили за цел статията да представят хода на регресионния анализ с различна степен на пълнота. Основната му задача е да покаже на конкретни примери възможностите на пакета 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.

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

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

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

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

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

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

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

y=mx+b

чрез функциите SLOPE и INTERCEPT, като наклонът на регресията m се определя от функцията SLOPE, а константният член b - от функцията INTERCEPT.

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

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

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

с помощта на подобен метод се определя стойността на параметъра b в клетка D19. И съдържанието му ще изглежда така: = INTERCEPT(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 се използва в математиката, по-специално - в теорията на вероятностите и математическата статистика. Този метод има най-голямо приложение при проблеми с филтрирането, когато е необходимо да се отдели полезният сигнал от насложения върху него шум.

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

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

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

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

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

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

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

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

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

снимка:

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

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

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

Снимка: pixabay.com

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

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

Има много приложения, тъй като позволява приблизително представяне на дадена функция от други по-прости. 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, трябва да се даде предпочитание на тази, която има най-малката стойност на сумата e i във всички разглеждани точки. Не всичко обаче е толкова просто, тъй като наред с положителните отклонения на практика ще има отрицателни.

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

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

В Excel, както знаете, има вградена функция за автоматично събиране, която ви позволява да изчислявате стойностите на всички стойности, разположени в избрания диапазон. Така нищо няма да ни попречи да изчислим стойността на израза (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 \u003d 0.

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

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

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

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

Функция ПРОГНОЗА

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

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

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

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

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

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

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

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

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

a = y cf - bx cf

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

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

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

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

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

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

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

Функция TREND в Excel

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

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

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

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

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

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

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