Indlela yokubala iipesenti kwi-Excel inyathelo ngenyathelo

Ukulawula indlela yokubala iipesenti kwi-Excel Kwenza wonke umahluko phakathi kokuzabalaza nekhaltyhuleyitha nokuvumela i-spreadsheet yenze umsebenzi onzima kuwe. Ukusuka kwinto elula "yintoni i-12% ye-200?" ukubala izaphulelo, iirhafu, ukuhluka kwenyanga-ngenyanga, okanye yiyiphi ipesenti yetotali yedatha nganye emele, yonke into inokuthi izenzekele ngeefomyula ezimbalwa ezilula kakhulu.

Kulo lonke eli nqaku uza kubona, inyathelo ngenyathelo, Zonke iindlela ezisebenzayo zokusebenza ngeepesenti kwi-ExcelZifakwa njani ngokuchanekileyo, indlela yokubonisa iziphumo ngophawu lwe-%, indlela yokubala ipesenti yenani, indlela yokufumanisa ukuba yeyiphi ipesenti inxalenye yetotali imele, ukubalwa njani ukonyuka kunye nokuncipha, izaphulelo, i-VAT, irhafu yengeniso, kunye nendlela yokubuyisela ixabiso lilonke xa usazi kuphela ixabiso elithotyiweyo kunye nepesenti esetyenzisiweyo.

Indlela i-Excel eziqonda ngayo iipesenti kunye nendlela yokuyifomatha

Ngaphambi kokuba uqale ukubhala iifomula, kubalulekile ukuqonda Indlela i-Excel etolika ngayo iipesenti ngaphakathiKwinkqubo, ipesenteji ayikho ngaphezu kwenani lokugqibela: i-10% ngu-0,1; I-25% yi-0,25; I-75% yi-0,75 kunye ne-100% yi-1.

Xa ufaka isicelo ifomathi yepesenti Xa ufomatha iseli njengedesimali, i-Excel ibonisa ngokulula ukuba idesimali iphindwe nge-100 kwaye yongeza i-% isimboli. Umzekelo, ukuba uno-0,1 kwiseli kwaye usebenzise i-% ifomathi, uyakubona i-10%; Ukuba une-0,754, ngokuxhomekeke kwizicwangciso zakho zedesimali, uyakubona into efana ne-75,4% okanye 75,40%.

Ukuba sele unamanani abhaliweyo uze usebenzise ifomathi yepesenti, Excel phinda-phinda loo manani nge-100Ngamanye amagama, ukuba iseli iqulethe i-10 kwaye uyifometha njengepesenti, isiphumo esibonakalayo siya kuba yi-1000,00%. Oku kudla ngokubamba abantu abaninzi bengalindelanga kuba into esengqiqweni yokwenza i-10% iya kuba kukuchwetheza u-0,1 okanye ukubonisa ukuba iseli yayiyipesenti phambi kokufaka ixabiso.

Nangona kunjalo, ukuba uqala ukufomatha uluhlu olungenanto njengepesenti kwaye uchwetheze kuyo, Excel Iziphatha ngokwahlukileyo kuxhomekeke kwinaniUkuba uchwetheza i-10 (ilingana okanye inkulu kuno-1), iya kubonisa i-10%; ukuba uchwetheza i-0,1 (ngaphantsi kwe-1), uya kubona kwakhona i-10% kuba iyayiphinda-phinda nge-100 ukuyibonisa njengepesenti.

Ke ngoko, ukusebenza kakuhle ngeepesenti, kubalulekile ukuba ixabiso elisisiseko lichanekile: ukuba ufuna 10%Eyona ndlela icocekileyo kukubhala u-0,1 okanye 10 kwiseli esele inefomathi yepesenti esetyenzisiweyo, kwaye emva koko uyeke i-Excel iyibonise ngokuchanekileyo.

Indlela yokufaka ipesenti yokufomatha kwi-Excel

Ukubonisa iziphumo zakho ngophawu lwe-%, kufuneka wenze njalo sebenzisa ifomathi yepesenti kwiiseli apho ufuna ukubonisa loo maxabiso. Kuyakhawuleza kakhulu ukwenza oku kwiribhoni.

Okokuqala, khetha i iseli okanye uluhlu lweeseli ofuna ukuyifomatha. Emva koko, yiya kwithebhu ethi "Ekhaya" kwaye, ngaphakathi kweqela elithi "Inombolo", nqakraza kwi icon ngepesenti (%). Ngoko nangoko, amaxabiso aya kuboniswa njengepesenti eneendawo zedesimali eziqwalaselwe ngokungagqibekanga.

