PëRAFRIMI I EXCEL

Ndër metodat e ndryshme të parashikimit është e pamundur të mos dallojmë përafrimin. Me ndihmën e saj, ju mund të bëni llogaritjet e përafërta dhe të llogarisni treguesit e planifikuar duke zëvendësuar objektet origjinale me ato më të thjeshta. Në Excel, ekziston edhe mundësia e përdorimit të kësaj metode për parashikimin dhe analizën. Le të shohim se si kjo metodë mund të aplikohet në programin e specifikuar me mjete të inkorporuara.

Ekzekutimi i përafrimit

Emri i kësaj metode vjen nga fjala latine proxima - "më e afërt". Është një përafrim duke thjeshtuar dhe zbutur treguesit e njohur, duke i ndërtuar ato në një trend dhe është baza e tij. Por kjo metodë mund të përdoret jo vetëm për parashikimin, por edhe për hulumtimin e rezultateve ekzistuese. Në fund të fundit, përafrimi është, në fakt, një thjeshtësim i të dhënave origjinale, dhe një version i thjeshtuar është më i lehtë për t'u eksploruar.

Mjeti kryesor me të cilin zbutja është kryer në Excel është ndërtimi i linjës së trendit. Linja e fundit është se, bazuar në treguesit e disponueshëm, orari i funksionit është i përfunduar për periudhat e ardhshme. Qëllimi kryesor i vijës së prirjes, pasi nuk është e vështirë të mendosh, po bën parashikime ose identifikon një prirje të përgjithshme.

Por mund të ndërtohet duke përdorur një nga pesë llojet e përafrimit:

linear;
eksponenciale;
logaritmike;
polinomi;
Të energjisë.

Konsideroni secilën prej opsioneve në detaje më vete.

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

Metoda 1: Zbutja lineare

Para së gjithash, le të shqyrtojmë përafrimin më të thjeshtë, domethënë, duke përdorur një funksion linear. Ne do të ndalemi në të në më shumë detaje, meqë kemi paraqitur pikat e përgjithshme që janë karakteristike për metoda të tjera, dmth. Komplotimin dhe disa nuanca të tjera që nuk do të ndalemi kur të shqyrtojmë mundësitë e mëvonshme.

Para së gjithash, ne do të ndërtojmë një grafik në bazë të të cilit do të kryejmë procedurën zbutëse. Për të ndërtuar një grafik, ne marrim një tabelë në të cilën tregohen mujore njësitë e kostos së prodhimit të prodhuar nga një ndërmarrje dhe fitimi korrespondues në një periudhë të caktuar. Funksioni grafik që ne ndërtojmë do të reflektojë varësinë e rritjes së fitimit në uljen e kostos së prodhimit.

Për të ndërtuar grafikun, para së gjithash, zgjidhni kolonat "Kostoja njësi e prodhimit" dhe "Fitimi". Pas kësaj lëvizje në tab "Fut". Tjetër në shiritin në bllokun e "Diagrameve" toolbox klikoni mbi butonin "Spot". Në listën që hapet, zgjidhni emrin "Dot me kthesa të lëmuara dhe shenja". Është ky lloj i tabelave që është më i përshtatshëm për të punuar me linjën e trendit dhe për këtë arsye, për aplikimin e metodës së përafrimit në Excel.

Orari i ndërtuar.

Për të shtuar një vijë trendi, zgjidhni atë duke klikuar butonin e djathtë të mausit. Shfaqet një meny kontekst. Zgjidhni një artikull në të "Shto linjë prirjeje ...".
Ka një mundësi tjetër për ta shtuar atë. Në një grup shtesë të skedave në shirit "Duke punuar me grafikët" lëviz në tab "Layout". Tjetra në kutinë e veglave "Analiza" klikoni mbi butonin "Linja e Trendit". Hapet një listë. Meqenëse ne duhet të aplikojmë një përafrim linear, nga pozicionet e paraqitura që ne zgjedhim "Përafrim linear".

