Ukuhlaziywa Kokuzwela ku-Excel (Isampula Lolwazi)

Izinqubo emkhakheni wezezimali zihlale zixhumene - isici esisodwa sincike kwesinye futhi sishintsha nayo. Landelela lezi zinguquko futhi uqonde ukuthi yini ongayilindela esikhathini esizayo, mhlawumbe usebenzisa imisebenzi ye-Excel nezindlela zesipredishithi.

Ukuthola imiphumela eminingi ngethebula ledatha

Amakhono e-datasheet yizici zokuhlaziya-uma-okuvame ukwenziwa nge-Microsoft Excel. Leli igama lesibili lokuhlaziya ukuzwela.

Uhlolojikelele

Ithebula ledatha iwuhlobo lohlu lwamaseli angasetshenziswa ukuxazulula izinkinga ngokushintsha amanani kwamanye amaseli. Idalwe uma kudingekile ukulandelela izinguquko ezingxenyeni zefomula futhi uthole izibuyekezo zemiphumela, ngokusho kwalezi zinguquko. Ake sithole ukuthi asetshenziswa kanjani amathebula edatha ocwaningweni, nokuthi ayizinhlobo zini.

Okuyisisekelo mayelana namathebula edatha

Kunezinhlobo ezimbili zamathebula edatha, ziyahlukahluka ngenani lezingxenye. Udinga ukuhlanganisa itafula eligxile enanini lamanani uXNUMXbuXNUMXb okudingeka uhlole ngalo.

Izazi zezibalo zisebenzisa ithebula eliguquguqukayo elilodwa lapho kunokuhlukahluka okukodwa kusisho esisodwa noma ngaphezulu okungashintsha umphumela wabo. Isibonelo, ivame ukusetshenziswa ngokuhambisana nomsebenzi we-PMT. Ifomula yakhelwe ukubala inani lenkokhelo evamile futhi icabangela izinga lenzalo elishiwo esivumelwaneni. Ezibalweni ezinjalo, okuguquguqukayo kubhalwa kukholamu eyodwa, futhi imiphumela yezibalo kwenye. Isibonelo sepuleti ledatha elinokwehluka okungu-1:

Okulandelayo, cabangela amapuleti aneziguquguquko ezi-2. Zisetshenziswa ezimeni lapho izici ezimbili zithonya ushintsho kunoma iyiphi inkomba. Okubili okuguquguqukayo kungase kugcine kwelinye ithebula elihlobene nemalimboleko, elingasetshenziswa ukunquma isikhathi esiphelele sokukhokha kanye nenani lenkokhelo yanyanga zonke. Kulesi sibalo, udinga futhi ukusebenzisa umsebenzi we-PMT. Isibonelo sethebula elinezinto ezi-2 eziguquguqukayo:

Ukudala ithebula ledatha elinokuguquguquka okukodwa

Cabangela indlela yokuhlaziya usebenzisa isibonelo sesitolo sezincwadi esincane esinezincwadi eziyi-100 kuphela esitokweni. Ezinye zazo zingathengiswa ezibiza kakhulu ($50), okunye kuzobiza abathengi ngaphansi ($20). Isamba semali engenayo evela ekuthengisweni kwazo zonke izimpahla ibalwa - umnikazi wanquma ukuthi uzothengisa u-60% wezincwadi ngentengo ephezulu. Udinga ukuthola ukuthi imali engenayo izokhuphuka kanjani uma ukhuphula intengo yevolumu enkulu yezimpahla - 70%, njalonjalo.

Nakani! Isamba semali engenayo kufanele ibalwe kusetshenziswa ifomula, ngaphandle kwalokho ngeke kwenzeke ukuhlanganisa ithebula ledatha.

1. Khetha iseli yamahhala kude nonqenqema lweshidi bese ubhala ifomula kulo: =Isamba semali engenayo. Isibonelo, uma imali engenayo ibhalwe kuseli C14 (igama elingahleliwe libonisiwe), udinga ukubhala lokhu: =S14.
2. Sibhala iphesenti lomthamo wezimpahla kukholamu kwesokunxele laleli seli - hhayi ngaphansi kwalo, lokhu kubaluleke kakhulu.
3. Sikhetha ububanzi bamaseli lapho kukhona ikholomu yephesenti kanye nesixhumanisi sesamba semali engenayo.

4. Sithola kuthebhu ethi "Idatha" into ethi "Kuthiwani uma ukuhlaziywa" bese uchofoza kukho - kumenyu evulayo, khetha inketho "Ithebula ledatha".

5. Iwindi elincane lizovuleka lapho udinga ukucacisa khona iseli elinamaphesenti ezincwadi aqale athengiswa ngentengo ephezulu kukholamu ethi “Faka esikhundleni samanani ngemigqa kokuthi …”. Lesi sinyathelo senziwa ukuze kubalwe kabusha yonke imali engenayo kucatshangelwa amaphesenti akhulayo.

