Indlela yokubala amaphesenti ku-Excel - ngezibonelo zefomula

Lo mbhalo unikeza ulwazi oluningiliziwe mayelana nendlela yokubala inzuzo ku-Excel, uchaza amafomula ayinhloko nangeziwe (khuphula noma nciphisa inani ngephesenti elithile).

Cishe ayikho indawo yempilo lapho ukubalwa kwenzuzo bekungeke kudingeke khona. Kungaba ithiphu koweta, ikhomishini eya kumdayisi, intela yemali engenayo noma inzalo ye-mortgage. Isibonelo, ingabe uye wanikezwa isaphulelo samaphesenti angu-25 kukhompuyutha entsha? Ingabe lolu hlelo luzuzisa ngezinga elingakanani? Futhi yimalini okufanele uyikhokhe, uma ususa inani lesaphulelo.

Namuhla uzokwazi ukwenza imisebenzi yamaphesenti ahlukahlukene ku-Excel ngokuphumelelayo.

Ifomula eyisisekelo yokunquma amaphesenti yenani eliphelele

Igama elithi "iphesenti" livela kwisiLatini. Lolu limi lunokwakhiwa "percentum", okuhunyushwa ngokuthi "ikhulu". Abantu abaningi abavela ezifundweni zezibalo bangakhumbula ukuthi yiziphi amafomula akhona.

Iphesenti liyingxenye yenombolo 100. Ukuze uyithole, udinga ukuhlukanisa inombolo A ngenombolo B bese uphindaphinda inombolo ewumphumela ngo-100.

Empeleni, ifomula eyisisekelo yokunquma amaphesenti imi kanje:

(Inombolo yengxenye/Inombolo ephelele)*100.

Ake sithi unama-tangerine angama-20, futhi ufuna ukunikeza ama-5 awo ngoNyaka Omusha. Ingakanani ngamaphesenti? Ngemva kokwenza imisebenzi elula (=5/20*100), sithola u-25%. Lena indlela eyinhloko yokubala iphesenti lenombolo empilweni evamile.

Ku-Excel, ukuthola amaphesenti kulula nakakhulu ngoba umsebenzi omningi wenziwa uhlelo olungemuva.

Kuyadabukisa, kepha ayikho indlela eyingqayizivele ekuvumela ukuthi wenze zonke izinhlobo ezikhona zokusebenza. Konke kuthonywa umphumela odingekayo, ukuze kuzuzwe izibalo ezenziwayo.

Ngakho-ke, nansi imisebenzi elula ku-Excel, njengokunquma, ukukhulisa / ukwehlisa inani lento ngokwemibandela yamaphesenti, ukuthola inani elilinganayo lephesenti.

Indlela eyinhloko yokunquma amaphesenti ku-Excel

Ingxenye/Ingqikithi = iphesenti

Uma uqhathanisa ifomula eyinhloko kanye nendlela yokunquma amaphesenti kumaspredishithi, ungabona ukuthi esimweni sakamuva asikho isidingo sokuphindaphinda inani eliwumphumela ngo-100. Lokhu kungenxa yokuthi i-Excel ikwenza ngokwayo lokhu uma uqala ukuguqula uhlobo lweseli. kuya “kumaphesenti”.

Futhi yiziphi ezinye izibonelo ezisebenzayo zokunquma iphesenti ku-Excel? Ake sithi ungumdayisi wezithelo nokunye ukudla. Unedokhumenti ebonisa inani lezinto ezi-odwe amakhasimende. Lolu hlu lunikezwe kukholamu A, kanye nenani lama-oda kukholamu B. Amanye awo kufanele alethwe, futhi le nombolo inikezwe kukholamu C. Ngokuvumelana nalokho, ikholamu D izobonisa inani lemikhiqizo elethiwe. Ukuze uyibale, udinga ukulandela lezi zinyathelo:

1. Khombisa = C2 / B2 kuseli D2 bese uyihambisa phansi ngokuyikopishela enambeni edingekayo yamaseli.
2. Chofoza inkinobho ethi “Ifomethi Yephesenti” kuthebhu ethi “Ekhaya” esigabeni “Inombolo”.
3. Khumbula ukwandisa inani lamadijithi ngemva kwephoyinti ledesimali uma kunesidingo.

Yilokho kuphela.

Uma uqala ukusebenzisa indlela ehlukile yokubala isithakazelo, ukulandelana kwezinyathelo kuzofana.

