Girma a matsayin kashi - dabara a cikin Excel

Kashi na ɗaya daga cikin shahararrun hanyoyin aiki tare da Excel. Musamman, masu amfani da yawa suna so su koyi yadda ake ƙididdige karuwa a cikin wani ƙididdiga a matsayin kashi. Don haka, yana da amfani don nazarin ƙididdiga na kuɗi ko canje-canjen farashin wasu kayayyaki idan aka kwatanta da lokacin rahoton baya.

Yadda ake lissafta Girman Girma da Girman Girma a cikin Excel

Don sanin girman girma da girma a cikin Excel, dole ne ku fara ayyana menene kowane ɗayan waɗannan ra'ayoyin. Matsakaicin girma yana nufin rabo tsakanin ƙimar da aka samar yayin wannan lokacin rahoton da siga guda ɗaya na wanda ya gabata. An bayyana wannan alamar a matsayin kashi. Idan babu girma idan aka kwatanta da lokacin rahoton da ya gabata, to ƙimar shine 100%.

Idan yawan ci gaban ya fi kashi 100, wannan yana nuna cewa wata alama ce ta girma a cikin lokacin rahoton ƙarshe (ko da yawa). Idan ƙasa kaɗan, to, bisa ga haka, ya faɗi. Tsarin gabaɗaya yana kama da daidaitaccen tsari don samun kashi, inda mai rarraba shine ƙimar da za a kwatanta, kuma ma'auni shine alamar da za a kwatanta.

Bi da bi, ma'anar girman girma ana aiwatar da shi ta wata hanya daban-daban. Na farko, ana ƙididdige ƙimar girma, bayan haka mun cire ɗari daga ƙimar da aka samu. Abin da ya rage shine adadin da karuwa ko raguwa a cikin maɓalli ya faru. Wani mai nuna alama don amfani? Duk ya dogara da wane nau'i na wakilci ya fi dacewa a cikin wani yanayi. Idan ya zama dole don nuna cikakkiyar haɓaka ko raguwa, to ana amfani da ƙimar girma; idan dangi, ana amfani da ƙimar girma.

Yawan girma da girma sun kasu kashi biyu: sarka da asali. Na farko shine rabon ƙimar halin yanzu da na baya. Girman tushe da haɓaka baya ɗaukar ƙimar da ta gabata a matsayin tushen kwatanta, amma wani nau'in ƙimar tushe. Misali, na farko a cikin jerin.

Menene ake la'akari da asali da ƙimar da ta gabata? Idan muna magana ne game da alamar farawa, alal misali, jigon Dow Jones a cikin Janairu 2020, kuma ana ɗaukar ma'auni a cikin Janairu 2021, to muna iya cewa ƙimar haɓakar tushe ta yi yawa. Har ila yau, a matsayin misali na ci gaba ko girma, za ku iya kwatanta shi da ƙimar farko ta wannan fihirisar lokacin da aka fara buga ta. Misalin haɓakawa ko riba da ya gabata shine kwatancin ƙimar wannan fihirisar a watan Disamba akan yanayin watan Nuwamba na wannan shekarar. Ko wane nau'in girma ne, kuna buƙatar cire 100 don samun ƙimar girma daga gare ta.

Yadda ake lissafin kashi a cikin Excel

Ana yin lissafin kashi a cikin Excel da farko. Kuna buƙatar shigar da lambobin da ake buƙata sau ɗaya, sannan aikace-aikacen zai yi duk ayyukan da kansa. Madaidaicin dabara don samun riba shine juzu'in lamba/lamba*100. Amma idan muka aiwatar da lissafin ta hanyar Excel, ana aiwatar da ninkawa ta atomatik. Don haka menene muke buƙatar mu yi don tantance kashi a cikin Excel?

1. Da farko muna buƙatar saita tsarin kashi. Don yin wannan, danna-dama akan tantanin da ake so, sannan zaɓi zaɓin “Format Cells”. Lokacin da akwatin maganganu ya bayyana, ya kamata mu zaɓi tsarin da ya dace.
2. Hakanan saita tsarin yana yiwuwa ta babban menu. Kuna buƙatar nemo shafin "Gida", je zuwa gare ta kuma nemo rukunin kayan aikin "Lambar". Akwai filin shigar da tsarin salula. Kuna buƙatar danna kibiya kusa da shi kuma zaɓi wanda kuke buƙata daga lissafin.

Yanzu bari mu nuna yadda ake aiwatar da wannan a aikace, ta amfani da misali na gaske. Bari mu ce muna da tebur wanda ya ƙunshi ginshiƙai uku: lambar samfur, tallace-tallace da aka tsara, da ainihin tallace-tallace. Aikinmu shine sanin matakin aiwatar da shirin.

Don cimma burin, kuna buƙatar yin irin waɗannan ayyuka. Za mu bayyana ƙa'idar, kuma kuna buƙatar samar da ƙimar da ta dace don shari'ar ku.

