2 nzira dzekuchinja sero kuzadza ruvara muExcel zvichienderana nehunhu hwavo

Muchinyorwa chino, uchawana nzira mbiri dzinokurumidza kushandura ruvara rwesero zvichienderana nekukosha kwayo muExcel 2013, 2010, uye 2007. Zvakare, iwe uchadzidza mashandisiro emafomula muExcel kushandura ruvara rwemasero asina chinhu. kana masero ane formula kukanganisa.

Wese munhu anoziva kuti kushandura ruvara rwekuzadza kwesero rimwe chete kana huwandu hwese muExcel, ingobaya bhatani Zadza ruvara (Zadza ruvara). Asi ko kana iwe uchida kushandura ruvara rwekuzadza masero ese ane humwe kukosha? Uyezve, ko kana iwe uchida kuti ruvara rwekuzadza rwesero rega rega rishanduke otomatiki sezvo zviri mukati mesero iroro zvinoshanduka? Kuwedzera mune chinyorwa iwe uchawana mhinduro dzemibvunzo iyi uye uwane akati wandei matipi anobatsira anozokubatsira iwe kusarudza nzira chaiyo yekugadzirisa dambudziko rega rega.

• Shandura iyo yekuzadza ruvara rwemaseru zvichienderana nehunhu hwavo (dynamically) - Iyo yekuzadza ruvara ichachinja otomatiki kana kukosha kwesero kuchichinja.
• Shandura ruvara rwesero zvichienderana nekukosha kwayo ikozvino (statically) - Kana yaiswa, ruvara rwekuzadza harushanduki, zvisinei kuti kukosha kwesero kunoshanduka sei.
• Shandura ruvara rwemamwe masero (asina chinhu, ane zvikanganiso, ane mafomula)

Maitiro ekushandura zvine simba ruvara rwesero muExcel zvichienderana nekukosha kwayo

Muvara wekuzadza uchachinja zvichienderana nekukosha kwesero.

Problem: Iwe une tafura kana huwandu hwe data uye iwe unoda kushandura ruvara rwekuzadza masero zvichienderana nehunhu hwavo. Uyezve, zvinodikanwa kuti ruvara urwu ruchinje zvine simba, zvichiratidza shanduko mu data mumaseru.

Sarudzo: Shandisa mafomati ezvimiso muExcel kuratidza kukosha kwakakura pane X, isingasviki Y, kana pakati peX naY.

Ngatiti iwe une runyorwa rwemitengo yegesi mumatunhu akasiyana, uye iwe unoda mitengo yakakwira kupfuura $ 3.7, zvakaratidzwa mutsvuku, uye zviduku kana zvakaenzana $ 3.45 - girinhi.

Cherechedza: Iwo mascreenshots emuenzaniso uyu akatorwa muExcel 2010, zvisinei, muExcel 2007 uye 2013, mabhatani, dialog, uye marongero achave akafanana kana akasiyana zvishoma.

Saka, hezvino zvaunofanira kuita nhanho nhanho:

1. Sarudza tafura kana renji yaunoda kushandura sero rekuzadza ruvara. Mumuenzaniso uyu, tinosimbisa $B$2:$H$10 (misoro yemisoro uye koramu yekutanga ine mazita ematunhu haina kusarudzwa).
2. Baya Home (Kumba), muchikamu Styles (Zvitaera) tinya Mamiriro Ekuumbwa (Conditional Formatting) > Mitemo Mitsva (Gadzira mutemo).
3. Pamusoro pebhokisi rebhokisi New Formatting Rule (Gadzira Formatting Rule) mumunda Sarudza Rule Type (Sarudza rudzi rwekutonga) sarudza Fomati chete maseru ane (Fomati masero ane chete).
4. Pazasi pebhokisi rebhokisi mubhokisi Fomati Chete Masero ane (Fomati chete masero anosangana nemamiriro anotevera) Seta mamiriro emutemo. Isu tinosarudza kufomati masero chete ane mamiriro: Sero Kukosha (cell value) - mukuru kudarika (zvimwe) - 3.7sezvinoratidzwa mumufananidzo uri pasi apa.Wobva wadzvanya bhatani zera (Format) kusarudza kuti ndeupi ruvara rwekuzadza runofanira kuiswa kana mamiriro akataurwa asangana.
5. Mubhokisi rebhokisi rinoonekwa Fomati Masero (Format Cells) tab Zadza (Zadza) uye sarudza ruvara (takasarudza tsvuku) uye tinya OK.
6. Mushure maizvozvo iwe uchadzokera kuhwindo New Formatting Rule (Kugadzira mutemo wekugadzirisa) uko mumunda Preview (Sample) icharatidza muenzaniso wemafomati ako. Kana wagutsikana, tinya OK.