Ukuba ukhetha ukuhlengahlengisa ifomathi ngokweenkcukacha ngakumbi, ungacofa ekunene kukhetho kwaye ukhethe "Ifomathi yeseli"Kwifestile evulayo, khetha "Ipesenti" kuluhlu lwamacandelo kwaye wenze isigqibo sokuba zingaphi iindawo zedesimali ofuna ukuzibonisa. Umzekelo, ukuba ufaka u-0 kwi-"Decimal places," uya kubona i-10% endaweni ye-10,00%.

Ungasebenzisa iindlela ezimfutshane ukuya ngokukhawuleza: kwiinguqulelo ezininzi ze-Excel, ucinezela Ctrl + Shift + % Isebenzisa ngoko nangoko isimbo sepesenti kuluhlu olukhethiweyo, ngaphandle kokudlula kwiimenyu.

Gcina ukhumbula ukuba, njengoko sibonile ngaphambili, ukuba usebenzisa ipesenti yokufomatha kwiiseli esele inamanani, Excel Akatoliki loo manani njengeepesenti. ngomlingo; iyaziphinda-phinda nge 100 kwaye yongeza i% isimboli, ke qiniseka ukuba ixabiso lakho lokuqala lichanekile.

Bala ukuba yipesenti emele inxalenye yetotali

Olunye lwemisebenzi eqhelekileyo kukwazi Yeyiphi ipesenti emele ubungakanani ngokunxulumene netotali?Ngokwezibalo kulula njengokwahlula ixabiso lenxenye ngexabiso lilonke kwaye, ukuba uyafuna, phinda-phinda nge-100 ukubonisa isiphumo njengepesenti.

Ifomula ngokubanzi iyakuba: ipesenti = (inxalenye / iyonke)Ukuba ufomatha elo lahlulo njengepesenti, awudingi kuphinda-phinda nge-100 kuba i-Excel iyakwenza oko ngokubonakalayo kuwe. Ukuba awuyi kufomatha, ke kungcono ukubhala (inxalenye / iyonke) * 100. Ukuba ufuna ukuphonononga imisebenzi ye-arithmetic kwi-Excel, bona imisebenzi yezibalo kwi-Excel.

Khawube nomfanekiso-ngqondweni weklasi yamabanga aphantsi enabafundi abangama-31, abali-17 kubo ingamantombazana. Ixabiso elingaphelelanga ngamantombazana (i-17), kwaye ixabiso lilonke ngabo bonke abafundi (31). Kwiseli, ungabhala = 17/31 kwaye, emva kokuyifomatha njengepesenti, uya kufumana malunga 54,8%Ngale ndlela unokuthelekisa ngokufanelekileyo nezinye iiklasi ezinabafundi abaninzi okanye abambalwa.

Ukuba ubufuna ukubona ubalo ngaphandle kokusebenzisa iiseli zereferensi, ungachwetheza ngokuthe ngqo kwiseli: = 17 / 31 * 100Kule meko, isiphumo siya kuba ngama-54,8 ngaphandle kwesimboli ye-%, kuba wenze uphinda-phindo ngo-100.

Le ndlela inye iluncedo kwimibuzo efana nokuthi “imele yeyiphi ipesenti = 7 / 30"?" (umzekelo, ama-apile athatyathwe kwitotali kwijagi) okanye “yeyiphi ipesenti yeklasi ephumeleleyo?”. Chwetheza ngokulula =7/30 kwiseli, sebenzisa ipesenti yokufomatha, kwaye uya kufumana eyona pesenti.

Bala ipesenti yesixa

Elinye ityala elisisiseko libuyela umva: ufuna ukwazi Yintoni ipesenti ethile yenani?Umzekelo, sithini isixa-mali esilingana ne-12% yama-200 okanye ama-40 ekhulwini angama-340? Apha umsebenzi uthe tye kakhulu: phinda-phinda inani elipheleleyo ngepesenti kwifomu yesishumi.

Ifomula eqhelekileyo iyakuba: ixabiso = lilonke * ipesentiUkuba unetotali kwiseli enye kunye nepesenti kwenye, isenzo esiqhelekileyo kukusebenzisa iireferensi zeeseli. Ngokomzekelo, ukuba inani likwi-D2 kwaye ipesenti iku-E2, ifomula iya kuba = D2 * E2.

