Mukati
- Iyo yakakosha formula yekuona zvikamu zvehuwandu hwehuwandu
- Nzira huru yekuona maperesenti muExcel
- Kusarudza chidimbu chehuwandu hwehuwandu
- Maitiro ekuverenga dhigirii rekugadzirisa kukosha senge muzana muExcel
- Kuverengera kufarira muhuwandu hwematemu
- Nzira yekushandura nhamba kune imwe muzana
- Maitiro ekuwedzera kana kuderedza kukosha kwese kwekoramu neperesenti
Ichi chinyorwa chinopa ruzivo rwakadzama nezve nzira yekuverengera kufarira muExcel, inotsanangura iwo makuru uye ekuwedzera mafomula (kuwedzera kana kuderedza kukosha neyakasiyana muzana).
Iko kunenge kusina nzvimbo yehupenyu umo kuverenga kwekufarira kwaisazodiwa. Inogona kunge iri zano kune mumiriri, komisheni kumutengesi, mutero wemari kana mubereko wemondi. Semuenzaniso, wakapihwa 25 muzana kuderedzwa pakombiyuta itsva? Kupiwa uku kunobatsira kusvika papi? Uye imarii yauchafanira kubhadhara, kana iwe ukabvisa huwandu hwekuderedzwa.
Nhasi uchakwanisa kuita akasiyana siyana mashandiro muExcel zvakanyanya.
Iyo yakakosha formula yekuona zvikamu zvehuwandu hwehuwandu
Izwi rekuti "muzana" rinobva muchiLatin. Mutauro uyu une chivakwa "percentum", iyo inoshandura se "zana". Vanhu vazhinji kubva muzvidzidzo zvemasvomhu vanogona kurangarira kuti ndeapi mafomula aripo.
A percentage chidimbu chenhamba 100. Kuti uiwane, unofanira kupatsanura nhamba A nenhamba B uye wowedzera nhamba inobuda ne100.
Chaizvoizvo, iyo yekutanga fomula yekutarisa maperesenti ndeiyi inotevera:
(Part number/Nhamba yese)*100.
Ngatitii une tangerines makumi maviri, uye iwe unoda kupa mashanu acho eGore Idzva. Imarii pachiyero? Mushure mekuita mabasa akareruka (=20/5*5), tinowana 20%. Iyi ndiyo nzira huru yekuverenga chikamu chenhamba muhupenyu hwemazuva ose.
MuExcel, kuona maperesenti kwakatonyanya nyore nekuti rakawanda rebasa rinoitwa nechirongwa kumashure.
Zvinosiririsa, asi hapana yakasarudzika nzira inobvumidza iwe kuita ese aripo marudzi emashandiro. Zvose zvinokonzerwa nemigumisiro inodiwa, nokuda kwekubudirira iyo maverengero anoitwa.
Naizvozvo, heano mamwe maoperation ari nyore muExcel, akadai sekutarisa, kuwedzera / kuderedza huwandu hwechimwe chinhu muhuwandu hwematemu, kuwana huwandu hwakaenzana nezana muzana.
Nzira huru yekuona maperesenti muExcel
Chikamu/Zvose = muzana
Kana uchienzanisa chimiro chikuru uye nzira yekugadzirisa zvikamu mumaspredishiti, unogona kuona kuti mumamiriro ekupedzisira hapana chikonzero chekuwedzera kukosha kunoguma ne 100. Izvi ndezvokuti Excel inoita izvi pachayo kana iwe ukatanga kuchinja sero rudzi. kusvika ku "percentage".
Uye ndeipi mimwe mienzaniso inoshanda yekuona iyo muzana muExcel? Ngatitii uri mutengesi wemichero nezvimwe zvekudya. Une gwaro rinoratidza huwandu hwezvinhu zvakarairwa nevatengi. Rondedzero iyi inopiwa mukoramu A, uye nhamba yemirairo mukoramu B. Mamwe acho anofanira kuendeswa, uye nhamba iyi inopiwa muchikamu C. Naizvozvo, koramu D icharatidza chikamu chezvigadzirwa zvinounzwa. Kuti uiverenge, unofanirwa kutevera matanho aya:
- Ratidza = C2 / B2 muchitokisi D2 uye woifambisa pasi nekuikopa kune nhamba inodiwa yemasero.
- Dzvanya bhatani re "Percentage Format" pane "Kumba" tebhu muchikamu che "Nhamba".
- Rangarira kuwedzera nhamba yemanhamba mushure meiyo decimal point kana zvichidikanwa.
Ndizvo zvose.
Kana iwe ukatanga kushandisa nzira yakasiyana yekuverenga kufarira, kutevedzana kwematanho kuchava kwakafanana.
Muchiitiko ichi, iyo yakatenderedzwa muzana yezvigadzirwa zvinounzwa inoratidzwa mukoramu D. Kuti uite izvi, bvisa nzvimbo dzese dzegumi. Iyo purogiramu icharatidza otomatiki kukosha kwakatenderedzwa.
Zvinoitwa nenzira iyi
Kusarudza chidimbu chehuwandu hwehuwandu
Nyaya yekusarudza mugove wenhamba sechikamu chatsanangurwa pamusoro apa yakajairika. Ngatitsanangure huwandu hwemamiriro ezvinhu apo ruzivo rwakawanikwa runogona kushandiswa mukuita.
Nyaya yekutanga: nhamba iri pazasi petafura mune imwe sero
Vanhu vanowanzoisa kukosha kwakazara kumagumo kwegwaro mune imwe sero (kazhinji pasi kurudyi). Mumamiriro ezvinhu aya, fomura inotora fomu imwechete seyakambopiwa kare, asi neine nuance shoma, sezvo kero yesero mune denominator iri mhedziso (kureva kuti, ine dhora, sezvakaratidzwa mumufananidzo uri pasi apa) .
Chiratidzo chedhora $ chinokupa kugona kusunga chinongedzo kune imwe sero. Naizvozvo, icharamba yakafanana, kunyangwe iyo fomula ichakopwa kune imwe nzvimbo. Saka, kana kuverenga kwakati kunoratidzwa mumutsara B, uye kukosha kwavo kwese kwakanyorwa muchitokisi B10, zvakakosha kuona chikamu uchishandisa fomula: =B2/$B$10.
Kana iwe uchida kuti kero yesero B2 ichinje zvichienderana nenzvimbo yekopi, unofanira kushandisa imwe kero (isina dhora chiratidzo).
Kana kero yakanyorwa muchitokisi $B10, muchiitiko icho denominator ichave yakafanana kusvika kumutsara 9 wetafura iri pazasi.
Nyaya: Kuti uchinje kero yehama kuita kero yakakwana, unofanirwa kuisa chiratidzo chemadhora mairi. Izvo zvakare zvinogoneka kudzvanya pane inodiwa chinongedzo mune fomula bar uye tinya F4 bhatani.
Heino skrini inoratidza mhedzisiro yedu. Pano takagadzira sero kuitira kuti zvikamu zvinosvika pazana zvionekwe.
Muenzaniso 2: zvikamu zvezvose zvakanyorwa pamitsetse yakasiyana
Semuenzaniso, ngatiti isu tine chigadzirwa chinoda matinji akawanda, uye isu tinofanirwa kunzwisisa kuti chigadzirwa ichi chakakurumbira sei pakatarisana nekumashure kwezvose zvakatengwa. Ipapo iwe unofanirwa kushandisa iyo SUMIF basa, izvo zvinoita kuti zvikwanise kutanga wawedzera nhamba dzese dzinogona kuverengerwa kune wakapihwa musoro, uye wogovanisa nhamba dzine chekuita nechigadzirwa ichi nemhedzisiro inowanikwa mukuita kwekuwedzera.
Kuti zvive nyore, heino fomula:
=SUMIF(value range, condition, summation range)/sum.
Sezvo koramu A ine ese mazita echigadzirwa, uye koramu B inoratidza kuti mangani akatengwa, uye sero E1 rinotsanangura zita rechigadzirwa chinodiwa, uye huwandu hwemaodha ese sero B10, fomula ichaita seizvi:
=SUMIF(A2:A9 ,E1, B2:B9) / $B$10.
Zvakare, mushandisi anogona kunyora zita rechigadzirwa zvakananga mumamiriro ezvinhu:
=SUMIF(A2:A9, «cherries», B2:B9) / $B$10.
Kana zvakakosha kuona chikamu muchikamu chidiki chezvigadzirwa, mushandisi anogona kunyora huwandu hwemibairo inowanikwa kubva kune akati wandei mabasa eSUMIF, uye obva aratidza huwandu hwese hwekutenga mudhinomineta. Semuenzaniso, seizvi:
=(SUMIF(A2:A9, «cherries», B2:B9) + SUMIF(A2:A9, «maapuro», B2:B9)) / $B$10.
Maitiro ekuverenga dhigirii rekugadzirisa kukosha senge muzana muExcel
Kune nzira dzakawanda dzekuverenga. Asi, zvichida, nzira yekusarudza shanduko yezana inoshandiswa kazhinji. Kuti unzwisise kuti yakawanda sei chiratidzo chakawedzera kana kuderera, pane formula:
Muzana shanduko = (BA) / A.
Paunenge uchiita masvomhu chaiwo, zvakakosha kuti unzwisise kuti ndeupi shanduko yekushandisa. Semuyenzaniso, mwedzi wapfuura kwaive nemapichisi makumi masere, uye iko zvino kwave nezana. Wedzero yacho yaiva 80 muzana. Kana zvisati zvaitika pane mapeaches zana, uye ikozvino pane makumi masere chete, zvino izvi zvinoratidza kuderera kwenhamba ne100 muzana (sezvo zvidimbu makumi maviri kubva muzana ndeye 20%).
Naizvozvo, iyo fomula muExcel ichaita seizvi: (Kukosha kutsva - kukosha kwekare) / kukosha kwekare.
Uye zvino iwe unofanirwa kufunga kuti ungashandisa sei iyi fomula muhupenyu chaihwo.
Muenzaniso 1: kuverenga shanduko mukukosha pakati pemakoramu
Ngatitii koramu B inoratidza mitengo yenguva yekupedzisira yekubika, uye koramu C inoratidza mitengo yeizvino. Wobva waisa iyo inotevera fomula muchitokisi C2 kuti uwane chiyero chekuchinja kukosha:
= (C2-B2) / B2
Inoyera iyo kukosha kwezvigadzirwa zvakanyorwa mukoromo A yakawedzera kana kuderera kana ichienzaniswa nemwedzi wapfuura (column B).
Mushure mekukopa sero kumitsetse yasara, isa iyo percentage fomati kuitira kuti manhamba mushure me zero aratidzwe zvakakodzera. Mhedzisiro ichave yakafanana neiyo skrini.
Mumuenzaniso uyu, maitiro akanaka anoratidzwa mumatema uye maitiro asina kunaka mutsvuku.
Muenzaniso 2: kuverenga chiyero chekuchinja pakati pemitsara
Kana paine koramu imwe chete yenhamba (semuenzaniso, C ine kutengeswa kwemazuva ese uye kwevhiki), iwe unozogona kuverenga iyo muzana shanduko mumutengo uchishandisa iyi formula:
= (S3-S2) / S2.
C2 ndiyo yekutanga uye C3 ndiyo yechipiri sero.
chitsamba. Iwe unofanirwa kusvetuka mutsara wekutanga uye kunyora fomu inodiwa musero rechipiri. Mumuenzaniso wakapihwa, iyi iD1.
Mushure mekushandisa iyo percentage fomati ku column, zvinotevera zvinozogadzirwa.
Kana zvakakosha kuti iwe uwane dhigirii rekushandurwa kwemutengo kune imwe sero, unofanirwa kuseta chinongedzo uchishandisa kero dzakakwana dzine chiratidzo chedhora $.
Saka, fomula yekuverenga shanduko yehuwandu hwemirairo muna Kukadzi zvichienzaniswa nemwedzi wekutanga wegore ndeiyi inotevera:
=(C3-$C$2)/$C$2.
Paunokopa sero kune mamwe maseru, iyo kero yakakwana haichinji chero bedzi hama yacho inotanga kureva C4, C5, nezvimwe.
Kuverengera kufarira muhuwandu hwematemu
Sezvawatoona, chero kuverenga muExcel ibasa riri nyore. Kuziva iyo muzana, zviri nyore kunzwisisa kuti ichange yakawanda sei kubva kune yese mumashoko edhijitari.
Ngatiti iwe unotenga laptop ye $ 950 uye iwe unofanirwa kubhadhara 11% mutero pakutenga. Imarii ichafanira kubhadharwa pakupedzisira? Mune mamwe mazwi, ingave yakawanda sei 11% ye950 $?
Fomura yacho ndeiyi:
Integer * muzana = share.
Kana tikafungidzira kuti yese iri muchitokisi A2, uye chikamu chiri muchitokisi B2, inoshandurwa kuita nyore. =A2*B2 Iko kukosha kwe $ 104,50 inoonekwa muchitokisi.
Rangarira kuti paunonyora kukosha kunoratidzwa nechiratidzo chezana (%), Excel inodudzira sezana. Semuenzaniso, 11% inoverengwa nechirongwa se 0.11, uye Excel inoshandisa iyi nhamba mune zvese kuverenga.
Nemamwe mashoko, formula =A2*11% yakafanana =A2*0,11. Sezvingatarisirwa, unogona kushandisa kukosha 0,11 pachinzvimbo chezana muzana mufomula kana zviri nyore panguva iyoyo.
Muenzaniso 2: kuwana yese kubva pachidimbu uye muzana
Semuenzaniso, shamwari yakakupa komputa yake yekare yemadhora mazana mana, inova 400% yemutengo wayo wekutenga, uye iwe unofanirwa kuziva kuti yakawanda sei komputa itsva.
Kutanga iwe unofanirwa kuona kuti ingani muzana yemutengo wepakutanga iyo yakashandiswa laptop inodhura.
Zvinoratidza kuti mutengo wayo 70 muzana. Zvino iwe unofanirwa kuziva fomula yekuverenga mutengo wekutanga. Ndiko kuti, kunzwisisa kubva nhamba 70% ichava 400. Mushonga unotevera:
Govera yehuwandu / muzana = kukosha kwese.
Kana ikaiswa kune chaiyo data, inogona kutora imwe yeanotevera mafomu: =A2/B2 kana =A2/0.7 kana =A2/70%.
Nzira yekushandura nhamba kune imwe muzana
Ngatitii nguva yezororo yatanga. Sezvinongoita, mashandisiro ezuva nezuva anokanganiswa, uye iwe ungangoda kufunga nezvedzimwe nzira dzekuwana iyo yakakwana pasvondo mari iyo kushandiswa kwevhiki kunogona kuwedzera. Zvadaro zvinobatsira kuwedzera nhamba neimwe muzana.
Kuti uwedzere huwandu hwemari nemubereko, unofanirwa kushandisa iyo formula:
= kukosha * (1+%).
Somuenzaniso, mune formula =A1*(1+20%) kukosha kwesero A1 kunowedzerwa necheshanu.
Kuti uderedze nhamba, shandisa fomula:
= Zvinoreva * (1–%).
Hongu, formula = A1*(1-20%) inoderedza kukosha muchitokisi A1 ne20%.
Mumuenzaniso wakatsanangurwa, kana A2 iri mari yako yazvino uye B2 iri muzana yaunofanirwa kuvashandura nayo, unofanirwa kunyora mafomula muchitokisi C2:
- Kuwedzera muzana: =A2*(1+B2).
- Deredza nechikamu: =A2*(1-B2).
Maitiro ekuwedzera kana kuderedza kukosha kwese kwekoramu neperesenti
Nzira yekushandura sei kukosha kwese mukoramu kusvika muzana?
Ngatimbofungidzira kuti une koramu yezvakakosha iwe yaunofanirwa kushandura kune chimwe chikamu, uye iwe unoda kuve neakagadziridzwa hunhu munzvimbo imwechete pasina kuwedzera koramu nyowani ine fomula. Heano 5 matanho ari nyore kupedzisa basa iri:
- Pinda zvese zvakakosha zvinoda kururamiswa mune chaiyo column. Somuenzaniso, muchikamu B.
- Musero risina chinhu, nyora imwe yeanotevera mafomula (zvichienderana nebasa racho):
- Wedzera: =1+20%
- Deredza: =1-20%.
Sezvingatarisirwa, pachinzvimbo che "20%" unofanirwa kutsanangura kukosha kunodiwa.
- Sarudza sero yakanyorwa fomura (iyi C2 mumuenzaniso watiri kutsanangura) uye kopira nekudzvanya kiyi musanganiswa Ctrl + C.
- Sarudza seti yemasero anoda kuchinjwa, tinya-kurudyi paari uye sarudza "Namira Special ..." mushanduro yeChirungu yeExcel kana "Paste Special" mu.
- Zvadaro, bhokisi rebhokisi richaonekwa raunoda kusarudza "Values" parameter (values), uye isa kushanda se "Kuwedzera" (wedzera). Zvadaro, tinya bhatani "OK".
Uye heino mhedzisiro - ese kukosha muchikamu B akawedzera ne20%.
Pakati pezvimwe zvinhu, iwe unogona kuwanda kana kupatsanura makoramu ane kukosha neimwe muzana. Ingoisa iyo yaunoda muzana mubhokisi risina chinhu uye tevera matanho ari pamusoro.