Nëse, megjithatë, keni zgjedhur opsionin e parë të veprimeve me shtesë përmes menutë kontekstuale, atëherë do të hapet dritarja e formatit.
Në bllokun e parametrave "Ndërtimi i linjës së trendit (përafrim dhe zbutje)" vendosni kalimin në pozitë "Linear".
Nëse dëshironi, mund të vendosni një shenjë pranë pozicionit "Trego ekuacionin në tabelë". Pas kësaj, diagrami do të shfaqë ekuacionin e zbutjes së funksioneve.
Gjithashtu në rastin tonë, për krahasimin e opsioneve të ndryshme të përafrimit, është e rëndësishme të kontrolloni kutinë "Vendosni në tabelë vlerën e një përafrimi të besueshëm (R ^ 2)". Ky tregues mund të ndryshojë nga 0 deri në 1. Sa më i lartë është, përafrimi më i mirë (më i besueshëm). Besohet se kur vlera e këtij treguesi 0,85 dhe zbutja më e lartë mund të konsiderohet e besueshme, dhe nëse shifra është më e ulët atëherë - jo.
Pasi të keni pasur të gjitha cilësimet e mësipërme. Ne shtypim butonin "Close"të vendosura në pjesën e poshtme të dritares.

Siç mund ta shihni, linja e trendit është hartuar në tabelë. Në rastin e përafrimit linear, ajo është e shënuar me një vijë të zezë të drejtë. Ky lloj zbutje mund të zbatohet në rastet më të thjeshta, kur të dhënat ndryshojnë shumë shpejt dhe varësia e vlerës së funksionit në argument është e qartë.

Zbutja, e cila përdoret në këtë rast, përshkruhet nga formula e mëposhtme:

y = ax + b

Në rastin tonë të veçantë, formula merr formën e mëposhtme:

y = -0.1156x + 72.255

Madhësia e saktësisë së përafrimit është e barabartë me ne 0,9418, i cili është një rezultat mjaft i pranueshëm, duke përshkruar zbutjen si të besueshme.

Metoda 2: Përafrimi eksponencial

Tani le të shqyrtojmë llojin e përafrimit eksponencial në Excel.

Në mënyrë që të ndryshoni llojin e linjës së trendit, zgjidhni atë duke klikuar butonin e djathtë të miut dhe në drop-down menu zgjidhni artikullin "Format i linjës së trendit ...".

Pas kësaj, nis dritarja e formatit që tashmë është e njohur për ne. Në bllokun për zgjedhjen e llojit të përafrimit, vendosni kalimin në "Eksponenciale". Cilësimet e mbetura mbeten të njëjta si në rastin e parë. Kliko në butonin "Close".

Pas kësaj, vija e prirjes do të përpilohet. Siç mund ta shihni, kur përdorni këtë metodë, ajo ka një formë paksa të lakuar. Niveli i besimit është 0,9592, e cila është më e lartë se kur përdorni përafrimin linear. Metoda eksponenciale përdoret më së miri kur vlerat e parë ndryshojnë shpejt dhe pastaj marrin një formë të ekuilibruar.

Pamja e përgjithshme e funksionit zbutës është si vijon:

y = jetë ^ x

ku e - kjo është baza e logaritmit natyror.

Në rastin tonë të veçantë, formula mori formën e mëposhtme:

y = 6282.7 * e ^ (- 0.012 * x)

Metoda 3: Zbutja e regjistrit

Tani është radha të shqyrtojmë metodën e përafrimit logaritmik.

Në të njëjtën mënyrë si në kohën e mëparshme, nëpërmjet menysë së kontekstit, fillo dritaren e formatit të linjës së trendit. Set kaloni në pozitë "Logaritmik" dhe klikoni mbi butonin "Close".

Ekziston një procedurë e ndërtimit të vijës së prirjes me një përafrim logaritmik. Ashtu si në rastin e mëparshëm, ky opsion është më i mirë për t'u përdorur kur të dhënat fillimisht ndryshojnë shpejt dhe pastaj marrin një pamje të ekuilibruar. Siç mund ta shihni, niveli i besueshmërisë është 0.946. Kjo është më e lartë se kur përdoret metoda lineare, por më e ulët se cilësia e vijës së prirjes me zbutjen eksponenciale.

Në përgjithësi, formula zbutëse duket kështu:

y = a * ln (x) + b

ku ln është madhësia e logaritmit natyror. Prandaj emri i metodës.

Në rastin tonë formula merr formën e mëposhtme:

y = -62,81ln (x) +404,96

Metoda 4: zbutja polinome

Është koha të merret në konsideratë metoda e zbutjes polinomiale.