Mhedzisiro yezvigadziriso zvako zvefomati inotaridzika seizvi:

Sezvo isu tichida kumisa chimwe chimiro chinotibvumira kushandura ruvara rwekuzadza kuita girini kune maseru ane kukosha kushoma pane kana kuenzana 3.45, wobva wadzvanya bhatani zvakare Mitemo Mitsva (Gadzira Rule) uye dzokorora nhanho 3 kusvika 6, uchiisa mutemo waunoda. Pazasi pane sampuli yechipiri ine zvirevo zvekufometa mutemo watakagadzira:

Kana zvese zvagadzirira - tinya OK. Iwe zvino une tafura yakarongwa zvakanaka iyo inokutendera iwe kuti uone iyo yakanyanya uye yakaderera mitengo yegasi mumatunhu akasiyana nekungotarisa. Zvakanaka kwavari ikoko, muTexas! 🙂

Tip: Nenzira imwecheteyo, iwe unogona kushandura iyo font color zvichienderana nekukosha kwesero. Kuti uite izvi, ingovhura iyo tab Font (Font) mubhokisi rekutaura Fomati Masero (Sero Format) sezvatakaita padanho 5 uye sarudza yaunoda font ruvara.

Maitiro ekuseta anogara esero ruvara zvichienderana nekukosha kwayo ikozvino

Kana yangoiswa, ruvara rwekuzadza harushanduke, zvisinei kuti zviri mukati mesero zvinoshanduka sei mune ramangwana.

Problem: Iwe unoda kugadzirisa ruvara rwesero zvichienderana nekukosha kwayo ikozvino, uye iwe unoda kuti ruvara rwekuzadza rurambe rwakafanana kunyangwe kukosha kwesero kuchichinja.

Sarudzo: Tsvaga maseru ese ane kukosha (kana kukosha) uchishandisa chishandiso Find All (Tsvaga Zvose) wobva washandura mafomati emasero akawanikwa uchishandisa bhokisi rehurukuro Fomati Masero (cell format).

Iri nderimwe reaya asingawanzo mabasa ayo asina tsananguro muExcel mafaera ekubatsira, maforamu kana mablogiki, uye pasina mhinduro yakananga. Uye izvi zvinonzwisisika, sezvo basa iri harina kufanana. Uye zvakadaro, kana iwe uchida kushandura sero kuzadza ruvara zvachose, ndiko kuti, kamwe uye zvachose (kana kusvika wachinja iwe pachako), tevera matanho aya.

Tsvaga uye sarudza maseru ese anosangana nemamiriro akapihwa

Mamiriro akati wandei anogoneka pano, zvichienderana nekuti ndeupi rudzi rwekukosha kwauri kutsvaga.

Kana iwe uchida kupendesa maseru nehumwe kukosha, semuenzaniso, 50, 100 or 3.4 - ipapo pane tab Home (Kumba) muchikamu Kugadzirisa (Editing) tinya Tsvaga Sarudza (Tsvaga uye simbisa) > wana (Tsvaga).

Isa kukosha kwaunoda uye tinya Find All (Tsvaga zvese).

