Yadda ake lissafin kashi a cikin Excel mataki-mataki

Don mamaye Yadda ake lissafin kashi a cikin Excel Yana yin kowane bambanci tsakanin gwagwarmaya tare da kalkuleta da barin maƙunsar rubutu ya yi muku aiki tuƙuru. Daga mai sauƙi "menene 12% na 200?" don ƙididdige rangwame, haraji, bambance-bambancen wata zuwa wata, ko wane kashi na jimillar kowane yanki na bayanai ke wakilta, ana iya sarrafa komai ta atomatik tare da ƴan tsari masu sauƙi.

A cikin wannan labarin za ku ga, mataki-mataki, Duk hanyoyi masu amfani don aiki tare da kashi a cikin ExcelYadda za a shigar da su daidai, yadda za a nuna sakamakon tare da alamar %, yadda za a ƙididdige yawan adadin, yadda za a gano abin da kashi na kashi na jimlar ke wakilta, yadda za a ƙididdige karuwa da raguwa, rangwame, VAT, harajin samun kudin shiga, har ma yadda za a dawo da jimlar ƙimar lokacin da kawai kun san farashi mai rahusa da adadin da aka yi amfani da shi.

Yadda Excel ke fahimtar kashi da yadda ake tsara su

Kafin ka fara rubuta dabara, yana da mahimmanci don fahimta Yadda Excel ke fassara kaso a cikiDon shirin, kashi ba kome ba ne fiye da lambar decimal: 10% shine 0,1; 25% shine 0,25; 75% shine 0,75 kuma 100% shine 1.

Lokacin da kake amfani da tsarin kashi Lokacin da ka tsara tantanin halitta a matsayin adadi na ƙima, Excel kawai yana nuna wannan adadin da aka ninka da 100 kuma yana ƙara alamar %. Misali, idan kuna da 0,1 a cikin tantanin halitta kuma kuyi amfani da tsarin %, zaku ga 10%; idan kana da 0,754, ya danganta da ƙayyadaddun saitunan ƙima, za ku ga wani abu kamar 75,4% ko 75,40%.

Idan kun riga kuna da lambobi da aka rubuta sannan ku yi amfani da tsarin kashi, Excel ninka waɗannan lambobin da 100Wato, idan tantanin halitta ya ƙunshi 10 kuma kuka tsara shi azaman kashi, sakamakon gani zai zama 1000,00%. Wannan sau da yawa yana kama mutane da yawa a cikin tsaro saboda abin da za a yi don 10% shine a rubuta 0,1 ko don nuna cewa tantanin halitta kashi ne kafin shigar da darajar.

Koyaya, idan ka fara tsara kewayon fanko a matsayin kashi sannan ka rubuta a ciki, Excel Ya bambanta dangane da lambarIdan ka rubuta 10 (daidai ko mafi girma 1), zai nuna 10%; idan ka rubuta 0,1 (kasa da 1), za ka ga 10% kuma saboda yana ninka shi da 100 don nuna shi a matsayin kashi.

Don haka, don yin aiki da kyau tare da kaso, yana da mahimmanci cewa ƙimar da ke ƙasa ta kasance daidai: idan kana so 10%Hanya mafi tsabta ita ce rubuta 0,1 ko 10 a cikin tantanin halitta wanda ya riga ya sami tsarin adadin da aka yi amfani da shi, sannan a bar Excel ya nuna shi daidai.

Yadda ake amfani da tsarin kashi a cikin Excel

Don nuna sakamakonku tare da alamar %, kuna buƙatar yi amfani da tsarin kashi zuwa sel inda kake son nuna waɗannan dabi'u. Yana da sauri sosai don yin wannan daga kintinkiri.

Da farko, zaɓi abin tantanin halitta ko kewayon sel wanda kuke son tsarawa. Sa'an nan, je zuwa shafin "Gida" kuma, a cikin rukunin "Lambar", danna gunkin tare da kashi (%). Nan take, za a nuna ƙimar a matsayin kaso tare da wuraren ƙima waɗanda aka saita ta tsohuwa.

