Momwe mungawerengere kuchuluka kwa manambala mu Excel

Excel imakupatsani mwayi wochita ntchito zosiyanasiyana ndi maperesenti: dziwani kuchuluka kwa manambala, onjezani palimodzi, onjezani kuchuluka kwa manambala, dziwani kuti kuchuluka kwachulukira kapena kuchepa, komanso kuchita ntchito zina zambiri. . Maluso amenewa ndi ofunika kwambiri m'moyo. Muyenera kuthana nawo nthawi zonse, chifukwa kuchotsera zonse, ngongole, madipoziti zimawerengedwa pamaziko awo. Tiyeni tiwone mwatsatanetsatane momwe tingachitire zinthu zosiyanasiyana ndi chidwi, kuyambira zosavuta mpaka zovuta kwambiri.

Peresenti ndi chiyani

Pafupifupi tonsefe timamvetsetsa kuti chidwi ndi chiyani komanso momwe tingachiwerengere. Tiyeni tibwereze zinthu izi. Tiyerekeze kuti mayunitsi 100 a chinthu china adaperekedwa kumalo osungiramo zinthu. Apa unit imodzi pankhaniyi ndi yofanana ndi gawo limodzi pa zana. Ngati ma unit 200 a katundu adatumizidwa kunja, ndiye kuti gawo limodzi pa zana likanakhala mayunitsi awiri, ndi zina zotero. Kuti mupeze peresenti imodzi, muyenera kugawa chiwerengero choyambirira ndi zana. Apa ndi pamene mungathe kuchoka nazo tsopano.

Kuwerengera kuchuluka kwa ndalama mu Excel

Kawirikawiri, chitsanzo chomwe chafotokozedwa pamwambapa ndi chisonyezero chowonekera bwino cha kupeza mtengo wamtengo wapatali kuchokera ku mtengo wokulirapo (ndiko kuti, chiwerengero cha zing'onozing'ono). Kuti timvetse bwino mutuwu, tiyeni titenge chitsanzo china.

Mupeza momwe mungadziwire mwachangu kuchuluka kwazinthu zomwe zimagwiritsidwa ntchito ku Excel.

Tiyerekeze kuti tebulo latsegulidwa pa kompyuta yathu lomwe lili ndi deta yambiri ndipo chidziwitso chomaliza chimalembedwa mu selo imodzi. Choncho, tiyenera kudziwa kuti ndi gawo lotani la malo amodzi motsutsana ndi maziko a mtengo wonsewo. Ndipotu, zonse ziyenera kuchitidwa mofanana ndi ndime yapitayi, chiyanjano chokha pa nkhaniyi chiyenera kusinthidwa kukhala mtheradi, osati wachibale.

Mwachitsanzo, ngati zikhalidwe zikuwonetsedwa mugawo B, ndipo chiwerengerocho chili mu selo B10, ndiye kuti ndondomeko yathu idzawoneka chonchi.

=B2/$B$10

Tiyeni tiwunike ndondomekoyi mwatsatanetsatane. Cell B2 muchitsanzo ichi isintha ikangodzaza zokha. Chifukwa chake, adilesi yake iyenera kukhala yachibale. Koma adilesi ya cell B10 ndi yokwanira. Izi zikutanthauza kuti adiresi ya mzere ndi adilesi ya mzati sizisintha mukakokera fomula kumaselo ena.

Kuti musinthe ulalo kukhala wokhazikika, muyenera kukanikiza F4 nambala yofunikira kapena kuyika chizindikiro cha dollar kumanzere kwa mzere ndi/kapena adilesi. 

Kwa ife, tiyenera kuyika zizindikiro ziwiri za dollar, monga momwe tawonetsera mu chitsanzo pamwambapa.

Pano pali chithunzi cha zotsatira.

Tiyeni titenge chitsanzo chachiwiri. Tiyerekeze kuti tili ndi tebulo lofanana ndi lachitsanzo lapitalo, chidziwitso chokhacho chimafalikira pamizere ingapo. Tiyenera kudziwa kuchuluka kwa ndalama zonse zomwe zimatengera maoda a chinthu chimodzi.

Njira yabwino yochitira izi ndikugwiritsa ntchito SUMMESLI. Ndi chithandizo chake, zimakhala zotheka kuwerengera ma cell okhawo omwe amagwera pamtundu wina. Mu chitsanzo chathu, ichi ndi choperekedwa. Zotsatira zomwe zapezedwa zimagwiritsidwa ntchito pozindikira gawo lachiwonkhetso. 

=SUMIF(range, criteria, sum_range)/total sum

Apa, gawo A lili ndi mayina azinthu zomwe zimapanga mitundu yosiyanasiyana. Mzere B umafotokoza zambiri za kuchuluka kwa zinthu zomwe zaperekedwa. Chikhalidwecho chalembedwa mu E1, ndilo dzina la mankhwala, lomwe pulogalamuyo imayang'ana kwambiri pozindikira kuchuluka kwake.

Mwambiri, chilinganizocho chidzawoneka chonchi (kutengera kuti chiwonjezero chonse chidzafotokozedwa mu selo B10).

N'zothekanso kulemba dzina mwachindunji mu chilinganizo.

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

Ngati mukufuna kuwerengera kuchuluka kwazinthu zingapo kuchokera pazokwanira, izi zimachitika m'magawo awiri:

1. Chinthu chilichonse chikuphatikizidwa ndi mzake.
2. Ndiye zotsatira zake zimagawidwa ndi chiwerengero chonse.

Chifukwa chake, njira yomwe imatsimikizira zotsatira za yamatcheri ndi maapulo idzakhala motere:

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

Momwe mungawerengere kuchuluka kwamitengo ya tebulo la Excel

Tiyeni tipange tebulo lotere ndi mndandanda wa ogulitsa ndi voliyumu yomwe adakwanitsa kukambirana. Pansi pa tebulo pali selo lomaliza, lomwe limalemba kuti onse pamodzi adatha kugulitsa zinthu. Tiyerekeze kuti tinalonjeza mitu itatu, yomwe kuchuluka kwake kwa ndalama zonse ndipamwamba kwambiri, bonasi. Koma choyamba muyenera kumvetsetsa kuti ndi zingati peresenti ya ndalama zonse zomwe zimagwera pa wogulitsa aliyense.

Onjezani ndime yowonjezera ku tebulo lomwe lilipo.

Mu selo C2, lembani ndondomekoyi.

=B2/$B$9

Monga tikudziwira kale, chizindikiro cha dola chimapangitsa ulalo kukhala wotheratu. Ndiye kuti, sizisintha kutengera komwe fomula imakopera kapena kukokera pogwiritsa ntchito chogwirira cha autocomplete. Popanda kugwiritsa ntchito chidziwitso chonse, sizingatheke kupanga chilinganizo chomwe chidzafanizire mtengo umodzi ndi mtengo wina, chifukwa chikasinthidwa, chilinganizocho chidzakhala chonchi:

=B3/$B$10

Tiyenera kuwonetsetsa kuti adilesi yoyamba isuntha, ndipo yachiwiri satero.

Pambuyo pake, timakokera mwachindunji ma cell otsala agawolo pogwiritsa ntchito chogwirira cha autocomplete.

Titagwiritsa ntchito mawonekedwe aperesenti, timapeza izi.

Momwe mungawerengere kuchuluka kwa nambala mu Excel

Kuti mudziwe gawo la nambala inayake mu Excel, muyenera kugawa nambala yaying'ono ndi yayikulu ndikuchulukitsa chilichonse ndi 100.

Chidwi mu Excel chili ndi mawonekedwe ake. Kusiyana kwake kwakukulu ndikuti selo yotere imangochulukitsa mtengo wake ndi 100 ndikuwonjezera chizindikiro cha peresenti. Chifukwa chake, njira yopezera kuchuluka mu Excel ndiyosavuta: mumangofunika kugawa nambala yaying'ono ndi yayikulu. Zina zonse pulogalamuyi idzawerengera yokha.

Tsopano tiyeni tifotokoze momwe zimagwirira ntchito pa chitsanzo chenicheni.

Tiyerekeze kuti mwapanga tebulo lomwe likuwonetsa kuchuluka kwazinthu zomwe zidalamulidwa komanso kuchuluka kwazinthu zoperekedwa. Kuti mumvetsetse kuchuluka komwe adayitanitsa, ndikofunikira (chiwerengerocho chimalembedwa potengera kuti nambala yonse yalembedwa mu selo B, ndipo katundu woperekedwa ali mu selo C):

