A legkisebb négyzetek módszere (OLS) a regresszióanalízis területéhez tartozik. Számos alkalmazása van, mivel lehetővé teszi egy adott függvény közelítő ábrázolását más egyszerűbbekkel. Az LSM rendkívül hasznos lehet a megfigyelések feldolgozásában, és aktívan használják bizonyos mennyiségek becslésére más véletlenszerű hibákat tartalmazó mérési eredmények alapján. Ebből a cikkből megtudhatja, hogyan lehet a legkisebb négyzetek számításait végrehajtani az Excelben.
A probléma megfogalmazása konkrét példán keresztül
Tegyük fel, hogy két mutató van X és Y. Sőt, Y függ X-től. Mivel az OLS a regresszióanalízis szempontjából érdekel minket (az Excelben a metódusait beépített függvényekkel valósítják meg), azonnal át kell térnünk egy konkrét probléma.
Tehát legyen X egy élelmiszerbolt üzlethelyisége négyzetméterben, Y pedig az éves forgalom, millió rubelben.
Előrejelzést kell készíteni, hogy mekkora (Y) forgalma lesz az üzletnek, ha van ilyen vagy olyan üzlethelyisége. Nyilvánvalóan az Y = f (X) függvény növekszik, hiszen a hipermarket több árut ad el, mint a bódé.
Néhány szó az előrejelzéshez használt kiindulási adatok helyességéről
Tegyük fel, hogy van egy táblánk, amely n bolt adataiból készült.
A matematikai statisztikák szerint az eredmények többé-kevésbé helyesek, ha legalább 5-6 objektum adatait megvizsgáljuk. Ezenkívül „rendellenes” eredmények nem használhatók. Különösen egy elit kis butik forgalma többszöröse lehet a „masmarket” osztályba tartozó nagy kiskereskedelmi egységek forgalmának.
A módszer lényege
A táblázat adatai derékszögű síkon ábrázolhatók M 1 (x 1, y 1), ... M n (x n, y n) pontok formájában. Most a feladat megoldása egy y = f (x) közelítő függvény kiválasztására lesz redukálva, amelynek gráfja a lehető legközelebb megy át az M 1, M 2, .. M n pontokhoz.
Természetesen polinomot is használhat magas fokozat, de ezt a lehetőséget nemcsak nehéz megvalósítani, hanem egyszerűen helytelen is, mivel nem tükrözi a fő trendet, amelyet észlelni kell. A legésszerűbb megoldás az y = ax + b egyenes keresése, amely a legjobban közelíti a kísérleti adatokat, pontosabban az a és b együtthatót.
Pontosság értékelése
Bármilyen közelítés esetén a pontosságának értékelése különösen fontos. Jelöljük e i-vel az x i pont funkcionális és kísérleti értékei közötti különbséget (eltérést), azaz e i = y i - f (x i).
Nyilvánvaló, hogy a közelítés pontosságának értékeléséhez használhatja az eltérések összegét, azaz amikor egyenest választ X X Y-tól való függésének hozzávetőleges ábrázolásához, előnyben kell részesítenie azt, amelyik a legkisebb értékkel rendelkezik. összege e i minden vizsgált pontban. Azonban nem minden olyan egyszerű, mivel a pozitív eltérések mellett negatívak is lesznek.
A probléma megoldható eltérési modulok vagy azok négyzetei segítségével. Az utolsó módszer a legszélesebb körben alkalmazott. Számos területen használják, beleértve a regressziós elemzést (Excelben két beépített függvény segítségével), és régóta bizonyította hatékonyságát.
Legkisebb négyzet alakú módszer
Az Excel, mint tudod, rendelkezik egy beépített AutoSum funkcióval, amely lehetővé teszi a kiválasztott tartományban található összes érték értékének kiszámítását. Így semmi sem akadályoz meg bennünket abban, hogy kiszámoljuk a kifejezés értékét (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
Matematikai jelöléssel ez így néz ki:
Mivel eredetileg úgy döntöttünk, hogy egy egyenest közelítünk, a következőt kaptuk: 
Így az X és Y mennyiségek fajlagos függőségét legjobban leíró egyenes megtalálásának feladata két változó függvényének minimumának kiszámítása:
Ehhez az új a és b változók parciális deriváltjait nullával kell egyenlővé tenni, és meg kell oldani egy primitív rendszert, amely két egyenletből áll, és két ismeretlen alakú:
Néhány egyszerű átalakítás után, beleértve a 2-vel való osztást és az összegek manipulálását, a következőket kapjuk:
Megoldásában például Cramer módszerével egy stacionárius pontot kapunk bizonyos a * és b * együtthatókkal. Ez a minimum, vagyis annak előrejelzésére, hogy egy üzlet mekkora forgalmat bonyolít le egy adott területen, alkalmas az y = a * x + b * egyenes, amely egy regressziós modell a szóban forgó példában. Természetesen ez nem teszi lehetővé a pontos eredmény megtalálását, de segít abban, hogy képet kapjon arról, hogy kifizetődik-e egy adott terület bolti hitelből történő vásárlása.
A legkisebb négyzetek implementálása az Excelben
Az Excelnek van egy funkciója az értékek legkisebb négyzetek használatával történő kiszámítására. Ennek a következő formája van: „TREND” (ismert Y értékek; ismert X értékek; új X értékek; állandó). Alkalmazzuk táblázatunkra az Excelben az OLS-számítás képletét.
Ehhez írja be a „=” jelet abba a cellába, amelyben az Excel legkisebb négyzetek módszerével végzett számítás eredményét meg kell jeleníteni, és válassza ki a „TREND” funkciót. A megnyíló ablakban töltse ki a megfelelő mezőket, kiemelve:
- az Y ismert értékeinek tartománya (ebben az esetben a kereskedelmi forgalom adatai);
- tartomány x 1 , …x n , azaz az üzlethelyiség mérete;
- x ismert és ismeretlen értékei, amelyekhez meg kell találnia a forgalom nagyságát (a munkalapon való elhelyezkedésükről lásd alább).
Ezenkívül a képlet tartalmazza a „Const” logikai változót. Ha 1-et ír be a megfelelő mezőbe, ez azt jelenti, hogy el kell végeznie a számításokat, feltételezve, hogy b = 0.
Ha egynél több x értékre kell megtudnia az előrejelzést, akkor a képlet beírása után ne nyomja meg az „Enter” gombot, hanem a „Shift” + „Control” + „Enter” kombinációt kell begépelnie a billentyűzeten.
Néhány funkció
A regressziós elemzés még a bábuk számára is elérhető. Az ismeretlen változókból álló tömb értékének előrejelzésére szolgáló Excel-képletet – TREND – azok is használhatják, akik még soha nem hallottak a legkisebb négyzetekről. Elég csak ismerni a munkájának néhány jellemzőjét. Különösen:
- Ha az y változó ismert értékeinek tartományát egy sorban vagy oszlopban rendezi el, akkor a program minden ismert x értékkel rendelkező sort (oszlopot) külön változóként érzékel.
- Ha a TREND ablakban nincs megadva ismert x-szel rendelkező tartomány, akkor a függvény Excelben történő használatakor a program egész számokból álló tömbként kezeli, amelynek száma megfelel a megadott értékekkel rendelkező tartománynak. y változó.
- A „megjósolt” értékek tömbjének kiadásához a trend kiszámításához használt kifejezést tömbképletként kell megadni.
- Ha nincs megadva x új értéke, akkor a TREND függvény egyenlőnek tekinti azokat az ismertekkel. Ha nincsenek megadva, akkor az 1. tömböt veszi argumentumnak; 2; 3; 4;…, amely arányos a már megadott y paraméterek tartományával.
- Az új x értékeket tartalmazó tartománynak ugyanannyi vagy több sorból vagy oszlopból kell állnia, mint az adott y értékeket tartalmazó tartománynak. Más szóval, arányosnak kell lennie a független változókkal.
- Egy ismert x értékkel rendelkező tömb több változót is tartalmazhat. Ha azonban csak egyről beszélünk, akkor szükséges, hogy a megadott x és y értékekkel arányos tartományok legyenek. Több változó esetén szükséges, hogy a megadott y értékekkel rendelkező tartomány egy oszlopba vagy egy sorban elférjen.
PREDICTION funkció
Az Excel regressziós elemzése több függvény segítségével valósul meg. Az egyik az úgynevezett „PREDICTION”. Hasonló a „TREND”-hez, azaz a legkisebb négyzetek módszerével végzett számítások eredményét adja meg. Azonban csak egy X-re, amelyre Y értéke ismeretlen.
Most már ismer olyan képleteket az Excelben, amelyek lehetővé teszik egy adott mutató jövőbeli értékének előrejelzését egy lineáris trend szerint.
A legkisebb négyzetek módszere egy matematikai eljárás egy olyan lineáris egyenlet megalkotására, amely a legpontosabban illeszkedik két számsorból álló halmazra. A módszer használatának célja a teljes négyzetes hiba minimalizálása. Az Excel rendelkezik olyan eszközökkel, amelyek segítségével ezt a módszert alkalmazhatja a számításokhoz. Találjuk ki, hogyan történik ez.
· A módszer használata Excelben
o A „Megoldáskeresés” kiegészítő engedélyezése
o Problémakörülmények
o Megoldás
A módszer használata Excelben
A legkisebb négyzetek módszere (LSM) az egyik változó egy másiktól való függésének matematikai leírása. Előrejelzésre használható.
A Megoldás keresése bővítmény engedélyezése
Az MNC Excelben való használatához engedélyeznie kell a bővítményt "Megoldást találni", amely alapértelmezés szerint le van tiltva.
1. Lépjen a lapra "Fájl".
2. Kattintson a szakasz nevére "Lehetőségek".
3. A megnyíló ablakban válassza ki az alszakaszt "Kiegészítők".
4. A blokkban "Ellenőrzés", amely az ablak alján található, állítsa a kapcsolót állásba "Excel bővítmények"(ha más az értéke), és kattintson a gombra "Megy...".
5. Kinyílik egy kis ablak. A paraméter mellé pipát teszünk "Megoldást találni". Kattintson a gombra "RENDBEN".
Most a funkció Megoldás keresése az Excelben aktiválva van, és eszközei megjelennek a szalagon.
Lecke: Megoldás keresése Excelben
A probléma körülményei
Egy konkrét példán keresztül írjuk le az LSM használatát. Két számsorunk van xÉs y, melynek sorrendje az alábbi képen látható.
Ez a függőség a legpontosabban a következő függvénnyel írható le:
Ugyanakkor tudható, hogy mikor x=0 év szintén egyenlő 0 . Ezért ez az egyenlet a függéssel írható le y=nx.
Meg kell találnunk a különbség minimális négyzetösszegét.
Megoldás
Térjünk át a módszer közvetlen alkalmazásának leírására.
1. Az első értéktől balra x tegyen egy számot 1 . Ez az első együttható érték közelítő értéke lesz n.
2. Az oszloptól jobbra yújabb oszlop hozzáadása - nx. Ennek az oszlopnak az első cellájába írjuk az együttható szorzásának képletét n az első változó cellánként x. Ezzel egyidejűleg az együtthatójú mezőhöz fűzzük a hivatkozást abszolút, mivel ez az érték nem változik. Kattintson a gombra Belép.
3. A kitöltési marker segítségével másolja ezt a képletet az alábbi oszlopban található táblázat teljes tartományába.
4. Külön cellában számítsa ki az értékek négyzetei közötti különbségek összegét! yÉs nx. Ehhez kattintson a gombra "Funkció beszúrása".
5. A megnyitott "Funkcióvarázsló" bejegyzést keres "SUMMKVARNA". Válassza ki és nyomja meg a gombot "RENDBEN".
6. Megnyílik az argumentumok ablaka. A terepen "Tömb_x" y. A terepen "Tömb_y" adja meg az oszlopcellák tartományát nx. Az értékek megadásához egyszerűen helyezze a kurzort a mezőbe, és válassza ki a megfelelő tartományt a lapon. Belépés után kattintson a gombra "RENDBEN".
7. Lépjen a lapra "Adat". Az eszköztár szalagján "Elemzés" kattintson a gombra "Megoldást találni".
8. Megnyílik az eszköz paraméterablakja. A terepen „A célfüggvény optimalizálása” képlettel jelölje meg a cella címét "SUMMKVARNA". A paraméterben "Előtt"ügyeljen arra, hogy a kapcsolót állásba állítsa "Minimális". A terepen "Cellák megváltoztatása" az együttható értékével jelölje meg a címet n. Kattintson a gombra "Megoldást talál".
9. A megoldás megjelenik az együttható cellában n. Ez az érték lesz a függvény legkisebb négyzete. Ha az eredmény kielégíti a felhasználót, kattintson a gombra "RENDBEN" egy további ablakban.
Mint látható, a legkisebb négyzetek módszerének alkalmazása meglehetősen bonyolult matematikai eljárás. Egy egyszerű példán bemutattuk a gyakorlatban, de vannak ennél sokkal bonyolultabb esetek is. A Microsoft Excel eszközöket azonban úgy tervezték, hogy a lehető legnagyobb mértékben leegyszerűsítsék a számításokat.
http://multitest.semico.ru/mnk.htm
Általános rendelkezések
Minél kisebb a szám abszolút értékben, annál jobb a választott egyenes (2). Az egyenes kiválasztásának pontosságának jellemzőjeként (2) vehetjük a négyzetek összegét
Az S minimális feltételei a következők lesznek
 | (6) |
 | (7) |
A (6) és (7) egyenlet a következőképpen írható fel:
 | (8) |
 | (9) |
A (8) és (9) egyenletekből könnyen megtalálhatjuk a-t és b-t xi és y i kísérleti értékeiből. A (8) és (9) egyenlettel meghatározott (2) egyenest a legkisebb négyzetek módszerével kapott egyenesnek nevezzük (ez a név hangsúlyozza, hogy az S négyzetösszegnek van minimuma). A (8) és (9) egyenleteket, amelyekből a (2) egyenest határozzuk meg, normál egyenleteknek nevezzük.
Megadhat egy egyszerű és általános módot a normál egyenletek összeállítására. Az (1) kísérleti pontok és a (2) egyenlet segítségével felírhatunk egyenletrendszert a és b-re.
| y 1 = ax 1 + b, | ||
| y 2 =ax 2 +b, ... | (10) | |
| y n = ax n + b, |
Szorozzuk meg ezen egyenletek bal és jobb oldalát az első ismeretlen a együtthatójával (azaz x 1, x 2, ..., x n-nel), és adjuk össze a kapott egyenleteket, így az első normál egyenlet (8) jön létre. .
Szorozzuk meg ezen egyenletek bal és jobb oldalát a második ismeretlen b együtthatójával, azaz. 1-gyel, és összeadjuk a kapott egyenleteket, az eredmény a második normálegyenlet (9).
Ez a normálegyenletek előállítási módja általános: alkalmas például a függvényre
állandó érték van, és azt kísérleti adatokból kell meghatározni (1).
A k egyenletrendszere felírható:
Keresse meg az egyenest (2) a legkisebb négyzetek módszerével.
Megoldás. Találunk:
X i = 21, y i = 46,3, x i 2 = 91, x i y i = 179,1.
Felírjuk a (8) és (9)91a+21b=179.1 egyenleteket,
21a+6b=46,3, innen találjuk
a=0,98 b=4,3.
A jellemzők közötti sztochasztikus kapcsolatok vizsgálatának egyik módszere a regressziós elemzés.
A regresszióanalízis egy regressziós egyenlet levezetése, amelynek segítségével egy valószínűségi változó (eredményattribútum) átlagértékét találjuk meg, ha egy másik (vagy más) változó (faktor-attribútum) értéke ismert. Ez a következő lépéseket tartalmazza:
- a kapcsolat formájának kiválasztása (analitikus regressziós egyenlet típusa);
- egyenletparaméterek becslése;
- az analitikai regressziós egyenlet minőségének értékelése.
Lineáris páronkénti kapcsolat esetén a regressziós egyenlet a következőképpen alakul: y i =a+b·x i +u i . Ennek az egyenletnek a és b paramétereit az x és y statisztikai megfigyelési adatokból becsüljük meg. Az ilyen értékelés eredménye a következő egyenlet: , ahol , az a és b paraméterek becslései, a regressziós egyenletből kapott attribútum (változó) értéke (számított érték).
Leggyakrabban paraméterek becslésére használják legkisebb négyzetek módszere (LSM).
A legkisebb négyzetek módszere biztosítja a legjobb (konzisztens, hatékony és torzítatlan) becsléseket a regressziós egyenlet paramétereire. De csak akkor, ha az (u) véletlentaggal és a független változóval (x) kapcsolatos bizonyos feltevések teljesülnek (lásd az OLS-feltevéseket).
Lineáris páregyenlet paramétereinek becslésének problémája a legkisebb négyzetek módszerével a következő: olyan paraméterbecslések , , , amelyeknél az eredő jellemző tényleges értékeinek négyzetes eltéréseinek összege - y i - a számított értékektől minimális.
Formálisan OLS tesztígy írható: 
.
A legkisebb négyzetek módszereinek osztályozása
- Legkisebb négyzet alakú módszer.
- Maximum likelihood módszer (normál klasszikus lineáris regressziós modellnél a regressziós maradékok normalitása feltételezhető).
- A hibák autokorrelációja és heteroszkedaszticitás esetén az általánosított legkisebb négyzetek OLS módszerét alkalmazzuk.
- Súlyozott legkisebb négyzetek módszere (az OLS speciális esete heteroszkedasztikus reziduumokkal).
Illusztráljuk a lényeget klasszikus legkisebb négyzetek módszere grafikusan. Ehhez a megfigyelési adatok (x i, y i, i=1;n) alapján egy téglalap alakú koordináta-rendszerben (az ilyen szórásdiagramot korrelációs mezőnek nevezzük) készítünk egy szóródiagramot. Próbáljunk meg kiválasztani egy olyan egyenest, amely a legközelebb van a korrelációs mező pontjaihoz. A legkisebb négyzetek módszere szerint az egyenest úgy választjuk ki, hogy a korrelációs mező pontjai és ez az egyenes közötti függőleges távolságok négyzetösszege minimális legyen. 
A feladat matematikai jelölése: 
.
Az y i és x i =1...n értékei számunkra ismertek, ezek megfigyelési adatok. Az S függvényben konstansokat jelentenek. Ebben a függvényben a változók a - , paraméterek szükséges becslései. Két változó függvényének minimumának meghatározásához ki kell számítani ennek a függvénynek a parciális deriváltjait mindegyik paraméterre, és egyenlővé kell tenni azokat nullával, azaz. 
.
Ennek eredményeként 2 normál lineáris egyenletből álló rendszert kapunk: 
Ezt a rendszert megoldva megtaláljuk a szükséges paraméterbecsléseket: 
A regressziós egyenlet paramétereinek számításának helyessége az összegek összevetésével ellenőrizhető (a számítások kerekítése miatt előfordulhat némi eltérés).
A paraméterbecslések kiszámításához összeállíthatja az 1. táblázatot.
A b regressziós együttható előjele jelzi a kapcsolat irányát (ha b >0, a kapcsolat közvetlen, ha b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formálisan az a paraméter értéke y átlagos értéke, ahol x egyenlő nullával. Ha az attribútum-tényezőnek nincs és nem is lehet nulla értéke, akkor az a paraméter fenti értelmezése értelmetlen.
A jellemzők közötti kapcsolat szorosságának értékelése
az r x,y lineáris pár korrelációs együttható segítségével végezzük. A képlet segítségével számítható ki: 
. Ezenkívül a lineáris pár korrelációs együttható a b regressziós együtthatóval határozható meg: 
.
A lineáris pár korrelációs együttható elfogadható értékeinek tartománya –1 és +1 között van. A korrelációs együttható előjele jelzi a kapcsolat irányát. Ha r x, y >0, akkor a kapcsolat közvetlen; ha r x, y<0, то связь обратная.
Ha ez az együttható nagyságrendileg egységhez közeli, akkor a jellemzők közötti kapcsolat meglehetősen szoros lineárisként értelmezhető. Ha a modulja egyenlő egy ê r x , y ê =1-gyel, akkor a jellemzők közötti kapcsolat funkcionális lineáris. Ha az x és y jellemzők lineárisan függetlenek, akkor r x,y közel 0.
Az r x,y kiszámításához használhatja az 1. táblázatot is.
A kapott regressziós egyenlet minőségének értékeléséhez számítsa ki az elméleti determinációs együtthatót - R 2 yx: 
,
ahol d 2 az y regressziós egyenlettel magyarázott varianciája;
e 2 - y reziduális (a regressziós egyenlettel meg nem magyarázható) varianciája;
s 2 y - y teljes (teljes) varianciája.
A determinációs együttható a regresszióval magyarázható y eredő attribútum variációjának (szórásának) arányát jellemzi az y teljes variációban (szórásban). Az R 2 yx determinációs együttható 0 és 1 közötti értékeket vesz fel. Ennek megfelelően az 1-R 2 yx érték az y variancia hányadosát jellemzi, amelyet a modellben nem vett egyéb tényezők és a specifikációs hibák okoznak.
Páros lineáris regresszióval R 2 yx =r 2 yx.
Legkisebb négyzetek módszere (LSM)
Egy m lineáris egyenletrendszer n ismeretlennel a következő alakú:
Három eset lehetséges: m Ha m>n és a rendszer konzisztens, akkor az A mátrixnak legalább m - n lineárisan függő sora van. Itt a megoldást úgy kaphatjuk meg, hogy kiválasztunk n tetszőleges lineárisan független egyenletet (ha van ilyen), és az X = A -1 CV képletet alkalmazzuk, vagyis a feladatot egy korábban megoldottra redukáljuk. Ebben az esetben a kapott megoldás mindig kielégíti a fennmaradó m - n egyenleteket. Számítógép használatakor azonban kényelmesebb egy általánosabb megközelítést alkalmazni - a legkisebb négyzetek módszerét. Az algebrai legkisebb négyzetek módszere lineáris egyenletrendszerek megoldására szolgáló módszer az euklideszi norma minimalizálásával Fejsze? b? > inf. (1.2) Tekintsünk néhány kísérletet, amely során bizonyos időpillanatokban Például a Q(t) hőmérsékletet mérjük. Adjuk meg a mérési eredményeket egy tömbben Tegyük fel, hogy a kísérleti körülmények olyanok, hogy a méréseket ismert hibával végezzük. Ezekben az esetekben a hőmérsékletváltozás Q(t) törvényét egy bizonyos polinom segítségével keressük P(t) = + + + ... +, az ismeretlen együtthatók meghatározása, ..., abból a megfontolásból, hogy az egyenlőség által meghatározott E(, ...,) érték gauss algebrai exel közelítés a minimális értéket vette fel. Mivel a négyzetek összege minimális, ezt a módszert az adatok legkisebb négyzetes közelítésének nevezzük. Ha P(t)-t a kifejezésével helyettesítjük, azt kapjuk Tegyük fel a feladatot egy tömb definiálására úgy, hogy az érték minimális legyen, pl. Határozzuk meg a tömböt a legkisebb négyzetek módszerével. Ehhez a parciális deriváltokat nullával egyenlővé tesszük: Ha beírja az m × n mátrixot A = (), i = 1, 2..., m; j = 1, 2, ..., n, ahol I = 1, 2..., m; j = 1, 2, ..., n, akkor az írott egyenlőség olyan formát ölt Írjuk át az írott egyenlőséget a mátrixokkal végzett műveletek szempontjából. A mátrixot egy oszloppal szorozva megvan Egy transzponált mátrix esetében egy hasonló kapcsolat így néz ki Vezessük be a jelölést: az Ax vektor i-edik komponensét jelöljük Az írott mátrixegyenlőségeknek megfelelően Mátrix formában ez az egyenlőség átírható így A T x = A T B (1,3) Itt A egy négyszögletes m×n mátrix. Sőt, az adatközelítési problémákban általában m > n. Az (1.3) egyenletet normál egyenletnek nevezzük. Az euklideszi vektornormát használva már a kezdetektől lehetséges volt a feladatot ekvivalens mátrix alakban felírni: Célunk ennek a függvénynek a minimalizálása x-ben. Ahhoz, hogy egy megoldási pontban elérjük a minimumot, az x-hez viszonyított első deriváltnak ebben a pontban nullával kell egyenlőnek lennie. Ennek a függvénynek a deriváltjai az 2A T B + 2A T Ax és ezért a megoldásnak meg kell felelnie a lineáris egyenletrendszernek (A T A)x = (A T B). Ezeket az egyenleteket normálegyenleteknek nevezzük. Ha A egy m× n mátrix, akkor A>A - n × n egy mátrix, azaz. A normál egyenlet mátrixa mindig négyzetes szimmetrikus mátrix. Sőt, megvan a pozitív meghatározottság tulajdonsága abban az értelemben, hogy (A>Ax, x) = (Ax, Ax) ? 0. Megjegyzés. Néha az (1.3) alakú egyenlet megoldását az Ax = B rendszer megoldásának nevezik, ahol A egy négyszögletes m × n (m > n) mátrix a legkisebb négyzetek módszerével. A legkisebb négyzetek problémája grafikusan úgy értelmezhető, hogy minimalizálja az adatpontok és a modellgörbe közötti függőleges távolságokat (lásd 1.1. ábra). Ez az elképzelés azon a feltételezésen alapul, hogy a közelítésben szereplő összes hiba megfelel a megfigyelések hibáinak. Ha a független változókban is vannak hibák, akkor célszerűbb lehet minimalizálni az euklideszi távolságot az adatoktól a modellig. Az alábbi algoritmus az OLS Excelben való megvalósításához feltételezi, hogy az összes kezdeti adat már ismert. A bal oldali rendszer AЧX=B mátrixegyenletének mindkét oldalát megszorozzuk a А Т rendszer transzponált mátrixával: A T AX = A T B Ezután a bal oldali egyenlet mindkét oldalát megszorozzuk az (A T A) mátrixszal -1. Ha ez a mátrix létezik, akkor a rendszer definiált. Tekintve, hogy (A T A) -1 *(A T A)=E, kapjuk X=(A T A) -1 A T B. A kapott mátrixegyenlet egy m lineáris egyenletrendszer megoldása, n ismeretlennel m>n esetén. Tekintsük a fenti algoritmus alkalmazását egy konkrét példán keresztül. Példa. Legyen szükséges a rendszer megoldása Az Excelben a probléma megoldási lapja képletmegjelenítési módban így néz ki: Számítási eredmények: A szükséges X vektor az E11:E12 tartományban található. Egy adott lineáris egyenletrendszer megoldása során a következő függvényeket használtuk: 1. MOBR - a tömbben tárolt mátrix inverz mátrixát adja vissza. Szintaxis: MOBR(tömb). A tömb egyenlő számú sorból és oszlopból álló numerikus tömb. 2. MULTIPULT - a mátrixok szorzatát adja vissza (a mátrixok tömbökben vannak tárolva). Az eredmény egy tömb, amely ugyanannyi sort tartalmaz, mint a tömb1, és ugyanannyi oszlopot tartalmaz, mint a tömb2. Szintaxis: MULTIPLE(tömb1,tömb2). A tömb1, tömb2 szorozható tömbök. Miután beírt egy függvényt egy tömbtartomány bal felső cellájába, jelölje ki a tömböt a képletet tartalmazó cellával kezdve, nyomja le az F2 billentyűt, majd nyomja le a CTRL+SHIFT+ENTER billentyűkombinációt. 3. SZÁLLÍTÁS - függőleges cellakészletet alakít át vízszintessé, vagy fordítva. A függvény használatának eredményeként megjelenik egy tömb, amelyben a sorok száma megegyezik az eredeti tömb oszlopainak számával, az oszlopok száma pedig a kezdeti tömb sorainak számával. 4.1. Beépített funkciók használata Számítás regressziós együtthatók funkció segítségével hajtjuk végre LINEST(Értékek_y; x-értékek; Const; statisztika), Értékek_y- y értékek tömbje, x-értékek- opcionális értéktömb x, ha tömb x ki van hagyva, akkor feltételezzük, hogy ez egy ugyanolyan méretű tömb (1;2;3;...) Értékek_y, Const- egy logikai érték, amely jelzi, hogy szükséges-e a konstans b egyenlő volt 0-val. Ha Const jelentése van IGAZ vagy kimaradt, akkor b a szokásos módon számítják ki. Ha az érv Const akkor HAMIS b 0-nak és az értékeknek tételezzük fel aúgy vannak kiválasztva, hogy a kapcsolat teljesüljön y=ax. Statisztika egy logikai érték, amely azt jelzi, hogy szükség van-e további regressziós statisztikák visszaküldésére. Ha az érv Statisztika jelentése van IGAZ, majd a függvény LINEST további regressziós statisztikákat ad vissza. Ha az érv Statisztika jelentése van FEKSZIK vagy kihagyva, akkor a függvényt LINEST csak az együtthatót adja vissza aés állandó b. Emlékeztetni kell arra, hogy a függvények eredménye VONAL() egy értékkészlet – egy tömb. Számításhoz korrelációs együttható funkciót használják CORREL(Tömb1;Tömb2), a korrelációs együttható értékeinek visszaadása, ahol Tömb1- értékek tömbje y, Tömb2- értékek tömbje x. Tömb1És Tömb2 azonos méretűnek kell lennie. 1. PÉLDA. Függőség y(x) szerepel a táblázatban. Épít regressziós egyenesés kiszámítani korrelációs együttható. Írjunk be egy értéktáblázatot egy MS Excel lapba, és készítsünk egy szóródiagramot. A munkalap az ábrán látható formájú lesz. 2. A regressziós együtthatók értékeinek kiszámításához AÉs b válassza ki a cellákat A7:B7, Menjünk a függvényvarázslóhoz és a kategóriában Statisztikai válasszon funkciót LINEST. Töltse ki a megjelenő párbeszédablakot az ábrán látható módon. 3 és nyomja meg rendben. Ennek eredményeként a számított érték csak a cellában jelenik meg A6(4. ábra). Annak érdekében, hogy az érték megjelenjen a cellában B6 be kell lépnie a szerkesztési módba (kulcs F2), majd nyomja meg a billentyűkombinációt CTRL+SHIFT+ENTER. A korrelációs együttható értékének kiszámítása egy cellában C6 a következő képlet került bevezetésre: C7=CORREL(B3:J3;B2:J2). A regressziós együtthatók ismerete AÉs b számoljuk ki a függvényértékeket y=fejsze+b adottnak x. Ehhez bemutatjuk a képletet B5=$A$7*B2+$B$7 és másolja a tartományba C5:J5(5. ábra). Ábrázoljuk a regressziós egyenest a diagramon. Jelölje ki a kísérleti pontokat a grafikonon, kattintson a jobb gombbal, és válassza ki a parancsot Kezdeti adatok. A megjelenő párbeszédpanelen (5. ábra) válassza ki a lapot Sorés kattintson a gombra Hozzáadás. Töltsük ki a beviteli mezőket az ábra szerint. 6 és nyomja meg a gombot rendben. A kísérleti adatgrafikonhoz egy regressziós egyenes kerül hozzáadásra. Alapértelmezés szerint a grafikonja olyan pontokként lesz megrajzolva, amelyeket nem simító vonalak kötnek össze. A regressziós egyenes megjelenésének megváltoztatásához hajtsa végre a következő lépéseket. Kattintson a jobb gombbal a vonaldiagramot ábrázoló pontokra, és válassza ki a parancsot Diagram típusaábra szerint állítsa be a szórási diagram típusát. 7. A vonal típusa, színe és vastagsága az alábbiak szerint változtatható. Jelöljön ki egy sort a diagramon, kattintson a jobb gombbal, és válassza ki a parancsot a helyi menüben Adatsor formátum... Ezután végezze el a beállításokat, például az ábra szerint. 8. Az összes transzformáció eredményeként egy grafikus területen kapunk egy grafikont a kísérleti adatokból és egy regressziós egyenest (9. ábra). 4.2. Trendvonal használata. A különféle közelítő függőségek felépítése az MS Excelben diagramtulajdonságként valósul meg - trendvonal. 2. PÉLDA. A kísérlet eredményeként egy bizonyos táblázatfüggést határoztunk meg. Válasszon ki és állítson össze egy közelítő függőséget. Készítsen grafikonokat táblázatos és kiválasztott analitikai függőségekről. A probléma megoldása a következő szakaszokra osztható: kezdeti adatok bevitele, szóródási diagram készítése és trendvonal hozzáadása ehhez a grafikonhoz. Nézzük meg ezt a folyamatot részletesen. Írjuk be a kiindulási adatokat a munkalapra, és ábrázoljuk a kísérleti adatokat. Ezután válassza ki a kísérleti pontokat a grafikonon, kattintson a jobb gombbal, és használja a parancsot Hozzáadás l trendvonal(10. ábra). A megjelenő párbeszédpanel lehetővé teszi egy közelítő függőség felépítését. Ennek az ablaknak az első füle (11. ábra) jelzi a közelítő függőség típusát. A másodikon (12. ábra) az építési paramétereket határozzuk meg: · a közelítő függőség neve; · előrejelzés előre (hátra) by n units (ez a paraméter határozza meg, hogy hány egységgel előre (hátra) kell meghosszabbítani a trendvonalat); hogy mutassuk-e a görbe metszéspontját egy egyenessel y=áll; · a közelítő függvény megjelenítése a diagramon vagy sem (az egyenlet diagramon való megjelenítésének lehetősége); · a szórás értékét elhelyezzük-e a diagramon vagy sem (a közelítési megbízhatóság értékének diagramon való elhelyezésének lehetősége). Közelítő függésként válasszunk egy másodfokú polinomot (11. ábra), és jelenítsük meg gráfon a polinomot leíró egyenletet (12. ábra). Az így kapott diagramot a ábra mutatja. 13. Hasonlóképpen használva trendvonalak kiválaszthatja az olyan függőségek paramétereit, mint lineáris y=a∙x+b, logaritmikus y=a∙ln(x)+b, · exponenciális y=a∙e b, · nyugtató hatású y=a∙x b, polinom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+dés így tovább, egészen a 6. fokú polinomig, · lineáris szűrés. 4.3. Megoldó blokk használata Jelentős érdeklődésre tarthat számot az MS Excelben a legkisebb négyzetek módszerével, megoldóblokk segítségével történő paraméterek kiválasztásának megvalósítása. Ez a technika lehetővé teszi bármilyen típusú függvény paramétereinek kiválasztását. Tekintsük ezt a lehetőséget a következő probléma példájával. 3. PÉLDA. A kísérlet eredményeként a táblázatban bemutatott z(t) függést kaptuk Válassza ki a függőségi együtthatókat Z(t)=At 4 +Bt 3 +Ct 2 +Dt+K legkisebb négyzetek módszere. Ez a probléma egyenértékű az öt változóból álló függvény minimumának megtalálásával Tekintsük az optimalizálási feladat megoldásának folyamatát (14. ábra). Legyen az értékek A, BAN BEN, VAL VEL, DÉs NAK NEK sejtekben tárolják A7:E7. Számítsuk ki a függvény elméleti értékeit Z(t)=4-nél +Bt 3 +Ct 2 +Dt+K adottnak t(B2:J2).
Ehhez a cellában B4írja be a függvény értékét az első pontba (cella B2): B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7. Másoljuk be ezt a képletet a tartományba C4:J4és kapjuk meg a függvény várható értékét azokon a pontokon, amelyek abszcisszáit a cellákban tároljuk B2:J2. A cellába B5 Vezessünk be egy képletet, amely kiszámolja a kísérleti és számított pont közötti különbség négyzetét: B5=(B4-B3)^2, és másolja a tartományba C5:J5. Egy cellában F7 tároljuk a teljes négyzetes hibát (10). Ehhez írja be a képletet: F7 = SZUM(B5:J5). Használjuk a parancsot Service®Megoldás kereséseés korlátok nélkül oldja meg az optimalizálási problémát. ábrán látható párbeszédpanel beviteli mezőit ennek megfelelően töltsük ki. 14 és nyomja meg a gombot Végrehajtás. Ha sikerül megoldást találni, az ábrán látható ablak. 15. A döntési blokk eredménye a cellákba kerül A7:E7paraméterértékek funkciókat Z(t)=4-nél +Bt 3 +Ct 2 +Dt+K. A sejtekben B4:J4 kapunk függvény várható értéke a kiindulópontokon. Egy cellában F7 tárolásra kerül teljes négyzetes hiba. Egy tartomány kiválasztásával a kísérleti pontokat és az illesztett vonalat egy grafikus területen jelenítheti meg B2:J4, hívás Diagram varázsló majd formázza kinézet kapott grafikonokat. Rizs. 17 megjeleníti az MS Excel munkalapot a számítások elvégzése után. 5. IRODALOM 1. Alekseev E.R., Chesnokova O.V., Számítási matematikai problémák megoldása a Mathcad12, MATLAB7, Maple9 csomagokban. – NT Press, 2006.–596 p. :il. – (oktatóanyag) 2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, mérnöki és matematikai feladatok megoldása. –M., BINOM, 2008.–260 p. 3. Berezin I.S., Zhidkov N.P., Számítási módszerek – M.: Nauka, 1966. – 632 p. 4. Garnaev A.Yu., MS EXCEL és VBA használata közgazdaságtanban és pénzügyekben. – St. Petersburg: BHV - Petersburg, 1999.–332 p. 5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Az elemzés numerikus módszerei – M.: Nauka, 1967. – 368 p. 6. Korn G., Korn T., Matematika kézikönyve tudósok és mérnökök számára – M., 1970, 720 p. 7. Alekseev E.R., Chesnokova O.V. Útmutató az MS EXCEL-ben végzett laboratóriumi munkákhoz. Minden szakos hallgató számára. Donyeck, DonNTU, 2004. 112 p.Algebrai legkisebb négyzetek módszere
Kísérleti adatok elemzése
MNC Excelben
y
0.5
1.5
2.5
3.5
x
2.39
2.81
3.25
3.75
4.11
4.45
4.85
5.25
Rizs. 6
0.15
0.16
0.17
0.18
0.19
0.20
4.4817
4.4930
5.4739
6.0496
6.6859
7.3891
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