Ngemva kokuchofoza inkinobho ethi "KULUNGILE" efasiteleni lapho idatha ifakwe khona ukuze kuhlanganiswe ithebula, imiphumela yezibalo izovela emigqeni.

Ukwengeza Ifomula Kuthebula Ledatha Eliguquguqukayo Elilodwa

Kusuka kuthebula elisize ukubala isenzo esinokuhlukahluka okukodwa kuphela, ungenza ithuluzi lokuhlaziya eliyinkimbinkimbi ngokwengeza ifomula eyengeziwe. Kumele ifakwe eduze kwefomula ekhona kakade - isibonelo, uma ithebula liqondiswe kumugqa, sifaka isisho kuseli ngakwesokudla kwaleyo ekhona. Uma umumo wekholomu usethiwe, sibhala ifomula entsha ngaphansi kwendala. Okulandelayo, landela i-algorithm:

1. Khetha ububanzi bamaseli futhi, kodwa manje kufanele ifake ifomula entsha.
2. Vula imenyu yokuhlaziya ethi “what if” bese ukhetha okuthi “Datasheet”.
3. Sengeza ifomula entsha ensimini ehambisanayo emigqeni noma kumakholomu, kuye ngokuthi ipuleti ikuphi.

Dala ithebula ledatha elinezinto ezimbili eziguquguqukayo

Ukuqala kwetafula elinjalo kuhluke kancane - udinga ukubeka isixhumanisi semali engenayo ngaphezu kwamanani ephesenti. Okulandelayo, senza lezi zinyathelo:

1. Bhala izinketho zentengo emgqeni owodwa ngesixhumanisi semali engenayo - iseli elilodwa ngentengo ngayinye.
2. Khetha ububanzi bamaseli.

3. Vula iwindi lethebula ledatha, njengalapho uhlanganisa itafula elinokuguquguquka okukodwa - ngokusebenzisa ithebhu "Idatha" kubha yamathuluzi.
4. Faka esikhundleni sekholomu ethi “Faka esikhundleni samanani ngamakholomu kokuthi …” iseli elinenani lokuqala eliphezulu.
5. Engeza iseli elinamaphesenti okuqala okuthengiswa kwezincwadi ezibizayo kukholomu ethi “Faka esikhundleni samanani ngemigqa ku-…” bese uchofoza okuthi “KULUNGILE”.

Ngenxa yalokho, itafula lonke ligcwele amanani emali engenayo enemibandela ehlukene yokuthengiswa kwempahla.

Sheshisa izibalo zamakhasi okusebenza aqukethe amathebula edatha

Uma udinga izibalo ezisheshayo kuthebula ledatha elingakuqalisi ukubalwa kabusha kwencwadi yonke yokusebenzela, kunezinto ezimbalwa ongazenza ukusheshisa inqubo.

1. Vula iwindi lezinketho, khetha into ethi “Amafomula” kumenyu engakwesokudla.
2. Khetha into ethi “Okuzenzakalelayo, ngaphandle kwamathebula edatha” esigabeni esithi “Izibalo encwadini yokusebenzela”.

3. Ake sibale kabusha imiphumela etafuleni mathupha. Ukuze wenze lokhu, khetha amafomula bese ucindezela ukhiye F.

Amanye Amathuluzi Okwenza Ukuhlaziya Ukuzwela

Kukhona amanye amathuluzi ohlelweni ukukusiza wenze ukuhlaziya ukuzwela. Benza ngokuzenzakalelayo ezinye izenzo obekungadingeka zenziwe mathupha.

1. Umsebenzi "wokukhetha ipharamitha" ufanelekile uma umphumela owufunayo waziwa, futhi udinga ukwazi inani lokufaka lokuguquguqukayo ukuze uthole umphumela onjalo..
2. "Sesha isixazululo" isengezo sokuxazulula izinkinga. Kudingeka ukusetha imingcele futhi ukhombe kubo, emva kwalokho uhlelo luzothola impendulo. Isixazululo sinqunywa ngokushintsha amanani.
3. Ukuhlaziya ukuzwela kungenziwa kusetshenziswa Isiphathi Sesimo. Leli thuluzi litholakala kumenyu yokuhlaziya okuthi-uma ngabe ngaphansi kwethebhu Yedatha. Ishintsha amanani kumaseli amaningana - inombolo ingafinyelela ku-32. I-dispatcher iqhathanisa lawa manani ukuze umsebenzisi angawashintshi ngesandla. Isibonelo sokusebenzisa umphathi weskripthi:

Ukuhlaziywa kokuzwela kwephrojekthi yokutshala imali ku-Excel

