Mjetet e parashikimit në Microsoft Excel

Parashikimi është një element shumë i rëndësishëm i pothuajse çdo fushë veprimtarie, nga ekonomia në inxhinieri. Ekziston një numër i madh i softverëve që specializohen në këtë fushë. Fatkeqësisht, jo të gjithë përdoruesit e dinë që procesori i zakonshëm i spreadsheet Excel ka në mjetet e tij arsenal për parashikimin, të cilat nuk janë shumë inferiorë ndaj programeve profesionale në efikasitetin e tyre. Le të zbulojmë se cilat janë këto mjete dhe si të bëjmë një parashikim në praktikë.

Procedura e parashikimit

Qëllimi i çdo parashikimi është të identifikojë trendin aktual, dhe të përcaktojë rezultatin e pritur në lidhje me objektin e studiuar në një moment të caktuar kohor në të ardhmen.

Metoda 1: linja e trendit

Një nga llojet më të njohura të parashikimit grafik në Excel është ekstrapolimi duke ndërtuar një linjë prirjeje.

Le të përpiqemi të parashikojmë sasinë e fitimit të ndërmarrjes në 3 vjet në bazë të të dhënave mbi këtë tregues për 12 vitet e mëparshme.

1. Ne ndërtojmë një grafik të varësisë bazuar në të dhënat tabelare që përbëhen nga argumentet dhe vlerat e funksionit. Për ta bërë këtë, zgjidhni zonën e tryezës dhe më pas, duke qenë në skedë "Insert", klikoni në ikonën e llojit të dëshiruar të grafikut, i cili ndodhet në bllok "Diagramet". Pastaj zgjedhim llojin e përshtatshëm për një situatë specifike. Shtë më mirë të zgjidhni një tabelë shpërndarëse. Ju mund të zgjidhni një pamje tjetër, por pastaj, në mënyrë që të dhënat të shfaqen në mënyrë korrekte, do të duhet të kryeni redaktim, në veçanti, të hiqni rreshtin e argumentit dhe të zgjidhni një shkallë tjetër të boshtit horizontale.



2. Tani duhet të ndërtojmë një vijë prirjeje. Ne klikojmë me të djathtën në secilën nga pikat në diagram. Në menunë e aktivizuar të kontekstit, ndaloni zgjedhjen në artikull Shtoni Linjën e Trendit.



3. Hapet dritarja e formatimit të linjës së trendit. Në të mund të zgjidhni një nga gjashtë llojet e përafrimit:
   • linear;
   • logaritmike;
   • tregues;
   • Degrees;
   • polinom;
   • Filtrimi linear.

   Le të fillojmë duke zgjedhur një përafrim linear.

   Në bllokun e cilësimeve "Parashikimi" në fushë "Përpara në" vendosni numrin "3,0", pasi që duhet të bëjmë një parashikim për tre vjet më parë. Përveç kësaj, mund të kontrolloni kutinë pranë cilësimeve. "Trego ekuacionin në diagram" dhe "Vendosni vlerën e besimit të përafrimit (R ^ 2) në diagram". Treguesi i fundit tregon cilësinë e linjës së trendit. Pasi të bëhen cilësimet, klikoni në butonin "Close".



4. Linja e trendit është ndërtuar dhe nga ajo mund të përcaktojmë shumën e përafërt të fitimit në tre vjet. Siç e shohim, deri në atë kohë duhet të jetë mbi 4500 mijë rubla. faktor R2Siç u përmend më lart, tregon cilësinë e linjës së trendit. Në rastin tonë, vlera R2 përbën 0,89. Sa më i lartë të jetë koeficienti, aq më i lartë është besueshmëria e linjës. Vlera e saj maksimale mund të jetë e barabartë 1. Në përgjithësi pranohet që me një koeficient më lart 0,85 linja e trendit është e besueshme.



5. Nëse niveli i besimit nuk ju përshtatet, atëherë mund të ktheheni në dritaren e formatit të linjës së trendit dhe të zgjidhni çdo lloj tjetër të përafrimit. Mund të provoni të gjitha opsionet në dispozicion për të gjetur sa më të sakta.

   

   Duhet të theksohet se parashikimi duke përdorur ekstrapolimin përmes vijës së trendit mund të jetë efektiv nëse periudha e parashikimit nuk kalon 30% të bazës së analizuar të periudhave. Kjo do të thotë, kur analizojmë një periudhë 12 vjeç, ne nuk mund të bëjmë një parashikim efektiv për më shumë se 3-4 vjet. Por edhe në këtë rast, do të jetë relativisht e besueshme nëse gjatë kësaj kohe nuk do të ketë forcë madhore ose, përkundrazi, rrethana jashtëzakonisht të favorshme, të cilat nuk ishin në periudhat e mëparshme.