Shkoni te dritarja e formatit të vijës së trendit, siç keni bërë tashmë më shumë se një herë. Në bllok "Ndërtimi i linjës së trendit" vendosni kalimin në pozitë "Polinom". Në të djathtë të këtij artikulli është një fushë "Diplomë". Kur zgjedhni "Polinom" ajo bëhet aktive. Këtu mund të specifikoni ndonjë vlerë të energjisë nga 2 (të paracaktuar) në 6. Ky tregues përcakton numrin e maksimumeve dhe minimumit të funksionit. Kur vendosni një polinom të shkallës së dytë, përshkruhet vetëm një maksimum dhe kur është instaluar një polinom i shkallës së gjashtë, mund të përshkruhen deri në pesë maksimume. Për të filluar, lëmë parametrat e parazgjedhur, domethënë ne specifikojmë shkallën e dytë. Cilësimet e mbetura mbeten të njëjta sikurse i kemi vendosur në metodat e mëparshme. Ne shtypim butonin "Close".

Linja e trendit duke përdorur këtë metodë është ndërtuar. Siç mund ta shihni, është edhe më e lakuar se kur përdorni përafrimin eksponencial. Niveli i besimit është më i lartë se sa me ndonjë nga metodat e përdorura më parë, dhe është 0,9724.
Kjo metodë mund të zbatohet me sukses nëse të dhënat ndryshojnë vazhdimisht. Funksioni që përshkruan këtë lloj zbutjeje duket kështu:
y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ n
Në rastin tonë formula mori formën e mëposhtme:
y = 0,0015 * x ^ 2-1,7202 * x + 507,01
Tani le të ndryshojmë shkallën e polinomeve për të parë nëse rezultati do të jetë i ndryshëm. Ne kthehemi në dritaren e formatit. Lloji i përafrimit është lënë polinom, por para tij në dritaren e shkallës ne vendosim vlerën maksimale të mundshme - 6.

Siç mund ta shihni, pas kësaj, linja jonë e prirjes mori formën e një kurbë të theksuar, në të cilën numri i të parave është gjashtë. Niveli i besimit u rrit edhe më shumë, duke e bërë 0,9844.

Formula që përshkruan këtë lloj zbutje, mori formën e mëposhtme:

y = 8E-08x ^ 6-0,0003x ^ 5 + 0,3725x ^ 4-269,33x ^ 3 + 109525x ^ 2-2E + 07x + 2E + 09

Metoda 5: Zbutja e energjisë

Në përfundim, konsideroni metodën e përafrimit të fuqisë në Excel.

Shkoni te dritarja "Format i linjës së trendit". Vendosni kalimin e shikimit të zbutjes në pozicionin "Diplomë". Duke treguar ekuacionin dhe nivelin e besimit, si gjithmonë, lini atë. Ne shtypim butonin "Close".

Programi formon një linjë trendi. Siç mund ta shihni, në rastin tonë, kjo është një vijë me një kthesë të lehtë. Niveli i besimit është 0,9618që është një figurë shumë e lartë. Nga të gjitha metodat e përshkruara më sipër, niveli i besimit ishte më i lartë vetëm kur përdoret metoda polinom.

Kjo metodë përdoret në mënyrë efektive në rastet e ndryshimeve intensive në të dhënat e funksionit. Është e rëndësishme të theksohet se ky opsion është i zbatueshëm vetëm nëse funksioni dhe argumenti nuk pranojnë vlera negative ose zero.

Formula e përgjithshme që përshkruan këtë metodë është si vijon:

y = bx ^ n

Në rastin tonë të veçantë, duket kështu:

y = 6E + 18x ^ (- 6.512)

Siç mund ta shikoni, kur përdorni të dhënat specifike që kemi përdorur për shembull, metoda e përafrimit polinomi me polinom në shkallën e gjashtë (0,9844), niveli më i ulët i besimit në metodën lineare (0,9418). Por kjo nuk do të thotë aspak se e njëjta prirje do të jetë kur përdoret shembuj të tjerë. Jo, niveli i efikasitetit të metodave të mësipërme mund të ndryshojë ndjeshëm, varësisht nga lloji i caktuar i funksionit për të cilin do të ndërtohet linja e trendit. Prandaj, nëse metoda e zgjedhur është më efektive për këtë funksion, kjo nuk do të thotë aspak se do të jetë gjithashtu optimale në një situatë tjetër.

Nëse nuk mund të përcaktoni menjëherë, bazuar në rekomandimet e mësipërme, çfarë lloj përafrimi do të përshtatet në mënyrë specifike në rastin tuaj, atëherë ka kuptim të provoni të gjitha metodat. Pas ndërtimit të linjës së trendit dhe shikimit të nivelit të besueshmërisë, ju mund të zgjidhni opsionin më të mirë.