Yiba nomfanekiso ufuna ukwazi i-12% yama-200. Ungabhala kwiseli: =200*12% Ngaphandle koko, ukuba unayo i-200 kwi-A1 kunye ne-0,12 kwi-B1, uhlobo = A1 * B1. Naluphi na ukhetho luya kukunika ama-24 ngenxa yoko. Ukuba utshintsha i-200 okanye uguqule ipesenti, ixabiso liya kuhlaziya ngokuzenzekelayo ngaphandle kokuba uphinde ubale.

Omnye umzekelo oqhelekileyo: ukubala ukuba yeyiphi ipesenti I-40% ye-340Ungabhala ngokuthe ngqo kwiseli: =340*40/100, okanye ubhale 340 kwenye iseli, 40 kwenye, kwaye kwiseli yesithathu ubeke =(total_cell*percentage_cell)/100. Isiphumo kuzo zombini iimeko yi-136.

Ukusebenza, kulula kakhulu ukubhala ipesenti nge % isimboli (umzekelo, 40%) kwiseli efomatiweyo njengepesenti kwaye wenze ngokulula. iyonke * ipesenti_yeseliNgaloo ndlela akunyanzelekanga ukuba uqhubeke usahlule nge-100 ngokuphindaphindiweyo.

Bonisa iipesenti kwaye ulungelelanise idesimali

Xa uguqulela inxalenye/ifomula iyonke kwipesenti, i-Excel iqhelekile Susa iindawo ezingagqibekanga zedesimaliNgamanye amazwi, ukuba eyona sibalo yi-0,548, ukusebenzisa i-% ukufomatha kunokukubonisa i-55% ngokusondeza.

Ukuba ufuna ukuchaneka ngakumbi, ungaya ku- "Format Cells," khetha udidi "Ipesenti", kwaye wandise inani leendawo zedesimali. Ngale ndlela, endaweni ye-55%, uya kubona, umzekelo, 54,8% okanye 54,84% kuxhomekeke ukuba ufuna iinkcukacha kangakanani isiphumo.

Oku kulungelelaniswa kubalulekile xa usebenza neenkcukacha zemali, amanqaku, izibalo, okanye nayiphi na enye imeko apho Indawo enye ephezulu okanye ezantsi inokufanelekaUmahluko we-0,5% unokubonakala ungabalulekanga kuluhlu lwabafundi, kodwa yongeza kwisixa esikhulu semali kwibhajethi enkulu.

Iipesenti zemiqolo emininzi kunye neepesenti zetotali

Kwiimeko ezininzi awufuni nje ukubala ipesenti yexabiso elinye, kodwa bona yipesenti engakanani yetotali emele umqolo ngamnye? kwitafile yedatha. I-Excel nayo isombulula oku ngaphandle kobunzima obukhulu ngokudibanisa i-SUM kunye nokwahlula.

Yiba nomfanekiso wetafile ekwivenkile encinci apho ikholamu B idwelisa iiyunithi ezithengiweyo zemveliso nganye. Okokuqala, kwiseli ekupheleni kwekholomu (umzekelo, B11), ubala inani elipheleleyo usebenzisa ifomula = SUM (B2: B10). Loo nombolo iya kuba yeyakho zizonke Ukuthenga.

Emva koko, kwenye ikholamu (umzekelo, ikholamu C), kumqolo wesi-2, bhala ifomula ukufumanisa ukuba yeyiphi ipesenti yetotali ehambelana naloo mgca: =B2/$B$11Ukusebenzisa iireferensi ezipheleleyo (kunye neempawu zeedola) kuthintela iseli equlethe inani elipheleleyo ekutshintsheni xa ifomula ikhutshelwa phantsi.

Ngokusebenzisa ipesenti yokufomatha kwikholam C, uya kufumana ipesenti yemveliso nganye. ipesenti yokuthenga iyonkeNgale ndlela unokubona, umzekelo, ukuba into ethile imele i-30% yetotali, enye i-5%, njl.

Ukuba imveliso efanayo ivela kwimiqolo emininzi kwaye ufuna ukwazi ukuba yeyiphi ipesenti yetotali iyonke imele, ungasebenzisa umsebenzi. YONGEZA UKUBAUmzekelo, ukuba ikholamu A iqulethe amagama emveliso kunye nekholamu B iqulethe iiyunithi, kwaye kwiseli E1 ubhala igama elithi "Iibhulukhwe", kwiseli E2 ungabeka ifomula = SUMIF(A2:A10,E1,B2:B10)/$B$11, eya kubuyisela ipesenti emele zonke "Iibhulukhwe" ngokubhekiselele kwitotali.