mësim: Si të ndërtoni një linjë trendesh në Excel

Metoda 2: operatori PARATHNIE

Ekstrapolimi për të dhënat tabelare mund të bëhet përmes funksionit standard të Excel PARASHIKIMI. Ky argument i përket kategorisë së mjeteve statistikore dhe ka sintaksën e mëposhtme:

= PREDICT (X; i njohur_y_values; i njohur_x_values)

"X" është një argument për të cilin duhet të përcaktohet vlera e funksionit. Në rastin tonë, argumenti do të jetë viti për të cilin duhet të bëhet parashikimi.

Vlerat e njohura y - baza e vlerave të funksionit të njohura. Në rastin tonë, roli i saj luhet nga sasia e fitimit për periudhat e mëparshme.

Vlerat e njohura x janë argumentet për të cilat korrespondojnë vlerat e njohura të funksionit. Në rolin e tyre, kemi numrin e viteve për të cilat u mblodhën informacionet mbi fitimin e viteve të kaluara.

Natyrisht, argumenti nuk ka pse të jetë kohëzgjatja. Për shembull, mund të jetë temperatura, dhe vlera e funksionit mund të jetë niveli i zgjerimit të ujit kur nxehet.

Gjatë llogaritjes së kësaj metode, përdoret metoda e regresionit linear.

Le të shohim nuancat e përdorimit të operatorit PARASHIKIMI në një shembull konkret. Merrni gjithë tryezën. Do të duhet të dimë parashikimin e fitimit për vitin 2018.

1. Zgjidhni një qelizë boshe në fletë ku planifikoni të shfaqni rezultatin e përpunimit. Klikoni në butonin "Vendos funksionin".



2. Hapet Wizard Feature. Në kategori "Statistikore" zgjidhni emrin "Parashikimi"dhe pastaj kliko në butonin "OK".



3. Fillon dritarja e argumentit. Në fushë "X" tregoni vlerën e argumentit në të cilin dëshironi të gjeni vlerën e funksionit. Në rastin tonë, ky është 2018. Prandaj, ne shkruajmë "2018". Por është më mirë ta tregoni këtë tregues në një qelizë në çarçafë dhe në fushë "X" thjesht jepni një lidhje për të. Kjo do të lejojë që në të ardhmen të automatizohen llogaritjet dhe, nëse është e nevojshme, të ndryshojnë me lehtësi vitin.

   Në fushë Vlerat e njohura y specifikoni koordinatat e kolonës "Fitimi i ndërmarrjes". Kjo mund të bëhet duke vendosur kursorin në fushë, dhe pastaj duke mbajtur butonin e majtë të miut dhe duke theksuar kolonën përkatëse në fletë.

   Ngjashëm në fushë Vlerat e njohura x shkruani adresën e kolonës "Viti" me të dhëna për periudhën e kaluar.

   Pasi të jenë futur të gjitha informacionet, klikoni në butonin "OK".



4. Operatori llogarit bazuar në të dhënat e futura dhe tregon rezultatin në ekran. Për vitin 2018, planifikohet të përfitojë në rajonin prej 4,564,7 mijë rubla. Bazuar në tabelën që rezulton, ne mund të ndërtojmë një grafik duke përdorur mjetet e hartës, të diskutuara më lart.



5. Nëse e ndryshoni vitin në qelizën që është përdorur për të hyrë në argument, rezultati do të ndryshojë në përputhje me rrethanat, dhe orari do të azhurnohet automatikisht. Për shembull, sipas parashikimeve në vitin 2019, shuma e fitimit do të jetë 4637.8 mijë rubla.

Por mos harroni se, si me ndërtimin e linjës së trendit, periudha e kohës para periudhës së parashikimit nuk duhet të kalojë 30% të të gjithë periudhës për të cilën u grumbullua baza e të dhënave.

mësim: Nxjerrja në Excel

Metoda 3: Operatori TREND

Për parashikimin, mund të përdorni një funksion tjetër - TRENDI. I përket gjithashtu kategorisë së operatorëve statistikorë. Sintaksa e saj është shumë si sintaksë e mjeteve PARASHIKIMI dhe duket si kjo:

= TREND (vlerat e njohura_y; vlerat e njohura_x; new_values_x; [konst])

