Maitiro ekuverenga iyo muzana yehuwandu hwenhamba muExcel

Excel inobvumidza iwe kuti uite akasiyana mashandiro nemaperesenti: tarisa huwandu hwenhamba, wedzera pamwe chete, wedzera chikamu kunhamba, tarisa kuti ndeipi muzana iyo nhamba yakawedzera kana kudzikira, uye zvakare ita huwandu hukuru hwemamwe maoperation. . Hunyanzvi uhu hunodiwa zvakanyanya muhupenyu. Iwe unogara uchitarisana navo, nekuti zvese zvaderedzwa, zvikwereti, madhipoziti zvinoverengerwa pahwaro hwavo. Ngatitarisei zvakanyanya maitiro ekuita zvakasiyana-siyana zvekushanda nekufarira, kubva pane zviri nyore kusvika kune zvakanyanya kuoma.

Chii chinonzi percentage

Tinenge tose tinonzwisisa kuti kufarira chii uye kuti tingaiverenga sei. Ngatidzokorore zvinhu izvi. Ngatimbofungidzira kuti 100 mayuniti echimwe chigadzirwa akaunzwa kudura. Pano chikamu chimwe pachiitiko ichi chakaenzana nechikamu chimwe chete kubva muzana. Kana 200 zvikamu zvezvinhu zvakaunzwa kunze kwenyika, ipapo chikamu chimwe chete muzana chaizova mayuniti maviri, zvichingodaro. Kuti uwane chikamu chimwe chete, unoda kugovera nhamba yepakutanga nezana. Apa ndipo paunogona kutiza izvozvi.

Kuverengera muzana yehuwandu muExcel

Kazhinji, muenzaniso watsanangurwa pamusoro apa watova kuratidzwa kwakajeka kwekuwana kukosha kwezana kubva kune yakakura kukosha (kureva, huwandu hwezvidiki). Kuti tinzwisise zviri nani musoro uyu, ngatitore mumwe muenzaniso.

Iwe unozoona maitiro ekukurumidza kuona iyo muzana yehuwandu hwehuwandu hwehuwandu uchishandisa Excel.

Ngatitii tafura yakavhurika pakombuta yedu ine huwandu hukuru hwe data uye ruzivo rwekupedzisira rwakanyorwa mune imwe sero. Saizvozvo, isu tinofanirwa kuona kuti ndechipi chikamu chechinzvimbo chimwe pakatarisana nekumashure kweiyo kukosha kwese. Muchokwadi, zvese zvinofanirwa kuitwa nenzira yakafanana nendima yapfuura, chete chinongedzo munyaya iyi chinofanira kushandurwa kuita mhedziso, kwete hama.

Semuyenzaniso, kana hunhu huchiratidzwa mukoramu B, uye mhedzisiro nhamba iri muchitokisi B10, saka fomula yedu ichaita seizvi.

=B2/$B$10

Ngationgororei fomula iyi zvakadzama. Sero B2 mumuenzaniso uyu rinochinja kana razadzwa otomatiki. Nokudaro, kero yayo inofanira kuva ine hukama. Asi kero yesero B10 ndeye zvachose. Izvi zvinoreva kuti ese kero yemutsara uye kero yekoramu hazvichinji kana iwe uchikwevera fomula kune mamwe maseru.

Kushandura chinongedzo kuita chizere, unofanirwa kudzvanya F4 nhamba inodiwa yenguva kana kuisa chiratidzo chedhora kuruboshwe rwemutsara uye/kana kero yekoramu. 

Muchiitiko chedu, tinoda kuisa zviratidzo zviviri zvemadhora, sezvakaratidzwa mumuenzaniso uri pamusoro apa.

Heino mufananidzo wemhedzisiro.

Ngatitorei muenzaniso wechipiri. Ngatimbofungidzira isu tine tafura yakafanana semumuenzaniso wapfuura, ruzivo chete rwunopararira pamusoro pemitsara yakati wandei. Isu tinofanirwa kuona kuti ndechipi chikamu chemari yese account yeodha yechigadzirwa chimwe.

