Метод на най-малките квадрати и търсене в Excel. Прилагане на метода на най-малките квадрати в изчисление на Excel от метода на XLS квадрати

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

Как да активирате добавката "Търсене на решение"

Първо ще разберем как да включим тази надстройка.

1. Отидете в менюто "Файл" и изберете елемента на настройките на Excel.

2. В прозореца, който се появява, изберете "Решение за решение" и кликнете върху "Go".

3. В следващия прозорец поставяме отметка срещу елемента "Решение за търсене" и щракнете върху OK.

4. Добавките се активира - сега може да се намери в елемента "Данни".

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

Сега накратко О. метод на най-малките квадрати (MNC) И за това къде може да се приложи.

Да предположим, че имаме набор от данни, след като направим някакъв експеримент, където изучавахме ефектите от стойностите на стойността на Y.

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

Ще взема супер-прост пример (виж фиг.).

Ясно е, че точките са разположени един за друг като по права линия и затова смело приемаме, че нашата зависимост е описана от линейната функция y \u003d kx + b. В същото време, ние именно сме сигурни, че с X е равен на нула, стойността на Y също е нула. Следователно функцията, описваща зависимостта, ще бъде още по-лесна: Y \u003d KX (запомни училищната програма).

Като цяло трябва да намерим коефициент k. Това ще направим с това Манастир с използването на допълнително решение за решения.

Методът е, че (тук - внимание: трябва да помислите) сумата на квадратите на разликата в експериментално получените и съответните изчислителни стойности бяха минимални. Това е, когато X1 \u003d 1, действително измерената стойност Y1 \u003d 4.6, и оценката Y1 \u003d F (X1) е 4, квадратът на разликата ще (Y1-Y1) ^ 2 \u003d (4-4.6) ^ 2 \u003d 0.36. Със по същия начин: когато x2 \u003d 2, действително измерената стойност Y2 \u003d 8.1 и изчисленият U2 е 8, квадратът на разликата ще бъде (Y2-Y2) ^ 2 \u003d (8-8,1) ^ 2 \u003d 0.01. И сумата от всички тези квадрати трябва да бъде възможно най-малко.

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

Търсене в приложението

1. Ако не сте включили добавката "Търсене на решение", тогава се връщаме към елемента Как да активирате разтвора "Решение" и включване 🙂

2. В клетката А1 въвеждаме стойността "1". Това устройство ще бъде първото приближение към реалната стойност на коефициента (k) на нашата функционална зависимост y \u003d kx.

3. В колоната Б, ние уредихме стойностите на параметъра X, в колоната с C колона - стойностите на параметъра y. В колоните клетки въвеждаме формулата: "Компютърът K, умножена до x стойност. Например, в клетката D1, ние въвеждаме "\u003d A1 * B1", в клетката D2 влизаме "\u003d A1 * B2" и т.н.

4. Ние вярваме, че коефициентът k е равен на един и функцията f (x) \u003d y \u003d 1 * x е първото приближение към нашето решение. Можем да изчислим сумата на квадратите на разликите между измерените стойности на стойността на Y и изчислени съгласно формулата y \u003d 1 * x. Можем да направим всичко това ръчно, задвижвано във формулата съответните връзки към клетките: "\u003d (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... и т.н. Краят, който сме сгрешили и разбираме, че са загубили купчина време. В Excel има специална формула за изчисляване на сумата от квадрати на разликите, които ще бъдат въведени в A2 клетка и ще зададете източника на данни: диапазонът на измерените Стойностите y (колона в) и обхват на изчислените стойности y (колона г).

4. Сумата от разликата в квадратите е изчислена - сега отиваме в раздела "Данни" и да изберем "Търсене на решения".

5. В менюто, което се появява като променлива клетка, изберете клетката A1 (тази с K коефициент).

6. Като цел изберете A2 клетката и задайте условието за "установяване на еднаква минимална стойност". Не забравяйте, че това е клетка, в която правим изчисляване на сумата на квадратите на разликите между изчислените и измерените стойности, а сумата трябва да бъде минимална. Кликнете върху "Run".

