Çfarë është një linjë trendi në excel. Mjetet e parashikimit në Microsoft Excel

Komploti

Analiza e regresionit

ekuacioni i regresionit Y nga X quhet varësi funksionale y=f(x), dhe komploti i tij është një vijë regresioni.

Excel ju lejon të krijoni grafikët dhe grafikët me cilësi mjaft të pranueshme. Excel ka një mjet të veçantë - magjistarin e grafikut, nën drejtimin e të cilit përdoruesi kalon në të katër fazat e procesit të ndërtimit të një grafiku ose grafiku.

Si rregull, ndërtimi i një grafiku fillon me zgjedhjen e një diapazoni që përmban të dhënat mbi të cilat duhet të ndërtohet. Një fillim i tillë thjeshton rrjedhën e mëtejshme të komplotit. Megjithatë, diapazoni me të dhënat fillestare mund të ndahet edhe në fazën e dytë të dialogut me MJESHTRI I DIAGRAMIT. Në Excel 2003 MJESHTRI I DIAGRAMITështë në meny në formën e një butoni ose një diagram mund të krijohet duke klikuar në skedën INSERT dhe në listën që hapet, gjeni artikullin DIAGRAMË. Në Excel 2007 gjejmë gjithashtu skedën INSERT(Fig. 31).

Oriz. 31. MJESHTRI I DIAGRAMIT në Excel 2007

Mënyra më e lehtë për të zgjedhur gamën e të dhënave burimore në të cilën këto të dhëna janë në rreshtat ngjitur (kolona ose rreshta) është të klikoni në qelizën e sipërme të majtë të diapazonit dhe më pas të tërhiqni treguesin e miut në qelizën e poshtme djathtas të diapazonit. Kur zgjidhni të dhëna në rreshtat jo ngjitur, tërhiqni treguesin e miut mbi rreshtat e zgjedhur duke mbajtur të shtypur tastin Ctrl. Nëse një nga seritë e të dhënave ka një qelizë me titull, seria tjetër e zgjedhur duhet të ketë gjithashtu një qelizë përkatëse, edhe nëse ajo është bosh.

Për analizën e regresionit, është mirë të përdoret një grafik shpërndarjeje (Fig. 30). Gjatë vizatimit të tij, Excel trajton rreshtin e parë të gamës së zgjedhur të të dhënave burimore si një grup vlerash për argumentin e funksioneve, grafikët e të cilëve duhet të vizatohen (i njëjti grup për të gjitha funksionet). Rreshtat e mëposhtëm perceptohen si grupe vlerash të vetë funksioneve (çdo rresht përmban vlerat e një prej funksioneve që korrespondojnë me vlerat e argumenteve të dhëna që janë në rreshtin e parë të diapazonit të zgjedhur).

Në Excel 2007, titujt e boshteve vendosen në skedën e menysë PARAQITJA(Fig. 32).

Oriz. 32. Vendosja e emrave të boshteve të grafikut në Excel 2007

Për të marrë një model matematikor, është e nevojshme të vizatoni një vijë trendi në grafik. Në Excel 2003 dhe 2007, kliko me të djathtën mbi pikat e grafikut. Më pas në Excel 2003 do të shfaqet një skedë me një listë artikujsh nga të cilët zgjedhim SHTO LINE TRENDIM(Fig. 33).

Oriz. 33. SHTO LINE TRENDIM

Pasi të klikoni mbi artikullin SHTO LINE TRENDIM do të shfaqet një dritare LINJA E TRENDIT(Fig. 34). Në skedën TYPE, mund të zgjidhni llojet e mëposhtme të linjave: filtrim linear, logaritmik, eksponencial, eksponencial, polinom, linear.

Oriz. 34. Dritare LINJA E TRENDIT në Excel 2003

