Kukura sechikamu - fomula muExcel

Mazana ndeimwe yedzakanyanya kufarirwa nzira dzekushanda neExcel. Kunyanya, vashandisi vazhinji vanoda kudzidza maverengero ekuwedzera mune imwe chiratidzo senge muzana. Saka, zvinobatsira pakuongorora makotesheni emari kana shanduko mumitengo yezvimwe zvinhu zvichienzaniswa nenguva yadarika yekushuma.

Maitiro eKuverengera Kukura Kwekure uye Kukura Mwero muExcel

Kuti uone kukura uye kukura muExcel, unofanira kutanga watsanangura kuti imwe neimwe yeiyi pfungwa chii. Chiyero chekukura chinoreva chiyero pakati pekukosha kwakagadzirwa panguva ino yekuzivisa uye imwechete parameter kune yapfuura. Ichi chiratidzo chinotsanangurwa sechikamu. Kana pasina kukura kana kuenzaniswa neyakapfuura yekuzivisa nguva, ipapo kukosha ndeye 100%.

Kana mwero wekukura uchipfuura 100 muzana, izvi zvinoratidza kuti chimwe chiratidzo chakura pamusoro penguva yekupedzisira yekushuma (kana akati wandei). Kana zvishoma, saka, maererano, yakawa. Maumbirwo echikuru anofanana nemaitiro akajairika ekuwana chikamu, uko mupatsanuri ndiko kukosha kwekuenzaniswa, uye denominator ndiyo chiratidzo chekufananidza nacho.

Zvakare, tsananguro yemazinga ekukura inoitwa nenzira yakati siyanei. Kutanga, chiyero chekukura chinotarirwa, mushure mezvo tinobvisa zana kubva pamutengo unoguma. Chinosara ndicho chikamu chekuwedzera kana kuderera kwechiratidzo chikuru chakaitika. Ndechipi chiratidzo chekushandisa? Izvo zvese zvinoenderana nemhando ipi yekumiririra iri nyore mune imwe mamiriro ezvinhu. Kana zvakakosha kuratidza kuwedzera kwakakwana kana kuderera, ipapo kuwedzera kwekukura kunoshandiswa; kana iri hama, mazinga ekukura anoshandiswa.

Kukura uye kukura mazinga akakamurwa kuva marudzi maviri: cheni uye yakakosha. Yokutanga ndiyo chiyero chemutengo wezvino kune yapfuura. Kukura kwekutanga uye kukura hakutore kukosha kwekare sehwaro hwekuenzanisa, asi imwe mhando yehwaro kukosha. Semuenzaniso, yekutanga munhevedzano.

Chii chinonzi kukosha kwekutanga uye kwekare? Kana isu tichitaura nezve chiratidzo chekutanga, semuenzaniso, iyo Dow Jones index muna Ndira 2020, uye zviyero zvinotorwa muna Ndira 2021, saka tinogona kutaura kuti chiyero chekukura kweiyo index chakanyanya. Zvakare, semuenzaniso wekukura kana kukura kwepasi, unogona kuenzanisa nehukoshi hwekutanga hweindekisi iyi payakatanga kuburitswa. Muenzaniso wekukwira kwekare kana kuwana kuenzanisa kwekukosha kweindekisi iyi muna Zvita zvichienderana nekwaNovember wegore rimwe chetero. Hazvina mhosva kuti rudzii rwekukura, unofanirwa kubvisa zana kuti uwane chiyero chekukura kubva pachiri.

Maitiro ekuverenga maperesenti muExcel

Kuverenga mapeji muExcel kunoitwa pakutanga. Iwe unofanirwa kuisa nhamba dzinodiwa kamwe chete, uye ipapo application ichaita zvese zviito pachayo. Iyo yakajairwa fomula yekuwana mhindu chikamu chenhamba/nhamba*100. Asi kana tikaita kuverenga kuburikidza neExcel, kuwanda kunoitwa otomatiki. Saka chii chatinofanira kuita kuti tione chikamu muExcel?