Kulokhu, iphesenti eliyindilinga lemikhiqizo elethiwe liboniswa kukholomu D. Ukuze wenze lokhu, susa zonke izindawo zedesimali. Uhlelo luzobonisa ngokuzenzakalelayo inani eliyindilinga.

Kwenziwa ngale ndlela

Ukunquma ingxenye yenani eliphelele

Indaba yokunquma isabelo senani eliphelele njengephesenti elichazwe ngenhla ivame kakhulu. Ake sichaze izimo ezimbalwa lapho ulwazi olutholiwe lungasetshenziswa khona.

Ikesi 1: inombolo ingaphansi kwetafula kuseli ethile

Abantu bavame ukubeka inani eliphelele ekugcineni kwedokhumenti kuseli ethile (imvamisa ngezansi kwesokudla). Kulesi simo, ifomula izothatha ifomu elifanayo nalelo elinikezwe ngaphambili, kodwa nge-nuance encane, njengoba ikheli leseli e-denominator liphelele (okungukuthi, liqukethe idola, njengoba kuboniswe esithombeni esingezansi) .

Uphawu lwedola $ lukunikeza ikhono lokuhlanganisa isixhumanisi seseli ethile. Ngakho-ke, izohlala injalo, nakuba ifomula izokopishelwa endaweni ehlukile. Ngakho-ke, uma ukufundwa okuningana kukhonjisiwe kukholamu B, futhi inani lazo eliphelele libhalwe kuseli B10, kubalulekile ukunquma iphesenti usebenzisa ifomula: =B2/$B$10.

Uma ufuna ikheli leseli B2 lishintshe kuye ngendawo yekhophi, kufanele usebenzise ikheli elihlobene (ngaphandle kophawu lwedola).

Uma ikheli libhalwe esitokisini $B$10, lapho idinomineyitha izofana kuze kufike kumugqa 9 wethebula elingezansi.

Isincomo: Ukuze uguqule ikheli elihlobene libe ikheli eliphelele, kufanele ufake uphawu lwedola kulo. Kungenzeka futhi ukuthi uchofoze isixhumanisi esidingekayo kubha yefomula bese ucindezela inkinobho ethi F4.

Nasi isithombe-skrini esibonisa umphumela wethu. Lapha sifomethe iseli ukuze kuboniswe izingxenyana ezifika kwekhulu.

Isibonelo sesi-2: izingxenye zazo zonke zikleliswe emigqeni eyahlukene

Isibonelo, ake sithi sinomkhiqizo odinga ukuthungwa okuningi, futhi sidinga ukuqonda ukuthi lo mkhiqizo udume kangakanani ngokumelene nokwasemuva kwakho konke ukuthengwa. Khona-ke kufanele usebenzise umsebenzi we-SUMIF, okwenza kube nokwenzeka ukungeza kuqala zonke izinombolo ezingabalelwa esihlokweni esinikeziwe, bese uhlukanisa izinombolo ezihlobene nalo mkhiqizo ngomphumela otholwe ngenqubo yokwengeza.

Ukuze kube lula, nali ifomula:

=I-SUMIF(ububanzi benani, isimo, uhla lokuhlanganisa)/sum.

Njengoba ikholomu A iqukethe wonke amagama emikhiqizo, futhi ikholomu B ibonisa ukuthi bangaki ukuthengwa okwenziwe, futhi iseli E1 ichaza igama lomkhiqizo odingekayo, futhi isamba sawo wonke ama-oda siyiseli B10, ifomula izobukeka kanje:

=SUMIF(A2:A9 ,E1, B2:B9) / $B$10.

Futhi, umsebenzisi angakwazi ukubeka igama lomkhiqizo ngokuqondile esimweni:

=SUMIF(A2:A9, «cherries», B2:B9) / $B$10.

Uma kubalulekile ukunquma ingxenye kusethi encane yemikhiqizo, umsebenzisi angabeka isamba semiphumela etholwe emisebenzini eminingana ye-SUMIF, abese ebonisa inani eliphelele lokuthengwa ku-denominator. Ngokwesibonelo, kanje:

=(SUMIF(A2:A9, «cherries», B2:B9) + SUMIF(A2:A9, «ama-apula», B2:B9)) / $B$10.

Ungabala kanjani izinga lokulungiswa kwevelu njengephesenti ku-Excel

Ziningi izindlela zokubala. Kodwa, mhlawumbe, ifomula yokunquma ushintsho kumaphesenti isetshenziswa kaningi. Ukuze uqonde ukuthi inkomba ikhule noma yehle kangakanani, kunefomula:

Ushintsho lwamaphesenti = (BA) / A.

