Stiahnite si ukážkový zošit
Tento tutoriál ukazuje, ako používať Funkcia Excel LINEST v Exceli na výpočet štatistík trendovej čiary.
Prehľad funkcií LINEST
Funkcia LINEST Vypočíta štatistiku trendovej čiary prispôsobenej známym dátovým bodom pomocou metódy najmenších štvorcov.
Ak chcete použiť funkciu LINEST Excel Workheet, vyberte bunku a napíšte:
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Funkcia LINEST Syntax a vstupy
| 1 | = LINEST (známe_ys, známe_xs, konšt., Štatistiky) |
known_y's - Súbor známych hodnôt Y.
známe_x - Súbor známych hodnôt X.
konšt - VOLITEĽNÉ. Logická hodnota udávajúca, či sa má vypočítať B (priesečník v y = mx + b) pomocou metódy najmenších štvorcov (TRUE alebo vynechaný) alebo manuálne nastaviť B = 0 (FALSE).
štatistiky - VOLITEĽNÉ. Vrátiť dodatočnú štatistiku (TRUE) alebo vrátiť iba m (sklon) a b (zachytenie) (FALSE alebo vynechané)
Čo je to LINEST?
Funkcia LINEST v Exceli je funkcia používaná na generovanie regresných štatistík pre lineárny regresný model. LINEST je maticový vzorec a je možné ho použiť samostatne alebo s inými funkciami na výpočet konkrétnych štatistík o modeli.
Lineárna regresia je metóda v štatistikách, ktorá sa používa na predpovedanie údajov podľa priamky pomocou známych údajov. Regresia sa používa na predpovedanie hodnôt, ako je rast tržieb, požiadavky na zásoby alebo jednoduché trendy na trhu.
LINEST je ako FORECAST v tom, že dosahuje podobný výsledok, ale s oveľa väčším počtom informácií o vašom regresnom modeli a možnosťou vložiť viac ako jednu nezávislú premennú.
Predpokladajme, že mám tabuľku údajov s X a r hodnoty kde X je nezávislá premenná a r je závislá premenná:
Chcem vedieť, čo je regresná rovnica vyššie uvedených údajov. Použitie LINEST:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Interceptová hodnota y je tu vo vedeckom zápise ekvivalentná 0.
Rovnica priamky je y = 2x + 0. Všimnite si toho, že LINEST vráti obaja sklon a priesečník čiary. Ak chcete vrátiť obe hodnoty, vzorec musíte zadať ako vzorec poľa. Viac o vzorcoch polí neskôr.
Ako používať LINEST
Funkcia LINEST má štyri argumenty:
| 1 | = LINEST (známe_ roky, známe_x, konšt., Štatistiky) |
Kde,
| Hádka | Popis |
| known_y's a známe_x | Je X a r údaje vo vašej dátovej tabuľke |
| konšt | TRUE/FALSE voľba, či má byť priesečník y vynútený na 0 alebo vypočítaný normálne |
| štatistiky | PRAVDA/NEPRAVDA, či sa majú vrátiť ďalšie regresné štatistiky |
Na našom prvom príklade je funkcia zapísaná ako:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Keď štatistiky je nastavená na hodnotu TRUE, organizácia regresnej štatistiky je nasledovná:
Možno sa pýtate, čo jednotlivé premenné znamenajú.
| Štatistiky | Popis |
| mn | Koeficienty sklonu pre x premenných |
| b | y-zachytiť |
| sen | Štandardná chyba pre každý koeficient sklonu |
| seb | Štandardná chyba pre zachytenie osi y |
| r2 | Koeficient determinácie |
| ser | Štandardná chyba pre súbor r odhad |
| F | Štatistika F (na určenie, či k vzťahu premenných dochádza náhodou) |
| df | Stupne slobody |
| ssreg | Regresný súčet štvorcov |
| ssreziduum | Zostávajúci súčet štvorcov |
Hlavnými štatistikami, ktoré je potrebné porozumieť, sú koeficienty sklonu, inter -cept a koeficient determinácie alebo r2 hodnotu modelu.
Použitím vyššie uvedeného príkladu a výberom položky TRUE pre štatistiky parameter:
Zvýraznené bunky ukazujú sklon = 2, zachytenie = 0 a r2 = 1.
The r2 hodnota je indikátorom sily korelácie modelu. Dá sa to považovať za indikátor vhodnosti. Nízka r2 hodnota by znamenala slabú koreláciu medzi vašimi závislými a nezávislými premennými a opak je pravdou pre vysoké r2 hodnoty, s r2 = 1 perfektne sedí.
Vo vydaniach, ktoré nasledovali po januári 2022 Excelu v Microsoft 365 (predtým Office 365), dynamické polia zmenili spôsob vyhodnocovania vzorcov polí. Už nie je potrebné používať CTRL + SHIFT + ENTER alebo zvýrazňovať oblasť buniek, ktoré pole zaberie. Jednoducho zadajte vzorec a kliknite na tlačidlo Enter a výsledné bunky sa „vysypú“ do poľa.
V zostávajúcej časti tohto článku sa budeme odvolávať na použitie LINEST s ohľadom na dynamické polia v programe Microsoft 365 Excel.
Prognózy s LINEST (jednoduchá regresia)
Kombináciu funkcií LINEST a SUM je možné použiť na predpovedanie hodnoty závislej premennej r, daná známa X a r údaje. Nasleduje príklad, ktorý ukazuje, čo r hodnota bude, keď x = 14.
| 1 | = SUM (LINEST (C3: C7, B3: B7)*{14,1}) |
Model je v tvare y = mx + b. Je to rovnaké ako y = a+ bx, iba iný spôsob reprezentácie rovnice. Tip, ktorý by ste mali mať na pamäti pre lineárne rovnice, je premenná vedľa X je vždy sklon a premenná po znamienku plus alebo mínus je vždy zachytenie bez ohľadu na písmená použité v rovnici.
Pomocou vzorca: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) vráti výsledok 28. Keďže ide o jediný výsledok, nie je potrebné ho zadávať ako pole.
Chvost vyššie uvedeného vzorca *{14,1} určuje nezávislú premennú, ktorá sa má použiť na predikciu závislej premennej, v tomto prípade 14.
Môžeme to skontrolovať zadaním x = 14 do rovnice čiary, y = 2x + 0.
Prognózy s LINEST (viacnásobná lineárna regresia)
Nasledujúca tabuľka s údajmi pochádza zo stránky LINEST spoločnosti Microsoft.
V niektorých prípadoch existuje viac ako jedna nezávislá premenná, ktorú je potrebné vziať do úvahy pri vytváraní lineárneho regresného modelu. Toto sa nazýva viacnásobná lineárna regresia (t.j. viac nezávislých premenných). Ak chcem odhadnúť náklady na kancelársku budovu, súčasťou rovnice by boli veci ako podlahová plocha, počet vchodov do budovy, vek budovy a počet kancelárií. Pozrime sa na príklad.
Zadaním vzorca LINEST do bunky G29 a jeho vykonaním získame:
| 1 | = LINEST (E3: E13, A3: D13, TRUE, TRUE) |
Model je dodávaný vo forme:
Pamätajte si, že pole výsledkov LINEST je v opačnom poradí ako v rovnici. Vo vyššie uvedenom príklade je 52 317,8 náš intercept, b a 27,6 je naše m1 alebo hodnota sklonu pre premennú Podlahový priestor, X1.
Pomocou funkcie LINEST s poskytnutými údajmi je náš regresný model:
S r2 hodnotu 0,997, čo naznačuje silný alebo vysoko korelovaný model. Pomocou modelu teraz môžete predpovedať, aká bude hodnotená hodnota kancelárskej budovy na základe akejkoľvek kombinácie vyššie uvedených nezávislých premenných.
LINEST tipy
- Zaistite, aby ste mali najaktuálnejšiu verziu systému Microsoft 365 na používanie LINEST s dynamickými poľami. Na používanie funkcií dynamického poľa bude možno potrebné povoliť aktuálny kanál (Preview) balíka Office Insider. Na stránke účtu:
- Ak používate vydanie iné ako Microsoft 365, budete musieť na vyhodnotenie vzorcov poľa použiť starú metódu CTRL + SHIFT + ENTER (CSE).
- Ak používate starú metódu, počet stĺpcov, ktoré sa majú zvýrazniť pri zadávaní funkcie poľa LINEST, je vždy počet X premenné vo vašich údajoch plus 1. Počet riadkov, ktoré je potrebné vybrať pre pole, je 5.
- Ak budete svoju verziu Excelu s povoleným dynamickým poľom zdieľať s niekým, kto používa vydanie iné ako Microsoft 365, použite starú metódu CSE, aby ste sa vyhli problémom s kompatibilitou.
Máte záujem o viac prognóz?
Pozrite si naše ďalšie články o prognózach s funkciami Exponenciálne vyhladzovanie, TREND, RAST a LOGEST.
LINEST fungovať v Tabuľkách Google
Funkcia LINEST funguje v Tabuľkách Google úplne rovnako ako v programe Excel.
LINEST príklady vo VBA
Vo VBA môžete použiť aj funkciu LINEST. Typ:application.worksheetfunction.linest (known_ys, known_xs, const, stats)
Vykonanie nasledujúceho vyhlásenia VBA
| 1 | Rozsah ("D2") = Application.WorksheetFunction.LinEst (Range ("A2: A8"), Range ("B2: B8")) |
prinesie nasledujúce výsledky
Pokiaľ ide o argumenty funkcie (známe_roky atď.), Môžete ich buď zadať priamo do funkcie, alebo definovať premenné, ktoré sa namiesto toho použijú.
Vráťte sa do zoznamu všetkých funkcií v programe Excel