Idan kun fi son daidaita tsarin daki-daki, zaku iya danna-dama akan zaɓin kuma zaɓi "Tsarin salula"A cikin taga da yake buɗewa, zaɓi "Kashi" daga jerin nau'ikan kuma zaɓi wurare nawa nawa kuke son nunawa. Misali, idan ka shigar da 0 a cikin “Decimal places,” zaka ga 10% maimakon 10,00%.

Hakanan zaka iya amfani da gajerun hanyoyi don tafiya da sauri: a yawancin nau'ikan Excel, latsawa Ctrl + Shift + % Nan take yana aiwatar da salon kaso zuwa kewayon da aka zaɓa, ba tare da shiga cikin menus ba.

Ka tuna cewa, kamar yadda muka gani a baya, idan ka yi amfani da tsarin kashi zuwa sel waɗanda suka riga sun ƙunshi lambobi, Excel Ba ya fassara waɗannan lambobin a matsayin kashi-kashi. sihiri; kawai yana ninka su da 100 kuma yana ƙara alamar%, don haka tabbatar da ƙimar farawa ta daidai.

Yi lissafin adadin kashi nawa ke wakilta na jimillar

Ɗaya daga cikin ayyukan yau da kullum shine sani Wane kashi nawa adadin ke wakilta dangane da jimillar?A ilimin lissafi yana da sauƙi kamar rarraba ɓangaren ƙima ta jimillar ƙima kuma, idan kuna so, ninka ta 100 don bayyana sakamakon a matsayin kashi.

Tsarin gabaɗaya zai kasance: kashi = (bangare / jimla)Idan ka tsara wannan rabon a matsayin kashi, ba kwa buƙatar ninka da 100 saboda Excel zai yi maka ta gani. Idan ba za ku tsara shi ba, to yana da kyau a rubuta (part / total) * 100. Idan kuna buƙatar duba ayyukan lissafi a cikin Excel, duba. Ayyukan lissafi a cikin Excel.

Ka yi tunanin ajin firamare da dalibai 31, 17 daga cikinsu mata ne. Babban darajar ita ce 'yan mata (17), kuma jimillar ƙimar duka ɗalibai ne (31). A cikin tantanin halitta, zaku iya rubuta =17/31 kuma, bayan tsara shi azaman kashi, zaku sami kusan 54,8%Ta wannan hanyar za ku iya kwatanta daidai da sauran azuzuwan da ke da ɗalibai ko kaɗan.

Idan kuna son ganin lissafin ba tare da yin amfani da ƙwayoyin tunani ba, kuna iya rubutawa kai tsaye cikin tantanin halitta: = 17 / 31 * 100A wannan yanayin, sakamakon zai zama 54,8 ba tare da alamar% ba, tunda kun yi ninka ta 100.

Wannan hanya iri ɗaya tana da amfani ga tambayoyi kamar "kashi nawa yake wakilta = 7/30"?" (misali, apples da aka ɗauka daga jimillar a cikin kwalba) ko "nawa kashi na ajin ya wuce?". Kawai rubuta = 7/30 a cikin tantanin halitta, yi amfani da tsarin kashi, kuma za ku sami ainihin kashi.

Yi lissafin adadin adadin

Sauran shari'ar asali ita ce ta baya: kuna so ku sani Menene takamaiman kashi na lamba?Misali, wane adadin ne daidai da 12% na 200 ko 40% na 340? Anan aikin yana da sauƙi sosai: kawai ninka jimillar da kaso a sigar decimal.

Tsarin tsari na gama-gari zai zama: darajar = jimla * kashiIdan kuna da jimlar a cikin tantanin halitta ɗaya da kashi a cikin wani, al'adar al'ada ita ce amfani da bayanan tantanin halitta. Misali, idan jimlar tana cikin D2 kuma adadin yana cikin E2, tsarin zai zama = D2 * E2.