Siç mund ta shihni, argumentet Vlerat e njohura y dhe Vlerat e njohura x korrespondojnë plotësisht me elementë të ngjashëm të operatorit PARASHIKIMI, dhe argumenti "Vlera të reja x" argumenti i ndeshjeve "X" mjet i mëparshëm. Përveç kësaj, TRENDI ekziston një argument shtesë "Constant", por është opsionale dhe përdoret vetëm nëse ekzistojnë faktorë të vazhdueshëm.

Ky operator përdoret më efektivisht në prani të një varësie lineare të funksionit.

Le të shohim se si do të funksionojë ky mjet me të njëjtën grup të të dhënave. Për të krahasuar rezultatet, ne përcaktojmë pikën e parashikimit si 2019.

1. Ne caktojmë qelizën për të shfaqur rezultatin dhe për të drejtuar Wizard Feature në mënyrën e zakonshme. Në kategori "Statistikore" gjeni dhe nxjerrë në pah emrin "TREND". Klikoni në butonin "OK".



2. Hapet dritarja e Argumentit të Operatorit TRENDI. Në fushë Vlerat e njohura y me metodën e përshkruar më sipër ne futim koordinatat e kolonës "Fitimi i ndërmarrjes". Në fushë Vlerat e njohura x shkruani adresën e kolonës "Viti". Në fushë "Vlera të reja x" ne futim lidhjen për qelizën ku ndodhet numri i vitit për të cilin duhet të tregohet parashikimi. Në rastin tonë, ky është 2019. Fushë "Constant" lëre bosh. Klikoni në butonin "OK".



3. Operatori përpunon të dhënat dhe tregon rezultatin në ekran. Siç mund ta shihni, shuma e fitimit të parashikuar për vitin 2019, e llogaritur me metodën e varësisë lineare, do të jetë, si në metodën e llogaritjes së mëparshme, 4637.8 mijë rubla.

Metoda 4: Operatori i RRITJES

Një funksion tjetër që mund të përdoret për parashikimin në Excel është operatori GROWTH. Ajo gjithashtu i përket grupit statistikor të mjeteve, por, ndryshe nga ato të mëparshme, gjatë llogaritjes së tij, ai nuk përdor metodën e varësisë lineare, por atë eksponenciale. Sintaksa e këtij mjeti është si më poshtë:

= GRWTH (vlerat e njohura_y; vlerat e njohura_x; new_values_x; [konst])

Siç mund ta shihni, argumentet e këtij funksioni saktësisht përsërisin argumentet e operatorit TRENDI, kështu që ne nuk do të ndalemi në përshkrimin e tyre për herë të dytë, por menjëherë do të vazhdojmë me zbatimin praktik të këtij mjeti.

1. Ne zgjedhim qelizën për nxjerrjen e rezultatit dhe e quajmë atë në mënyrën e zakonshme Wizard Feature. Në listën e operatorëve statistikorë, kërkoni artikullin "Rritja", zgjidhni atë dhe klikoni në butonin "OK".



2. Dritarja e argumentit të funksionit të mësipërm aktivizohet. Vendosni të dhënat në fushat e kësaj dritare në të njëjtën mënyrë si i futëm ato në dritaren e argumenteve të operatorit TRENDI. Pas futjes së informacionit, klikoni në butonin "OK".



3. Rezultati i përpunimit të të dhënave shfaqet në monitor në qelizën e treguar më parë. Siç mund ta shihni, këtë herë rezultati është 4682.1 mijë rubla. Dallimet nga rezultatet e përpunimit të të dhënave të operatorit TRENDI të parëndësishme, por ato janë në dispozicion. Kjo për faktin se këto mjete përdorin metoda të ndryshme të llogaritjes: metoda e varësisë lineare dhe metoda e varësisë eksponenciale.

Metoda 5: Operator LINEAR

operator linear në llogaritjen përdor metodën lineare të përafrimit. Nuk duhet të ngatërrohet me metodën e varësisë lineare të përdorur nga mjeti. TRENDI. Sintaksa e saj është si më poshtë:

= LINE (vlerat e njohura_y; vlerat e njohura_x; new_values_x; [konst]; [statistikat])

Dy argumentet e fundit janë opsionale. Me dy të parat, ne jemi njohur me metodat e mëparshme. Por ju ndoshta keni vënë re se nuk ka asnjë argument në këtë funksion që tregon për vlera të reja. Fakti është se ky mjet përcakton vetëm ndryshimin në të ardhura për njësi të periudhës, e cila në rastin tonë është e barabartë me një vit, por duhet të llogarisim rezultatin total veç e veç, duke shtuar rezultatin e llogaritjes së operatorit në vlerën e fundit të fitimit linearherë numri i viteve.

1. Ne zgjedhim qelizën në të cilën do të kryhet llogaritja dhe drejtojmë funksionin Wizard. Zgjidhni emrin "Linear" në kategori "Statistikore" dhe klikoni në butonin "OK".



