Аз съм математик и програмист. Повечето голям скокв кариерата си постигнах, когато се научих да казвам: "Нищо не разбирам!"Сега не ме е срам да кажа на светилото на науката, че ми чете лекция, че не разбирам какво ми говори той, светилото. И е много трудно. Да, да признаеш невежеството си е трудно и неудобно. Кой обича да признава, че не знае основите на нещо? Поради професията си трябва да присъствам големи количествапрезентации и лекции, където, признавам си, в по-голямата част от случаите искам да спя, защото нищо не разбирам. Но не разбирам, защото огромният проблем на настоящата ситуация в науката се крие в математиката. Предполага се, че всички слушатели са запознати с абсолютно всички области на математиката (което е абсурдно). Признаването, че не знаете какво е производно (ще говорим за това какво е малко по-късно), е срамно.

Но се научих да казвам, че не знам какво е умножение. Да, не знам какво е подалгебра върху алгебра на Лъжа. Да, не знам защо са необходими в живота квадратни уравнения. Между другото, ако сте сигурни, че знаете, тогава имаме за какво да говорим! Математиката е поредица от трикове. Математиците се опитват да объркат и сплашат обществеността; където няма объркване, няма репутация, няма авторитет. Да, престижно е да се говори на възможно най-абстрактен език, което е пълна глупост.

Знаете ли какво е производно? Най-вероятно ще ми кажете за границата на съотношението на разликата. В първата година по математика и механика в Санкт Петербургския държавен университет Виктор Петрович Хавин ми каза определенпроизводна като коефициент на първия член от реда на Тейлър на функцията в точка (това беше отделна гимнастика за определяне на реда на Тейлър без производни). Дълго време се смях на това определение, докато накрая разбрах за какво става дума. Производната не е нищо повече от проста мярка за това колко подобна е функцията, която диференцираме, с функцията y=x, y=x^2, y=x^3.

Сега имам честта да изнасям лекции на студенти, които страхувам сематематика. Ако те е страх от математиката, ние сме на същия път. Щом се опитате да прочетете някакъв текст и ви се струва, че е прекалено сложен, знайте, че е лошо написан. Твърдя, че няма нито една област на математиката, която да не може да се обсъжда „на пръсти“, без да се губи точност.

Задача за близкото бъдеще: Възложих на моите ученици да разберат какво е линеен квадратичен регулатор. Не се срамувайте, отделете три минути от живота си и последвайте връзката. Ако не разбирате нещо, значи сме на същия път. И аз (професионален математик-програмист) нищо не разбрах. И ви уверявам, че можете да разберете това „на пръстите си“. включено в моментаНе знам какво е, но ви уверявам, че можем да го разберем.

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

И така, при дадени две точки (x0, y0), (x1, y1), например (1,1) и (3,2), задачата е да се намери уравнението на правата, минаваща през тези две точки:

илюстрация

Този ред трябва да има уравнение като следното:

Тук алфа и бета са неизвестни за нас, но две точки от тази линия са известни:

Можем да напишем това уравнение в матрична форма:

Тук трябва да направим едно лирично отклонение: какво е матрица? Матрицата не е нищо повече от двуизмерен масив. Това е начин за съхраняване на данни; не трябва да му се придават други значения. От нас зависи как точно да интерпретираме дадена матрица. Периодично ще го тълкувам като линейно картографиране, периодично като квадратна форма, а понякога просто като набор от вектори. Всичко това ще бъде изяснено в контекста.

Нека заменим конкретните матрици с тяхното символно представяне:

Тогава (алфа, бета) могат лесно да бъдат намерени:

По-конкретно за нашите предишни данни:

Което води до следното уравнение на правата, минаваща през точките (1,1) и (3,2):

Добре, тук всичко е ясно. Нека намерим уравнението на правата, минаваща през нея триточки: (x0,y0), (x1,y1) и (x2,y2):

О-о-о, но имаме три уравнения за две неизвестни! Един стандартен математик ще каже, че няма решение. Какво ще каже програмистът? И той първо ще пренапише предишната система от уравнения в следната форма:

В нашия случай вектори i,j,bтриизмерен, следователно (в общ случай) няма решение за тази система. Всеки вектор (алфа\*i + бета\*j) лежи в равнината, обхваната от векторите (i, j). Ако b не принадлежи на тази равнина, тогава няма решение (не може да се постигне равенство в уравнението). какво да правя Да потърсим компромис. Нека означим с e(алфа, бета)колко точно не сме постигнали равенство:

И ние ще се опитаме да минимизираме тази грешка:

Защо квадрат?

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

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

Илюстрация

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

АКТУАЛИЗАЦИЯ: Имам проблем тук, разстоянието до правата линия трябва да се измерва вертикално, а не ортогонална проекция. Този коментатор е прав.

Илюстрация

С напълно различни думи (внимателно, зле формализирани, но трябва да е ясно): вземаме всички възможни линии между всички двойки точки и търсим средната линия между всички:

Илюстрация

Друго обяснение е просто: прикрепяме пружина между всички точки от данни (тук имаме три) и правата линия, която търсим, и правата линия на равновесното състояние е точно това, което търсим.

Минимална квадратна форма

И така, даден е този вектор bи равнина, обхваната от колонните вектори на матрицата А(в този случай (x0,x1,x2) и (1,1,1)), ние търсим вектора дс минимална квадратна дължина. Очевидно минимумът е постижим само за вектора д, ортогонална на равнината, обхваната от колонните вектори на матрицата А:

С други думи, ние търсим вектор x=(алфа, бета), така че:

Нека ви напомня, че този вектор x=(алфа, бета) е минимумът квадратична функция||e(алфа, бета)||^2:

Тук би било полезно да запомните, че матрицата може да се интерпретира и като квадратна форма, например матрицата на идентичност ((1,0),(0,1)) може да се интерпретира като функция x^2 + y^ 2:

квадратна форма

Цялата тази гимнастика е известна под името линейна регресия.

Уравнение на Лаплас с гранично условие на Дирихле

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

Оригиналният ангажимент е наличен. Да се ​​минимизира външни зависимостиВзех кода на моя софтуерен рендер, който вече е на Хабре. За решаване линейна системаИзползвам OpenNL, той е отличен солвър, който обаче е много труден за инсталиране: трябва да копирате два файла (.h+.c) в папката с вашия проект. Цялото изглаждане се извършва със следния код:

За (int d=0; d<3; d++) { nlNewContext(); nlSolverParameteri(NL_NB_VARIABLES, verts.size()); nlSolverParameteri(NL_LEAST_SQUARES, NL_TRUE); nlBegin(NL_SYSTEM); nlBegin(NL_MATRIX); for (int i=0; i<(int)verts.size(); i++) { nlBegin(NL_ROW); nlCoefficient(i, 1); nlRightHandSide(verts[i][d]); nlEnd(NL_ROW); } for (unsigned int i=0; i&лице = лица[i];<3; j++) { nlBegin(NL_ROW); nlCoefficient(face[ j ], 1); nlCoefficient(face[(j+1)%3], -1); nlEnd(NL_ROW); } } nlEnd(NL_MATRIX); nlEnd(NL_SYSTEM); nlSolve(); for (int i=0; i<(int)verts.size(); i++) { verts[i][d] = nlGetVariable(i); } }

за (int j=0; j

Координатите X, Y и Z са разделими, изглаждам ги отделно. Тоест решавам три системи от линейни уравнения, всяка с брой променливи, равен на броя на върховете в моя модел. Първите n реда на матрица A имат само едно 1 на ред, а първите n реда на вектор b имат координатите на оригиналния модел. Тоест връзвам пружина между новата позиция на върха и старата позиция на върха - новите не трябва да се отдалечават много от старите.

Всички следващи редове на матрица A (faces.size()*3 = брой ръбове на всички триъгълници в мрежата) имат едно появяване на 1 и едно появяване на -1, като векторът b има нулеви противоположни компоненти. Това означава, че поставям пружина на всеки ръб на нашата триъгълна мрежа: всички ръбове се опитват да получат същия връх като тяхната начална и крайна точка.

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

Ето резултата:

Всичко би било наред, моделът наистина е изгладен, но се е отдалечил от първоначалния си ръб. Нека променим малко кода:<(int)verts.size(); i++) { float scale = border[i] ? 1000: 1; nlBegin(NL_ROW); nlCoefficient(i, scale); nlRightHandSide(scale*verts[i][d]); nlEnd(NL_ROW); }