Ka yi tunanin kana so ka san 12% na 200. Kuna iya rubutawa a cikin tantanin halitta: =200*12% A madadin, idan kuna da 200 a A1 da 0,12 a B1, rubuta = A1*B1. Ko wanne zaɓi zai ba ku 24 a sakamakon haka. Idan kun canza 200 ko canza kashi, ƙimar za ta ɗaukaka ta atomatik ba tare da sake ƙididdigewa ba.

Wani misali na al'ada: ƙididdige adadin kashi nawa 40% na 340Kuna iya rubuta kai tsaye a cikin tantanin halitta: = 340*40/100, ko rubuta 340 a cikin tantanin halitta ɗaya, 40 a cikin wani, kuma a cikin tantanin halitta na uku saka =(total_cell*percentage_cell)/100. Sakamakon duka biyun shine 136.

A aikace, yana da matuƙar dacewa don rubuta kashi tare da alamar % (misali, 40%) a cikin tantanin halitta da aka tsara azaman kashi sannan kawai a yi. jimlar * percentage_cellTa haka ba sai ka ci gaba da rarrabawa da 100 akai-akai ba.

Nuna kashi kuma daidaita ƙima

Lokacin da kuka canza sashin / jimlar dabara zuwa kashi, Excel yawanci Cire tsoffin wurare na gomaA wasu kalmomi, idan ainihin lissafin shine 0,548, yin amfani da % tsarawa zai iya nuna maka 55% ta hanyar zagaye.

Idan kuna buƙatar ƙarin daidaito, zaku iya zuwa "Format Cells," zaɓi nau'in "Kashi", sannan ku ƙara yawan wuraren ƙima. Ta wannan hanyar, maimakon 55%, zaku ga, misali, 54,8% ko 54,84% dangane da cikakken bayanin da kuke son sakamakon.

Wannan keɓancewa yana da mahimmanci yayin aiki tare da bayanan kuɗi, bayanin kula, ƙididdiga, ko kowane mahallin inda Wuri ɗaya na goma sama ko ƙasa zai iya dacewaBambanci na 0,5% na iya zama kamar maras muhimmanci a cikin jerin ɗalibai, amma yana ƙara har zuwa adadi mai yawa a cikin babban kasafin kuɗi.

Kashi na layuka da yawa da kaso na jimlar

A yawancin lokuta ba kawai kuna son ƙididdige adadin ƙimar ɗaya ba, amma duba kashi nawa ne kowane jere ke wakilta? a cikin tebur data. Hakanan Excel yana magance wannan ba tare da rikitarwa mai yawa ba ta hanyar haɗa SUM da rarrabawa.

Ka yi tunanin tebur daga ƙaramin kanti inda shafi na B ya jera raka'o'in da aka saya na kowane samfur. Da farko, a cikin tantanin halitta a ƙarshen ginshiƙi (misali, B11), kuna lissafin jimillar ta amfani da dabara = SUM(B2:B10). Wannan lambar za ta zama taku duka Siyayya.

Sannan, a cikin wani ginshiƙi (misali, shafi na C), a jere na 2, rubuta dabarar don gano adadin adadin jimlar ya yi daidai da wannan layin: = B2/$11Amfani da cikakkun bayanai (tare da alamun dala) yana hana tantanin halitta da ke ɗauke da jimillar canzawa lokacin da aka kwafi dabarar ƙasa.

Ta hanyar aiwatar da tsarin kashi zuwa shafi na C, zaku sami kashi na kowane samfur. kashi na jimlar sayanTa wannan hanyar za ku iya gani, alal misali, cewa wani abu yana wakiltar 30% na jimlar, wani 5%, da dai sauransu.

Idan samfurin iri ɗaya ya bayyana a cikin layuka da yawa kuma kuna son sanin kashi nawa na jimlar jimillarsu ke wakilta, zaku iya amfani da aikin. KARA IDANMisali, idan ginshikin A ya ƙunshi sunayen samfura kuma shafi na B ya ƙunshi raka'o'in, kuma a cikin tantanin halitta E1 za ku rubuta sunan "Pants", a cikin tantanin halitta E2 za ku iya sanya dabarar = SUMIF(A2: A10, E1, B2: B10) / $ B$11, wanda zai dawo da adadin da duk "Pants" ke wakilta game da jimillar.