Në skedën PARAMETRAT(Fig. 35) shënoni kutinë pranë artikujve SHQYRONI EKUACIONIN NË DIAGRAM, atëherë një model matematikor i kësaj varësie do të shfaqet në grafik. Ne gjithashtu vendosim një shenjë kontrolli përpara artikullit TREGONI NË DIAGRAM VLERËN E BESUESHMËRISË SË PËRAFARJES (R^2). Sa më afër vlera e besueshmërisë së përafrimit të jetë 1, aq më afër kurba e përzgjedhur i afrohet pikave në grafik. Tjetra, klikoni në butonin Ne rregull. Një linjë trendi do të shfaqet në grafik, ekuacioni që i korrespondon dhe vlera e besueshmërisë së përafrimit.

Oriz. 35. Tab PARAMETRAT

Në Excel 2007, pasi të klikojmë me të djathtën në pikat e grafikut, do të shfaqet një listë e artikujve të menusë, nga e cila ZGJIDHNI SHTO LINE TRENDIM(Fig. 36).

Oriz. 36. SHTO LINE TRENDIM

Oriz. 37. Tab PARAMETRAT E LINJËS SË TRENDIMIT

Vendosni kutitë e kërkuara të kontrollit dhe klikoni butonin MBYLL.

Një linjë trendi do të shfaqet në grafik, ekuacioni që i korrespondon dhe vlera e besueshmërisë së përafrimit.

Duke parë çdo grup të dhënash të shpërndara me kalimin e kohës (seritë kohore), ne mund të identifikojmë vizualisht ngritjet dhe uljet e treguesve që ai përmban. Modeli i ulje-ngritjeve quhet trend, i cili mund të tregojë nëse të dhënat tona janë në rritje apo në rënie.

Ndoshta, do të filloj serinë e artikujve mbi parashikimin me gjënë më të thjeshtë - ndërtimin e një funksioni trendi. Për shembull, le të marrim të dhënat e shitjeve dhe të ndërtojmë një model që përshkruan varësinë e shitjeve në kohë.

Konceptet bazë

Unë mendoj se të gjithë janë të njohur me funksionin linear që nga shkolla, ai thjesht qëndron në themel të prirjes:

Y(t) = a0 + a1*t + E

Y është vëllimi i shitjeve, variabli që do ta shpjegojmë sipas kohës dhe nga i cili varet, pra Y (t);

t është numri i periudhës (numri serial i muajit), i cili shpjegon planin e shitjeve Y;

a0 është koeficienti i regresionit zero, i cili tregon vlerën e Y(t), në mungesë të ndikimit të faktorit shpjegues (t=0);

a1 është koeficienti i regresionit, i cili tregon se sa varet nga faktori ndikues t treguesi i studiuar i shitjeve Y;

E janë shqetësime të rastësishme që pasqyrojnë ndikimin e faktorëve të tjerë që nuk merren parasysh në model, përveç kohës t.

Ndërtesa model

Pra, ne e dimë vëllimin e shitjeve për 9 muajt e fundit. Ja se si duket bordi ynë:

Gjëja tjetër që duhet të bëjmë është të përcaktojmë koeficientët a0 Dhe a1 për të parashikuar vëllimin e shitjeve për muajin e 10-të.

Përcaktimi i koeficientëve të modelit

Ne ndërtojmë një tabelë. Horizontalisht shohim muajt në pritje, vertikalisht vëllimin e shitjeve:

Në Fletët e Google zgjidhni Redaktori i Diagramit -> Shtesë dhe kontrolloni kutinë pranë linjat e trendit. Në cilësimet zgjidhni Etiketa — Ekuacioni Dhe Shfaq R^2.

Nëse bëni gjithçka në MS Excel, atëherë klikoni me të djathtën në grafik dhe zgjidhni "Shto linjën e trendit" në menunë rënëse.

Si parazgjedhje, është ndërtuar një funksion linear. Në të djathtë, zgjidhni "Trego ekuacionin në diagram" dhe "Vlera e besueshmërisë së përafrimit R^2".

Ja çfarë ndodhi:

Në grafik shohim ekuacionin e funksionit:

y = 4856*x + 105104