Tip: Kurudyi rwebhokisi rebhokisi Tsvaga uye Tora (Tsvaga uye Tsiva) pane bhatani Options (Sarudzo), nekudzvanya izvo zvauchakwanisa kuwana kune akati wandei epamusoro ekutsvaga marongero, senge Match Case (case sensitive) uye Matanidza zvese zviri mukati mesero (Sero yese). Unogona kushandisa mavara emusango senge asterisk (*) kuenderana nechero tambo yemavara, kana mubvunzo mucherechedzo (?) kuti uenderane chero mavara.

Nezve muenzaniso wapfuura, kana tichida kuwana ese mitengo yepeturu kubva 3.7 to 3.799, tobva taisa zvinotevera kutsvaga maitiro:

Zvino tinya pane chero chinhu chakawanikwa pazasi pebhokisi rebhokisi Tsvaga uye Tora (Tsvaga uye Tsiva) uye tinya Ctrl + Akuratidza zvese zvakawanikwa. Mushure meizvozvo dzvanya bhatani pedyo (Vhara).

Aya ndiwo maitiro aungaita masero ese ane kukosha kwakapihwa (values) uchishandisa sarudzo Find All (Tsvaga Zvese) muExcel.

Nekudaro, muchokwadi, isu tinofanirwa kuwana ese mitengo yepeturu inodarika $ 3.7. Zvinosuruvarisa chombo Tsvaga uye Tora (Tsvaga uye Tsiva) haigone kutibatsira neizvi.

Chinja Ruzadza Ruvara rweMasero Akasarudzwa Uchishandisa iyo Format Cells Dialog Bhokisi

Iwe zvino une maseru ese ane kukosha kwakapihwa (kana kukosha) akasarudzwa, isu takangoita izvi nechombo Tsvaga uye Tora (Tsvaga uye tsiva). Zvose zvaunofanirwa kuita kuisa ruvara rwekuzadza kwemasero akasarudzwa.

Vhura bhokisi rekutaura Fomati Masero (Sero Format) mune chero ye3 nzira:

• kudzvinyirira Ctrl + 1.
• nekudzvanya pane chero sero yakasarudzwa ine bhatani rekurudyi rembeva uye kusarudza chinhu kubva pane zviri mukati menyu Fomati Masero (cell format).
• Tab Home (Kumba) > Masero. Masero. (masero) > zera (Format) > Fomati Masero (cell format).

Tevere, gadzirisa sarudzo dzemafomati sezvaunoda. Panguva ino tichaisa ruvara rwekuzadza kune orenji, kungoshandura 🙂

Kana iwe uchida kushandura chete ruvara rwekuzadza pasina kubata mamwe mafomati sarudzo, unogona kungodzvanya bhatani Zadza ruvara (Zadza ruvara) uye sarudza ruvara rwaunoda.

Heino mhedzisiro yekuchinja kwedu mafomati muExcel:

Kusiyana neyakapfuura nzira (ine inomisikidzwa fomati), iyo yekuzadza ruvara yakaiswa nenzira iyi haizombozvichinji pachayo pasina ruzivo rwako, zvisinei kuti maitiro anochinja sei.

Chinja iyo ruvara rwekuzadza kune akakosha maseru (isina chinhu, paine kukanganisa mufomula)

Sezvakaita mumuenzaniso wapfuura, unogona kushandura ruvara rwekuzadza masero munzira mbiri: zvine simba uye statically.

Shandisa fomula kuti uchinje ruvara rwekuzadza masero akakosha muExcel

Rudzi rwesero ruchachinja otomatiki zvichienderana nekukosha kwesero.

Iwe uchanyanya kushandisa nzira iyi yekugadzirisa dambudziko mu99% yezviitiko, ndiko kuti, kuzadza kwemasero kuchachinja zvichienderana nemamiriro awakatsanangura.

