Excel Zvigadziro Zvokufambisa Mafomu

Kuwedzera kuumbwa kwemamiriro ezvinhu muExcel kunokubvumira kushandisa maitiro akasiyana-siyana ekufambisa kune imwe sero kana uwandu hwemasero anosangana nemamiriro ezvinhu aunoisa.

Maikirwo ekugadzirisa anoshandiswa achishandiswa apo masero anosarudzwa anogadzirisa izvi zvakagadziriswa.

Mafaira ekugadzirisa anokwanisa kushandiswa anosanganisira fonti uye ruvara rwemuvara mavara, maitiro epafaira, masero emasero, uye kuwedzera nhamba yekufambisa kune data.

Kubvira paExcel 2007, Excel yakave nenzvimbo dzakawanda dzakagadzirirwa mukati mezviitiko zvinowanzoshandiswa zvakadai sekuwana nhamba dzakawanda kudarika kana dzisingasviki kune chimwe chinhu chakanaka kana kuwana nhamba dziri pamusoro kana pasi pemutengo wepakati .

Mukuwedzera kune izvi zvisati zvagadziriswa, zvinogonawo kugadzira mitemo yemitemo yakagadziridzwa kushandiswa uchishandisa Excel maforomo kuti uongorore zvinoshandiswa nevashandisi-zvataurwa.

Kushandisa Mitemo Yakasiyana-siyana

Zvinopfuura mutemo mumwechete zvinogona kushandiswa kune imwechete data kuti uongorore mamiriro akasiyana. Semuenzaniso, bhadha yemari inogona kunge yakagadziriswa iyo inoshandiswa pakuchinja maitiro apo mamwe mazinga - akadai se50%, 75%, uye 100% - ehurumende yose inoshandiswa.

Mumamiriro ezvinhu akadaro, Excel inotanga kusarudza kana mitemo yakasiyana-siyana inopesana, uye, kana zvakadaro, purogiramu inotevera urongwa hwakarongwa hwekutangira kuziva kuti ndeupi mutemo wekugadzirisa maitiro unoshandiswa kune data.

Muenzaniso: Kuwana Dhiyabhorosi inodarika 25% uye 50% Inowedzera neMafungiro Anokodzera

Mumuenzaniso unotevera, mitemo miviri yakagadziridzwa yekugadzirisa maitiro ichashandiswa kumakero emasero B2 kusvika B5.

• mutemo wekutanga unotarisa kuona kana data iri mumasero A2 kusvika kune A5 yakakura kudarika zvakakodzana neB2 kusvika B5 nepamusoro pe25%.
• mutemo wechipiri unotarisa kuona kana imwe chete yepaA2: A5 inodarika kukoshesa kwakakosha muB2: B5 neinopfuura 50%.

Sezvo zvinogona kuonekwa mumufananidzo wepamusoro, kana imwe yemamiriro ezvinhu ari pamusoro apa ichokwadi, ruvara rwemukati rwekamuri kana masero ari muhupamhi B1: B4 ichachinja.

• nokuda kwedata iyo kusiyana kwacho kunopfuura 25%, ruvara rwemukati rwemasero ruchachinja kuva bhuruu.
• kana musiyano uri mukuru kupfuura 50% ruvara rwemukati chemukati ruchachinja kusvika rutsvuku.

Mitemo inoshandiswa kuita basa iri,

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

richasvika uchishandisa maitiro ekugadzirisa New Formatting Rule dialog box .

Kupinda Tutorial Data

1. Isa data mumasero A1 kusvika kuC5 sezvinoonekwa mumufananidzo uri pamusoro

Cherechedza: Chikamu chechitatu chekudzidzisa chichawedzera mafomu kumasero C2: C4 anoratidza chaiyo yepamusiyano yakasiyana pakati pemaonero mumasero A2: A5 uye B2: B5 kuitira kuti uone kutora kwemirairo yemitemo yakagadziriswa.

Kugadzirisa Mitemo yekufambisa mitemo

Sezvambotaurwa, mitemo yekugadzirisa maitiro iyo inotarisa zvinhu zviviri izvi ichapindwa uchishandiswa nenzira yakagadziridzwa kuiswa kweNew Formatting Rule dialog box .

