PëRAFRIMI Në EXCEL

Send

Ndër metodat e ndryshme të parashikimit, nuk mund të veçohet përafrimi. Duke përdorur atë, ju mund të bëni vlerësime të përafërta dhe të llogaritni treguesit e planifikuar duke zëvendësuar objektet origjinale me ato më të thjeshta. Në Excel, ekziston gjithashtu mundësia e përdorimit të kësaj metode për parashikimin dhe analizën. Le të shohim se si kjo metodë mund të zbatohet në programin e specifikuar me mjete të integruara.

Kryerja përafrimi

Emri i kësaj metode vjen nga fjala latine proxima - "më e afërt" .Ashtron përafrimi me thjeshtësimin dhe zbutjen e treguesve të njohur, duke i ndërtuar ato në një trend që është baza e tij. Por kjo metodë mund të përdoret jo vetëm për parashikimin, por edhe për studimin e rezultateve ekzistuese. Në fund të fundit, përafrimi është, në të vërtetë, një thjeshtësim i të dhënave të burimit, dhe një version i thjeshtuar është më i lehtë për t’u eksploruar.

Mjeti kryesor me të cilin kryhet zbutja në Excel është ndërtimi i një linje trendi. Në fund të fundit është se, bazuar në treguesit ekzistues, një grafik i funksionit për periudhat e ardhshme po përfundon. Qëllimi kryesor i linjës së trendit, siç mund ta merrni me mend, është bërja e parashikimeve ose identifikimi i 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ë.

Ne e konsiderojmë secilën prej opsioneve më në detaje veç e veç.

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

Metoda 1: zbutje lineare

Para së gjithash, le të shohim opsionin më të thjeshtë të përafrimit, domethënë duke përdorur një funksion linear. Ne do të ndalemi në të në mënyrë më të detajuar, pasi do të përshkruajmë pikat e përgjithshme që janë karakteristike për metodat e tjera, përkatësisht, ndërtimin e një orari dhe disa nuanca të tjera, të cilat nuk do të ndalemi kur shqyrtojmë opsionet e mëposhtme.

Para së gjithash, ne do të ndërtojmë një grafik, në bazë të të cilit do të kryejmë procedurën e zbutjes. Për të ndërtuar një orar, marrim një tabelë në të cilën tregohet kostoja mujore e një njësie të prodhimit të prodhuar nga ndërmarrja dhe fitimi përkatës në një periudhë të caktuar. Funksioni grafik që do të ndërtojmë do të pasqyrojë varësinë e rritjes së fitimit nga ulja e kostos së prodhimit.

Për të komplotuar, para së gjithash, zgjidhni kolonat "Kostoja e njësisë" dhe "Fitimi". Pas kësaj, zhvendoseni në skedë "Insert". Tjetra, në shiritin në kutinë e veglave Charts, klikoni në butonin "Spot". Në listën që hapet, zgjidhni emrin "Vend me kthesa të buta dhe shënues". Shtë ky lloj grafiku që është më i përshtatshmi për të punuar me një linjë trendi, dhe për këtë arsye, për aplikimin e metodës së përafrimit në Excel.

Orari është ndërtuar.

Për të shtuar një linjë trendesh, zgjidhni atë duke klikuar butonin e djathtë të miut. Një menu konteksti shfaqet. Zgjidhni artikullin në të "Shtoni një linjë tendenci ...".
Ekziston një mundësi tjetër për ta shtuar atë. Në një grup shtesë të skedave në shirit "Puna me grafikët" zhvendoset në skedë "Layout". Më tej në bllokun e mjeteve "Analiza" klikoni në butonin Linja e Trendit. Hapet lista. Meqenëse duhet të aplikojmë një përafrim linear, ne zgjedhim nga pozicionet e paraqitura "Përafrimi linear".