Yi lissafin canje-canje da bambance-bambancen kashi

Wani yanayin da ya zama ruwan dare shine na Kwatanta adadi biyu kuma duba nawa kashi nawa suka canza.Misali, tallace-tallace daga wata daya zuwa na gaba, ziyartar gidan yanar gizo a wannan shekara idan aka kwatanta da bara, ko kilos na samfur a hannun jari a cikin kayayyaki daban-daban guda biyu.

Gabaɗayan dabara don canjin kashi shine: (sabon_darajar - old_value) / old_valueIdan ka tsara wannan sakamakon a matsayin kashi, za ka ga karuwa ko raguwa da aka bayyana tare da % alamar.

Misali, tunanin kudin shiga a watan Nuwamba shine €2342 kuma a cikin Disamba €2500. A cikin tantanin halitta, zaku iya rubuta = (2500-2342)/2342. Sakamakon decimal yana kusan 0,06746, wanda, wanda aka tsara azaman kashi, za'a nuna shi azaman 6,75% karuwa (ya danganta da adadin wuraren goma da ka zaɓa).

Idan kudin shiga a watan Janairu shine € 2425 kuma kuna son ganin canjin idan aka kwatanta da Disamba, kawai kuna amfani da dabara = (2425-2500)/2500. Sakamakon shine -0,03 a cikin adadi; idan kun yi amfani da tsarin kashi za ku ga -3,00%, yana nuna a Rage 3%.

A cikin teburin tallace-tallace na wata-wata, abu ne na gama-gari don saka adadin a cikin shafi ɗaya da haɓakar wata-wata a cikin ginshiƙi kusa. Misali, idan tallace-tallace na Janairu yana cikin B2, tallace-tallace na Fabrairu a B3, da sauransu, a cikin C3 zaku iya rubutawa = (B3-B2)/B2 da kwafi dabara zuwa ƙasa don saka idanu akan yawan juyin halitta da kashi.

Ƙara ko rage ƙima da kashi ɗaya

Hakanan Excel na iya taimaka muku yanke shawara Nawa don haɓaka ko rage adadin ta amfani da kashiWannan yana da amfani sosai lokacin da kuka daidaita kasafin kuɗi, farashi, albashi, kashe kuɗi, ko duk wani girman da kuke son gyarawa.

Hankalin yana da sauƙi: idan kuna son ƙara lamba ta X%, kuna ninka ta (1 + X) da (1-X)Idan kuna son rage shi da X%, kuna ninka ta (1 − X). A cikin duka biyun, ana bayyana X azaman ƙima (misali, 0,25 don 25%).

Ka yi tunanin za ku kashe € 113 a mako akan abinci kuma kuna son sanin nawa za ku iya kashewa idan kun ƙara wannan kasafin da kashi 25%. A cikin tantanin halitta kyauta, zaku iya rubuta =113*(1+0,25). Sakamakon shine 141,25 €wanda shine sabon iyakar kashe kuɗin ku na mako-mako. Idan kuna aiki tare da kasafin kuɗi, tuntuɓi kasafin kudi na sirri.

Idan maimakon ƙara shi kuna son yanke wannan kasafin da kashi 25%, tsarin zai zama =113*(1-0,25). Sakamakon shine 84,75 €wanda zai zama sabon adadin da ya kamata ku yi ƙoƙarin kada ku wuce.

Ana iya yin haka a kowane wata tare da wasu kuɗaɗe. Misali, idan kamfani yana da matsakaicin farashin kowane wata na € 1000 kuma ya yanke shawarar ƙara su da 10% don faɗaɗa wasu abubuwa, zai shigar = 1000 * (1 + 0,10) a cikin tantanin halitta; idan yana so ya rage waɗannan kudaden ta 10%, zai yi amfani da = 1000 * (1-0,10), samun sakamakon sakamakon kai tsaye.

