Yadda ake cirewa / ƙara kashi daga lamba a cikin Excel (+ misalai)

Ikon ƙayyade yawan adadin da kuma yin ayyuka daban-daban tare da su yana da mahimmanci a cikin bangarori daban-daban na ayyuka - lissafin kudi, zuba jari, har ma lokacin cin abinci a gidan abinci. Babu wani yanki na rayuwa wanda ba zai zama dole ba daga lokaci zuwa lokaci don ƙayyade ɓangaren gaba ɗaya.

Excel yana da kayan aikin gabaɗaya waɗanda ke ba ku damar aiwatar da ayyuka tare da kaso. Yawancin su ana yin su ta atomatik, kawai shigar da dabarar, kuma za a ƙididdige ƙimar da ake so. Cikin kwanciyar hankali.

Yadda ake aiki tare da kaso a cikin Excel

Kowa yanzu ya san yadda ake tantance kashi. Kuma ko da bai san ta ba, ana iya yin ta ta amfani da na’urar lissafi (ko da yake da wuya a samu irin wannan). A kan wannan na'urar, ana yin ayyuka tare da kaso ta hanyar tambarin% na musamman. 

Tare da Excel, wannan ya fi sauƙi fiye da kan ku. Amma kafin ka zana dabaru da kuma yin wasu ayyuka da su, kana bukatar ka tuna da asali na makaranta.

Kashi ɗari ne na lamba. Don tantance shi, kuna buƙatar raba sashin ta ƙimar lamba kuma ninka sakamakon da 100. 

A ce kai manajan sito ne. An kawo muku raka'a 30 na samfura. A ranar farko, 5 daga cikinsu ne kawai aka gane. To, menene ainihin kashi na samfurin da aka sayar?

Mun fahimci cewa 5 juzu'i ne kuma 30 lamba ce. Na gaba, kawai kuna buƙatar saka lambobi masu dacewa a cikin dabarar da aka bayyana a sama, bayan haka mun sami sakamakon 16,7%.

Ƙara kashi zuwa lamba a daidaitaccen hanya yana da ɗan wahala, tunda ana yin wannan aikin ta matakai da yawa.

Da farko kuna buƙatar ƙayyade 5%, sannan ƙara wannan ƙimar zuwa lambar. Misali, idan kun ƙara 5% zuwa 25, sakamakon ƙarshe zai zama 26,5.

Yanzu, bayan mun san ka'idodin aiki tare da kashi a cikin rayuwa ta ainihi, ba shi da wahala a fahimci yadda yake aiki a Excel.

Ƙididdiga yawan adadin lamba a cikin Excel

Don yin wannan, zaka iya amfani da hanyoyi da yawa.

Bari mu yi tunanin cewa muna da irin wannan tebur. Tantanin halitta na farko a kwance shine jimillar adadin kayayyaki, na biyu kuma, bi da bi, nawa aka sayar. A cikin na uku, za mu yi aikin lissafi.

Yanzu bari mu kalli wannan hoton da kyau. Ba ku ga wani abin mamaki ba? Mashigin dabara yana nuna sassauƙan rarrabuwa na ɓangaren gabaɗaya, ana nuna adadin, amma ba mu ninka sakamakon da 100 ba. Me yasa hakan ke faruwa?

Gaskiyar ita ce, kowane tantanin halitta a cikin Excel na iya samun nasa tsarin. A cikin yanayin C1, ana amfani da kashi. Wato shirin yana ninka sakamakon ta atomatik da 100, kuma ana ƙara alamar % zuwa sakamakon. Idan akwai irin wannan buƙata, mai amfani zai iya ƙayyade wurare nawa ya kamata a nuna a sakamakon sakamakon. 

Yanzu bari mu tantance menene lamba biyar bisa dari na lamba 25. Don yin wannan, dole ne ka fara ninka waɗannan dabi'u, sannan ka raba su da 100. Sakamakon yana bayyane a cikin hoton.