Nëse megjithatë keni zgjedhur opsionin e parë me shtimin e veprimeve përmes menusë së kontekstit, do të hapet një dritare format.
Në bllokun e parametrave "Ndërtimi i një linje trendi (përafrimi dhe zbutja)" vendosni kaloni në pozicion "Linear".
Nëse dëshironi, mund të kontrolloni kutinë pranë pozicionit "Trego ekuacionin në diagram". Pas kësaj, ekuacioni i funksionit të zbutjes do të shfaqet në diagram.
Gjithashtu në rastin tonë, për të krahasuar opsione të ndryshme të përafrimit, është e rëndësishme të kontrolloni kutinë pranë "Vendoseni në tabelë vlerën e përafrimit të besueshëm (R ^ 2)". Ky tregues mund të ndryshojë nga 0 në 1. Sa më i lartë të jetë, përafrimi është më i mirë (më i besueshëm). Besohet se me vlerën e këtij treguesi 0,85 dhe më e lartë, zbutja mund të konsiderohet e besueshme, por nëse treguesi është më i ulët, atëherë jo.
Pasi të keni përfunduar të gjitha cilësimet e mësipërme. Klikoni në butonin "Close"e vendosur në fund të dritares.

Siç mund ta shihni, linja e trendit është vizatuar në tabelë. Me një përafrim linear, tregohet nga një vijë e drejtë e zezë. Lloji i specifikuar i zbutjes mund të përdoret në rastet më të thjeshta kur të dhënat ndryshojnë mjaft shpejt dhe varësia e vlerës së funksionit nga argumenti është e dukshme.

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

y = sëpata + b

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

y = -0.1156x + 72.255

Vlera e saktësisë së përafrimit është e barabartë me 0,9418, i cili është një rezultat mjaft i pranueshëm që karakterizon zbutjen si të besueshme.

Metoda 2: Përafrimi eksponencial

Tani le të shohim llojin eksponencial të përafrimit në Excel.

Për të ndryshuar llojin e linjës së trendit, zgjidhni atë duke klikuar butonin e djathtë të miut dhe zgjidhni artikullin në menunë që shfaqet "Formati i linjës së trendit ...".

Pas kësaj, fillon dritarja e formatit të njohur. Në bllokun e përzgjedhjes së tipit të përafrimit, vendosni kaloni te "Eksponenciale". Cilësimet e mbetura do të mbeten të njëjta si në rastin e parë. Klikoni në butonin "Close".

Pas kësaj, linja e trendit do të vizatohet në tabelë. Siç mund ta shihni, kur përdorni këtë metodë, ajo ka një formë pak të lakuar. Në këtë rast, 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 ndryshojnë shpejt dhe më pas marrin një formë të ekuilibruar.

Forma e përgjithshme e funksionit të zbutjes është si më poshtë:

y = be ^ x

ku e është baza e logaritmit natyror.

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

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

Metoda 3: zbutja logaritmike

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

Në të njëjtën mënyrë si herën e kaluar, ne fillojmë dritaren e formatit të linjës së trendit përmes menusë së kontekstit. Vendosni kaloni në pozicion "Logaritmik" dhe klikoni në butonin "Close".

Ekziston një procedurë për ndërtimin e një linje trendi me një përafrim logaritmik. Si në rastin e mëparshëm, kjo mundësi përdoret më së miri kur fillimisht të dhënat ndryshojnë shpejt dhe më pas marrin një pamje të ekuilibruar. Siç mund ta shihni, niveli i besimit është 0.946. Kjo është më e lartë sesa përdorimi i metodës lineare, por më e ulët se cilësia e linjës së trendit me zbutjen eksponenciale.

Në përgjithësi, formula e zbutjes duket si kjo:

y = a * ln (x) + b

ku ln është vlera 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 polinomiale

Ka ardhur koha të marrim në konsideratë metodën e zbutjes së polinomit.