1. Kutanga tinoda kuseta iyo percentage format. Kuti uite izvi, tinya-kurudyi pane yaunoda sero, uye wobva wasarudza iyo "Format Cells" sarudzo. Kana bhokisi rebhokisi richinge raonekwa, tinofanira kusarudza fomati chaiyo.
2. Zvakare kuseta iyo fomati inogoneka kuburikidza neiyo huru menyu. Iwe unofanirwa kuwana iyo "Kumba" tab, enda kwairi uye uwane "Nhamba" boka rezvishandiso. Pane chikamu chekuisa chimiro chesero. Iwe unofanirwa kudzvanya pamuseve uri padyo nayo uye wosarudza iyo yaunoda kubva pane iyo rondedzero.

Zvino ngatiratidzei kuti izvi zvinoitwa sei mukuita, tichishandisa muenzaniso chaiwo. Ngatitii tine tafura ine makoramu matatu: nhamba yechigadzirwa, kutengeswa kwakarongwa, uye kutengesa chaiko. Basa redu nderokuona dhigirii yekushandiswa kwechirongwa.

Kuti uwane chinangwa, unofanirwa kuita zviito zvakadaro. Isu tichatsanangura musimboti, uye iwe uchafanirwa kupa iwo akakodzera kukosha kwenyaya yako.

1. Isu tinonyora pasi fomu = C2 / B2 muchitokisi D2. Ndiko kuti, isu tinofanirwa kupatsanura kuita chaiko kwebasa munhamba, uye yakarongwa mudhinomineta.
2. Mushure mezvo, tichishandisa nzira yakatsanangurwa kare, tinoshandura chimiro muchikamu.
3. Tevere, isu tinowedzera fomula kune asara maseru tichishandisa autocomplete mubato.

Mushure meizvozvo, zvese zvasara zviito zvichaitwa otomatiki. Iyi ndiyo mukana weExcel uchienzaniswa neiyo nzira yekuverenga maperesenti - iwe unongoda kuisa iyo fomula kamwe chete, uye wobva wagona kuikopa kakawanda sezvaunoda, uye ese maitiro achaverengerwa ega. , uye zvakarurama.

Chikamu chenhamba

Ngatitii tinoziva kuti ndechipi chikamu chinofanira kuva chikamu chenhamba. Uye basa rakaiswa kuti rione kuti chikamu ichi chichava yakawanda sei muchimiro chenhamba. Kuti uite izvi, shandisa fomula = muzana% * nhamba. Ngatitii, maererano nemamiriro edambudziko, zvinotarisirwa kuona kuti ichava yakawanda sei 7% yemakumi manomwe. Kuigadzirisa, unoda:

1. Dzvanya pane chaiyo sero uye isa inotevera fomula ipapo: =7%*70.
2. Dzvanya kiyi Enter uye mhedzisiro ichanyorwa muchitokisi ichi.

Zvinokwanisikawo kunongedzera kwete kune imwe nhamba, asi kune chinongedzo. Kuti uite izvi, zvakakwana kuisa kero yesero rinoenderana muB1 format. Ita shuwa kuti ine manhamba data usati waishandisa mune fomula.

Chikamu chemari

Kazhinji, panguva yekugadzirisa data, mushandisi anopihwa basa rekuona iyo inoguma huwandu hwehukoshi, uyezve kuverenga chikamu chehumwe kukosha kubva pamhedzisiro kukosha. Pane mhinduro mbiri dziripo: mhedzisiro inogona kunyorwa zvichibva pane imwechete sero kana kugoverwa patafura. Ngatipei muenzaniso wekugadzirisa yekutanga vhezheni yedambudziko:

1. Kana tichida kurekodha mhedzisiro yekuverenga chikamu chesero rimwe chairo, tinoda kunyora chirevo chakakwana mudhinomineta. Kuti uite izvi, iwe unofanirwa kuisa imwe dhora chiratidzo ($) pamberi pekero yemutsara uye koramu.
2. Sezvo kukosha kwedu kwekupedzisira kwakanyorwa muchitokisi B10, zvakakosha kugadzirisa kero yayo kuitira kuti kana fomu yacho ichipararira kune mamwe masero, haishanduki. Kuti tiite izvi, tinonyora nzira inotevera: =B2/$B$10.
3. Ipapo iwe unofanirwa kushandura fomati yeese maseru mune ino nhevedzano kusvika muzana. Mushure meizvozvo, uchishandisa iyo autocomplete marker, dhonza formula kune mamwe ese mitsara.

Tinogona kutarisa mhedzisiro. Nekuti referensi yatakashandisa yaive yemhedziso, dhinomineta mufomula haina kushanduka mune mamwe maseru. Kana tisina kuisa chiratidzo chedhora, ipapo kero yaizo "slide" pasi. Saka, mumutsara unotevera, denominator inenge yatova nekero B11, ipapo - B12, zvichingodaro.

Asi chii chaunofanira kuita kana ruzivo rwakakosha rwakagoverwa mutafura yose? Kugadzirisa dambudziko iri rakaoma, unofanira kushandisa basa SUMMESLI. Iyo inotarisa kukosha muhuwandu ichipesana neyakatsanangurwa maitiro, uye kana vakadaro, inopfupisa. Mushure meizvozvo, iwe unofanirwa kuwana chiyero cheiyo inoguma kukosha.

Iyo formula pachayo kazhinji ine inotevera syntax: uXNUMXd SUMIF (matanho emhando; summation renji) / yakazara sum. Mushanduro yeChirungu yepurogiramu, basa iri rinonzi SUMIF. Ngatitsanangure kuti formula iri pamusoro inoshanda sei:

1. Muchiitiko chedu, huwandu hwehuwandu hunoreva mazita ezvigadzirwa. Vari muchikamu chekutanga.
2. Iyo yekuwedzera huwandu ndeye ese ma values ​​ari mu column B. Kureva, kwatiri, iyi ndiyo nhamba yezvigadzirwa zvemusoro wega wega. Aya maitiro anofanira kuwedzera.
3. Criterion. Kwatiri, ndiro zita remuchero.
4. Mhedzisiro yacho yakanyorwa muchitokisi B10.

Kana tikagadzirisa iyo yakajairika fomula kumuenzaniso wedu, inotaridzika seizvi: =СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10. Uye skrini yekujekesa.

Saka iwe unogona kuwana mhedzisiro yekuverenga kune yega yega paramita.

Maitiro eKuverengera Percent Shanduko

Uye zvino ngationei kuti chii chinoda kuitwa kuona kuwedzera kana kuderera mune imwe kukosha kana ichienzaniswa nenguva yakafanana yapfuura. Iyo yakavakirwa-mukati mashandiro eExcel inobvumidza iwe kuti uite maverengero akadaro. Kuti uite izvi, unofanirwa kushandisa fomula, iyo muhuwandu hwemasvomhu fomu (isina kuchinjirwa kuExcel) inoita seizvi: (BA)/A = musiyano. Asi iyo muzana shanduko inoverengerwa sei muExcel?

1. Ngatitii tine tafura umo chikamu chekutanga chine chigadzirwa chatiri kuongorora. Koramu yechipiri neyechitatu inoratidza kukosha kwayo muna Nyamavhuvhu naGunyana, zvichiteerana. Uye muchikamu chechina, tichaverenga kuwedzera kana kuderera sechikamu.
2. Saizvozvo, zvinodikanwa musero rekutanga mushure memusoro mukoramu D kunyora fomula yekuverenga shanduko yezana mumutsara wekutanga. =(C2/B2)/B2.
3. Tevere, shandisa autocomplete kutambanudza fomula kune iyo column yese.