Ai përshkruan vëllimin e shitjeve në varësi të numrit të muajit për të cilin duam të parashikojmë këto shitje. Aty pranë shohim koeficientin e përcaktimit R^2, i cili tregon cilësinë e modelit dhe sa mirë përshkruan shitjet tona (Y). Sa më afër 1 aq më mirë.

Unë kam R^2 = 0,75. Ky është një tregues mesatar, ai thotë se modeli nuk merr parasysh ndonjë faktor tjetër të rëndësishëm përveç kohës t, për shembull, mund të jetë sezonaliteti.

Ne parashikojmë

y = 4856*10 + 105104

Ne marrim 153664 shitje në muajin e ardhshëm. Nëse shtojmë një pikë të re në grafik, menjëherë shohim se R^2 është përmirësuar.

Kështu, ju mund t'i parashikoni të dhënat për disa muaj përpara, por pa marrë parasysh faktorë të tjerë, parashikimi juaj do të qëndrojë në linjën e trendit dhe nuk do të jetë aq informues sa do të dëshironit. Përveç kësaj, një parashikim afatgjatë i bërë në këtë mënyrë do të jetë shumë i përafërt.

Ju mund të rrisni saktësinë e modelit duke shtuar sezonalitetin në funksionin e trendit, gjë që do ta bëjmë në artikullin vijues.

Një linjë trendi përdoret për të ilustruar vizualisht tendencat e çmimeve. Elementi i analizës teknike është një paraqitje gjeometrike e vlerave mesatare të treguesit të analizuar.

Le të shohim se si të shtojmë një linjë trendi në një grafik në Excel.

Shtimi i një linje trendi në një grafik

Për shembull, le të marrim çmimet mesatare të naftës që nga viti 2000 nga burimet e hapura. Të dhënat për analizë do t'i fusim në tabelë:

Një linjë trendi në Excel është një grafik i një funksioni të përafërt. Pse është e nevojshme - për të bërë parashikime bazuar në të dhënat statistikore. Për këtë qëllim, është e nevojshme të zgjerohet linja dhe të përcaktohen vlerat e saj.

Nëse R2 = 1, atëherë gabimi i përafrimit është zero. Në shembullin tonë, zgjedhja e një përafrimi linear dha besim të ulët dhe një rezultat të dobët. Parashikimi do të jetë i pasaktë.

Kujdes!!! Një linjë trendi nuk mund të shtohet në llojet e mëposhtme të grafikëve dhe grafikëve:

• petal;
• rrethore;
• sipërfaqe;
• unazore;
• vëllimi;
• me akumulim.



Ekuacioni i linjës së trendit në Excel

Në shembullin e mësipërm, një përafrim linear u zgjodh vetëm për të ilustruar algoritmin. Siç tregohet nga vlera e besueshmërisë, zgjedhja nuk ishte plotësisht e suksesshme.

Ju duhet të zgjidhni llojin e ekranit që ilustron më saktë prirjen në hyrjen e përdoruesit. Le të hedhim një vështrim në opsionet.

Përafrim linear

Paraqitja e tij gjeometrike është një vijë e drejtë. Prandaj, një përafrim linear përdoret për të ilustruar një tregues që rritet ose zvogëlohet me një ritëm konstant.

Merrni parasysh numrin e kushtëzuar të kontratave të lidhura nga menaxheri për 10 muaj:

Bazuar në të dhënat në tabelën Excel, ne do të ndërtojmë një grafik shpërndarjeje (kjo do të ndihmojë në ilustrimin e llojit linear):

Zgjidhni grafikun - "shtoni një linjë trendi". Në parametrat, zgjidhni llojin linear. Ne shtojmë vlerën e besueshmërisë së përafrimit dhe ekuacionin e linjës së trendit në Excel (thjesht kontrolloni kutitë në fund të dritares "Parametrat").

Ne marrim rezultatin:

Shënim! Me llojin linear të përafrimit, pikat e të dhënave janë të vendosura sa më afër që të jetë e mundur me një vijë të drejtë. Kjo pamje përdor ekuacionin e mëposhtëm:

