Prenesite primer delovnega zvezka
Ta vadnica prikazuje, kako uporabljati Excel LINEST funkcija v Excelu za izračun statistike o trendni liniji.
LINEST Pregled funkcije
Funkcija LINEST Izračuna statistiko o liniji trenda, ki je prilagojena znanim podatkovnim točkam, z uporabo metode najmanjših kvadratov.
Če želite uporabiti LINEST funkcijo delovnega lista Excel, izberite celico in vnesite:
(Upoštevajte, kako so prikazani vnosi formule)
LINEST funkcija Sintaksa in vhodi
| 1 | = LINEST (znan_ys, znan_xs, const, statistika) |
znani_y - Niz znanih vrednosti Y.
znani_x -ji - Niz znanih vrednosti X.
const - NEOBVEZNO. Logična vrednost, ki označuje, ali je treba izračunati B (prestrezanje v y = mx + b) z uporabo metode najmanjših kvadratov (TRUE ali ommit) ali ročno nastaviti B = 0 (FALSE).
statistika - NEOBVEZNO. Vrni dodatne statistike (TRUE) ali samo m (naklon) in b (prestrezanje) (FALSE ali izpuščeno)
Kaj je LINEST?
LINEST funkcija v Excelu je funkcija za ustvarjanje regresijske statistike za linearni regresijski model. LINEST je matrična formula in se lahko uporablja samostojno ali z drugimi funkcijami za izračun posebnih statističnih podatkov o modelu.
Linearna regresija je metoda v statistiki, ki se uporablja za napovedovanje podatkov po ravni črti z uporabo znanih podatkov. Regresija se uporablja za napovedovanje vrednosti, kot so rast prodaje, potrebe po zalogah ali preprosti tržni trendi.
LINEST je podoben napovedi, saj dosega podoben rezultat, vendar z veliko več informacijami o vašem modelu regresije in možnostjo, da se prilega več kot eni neodvisni spremenljivki.
Recimo, da imam tabelo podatkov s x in y vrednosti kje x je neodvisna spremenljivka in y je odvisna spremenljivka:
Želim vedeti, kaj je regresijska enačba zgornjih podatkov. Uporaba LINEST:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Vrednost y-prestrezanja je v znanstvenem zapisu enaka 0.
Enačba črte je y = 2x + 0. Upoštevajte, da se vrne LINEST oboje naklon in prestrezanje črte. Za vrnitev obeh vrednosti je treba formulo vnesti kot formulo matrike. Več o formulah matrike kasneje.
Kako uporabljati LINEST
Funkcija LINEST ima štiri argumente:
| 1 | = LINEST (znani_y, znani_x, const, statistika) |
Kje,
| Prepir | Opis |
| znani_y in znani_x -ji | Ali je x in y podatke v vaši podatkovni tabeli |
| const | TRUE/FALSE možnost za to, ali je treba y-prestrezanje prisiliti na 0 ali normalno izračunati |
| statistika | Možnost TRUE/FALSE, ali je treba vrniti dodatno statistiko regresije |
V prvem primeru je funkcija zapisana tako:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Ko statistika možnost je nastavljena na TRUE, organizacija regresijske statistike je naslednja:
Morda se sprašujete, kaj pomeni vsaka spremenljivka.
| Statistika | Opis |
| mn | Koeficienti naklona za x spremenljivke |
| b | y-prestrezanje |
| sen | Standardna napaka za vsak koeficient naklona |
| seb | Standardna napaka za y-prestrezanje |
| r2 | Koeficient odločnosti |
| sey | Standardna napaka za y oceniti |
| F. | Statistika F (za ugotavljanje, ali do razmerja spremenljivk pride po naključju) |
| df | Stopnje svobode |
| ssreg | Regresijska vsota kvadratov |
| ssostanek | Preostala vsota kvadratov |
Glavni statistični podatki, ki jih je treba razumeti, so koeficienti naklona, y-prestrezanje in koeficient določanja ali r2 vrednost modela.
Z uporabo zgornjega primera in izbiro TRUE za datoteko statistika parameter:
Označene celice prikazujejo naklon = 2, prestrezanje = 0 in r2 = 1.
The r2 vrednost je pokazatelj moči korelacije modela. Lahko ga razumemo kot pokazatelj primernosti. Nizek r2 vrednost bi pomenila slabo korelacijo med vašimi odvisnimi in neodvisnimi spremenljivkami, nasprotno pa velja za visok r2 vrednosti, z r2 = 1 se popolnoma prilega.
V izdajah Excela v januarju 2022 v sistemu Microsoft 365 (prej Office 365) so dinamična polja spremenila način ocenjevanja formul matrike. Ni več treba uporabljati CTRL + SHIFT + ENTER ali označevati območja celic, ki jih bo matrika zavzela. Preprosto vnesite formulo in kliknite enter in nastale celice se bodo "razlile" v matriko.
V preostalem delu tega članka se bomo sklicevali na uporabo LINEST glede na dinamične matrike v programu Microsoft 365 Excel.
Napovedovanje z LINEST (preprosta regresija)
S kombinacijo funkcij LINEST in SUM lahko napovemo vrednost odvisne spremenljivke y, glede na znano x in y podatkov. Spodaj je primer, ki prikazuje, kaj y vrednost bo, ko je x = 14.
| 1 | = SUM (LINEST (C3: C7, B3: B7)*{14,1}) |
Model je v obliki y = mx + b. To je enako kot y = a+ bx, le drugačen način predstavljanja enačbe. Nasvet, ki ga morate upoštevati pri linearnih enačbah, je spremenljivka poleg x je vedno naklon, spremenljivka, ki sledi znaku plus ali minus, pa je vedno prestrezanje, ne glede na črke, uporabljene v enačbi.
Z uporabo formule: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) vrne rezultat 28. Ker gre za en sam rezultat, ga ni treba vnesti kot matriko.
Zadnji del zgornje formule *{14,1} podaja neodvisno spremenljivko, ki jo je treba uporabiti za napovedovanje odvisne spremenljivke, v tem primeru 14.
To lahko preverimo tako, da v enačbo črte vnesemo x = 14, y = 2x + 0.
Napovedovanje z LINEST (večkratna linearna regresija)
Naslednja podatkovna tabela prihaja s strani LINEST na spletnem mestu Microsoftove podpore.
V nekaterih primerih obstaja več kot ena neodvisna spremenljivka, ki jo je treba upoštevati pri ustvarjanju linearnega regresijskega modela. To se imenuje večkratna linearna regresija (tj. Več neodvisnih spremenljivk). Če želim oceniti stroške poslovne stavbe, bi bile v enačbo vključene stvari, kot so tlorisne površine, število vhodov v stavbo, starost stavbe in število pisarn. Poglejmo primer.
Če v celico G29 vnesemo formulo LINEST in jo izvedemo, dobimo:
| 1 | = LINEST (E3: E13, A3: D13, TRUE, TRUE) |
Model je na voljo v naslednji obliki:
Ne pozabite, da je niz rezultatov LINEST v obratnem vrstnem redu od enačbe. V zgornjem primeru je 52.317,8 naše prestrezanje, b, 27.6 pa naš m1 ali vrednost naklona za spremenljivko Floor space, x1.
Z uporabo funkcije LINEST s posredovanimi podatki je naš regresijski model:
Z r2 vrednost 0,997, kar kaže na močan ali zelo koreliran model. Z modelom lahko zdaj predvidite, kakšna bo ocenjena vrednost poslovne stavbe na podlagi katere koli kombinacije zgoraj navedenih neodvisnih spremenljivk.
LINEST Nasveti
- Poskrbite, da imate najnovejšo različico Microsoft 365 za uporabo LINEST z dinamičnimi matrikami. Za uporabo funkcij dinamičnega niza boste morda morali omogočiti trenutni kanal Office Insider (predogled). Na strani računa:
- Če uporabljate izdajo, ki ni Microsoft 365, boste morali za ocenjevanje formul matrike uporabiti podedovano metodo CTRL + SHIFT + ENTER (CSE).
- Če uporabljate podedovano metodo, je število stolpcev, ki jih označite pri vnosu funkcije matrike LINEST, vedno število x spremenljivk v vaših podatkih plus 1. Število vrstic, ki jih izberete za matriko, je 5.
- Če boste različico Excela, ki podpira dinamično matriko, delili z nekom, ki uporablja izdajo, ki ni Microsoft 365, uporabite podedovano metodo CSE, da se izognete težavam z združljivostjo.
Vas zanima več napovedi?
Oglejte si druge članke o napovedovanju s funkcijami eksponentnega glajenja, TREND, RAST in LOGEST.
LINEST funkcijo v Google Preglednicah
Funkcija LINEST v Google Preglednicah deluje popolnoma enako kot v Excelu.
LINEST Primeri v VBA
V VBA lahko uporabite tudi funkcijo LINEST. Vrsta:application.worksheetfunction.linest (known_ys, known_xs, const, stats)
Izvajanje naslednjega stavka VBA
| 1 | Obseg ("D2") = Application.WorksheetFunction.LinEst (obseg ("A2: A8"), obseg ("B2: B8")) |
bo prinesel naslednje rezultate
Za argumente funkcije (znani_y itd.) Jih lahko vnesete neposredno v funkcijo ali pa namesto tega določite spremenljivke.
Vrnite se na seznam vseh funkcij v Excelu