Shkoni në dritaren e formatit të linjës së trendit, siç është bërë më shumë se një herë. Në bllok "Ndërtimi i një linje trendi" vendosni kaloni në pozicion "Polinom". Në të djathtë të këtij artikulli është një fushë "Diplomë". Kur zgjidhni një vlerë "Polinom" bëhet aktiv. Këtu mund të specifikoni çdo vlerë të energjisë nga 2 (vendosur si parazgjedhje) te 6. Ky tregues përcakton numrin e maksimave dhe minimumit të funksionit. Kur instaloni një polinom të shkallës së dytë, përshkruhet vetëm një maksimum, dhe kur instaloni një polinom të shkallës së gjashtë, mund të përshkruhen deri në pesë maksima. Së pari, le të lëmë cilësimet e paracaktuar, domethënë, do të tregojmë shkallën e dytë. Ne i lëmë pjesën tjetër të cilësimeve njësoj siç i vendosëm në metodat e mëparshme. Klikoni në butonin "Close".

Linja e trendit duke përdorur këtë metodë është e vizatuar. Siç mund ta shihni, është edhe më e lakuar sesa kur përdorni përafrim eksponencial. Niveli i besimit është më i lartë se me ndonjë nga metodat e përdorura më parë, dhe është 0,9724.
Kjo metodë mund të aplikohet më me sukses nëse të dhënat janë vazhdimisht të ndryshueshme. Funksioni që përshkruan këtë lloj zbutjeje duket si ky:
y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ n
Në rastin tonë, formula ka marrë 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ë ndryshojë. Ne kthehemi në dritaren e formatit. Ne lëmë polinomin e tipit të përafrimit, por përballë tij, në dritaren e shkallës, vendosim vlerën maksimale të mundshme - 6.

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

Formula që përshkruan këtë lloj zbutjeje merr 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ë

Si përfundim, ne konsiderojmë metodën e përafrimit të ligjit-fuqi në Excel.

Kalojmë në dritare Formati i linjës së trendit. Vendosni llojin e kaloni për zbutjen në pozicion "Diplomë". Afishimi i ekuacionit dhe nivelit të besimit, si gjithmonë, mbetet në. Klikoni në butonin "Close".

Programi formon një linjë trendi. Siç mund ta shihni, në rastin tonë është një vijë me një përkulje të lehtë. Niveli i besimit është 0,9618, që është një normë mjaft e lartë. Nga të gjitha metodat e mësipërme, niveli i besimit ishte më i lartë vetëm kur përdorni metodën polinomiale.

Kjo metodë përdoret në mënyrë efektive në rastet e ndryshimit intensiv të të dhënave të funksionit. Shtë e rëndësishme të merret në konsideratë që kjo mundësi është e zbatueshme vetëm në kushtet kur funksioni dhe argumenti nuk pranojnë vlera negative ose zero.

Formula e përgjithshme që përshkruan këtë metodë ka formën e mëposhtme:

y = bx ^ n

Në rastin tonë të veçantë, duket si kjo:

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

Siç mund ta shihni, kur kemi përdorur të dhënat specifike që kemi përdorur si shembull, metoda e përafrimit polinom me një polinom në shkallën e gjashtë tregoi nivelin më të lartë të besueshmërisë (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 tendencë do të jetë me shembuj të tjerë. Jo, niveli i efikasitetit të metodave të mësipërme mund të ndryshojë ndjeshëm, në varësi të llojit specifik të funksionit për të cilin do të ndërtohet linja e trendit. Prandaj, nëse metoda e zgjedhur është më e efektshme për këtë funksion, kjo nuk do të thotë aspak se do të jetë gjithashtu optimale në një situatë tjetër.

Nëse ende nuk mund të përcaktoni menjëherë, bazuar në rekomandimet e mësipërme, cili lloj i përafrimit është i përshtatshëm posaçërisht për rastin tuaj, atëherë ka kuptim të provoni të gjitha metodat. Pas ndërtimit të një linje trendi dhe shikimit të nivelit të tij të besimit, do të jetë e mundur të zgjidhni opsionin më të mirë.

Send