Bala utshintsho lwepesenti kunye nokwahluka

Enye imeko eqhelekileyo kakhulu kukuba Thelekisa amanani amabini kwaye ubone ukuba yeyiphi ipesenti ayitshintshileyo.Umzekelo, ukuthengisa ukusuka kwinyanga ukuya kwelandelayo, ukutyelela iwebhusayithi kulo nyaka xa kuthelekiswa nonyaka ophelileyo, okanye iikhilogram zemveliso kwisitokhwe kwii-inventri ezimbini ezahlukeneyo.

Ifomula ngokubanzi yotshintsho lwepesenti yile: (ixabiso_elitsha − elidala_ixabiso) / elidala_ixabisoUkuba ufomatha eso siphumo njengepesenti, uya kubona ukonyuka okanye ukuncipha kubonakaliswe ngophawu lwe-%.

Umzekelo, khawucinge ingeniso yakho ngoNovemba yayiyi-2342 yee-euro kwaye ngoDisemba i-2500. Kwiseli, ungabhala =(2500−2342)/2342. Isiphumo sedesimali simalunga ne-0,06746, ethi, ifomathiwe njengepesenti, iya kuboniswa njenge Ukwanda nge-6,75% (kuxhomekeke kwinani leendawo ozikhethileyo).

Ukuba umvuzo wakho ngoJanuwari yi-2425 € kwaye ufuna ukubona utshintsho xa uthelekisa noDisemba, uya kusebenzisa ifomula =(2425−2500)/2500. Iziphumo ngu- -0,03 kwidesimali; xa usebenzisa ifomathi yepesenti uya kubona −3,00%, ebonisa a Ukuncitshiswa okungama-3%.

Kwiitheyibhile zeentengiso zenyanga, kuqheleke kakhulu ukubeka iimali kwikholamu enye kunye nokukhula kwenyanga-ngenyanga kwikholamu ekufutshane. Ngokomzekelo, ukuba ukuthengiswa kukaJanuwari ku-B2, ukuthengiswa kukaFebruwari kwi-B3, njalo njalo, kwi-C3 ungabhala =(B3−B2)/B2 kwaye ukhuphele ifomyula phantsi ukujonga ipesenti yendalo ngepesenti.

Nyusa okanye unciphise ixabiso ngepesenti

I-Excel nayo inokukunceda wenze isigqibo Yimalini yokunyusa okanye ukuthoba isixa usebenzisa ipesentiOku kuluncedo kakhulu xa uhlengahlengisa uhlahlo lwabiwo-mali, amaxabiso, imivuzo, iindleko, okanye nabuphi na obunye ubungakanani ofuna ukulungisa.

Ingqiqo ilula: ukuba ufuna ukonyusa inani ngo X%, uphinda-phinda nge (1 + X) kunye (1 − X)Ukuba ufuna ukuyinciphisa ngo-X%, uphinda-phinda ngo (1 − X). Kuzo zombini iimeko, u-X ubonakaliswa njengedesimali (umzekelo, i-0,25 ye-25%).

Khawucinge ukuba uchitha i-€ 113 ngeveki ekutyeni kwaye ufuna ukwazi ukuba yimalini onokuyisebenzisa ukuba unyuse uhlahlo lwabiwo-mali nge-25%. Kwiseli esimahla, ungabhala =113*(1+0,25). Isiphumo kukuba 141,25 €engumda wakho omtsha wenkcitho weveki. Ukuba usebenza ngohlahlo lwabiwo-mali, qhagamshelana uhlahlo lwabiwo-mali lomntu.

Ukuba endaweni yokuyinyusa ufuna ukunqumla olo hlahlo lwabiwo-mali ngama-25%, ifomula iya kuba =113*(1−0,25). Isiphumo kukuba 84,75 €eya kuba yimali entsha omele uzame ukuba ungayigqithi.

Oku kunokwenziwa rhoqo ngenyanga nezinye iindleko. Ngokomzekelo, ukuba inkampani ineendleko zenyanga eziqhelekileyo ze-€ 1000 kwaye ithatha isigqibo sokuyinyusa nge-10% ukwandisa izinto ezithile, iya kungena = 1000 * (1 + 0,10) kwiseli; ukuba ifuna ukuthoba ezo ndleko nge-10%, iyakusebenzisa =1000*(1−0,10), ifumana ixabiso lesiphumo ngokuthe ngqo.