Ukuhlaziya okuthi-ke uma kuwusizo ikakhulukazi ezimweni lapho kudingeka khona ukubikezela, njengokutshalwa kwezimali. Abahlaziyi basebenzisa le ndlela ukuthola ukuthi inani lesitoko senkampani lizoshintsha kanjani ngenxa yezinguquko kwezinye izici.

Indlela Yokuhlaziya Ukuzwela Kokutshalwa Kwezimali

Uma uhlaziya “kuthiwani uma” sebenzisa ukubala – okwenziwa ngesandla noma okuzenzakalelayo. Ububanzi bamanani baziwa, futhi ashintshwa abe yifomula ngayinye. Umphumela uba isethi yamanani. Khetha inombolo efanele kubo. Ake sicabangele izinkomba ezine okwenziwa kuzo ukuhlaziywa kokuzwela emkhakheni wezezimali:

1. Isamba Semali Yamanje - Ibalwa ngokukhipha inani le-investimenti enanini lemali engenayo.
2. Izinga langaphakathi lembuyiselo / inzuzo - likhombisa ukuthi yimalini inzuzo edingekayo ukuze itholwe ekutshalweni kwemali ngonyaka.
3. Isilinganiso senkokhelo yisilinganiso sayo yonke inzuzo ekutshalweni kwezimali kokuqala.
4. Inkomba yenzuzo enesaphulelo - ikhombisa ukusebenza kahle kokutshalwa kwezimali.

Formula

Ukuzwela kokushumeka kungabalwa kusetshenziswa le fomula: Shintsha kupharamitha yokuphumayo ku-% / Shintsha kupharamitha yokufaka ku-%.

Amapharamitha okukhiphayo nawokufaka angaba amanani achazwe ngaphambilini.

1. Udinga ukwazi umphumela ngaphansi kwezimo ezijwayelekile.
2. Sishintsha okukodwa kokuguquguqukayo futhi siqaphe izinguquko kumphumela.
3. Sibala ukuguqulwa kwephesenti yazo zombili imingcele ngokuhlobene nezimo ezimisiwe.
4. Sifaka amaphesenti atholiwe kufomula futhi sinqume ukuzwela.

Isibonelo sokuhlaziywa kokuzwela kwephrojekthi yokutshala imali ku-Excel

Ukuze uqonde kangcono indlela yokuhlaziya, isibonelo siyadingeka. Ake sihlaziye iphrojekthi ngedatha eyaziwayo elandelayo:

1. Gcwalisa ithebula ukuze uhlaziye iphrojekthi kulo.

2. Sibala ukuhamba kwemali sisebenzisa umsebenzi we-OFFSET. Esigabeni sokuqala, ukugeleza kuyalingana nokutshalwa kwezimali. Okulandelayo, sisebenzisa ifomula: =IF(OFFSET(Inombolo,1;)=2;USM(Inflow 1:Okuphumayo 1); I-SUM(Inflow 1:Okuphumayo 1)+$B$ 5)
   Ukuqokwa kwamaseli kufomula kungase kwehluke, kuye ngokwakheka kwethebula. Ekugcineni, inani elivela kudatha yokuqala liyengezwa - inani lokulondoloza.

3. Sinquma isikhathi iphrojekthi ezokhokha ngaso. Esikhathini sokuqala, sisebenzisa le fomula: =KUFISHAYO(G7 :g17; <0″). Ububanzi beseli yikholomu yokuhamba kwemali. Kwezinye izikhathi, sisebenzisa le fomula: =Inkathi yokuqala+IF(Eyokuqala e.stream>0; Eyokuqala e.stream;0). Le phrojekthi isendaweni yekhefu eminyakeni emi-4.

4. Sakha ikholomu yezinombolo zalezo zikhathi lapho iphrojekthi ikhokha.

5. Sibala imbuyiselo ekutshalweni kwezimali. Kuyadingeka ukwenza inkulumo lapho inzuzo ngesikhathi esithile ihlukaniswa ngokutshalwa kwezimali kokuqala.

6. Sinquma isici sesaphulelo sisebenzisa le fomula: =1/(1+Disc.%) ^Inombolo.

7. Sibala inani lamanje sisebenzisa ukuphindaphinda - ukuhamba kwemali kuphindwa ngesici sesaphulelo.

8. Ake sibale i-PI (inkomba yenzuzo). Inani lamanje ngokuhamba kwesikhathi lihlukaniswa yi-investimenti ekuqaleni kwephrojekthi.

9. Ake sichaze izinga langaphakathi lembuyiselo sisebenzisa umsebenzi we-IRR: =IRR(Ibanga lokugeleza kwemali).

Ukuhlaziya Ukuzwela Kokutshalwa Kwezimali Ngokusebenzisa I-Datasheet