1. Gawani chiwerengero cha katundu woperekedwa ndi chiwerengero chonse. Kuti muchite izi, ingolowetsani = C2 / B2 ku formula bar.
2. Kenako, ntchitoyi imakopera ku nambala yofunikira ya mizere pogwiritsa ntchito chikhomo cha autocomplete. Maselo amapatsidwa mtundu wa "Percentage". Kuti muchite izi, dinani batani lolingana mu gulu la "Home".
3. Ngati pali manambala ochulukirapo kapena ochepa pambuyo pa decimal point, mutha kusintha izi.

Pambuyo pakusintha kosavuta uku, timapeza peresenti mu selo. Kwa ife, zalembedwa mu ndime D.

Ngakhale atagwiritsidwa ntchito mosiyanasiyana, palibe chomwe chimasintha zochitazo.

Nambala yofunidwayo mwina isakhale m'maselo aliwonse. Ndiye izo ziyenera analowetsedwa mu chilinganizo pamanja. Ndikokwanira kungolemba nambala yofananira m'malo mwa mkangano wofunikira.

= 20/150

Momwe mungawerengere kuchuluka kwa zinthu zambiri kuchokera patebulo

Mu chitsanzo chapitachi, panali mndandanda wa mayina a ogulitsa, komanso chiwerengero cha mankhwala ogulitsidwa, omwe adatha kufika. Tinkafunika kudziwa kuti munthu aliyense wathandizira bwanji pazachuma chonse cha kampani.

Koma tiyeni tiyerekeze mkhalidwe wosiyana. Tili ndi mndandanda womwe zikhalidwe zomwezo zimafotokozedwa m'maselo osiyanasiyana. Danga lachiwiri ndi chidziwitso cha kuchuluka kwa malonda. Tiyenera kuwerengera gawo la chinthu chilichonse muzopeza zonse, zowonetsedwa ngati peresenti.

Tiyerekeze kuti tikuyenera kudziwa kuchuluka kwa ndalama zomwe timapeza zimachokera ku tomato, zomwe zimagawidwa m'mizere ingapo mosiyanasiyana. Kutsatira kwa zochita ndi motere:

1. Tchulani chinthu kumanja.
   

   

2. Timapanga kuti chidziwitso mu selo E2 chiwonetsedwe ngati peresenti. 
3. Ikani SUMMESLI kuwerengera tomato ndikuwona kuchuluka kwake. 

Njira yomaliza idzakhala zotsatirazi.

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

Momwe fomula iyi imagwirira ntchito

Tagwiritsa ntchito ntchitoyi SUMMESLEY, kuwonjezera ma cell awiri, ngati, chifukwa choyang'ana kutsata kwawo ndi chikhalidwe china, Excel imabweza mtengo. WOONA.

Kalembedwe ka ntchitoyi ndi kosavuta. Kuwunika kosiyanasiyana kumalembedwa ngati mtsutso woyamba. Mkhalidwewo umalembedwa m'malo achiwiri, ndipo mndandanda womwe uyenera kufotokozedwa mwachidule uli pamalo achitatu. 

Mtsutso wosankha. Ngati simunatchule, Excel idzagwiritsa ntchito yoyamba ngati yachitatu.

Momwe mungawonjezere maperesenti ku nambala mu Excel

M'mikhalidwe ina ya moyo, momwe ndalama zimakhalira zimasintha. N’zotheka kuti pafunika kusintha zinthu zina. 

Njira yowonjezerera kuchuluka kwa chiwerengero ndi yosavuta.

=Mtengo*(1+%)

Mwachitsanzo, mukakhala patchuthi, mungafune kuwonjezera bajeti yanu yosangalatsa ndi 20%. Pankhaniyi, ndondomekoyi itenga mawonekedwe otsatirawa.

=A1*(1-20%)

Kusiyana pakati pa manambala monga peresenti mu Excel

Njira yodziwira kusiyana pakati pa ma cell kapena manambala amodzi monga peresenti ili ndi mawu awa.

(BA)/A

Pogwiritsa ntchito ndondomekoyi muzochitika zenizeni, muyenera kumvetsetsa bwino momwe mungayikitsire nambala.