y = 4,503x + 6,1333

• ku 4.503 është treguesi i pjerrësisë;
• 6,1333 - kompensime;
• y është një sekuencë vlerash,
• x është numri i periudhës.

Vija e drejtë në grafik tregon një rritje të qëndrueshme të cilësisë së punës së menaxherit. Vlera e besueshmërisë së përafrimit është 0.9929, që tregon një marrëveshje të mirë midis vijës së drejtë të llogaritur dhe të dhënave fillestare. Parashikimet duhet të jenë të sakta.

Për të parashikuar numrin e kontratave të lidhura, për shembull, në periudhën e 11-të, duhet të zëvendësoni numrin 11 në vend të x në ekuacion. Gjatë përllogaritjeve mësojmë se në periudhën e 11-të ky drejtues do të lidhë 55-56 kontrata.

Linja e trendit eksponencial

Ky lloj do të jetë i dobishëm nëse vlerat e hyrjes ndryshojnë me një ritëm vazhdimisht në rritje. Përafrimi eksponencial nuk zbatohet në prani të karakteristikave zero ose negative.

Le të ndërtojmë një linjë trendi eksponencial në Excel. Le të marrim për shembull vlerat e kushtëzuara të furnizimit të dobishëm me energji elektrike në rajonin X:

Ne ndërtojmë një tabelë. Shtoni një vijë eksponenciale.

Ekuacioni ka formën e mëposhtme:

y = 7,6403е^-0,084x

• ku 7.6403 dhe -0.084 janë konstante;
• e është baza e logaritmit natyror.

Indeksi i besueshmërisë së përafrimit ishte 0.938 - kurba korrespondon me të dhënat, gabimi është minimal, parashikimet do të jenë të sakta.

Regjistrohu Trendline në Excel

Përdoret për ndryshimet e mëposhtme në tregues: së pari, një rritje ose ulje e shpejtë, pastaj stabilitet relativ. Kurba e optimizuar përshtatet mirë me këtë "sjellje" të sasisë. Trendi logaritmik është i përshtatshëm për parashikimin e shitjeve të një produkti të ri që sapo po prezantohet në treg.

Në fazën fillestare, detyra e prodhuesit është të rrisë bazën e klientëve. Kur produkti ka blerësin e vet, ai duhet të mbahet, të shërbehet.

Le të ndërtojmë një grafik dhe të shtojmë një linjë prirje logaritmike për të parashikuar shitjet e një produkti të kushtëzuar:

R2 është afër në vlerë me 1 (0.9633), që tregon gabimin minimal të përafrimit. Ne do të parashikojmë vëllimet e shitjeve në periudhat pasuese. Për ta bërë këtë, ju duhet të zëvendësoni numrin e periudhës në ekuacion në vend të x.

Për shembull:

Periudha	14	15	16	17	18	19	20
Parashikim	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

Për të llogaritur shifrat e parashikimit është përdorur formula e mëposhtme: =272.14*LN(B18)+287.21. Ku B18 është numri i periudhës.

Trendline polinomiale në Excel

Kjo kurbë ka variabla rritëse dhe zbritëse. Për polinomet (polinomet), shkalla përcaktohet (nga numri i vlerave maksimale dhe minimale). Për shembull, një ekstrem (minimumi dhe maksimal) është shkalla e dytë, dy ekstreme janë shkalla e tretë, tre janë shkalla e katërt.

Trendi polinomial në Excel përdoret për të analizuar një grup të madh të dhënash për një vlerë të paqëndrueshme. Le të shohim shembullin e grupit të parë të vlerave (çmimet e naftës).

Për të marrë një vlerë të tillë të besueshmërisë së përafrimit (0.9256), më duhej të vendosja shkallën e 6-të.

Por një prirje e tillë ju lejon të bëni parashikime pak a shumë të sakta.