Ukuze kuhlaziywe amaphrojekthi emkhakheni wokutshalwa kwezimali, ezinye izindlela zifaneleka kangcono kunethebula ledatha. Abasebenzisi abaningi bahlangabezana nokudideka lapho behlanganisa ifomula. Ukuze uthole ukuncika kwesici esisodwa ekushintsheni kwezinye, udinga ukukhetha amaseli alungile okufaka izibalo nokufunda idatha.

Ukuhlaziywa kwe-factor nokusabalalisa ku-Excel nge-automation yokubala

Enye i-typology yokuhlaziywa kokuzwela ukuhlaziya isici nokuhlaziywa kokuhlukahluka. Uhlobo lokuqala luchaza ubudlelwano phakathi kwezinombolo, okwesibili kuveza ukuncika kokuguquguquka okukodwa kwezinye.

I-ANOVA ku-Excel

Injongo yokuhlaziya okunjalo iwukuhlukanisa ukuhlukahluka kwevelu kube izingxenye ezintathu:

1. Ukuhlukahluka ngenxa yethonya lamanye amanani.
2. Izinguquko ngenxa yobudlelwano bamanani obuthintayo.
3. Izinguquko ezingahleliwe.

Masenze ukuhlaziya kokuhluka ngokusebenzisa isengezo se-Excel “Ukuhlaziywa Kwedatha”. Uma inikwe amandla, inganikwa amandla kuzilungiselelo.

Ithebula lokuqala kufanele lilandele imithetho emibili: kunekholomu eyodwa yenani ngalinye, futhi idatha ekulo ihlelwe ngendlela ekhuphukayo noma eyehlayo. Kuyadingeka ukuhlola umthelela wezinga lemfundo ekuziphatheni ezingxabanweni.

1. Thola ithuluzi Lokuhlaziya Idatha kuthebhu Yedatha bese uvula iwindi layo. Ohlwini, udinga ukukhetha ukuhlaziya kwendlela eyodwa yokuhluka.

2. Gcwalisa imigqa yebhokisi lengxoxo. Isikhawu sokufaka wonke amaseli, ngaphandle kwezihloko nezinombolo. Qoqa ngamakholomu. Sibonisa imiphumela eshidini elisha.

Njengoba inani leseli eliphuzi likhulu kunelodwa, ukucabanga kungabhekwa njengokungalungile - abukho ubuhlobo phakathi kwemfundo nokuziphatha okungqubuzanayo.

Ukuhlaziywa kwezinto ku-Excel: isibonelo

Ake sihlaziye ubudlelwano bedatha emkhakheni wokuthengisa - kuyadingeka ukukhomba imikhiqizo edumile nengathandeki. Ulwazi lokuqala:

1. Kudingeka sithole ukuthi yiziphi izidingo zezimpahla ezikhuphuke kakhulu ngenyanga yesibili. Sihlanganisa ithebula elisha lokunquma ukukhula nokwehla kwesidingo. Ukukhula kubalwa kusetshenziswa le fomula: =IF((Isidingo 2-Isidingo 1)>0; Isidingo 2- Isidingo 1;0). Nciphisa ifomula: =IF(Ukukhula=0; Isidingo 1- Isidingo 2;0).

2. Bala ukukhula kwesidingo sezimpahla njengephesenti: =IF(Ukukhula/Umphumela 2 =0; Ukuncipha/Umphumela 2; Ukukhula/Umphumela 2).

3. Masenze ishadi ukuze licace – khetha ububanzi bamaseli bese udala i-histogram ngethebhu ethi “Faka”. Kuzilungiselelo, udinga ukususa ukugcwaliswa, lokhu kungenziwa ngethuluzi le-Format Data Series.

Ukuhlaziywa kwezindlela ezimbili zokuhlukahluka ku-Excel

Ukuhlaziywa kokuhlukahluka kwenziwa ngeziguquguquko eziningana. Cabanga ngalokhu ngesibonelo: udinga ukuthola ukuthi ukusabela komsindo wevolumu ehlukile kubonakala kanjani kumadoda nabesifazane.

1. Sivula "Ukuhlaziywa Kwedatha", ohlwini udinga ukuthola ukuhlaziywa okubili kokuhluka ngaphandle kokuphindaphinda.
2. Isikhawu okokufaka – amaseli aqukethe idatha (ngaphandle kweheda). Sibonisa imiphumela eshidini elisha bese uchofoza okuthi “KULUNGILE”.

Inani lika-F likhulu kune-F-critical, okusho ukuthi iphansi lithinta isivinini sokusabela kumsindo.

Isiphetho

Kulesi sihloko, ukuhlaziywa kokuzwela kuspredishithi se-Excel kuxoxwe ngokuningiliziwe, ukuze umsebenzisi ngamunye akwazi ukuqonda izindlela zokusetshenziswa kwayo.