Chitsanzo chaching'ono: tiyerekeze kuti munabweretsa maapulo 80 kumalo osungiramo katundu dzulo, pomwe lero abweretsa ochuluka ngati 100.

Funso: Ndi angati omwe abweretsedwa lero? Ngati muwerengera molingana ndi chilinganizochi, chiwonjezeko chidzakhala 25 peresenti.

Momwe mungapezere kuchuluka pakati pa manambala awiri kuchokera pamizere iwiri mu Excel

Kuti mudziwe kuchuluka pakati pa manambala awiri kuchokera pamizati iwiri, muyenera kugwiritsa ntchito njira yomwe ili pamwambapa. Koma ikani ena ngati ma adilesi amafoni.

Tiyerekeze kuti tili ndi mitengo ya chinthu chomwecho. Ndime imodzi ili ndi yaikulu, ndipo yachiwiri ili ndi yaing’ono. Tiyenera kumvetsetsa momwe mtengo wasinthira poyerekeza ndi nthawi yapitayi.

Njirayi ndi yofanana ndi yomwe yaperekedwa mu chitsanzo chapitachi, m'malo ofunikira muyenera kuyika osati ma cell omwe ali m'mizere yosiyana, koma m'mizere yosiyana.

Momwe fomula idzawonekere kwa ife ikuwoneka bwino pazithunzi.

Kungotsala njira ziwiri zosavuta:

1. Khazikitsani mtundu wamaperesenti.
2. Kokani chilinganizo kumaselo ena onse.

Momwe mungachulukitsire peresenti mu Excel

Nthawi zina mungafunike kuchulukitsa zomwe zili m'maselo ndi peresenti inayake mu Excel. Kuti muchite izi, muyenera kungolowetsa masamu amtundu wamtundu wa nambala kapena nambala, kenako lembani nyenyezi (*), kenako lembani kuchuluka ndikuyika chizindikiro %.

Peresenti imathanso kukhala mu selo lina. Pankhaniyi, muyenera kufotokoza adiresi ya selo yomwe ili ndi peresenti monga chochulukitsa chachiwiri.

Momwe mungapezere kuchuluka pakati pa manambala awiri kuchokera pamizere iwiri mu Excel

Njirayi ndi yofanana, koma m'malo mwa nambala yaying'ono, muyenera kupereka ulalo ku cell yomwe ili ndi nambala yaying'ono, ndipo m'malo mwa nambala yayikulu, motsatana.

Momwe mungawerengere chiwongola dzanja pogwiritsa ntchito Excel

Musanaphatikize chowerengera cha ngongole, muyenera kuganizira kuti pali mitundu iwiri ya kuchuluka kwawo. Yoyamba imatchedwa annuity. Zikutanthauza kuti mwezi uliwonse ndalamazo zimakhala zofanana.

Yachiwiri imasiyanitsidwa, kumene malipiro a mwezi uliwonse amachepetsedwa.

Nayi njira yosavuta yowerengera ndalama za annuity mu Excel.

1. Pangani tebulo lokhala ndi deta yoyambira.
2. Pangani tebulo lolipira. Mpaka pano, sipadzakhala chidziwitso chenichenicho.
3. Lowetsani chilinganizo =ПЛТ($B$3/12; $B$4; $B$2) ku selo loyamba. Apa timagwiritsa ntchito maumboni athunthu.
   

   

Ndi malipiro osiyanitsidwa, chidziwitso choyambirira chimakhalabe chofanana. Ndiye muyenera kupanga chizindikiro cha mtundu wachiwiri.

M’mwezi woyamba, ngongoleyo idzakhala yofanana ndi kuchuluka kwa ngongoleyo. Kenako, kuti muwerenge, muyenera kugwiritsa ntchito fomula =ЕСЛИ(D10>$B$4;0;E9-G9), malinga ndi mbale yathu. 

Kuti muwerengere chiwongola dzanja chanu, muyenera kugwiritsa ntchito njira iyi: =E9*($B$3/12).

Kenako, mafomuwa amalowetsedwa m'mizati yoyenera, kenako amasamutsidwa patebulo lonse pogwiritsa ntchito cholembera cha autocomplete.