За (int i=0; i

В нашата матрица A, за върховете, които са на ръба, добавям не ред от категорията v_i = verts[i][d], а 1000*v_i = 1000*verts[i][d]. Каква разлика прави това? И това променя нашата квадратична форма на грешка. Сега едно отклонение от върха на ръба ще струва не една единица, както преди, а 1000*1000 единици. Тоест, окачихме по-силна пружина на крайните върхове, решението ще предпочете да опъне останалите по-силно. Ето резултата:
Нека удвоим силата на пружината между върховете:

nlКоефициент(лице[j], 2);

nlКоефициент(лице[(j+1)%3], -2);

Логично е, че повърхността е станала по-гладка:

Уравнение на Поасон

Нека си спомним още едно готино име.

Да приемем, че имам изображение като това:

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

Ще разполовя снимката:

И ще избера стол с ръцете си:

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

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

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

Пример от живота

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

Имам няколко снимки на мостри на тъкани като тази:

Моята задача е да правя безпроблемни текстури от снимки с такова качество. За начало търся (автоматично) повтарящ се модел:

Ако изрежа този четириъгълник направо, тогава поради изкривяване ръбовете няма да се срещнат, ето пример за модел, повторен четири пъти:

Скрит текст

Ето фрагмент, където шевът е ясно видим:

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

Скрит текст

И ето модел, повторен четири пъти:

Скрит текст

И фрагмент от него, за да стане по-ясно:

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

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

Което намира най-широко приложение в различни области на науката и практическата дейност. Това може да бъде физика, химия, биология, икономика, социология, психология и така нататък, и така нататък. По волята на съдбата често ми се налага да се занимавам с икономиката и затова днес ще организирам за вас пътуване до една невероятна страна, наречена Иконометрия=) ...Как да не искаш?! Там е много добре - просто трябва да вземете решение! ...Но това, което вероятно определено искате, е да се научите как да решавате проблеми метод на най-малките квадрати. И особено прилежните читатели ще се научат да ги решават не само точно, но и МНОГО БЪРЗО ;-) Но първо общо изложение на проблема+ придружаващ пример:

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

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

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

Да предположим, че след извършване на наблюдения/експерименти/изчисления/танци с тамбура разполагаме с числени данни:

С магазините за хранителни стоки мисля, че всичко е ясно: - това е площта на 1-ви магазин, - годишният му оборот, - площта на 2-ри магазин, - годишният му оборот и т.н. Между другото, изобщо не е необходимо да имате достъп до класифицирани материали - доста точна оценка на търговския оборот може да се получи с помощта на математическа статистика. Но нека не се разсейваме, курсът по търговски шпионаж вече е платен =)

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

Нека отговорим на един важен въпрос: Колко точки са необходими за качествено изследване?

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

Казано много просто, трябва да изберем функция, графиккойто минава възможно най-близо до точките . Тази функция се нарича приближаващ (приближение - приближение)или теоретична функция . Най-общо казано, тук веднага се появява очевиден „претендент“ - полином от висока степен, чиято графика минава през ВСИЧКИ точки. Но тази опция е сложна и често просто неправилна. (тъй като графиката ще се „върти“ през цялото време и ще отразява слабо основната тенденция).

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


Как да оценим точността на това приближение? Нека изчислим и разликите (отклоненията) между експерименталните и функционалните стойности (изучаваме чертежа). Първата мисъл, която идва на ум, е да преценим колко голяма е сумата, но проблемът е, че разликите могат да бъдат отрицателни (Например, ) и отклоненията в резултат на такова сумиране ще се компенсират взаимно. Следователно, като оценка на точността на приближението, е необходимо да се вземе сумата модулиотклонения:

или свито: (ако някой не знае: – това е иконата на сумата и – спомагателна променлива „брояч“, която приема стойности от 1 до ).

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

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

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

И сега се връщаме към друг важен момент: както беше отбелязано по-горе, избраната функция трябва да е доста проста - но има и много такива функции: линеен , хиперболичен, експоненциален, логаритмичен, квадратна и т.н. И, разбира се, тук веднага бих искал да „намаля сферата на дейност“. Кой клас функции да избера за изследване? Примитивна, но ефективна техника:

– Най-лесният начин е да изобразите точки върху чертежа и анализирайте местоположението им. Ако те са склонни да се движат по права линия, тогава трябва да потърсите уравнение на права с оптимални стойности и . С други думи, задачата е да се намерят ТАКИВА коефициенти, така че сумата на квадратите на отклоненията да е най-малка.

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

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

И по същество трябва да решим стандартен проблем - намери минимална функция на две променливи.

Нека си спомним нашия пример: да предположим, че точките на „магазин“ обикновено са разположени в права линия и има всички основания да се смята, че линейна зависимостоборот от търговски площи. Нека намерим ТАКИВА коефициенти “a” и “be”, така че сумата от квадратите на отклоненията беше най-малкият. Всичко е както обикновено - първо Частични производни от 1-ви ред. Според правило за линейностМожете да разграничите точно под иконата за сума:

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

Нека създадем стандартна система:

Ние намаляваме всяко уравнение с „две“ и в допълнение „разбиваме“ сумите:

Забележка : независимо анализирайте защо „a“ и „be“ могат да бъдат извадени отвъд иконата за сума. Между другото, формално това може да стане със сумата

Нека пренапишем системата в „приложна“ форма:

след което алгоритъмът за решаване на нашия проблем започва да се появява:

Знаем ли координатите на точките? Ние знаем. суми можем ли да го намерим? Лесно. Нека направим най-простото система от две линейни уравнения с две неизвестни(„а“ и „бъди“). Решаваме системата, напр. Методът на Крамер, в резултат на което получаваме неподвижна точка. Проверка достатъчно условие за екстремум, можем да проверим, че в този момент функцията достига точно минимум. Проверката включва допълнителни изчисления и затова ще я оставим зад кулисите (при необходимост може да се види липсващата рамка). Правим окончателното заключение:

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

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

Ще анализирам само един проблем с „реални“ числа, тъй като в него няма трудности - всички изчисления са на нивото на училищната програма за 7-8 клас. В 95 процента от случаите ще бъдете помолени да намерите само линейна функция, но в самия край на статията ще покажа, че не е по-трудно да намерите уравненията на оптималната хипербола, експоненциалната и някои други функции.

Всъщност остава само да раздадете обещаните лакомства - за да се научите да решавате подобни примери не само точно, но и бързо. Ние внимателно изучаваме стандарта:

Задача

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

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

Моля, обърнете внимание, че значенията на „x“ са естествени и това има характерно смислово значение, за което ще говоря малко по-късно; но те, разбира се, могат да бъдат и дробни. Освен това, в зависимост от съдържанието на конкретна задача, стойностите на „X“ и „игра“ могат да бъдат напълно или частично отрицателни. Е, дадена ни е „безлична“ задача и започваме решение:

Намираме коефициентите на оптималната функция като решение на системата:

С цел по-компактен запис, променливата „брояч“ може да бъде пропусната, тъй като вече е ясно, че сумирането се извършва от 1 до .

По-удобно е да се изчислят необходимите количества в таблична форма:


Изчисленията могат да се извършват на микрокалкулатор, но е много по-добре да използвате Excel - както по-бързо, така и без грешки; вижте кратко видео:

Така получаваме следното система:

Тук можете да умножите второто уравнение по 3 и извадете 2-то от 1-вото уравнение член по член. Но това е късмет - на практика системите често не са подарък и в такива случаи спестява Методът на Крамер:
, което означава, че системата има уникално решение.

Да проверим. Разбирам, че не искате, но защо пропускате грешки, когато те абсолютно не могат да бъдат пропуснати? Нека заместим намереното решение в лявата част на всяко уравнение на системата:

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

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

За разлика от директен зависимост на оборота на магазина от неговата площ, установената зависимост е обратен (принцип "колкото повече, толкова по-малко"), и този факт веднага се разкрива от негатива наклон. функция ни казва, че с увеличаване на определен показател с 1 единица, стойността на зависимия показател намалява среднос 0,65 единици. Както се казва, колкото по-висока е цената на елдата, толкова по-малко се продава.

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

и изпълнете чертежа:


Построената права се нарича тренд линия (а именно линейна линия на тенденция, т.е. в общия случай тенденцията не е непременно права линия). Всеки е запознат с израза „да бъдеш в тенденция“ и смятам, че този термин не се нуждае от допълнителни коментари.