2. Në fushë Vlerat e njohura y, dritarja e hapur e argumenteve, shkruani koordinatat e kolonës "Fitimi i ndërmarrjes". Në fushë Vlerat e njohura x shkruani adresën e kolonës "Viti". Fushat e mbetura kanë mbetur bosh. Pastaj klikoni në butonin "OK".



3. Programi llogarit dhe shfaq vlerën e trendit linear në qelizën e zgjedhur.



4. Tani duhet të zbulojmë madhësinë e fitimit të parashikuar për vitin 2019. Vendosni shenjën "=" në ndonjë qelizë boshe në fletë. Ne klikojmë në qelizën që përmban shumën aktuale të fitimit për vitin e fundit të studiuar (2016). Ne vendosëm një shenjë "+". Tjetra, klikoni në qelizën që përmban trendin linear të llogaritur më parë. Ne vendosëm një shenjë "*". Meqenëse midis vitit të fundit të periudhës së studimit (2016) dhe vitit për të cilin dëshironi të bëni një parashikim (2019), qëndron një periudhë prej tre vjetësh, vendosim numrin në qelizë "3". Për të bërë një llogaritje klikoni në butonin hyj.

Siç mund ta shihni, marzhi i parashikuar i fitimit i llogaritur me metodën lineare të përafrimit në vitin 2019 do të arrijë në 4,614.9 mijë rubla.

Metoda 6: Operatori LGRFPPRIBLE

Mjeti i fundit që do të shikojmë do të jetë LOGEST. Ky operator kryen llogaritjet bazuar në metodën e përafrimit eksponencial. Sintaksa e saj ka strukturën e mëposhtme:

= LGRFPRIBLE (vlerat e njohura_y; vlerat e njohura_x; new_values_x; [konstatimi]; [statistikat])

Siç mund ta shihni, të gjitha argumentet përsërisin plotësisht elementët përkatës të funksionit të mëparshëm. Algoritmi i llogaritjes së parashikimit do të ndryshojë pak. Funksioni llogarit trendin eksponencial, i cili tregon se sa herë shuma e të ardhurave do të ndryshojë për një periudhë, d.m.th., për një vit. Do të duhet të gjejmë ndryshimin në fitim midis periudhës së fundit aktuale dhe asaj të parë të planifikuar, shumëzuar atë me numrin e periudhave të planifikuara (3) dhe shtoni në rezultat shumën e periudhës së fundit aktuale.

1. Në listën e operatorëve të Wizard Function, zgjidhni emrin "LOGEST". Klikoni në butonin "OK".



2. Fillon dritarja e argumentit. Në të, ne i futim të dhënat saktësisht siç kemi bërë, duke përdorur funksionin linear. Klikoni në butonin "OK".



3. Rezultati i prirjes eksponenciale llogaritet dhe shfaqet në qelizën e caktuar.



4. Ne vendosëm një shenjë "=" në një qelizë boshe. Hapni kllapat dhe zgjidhni qelizën që përmban vlerën e të ardhurave për periudhën e fundit aktuale. Ne vendosëm një shenjë "*" dhe zgjidhni qelizën që përmban trendin eksponencial. Ne vendosim një shenjë minus dhe përsëri klikoni në elementin në të cilin është vendosur vlera e të ardhurave për periudhën e fundit. Mbyllni kllapën dhe vozisni në karaktere "*3+" pa thonjëza. Përsëri, klikoni në të njëjtën qelizë që u zgjodh për herë të fundit. Për të kryer llogaritjen, klikoni në butonin hyj.

Shuma e parashikuar e fitimit në vitin 2019, e cila u llogarit me metodën e përafrimit eksponencial, do të arrijë në 4,639.2 mijë rubla, e cila përsëri nuk ndryshon shumë nga rezultatet e marra në llogaritjen e mëparshme.

mësim: Funksione të tjera statistikore në Excel

Zbuluam se si të bëjmë parashikime në programin Excel. Kjo mund të bëhet në mënyrë grafike përmes përdorimit të një linje trendi, dhe duke përdorur në mënyrë analitike një numër funksionesh të ndërtuara statistikore. Si rezultat i përpunimit të të dhënave identike nga këta operatorë, mund të merret një rezultat i ndryshëm. Por kjo nuk është për t'u habitur, pasi ata të gjithë përdorin metoda të ndryshme të llogaritjes. Nëse luhatja është e vogël, atëherë të gjitha këto mundësi të zbatueshme për një rast të veçantë mund të konsiderohen relativisht të besueshme.