Antaŭvidaj Iloj en Microsoft Excel

Antaŭvidado estas tre grava elemento de preskaŭ iu ajn agadkampo, de ekonomio ĝis inĝenierado. Estas multaj programoj, kiuj specialiĝas pri ĉi tiu areo. Bedaŭrinde ne ĉiuj uzantoj scias, ke la kutima Excel-kalkultabila procesoro havas en sia arsenalo ilojn por prognozo, kiuj ne multe malsuperas al profesiaj programoj laŭ sia efikeco. Ni eksciu kiaj estas tiuj iloj kaj kiel prognoze praktiku.

Prognoza Proceduro

La celo de ĉiu prognozo estas identigi la nunan tendencon, kaj determini la atendatan rezulton rilate al la studita objekto en certa tempo en la estonteco.

Metodo 1: tendenco-linio

Unu el la plej popularaj specoj de grafika prognozo en Excel estas ekstrapola konstruado de tendenco-linio.

Ni provu antaŭdiri la profiton de la entrepreno en 3 jaroj surbaze de datumoj pri ĉi tiu indikilo por la antaŭaj 12 jaroj.

1. Ni konstruas dependecon grafikaĵon bazitan sur tabulaj datumoj konsistantaj el argumentoj kaj funkciaj valoroj. Por fari tion, elektu la tablo-areon, kaj tiam, estante en la langeto Enmetu, alklaku la ikonon de la dezirata speco de diagramo, kiu troviĝas en la bloko Grafikoj. Poste ni elektas la tipon taŭgan por specifa situacio. Plej taŭgas elekti disĵeton. Vi povas elekti alian vidon, sed tiam, por ke la datumoj aperu ĝuste, vi devos plenumi redaktadon, precipe forigi la linion de la argumento kaj elekti alian skalon de la horizontala akso.



2. Nun ni bezonas konstrui tendencan linion. Ni dekstre alklaku iun el la punktoj en la diagramo. En la aktivigita kunteksta menuo, ĉesu la elekto sur la ero Aldonu Trend Line.



3. La fenestra formatado de la tendenca linio malfermiĝas. En ĝi vi povas elekti unu el ses specoj de proksimumado:
   • Lineara;
   • Logaritma;
   • Eksponenta;
   • Potenco;
   • Polinomo;
   • Lineara filtrado.

   Ni komencu elektante linian proksimumon.

   En la agordoj blokas "Antaŭvido" sur la kampo "Antaŭen al" agordi la numeron "3,0", ĉar ni bezonas antaŭdiri antaŭ tri jaroj. Krome vi povas kontroli la skatolon apud la agordoj. "Montri ekvacion en diagramo" kaj "Metu la proksimuman konfidencan valoron (R ^ 2) sur la diagramon". La lasta indikilo montras la kvaliton de la tendenco-linio. Post kiam la agordoj estas faritaj, alklaku la butonon Proksime.



4. La tendenco estas konstruita kaj de ĝi ni povas determini la proksimuman sumon de profito en tri jaroj. Kiel ni vidas, antaŭ tiu tempo ĝi devus esti super 4500 mil rubloj. Koeficiento R2Kiel menciite supre, montras la kvaliton de la tendenco-linio. En nia kazo, la valoro R2 konsistigas 0,89. Ju pli alta estas la koeficiento, des pli alta la fidindeco de la linio. Ĝia maksimuma valoro povas esti egala 1. Oni ĝenerale akceptas tion kun koeficiento supre 0,85 la tendenco-linio estas fidinda.



5. Se la konfidenca nivelo ne taŭgas al vi, tiam vi povas reveni al la tendenco-linia formato-fenestro kaj elekti iun alian tipon de proksimumo. Vi povas provi ĉiujn disponeblajn eblojn por trovi la plej ĝustajn.

   

   Oni devas rimarki, ke la prognozo uzanta ekstrapolon tra la tendenco-linio povas esti efika se la prognoza periodo ne superas 30% de la analizita bazo de periodoj. Tio estas, kiam oni analizas periodon de 12 jaroj, ni ne povas fari efikan prognozon dum pli ol 3-4 jaroj. Sed eĉ en ĉi tiu kazo, ĝi restos relative fidinda, se dum ĉi tiu tempo ne ekzistos forto majora aŭ kontraŭe ekstreme favoraj cirkonstancoj, kiuj ne estis en antaŭaj periodoj.

Leciono: Kiel konstrui tendencan linion en Excel