Нека изчислим сумата на квадратите на отклоненията между емпирични и теоретични стойности. Геометрично това е сумата от квадратите на дължините на сегментите „малина“. (две от които са толкова малки, че дори не се виждат).

Нека обобщим изчисленията в таблица:


Отново, те могат да бъдат направени ръчно за всеки случай, ще дам пример за 1-ва точка:

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

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

Нека намерим съответната сума от квадратни отклонения - за да ги различим, ще ги обознача с буквата "епсилон". Техниката е абсолютно същата:


И отново, за всеки случай, изчисления за 1-ва точка:

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

Заключение: , което означава, че експоненциалната функция приближава експерименталните точки по-лошо от права линия .

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

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

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

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

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

Най-често се използва линейна форма за описание на статистическата връзка на характеристиките. Фокусът върху линейните зависимости се обяснява с ясната икономическа интерпретация на неговите параметри, ограничената вариация на променливите и факта, че в повечето случаи нелинейните форми на зависимости се преобразуват (чрез логаритъм или заместване на променливи) в линейна форма за извършване на изчисления .
В случай на линейна връзка по двойки, регресионното уравнение ще приеме формата: y i =a+b·x i +u i . Параметрите a и b на това уравнение се оценяват от данните от статистическите наблюдения x и y. Резултатът от такава оценка е уравнението: , където , са оценки на параметри a и b, е стойността на резултантния атрибут (променлива), получена от регресионното уравнение (изчислена стойност).

Най-често се използва за оценка на параметри метод на най-малките квадрати (LSM).
Методът на най-малките квадрати предоставя най-добрите (последователни, ефективни и безпристрастни) оценки на параметрите на регресионното уравнение. Но само ако са изпълнени определени допускания по отношение на случайния член (u) и независимата променлива (x) (вижте допусканията на OLS).

Проблемът за оценяване на параметрите на уравнение на линейна двойка с помощта на метода на най-малките квадратие както следва: да се получат такива оценки на параметрите , , при които сумата от квадратните отклонения на действителните стойности на резултантната характеристика - y i от изчислените стойности - е минимална.
Формално OLS критерийможе да се напише така: .

Класификация на методите на най-малките квадрати

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

Нека илюстрираме идеята класически метод на най-малките квадрати графично. За да направим това, ще изградим диаграма на разсейване въз основа на данни от наблюдения (x i, y i, i=1;n) в правоъгълна координатна система (такава диаграма на разсейване се нарича корелационно поле). Нека се опитаме да изберем права линия, която е най-близо до точките на корелационното поле. По метода на най-малките квадрати линията се избира така, че сумата от квадратите на вертикалните разстояния между точките на корелационното поле и тази линия да е минимална.

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

Правилността на изчислението на параметрите на регресионното уравнение може да се провери чрез сравняване на сумите (може да има известно несъответствие поради закръгляване на изчисленията).
За да изчислите оценките на параметрите, можете да съставите таблица 1.
Знакът на регресионния коефициент b показва посоката на връзката (ако b >0, връзката е пряка, ако b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Формално, стойността на параметър a е средната стойност на y с x равно на нула. Ако атрибут-факторът няма и не може да има нулева стойност, тогава горната интерпретация на параметър a няма смисъл.

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

За да оцените качеството на полученото регресионно уравнение, изчислете теоретичния коефициент на детерминация - R 2 yx:

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

Пример.

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

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

Използване метод на най-малките квадрати, апроксимирайте тези данни чрез линейна зависимост 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 винаги може да се покаже на диаграмата.

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

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

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

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

Същите действия могат лесно да бъдат изпълнени чрез преместване на показалеца на мишката върху графиката, съответстваща на една от сериите данни, и щракване с десния бутон; В контекстното меню, което се показва, изберете командата Добавяне на линия на тенденция. Диалоговият прозорец Trend Line ще се появи на екрана с отворен раздел 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 г.

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

Следвайки методологията, дадена при решаването на задача 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 показва таблицата в режим на показване на формула.

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

Проблем 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);

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

2 С помощта на манипулатора за попълване копирайте тази формула в диапазона от клетки C4:C17. Получаваме необходимите серии от данни (фиг. 12). Поради факта, че броят на заявките е цяло число, трябва да зададете числовия формат с броя на десетичните знаци на 0 в раздела Число на прозореца Формат на клетката.