7. Избран е коефициентът k. Сега можете да се уверите, че изчислените стойности са много близки до измерените.

P.S.

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

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

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

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

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

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

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

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

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

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

Илюстрираме същността класически най-малък квадратен метод графично. За да направите това, ние изграждаме график на точка според наблюденията (x I, Y i, i \u003d 1; n) в правоъгълна координатна система (такава точка с точка се нарича поле за корелация). Ще се опитаме да изберем права линия, която е най-близо до точките на полето за корелация. Съгласно метода на най-малките квадрати, линията е избрана така, че сумата на квадратите на вертикалните разстояния между точките на съответното поле и тази линия би било минимално.

Математически запис на тази задача: .
Стойностите на Y I и X I \u003d 1 са известни на нас, това са наблюдателни данни. В функцията S, те са константи. Променливи в тази функция са желаните оценки на параметрите - ,. За да намерите минимум 2-променливи функции, е необходимо да се изчислят частните деривати на тази функция за всеки от параметрите и да им приравнят нула, т.е. .
В резултат на това получаваме система от 2 нормални линейни уравнения:
Решаване на тази система, ние ще намерим желаните оценки на параметрите:

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

Оценка на стягане на комуникацията между знаците Провежда се с коефициента на корелация на линейната двойка - R x, y. Тя може да бъде изчислена по формулата: . В допълнение, коефициентът на линейна корелация на сдвояване може да се определи чрез регресионния коефициент b: .
Площта на допустимите стойности на линейния коефициент на двойката корелация от -1 до +1. Знакът за коефициент на корелация показва посоката на комуникация. Ако R x, Y\u003e 0, тогава връзката е права; Ако r x, y<0, то связь обратная.
Ако този коефициент е близо до един, връзката между характеристиките може да се интерпретира като доста тясна линейна. Ако неговият модул е \u200b\u200bравен на единица ê R x, y ê \u003d 1, връзката между знаците е функционална линейна. Ако знаците x и y са линейно независими, след това R x, Y е близо до 0.
За изчисляване на R X, Y може да използва таблица 1.

За оценка на качеството на полученото уравнение на регресия се изчислява теоретичният коефициент на определяне - R2 YX:

,
където D2 е разпръскването на Y; обяснено от уравнението на регресията;
е 2 - остатъчна (необяснима регресионна уравнение) дисперсия y;
s 2 Y е общо (пълна) дисперсия Y.
Коефициентът на определяне характеризира съотношението на вариация (дисперсия) на ефективния знак Y, обяснен с регресията (и следователно, факторът x), в общото изменение (дисперсия) y. Коефициентът на определяне R2 YX зависи от 0 до 1. Съответно, стойността на 1-R2 YX характеризира фракцията на дисперсията y, причинена от влиянието на други неизвестни фактори в модела и грешките на спецификацията.
С сдвоена линейна регресия R2 YX \u003d R2 YX.

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

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

Linene(Verse_y.; Verse_x.; Конста; статистика),

Verse_y. - Масив от y стойности

Verse_x.- Незадължителен масив от стойности х.Ако масив х. пропуснати, след това се приема, че този масив (1; 2; 3; ...) от същия размер като Verse_y.,

Конста- логическа стойност, която показва дали е необходимо да се константира б. е равен на 0. Ако Конста има значението ВЯРНО или пропуснати тогава б. Той се изчислява по обичайния начин. Ако аргументът Конста Тогава има значение б. разчита равен на 0 и стойности а. избрано, така че съотношението y \u003d ax.

Статистика- логическа стойност, която показва дали да се върне допълнителна регресионна статистика. Ако аргументът Статистика има значението ВЯРНО, след това функция Linene Връща допълнителна статистика за регресия. Ако аргументът Статистика има значението Фалшив или пропуснати, след това функцията Linene Връща само коефициента а. И константа б..

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

За изчисление коефициент на корелация Използвана функция

Корнел(Massive1.;Massive2.),

връщащи се стойности на коефициента на корелация, където Massive1. - Масив от ценности y., Massive2. - Масив от ценности х.. Massive1. и Massive2. Трябва да бъде едно измерение.