Metodo 2: la prognoza funkciigisto

Ekstrapolado por tabulaj datumoj fareblas per la norma Excel-funkcio PREDIKTO. Ĉi tiu argumento apartenas al la kategorio de statistikaj iloj kaj havas la jenan sintakson:

= PREDICT (X; konataj_valoroj; konataj_x_valoroj)

"X" estas argumento por kiu la funkcia valoro bezonas esti determinita. En nia kazo, la argumento estos la jaro por kiu prognozado estu farita.

Konataj kaj Valoroj - bazo de konataj funkciaj valoroj. En nia kazo, ĝia rolo estas ludata de la profito de antaŭaj periodoj.

Konataj x Valoroj estas la argumentoj al kiuj respondas la konataj valoroj de la funkcio. En ilia rolo, ni havas la nombron de jaroj dum kiuj oni kolektis informojn pri la profito de antaŭaj jaroj.

Nature, la argumento ne devas esti tempodaŭro. Ekzemple, ĝi povas esti temperaturo, kaj la valoro de la funkcio eble estas la nivelo de ekspansio de akvo kiam varmigita.

Por kalkuli ĉi tiun metodon, la lineara regresiga metodo estas uzata.

Ni rigardu la nuancojn de uzado de la telefonisto PREDIKTO sur konkreta ekzemplo. Prenu la tutan tablon. Ni bezonos scii la prognozon pri profito por 2018.

1. Elektu malplenan ĉelon sur la folio, kie vi planas aperigi la pretigan rezulton. Alklaku la butonon "Enmetu funkcion".



2. Malfermas Feature Sorĉisto. En kategorio "Statistika" elektu la nomon "PREDIKTO"kaj tiam alklaku la butonon "Bone".



3. La argumenta fenestro komenciĝas. En la kampo "X" indiku la valoron de la argumento, al kiu vi volas trovi la valoron de la funkcio. En nia kazo, ĉi tio estas 2018. Tial ni skribas "2018". Sed estas pli bone indiki ĉi tiun indikilon en ĉelo sur la folio kaj en la kampo "X" nur donu ligon al ĝi. Ĉi tio permesos estonte aŭtomatigi la kalkulojn kaj, se necese, facile ŝanĝi la jaron.

   En la kampo Konataj kaj Valoroj precizigu la koordinatojn de la kolumno "Profito de la entrepreno". Ĉi tio povas fari metante la kursoron sur la kampon, poste tenante la maldekstran musbutonon kaj reliefigante la respondan kolumnon sur la folio.

   Simile en la kampo Konataj x Valoroj enigu la kolumnan adreson "Jaro" kun datumoj por la pasinta periodo.

   Post kiam ĉiuj informoj estis eniritaj, alklaku la butonon "Bone".



4. La telefonisto kalkulas surbaze de la enmetitaj datumoj kaj montras la rezulton sur la ekrano. Por 2018, oni planas profiti en la regiono de 44564,7 mil rubloj. Surbaze de la rezulta tabelo, ni povas konstrui grafeon per la grafikaj iloj diskutitaj supre.



5. Se vi ŝanĝas la jaron en la ĉelo, kiu estis uzata por eniri la argumenton, la rezulto ŝanĝiĝos sekve, kaj la horaro aŭtomate ĝisdatigos. Ekzemple laŭ prognozoj en 2019, la sumo de profito estos 4637,8 mil rubloj.

Sed ne forgesu, ke same kiel kun la konstruado de la tendenco, la periodo antaŭ la prognoza periodo ne devas superi 30% de la tuta periodo por kiu la datumbazo estis amasigita.

Leciono: Ekstrapolado en Excel

Metodo 3: TREND-telefonisto

Por prognozo, vi povas uzi alian funkcion - TRENDU. Ĝi ankaŭ apartenas al la kategorio de statistikaj telefonistoj. Ĝia sintakso similas al ila sintakso PREDIKTO kaj aspektas jene:

= TREND (Konataj valoroj_y; konataj valoroj_x; novaj_valoroj_x; [const])

Kiel vi povas vidi, la argumentoj Konataj kaj Valoroj kaj Konataj x Valoroj tute respondas al similaj elementoj de la telefonisto PREDIKTO, kaj la argumento "Novaj x valoroj" kongruas argumento "X" antaŭa ilo. Krome, TRENDU estas plia argumento "Konstanta", sed ĝi estas elektebla kaj uziĝas nur se ekzistas konstantaj faktoroj.