Çfarë duhet të bëni nëse nuk ka matje kohore për vëllime/madhësi të caktuara të produkteve? Apo numri i matjeve është i pamjaftueshëm dhe nuk mund të bëhen vëzhgime shtesë në të ardhmen e afërt? Mënyra më e mirë për të zgjidhur këtë problem është ndërtimi i varësive të llogaritura (ekuacionet e regresionit) duke përdorur linjat e trendit në MS Excel.

Konsideroni një situatë reale: në një depo, për të përcaktuar sasinë e kostove të punës për mbledhjen në kuti të një porosie, u kryen vëzhgime kronometrike. Rezultatet e këtyre vëzhgimeve janë paraqitur në tabelën 1 më poshtë.

Më pas, u bë e nevojshme të përcaktohet koha e shpenzuar për marrjen e 0,6 dhe 0,9 m3 mallra/porosi. Për shkak të pamundësisë për të kryer studime shtesë kronometrike, koha e shpenzuar për zgjedhjen e këtyre vëllimeve të rendit është llogaritur duke përdorur ekuacionet e regresionit në MS Excel. Për ta bërë këtë, tabela 1 u konvertua në tabelën 2.

Zgjedhja e parcelës së shpërndarjes, fig. një

Hapi tjetër: kursori i miut u vendos në një nga pikat e grafikut dhe me butonin e djathtë të miut u thirr menyja e kontekstit, në të cilën u zgjodh artikulli: "Shto linjën e trendit" (Fig. 2).

Shtimi i një linje trendi, fig. 2

Në dritaren e konfigurimit të formatit të linjës së trendit që u shfaq (Fig. 3) u zgjodhën në mënyrë sekuenciale: lloji i linjës lineare/ligj i fuqisë dhe kutitë e kontrollit u vendosën për pikat e mëposhtme: "tregoni ekuacionin në diagram" dhe "vendosni në diagram vlerën e besueshmërisë së përafrimit (R^2)" (koeficienti i përcaktimit).

Formati i linjës së trendit, fig. 3

Si rezultat, grafikët e paraqitur në fig. 4 dhe 5.

Varësia e llogaritur lineare, fig. 4

Varësia e llogaritur e fuqisë, fig. pesë

Një analizë vizuale e grafikëve tregon qartë afërsinë e varësive të marra. Përveç kësaj, vlera e besueshmërisë së përafrimit (R^2), e cila quhet edhe koeficienti i përcaktimit, në rastin e të dy varësive është e njëjta vlerë prej 0.97. Dihet se sa më afër koeficientit të përcaktimit të jetë 1, aq më shumë linja e trendit korrespondon me realitetin. Mund të thuhet gjithashtu se ndryshimi në kohën e shpenzuar për përpunimin e porosisë është 97% për shkak të një ndryshimi në sasinë e mallrave. Prandaj, në këtë rast, nuk ka rëndësi: e cila llogariti varësinë për të zgjedhur si kryesoren për llogaritjen e mëvonshme të kostove të kohës.

Le të marrim si kryesore - një varësi lineare të llogaritur. Pastaj vlerat e kohës së shpenzuar në varësi të sasisë së mallrave do të përcaktohen me formulën: y = 54,511x + 0,1489. Rezultatet e këtyre llogaritjeve për sasinë e mallrave, për të cilat më parë janë kryer vëzhgime kronometrike, janë paraqitur në tabelën 3 më poshtë.

Le të përcaktojmë devijimin mesatar të kostove kohore të llogaritura nga ekuacioni i regresionit nga kostot kohore të llogaritura nga të dhënat e vëzhgimeve kronometrike: (-0,05+0,10-0,05+0,01)/4=0,0019. Kështu, kostot kohore të llogaritura sipas ekuacionit të regresionit ndryshojnë nga kostot kohore të llogaritura sipas të dhënave të vëzhgimeve kronometrike vetëm me 0,19%. Mospërputhja e të dhënave është e papërfillshme.

Sipas formulës: y = 54,511x + 0,1489, do të vendosim kostot kohore për sasinë e mallrave për të cilat nuk janë kryer më parë vëzhgime kronometrike. (tabela 4).

