Yadda ake lissafta kaso a cikin Excel - tare da misalan dabara

Wannan rubutun yana ba da cikakkun bayanai game da hanyar lissafin sha'awa a cikin Excel, yana bayyana ainihin da ƙarin ƙididdiga (ƙara ko rage ƙimar ta takamaiman kashi).

Kusan babu wani yanki na rayuwa wanda ba za a buƙaci lissafin sha'awa ba. Yana iya zama tip ga ma'aikaci, kwamiti ga mai siyarwa, harajin shiga ko ribar jinginar gida. Misali, an yi muku rangwamen kashi 25 akan sabuwar kwamfuta? Yaya girman wannan tayin ke da fa'ida? Kuma nawa ne za ku biya, idan kun rage adadin rangwamen.

A yau za ku iya yin ayyuka daban-daban na kashi a cikin Excel cikin inganci.

Ƙididdigar asali don ƙayyade kashi na jimlar ƙimar

Kalmar "kashi" daga asalin Latin. Wannan harshe yana da ginin "kashi dari", wanda ke fassara a matsayin "ɗari". Mutane da yawa daga darussan lissafi za su iya tuna waɗanne dabaru suke wanzuwa.

Kashi kashi-kashi ne na lamba 100. Don samun ta, kuna buƙatar raba lambar A ta lamba B kuma ninka sakamakon da aka samu da 100.

A haƙiƙa, ƙayyadaddun ƙayyadaddun ƙayyadaddun kaso kamar haka:

(Lambar sashe/Lambobi duka)*100.

Bari mu ce kuna da tangerines 20, kuma kuna so ku ba 5 daga cikinsu don Sabuwar Shekara. Nawa ne a cikin kashi? Bayan yin ayyuka masu sauƙi (= 5/20 * 100), muna samun 25%. Wannan ita ce babbar hanyar ƙididdige adadin adadin a rayuwar yau da kullun.

A cikin Excel, ƙayyade kashi ya fi sauƙi saboda yawancin ayyukan da shirin ke yi a baya.

Abin takaici ne, amma babu wata hanya ta musamman wacce ke ba ku damar aiwatar da duk nau'ikan ayyukan da ke akwai. Duk abin yana rinjayar sakamakon da ake buƙata, don nasarar da aka yi lissafin.

Don haka, a nan akwai wasu ayyuka masu sauƙi a cikin Excel, kamar ƙayyadaddun, haɓaka / rage adadin wani abu cikin sharuddan kaso, samun ma'aunin ƙididdiga daidai da kaso.

Babban hanya don ƙayyade kashi a cikin Excel

Part/Total = kashi

Lokacin kwatanta babban tsari da kuma hanyar da za a ƙayyade kashi a cikin maƙunsar bayanai, za ku iya ganin cewa a cikin halin da ake ciki na baya babu buƙatar ninka sakamakon da aka samu da 100. Wannan shi ne saboda Excel yana yin haka da kansa idan kun fara canza nau'in tantanin halitta. zuwa "kashi".

Kuma menene wasu misalai masu amfani na tantance kashi a cikin Excel? A ce kai mai siyar da 'ya'yan itatuwa ne da sauran kayan abinci. Kuna da takaddar da ke nuna adadin abubuwan da abokan ciniki suka yi oda. An ba da wannan jeri a shafi na A, da adadin umarni a shafi na B. Wasu daga cikinsu dole ne a ba da su, kuma an ba da wannan lambar a shafi na C. Saboda haka, shafi na D zai nuna rabon samfuran da aka kawo. Don lissafta shi, kuna buƙatar bin waɗannan matakan:

1. Nuna = C2/B2 a cikin cell D2 kuma matsar da shi ƙasa ta kwafa shi zuwa adadin da ake buƙata na sel.
2. Danna maɓallin "Format Kashi" akan shafin "Gida" a cikin sashin "Lambar".
3. Ka tuna ƙara adadin lambobi bayan maki goma idan ya cancanta.

Shi ke nan.

Idan ka fara amfani da wata hanya ta daban na ƙididdige sha'awa, jerin matakai zasu kasance iri ɗaya.

A wannan yanayin, yawan adadin samfuran da aka kawo ana nunawa a shafi na D. Don yin wannan, cire duk wuraren ƙima. Shirin zai nuna ta atomatik ƙima.