Semuenzaniso, ngatitorei tafura yemutengo wepeturu zvakare, asi panguva ino tichawedzera mamwe akati wandei, uye tiite mamwe maseru asina chinhu. Iye zvino tarisa kuti ungawana sei maseru asina chinhu uye uchinje iwo mavara ekuzadza.

1. Pa Advanced tabo Home (Kumba) muchikamu Styles (Zvitaera) tinya Mamiriro Ekuumbwa (Conditional Formatting) > Mitemo Mitsva (Gadzira mutemo). Sezvakangoita muchikamu chechipiri chemuenzaniso Maitiro ekushandura zvine simba ruvara rwesero zvichienderana nekukosha kwayo.
2. Mubhokisi rehurukuro New Formatting Rule (Gadzira mutemo wekufomatidza) sarudza sarudzo Shandisa fomula kuti uone kuti ndeapi maseru fomati (Shandisa fomula kuona kuti ndeapi maseru ekufomatidza). Kuwedzera kumunda Fomati maitiro apo fomula iyi ichokwadi (Mafomati maitiro ayo anotevera formula ichokwadi) isa imwe yemafomula:
   • kushandura kuzadzwa kwemasero asina chinhu

     =ISBLANK()

     =ЕПУСТО()

   • kushandura shading yemasero ane mafomula anodzosa kukanganisa

     =ISERROR()

     =ЕОШИБКА()

   Sezvo tichida kushandura ruvara rwemasero asina chinhu, tinoda basa rekutanga. Ipinze, wobva waisa chitubu pakati pemabhuraketi uye tinya chiratidzo chekusarudza renji padivi rekurudyi remutsara (kana nyora iro raunoda nemaoko):

   =ISBLANK(B2:H12)

   =ЕПУСТО(B2:H12)

   

3. dzvanya zera (Format), sarudza yaunoda kuzadza ruvara pane tab Zadza (Zadza), wobva wadzvanya OK. Mirayiridzo yakadzama inopihwa mudanho rechishanu remuenzaniso "Maitiro ekuchinja zvine simba ruvara rwesero zvichienderana nekukosha kwaro." Sample yeconditional fomati yawakamisa inotaridzika seizvi:
4. Kana uchifara nemuvara, tinya OK. Iwe uchaona kuti mutemo wakagadzirwa uchashandiswa sei pakarepo patafura.

Shandura ruvara rwekuzadza kwemasero akakosha statically

Kana yangogadziriswa, kuzadza kucharamba kusina kuchinja, pasinei nekukosha kwesero.

Kana iwe uchida kuseta chisingaperi ruvara rwekuzadza maseru asina chinhu kana maseru ane mafomula ane zvikanganiso, shandisa iyi nzira:

1. Sarudza tafura kana renji wodzvanya F5kuvhura dialog Enda ku (Jump), wobva wadzvanya bhatani Special (Simbisa).
2. Mubhokisi rehurukuro Enda kune Special (Sarudza boka remasero) tarisa sarudzo blanks (Masero asina chinhu) kusarudza ese asina masero.Kana iwe uchida kuratidza maseru ane mafomula ane zvikanganiso, tarisa sarudzo Formulas (mafomula) > Zvikanganiso (Mistakes). Sezvauri kuona pamufananidzo uri pamusoro, kune akawanda mamwe marongero anowanikwa kwauri.
3. Pakupedzisira, shandura kuzadza kwemasero akasarudzwa kana kuisa chero mamwe maitiro ekugadzirisa uchishandisa bhokisi rebhokisi Fomati Masero (Format Cells), sekutsanangurwa kwazvinoitwa muKuchinja Kuzadza Kwemasero Akasarudzwa.

Usakanganwa kuti mafomati magadzirirwo akaitwa nenzira iyi anozochengetedzwa kunyangwe maseru asina chinhu akazadzwa nehunhu kana zvikanganiso mumafomula akagadziriswa. Zvakaoma kufungidzira kuti mumwe munhu angangoda kuenda nenzira iyi, kunze kwezvinangwa zvekuyedza 🙂