Stáhněte si ukázkový sešit
Tento tutoriál ukazuje, jak používat Funkce Excel LINEST v Excelu pro výpočet statistik o trendové linii.
Přehled funkcí LINEST
Funkce LINEST Vypočítává statistiky o trendové linii přizpůsobené známým datovým bodům pomocí metody nejmenších čtverců.
Chcete -li použít funkci LINEST Excel List, vyberte buňku a zadejte:
(Všimněte si, jak se zobrazují vstupy vzorců)
Funkce LINEST Syntaxe a vstupy
| 1 | = LINEST (known_ys, known_xs, const, stats) |
known_y's - Pole známých hodnot Y.
známé_x - Pole známých hodnot X.
konst - VOLITELNÉ. Logická hodnota udávající, zda se má vypočítat B (průsečík v y = mx + b) pomocí metody nejmenších čtverců (TRUE nebo Ommitted) nebo ručně nastavit B = 0 (FALSE).
statistiky - VOLITELNÉ. Vrátit další statistiky (PRAVDA) nebo pouze vrátit m (sklon) a b (zachytit) (NEPRAVDA nebo vynecháno)
Co je LINEST?
Funkce LINEST v Excelu je funkce používaná ke generování regresní statistiky pro lineární regresní model. LINEST je maticový vzorec a lze jej použít samostatně nebo s jinými funkcemi pro výpočet konkrétní statistiky o modelu.
Lineární regrese je metoda ve statistikách používaná pro predikci dat po přímce pomocí známých dat. Regrese se používá k předpovídání hodnot, jako je růst tržeb, požadavky na zásoby nebo jednoduché tržní trendy.
LINEST je jako FORECAST v tom, že dosahuje podobného výsledku, ale s mnohem více informacemi o vašem regresním modelu a také možností přizpůsobit více než jedné nezávislé proměnné.
Předpokládejme, že mám tabulku dat s X a y hodnoty kde X je nezávislá proměnná a y je závislá proměnná:
Chci vědět, co je regresní rovnice výše uvedených dat. Pomocí LINEST:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Hodnota interceptu y je zde ve vědecké notaci ekvivalentní 0.
Rovnice přímky je y = 2x + 0. Všimněte si, že LINEST vrací oba sklon a průsečík čáry. Chcete -li vrátit obě hodnoty, musí být vzorec zadán jako maticový vzorec. Více o vzorcích pole později.
Jak používat LINEST
Funkce LINEST přebírá čtyři argumenty:
| 1 | = LINEST (known_y’s, known_x’s, const, stats) |
Kde,
| Argument | Popis |
| known_y's a známé_x | Je X a y data ve vaší datové tabulce |
| konst | PRAVDA/NEPRAVDA možnost, zda má být zachycení y vynuceno na 0 nebo vypočteno normálně |
| statistiky | PRAVDA/NEPRAVDA možnost, zda mají být vráceny další regresní statistiky |
Pomocí našeho prvního příkladu je funkce zapsána jako:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Když statistiky možnost je nastavena na hodnotu TRUE, organizace regresní statistiky je následující:
Možná si říkáte, co každá proměnná znamená.
| Statistický | Popis |
| mn | Koeficienty sklonu pro x proměnných |
| b | y-zachytit |
| sen | Standardní chyba pro každý koeficient sklonu |
| seb | Standardní chyba pro zachycení y |
| r2 | Koeficient determinace |
| sey | Standardní chyba pro y odhad |
| F | Statistika F (k určení, zda vztah proměnných nastává náhodou) |
| dF | Stupně svobody |
| ssreg | Regresní součet čtverců |
| ssrezidua | Zbylý součet čtverců |
Hlavní statistikou, které je třeba porozumět, jsou svahové koeficienty, y-intercept a koeficient determinace nebo r2 hodnotu modelu.
Pomocí výše uvedeného příkladu a výběrem PRAVDA pro statistiky parametr:
Zvýrazněné buňky ukazují sklon = 2, intercept = 0 a r2 = 1.
The r2 hodnota je indikátorem síly korelace modelu. Lze to považovat za indikátor vhodnosti. Nízká r2 hodnota by znamenala špatnou korelaci mezi závislými a nezávislými proměnnými a opak je pravdou pro vysoké r2 hodnoty, s r2 = 1 se perfektně hodí.
Ve verzích následujících po lednu 2022 aplikace Excel v Microsoft 365 (dříve Office 365) dynamická pole změnila způsob vyhodnocování vzorců polí. Již není nutné používat CTRL + SHIFT + ENTER nebo zvýrazňovat oblast buněk, které pole zabere. Jednoduše zadejte vzorec a klikněte na Enter a výsledné buňky se „vysypou“ do pole.
Ve zbývající části tohoto článku budeme odkazovat pomocí LINEST s ohledem na dynamická pole v Microsoft 365 Excel.
Prognózy s LINEST (jednoduchá regrese)
Pro predikci hodnoty závislé proměnné lze použít kombinaci funkcí LINEST a SUM y, vzhledem k tomu známý X a y data. Níže je uveden příklad, který ukazuje, co y hodnota bude, když x = 14.
| 1 | = SUM (LINEST (C3: C7, B3: B7)*{14,1}) |
Model je ve tvaru y = mx + b. To je stejné jako y = a+ bx, jen jiný způsob reprezentace rovnice. Tip, který byste měli mít na paměti pro lineární rovnice, je proměnná vedle X je vždy sklon a proměnná za znaménkem plus nebo mínus je vždy průsečík, bez ohledu na písmena použitá v rovnici.
Pomocí vzorce: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) vrátí výsledek 28. Protože se jedná o jediný výsledek, není nutné jej zadávat jako pole.
Zadní konec výše uvedeného vzorce *{14,1} určuje nezávislou proměnnou, která má být použita pro předpověď závislé proměnné, v tomto případě 14.
Můžeme to ověřit zadáním x = 14 do rovnice přímky, y = 2x + 0.
Prognózy s LINEST (vícenásobná lineární regrese)
Následující tabulka dat pochází ze stránky LINEST na webu podpory společnosti Microsoft.
V některých případech existuje více než jedna nezávislá proměnná, kterou je třeba vzít v úvahu při vytváření lineárního regresního modelu. Toto se nazývá vícenásobná lineární regrese (tj. Více nezávislých proměnných). Pokud chci odhadnout náklady na administrativní budovu, součástí rovnice by byly věci jako podlahová plocha, počet vchodů do budovy, stáří budovy a počet kanceláří. Podívejme se na příklad.
Zadáním vzorce LINEST do buňky G29 a jeho spuštěním získáme:
| 1 | = LINEST (E3: E13, A3: D13, TRUE, TRUE) |
Model je dodáván ve formě:
Pamatujte, že pole výsledků LINEST je v opačném pořadí než rovnice. Ve výše uvedeném příkladu je 52 317,8 náš intercept, b a 27,6 je naše m1 nebo hodnota sklonu pro proměnnou Podlahový prostor, X1.
Pomocí funkce LINEST s poskytnutými daty je náš regresní model:
S r2 hodnotu 0,997, což znamená silný nebo vysoce korelovaný model. Pomocí modelu nyní můžete předvídat, jaká bude odhadovaná hodnota kancelářské budovy na základě jakékoli kombinace výše uvedených nezávislých proměnných.
LINEST tipy
- Zajistěte, abyste měli nejaktuálnější verzi Microsoft 365 k využití LINEST s dynamickými poli. Chcete -li využívat funkce dynamického pole, bude možná nutné povolit aktuální kanál (Preview) Office Insider. Na stránce účtu:
- Pokud používáte verzi, která není Microsoft 365, budete muset použít starší metodu CTRL + SHIFT + ENTER (CSE) k vyhodnocení vzorců pole.
- Pokud používáte starší metodu, počet sloupců, které je třeba zvýraznit při zadávání funkce LINEST array, je vždy počet X proměnné ve vašich datech plus 1. Počet řádků, které lze pro pole vybrat, je 5.
- Pokud budete svoji verzi Excelu s povoleným dynamickým polem sdílet s někým, kdo používá vydání jiné než Microsoft 365, vyhněte se problémům s kompatibilitou pomocí starší metody CSE.
Zajímá vás více prognóz?
Podívejte se na naše další články o předpovědích s funkcemi Exponential Smoothing, TREND, GROWTH a LOGEST.
LINEST funkce v Tabulkách Google
Funkce LINEST funguje v Tabulkách Google úplně stejně jako v Excelu.
LINEST Příklady ve VBA
Můžete také použít funkci LINEST ve VBA. Typ:application.worksheetfunction.linest (known_ys, known_xs, const, stats)
Provedení následujícího prohlášení VBA
| 1 | Rozsah ("D2") = Application.WorksheetFunction.LinEst (Range ("A2: A8"), Range ("B2: B8")) |
přinese následující výsledky
Pokud jde o argumenty funkce (známé_y atd.), Můžete je buď zadat přímo do funkce, nebo definovat proměnné, které se místo toho použijí.
Zpět na seznam všech funkcí v Excelu