1. Mun rubuta dabara = C2/B2 a cikin cell D2. Wato, muna buƙatar rarraba ainihin aiwatar da aikin a cikin ƙididdiga, da wanda aka tsara a cikin ƙididdiga.
2. Bayan haka, ta amfani da hanyar da aka bayyana a baya, muna fassara tsarin zuwa kashi.
3. Na gaba, muna mika dabara zuwa sauran sel ta amfani da rikewar atomatik.

Bayan haka, duk sauran ayyukan za a yi ta atomatik. Wannan shine fa'idar Excel idan aka kwatanta da hanyar jagorar ƙididdige kashi - kawai kuna buƙatar shigar da dabara sau ɗaya, sannan zaku iya kwafa shi sau da yawa kamar yadda kuke buƙata, kuma duk ƙimar za a ƙididdige su da kansu. , kuma daidai.

Kashi na lambar

A ce mun san kashi nawa ya kamata ya zama ɓangaren lambar. Kuma an saita aikin don tantance nawa wannan bangare zai kasance a sigar lambobi. Don yin wannan, yi amfani da dabara = kashi% * lamba. A ce, bisa ga yanayin matsalar, ana buƙatar sanin nawa zai zama kashi 7% na saba'in. Don warware shi, kuna buƙatar:

1. Danna kan madaidaicin tantanin halitta kuma shigar da dabarar a can: =7%*70.
2. Danna maɓallin Shigar kuma za a rubuta sakamakon a cikin wannan tantanin halitta.

Hakanan yana yiwuwa a nuna ba takamaiman lamba ba, amma zuwa hanyar haɗi. Don yin wannan, ya isa ya shigar da adireshin tantanin halitta mai dacewa a cikin tsarin B1. Tabbatar ya ƙunshi bayanan lambobi kafin amfani da su a cikin dabara.

Kashi na adadin

Sau da yawa, yayin sarrafa bayanai, ana ba mai amfani alhakin tantance jimlar ƙimar da aka samu, sannan kuma ƙididdige adadin ƙima daga ƙimar da aka samu. Akwai mafita guda biyu da ake samu: ana iya rubuta sakamakon bisa takamaiman tantanin halitta ɗaya ko rarraba a cikin tebur. Bari mu ba da misali na warware sigar farko ta matsalar:

1. Idan muna buƙatar yin rikodin sakamakon ƙididdige adadin takamaiman tantanin halitta ɗaya, muna buƙatar rubuta cikakkiyar magana a cikin ma'auni. Don yin wannan, kuna buƙatar sanya alamar dala ɗaya ($) a gaban adireshin layi da shafi.
2. Tun da an rubuta darajar mu ta ƙarshe a cikin tantanin halitta B10, ya zama dole a gyara adireshinsa don lokacin da tsarin ya yada zuwa wasu sel, ba zai canza ba. Don yin wannan, muna rubuta dabara mai zuwa: = B2/$B$10.
3. Sannan kuna buƙatar canza tsarin duk sel a cikin wannan jerin zuwa kashi-kashi. Bayan haka, ta amfani da alamar autocomplete, ja dabarar zuwa duk sauran layin.

Za mu iya duba sakamakon. Domin bayanin da muka yi amfani da shi cikakke ne, maƙasudin ƙira a cikin dabarar bai canza a cikin sauran sel ba. Idan ba mu sanya alamar dala ba, to adireshin zai "zamewa" ƙasa. Don haka, a cikin layi na gaba, mai ƙima zai riga ya sami adireshin B11, sannan - B12, da sauransu.

Amma menene za a yi idan an rarraba bayanin da ake bukata a cikin tebur? Don magance wannan matsala mai rikitarwa, kuna buƙatar amfani da aikin SUMMESLI. Yana bincika ƙimar da ke cikin kewayon daidai da ƙayyadaddun sharuɗɗan, kuma idan sun yi, ta taƙaita su. Bayan haka, kuna buƙatar samun adadin ƙimar da aka samu.

Daftarin da kanta gabaɗaya yana da ma'ana mai zuwa: uXNUMXd SUMIF (kewayon ma'auni; kewayon taƙaitawa) / jimlar jimla. A cikin harshen Ingilishi na shirin, ana kiran wannan aikin SUMIF. Bari mu bayyana yadda dabarar da ke sama ke aiki:

1. A cikin yanayinmu, kewayon ƙimar yana nufin sunayen samfuran. Suna cikin rukunin farko.
2. Ƙarin kewayon shine duk ƙimar da ke ƙunshe a shafi na B. Wato, a cikin yanayinmu, wannan shine adadin samfuran kowane taken. Dole ne waɗannan ƙimar su ƙara girma.
3. Ma'auni. A cikin yanayinmu, shine sunan 'ya'yan itace.
4. Ana yin rikodin sakamakon a cikin tantanin halitta B10.

Idan muka daidaita maƙasudin gaba ɗaya na sama zuwa misalinmu, zai yi kama da haka: =СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10. Kuma hoton allo don tsabta.

Don haka zaku iya samun sakamakon lissafin don kowane sigogi.

Yadda Ake Kididdige Canjin Kashi