Bala izaphulelo kunye namaxabiso okugqibela usebenzisa iipesenti

Iipesenti zibalulekile xa kufikwa Bala izaphulelo kwi-ExcelNokuba uthenga into ethengiswayo okanye udala uluhlu lwamaxabiso eshishini lakho, kubalulekile ukwazi indlela yokumisela iifomula ngokuchanekileyo.

Ukuba imveliso ixabisa i-€50 kwaye inesaphulelo se-30%, ufuna ukwazi ixabiso lokugqibela oya kulihlawula. Indlela ecace kakhulu yokubona oku: final_price = ixabiso − (ixabiso × isaphulelo)Kwi-Excel, ukuba ixabiso li-A2 kwaye ipesenti yesaphulelo kwi-B2 (efana ne-30% okanye i-0,3), kwi-C2 ungabhala = A2-(A2 * B2).

Enye indlela elinganayo, kwaye ngamanye amaxesha ngokukhawuleza, kukuphindaphinda ngokuthe ngqo (1 - discount_percentage). Ukulandela umzekelo webhatyi ye-50 ye-euro enesaphulelo se-30%, kuya kwanela ukuba: =50*(1−0,30)Isiphumo siya kuba yi-35 €, imali oza kuyihlawula.

Esi sikimu sikwasebenza kumatyala apho ixabiso elithotyiweyo lilelo olaziyo kwaye ufuna ukulifumana. ixabiso lokuqalaUmzekelo, ukuba ihempe ithengiswa nge-15 € kwaye uyazi ukuba ixabiso li-25% ngaphantsi kweyokuqala, uhlawula i-75% yexabiso. Kwi-Excel, uya kungena = 15 / 0,75, kwaye uya kufumana i-€ 20 njengexabiso ngaphandle kwesaphulelo.

Xa usebenza ngeeseli, kuluncedo kakhulu ukuba nexabiso lokuqala kwikholamu enye, isaphulelo kwenye, ixabiso lokugqibela kwelinye, kwaye, ukuba ufuna, isixa esithotyiweyo kwikholamu yesine. Ngeefomula ezilula ezifana A2*B2 kunye no-A2−(A2*B2) unokuba nalo lonke ulwazi olubonakalayo.

I-VAT, irhafu yengeniso yomntu kunye nezinye iirhafu ngeepesenti

Kwi-billing kunye ne-accounting, nokuba kusekhaya okanye kubuchwephesha, kuyinto eqhelekileyo ukuba yongeza okanye uthabathe iirhafu ichazwa njengepesenti, efana ne-VAT okanye irhafu yengeniso. I-Excel iwukhawulezisa kakhulu lo msebenzi, ngakumbi xa unemveliso emininzi okanye imigca yenkonzo.

Ukongeza i-VAT kwixabiso elisisiseko, unokulandela ingqiqo efanayo naleyo isetyenziselwa ukonyuka kohlahlo lwabiwo-mali. Ukuba ixabiso lerhafu yangaphambili liku-A2 kwaye izinga le-VAT (umzekelo, i-21%) likwi-B2, kwi-C2 ungabala isixa se-VAT usebenzisa... = A2 * B2, kunye ne-D2 iyonke kunye neerhafu kunye = A2 + C2.

Olunye ukhetho oluhlangeneyo kukubala inani elipheleleyo ngokuthe ngqo usebenzisa ifomula =A2*(1+B2)eyongeza i-21% ukuya kwi-100% yexabiso. Ngoko ke, ukuba isiseko esirhafiswa yi-€ 100, ukusebenzisa =100*(1+0,21) kuya kukunika i-121 €.

Kwimeko yerhafu yengeniso yomntu okanye nayiphi na enye ingcamango ekufuneka ibe thabatha kwitotaliIngqiqo i-symmetrical kodwa ibandakanya ukuthabatha. Ukuba unemali exhomekeke kwi-15% yokubamba irhafu, ungafumana ixabiso eliseleyo ngokuchwetheza = isixa*(1−0,15). Oku kuya kukunika imali ngokuthe ngqo emva kokubamba irhafu.

Ukuba wenza itheyibhile ye-invoyisi enezintlu zesiseko, iVAT, irhafu yengeniso, kunye netotali, ungasebenzisa iifomula ezifana (isiseko + iVAT - irhafu yengeniso yomntu) kwikholamu iyonke ukubonisa ngokucacileyo yonke imisebenzi yokudibanisa nokuthabatha ngeepesenti.