y = mx+b

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

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

За да направите това:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чрез конструиране на интерполационен полином от n степен, който преминава директно през всички точкидаден масив от данни. В този случай апроксимиращата функция се представя под формата на: интерполационен полином във форма на Лагранж или интерполационен полином във форма на Нютон.

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

Метод на най-малките квадрати(в англоезичната литература Ordinary Least Squares, OLS) е математически метод, основан на определяне на апроксимираща функция, която се конструира в най-близка близост до точки от даден масив от експериментални данни. Близостта на оригиналната и апроксимиращата функция F(x) се определя от числена мярка, а именно: сумата от квадратите на отклоненията на експерименталните данни от апроксимиращата крива F(x) трябва да бъде най-малката.

Апроксимираща крива, конструирана чрез метода на най-малките квадрати

Използва се методът на най-малките квадрати:

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

Да намери решение в случай на обикновени (не свръхопределени) нелинейни системи от уравнения;

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

Апроксимиращата функция с помощта на метода на най-малките квадрати се определя от условието за минималната сума на квадратите на отклоненията на изчислената апроксимираща функция от даден масив от експериментални данни. Този критерий на метода на най-малките квадрати се записва като следния израз:

Стойностите на изчислената апроксимираща функция в възловите точки,

Даден масив от експериментални данни в възлови точки.

Квадратният критерий има редица „добри“ свойства, като например диференцируемост, предоставяйки уникално решение на проблема с приближението с полиномиални апроксимиращи функции.

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

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

∙ Ако степента на апроксимиращата функция е m=1, тогава апроксимираме табличната функция с права линия (линейна регресия).

∙ Ако степента на апроксимиращата функция е m=2, тогава апроксимираме табличната функция с квадратна парабола (квадратично приближение).

∙ Ако степента на апроксимиращата функция е m=3, тогава апроксимираме табличната функция с кубична парабола (кубична апроксимация).

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

- неизвестни коефициенти на апроксимиращия полином от степен m;

Броят на зададените стойности на таблицата.

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

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

Тази система от линейни алгебрични изрази може да бъде пренаписана в матрична форма:

В резултат се получава система от линейни уравнения с размерност m+1, която се състои от m+1 неизвестни. Тази система може да бъде решена с помощта на всеки метод за решаване на линейни алгебрични уравнения (например методът на Гаус). В резултат на решението ще бъдат намерени неизвестни параметри на апроксимиращата функция, които осигуряват минималната сума на квадратите на отклоненията на апроксимиращата функция от оригиналните данни, т.е. най-доброто възможно квадратично приближение. Трябва да се помни, че ако дори една стойност на изходните данни се промени, всички коефициенти ще променят стойностите си, тъй като те са напълно определени от изходните данни.

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

(линейна регресия)

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

Координати на възлите на таблицата;

Неизвестни коефициенти на апроксимиращата функция, която е зададена като линейна зависимост.

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

Нека трансформираме получената линейна система от уравнения.

Решаваме получената система от линейни уравнения. Коефициентите на апроксимиращата функция в аналитична форма се определят, както следва (метод на Крамер):

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

Алгоритъм за прилагане на метода на най-малките квадрати

1. Изходни данни:

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

Посочва се степента на апроксимиращия полином (m).

2. Алгоритъм за изчисление:

2.1. Определят се коефициентите за построяване на система от уравнения с размерности

Коефициенти на системата от уравнения (лявата страна на уравнението)

- индекс на номера на колоната на квадратната матрица на системата от уравнения

Свободни членове на система от линейни уравнения (дясната страна на уравнението)

- индекс на номера на реда на квадратната матрица на системата от уравнения

2.2. Образуване на система от линейни уравнения с размерност .

2.3. Решаване на система от линейни уравнения за определяне на неизвестните коефициенти на апроксимиращ полином от степен m.

2.4 Определяне на сумата от квадратите на отклоненията на апроксимиращия полином от първоначалните стойности във всички възлови точки

Намерената стойност на сумата от квадратите на отклоненията е минималната възможна.

Апроксимация с помощта на други функции

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

Логаритмично приближение

Нека разгледаме случая, когато апроксимиращата функция е дадена от логаритмична функция от формата: