Indlela yokukhipha / yokwengeza iphesenti enombolweni ku-Excel (+ izibonelo)

Ikhono lokunquma iphesenti lenombolo nokwenza imisebenzi ehlukahlukene ngayo libaluleke kakhulu ezindaweni ezihluke ngokuphelele zomsebenzi - ukubalwa kwezimali, ukutshalwa kwezimali, ngisho nalapho udla endaweni yokudlela. Ayikho indawo yempilo lapho bekungeke kudingeke khona ngezikhathi ezithile ukucacisa ingxenye yayo yonke.

I-Excel inamathuluzi aphelele akuvumela ukuthi wenze imisebenzi ngamaphesenti. Eziningi zazo zenziwa ngokuzenzakalelayo, vele ufake ifomula, futhi inani elifunayo lizobalwa. Ngokunethezeka kakhulu.

Ungasebenza kanjani ngamaphesenti ku-Excel

Wonke umuntu manje uyazi ukuthi anganquma kanjani amaphesenti. Futhi noma engazi ukuthi kanjani, kungenziwa njalo kusetshenziswa umshini wokubala (yize kungekho muntu onjalo). Kule divayisi, ukusebenza ngamaphesenti kwenziwa ngesithonjana esikhethekile%. 

Nge-Excel, lokhu kulula nakakhulu kunokuzenzela ngokwakho. Kodwa ngaphambi kokwenza amafomula futhi wenze imisebenzi ethile nawo, udinga ukukhumbula izisekelo zesikole.

Iphesenti liyikhulu lenombolo. Ukuze uyinqume, udinga ukuhlukanisa ingxenye ngenani eliphelele bese uphindaphinda umphumela ngo-100. 

Ake sithi ungumphathi we-warehouse. Amayunithi angama-30 emikhiqizo alethwe kuwe. Ngosuku lokuqala, ezi-5 kuphela zazo ezabonwa. Ngakho yiliphi iphesenti lomkhiqizo elithengisiwe ngempela?

Siyaqonda ukuthi u-5 uyiqhezu kanti u-30 uyinombolo ephelele. Okulandelayo, udinga nje ukufaka izinombolo ezifanele kufomula echazwe ngenhla, ngemva kwalokho sithola umphumela we-16,7%.

Ukwengeza iphesenti enombolweni ngendlela evamile kunzima kakhulu, njengoba lokhu kusebenza kwenziwa ngezinyathelo ezimbalwa.

Okokuqala udinga ukunquma u-5%, bese wengeza leli nani enombolweni. Isibonelo, uma ungeza u-5% kuya ku-25, umphumela wokugcina uzoba ngu-26,5.

Manje, ngemva kokwazi imithetho yokusebenza ngamaphesenti empilweni yangempela, akunzima kakhulu ukuqonda ukuthi isebenza kanjani ku-Excel.

Ibala iphesenti lenombolo ku-Excel

Ukuze wenze lokhu, ungasebenzisa izindlela eziningana.

Ake sithi sinetafula elinjalo. Iseli lokuqala ngokuvundlile yinani eliphelele lezimpahla, kanti eyesibili, ngokulandelana, ukuthi idayiswe malini. Kweyesithathu, sizokwenza umsebenzi wezibalo.

Manje ake sibhekisise lesi sithombe. Awuboni lutho olumangazayo? Ibha yefomula ibonisa ukuhlukaniswa okulula kwengxenye yonke, iphesenti liyaboniswa, kodwa asiphindanga umphumela ngo-100. Kungani lokhu kwenzeka?

Iqiniso liwukuthi iseli ngalinye ku-Excel lingaba nefomethi yalo. Endabeni ye-C1, amaphesenti asetshenziswa. Okusho ukuthi, uhlelo luphindaphinda ngokuzenzakalelayo umphumela ngo-100, futhi uphawu lwe-% lwengezwe kumphumela. Uma kunesidingo esinjalo, umsebenzisi anganquma ukuthi zingaki izindawo zamadesimali okufanele ziboniswe kumphumela owumphumela. 

Manje ake sinqume ukuthi iyiphi inombolo engamaphesenti amahlanu enamba engu-25. Ukuze wenze lokhu, kufanele uqale uwaphindaphinde la manani, bese uwahlukanisa ngo-100. Umphumela ubonakala kusithombe-skrini.

Hhayi-ke, noma inketho yesibili ukwahlukanisa inombolo ngekhulu, bese uphindaphinda ngo-5. Umphumela ngeke ushintshe kulokhu. 

Lo msebenzi ungenziwa ngenye indlela. Ukuze wenze lokhu, udinga ukuthola% uphawu kukhibhodi (ukuyengeza, udinga ukucindezela kanyekanye inombolo 5 ngokhiye Shift).

Futhi manje ake sihlole ngokusebenza ukuthi ungasebenzisa kanjani ulwazi oluzuziwe.

Ake sithi sinetafula elinohlu lwezinto ezithengiswayo, izindleko zazo, futhi siyalazi izinga le-VAT (ake sithi liyi-18%). Ngakho, kukholamu yesithathu kuyadingeka ukurekhoda inani lentela.

Ngemuva kokuthi intengo yomkhiqizo iphindwe ngo-18%, udinga ukusebenzisa umaka wokuqedela ngokuzenzakalela ukuze ubhale le fomula kuseli ngalinye lekholomu. Ukuze wenze lokhu, udinga ukuchofoza ibhokisi elisekhoneni elingezansi kwesokudla bese ulihudulela phansi enambeni oyifunayo yamaseli. 

Ngemuva kokuthi sithole inani lentela, kuyadingeka ukunquma ukuthi umsebenzisi uzokhokha malini ekugcineni.

Ifomula kanje:

=(B1*100)/18

Ngemva kokuwusebenzisa, sithola umphumela onjalo etafuleni.

Siyazi ukuthi zingaki izinto ezidayisiwe sezizonke futhi ngazinye. Manje sidinga ukuqonda ukuthi yiliphi iphesenti lokudayiswa okuphelele kweyunithi ngayinye.

Ifomula ayishintshi. Udinga ukuhlukanisa isheya ngenani eliphelele, bese uphindaphinda umphumela ngo-100. Kodwa kulokhu, udinga ukwenza isixhumanisi siphelele. Ukuze wenze lokhu, yandulela inombolo yomugqa nekholomu ngophawu lwedola $. Uzothola umphumela olandelayo.

Ukwengeza iphesenti enombolweni ku-Excel

Ukuze wenze lokhu, udinga ukulandela izinyathelo ezimbili:

1. Nquma iphesenti lenombolo. Esimeni sethu yi-1,25.
   

   

2. Umphumela wengezwe kunombolo ephelele. Esibonelweni sethu, umphumela uzoba ngu-26,5. Okusho ukuthi, ukulandelana kwezenzo kuyafana nokubala okujwayelekile, zonke izibalo zenziwa ngaphakathi kwe-Excel.
   

   

Futhi kuleli thebula, sifaka ngokuqondile amanani. Masingagxili esenzweni esimaphakathi.

Ekuqaleni, sinetafula elifana naleli.

Siyazi ukuthi esibonelweni sethu izinga le-VAT libekwe kumaphesenti ayi-18. Ngakho-ke, ukuze unqume inani eliphelele lezimpahla nge-VAT, kufanele uqale unqume inani lentela, bese uyengeza enanini.

Kubalulekile ukukhumbula ukubhala abakaki, njengoba betshela uhlelo ukuthi kwenziwa kanjani imisebenzi yezibalo.

Ukuze unciphise inombolo ngephesenti elithile, ifomula icishe ifane, ngaphandle kokuthi esikhundleni sokwengeza, kwenziwa umsebenzi wokukhipha.

Bala Umehluko Wamaphesenti ku-Excel

Umehluko isilinganiso esiveza izinga lokushintsha kwentengo, esivezwa ngeyunithi ethile. Esimweni sethu, lawa ngamaphesenti. 

Masingacabangi nge-Excel kuqala, kodwa sicabangele isimo sisonke. Ake sithi amatafula abiza ama-ruble angu-100 ngenyanga edlule, futhi manje abiza ama-ruble angu-150. 

Kulokhu, kufanele kusetshenziswe ifomula elandelayo ukuze kunqunywe ukuthi leli nani lishintshwe kangakanani.

Umehluko wamaphesenti = (idatha entsha - idatha endala) / idatha endala * 100%.

Esimweni sethu, intengo ikhuphuke ngo-50%.

amaphesenti okususa ku-excel

Futhi manje sizochaza indlela yokwenza okufanayo ku-Excel. Nasi isithombe-skrini sokucaca. Naka ibha yefomula.

Kubalulekile ukusetha ifomethi yephesenti ukuze amanani aboniswe ngendlela efanele.

Uma udinga ukubala ukuthi intengo ishintshile ngamaphesenti amangaki uma iqhathaniswa naleyo ekhonjiswe emugqeni odlule, udinga ukusebenzisa le fomula (naka isithombe-skrini).

Ngokuvamile, kubonakala kanje: (inani elilandelayo - inani langaphambilini) / inani langaphambilini.

Njengoba ukucaciswa kwedatha kunganikezi ithuba lokwethula ushintsho lwephesenti ngomugqa, ingahle yeqiwe. 

Ngezinye izikhathi kungase kudingeke wenze isiqhathaniso noJanuwari. Ukuze wenze lokhu, udinga ukuguqula isixhumanisi sibe esiphelele, bese usebenzisa umaka wokuqedela ngokuzenzakalela uma kudingeka.

Ikhipha amaphesenti kusipredishithi se-excel esiqediwe

Kodwa kuthiwani uma ulwazi selufakiwe kakade etafuleni? Kulokhu, kufanele uqale ubeke ikhesa esitokisini esiphezulu esingenalutho bese ufaka uphawu =. Ngemva kwalokho, chofoza iseli eliqukethe inani ofuna ukunquma ngalo iphesenti. Okulandelayo, cindezela - (ukwenza umsebenzi wokukhipha) bese uchofoza kuseli efanayo). Bese sicindezela isithonjana senkanyezi (esho umsebenzi wokuphindaphinda ku-Excel) bese uthayipha inani lamaphesenti adinga ukukhishwa kule nombolo. Ngemuva kwalokho, mane ubhale uphawu lwephesenti bese uqinisekisa ukufakwa kwefomula ngokhiye u-Enter.

Umphumela uzovela kuseli efanayo lapho ifomula ibhalwe khona.

Ukuyikopisha ngokuqhubekayo phansi kwekholomu futhi wenze umsebenzi ofanayo ngokuphathelene neminye imigqa, kufanele usebenzise umaka wokuqedela ngokuzenzakalela njengoba kuchazwe ngenhla. Okusho ukuthi, hudula iseli ekhoneni elingezansi kwesokudla uye enanini elidingekayo lamaseli uye phansi. Ngemva kwalokho, kuseli ngalinye uzothola umphumela wokukhipha iphesenti elithile enombolweni enkulu.

Ukukhipha intshisekelo kuthebula elinamaphesenti angashintshi

Ake sithi sinetafula elinjalo.

Kuyo, elinye lamaseli liqukethe iphesenti elingashintshi kuzo zonke izibalo kuwo wonke amaseli ale kholomu. Ifomula esetshenziswa kulesi simo ibonakala kusithombe-skrini esingenhla (iseli G2 liqukethe nje iphesenti elinjalo).

Uphawu lwereferensi oluya ekhelini eliphelele leseli lungacaciswa ngokuzenzela (ngokuvele ulufake ngaphambi kwekheli lomugqa noma ikholomu), noma ngokuchofoza iseli bese ucindezela inkinobho ethi F4. 

Lokhu kuzolungisa isixhumanisi ukuze singashintshi lapho sikopishwa kwamanye amaseli. Ngemva kokucindezela ukhiye we-Enter, sithola umphumela obaliwe oqediwe.

Ngemva kwalokho, ngendlela efanayo njengasezibonelweni ezingenhla, ungasebenzisa umaka wokuqedela ngokuzenzakalela ukuze welule ifomula kuwo wonke amaseli kukholamu.

Dala ishadi lephesenti ku-Excel

Kwezinye izimo, ungase ufune ukudala ishadi lephesenti. Lokhu kungenziwa ngezindlela eziningana. Esokuqala siwukwenza ikholomu ezoklelisa amaphesenti azosetshenziswa njengomthombo wedatha. Esimweni sethu, leli yiphesenti lakho konke ukuthengisa.

Ngaphezu kwalokho, ukulandelana kwezenzo kungokulandelayo:

1. Khetha ithebula elinolwazi. Esimweni sethu, lolu uhlu lwamaphesenti.
2. Yiya kuthebhu ethi "Faka" - "Umdwebo". Sizodala ishadi likaphayi, ngakho lolu wuhlobo esilukhethayo.
   

   

3. Okulandelayo, uzocelwa ukuthi ukhethe ukubukeka komdwebo wesikhathi esizayo. Ngemva kokuyikhetha, ivele ivele.
   

   

Khona-ke ungakwazi ukuyilungisa ngokusebenzisa ithebhu ekhethekile "Ukusebenza ngemidwebo" - "Umklami". Lapho ungakhetha izinhlobo eziningi ezahlukene zezilungiselelo:

1. Ukushintsha uhlobo lweshadi. Uma uchofoza inkinobho ehambisanayo, uzokwazi ukusetha uhlobo lweshadi.
   

   

2. Shintshanisa imigqa namakholomu. 
3. Shintsha idatha esetshenziswa eshadini. Isici esiwusizo kakhulu uma uhlu lwamaphesenti ludinga ukushintshwa. Isibonelo, ungakopisha ulwazi lokuthengisa lwenyanga edlule, esikhundleni salo ufake enye ikholomu ngamaphesenti amasha, bese ushintsha idatha yeshadi ibe yileyamanje.
4. Hlela idizayini yeshadi. 
5. Hlela izifanekiso nezakhiwo. 

Inketho yokugcina inentshisekelo ethile kithi, ngoba kungenxa yayo lapho ungasetha ifomethi yephesenti. Ohlwini nje lwezakhiwo olwanikezwa yi-Excel, sithola inketho lapho izithonjana zephesenti zidwetshwa khona emikhakheni.

Ungakwazi futhi ukubonisa idatha ngefomethi yephesenti ngenye indlela. Ukuze wenze lokhu, chofoza ishadi likaphayi elikhona, hamba kuthebhu ethi “Isakhiwo” bese uthola inketho ethi “Amalebula Wedatha” lapho.

Uhlu lwemisebenzi luzovuleka lapho udinga ukukhetha indawo yesiginesha.

Ngemva kokwenza lokhu, isithombe sephesenti sizovela eshadini.

Uma uchofoza kwesokudla kwenye yazo, bese usebenzisa imenyu ethi “Ifomethi Yelebula Yedatha”, ungakwazi ukumisa amalebula kalula. Esimweni sethu, sinentshisekelo yokufaka amasheya kumasiginesha, ngoba le nto kufanele ikhethwe ukuze kuqinisekiswe ifomethi yephesenti.

Futhi ifomethi yephesenti ngokwayo isethwe kumenyu ethi "Inombolo", engavulwa ngephaneli etholakala kwesokunxele sebhokisi lengxoxo.

Njengoba ubona, ukusebenza ngamaphesenti ku-Excel akudingi amakhono akhethekile. Udinga nje ukufunda amaqhinga ambalwa ukuze wenze ngisho nemisebenzi eyinkimbinkimbi kalula nangobuhle. Yiqiniso, lena akuyona yonke imisebenzi etholakalayo kumsebenzisi we-Excel, ngoba amaphesenti angabuye alawulwe ngezinye izindlela, isibonelo, ngokusebenzisa i-macro. Kodwa lokhu kakade kuyizinga elithuthuke kakhulu, elidinga ulwazi lwezihloko eziyinkimbinkimbi. Ngakho-ke, kunengqondo ukushiya umsebenzi ngamaphesenti ngama-macro ukuze kamuva. 

Amaphesenti asebenziseka kalula kwinani lamafomula, ngalinye elingashintshwa ukuze lihambisane nezidingo zomsebenzisi othile.