Yi ƙididdige rangwame da farashin ƙarshe ta amfani da kaso

Kashi na da mahimmanci idan ya zo Yi lissafin rangwame a cikin ExcelKo kuna siyan wani abu akan siyarwa ko ƙirƙirar jerin farashi don kasuwancin ku, yana da mahimmanci ku san yadda ake saita hanyoyin daidai.

Idan samfurin yana farashin € 50 kuma yana da rangwame 30%, kuna son sanin farashin ƙarshe da zaku biya. Wata bayyananniyar hanya don ganin haka ita ce: final_price = farashi - (farashi × rangwame)A cikin Excel, idan farashin yana cikin A2 kuma adadin ragi yana cikin B2 (kamar 30% ko 0,3), a cikin C2 zaku iya rubuta = A2− (A2 * B2).

Wani daidai, kuma wani lokacin sauri, hanya ita ce ninka kai tsaye ta (1 - rangwame_percentage). Bin misalin jaket ɗin € 50 tare da rangwame 30%, zai isa: = 50* (1-0,30)Sakamakon zai zama € 35, adadin da za ku biya.

Wannan makirci kuma yana aiki don lokuta inda farashin rangwamen shine wanda kuka sani kuma kuna son ganowa. farashin asaliMisali, idan an sayar da rigar akan Yuro 15 kuma kun san cewa farashin ya ragu da kashi 25% na asali, kuna biyan kashi 75% na farashin. A cikin Excel, zaku shigar da = 15/0,75, kuma zaku sami € 20 azaman farashin ba tare da ragi ba.

Lokacin aiki tare da sel, yana da amfani sosai don samun ainihin farashin a cikin shafi ɗaya, rangwame a wani, farashin ƙarshe a wani, kuma, idan kuna so, adadin rangwame a cikin shafi na huɗu. Tare da sauki dabaru kamar A2*B2 da A2−(A2*B2) zaku iya samun duk bayanan da kuke gani.

VAT, harajin shiga na mutum da sauran haraji tare da kaso

A cikin lissafin kuɗi da lissafin kuɗi, ko a gida ko a sana'a, ya zama ruwan dare ƙara ko rage haraji bayyana a matsayin kashi, kamar VAT ko harajin shiga. Excel yana haɓaka wannan aikin sosai, musamman idan kuna da samfura ko layin sabis da yawa.

Don ƙara VAT zuwa farashi mai tushe, zaku iya bin dabaru iri ɗaya ga wanda aka yi amfani da shi don haɓaka kasafin kuɗi. Idan farashin kafin haraji yana cikin A2 kuma ƙimar VAT (misali, 21%) yana cikin B2, a cikin C2 zaku iya ƙididdige adadin VAT ta amfani da ... = A2 * B2, kuma a cikin D2 jimlar tare da haraji tare da = A2+C2.

Wani ƙarin ƙaramin zaɓi shine ƙididdige jimlar kai tsaye ta amfani da dabara =A2*(1+B2)wanda ya kara 21% zuwa 100% na farashin. Don haka, idan tushen haraji ya kasance € 100, amfani = 100 * (1 + 0,21) zai ba ku € 121.

A cikin yanayin harajin kuɗin shiga na mutum ko duk wani ra'ayi da ke buƙatar zama cire daga dukaHankali yana daidaita amma ya ƙunshi ragi. Idan kana da adadin da ke ƙarƙashin harajin riƙewa na kashi 15%, za ka iya samun ƙimar net ta buga = adadin*(1-0,15). Wannan zai ba ku adadin kai tsaye bayan harajin riƙewa.

Idan ka ƙirƙiri tebur daftari tare da ginshiƙai don tushe, VAT, harajin shiga, da jimlar, zaku iya amfani da dabaru kamar (tushe + VAT - harajin shiga na mutum) a cikin jimlar ginshiƙi don nuna a sarari duk ayyukan tarawa da ragi tare da kaso.