Lapho wenza izibalo zangempela, kubalulekile ukuqonda ukuthi yikuphi okuguquguqukayo okufanele kusetshenziswe. Isibonelo, enyangeni edlule bekunamapentshisi angama-80, futhi manje aseyi-100. Lokhu kukhomba ukuthi njengamanje unamapentshisi angama-20 kunangaphambili. Ukwenyuka kube ngamaphesenti angama-25. Uma ngaphambi kwalokho kwakukhona amapentshisi angu-100, futhi manje kukhona angama-80 kuphela, lokhu kubonisa ukwehla kwenani ngamaphesenti angu-20 (njengoba izingcezu ezingu-20 kweziyikhulu zingama-20%).

Ngakho-ke, ifomula ku-Excel izobukeka kanje: (Inani elisha – inani elidala) / inani elidala.

Futhi manje udinga ukuthola ukuthi ungayisebenzisa kanjani le fomula empilweni yangempela.

Isibonelo 1: ukubala ukuguqulwa kwevelu phakathi kwamakholomu

Ake sithi ikholomu B ibonisa izintengo zenkathi yokugcina yokubika, futhi ikholomu C ibonisa izintengo zalesi samanje. Bese ufaka ifomula elandelayo kuseli C2 ukuze uthole izinga loshintsho lwenani:

= (C2-B2) / B2

Ikala izinga lapho inani lemikhiqizo esohlwini lwekholomu A lenyuke noma lehle kangakanani uma kuqhathaniswa nenyanga edlule (ikholomu B).

Ngemva kokukopisha iseli emigqeni esele, setha ifomethi yephesenti ukuze izinombolo ngemva kweqanda ziboniswe ngendlela efanele. Umphumela uzofana nesithombe-skrini.

Kulesi sibonelo, izitayela ezinhle ziboniswa ezinkambisweni ezimnyama nezimbi ezibomvu.

Isibonelo sesi-2: ukubala izinga loshintsho phakathi kwemigqa

Uma kukhona ikholomu eyodwa kuphela yezinombolo (isibonelo, C equkethe ukuthengiswa kwansuku zonke neviki), uzokwazi ukubala ukuguqulwa kwephesenti enani usebenzisa le fomula:

= (S3-S2) / S2.

I-C2 ingeyokuqala kanti i-C3 iyiseli yesibili.

Note. Kufanele weqe umugqa wokuqala bese ubhala ifomula edingekayo kuseli yesibili. Esibonelweni esinikeziwe, lena yi-D1.

Ngemva kokusebenzisa ifomethi yephesenti kukholamu, umphumela olandelayo uzokhiqizwa.

Uma kubalulekile kuwe ukuthi uthole izinga lokuguqulwa kwenani leseli ethile, udinga ukumisa isixhumanisi usebenzisa amakheli aphelele aqukethe uphawu lwedola $.

Ngakho-ke, ifomula yokubala ukuguqulwa kwenani lama-oda ngoFebhuwari uma iqhathaniswa nenyanga yokuqala yonyaka imi kanje:

=(C3-$C$2)/$C$2.

Uma ukopisha iseli kwamanye amaseli, ikheli eliphelele alishintshi inqobo nje uma elihlobene liqala ukubhekisela ku-C4, C5, njll.

Ukubalwa kwentshisekelo ngemibandela yobuningi

Njengoba usubonile, noma yiziphi izibalo ku-Excel ziwumsebenzi olula. Ukwazi iphesenti, kulula ukuqonda ukuthi izoba yimalini kuyo yonke ngokwemibandela yedijithali.

Isibonelo 1: ukubala ingxenye yenani eliphelele

Ake sithi uthenga ikhompuyutha ephathekayo ngo-$950 futhi kufanele ukhokhe intela engu-11% ekuthengeni. Ingakanani imali okuzodingeka ikhokhwe ekugcineni? Ngamanye amazwi, u-11% wama-$950 angaba yimalini?

Ifomula ithi:

Inombolo ephelele * iphesenti = isabelo.

Uma sicabanga ukuthi konke kuseli A2, futhi iphesenti likuseli B2, liguqulwa libe into elula. =A2*B2  Inani elingu-$104,50 livela kuseli.

Khumbula ukuthi uma ubhala inani eliboniswa ngophawu lwephesenti (%), i-Excel ilihumusha njengekhulu. Isibonelo, u-11% ufundwa uhlelo njengo-0.11, futhi i-Excel isebenzisa lesi sibalo kuzo zonke izibalo.