Ana yin haka

Ƙayyade ɗan juzu'in ƙimar lamba

Batun tantance rabon lamba a matsayin kaso da aka kwatanta a sama ya zama ruwan dare gama gari. Bari mu bayyana yanayi da yawa inda za a iya amfani da ilimin da aka samu a aikace.

Case 1: lamba yana a ƙasan tebur a cikin takamaiman tantanin halitta

Mutane sukan sanya ƙimar lamba a ƙarshen takarda a cikin takamaiman tantanin halitta (yawanci ƙasan dama). A wannan yanayin, tsarin zai ɗauki nau'i iri ɗaya da wanda aka ba da shi a baya, amma tare da ɗan ƙaranci, tun da adireshin tantanin halitta a cikin ma'auni cikakke ne (wato yana dauke da dala, kamar yadda aka nuna a hoton da ke ƙasa). .

Alamar dala $ tana ba ku ikon ɗaure hanyar haɗi zuwa takamaiman tantanin halitta. Saboda haka, zai kasance iri ɗaya, kodayake za a kwafi dabarar zuwa wani wuri daban. Don haka, idan an nuna adadin karatu da yawa a cikin shafi na B, kuma an rubuta jimlar ƙimar su a cikin tantanin halitta B10, yana da mahimmanci a ƙayyade adadin ta amfani da dabara: = B2/$10.

Idan kuna son adireshin cell B2 ya canza ya danganta da wurin kwafin, dole ne ku yi amfani da adireshin dangi (ba tare da alamar dala ba).

Idan an rubuta adireshin a cikin tantanin halitta $B$10, a cikin abin da adadin zai kasance iri ɗaya har zuwa jere na 9 na teburin da ke ƙasa.

shawarwarin: Don canza adireshin dangi zuwa cikakken adireshi, dole ne ka shigar da alamar dala a ciki. Hakanan yana yiwuwa a danna hanyar haɗin da ake buƙata a cikin mashaya dabara kuma danna maɓallin F4.

Anan hoton hoton yana nuna sakamakon mu. Anan mun tsara tantanin halitta ta yadda za a nuna ɓangarorin har zuwa ɗari.

Misali 2: An jera sassan gaba ɗaya akan layi daban-daban

Misali, bari mu ce muna da samfurin da ke buƙatar dinki da yawa, kuma muna buƙatar fahimtar yadda shaharar wannan samfurin ya saba da yanayin duk siyayyar da aka yi. Sa'an nan kuma ya kamata ku yi amfani da aikin SUMIF, wanda ke ba da damar fara ƙara duk lambobi waɗanda za a iya danganta su zuwa wani jigon da aka ba su, sa'an nan kuma raba lambobin da ke da alaƙa da wannan samfurin ta hanyar sakamakon da aka samu a cikin tsarin kari.

Don sauƙi, ga dabara:

= SUMIF (ƙimar ƙimar, yanayi, kewayon taƙaitawa)/ jimla.

Tunda ginshiƙin A ya ƙunshi duk sunayen samfuran, kuma shafi na B yana nuna adadin sayayya da aka yi, kuma tantanin halitta E1 ya bayyana sunan samfurin da ake buƙata, kuma jimlar duk umarni shine cell B10, tsarin zai yi kama da haka:

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

Hakanan, mai amfani zai iya rubuta sunan samfurin kai tsaye a cikin yanayin:

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

Idan yana da mahimmanci don ƙayyade wani ɓangare a cikin ƙananan samfurori na samfurori, mai amfani zai iya rubuta jimlar sakamakon da aka samu daga ayyuka na SUMIF da yawa, sa'an nan kuma nuna yawan adadin sayayya a cikin ƙididdiga. Misali, kamar haka:

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

Yadda ake lissafta matakin gyaran ƙima a matsayin kaso a cikin Excel

Akwai hanyoyin lissafi da yawa. Amma, mai yiwuwa, ana amfani da dabarar tantance canjin kashi mafi yawan lokuta. Don fahimtar nawa mai nuna alama ya karu ko raguwa, akwai wata dabara:

Canjin kashi = (BA) / A.

Lokacin yin ƙididdiga na ainihi, yana da mahimmanci don fahimtar ko wane nau'i ne don amfani. Misali, wata daya da ya gabata akwai peach 80, kuma yanzu akwai 100. Wannan yana nuna cewa a halin yanzu kuna da karin peach 20 fiye da da. An samu karuwar kashi 25 cikin dari. Idan kafin wannan akwai 100 peaches, kuma yanzu akwai kawai 80, to, wannan yana nuna raguwar adadin da kashi 20 cikin 20 (tun da kashi 20 na ɗari shine XNUMX%).

Saboda haka, dabara a cikin Excel zai yi kama da haka: (Sabon ƙima – tsohuwar ƙima) / tsohuwar ƙima.

Kuma yanzu kuna buƙatar gano yadda ake amfani da wannan dabarar a rayuwa ta ainihi.

Misali 1: ƙididdige canjin ƙimar tsakanin ginshiƙai

Bari mu ce shafi na B yana nuna farashi na lokacin rahoton ƙarshe, kuma shafi na C yana nuna farashin na yanzu. Sannan shigar da dabara mai zuwa a cikin cell C2 don gano adadin canjin ƙima:

= (C2-B2) / B2

Yana auna girman ƙimar samfuran da aka jera a shafi na A ya karu ko raguwa idan aka kwatanta da watan da ya gabata (shafi B).

Bayan kwafin tantanin halitta zuwa sauran layuka, saita tsarin kashi ta yadda lambobi bayan sifili su nuna daidai. Sakamakon zai zama iri ɗaya kamar a cikin hoton allo.

A cikin wannan misali, ana nuna halaye masu kyau a cikin baƙar fata da kuma mummunan yanayi a cikin ja.

Misali 2: ƙididdige ƙimar canji tsakanin layuka

Idan akwai ginshiƙi ɗaya na lambobi (misali, C mai ɗauke da tallace-tallace na yau da kullun da na mako-mako), zaku iya ƙididdige adadin canjin farashin ta amfani da wannan dabara:

= (S3-S2) / S2.

C2 shine na farko kuma C3 shine tantanin halitta na biyu.

Note. Ya kamata ku tsallake layi na 1 kuma ku rubuta dabarar da ake buƙata a cikin tantanin halitta na biyu. A cikin misalin da aka bayar, wannan shine D3.

Bayan amfani da tsarin kashi zuwa ginshiƙi, za a samar da sakamako mai zuwa.

Idan yana da mahimmanci a gare ku don gano matakin gyare-gyaren ƙima don takamaiman tantanin halitta, kuna buƙatar saita hanyar haɗin yanar gizo ta amfani da cikakkun adiresoshin da ke ɗauke da alamar dala $.

Don haka, dabarar ƙididdige canjin adadin umarni a cikin Fabrairu idan aka kwatanta da watan farko na shekara kamar haka:

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

Lokacin da kuka kwafi tantanin halitta zuwa wasu sel, cikakken adireshin ba ya canzawa muddin dangi ya fara komawa zuwa C4, C5, da sauransu.

Lissafin sha'awa cikin sharuddan ƙididdiga

Kamar yadda kuka riga kuka gani, kowane lissafi a cikin Excel aiki ne mai sauƙi. Sanin kashi, yana da sauƙin fahimtar nawa zai kasance daga duka a cikin sharuddan dijital.

Misali 1: ƙididdige rabon jimlar

Bari mu ce kun sayi kwamfutar tafi-da-gidanka akan $ 950 kuma dole ne ku biya haraji 11% akan siyan. Nawa ne kudin da za a biya a karshen? A wasu kalmomi, nawa ne 11% na $950 zai kasance?

Tsarin shi ne:

lamba * kashi = raba.

Idan muka ɗauka cewa duka yana cikin tantanin halitta A2, kuma adadin yana cikin tantanin halitta B2, an canza shi zuwa sauƙi. = A2*B2  Darajar $104,50 tana bayyana a cikin tantanin halitta.

Ka tuna cewa lokacin da ka rubuta ƙimar da aka nuna tare da alamar kashi (%), Excel yana fassara shi azaman ɗari. Misali, 11% shirin yana karantawa azaman 0.11, kuma Excel yana amfani da wannan adadi a duk lissafin.

A wasu kalmomin, dabara = A2*11% kwatankwacin = A2*0,11. A zahiri, zaku iya amfani da ƙimar 0,11 maimakon kashi kai tsaye a cikin dabara idan hakan ya fi dacewa a lokacin.

Misali 2: gano duka daga juzu'i da kaso

Misali, wani aboki ya ba ka tsohuwar kwamfutarsa ​​akan dala 400, wanda shine kashi 30% na farashin sayan sa, kuma kana bukatar sanin nawa ne kudin sabuwar kwamfutar.

Da farko kuna buƙatar tantance kashi nawa na ainihin farashin kwamfutar tafi-da-gidanka da aka yi amfani da su.

Ya zama cewa farashinsa shine kashi 70 cikin dari. Yanzu kuna buƙatar sanin dabara don ƙididdige ƙimar asali. Wato fahimtar daga wane lamba 70% zai zama 400. Tsarin tsari shine kamar haka:

Raba jimlar / kashi = jimlar ƙima.

Idan aka yi amfani da bayanan gaske, zai iya ɗaukar ɗaya daga cikin waɗannan siffofi: = A2/B2 ko = A2/0.7 ko = A2/70%.

Yadda ake canza lamba zuwa wasu kaso

A ce an fara lokacin biki. A zahiri, kashe kuɗi na yau da kullun zai shafi, kuma kuna iya yin la'akari da wasu hanyoyi don nemo mafi kyawun adadin mako-mako wanda ciyarwar mako-mako zai iya ƙaruwa. Sannan yana da amfani a kara adadin da wani kaso.

Don ƙara adadin kuɗi ta hanyar riba, kuna buƙatar amfani da dabarar:

= darajar * (1+%).

Alal misali, a cikin tsari =A1*(1+20%) darajar tantanin halitta A1 ya karu da kashi biyar.

Don rage lambar, yi amfani da dabarar:

= Ma'ana * (1-%).

Ee, dabarar = A1*(1-20%) yana rage darajar a cikin tantanin halitta A1 da kashi 20%.

A cikin misalin da aka bayyana, idan A2 shine farashin ku na yanzu kuma B2 shine adadin da yakamata ku canza su ta, kuna buƙatar rubuta dabaru a cikin cell C2:

1. Kashi kashi:  =A2*(1+B2).
2. Rage da kashi: =A2*(1-B2).

Yadda ake haɓaka ko rage duk ƙimar gabaɗayan shafi da kashi ɗaya

Yadda za a canza duk dabi'u a cikin shafi zuwa kashi?

Bari mu yi tunanin cewa kuna da ginshiƙi na ƙimar da kuke buƙatar canza zuwa wani sashi, kuma kuna son samun sabbin dabi'u a wuri guda ba tare da ƙara sabon shafi tare da dabara ba. Anan akwai matakai masu sauƙi guda 5 don kammala wannan aikin:

1. Shigar da duk ƙimar da ke buƙatar gyara a cikin takamaiman shafi. Misali, a shafi na B.
2. A cikin tantanin halitta mara komai, rubuta ɗaya daga cikin waɗannan dabaru (ya danganta da aikin):
   1. Ƙara: =1+20%
   2. Rage: = 1-20%.

A zahiri, maimakon "20%" kuna buƙatar ƙayyade ƙimar da ake buƙata.

1. Zaɓi tantanin halitta da aka rubuta dabara (wannan shine C2 a cikin misalin da muke kwatantawa) kuma kwafi ta latsa maɓalli Ctrl + C.
2. Zaɓi saitin sel waɗanda ke buƙatar canzawa, danna-dama akan su kuma zaɓi “Manna Special…” a cikin sigar Turanci ta Excel ko “Manna Special” a cikin .

1. Bayan haka, akwatin maganganu zai bayyana wanda a ciki kuna buƙatar zaɓar madaidaicin "Dabi'u" (darajar), kuma saita aiki azaman "Nuni" (yawan yawa). Next, danna kan "Ok" button.

Kuma a nan ne sakamakon - duk dabi'un da ke cikin shafi B sun karu da 20%.

Daga cikin wasu abubuwa, zaku iya ninka ko raba ginshiƙai tare da ƙima ta wani kaso. Kawai shigar da adadin da ake so a cikin akwatin fanko kuma bi matakan da ke sama.