Ĉi tiu telefonisto estas plej efike uzata en ĉeesto de lineara dependeco de la funkcio.

Ni vidu kiel ĉi tiu ilo funkcios kun la sama datuma tabelo. Por kompari la rezultojn, ni difinas la prognozan punkton kiel 2019.

1. Ni nomumas la ĉelon por montri la rezulton kaj kuri Feature Sorĉisto laŭ la kutima maniero. En kategorio "Statistika" trovi kaj reliefigi la nomon "TRENDU". Alklaku la butonon "Bone".



2. Fenestro de Argumentisto Malfermiĝas TRENDU. En la kampo Konataj kaj Valoroj per la priskribita metodo ni eniras la koordinatojn de la kolumno "Profito de la entrepreno". En la kampo Konataj x Valoroj enigu la kolumnan adreson "Jaro". En la kampo "Novaj x valoroj" ni enigas la ligon al la ĉelo, kie troviĝas la jar-numero por kiu la prognozo estu indikita. En nia kazo, ĉi tio estas 2019. La kampo "Konstanta" lasu ĝin malplena. Alklaku la butonon "Bone".



3. La telefonisto procesas la datumojn kaj montras la rezulton sur la ekrano. Kiel vi povas vidi, la kvanto de projektita profito por 2019, kalkulita per la lineara dependiga metodo, estos, kiel en la antaŭa kalkula metodo, 4637,8 mil rubloj.

Metodo 4: KLUDANTO

Alia funkcio uzebla por prognozado en Excel estas la telefonisto GROWTH. Ĝi ankaŭ apartenas al la statistika grupo de iloj, sed male al la antaŭaj, kiam oni kalkulas ĝin, ĝi ne uzas la linean dependecan metodon, sed la eksponenta. La sintakso de ĉi tiu ilo estas jena:

= GROWTH (Konataj valoroj_y; konataj valoroj_x; novaj_valoroj_x; [konst])

Kiel vi povas vidi, la argumentoj de ĉi tiu funkcio ripetas ĝuste la argumentojn de la telefonisto TRENDU, do ni ne loĝos pri ilia priskribo por la dua fojo, sed tuj pasos al la praktika apliko de ĉi tiu ilo.

1. Ni elektas la ĉelon por produkti la rezulton kaj nomas ĝin laŭ la kutima maniero Feature Sorĉisto. En la listo de statistikaj telefonistoj, serĉu la eron ROST, elektu ĝin kaj alklaku la butonon "Bone".



2. La argumenta fenestro de ĉi-supra funkcio estas aktivigita. Enigu la datumojn en la kampoj de ĉi tiu fenestro sammaniere kiel ni enigis ilin en la fenestra argumento de telefonisto TRENDU. Post kiam la informoj estas eniritaj, alklaku la butonon "Bone".



3. La rezulto de prilaborado de datumoj aperas sur la monitoro en la antaŭe indikita ĉelo. Kiel vi povas vidi, ĉi-foje la rezulto estas 4682,1 mil rubloj. Diferencoj de rezultaj prilaboraj datumoj de telefonistoj TRENDU bagatelaj, sed ili haveblas. Ĉi tio estas pro la fakto, ke ĉi tiuj iloj uzas malsamajn kalkulajn metodojn: la lineara dependeca metodo kaj la eksponenta dependiga metodo.

Metodo 5: LINEAR-telefonisto

Funkciigisto LINIO en la kalkulo uzas la lineara proksimuma metodo. Oni ne konfuzu ĝin kun la lineara dependiga metodo uzata de la ilo. TRENDU. Ĝia sintakso estas jena:

= LINE (Konataj valoroj_y; konataj valoroj_x; novaj_valoroj_x; [konst]) [statistikoj])

La lastaj du argumentoj estas laŭvolaj. Kun la unuaj du, ni konas la antaŭajn metodojn. Sed vi probable rimarkis, ke neniu argumento en ĉi tiu funkcio atentigas pri novaj valoroj. Fakte, ĉi tiu ilo nur determinas la ŝanĝon de enspezoj per unuo de periodo, kiu en nia kazo egalas al unu jaro, sed ni devas kalkuli la totalan rezulton aparte, aldonante la rezulton de la kalkulo de la telefonisto al la lasta efektiva profito-valoro. LINIOfojoj la nombro de jaroj.

1. Ni elektas la ĉelon en kiu la kalkulo estos farita kaj funkcias la Funkcia Sorĉisto. Elektu la nomon LINEIN en kategorio "Statistika" kaj alklaku la butonon "Bone".