Nzira yakanakisisa yekuita izvi ndeyokushandisa basa SUMMESLI. Nerubatsiro rwayo, zvinokwanisika kuunganidza chete iwo masero anowira pasi pechimwe chimiro. Mumuenzaniso wedu, ichi ndicho chigadzirwa chakapiwa. Migumisiro yakawanikwa inoshandiswa kuona mugove wehuwandu. 

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

Pano, chikamu A chine mazita ezvigadzirwa izvo pamwe chete zvinoumba huwandu. Column B inotsanangura ruzivo nezve musanganiswa wehuwandu, inova iyo nhamba yese yezvinhu zvakaunzwa. Mamiriro acho akanyorwa muE1, ndiro zita rechigadzirwa, icho chirongwa chinotarisa pakusarudza chikamu.

Muzhinji, iyo fomula ichaita senge iyi (zvichipihwa kuti iyo huru yakazara ichatsanangurwa muchitokisi B10).

Zvinogoneka zvakare kunyora zita racho zvakananga mufomula.

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

Kana iwe uchida kuverenga huwandu hwezvakati wandei zvigadzirwa kubva kune yakazara, zvino izvi zvinoitwa mumatanho maviri:

1. Chinhu chimwe nechimwe chinosanganiswa pamwe chete.
2. Zvadaro chigumisiro chinoguma chinoparadzaniswa nehuwandu hunokosha.

Saka, iyo formula inosarudza mhedzisiro ye cherries nemaapuro ichave seizvi:

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

Maitiro ekuverenga iyo muzana yehuwandu hwehuwandu hweiyo Excel tafura

Ngatiite tafura yakadaro ine runyorwa rwevatengesi uye vhoriyamu yaakakwanisa kutaurirana. Pazasi petafura pane sero yekupedzisira, iyo inonyora kuti yakawanda sei ivo vese pamwe chete vakakwanisa kutengesa zvigadzirwa. Ngatitii takavimbisa misoro mitatu, iyo muzana yehuwandu hwekuchinja ndiyo yepamusoro, bhonasi. Asi chekutanga iwe unofanirwa kunzwisisa kuti ingani muzana yemari iyo yakazara inowira kune mumwe nemumwe mutengesi.

Wedzera imwe column kune tafura iripo.

Musero C2, nyora fomura inotevera.

=B2/$B$9

Sezvatinotoziva, chiratidzo chedhora chinoita kuti chinongedzo ive mhedziso. Ndokunge, hazvichinje zvichienderana nekuti iyo fomula inokopwa kupi kana kudhonzwa uchishandisa autocomplete mubato. Pasina kushandisa chirevo chakakwana, hazvigoneke kugadzira fomula inofananidza kukosha kumwe nehumwe kukosha chaiko, nekuti kana yadzikiswa pasi, iyo fomula inozove iyi:

=B3/$B$10

Tinofanira kuve nechokwadi chekuti kero yekutanga inofamba, uye yechipiri haifambe.

Mushure meizvozvo, isu tinodhonza zvakananga kukosha kune asara maseru ekoramu tichishandisa autocomplete mubato.

Mushure mekushandisa iyo percentage fomati, tinowana iyi mhedzisiro.

Maitiro ekuverenga muzana yenhamba muExcel

Kuti uone kuti ndechipi chikamu cheimwe nhamba muExcel, unofanira kupatsanura nhamba diki nehombe uye wowedzera zvese nezana.

Kufarira muExcel kune maitiro ayo. Musiyano waro mukuru ndewekuti sero rakadaro rinongowanza kukosha kunobuda ne100 uye rinowedzera chiratidzo chezana. Saizvozvo, iyo fomula yekuwana muzana muExcel yakatonyanya kupusa: iwe unongoda kupatsanura diki nhamba nehombe. Zvimwe zvese purogiramu ichazviverengera pachayo.

Zvino ngatitsanangure kuti inoshanda sei pamuenzaniso chaiwo.