Kuma yanzu bari mu gano abin da ya kamata a yi don tantance karuwa ko raguwa a wani ƙima idan aka kwatanta da lokacin da ya gabata. Ayyukan da aka gina a cikin Excel yana ba ku damar aiwatar da irin waɗannan ƙididdiga. Don yin wannan, kuna buƙatar amfani da dabarar, wanda a cikin tsarin lissafi gabaɗaya (ba a daidaita shi don Excel) yayi kama da wannan: (BA)/A = bambanci. Amma ta yaya ake ƙididdige yawan canjin kashi a cikin Excel?

1. Bari mu ce muna da tebur wanda shafi na farko ya ƙunshi samfurin da muke nazari. ginshiƙai na biyu da na uku suna nuna ƙimarsa na Agusta da Satumba, bi da bi. Kuma a shafi na hudu, zamu lissafta karuwa ko raguwa a matsayin kashi.
2. Saboda haka, ya zama dole a cikin tantanin halitta na farko bayan taken a shafi na D don rubuta dabara don ƙididdige canjin kashi a jere na farko. (C2/B2)/B2.
3. Na gaba, yi amfani da autocomplete don shimfiɗa dabarar zuwa dukan ginshiƙi.

Idan an sanya dabi'un da muke buƙatar ƙididdige su a cikin shafi ɗaya don wani samfurin na dogon lokaci, to muna buƙatar amfani da hanyar ƙididdige ɗan ɗan bambanta:

1. Shafi na biyu ya ƙunshi bayanan tallace-tallace don kowane takamaiman wata.
2. A cikin shafi na uku, muna lissafin canjin kashi. Tsarin da muke amfani da shi shine: = (B3-B2)/B2 .
3. Idan kana so ka kwatanta dabi'u tare da madaidaicin ma'anar alama wanda ke cikin wani tantanin halitta, to, mun sanya hanyar haɗi cikakke. Bari mu ce idan muna buƙatar yin kwatancen da Janairu, to tsarin mu zai kasance kamar haka. Kuna iya gani akan hoton allo.

Gaskiyar cewa akwai karuwa, ba faɗuwa ba, zamu iya fahimta ta rashin alamar ragi a gaban lambar. Bi da bi, munanan dabi'u suna nuna raguwar alamomi idan aka kwatanta da watan tushe.

Lissafin ƙima da jimlar adadin

Sau da yawa, mun san adadin adadin ne kawai, kuma muna buƙatar ƙayyade adadin adadin. Excel yana ba da hanyoyi biyu don magance wannan matsala. A ce kana da kwamfutar tafi-da-gidanka wanda farashinsa ya kai $950. Dangane da bayanin mai siyar, VAT, wanda shine 11%, dole ne kuma a saka shi cikin wannan farashin. Don ƙayyade sakamakon gabaɗaya, kuna buƙatar yin ƙididdiga na farko a cikin Excel.

1. Babban dabarar da za mu yi amfani da ita ita ce - Jimlar * % = Ƙimar.
2. Sanya siginan kwamfuta a cikin cell C2. A ciki mun rubuta dabarar da aka nuna a cikin hoton allo.
3. Don haka, alamar da harajin ya haifar zai zama $ 104,5. Don haka, jimlar kuɗin kwamfutar tafi-da-gidanka zai zama $ 1054.

Bari mu yi amfani da wani misali don nuna hanyar lissafi ta biyu. Bari mu ce mun sayi kwamfutar tafi-da-gidanka $ 400 kuma mai siyar ya ce farashin ya riga ya haɗa da ragi 30%. Kuma sha'awar ta ɗauke mu, amma menene farashin farko? Don yin wannan, kuna buƙatar bin wannan algorithm:

1. Na farko, muna ƙayyade rabon da muke biya. A cikin yanayinmu shine 70%.
2. Don nemo ainihin farashin, muna buƙatar raba rabon da kashi. Wato dabarar za ta kasance kamar haka: Part/% = Jimlar adadin
3. A misalinmu, shafi na farko ya ƙunshi farashin kwamfutar tafi-da-gidanka, kuma shafi na biyu ya ƙunshi kashi na ƙarshe na ainihin farashin da muka biya. Saboda haka, ana rubuta sakamakon ƙarshe a shafi na uku, a cikin tantanin halitta na farko bayan taken da muka rubuta dabarar = A2/B2 kuma canza tsarin tantanin halitta zuwa kashi.

Don haka, farashin kwamfutar tafi-da-gidanka ba tare da rangwame ba shine dala 571,43.

Canza ƙima da kashi ɗaya

Yawancin lokaci dole mu canza lamba ta wani kaso. Yadda za a yi? Za'a iya aiwatar da aikin ta amfani da tsari = Farashin*(1+%). Kuna buƙatar sanya dabi'u masu dacewa a wuraren da suka dace, kuma an cimma burin.

Kashi na Ayyuka a cikin Excel

A haƙiƙa, ƙididdige ƙididdige lambobi ɗaya ne da kowane, saboda haka zaku iya aiwatar da duk ayyukan ƙididdiga tare da su, da kuma amfani da ƙira. Don haka, a yau mun gano fasalulluka na aiki tare da kaso a cikin Excel. Musamman ma, mun fahimci yadda ake lissafin karuwar kashi, da yadda ake kara adadin da wani kaso.