To, ko kuma zaɓi na biyu shine a raba lamba ta ɗari, sannan a ninka ta 5. Sakamakon ba zai canza daga wannan ba. 

Ana iya aiwatar da wannan aikin ta wata hanyar. Don yin wannan, kuna buƙatar nemo alamar% akan madannai (don ƙara shi, kuna buƙatar danna lamba 5 lokaci guda tare da maɓallin Shift).

Kuma yanzu bari mu bincika a aikace yadda za ku yi amfani da ilimin da aka samu.

A ce muna da tebur da ke jera kayayyakin kayayyaki, farashinsu, kuma mun san adadin VAT (a ce kashi 18 ne cikin XNUMX). Saboda haka, a cikin shafi na uku ya zama dole don rikodin adadin haraji.

Bayan an ninka farashin samfur da 18%, kuna buƙatar amfani da alamar cikawa ta atomatik don rubuta wannan dabarar a kowace tantanin halitta na ginshiƙi. Don yin wannan, kuna buƙatar danna akwatin da ke cikin ƙananan kusurwar dama kuma ku ja shi zuwa adadin sel da ake so. 

Bayan mun karbi adadin haraji, ya zama dole don ƙayyade nawa mai amfani zai biya a ƙarshe.

Tsarin shi ne kamar haka:

(B1*100)/18

Bayan mun yi amfani da shi, muna samun irin wannan sakamakon a cikin tebur.

Mun san adadin kayan da aka sayar gaba ɗaya da ɗaiɗaiku. Yanzu muna buƙatar fahimtar kashi nawa na jimlar tallace-tallace na kowane yanki.

Tsarin ba ya canzawa. Kuna buƙatar raba rabo ta ƙimar lamba, kuma ninka sakamakon ta 100. Amma a wannan yanayin, kuna buƙatar sanya hanyar haɗi cikakke. Don yin wannan, gaba da lambar jere da alamar shafi tare da alamar dala $. Za ku sami sakamako mai zuwa.

Ƙara kashi zuwa lamba a cikin Excel

Don yin wannan, kuna buƙatar bi matakai biyu:

1. Ƙayyade yawan adadin lamba. A cikin yanayinmu shine 1,25.
   

   

2. Sakamakon sakamakon yana ƙara zuwa lamba. A cikin misalinmu, sakamakon zai zama 26,5. Wato jerin ayyuka iri ɗaya ne da daidaitattun ƙididdiga, kawai duk lissafin ana yin su a cikin Excel.
   

   

Kuma akan wannan tebur, muna ƙara ƙimar kai tsaye. Kada mu mai da hankali kan aikin tsaka-tsaki.

Da farko, muna da tebur kamar wannan.

Mun san cewa a cikin misalinmu an saita adadin VAT akan kashi 18 cikin ɗari. Don haka, don ƙayyade yawan adadin kayayyaki tare da VAT, dole ne ku fara ƙayyade adadin haraji, sannan ku ƙara zuwa farashin.

Yana da mahimmanci a tuna rubuta baƙaƙe, yayin da suke gaya wa shirin don yin ayyukan lissafi.

Don rage lamba da wani kaso, dabarar kusan iri ɗaya ce, sai dai maimakon ƙarawa, ana yin aikin ragewa.

Yi lissafin Bambancin Kashi a cikin Excel

Bambanci shine ma'auni da ke bayyana matakin canjin farashin, wanda aka bayyana a cikin wani yanki. A cikin yanayinmu, waɗannan su ne kashi-kashi. 

Kada mu fara tunanin Excel, amma la'akari da yanayin gaba ɗaya. A ce tebur farashin 100 rubles a wata daya da suka wuce, kuma yanzu sun biya 150 rubles. 

A wannan yanayin, dole ne a yi amfani da dabarar da ke gaba don tantance gwargwadon yadda aka canza wannan ƙimar.

Bambanci cikin ɗari = (sabon bayanai - tsohuwar bayanai) / tsohuwar bayanai * 100%.

A cikin yanayinmu, farashin ya karu da 50%.

raguwar kashi a cikin excel

Kuma yanzu za mu bayyana yadda ake yin haka a cikin Excel. Anan ga hoton allo don tsabta. Kula da dabara bar.

Yana da mahimmanci a saita tsarin kashi domin a nuna ƙimar daidai.

Idan kana buƙatar ƙididdige yawan adadin da farashin ya canza idan aka kwatanta da wanda aka nuna a layin da ya gabata, kana buƙatar amfani da wannan dabarar (ku kula da hoton hoton).

Gabaɗaya, yana kama da haka: (ƙimar gaba - ƙimar da ta gabata) / ƙimar da ta gabata.

Tun da ƙayyadaddun bayanan bai samar da yuwuwar gabatar da canjin kashi a jere ba, ana iya tsallake shi kawai. 

Wani lokaci yana iya zama dole a yi kwatanta da Janairu. Don yin wannan, kuna buƙatar juya hanyar haɗin gwiwa zuwa cikakkiyar ɗaya, sannan kawai amfani da alamar ta atomatik idan ya cancanta.

Rage kaso daga cikin maƙunsar bayanai na Excel da aka kammala

Amma idan an riga an shigar da bayanin a cikin tebur fa? A wannan yanayin, dole ne ka fara sanya siginan kwamfuta a saman tantanin halitta mara komai kuma ka sanya alamar =. Bayan haka, danna kan tantanin halitta wanda ke dauke da ƙimar da kuke son tantance kashi. Na gaba, danna – (don aiwatar da aikin ragewa) kuma danna kan wannan tantanin halitta). Daga nan sai mu danna alamar tauraro (yana nuna aikin ninkawa a cikin Excel) sannan mu rubuta adadin adadin da ake buƙatar cirewa daga wannan lambar. Bayan haka, kawai rubuta alamar kashi kuma tabbatar da shigar da dabara tare da maɓallin Shigar.

Sakamakon zai bayyana a cikin tantanin halitta ɗaya inda aka rubuta dabarar.

Don kwafa shi zuwa ƙasa da ginshiƙi da yin irin wannan aiki dangane da sauran layuka, dole ne ku yi amfani da alamar cikawa ta atomatik kamar yadda aka bayyana a sama. Wato, ja tantanin halitta a cikin ƙananan kusurwar dama zuwa adadin da ake buƙata na sel zuwa ƙasa. Bayan haka, a cikin kowane tantanin halitta za ku sami sakamakon cire wani kaso daga mafi girma lamba.

Rage sha'awa a cikin tebur tare da ƙayyadaddun kaso

A ce muna da irin wannan tebur.

A ciki, ɗayan sel ɗin ya ƙunshi kaso wanda baya canzawa a duk lissafin da ke cikin dukkan sel na wannan shafi. Tsarin da aka yi amfani da shi a cikin wannan yanayin yana bayyane a hoton da ke sama (kwayoyin G2 kawai sun ƙunshi irin wannan ƙayyadaddun kaso).

Ana iya ƙayyade alamar cikakkar adireshin tantanin halitta ko dai da hannu (ta hanyar shigar da ita kawai kafin adireshin layi ko shafi), ko ta danna kan tantanin halitta kuma danna maɓallin F4. 

Wannan zai gyara hanyar haɗin don kada ta canza lokacin da aka kwafi zuwa wasu sel. Bayan danna maɓallin Shigar, muna samun sakamakon ƙididdigewa da aka gama.

Bayan haka, kamar yadda yake a cikin misalan da ke sama, zaku iya amfani da alamar cikawa ta atomatik don shimfiɗa dabara zuwa duk sel a cikin ginshiƙi.

Ƙirƙiri ginshiƙi na kaso a cikin Excel

A wasu yanayi, ƙila kuna son ƙirƙirar ginshiƙi na kaso. Ana iya yin hakan ta hanyoyi da yawa. Na farko shine ƙirƙirar ginshiƙi wanda zai lissafta yawan adadin da za a yi amfani da shi azaman tushen bayanai. A cikin yanayinmu, wannan kashi ne na duk tallace-tallace.

Bayan haka, jerin ayyuka kamar haka:

1. Zaɓi tebur mai bayani. A cikin yanayinmu, wannan jerin kaso ne.
2. Je zuwa shafin "Saka" - "Tsarin". Za mu ƙirƙiri ginshiƙi na kek, don haka wannan shine nau'in da muka zaɓa.
   

   

3. Na gaba, za a umarce ku don zaɓar kamannin zane na gaba. Bayan mun zaɓi shi, yana bayyana ta atomatik.
   

   

Sa'an nan za ka iya saita shi ta hanyar musamman shafin "Aiki tare da zane-zane" - "Designer". A can za ku iya zaɓar nau'ikan saituna daban-daban:

1. Canza nau'in ginshiƙi. Idan ka danna maɓallin da ya dace, za ka iya saita nau'in ginshiƙi.
   

   

2. Musanya layuka da ginshiƙai. 
3. Canza bayanan da aka yi amfani da su a cikin ginshiƙi. Fasali mai fa'ida sosai idan lissafin kashi yana buƙatar canza shi. Misali, zaku iya kwafin bayanan tallace-tallace daga watan da ya gabata, maye gurbin shi da wani shafi tare da sabbin kaso, sannan canza bayanai don ginshiƙi zuwa na yanzu.
4. Gyara ƙira. 
5. Shirya samfura da shimfidu. 

Zaɓin na ƙarshe yana da ban sha'awa musamman a gare mu, saboda ta hanyarsa ne zaku iya saita tsarin kashi. Kawai a cikin jerin shimfidu waɗanda Excel ya bayar, mun sami zaɓi wanda aka zana gumakan kashi a cikin sassan.

Hakanan zaka iya nuna bayanai a tsarin kashi ta wata hanya. Don yin wannan, danna kan ginshiƙi na yanzu, je zuwa shafin "Layout" kuma nemo zaɓin "Labels Data" a can.

Lissafin ayyuka zai buɗe wanda a ciki kake buƙatar zaɓar wurin sa hannun hannu.

Bayan mun yi haka, hoton kashi zai bayyana akan ginshiƙi.

Idan ka danna ɗaya daga cikin dama-dama, to, ta hanyar menu na "Data Label Format", za ka iya ƙara daidaita lakabin. A cikin yanayinmu, muna sha'awar haɗa hannun jari a cikin sa hannu, saboda dole ne a zaɓi wannan abu don tabbatar da tsarin kashi.

Kuma tsarin kashi kanta an saita shi a cikin menu na "Lambar", wanda za'a iya buɗewa ta hanyar rukunin da ke gefen hagu na akwatin maganganu.

Kamar yadda kake gani, aiki tare da kashi a cikin Excel baya buƙatar ƙwarewa na musamman. Kuna buƙatar kawai koyan ƴan dabaru don yin ko da hadaddun ayyuka tare da sauƙi da ladabi. Tabbas, waɗannan ba duk ayyukan da ake samu ga mai amfani da Excel ba ne, tunda ana iya sarrafa kashi ta wasu hanyoyin, misali, ta hanyar macro. Amma wannan ya riga ya zama babban ci gaba, yana buƙatar sanin ƙarin batutuwa masu rikitarwa. Saboda haka, yana da ma'ana don barin aikin tare da kashi ta hanyar macro na gaba. 

Kashi na ɗari sun dace sosai don amfani da su a cikin ƙididdiga masu yawa, kowannensu yana iya dacewa da bukatun wani mai amfani.