Kështu, ndërtimi i varësive të llogaritura duke përdorur linjat e trendit në MS Excel - kjo është një mënyrë e shkëlqyer për të përcaktuar kohën e shpenzuar për operacione që, për arsye të ndryshme, nuk mbuloheshin nga vëzhgimet kronometrike.

Kryerja e detyrave për vizatimin e një linje trendi dallohet nga fakti se të dhënat fillestare mund të jenë një grup numrash që nuk lidhen me njëri-tjetrin.

Parashikimi në një orar të rregullt është i pamundur, pasi koeficienti i tij i determinizmit (R^2) do të jetë afër zeros.

Kjo është arsyeja pse përdoren funksione të veçanta.

Tani do t'i ndërtojmë, konfigurojmë dhe analizojmë ato.

Versioni i ndërtimit të lehtë

Procesi i ndërtimit të një linje trendi përbëhet nga tre faza: futja e të dhënave burimore në Excel, vizatimi i një grafiku, zgjedhja e një linje trendi dhe parametrat e saj.

Le të fillojmë me futjen e të dhënave.

1. Krijo një tabelë në Excel me të dhënat burimore.

(Figura 1)

2. Zgjidhni qelizat B3:B17 dhe shkoni te skeda "Fut" dhe zgjidhni "Grafiku".

(Figura 2)

3. Pasi të jetë ndërtuar grafiku, mund të shtoni titrat dhe një titull.

Së pari, kliko me të majtën në kufirin e grafikut për ta zgjedhur atë.

Pastaj shkoni te skeda "Designer" dhe zgjidhni "Layout 1".

(Figura 3)

4. Le të kalojmë në ndërtimin e një linje trendi. Për ta bërë këtë, zgjidhni përsëri grafikun dhe shkoni te skeda "Layout".

(Figura 4)

5. Klikoni në butonin "Trend Line" dhe zgjidhni "përafrim linear" ose "përafrim eksponencial".

(Figura 5)

Kështu ndërtuam linjën kryesore të trendit, e cila mund të mos korrespondojë me realitetin.

Ky është rezultati ynë i ndërmjetëm.

(Figura 6)

Dhe kështu do të jetë e nevojshme të rregulloni parametrat e linjës sonë të trendit ose të zgjidhni një funksion tjetër.

Versioni profesional: zgjedhja e linjës së trendit dhe cilësimet e parametrave

6. Klikoni në butonin "Trend line" dhe zgjidhni "Parametrat shtesë dhe linjat e trendit".

(Figura 7)

7. Në dritaren "Formati i linjës së trendit", kontrollojmë kutinë pranë "vendosni në grafik vlerën e besimit të përafrimit R ^ 2 dhe klikoni butonin "mbyll".

Ne shohim në diagram koeficientin R^2= 0,6442

(Figura 8)

8. Ktheni ndryshimet. Zgjidhni grafikun, klikoni në skedën "Layout", butonin "Trend line" dhe zgjidhni "Asnjë".

9. Shkoni te dritarja "Trend Line Format", por për të zgjedhur vijën e trendit "Polynomial", ndryshoni shkallën, duke arritur koeficientin R ^ 2 = 0.8321.

(Figura 9)

Parashikim

Nëse duhet të hamendësojmë se cilat të dhëna mund të merren në matjen e radhës, në dritaren "Formati i linjës së trendit", tregojmë numrin e periudhave për të cilat është bërë parashikimi.

(Figura 10)

Në bazë të parashikimit, mund të supozojmë se më 25 janar numri i pikëve të fituara do të ishte nga 60 në 70.

Prodhimi

Dhe në përfundim, nëse jeni të interesuar për formulën me të cilën ndërtohet trendi, në kutinë "Formati i linjës së trendit", kontrolloni kutinë pranë "tregoni ekuacionin në grafik".

Tani ju e dini se si ta përfundoni detyrën dhe të vizatoni një linjë trendi, edhe në një program si excel 2010.

Bëj pyetje, mos ki turp.