Yadda ake lissafin adadin adadin lambobi a cikin Excel

Excel yana ba ku damar aiwatar da ayyuka iri-iri tare da kashi-kashi: ƙayyade adadin lamba, haɗa su tare, ƙara kaso zuwa lamba, ƙayyade yawan adadin ya karu ko raguwa, sannan kuma aiwatar da wasu ayyuka masu yawa. . Waɗannan basirar suna da matukar buƙata a rayuwa. Dole ne ku yi hulɗa da su akai-akai, saboda duk ragi, lamuni, adibas ana ƙididdige su akan tushen su. Bari mu dubi yadda ake gudanar da ayyuka iri-iri tare da sha'awa, daga mafi sauƙi zuwa mafi rikitarwa.

Menene kashi

Kusan dukkanmu mun fahimci menene sha'awa da yadda ake lissafinta. Bari mu maimaita wannan kayan. Bari mu yi tunanin cewa an kai raka'a 100 na wani samfur zuwa ma'ajiyar. Anan raka'a ɗaya a wannan yanayin yana daidai da kashi ɗaya. Idan aka shigo da kaya 200, to kashi daya zai zama raka’a biyu, da sauransu. Don samun kashi ɗaya, kuna buƙatar raba ainihin adadi da ɗari. Wannan shi ne inda za ku iya fita da shi yanzu.

Lissafin adadin adadin a cikin Excel

Gabaɗaya, misalin da aka kwatanta a sama ya riga ya zama nuni mai haske na samun ƙimar kashi daga babbar ƙima (wato jimlar ƙarami). Don ƙarin fahimtar wannan batu, bari mu ɗauki wani misali.

Za ku nemo yadda ake saurin tantance adadin adadin ƙimar ta amfani da Excel.

A ce tebur yana buɗe akan kwamfutarmu wanda ke ɗauke da manyan bayanai kuma ana rubuta bayanan ƙarshe a cikin tantanin halitta ɗaya. Sabili da haka, muna buƙatar ƙayyade wane nau'i na matsayi ɗaya a kan tushen jimillar ƙimar. A gaskiya ma, duk abin da dole ne a yi daidai da sakin layi na baya, kawai hanyar haɗi a cikin wannan yanayin dole ne a juya zuwa cikakke, ba dangi ba.

Alal misali, idan an nuna dabi'u a shafi na B, kuma sakamakon da aka samu yana cikin cell B10, to, tsarinmu zai yi kama da wannan.

= B2/$10

Bari mu bincika wannan dabarar dalla-dalla. Cell B2 a cikin wannan misalin zai canza lokacin da aka cika ta atomatik. Don haka dole ne adireshinsa ya zama dangi. Amma adireshin cell B10 cikakke ne. Wannan yana nufin cewa duka adreshin jere da adireshin shafi ba sa canzawa lokacin da kake ja dabarar zuwa wasu sel.

Don juya hanyar haɗin zuwa cikakke, dole ne ka danna F4 adadin lokutan da ake buƙata ko sanya alamar dala zuwa hagu na jere da/ko adireshin shafi. 

A cikin yanayinmu, muna buƙatar sanya alamun dala biyu, kamar yadda aka nuna a cikin misalin da ke sama.

Ga hoton sakamakon.

Bari mu dauki misali na biyu. Bari mu yi tunanin muna da irin wannan tebur kamar yadda a cikin misalin da ya gabata, kawai bayanan da aka bazu a kan layuka da yawa. Muna buƙatar ƙayyade adadin adadin adadin kuɗin odar samfur ɗaya.

Hanya mafi kyau don yin wannan ita ce amfani da aikin SUMMESLI. Tare da taimakonsa, yana yiwuwa a tara kawai waɗancan sel waɗanda suka faɗi ƙarƙashin takamaiman yanayin. A cikin misalinmu, wannan shine samfurin da aka bayar. Ana amfani da sakamakon da aka samu don ƙayyade rabon jimlar. 

= SUMIF (kewaye, ma'auni, jimla) / jimlar jimla

Anan, shafi na A yana ƙunshe da sunayen kayayyaki waɗanda tare suka samar da kewayo. Rukunin B yana bayyana bayanai game da kewayon taƙaitawa, wanda shine jimillar adadin kayan da aka kawo. An rubuta yanayin a cikin E1, shine sunan samfurin, wanda shirin ya mayar da hankali kan lokacin ƙayyade kashi.

Gabaɗaya, dabarar za ta yi kama da wannan (idan aka ba da babban jimlar za a bayyana shi a cikin tantanin halitta B10).

Hakanan yana yiwuwa a rubuta sunan kai tsaye a cikin dabarar.

=СУММЕСЛИ(A2:A9;»cherries»;B2:B9)/$B$10

Idan kuna son ƙididdige yawan adadin samfuran daban-daban daga jimlar, to ana yin wannan a cikin matakai biyu:

1. Kowane abu yana haɗuwa da juna.
2. Sa'an nan sakamakon sakamakon ya raba ta jimlar ƙimar.

Don haka, tsarin da ke ƙayyade sakamakon cherries da apples zai kasance kamar haka:

=(СУММЕСЛИ(A2:A9;»cherries»;B2:B9)+СУММЕСЛИ(A2:A9;»apples»;B2:B9))/$B$10

Yadda ake ƙididdige adadin adadin ƙimar tebur na Excel

Bari mu yi irin wannan tebur tare da jerin masu sayarwa da ƙarar da ya gudanar don yin shawarwari. A ƙasan tebur ɗin akwai tantanin halitta na ƙarshe, wanda ke yin rikodin nawa duka suka sami damar siyar da samfuran. Bari mu ce mun yi alƙawarin batutuwa uku, waɗanda adadinsu na jimillar kuɗin da aka samu shine mafi girma, kari. Amma da farko kuna buƙatar fahimtar kashi nawa ne na kudaden shiga gaba ɗaya ya faɗi akan kowane mai siyarwa.

Ƙara ƙarin ginshiƙi zuwa teburin da ke akwai.

A cikin cell C2, rubuta wannan dabara.

= B2/$9

Kamar yadda muka riga muka sani, alamar dala ta sa hanyar haɗi cikakke. Wato baya canzawa ya danganta da inda aka kwafi ko ja da dabara ta amfani da madaidaicin atomatik. Ba tare da yin amfani da cikakken bayani ba, ba zai yiwu a yi dabarar da za ta kwatanta kimar ɗaya da wata ƙayyadaddun ƙima ba, domin idan aka matsa ƙasa, dabarar za ta zama ta atomatik:

=B3/$10

Muna buƙatar tabbatar da cewa adireshin farko yana motsawa, na biyu kuma baya.

Bayan haka, muna ja dabi'u kai tsaye zuwa ragowar sel na ginshiƙi ta amfani da madaidaicin autocomplete.

Bayan amfani da tsarin kashi, muna samun wannan sakamakon.

Yadda ake lissafta kaso na lamba a Excel

Don sanin wane yanki na takamaiman lamba a cikin Excel, yakamata ku raba ƙaramin lamba da babba kuma ku ninka komai da 100.

Sha'awa a cikin Excel yana da nasa tsarin. Babban bambancinsa shine irin wannan tantanin halitta ta atomatik yana ninka ƙimar da aka samu ta 100 kuma yana ƙara alamar kashi. Saboda haka, dabarar samun kashi a cikin Excel ya fi sauƙi: kawai kuna buƙatar raba ƙaramin lamba ta mafi girma. Komai sauran shirin zai lissafta kansa.

Yanzu bari mu kwatanta yadda yake aiki akan misali na gaske.

Bari mu ce kun ƙirƙiri tebur wanda ke nuna takamaiman adadin abubuwan da aka ba da oda da takamaiman adadin samfuran da aka kawo. Don fahimtar adadin da aka ba da oda, wajibi ne (an rubuta dabarar akan gaskiyar cewa jimlar adadin an rubuta shi a cikin tantanin halitta B, kuma kayan da aka kawo suna cikin tantanin halitta C):

1. Raba adadin kayan da aka kawo ta jimillar lamba. Don yin wannan, kawai shigar = C2/B2 zuwa ga dabara bar.
2. Bayan haka, ana kwafi wannan aikin zuwa adadin layuka da ake buƙata ta amfani da alamar cikawa ta atomatik. An sanya wa sel tsarin “Kashi”. Don yin wannan, danna maɓallin da ya dace a cikin rukunin "Gida".
3. Idan lambobi sun yi yawa ko kaɗan bayan maki goma, zaku iya daidaita wannan saitin.

Bayan waɗannan gyare-gyare masu sauƙi, muna samun kashi a cikin tantanin halitta. A cikin yanayinmu, an jera shi a shafi na D.

Ko da an yi amfani da wata dabara ta daban, babu wani abin da ke canzawa a cikin ayyukan.

Ƙila lambar da ake so ba ta kasance a cikin kowane sel ba. Sa'an nan kuma dole ne a shigar da shi a cikin dabarar da hannu. Ya isa kawai rubuta lambar da ta dace a maimakon hujjar da ake buƙata.

= 20/150

Yadda ake ƙididdige yawan ƙimar ƙima daga jimlar tebur

A misalin da ya gabata, akwai jerin sunayen masu sayar da kayayyaki, da kuma adadin kayayyakin da aka sayar, wanda suka yi nasarar kaiwa gare su. Muna buƙatar sanin yadda mahimmancin gudummawar kowane mutum ya kasance ga yawan kuɗin da kamfani ke samu.

Amma bari mu yi tunanin wani yanayi na daban. Muna da jeri inda aka kwatanta dabi'u iri ɗaya a cikin sel daban-daban. Shafi na biyu shine bayani akan adadin tallace-tallace. Muna buƙatar lissafin rabon kowane samfur a cikin jimlar kudaden shiga, wanda aka bayyana azaman kashi.

Bari mu ce muna buƙatar gano kashi nawa ne na jimlar kuɗin shigar da muke samu daga tumatur, wanda aka rarraba a cikin layuka da yawa a cikin kewayon. Jerin ayyuka kamar haka:

1. Ƙayyade samfurin a hannun dama.
   

   

2. Mun sanya shi don bayanin da ke cikin tantanin halitta E2 ya nuna a matsayin kashi. 
3. Aiwatar SUMMESLI don taƙaita tumatir da ƙayyade kashi. 

Tsarin ƙarshe zai kasance mai zuwa.

=СУММЕСЛИ($A$2:$A$8;$E$1;$B$2:$B$8)/B9

Yadda wannan dabara ke aiki

Mun yi amfani da aikin SUMMESLEY, ƙara ƙimar ƙwayoyin sel guda biyu, idan, sakamakon bincikar biyan su da wani yanayi, Excel ya dawo da ƙima. GASKIYA.

Maganar wannan aikin abu ne mai sauqi qwarai. An rubuta kewayon kimanta ma'auni azaman hujja ta farko. An rubuta yanayin a wuri na biyu, kuma iyakar da za a taƙaita shi ne a matsayi na uku. 

Hujja ta zaɓi. Idan ba ku fayyace shi ba, Excel zai yi amfani da na farko a matsayin na uku.

Yadda ake ƙara kashi zuwa lamba a Excel

A wasu yanayi na rayuwa, tsarin kashe kuɗi na yau da kullun na iya canzawa. Mai yiyuwa ne a yi wasu canje-canje. 

Ƙididdigar ƙara ƙayyadaddun kaso zuwa lamba abu ne mai sauƙi.

=daraja*(1+%)

Misali, yayin da kuke hutu, kuna iya son ƙara kasafin kuɗin nishaɗin ku da kashi 20%. A wannan yanayin, wannan dabarar za ta ɗauki nau'i mai zuwa.

=A1*(1-20%)

Bambanci tsakanin lambobi azaman kaso a cikin Excel

Ƙididdigar ƙayyadaddun bambanci tsakanin sel ko lambobi ɗaya a matsayin kaso yana da ma'ana mai zuwa.

(BA)/A

Yin amfani da wannan dabarar a zahiri, kuna buƙatar fahimtar a sarari inda za ku saka wace lamba.

Karamin misali: a ce an kai tuffa guda 80 a dakin ajiyar jiya, yayin da a yau suka kawo guda 100.

Tambaya: Nawa aka kawo yau? Idan ka lissafta bisa ga wannan tsari, karuwar zai zama kashi 25 cikin dari.

Yadda ake nemo kashi tsakanin lambobi biyu daga ginshiƙai biyu a cikin Excel

Don tantance adadin tsakanin lambobi biyu daga ginshiƙai biyu, dole ne ku yi amfani da dabarar da ke sama. Amma saita wasu azaman adiresoshin tantanin halitta.

A ce muna da farashin samfur iri ɗaya. Shafi ɗaya ya ƙunshi mafi girma, kuma shafi na biyu ya ƙunshi ƙarami. Muna buƙatar fahimtar girman girman da darajar ta canza idan aka kwatanta da lokacin da ya gabata.

Tsarin yana kama da wanda aka bayar a cikin misalin da ya gabata, kawai a wuraren da ake buƙata kuna buƙatar saka ba sel waɗanda ke cikin layuka daban-daban ba, amma a cikin ginshiƙai daban-daban.

Yadda dabarar za ta kasance a cikin yanayinmu yana bayyane a fili a cikin hoton allo.

Ya rage don ɗaukar matakai guda biyu masu sauƙi:

1. Saita tsarin kashi.
2. Ja da dabarar zuwa duk sauran sel.

Yadda ake ninka da kashi a cikin Excel

Wasu lokuta kuna iya buƙatar ninka abubuwan da ke cikin sel ta wani kaso a cikin Excel. Don yin wannan, kawai kuna buƙatar shigar da daidaitaccen aikin lissafi a cikin nau'i na lambar salula ko lamba, sannan ku rubuta alama (*), sannan rubuta kashi kuma sanya alamar %.

Hakanan ana iya ƙunsar kashi a cikin wani tantanin halitta. A wannan yanayin, kuna buƙatar saka adireshin tantanin halitta wanda ke ɗauke da kashi a matsayin mai ninka na biyu.

Yadda ake nemo kaso tsakanin lambobi biyu daga layuka 2 a cikin Excel

Tsarin yana kama da wannan, amma maimakon ƙaramin lamba, kuna buƙatar ba da hanyar haɗi zuwa tantanin halitta mai ƙarami, kuma maimakon babban lamba, bi da bi.

Yadda ake lissafin riba ta amfani da Excel

Kafin hada lissafin lamuni, kuna buƙatar la'akari da cewa akwai nau'ikan tarawar su biyu. Na farko shi ake kira annuity. Yana nuna cewa kowane wata adadin ya kasance iri ɗaya ne.

Na biyu ya bambanta, inda ake rage biyan kuɗi kowane wata.

Anan akwai dabara mai sauƙi kan yadda ake ƙididdige biyan kuɗin shekara a cikin Excel.

1. Ƙirƙiri tebur tare da bayanan farko.
2. Ƙirƙiri teburin biyan kuɗi. Ya zuwa yanzu, ba za a sami cikakken bayani a ciki ba.
3. Shigar da dabara = ПЛТ($B$3/12; $B$4; $B$2) zuwa tantanin farko. Anan muna amfani da cikakkun bayanai.
   

   

Tare da bambancin nau'i na biyan kuɗi, bayanin farko ya kasance iri ɗaya. Sannan kuna buƙatar ƙirƙirar lakabin nau'in na biyu.

A cikin wata na farko, ma'auni na bashin zai kasance daidai da adadin lamuni. Na gaba, don lissafta shi, kuna buƙatar amfani da dabarar =ЕСЛИ(D10>$B$4;0;E9-G9), bisa ga farantin mu. 

Don lissafin biyan kuɗin ruwa, kuna buƙatar amfani da wannan dabara: = E9*($B$3/12).

Bayan haka, ana shigar da waɗannan ƙididdiga a cikin ginshiƙan da suka dace, sa'an nan kuma an canja su zuwa dukan tebur ta amfani da alamar autocomplete.