Kugadzirisa mafungiro akasiyana-siyana kuwana hukuru hwe25%

1. Simbisai masero B2 kuB5 mubasa rekushanda.
2. Dzvanya paImba yebhu yebhoni.
3. Dzvanya pamufananidzo weChimiro Chekugadzirisa muRibhoni kuti uzarure mhando yekunyorera.
4. Sarudza Rute Rutsva kuti uzarure Rudzi Rutsva Rute Rule dialog box sezvinoonekwa mumufananidzo uri pamusoro.
5. Muchikamu chepamusoro chebhokisi rebhokisi, chengetedza pane sarudzo yekupedzisira: Shandisa fomu kuti uone masero kuti aite.
6. Muchikamu chepakati chebhokisi rebhokisi, chengetedza maBhizimisi ekufambisa apo chirevo ichi chiri chechokwadi: mutsara.
7. Tora fomu : = (A2-B2) / A2> 25% munzvimbo yakapiwa
8. Dzvanya paBhidhiyo yeBuritsa kuti uzarure maBiriti eCells mazita ebhokisi.
9. Mune iri bhokisi rebhokisi, chengeta paDzadza tab uye sarudza bhuruu yezai yebhuruu.
10. Dzvanya OK kaviri kuti uvhare mabhokisi ebhokisi uye udzoke kushizha.
11. Panguva ino, ruvara rwemashure emasero B3 neB5 rinofanira kuva bhuruu.

Kugadzirisa mafungiro emhando kuti uwane hukuru hunopfuura 50%

1. Ne masero B2 kuB5 achiri kusarudzwa, dzokorora matanho 1 kusvika ku6 pamusoro.
2. Tora fomu: = (A2-B2) / A2> 50% munzvimbo yakapiwa.
3. Dzvanya paBhidhiyo yeBuritsa kuti uzarure maBiriti eCells mazita ebhokisi.
4. Dzvanya paDzadza tab uye sarudza mutsvuku wezadza.
5. Dzvanya OK kaviri kuti uvhare mabhokisi ebhokisi uye udzoke kushizha .
6. Mucherechedzo wemusero wesero B3 inofanirwa kunge iine ruvara rwebhuruu inoratidza kuti asiyamu inoparadzanisa pakati pe nhamba mumasero A3 neB3 inopfuura 25% asi inopfuura kana yakaenzana ne50%.
7. Mucherechedzo wemusero wesero B5 inofanira kuchinja kusvika kuratidza kuratidza kuti chikamu chinoparadzanisa pakati pehamba mumasero A5 uye B5 inopfuura 50%.

Kuchechera Maitiro Ekugadzirisa Mitemo

Kuverenga% Kusiyana

Kuti uone kuti mitemo yakagadziridzwa yakagadziridzwa yakarurama, tinogona kuisa mazamu mumasero C2: C5 iyo ichaverenga chaiyo yepakati kusiyana pakati pezviyero mumakero A2: A5 uye B2: B5.

1. Dzvanya pachitokisi C2 kuti uite sero rinoshanda.
2. Tora mu formula = (A2-B2) / A2 uye shandisai Enter kiyi pane keyboard.
3. Mhinduro 10% inofanira kuonekwa muchitokisi C2, ichiratidza kuti nhamba iri muchitokisi A2 ndeye 10% yakakura kudarika nhamba muchitokisi B2.
4. Zvinogona kuve kudii kuti uchinje mafomiti pane imwe sero C2 kuitira kuti uratidze mhinduro yacho sezana.
5. Shandisa gadziriro yekuzadza kuti ukope iyo yakabva pane imwe sero C2 kusvika kumasero C3 kusvika C5.
6. Mhinduro dzemasero C3 kusvika kuC5 dzinofanira kuva: 30%, 25%, uye 60%.
7. Mhinduro dziri mumasero aya dzinoratidza kuti mitezo yemitemo yakagadzirwa yakagadzirwa yakarurama sezvo musiyano uri pakati pemasero A3 neB3 mukuru kupfuura 25% uye musiyano pakati pemasero A5 neB5 mukuru kupfuura 50%.
8. Siri B4 haina kuchinja marara nokuti kusiyana pakati pemasero A4 neB4 akaenzana ne25%, uye mutemo wedu wekugadzirisa mafungiro wakataura kuti chikamu chakakura kupfuura 25% chaidiwa kuti ruvara rwemukati ruchinje ruvara rwebhuruu.