Ngatiti iwe wagadzira tafura inoratidza chaiyo nhamba yezvinhu zvakaodha uye imwe nhamba yezvakaunzwa zvigadzirwa. Kuti unzwisise kuti ndeipi muzana yakarairwa, zvinodikanwa (formula yakanyorwa zvichibva pakuti nhamba yese yakanyorwa muchitokisi B, uye zvinhu zvakaunzwa zviri muchitokisi C):

1. Kamura nhamba yezvinhu zvakaunzwa nehuwandu hwehuwandu. Kuti uite izvi, ingopinda = C2 / B2 kune formula bar.
2. Tevere, basa iri rinokopwa kune inodiwa nhamba yemitsara uchishandisa iyo autocomplete marker. Masero anopihwa chimiro che "Percentage". Kuti uite izvi, tinya bhatani rinoenderana muboka re "Home".
3. Kana paine nhamba dzakawandisa kana shoma mushure meiyo decimal, unogona kugadzirisa iyi marongero.

Mushure mekuita izvi zviri nyore, tinowana chikamu muchitokisi. Muchiitiko chedu, yakanyorwa muchikamu D.

Kunyangwe kana imwe fomula yakashandiswa, hapana chinoshanduka muzviito.

Nhamba yaunoda inogona kunge isiri mune chero masero. Ipapo ichafanirwa kuiswa mune fomula manually. Zvakakwana kungonyora nhamba inoenderana panzvimbo yenharo inodiwa.

= 20/150

Maitiro ekuverengera huwandu hwehuwandu hwakawanda kubva pahuwandu hwetafura

Mumuenzaniso wekare, pakanga pane runyoro rwemazita evatengesi, pamwe chete nenhamba yezvigadzirwa zvakatengeswa, izvo vakakwanisa kusvika. Taifanira kuona kuti mupiro wemunhu mumwe nemumwe waive wakakosha zvakadii pamari yese yekambani.

Asi ngatimbofungidzira mamiriro akasiyana. Isu tine runyorwa apo maitiro akafanana anotsanangurwa mumaseru akasiyana. Chikamu chechipiri ndechemashoko ekutengesa mavhoriyamu. Isu tinofanirwa kuverenga chikamu chechigadzirwa chimwe nechimwe mumari yese, inotaridzwa sechikamu.

Ngatitii tinofanirwa kuona kuti ndechipi chikamu chemari yatinowana kubva kumadomasi, ayo anogovaniswa mumitsara yakawanda mumutsara. Kutevedzana kwezviito kunotevera:

1. Taura chigadzirwa chiri kurudyi.
   

   

2. Isu tinozviita kuitira kuti ruzivo rwuri muchitokisi E2 runoratidzwa sechikamu. 
3. Shandisa SUMMESLI kuunganidza matomatisi uye kuona chikamu. 

Iyo yekupedzisira formula ichava inotevera.

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

Kuti fomula iyi inoshanda sei

Takaisa basa racho SUMMESLEY, kuwedzera kukosha kwemasero maviri, kana, semugumisiro wekutarisa kutevedzera kwavo neimwe mamiriro, Excel inodzosera kukosha. TRUE.

Syntax yebasa iri iri nyore kwazvo. Huwandu hwekuongororwa kwemaitiro hunonyorwa senharo yekutanga. Mamiriro acho akanyorwa panzvimbo yechipiri, uye uwandu hunofanira kupfupikiswa huri munzvimbo yechitatu. 

Optional nharo. Kana ukasazvitsanangura, Excel ichashandisa yekutanga seyechitatu.

Maitiro ekuwedzera maperesenti kunhamba muExcel

Mune mamwe mamiriro ehupenyu, chimiro chenguva dzose chemari chinogona kuchinja. Zvinokwanisika kuti dzimwe shanduko dzichafanira kuitwa. 

Nzira yekuwedzera imwe muzana kunhamba iri nyore kwazvo.

=Kukosha*(1+%)

Semuenzaniso, paunenge uri pazororo, ungangoda kuwedzera bhajeti yako yevaraidzo ne20%. Muchiitiko ichi, fomula iyi inotora fomu rinotevera.

=A1*(1-20%)

Musiyano pakati penhamba sechikamu muExcel

Formula yekuona mutsauko pakati pemaseru kana manhamba ega ega sechikamu ine chirevo chinotevera.