Пример 1.. Пристрастяване y.(х.) Представени в таблицата. Build. регресионна линия и изчисли коефициент на корелация.

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

Ние въвеждаме таблицата на стойностите на листа от MS Excel и изграждаме график на точка. Работният списък ще бъде представен на изображението на фиг. 2.

За да се изчислят стойностите на регресионните коефициенти нои б.акценти A7: B7, Обърнете се към капитана на функциите и в категорията Статистически Изберете функция Linene. Попълнете диалоговия прозорец Opped, както е показано на фиг. 3 и кликнете ДОБРЕ..

В резултат на това изчислената стойност ще се появи само в клетката A6. (Фиг.4). За да се появи стойността в клетката B6. Трябва да въведете режим за редактиране (ключ F2)и след това натиснете клавишната комбинация Ctrl + Shift + Enter.

Да се \u200b\u200bизчисли стойността на коефициента на корелация в клетката C6. Въведена е следната формула:

C7 \u003d Corvela (B3: J3; B2: J2).

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

B5 \u003d $ 7 * B2 + $ B $ 7

и го копирайте в диапазона C5: J5.(Фиг. 5).

Ще покажа регресионната линия в диаграмата. Маркирайте експерименталните точки на графиката, щракнете с десния бутон и изберете командата Първоначални данни. В диалоговия прозорец, който се появява (фиг. 5), изберете раздела Ред и кликнете върху бутона Добави. Попълнете полетата за въвеждане, както е показано на фиг. 6 и натиснете бутона Добре. Регресионната линия ще бъде добавена към експерименталната графика на данните. По подразбиране нейният график ще бъде изобразен под формата на точки, които не са свързани чрез изглаждане на линии.

Фиг. 6.

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

Тип линия, неговият цвят и дебелина може да се промени, както следва. Изберете реда на диаграмата, щракнете с десния бутон на мишката и изберете командата в контекстното меню Формат на серия данни ... След това направете инсталации, например, както е показано на фиг. осем.

В резултат на всички трансформации получаваме графика на експериментални данни и регресионна линия в една графична област (фиг. 9).

4.2. Използване на линията за тенденция.

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

Пример 2.. В резултат на експеримента се определя някаква таблична зависимост.

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

Изберете и изградете приблизително пристрастяване. Изградете графични таблични и избрани аналитична зависимост.

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

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

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

В първия раздел (Фиг. 11) от този прозорец е показан видът на приближаване на зависимостта.

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

· Името на приблизителната зависимост;

· Прогноза напред (назад) на н. единици (този параметър определя кой броят на предстоящите единици (обратно) трябва да разшири линията на тенденцията);

· Покажете дали точка на пресичане на кривата с права линия y \u003d const.;

· Показване на приблизителна функция на диаграма или не (параметър за показване на уравнението на диаграмата);

· Има ли диаграмата на стандартното отклонение или не (параметърът е поставен върху диаграмата, стойността на точността на сближаването).

Ние избираме полином от втора степен като приблизителна зависимост (фиг. 11) и отстранете уравнението, описващо този полином върху диаграмата (фиг. 12). Получената диаграма е представена на фиг. 13.

Подобно с помощта тендни линии Можете да изберете параметрите на такива зависимости като

· Линейни y.=a. X.+б.,

· Логаритмик y.=a ∙ ln.(х.)+б.,

· Експоненциален y.=a ∙ e b,

· Мощност y.=a ∙ x b,

· Полином y.=a. X. 2 +b ∙ X.+° С., y.=a. X. 3 +b ∙ X. 2 +c ∙ x + d И така нататък, до 6-та степен полином, включително,

· Линейно филтриране.

4.3. Използване на решаващ блок

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

Пример 3.. В резултат на експеримента, зависимостта z (t), представена в таблицата

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

Вземете коефициентите на пристрастяване Z (t) \u003d при 4 + bt 3 + ct2 + dt + k Метода на най-малките квадрати.

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

Помислете за процеса на решаване на проблема с оптимизацията (фиг. 14).