Kana kukosha kwatinoda kuverenga kwakaiswa mukoramu imwe yechimwe chigadzirwa kwenguva yakareba, saka isu tinofanirwa kushandisa nzira yekuverenga yakati siyanei:

1. Chikamu chechipiri chine ruzivo rwekutengesa kumwedzi wega wega.
2. Muchikamu chechitatu, tinoverenga kuchinja kwezana. Formula yatinoshandisa ndeiyi: =(B3-B2)/B2 .
3. Kana iwe uchida kuenzanisa kukosha neicho chakanyatsotsanangurwa chiratidzo chiri mune imwe sero, saka isu tinoita chinongedzo mhedziso. Ngatitii kana tichida kuita kuenzanisa naNdira, saka fomula yedu ichave seizvi. Iwe unogona kuzviona pane iyo skrini.

Ichokwadi chokuti kune kuwedzera, kwete kuwa, tinogona kunzwisisa nekusavapo kwechiratidzo cheminus pamberi penhamba. Nekudaro, maitiro asina kunaka anoratidza kuderera kwezviratidzo zvichienzaniswa nemwedzi wekutanga.

Kuverengera kukosha uye huwandu hwese

Kazhinji, isu tinongoziva huwandu hwenhamba, uye isu tinofanirwa kuona huwandu hwese. Excel inopa nzira mbiri dzekugadzirisa dambudziko iri. Ngatitii une laptop inodhura $950. Zvinoenderana neruzivo rwemutengesi, VAT, inova 11%, inofanirawo kuwedzerwa pamutengo uyu. Kuti uone mhedzisiro yacho, unofanirwa kuita akati wandei ekutanga kuverenga muExcel.

1. Formula yacho yatichashandisa ndeye − Zvose * % = Kukosha.
2. Isa chitubu muchitokisi C2. Mariri tinonyora pasi fomula inoratidzwa muscreenshot.
3. Saka, iyo markup yakakonzerwa nemutero ingave $104,5. Naizvozvo, mutengo wakakwana welaptop uchave $1054.

Ngatishandisei mumwe muenzaniso kuratidza nzira yekuverenga yechipiri. Ngatitii tinotenga $400 laptop uye mutengesi anoti mutengo wacho unotosanganisira 30% discount. Uye isu tinotorwa nekuda kuziva, asi chii chiri mutengo wekutanga? Kuti uite izvi, iwe unofanirwa kutevedzera iyi algorithm:

1. Kutanga, tinosarudza chikamu chinobhadharwa nesu. Muchiitiko chedu i70%.
2. Kuti tiwane mutengo wepakutanga, tinoda kupatsanura chikamu nechikamu. Ndiko kuti, formula ichave seizvi: Chikamu/% = Mari yese
3. Mumuenzaniso wedu, koramu yekutanga ine mutengo welaptop, uye yechipiri koramu ine chikamu chekupedzisira chemutengo wepakutanga watakabhadhara. Saizvozvo, mhedzisiro inonyorwa mumutsara wechitatu, musero rekutanga mushure memusoro watinonyora fomula. =A2/B2 uye shandura iyo sero fomati kusvika muzana.

Saka, mutengo welaptop pasina kuderedzwa waive 571,43 madhora.

Kuchinja kukosha nechikamu

Kazhinji tinofanira kushandura nhamba nechikamu chakati. Kuzviita sei? Basa racho rinogona kuitwa uchishandisa fomula =Mutengo*(1+%). Iwe unofanirwa kuisa hunhu hwakakodzera munzvimbo dzakakodzera, uye chinangwa chinowanikwa.

Percentage Operations muExcel

Sezvineiwo, maperesenti inhamba dzakafanana nechero dzipi, saka unogona kuita ese anobvira arithmetic mashandiro navo, pamwe nekushandisa mafomula. Saka, nhasi takaona maficha ekushanda nemaperesenti muExcel. Kunyanya, isu takanzwisisa nzira yekuverenga kuwedzera kwezana, pamwe nekuwedzera kwenhamba neimwe muzana.