Mutemo weKutanga kweMitemo yeMitemo yeMitemo

Kushandisa Mitemo Yakagadzikana Inogadzirisa Mitemo

Apo mitemo yakawanda inoshandiswa kune imwe nhamba ye data, Excel inotanga kusarudza kana mitemo inopesana.

Mitemo inokakavadzana ndeye iyo iyo sarudzo dzakasarudzwa dzokugadzira mitemo imwe neimwe hazvigoni kushandiswa kune imwechete data .

Mumuenzaniso unoshandiswa mumudzidzi uyu, mitemo inopesana sezvo zvose zviri mumitemo zvinoshandisa nzira imwe chete yekugadzirisa - iyo yekushandura kumashure kwemajeri.

Mamiriro ezvinhu apo mutemo wechipiri ndewechokwadi (musiyano wehuwandu hukuru kune 50% pakati pemasero maviri) ipapo mutemo wekutanga (musiyano uri muhukuru uri kupfuura 25%) ndeyechokwadi.

Excel's Order of Precedence

Sezvo sero risingakwanisi kuva nehomwe yakatsvuka uye yebhuruu panguva imwechete, Excel inoda kuziva kuti ndeupi mutemo wekugadzirisa maitiro inofanira kushandiswa.

Murairo upi unoshandiswa unotsanangurwa neExcel's order of precedence, iyo inotaura kuti mutemo wakakosha mune urongwa muChipatimendi Chekufambisa Mutemo Mutemo webhokisi yebhokisi chinotangira.

Sezvinoratidzwa mumufananidzo uri pamusoro, murairo wechipiri unoshandiswa mumudzidzi uyu (= (A2-B2) / A2> 50%) yakakwirira pane zvakanyorwa uye, naizvozvo, ine yekutanga pane murairo wekutanga.

Semugumisiro, ruvara rwemukati weSero B5 rakashandurwa kuva rutsvuku.

Nokusingaperi, mitemo mitsva inowanikwa kumusoro kwerongwa uye, naizvozvo, ine yakakwirira kupfuura.

Kuti uchinje urongwa hwekutangira shandisa maBhokisi Akumusoro uye Akadzika mubhokisi rebhokisi sezvinoratidzwa mumufananidzo uri pamusoro.

Kushandisa Zvisingawirirani Nemitemo

Kana mitemo miviri kana kupfuura yakawanda yemitemo isingarwisani zvose inoshandiswa kana mamiriro ezvinhu ega ega kuedza kuve echokwadi.

Kana iyo yekutanga inoshandiswa kuumbwa kwetsika kutonga mumuenzaniso wedu (= (A2-B2) / A2> 25%) yakarongedza huwandu hwemasero B2: B5 nemuganhu webhuruu pane ruvara rwemuvara rwebhuruu, mitemo miviri yakagadziridzwa yemaitiro haikwanisi kupesana kubva zvose zvigadziri zvinogona kushandiswa pasina kupindirana nezvimwe.

Somugumisiro, sero B5 raizova nemuganhu webhuruu uye ruvara rwemutsvuku, sezvo mutsauko pakati pemajeri mumasero A5 neB5 mukuru kudarika zvose makumi maviri ne25 ne50 muzana.

Format Formative vs. Regular Formatting

Mumamiriro ekurwisana pakati pemitemo yekugadzirisa mafungiro uye zvigadziriswa zvinoshandiswa pakugadzirisa maitiro, maitiro ekugadzirisa mafungiro nguva dzose anotangira mberi uye achashandiswa panzvimbo pezvimwe zvingowedzera maitiro ekufambisa.

Kana ruvara rwemutsvuku rwakanga rwuri kushandiswa kumasero B2 kusvika B5 mumuenzaniso, kana mitemo yemafomiti yepamutemo yakawedzerwa, masero chete B2 neB4 aizoramba akachena.

Nemhaka yokuti mitemo yakagadziridzwa yakaiswa inoshandiswa kumasero B3 neB5, mavara avo emamiriro evanhu aizotendeuka kubva kune yero kusvika kune bhuruu uye tsvuku.