Нека значенията НО, В, От, Д. и ДА СЕ Съхранени в клетки A7: E7.. Изчислете теоретичните стойности на функцията Z.(t.)=При 4 + Bt 3 + CT 2 + DT + K За определен t.(B2: J2.). Да направите това в клетката B4. Въвеждаме стойността на функцията в първата точка (клетка B2.):

B4 \u003d $ $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ c $ 7 * B2 ^ 2 + $ d $ 7 * B2 + $ e $ 7.

Копирайте тази формула в диапазона C4: J4. и получаваме очакваната стойност на функцията в точки, абдсценките на които се съхраняват в клетките B2: J2..

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

B5 \u003d (B4-B3) ^ 2,

и го копирайте в диапазона C5: J5.. В клетка. F7. Ще съхраняваме обща квадратична грешка (10). За да направите това, въвеждаме формулата:

F7 \u003d Суми (B5: J5).

Ние използваме екипа Service®Poisk решения и решаване на проблема с оптимизацията без ограничения. Попълнете съответните полета за въвеждане в диалоговия прозорец, показан на фиг. 14 и натиснете бутона Изпълнител. Ако се намери решението, прозорецът, показан на фиг. петнадесет.

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

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

Фиг. 17 показва работния списък на MS Excel след извършените изчисления.

5. Списък на препратките

1. Alekseev E.R., Chesnokova O.v., решаване на проблеми на компютърната математика в пакети MathCAD12, MATLAB7, Maple9. - NT Press, 2006.-596c. : I Л. - (настойнически)

2. Alekseev E.R., CHESNOKOVA O.V., E.A. Ръдънко, Scilab, решаване на инженерни и математически задачи. -M., Binom, 2008.-260s.

3. Berezin I.S., Lodine n.p., методи на изчисления. - m.: Наука, 1966.-632в.

4. Garnaev a.yu., използвайки г-жа Excel и VBA в икономиката и финансите. - SPB.: BHV - Петербург, 1999.-332в.

5. DEMIDOVICH B.P., MARRON и A., Shuvalova v.z., цифрови методи за анализ. - m.: Наука, 1967.-368С.

6. царевица Г., Корн Т., указател по математика за учени и инженери. - m., 1970, 720s.

7. Alekseev E.R., Chesnokova O.v. Методически инструкции за извършване на лабораторна работа в MS Excel. За студенти от всички специалитети. Донецк, донту, 2004. 112 стр.

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

Задаване на задачата за конкретен пример

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

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

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

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

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

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

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

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

Разбира се, е възможно да се използва полином от висока степен, но тази опция е не само по-трудна, но е просто неправилна, тъй като тя няма да отразява основната тенденция, че е необходимо да се открие. Най-разумното решение е търсенето на директно Y \u003d AX + B, което най-добре носи експерименталните данни и по-точно, коефициентите - А и Б.

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

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

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

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

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

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

В математическия запис той има формата:

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

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

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

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

Решаването му, например, от устройството, получаваме неподвижна точка с някои коефициенти A * и B *. Това е минимум, т.е. за предсказване, коя търговия ще бъде в магазина в определена област, правилната линия y \u003d A * X + B * е подходяща, която е регресионен модел за пример, който се обсъжда. Разбира се, тя няма да позволи да се намери точен резултат, но ще ви помогне да получите представа дали ще бъде закупуването на конкретна зона.

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

В "Excel" има функция за изчисляване на стойността на МНК. Той има следната форма: "trend" (известна. Стойности y; известни. X стойности; нови стойности x; const.). Нанесете формулата за изчисляване на MNC в Excel на нашата маса.

За да направите това, в клетката, в която резултатът от изчисляването на по-малките квадратчета трябва да се покаже в Excel, ние въвеждаме знака "\u003d" и да изберем функцията "trend". В отворения прозорец попълнете съответните полета, маркиране:

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

В допълнение, формулата има логическа променлива "const". Ако влезете в съответното поле 1, това ще означава, че трябва да се направят изчисленията, като вярват, че B \u003d 0.

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

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

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

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

Predicz функция

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

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