(BA)/A

Kushandisa iyi fomula mukuita chaiko, iwe unofanirwa kunzwisisa zvakajeka kupi kwekuisa iyo nhamba.

Muenzaniso mudiki: tomboti wanga uine maapuro makumi masere akaunzwa kuimba yekuchengetera zvinhu nezuro, asi nhasi vauya neanosvika zana.

Mubvunzo: vangani vaunzwa nhasi? Kana iwe ukaverenga maererano nemaitiro aya, kuwedzera kuchava 25 muzana.

Maitiro ekutsvaga muzana pakati penhamba mbiri kubva kumakoramu maviri muExcel

Kuti uone huwandu pakati penhamba mbiri kubva kumakoramu maviri, unofanirwa kushandisa fomula iri pamusoro. Asi isa mamwe sekero dzesero.

Ngatitii tine mitengo yechigadzirwa chimwe chete. Koramu imwe ine hombe, uye yechipiri ine diki. Tinofanira kunzwisisa kuti kukosha kwakachinja sei kana tichienzanisa nenguva yapfuura.

Iyo fomula yakafanana neyakapihwa mumuenzaniso wapfuura, munzvimbo dzinodiwa iwe unofanirwa kuisa kwete maseru ari mumitsara yakasiyana, asi mumakoramu akasiyana.

Kuti iyo fomula ichataridzika sei mune yedu inooneka zvakajeka muiyo skrini.

Zvinosara kutora matanho maviri ari nyore:

1. Seta mafomati ehuwandu.
2. Dhonza fomula kune mamwe maseru ese.

Nzira yekuwanza ne percent mu excel

Dzimwe nguva ungangoda kuwanza zviri mukati memasero neimwe muzana muExcel. Kuti uite izvi, unongoda kuisa chiyero chearithmetic muchimiro chenhamba yenhamba kana nhamba, wobva wanyora asterisk (*), wobva wanyora chikamu uye woisa % chiratidzo.

Iyo percentage inogona zvakare kuve mune imwe sero. Muchiitiko ichi, iwe unofanirwa kutsanangura kero yesero ine muzana seyechipiri yekuwedzera.

Nzira yekuwana sei muzana pakati penhamba mbiri kubva pamitsara miviri muexcel

Iyo formula yakafanana, asi pachinzvimbo chenhamba diki, unofanirwa kupa chinongedzo kune sero rine nhamba diki, uye pachinzvimbo chenhamba hombe, zvichiteerana.

Maitiro ekuverenga chikwereti chechikwereti uchishandisa Excel

Usati wagadzira chikwereti chekuverenga, iwe unofanirwa kufunga kuti kune maviri mafomu ekuwedzera kwavo. Yekutanga inonzi annuity. Zvinoreva kuti mwedzi wega wega mari inoramba yakafanana.

Yechipiri inosiyaniswa, uko kubhadhara kwemwedzi kunoderedzwa.

Heino nzira yakapusa yekuverenga annuity kubhadhara muExcel.

1. Gadzira tafura ine data yekutanga.
2. Gadzira tafura yekubhadhara. Kusvika ikozvino, hapazovi nemashoko chaiwo mairi.
3. Pinda formula =ПЛТ($B$3/12; $B$4; $B$2) kuchitokisi chokutanga. Pano tinoshandisa mareferensi akakwana.
   

   

Nemhando yekubhadhara yakasiyaniswa, ruzivo rwekutanga runoramba rwakafanana. Ipapo iwe unofanirwa kugadzira chinyorwa chemhando yechipiri.

Mumwedzi wekutanga, chiyero chechikwereti chichave chakafanana nehuwandu hwechikwereti. Tevere, kuti uiverenge, unofanirwa kushandisa fomula =ЕСЛИ(D10>$B$4;0;E9-G9), maererano nendiro yedu. 

Kuti uverenge mari yemubereko, unofanirwa kushandisa fomula iyi: =E9*($B $3/12).

Tevere, aya mafomula anopinzwa mumakoramu akakodzera, uye anobva aendeswa kutafura yese uchishandisa autocomplete marker.