Ngamanye amazwi, ifomula =A2*11% isifaniso =A2*0,11. Ngokwemvelo, ungasebenzisa inani elingu-0,11 esikhundleni sephesenti ngokuqondile kufomula uma lokho kufaneleka kakhulu ngaleso sikhathi.

Isibonelo sesi-2: ukuthola konke kuqhezu nephesenti

Isibonelo, umngane ukunikeze ikhompuyutha yakhe endala ngo-$400, okungamaphesenti angu-30 yenani lokuthenga, futhi udinga ukwazi ukuthi kubiza malini ikhompuyutha entsha.

Okokuqala udinga ukunquma ukuthi mangaki amaphesenti enani langempela lekhompyutha ephathekayo esetshenzisiwe.

Kuvele ukuthi intengo yayo ingamaphesenti angama-70. Manje udinga ukwazi ifomula yokubala izindleko zangempela. Okusho ukuthi, ukuqonda ukuthi iyiphi inombolo engu-70% ezoba ngu-400. Ifomula kanje:

Isabelo sengqikithi / iphesenti = inani eliphelele.

Uma isetshenziswa kudatha yangempela, ingathatha ifomu elilodwa kulawa alandelayo: =A2/B2 noma =A2/0.7 noma =A2/70%.

Ungashintsha kanjani inombolo ibe ngamaphesenti athile

Ake sithi isikhathi samaholidi sesiqalile. Ngokwemvelo, ukusetshenziswa kwansuku zonke kuzothinteka, futhi ungase uthande ukucabangela amanye amathuba okuthola inani elifanele leviki lonke ukusetshenziswa kweviki okunganyuka ngalo. Khona-ke kuyasiza ukwandisa inombolo ngamaphesenti athile.

Ukwandisa inani lemali ngenzalo, udinga ukusebenzisa ifomula:

= inani * (1+%).

Isibonelo, kufomula =A1*(1+20%) inani leseli A1 likhuphuka ngesihlanu.

Ukuze wehlise inombolo, sebenzisa ifomula:

= Okushiwo * (1–%).

Yebo, ifomula = A1*(1-20%) yehlisa inani kuseli A1 ngo-20%.

Esibonelweni esichaziwe, uma i-A2 kuyizindleko zakho zamanje futhi i-B2 iyiphesenti okufanele uzishintshe ngalo, udinga ukubhala amafomula kuseli C2:

1. Ukunyuka kwamaphesenti:  =A2*(1+B2).
2. Yehlisa ngephesenti: =A2*(1-B2).

Indlela yokukhulisa noma ukunciphisa wonke amanani ekholomu yonke ngephesenti

Ungawashintsha kanjani wonke amanani kukholomu abe amaphesenti?

Ake sicabange ukuthi unekholomu yamanani okudingeka uyishintshe ibe ingxenye ethile, futhi ufuna ukuba namanani abuyekeziwe endaweni efanayo ngaphandle kokwengeza ikholomu entsha ngefomula. Nazi izinyathelo ezi-5 ezilula zokuqedela lo msebenzi:

1. Faka wonke amanani adinga ukulungiswa kukholamu ethile. Ngokwesibonelo, kukholamu B.
2. Kuseli elingenalutho, bhala eyodwa yamafomula alandelayo (kuye ngokuthi umsebenzi):
   1. Khulisa: =1+20%
   2. Nciphisa: =1-20%.

Ngokwemvelo, esikhundleni sokuthi "20%" udinga ukucacisa inani elidingekayo.

1. Khetha iseli okubhalwe kulo ifomula (lena C2 esibonelweni esisichazayo) bese ukopisha ngokucindezela inhlanganisela yokhiye Ctrl + C.
2. Khetha isethi yamaseli adinga ukushintshwa, chofoza kwesokudla kuwo bese ukhetha okuthi “Namathisela Okukhethekile…” enguqulweni yesiNgisi ye-Excel noma “Namathisela Okukhethekile” kokuthi .

1. Okulandelayo, ibhokisi lengxoxo lizovela lapho udinga ukukhetha ipharamitha ethi "Values" (amanani), bese usetha ukusebenza njengokuthi "Phindaza" (phindaphinda). Okulandelayo, chofoza inkinobho ethi "OK".

Futhi nawu umphumela - wonke amanani kukholomu B akhuliswe ngo-20%.

Phakathi kwezinye izinto, ungaphindaphinda noma uhlukanise amakholomu ngamanani ngamaphesenti athile. Vele ufake iphesenti oyifunayo ebhokisini elingenalutho bese ulandela izinyathelo ezingenhla.