2. En la kampo Konataj kaj Valoroj, malfermita fenestro de argumentoj, enigu la koordinatojn de la kolumno "Profito de la entrepreno". En la kampo Konataj x Valoroj enigu la kolumnan adreson "Jaro". La ceteraj kampoj restas malplenaj. Tiam alklaku la butonon "Bone".



3. La programo kalkulas kaj montras la linean tendencan valoron en la elektita ĉelo.



4. Nun ni devas ekscii la grandecon de la projektita profito por 2019. Fiksu la signon "=" al iu ajn malplena ĉelo sur la folio. Ni alklakas la ĉelon, kiu enhavas la faktan enspezon por la lasta studita jaro (2016). Ni metis signon "+". Tuj poste alklaku la ĉelon, kiu enhavas la antaŭe kalkulitan linian tendencon. Ni metis signon "*". Ĉar inter la lasta jaro de la studperiodo (2016) kaj la jaro por kiu vi volas fari antaŭvidon (2019), periodo de tri jaroj kuŝas, ni fiksas la numeron en la ĉelo "3". Por fari kalkulon alklaku la butonon Eniru.

Kiel vi povas vidi, la projektita profita marĝeno kalkulita per la lineara proksimuma metodo en 2019 sumiĝos al 4.614,9 mil rubloj.

Metodo 6: LGRFPPRIBLE-operatoro

La lasta ilo, kiun ni rigardos, estos LGRFPPRIBLE. Ĉi tiu telefonisto plenumas kalkulojn bazitajn sur la eksponenta proksimuma metodo. Ĝia sintakso havas la jenan strukturon:

= LGRFPRIBLE (Konataj valoroj_y; konataj valoroj_x; novaj_valoroj_x; [konst]; [statistiko])

Kiel vi povas vidi, ĉiuj argumentoj ripetas tute la respondajn elementojn de la antaŭa funkcio. La algoritmo de prognozokalkulo iom ŝanĝiĝos. La funkcio kalkulas la eksponencan tendencon, kiu montras kiom da fojoj la enspezo ŝanĝiĝos dum unu periodo, tio estas dum unu jaro. Ni bezonos trovi la diferencon de profito inter la lasta efektiva periodo kaj la unua planita, multobligu ĝin per la nombro de planitaj periodoj (3) kaj aldonu al la rezulto la sumon de la lasta efektiva periodo.

1. En la listo de telefonistoj de la Funkcia Sorĉisto, elektu la nomon LGRFPPRIBL. Alklaku la butonon "Bone".



2. La argumenta fenestro komenciĝas. En ĝi ni enmetas la datumojn ekzakte kiel ni faris, uzante la funkcion LINIO. Alklaku la butonon "Bone".



3. La rezulto de la eksponenta tendenco estas kalkulita kaj montrita en la difinita ĉelo.



4. Ni metis signon "=" en malplenan ĉelon. Malfermu la krampojn kaj elektu la ĉelon, kiu enhavas la enspezan valoron por la lasta efektiva periodo. Ni metis signon "*" kaj elektu la ĉelon enhavantan la eksponan tendencon. Ni metas minusan signon kaj ree alklakas la elementon, en kiu troviĝas la enspezo de la lasta periodo. Fermu la krampon kaj enigu la gravulojn "*3+" sen citaĵoj. Denove alklaku la saman ĉelon, kiu estis elektita por la lasta fojo. Por efektivigi la kalkulon, alklaku la butonon Eniru.

La projektita kvanto de profito en la jaro 2019, kiu estis kalkulita per la metodo de eksponenta proksimumado, sumiĝos al 4.639,2 mil rubloj, kio denove ne multe diferencas de la rezultoj akiritaj en la antaŭa kalkulo.

Leciono: Aliaj statistikaj funkcioj en Excel

Ni eksciis kiel fari antaŭdirojn en la programo Excel. Ĉi tio povas fari grafike per la uzo de tendenco-linio, kaj analize uzante kelkajn enkonstruitajn statistikajn funkciojn. Rezulte prilabori identajn datumojn de ĉi tiuj telefonistoj, malsama rezulto povas esti akirita. Sed ĉi tio ne mirigas, ĉar ili ĉiuj uzas malsamajn kalkulajn metodojn. Se fluktuado estas malgranda, tiam ĉiuj ĉi tiuj ebloj aplikeblaj por aparta kazo povas esti konsiderataj relative fidindaj.