Mukati
- Dynamic cell background shanduko yeruvara
- Maitiro ekuchengetedza sero ruvara rwakafanana kunyangwe kukosha kuchichinja?
- Sarudza maseru ane chimwe chimiro
- Kuchinja kumashure kwemasero akasarudzwa kuburikidza ne "Format Cells" hwindo
- Kugadzirisa ruvara rwekumashure kwemaseru akakosha (asina kana zvikanganiso kana uchinyora fomula)
- Nzira yekuwana zvakanyanya kubva muExcel?
Muchikamu chino, iwe uchadzidza nzira mbiri dzakareruka dzekushandura kumashure kwemasero zvichibva pane zvawo mune zvazvino mavhezheni eExcel. Iwe unozonzwisisa zvakare kuti ndeapi mafomula ekushandisa kushandura mumvuri wemaseru kana maseru uko mafomura akanyorwa zvisizvo, kana pasina ruzivo zvachose.
Wese munhu anoziva kuti kugadzirisa kumashure kwesero iri nyore inzira iri nyore. Dzvanya pa "background color". Asi ko kana iwe uchida kugadziridzwa kwemavara zvichibva pane imwe sero yemukati? Ndingaita sei kuti izvi zviitike zvoga? Izvi zvinotevera nhevedzano yeruzivo runobatsira runozoita kuti iwe uwane nzira kwayo yekuzadzisa ese aya mabasa.
Dynamic cell background shanduko yeruvara
basa: Une tafura kana seti yezvakakosha, uye iwe unofanirwa kugadzirisa kumashure kwemavara emaseru zvichienderana nenhamba ipi yakapinda ipapo. Iwe unofanirwawo kuve nechokwadi kuti hue inopindura kuchinja maitiro.
mhinduro: Pabasa iri, basa reExcel "conditional formatting" rinopihwa mavara maseru ane nhamba huru pane X, isingasviki Y, kana pakati paX naY.
Ngatiti iwe une seti yezvigadzirwa mumatunhu akasiyana nemitengo yazvo, uye iwe unofanirwa kuziva kuti ndeupi wavo anodhura kupfuura $3,7. Naizvozvo, takasarudza kuratidza izvo zvigadzirwa zviri pamusoro pekukosha uku mutsvuku. Uye masero ane mutengo wakafanana kana mukuru, zvakasarudzwa kuti zvisvibise mune yakasvibira tint.
Cherechedza: Iyo skrini yakatorwa mushanduro ye2010 yechirongwa. Asi izvi hazvikanganisi chero chinhu, sezvo kutevedzana kwezviito kwakafanana, pasinei neshanduro ipi - yazvino kana kwete - munhu anoshandisa.
Saka hezvino zvaunofanira kuita (nhanho nhanho):
1. Sarudza masero umo hue inofanira kugadziriswa. Somuenzaniso, mutsara $B$2:$H$10 (mazita ekoramu uye koramu yekutanga, iyo inoronga mazita ehurumende, haana kubatanidzwa kubva mumuenzaniso).
2. Dzvanya "Kumba" muboka “Chimiro”. Pachava nechinhu "Conditional Formatting". Ikoko unofanirawo kusarudza “Mutemo Mutsva”. Muchirungu vhezheni yeExcel, kutevedzana kwematanho kunotevera: "Kumba", "Styles group", "Conditional Formatting> New Rule».
3. Muhwindo rinovhura, tarisa bhokisi "Fomati masero ane chete" (“Fomati maseru anemo chete” mushanduro yeChirungu).
4. Pazasi pehwindo iri pasi pechinyorwa "Fomati masero chete anosangana nemamiriro anotevera" (Fomati maseru chete ne) unogona kupa mitemo iyo mafomati achaitwa. Isu takasarudza iyo fomati yeyakatarwa kukosha mumaseru, iyo inofanirwa kunge yakakura kupfuura 3.7, sezvauri kuona kubva pascreenshot:
5. Zvadaro, baya bhatani "Fomati". Hwindo richaonekwa rine nzvimbo yekusarudza ruvara rwekumashure kuruboshwe. Asi zvisati zvaitika, iwe unofanirwa kuvhura iyo tab “Zadza” ("Zadza"). Muchiitiko ichi, yakatsvuka. Pashure paizvozvo, tinya bhatani "OK".
6. Ipapo iwe uchadzokera kuhwindo "New Formatting Rule", asi nechekare pazasi pehwindo rino unogona kuona kuti sero iri richataridzika sei. Kana zvinhu zvose zvakanaka, unofanira tinya bhatani "OK".
Nekuda kweizvozvo, iwe unowana chimwe chinhu chakadai:
Tevere, isu tinofanirwa kuwedzera imwe mamiriro, ndiko kuti, shandura kumashure kwemaseru ane tsika isingasviki 3.45 kune yegirini. Kuti uite basa iri, unofanirwa kudzvanya "New Formatting Rule" uye dzokorora nhanho dziri pamusoro, chete mamiriro anofanirwa kuiswa se "zvishoma pane, kana zvakaenzana" (mushanduro yeChirungu "zvishoma pane kana kuenzana na", wobva wanyora kukosha. Pakupedzisira, unofanirwa kubaya bhatani rekuti "OK".
Iye zvino tafura yakarongwa nenzira iyi.
Inoratidza mitengo yemafuta epamusoro uye yakaderera munzvimbo dzakasiyana siyana, uye unogona kuona nekukasika mamiriro ezvinhu ane tarisiro (muTexas, hongu).
Nyaya: Kana zvichidikanwa, unogona kushandisa yakafanana fomati nzira, kugadzirisa kwete kumashure, asi font. Kuti uite izvi, muhwindo rekufomatidza rakaonekwa muchikamu chechishanu, unofanirwa kusarudza iyo tab "Font" uye tevera zvirevo zvinopihwa pahwindo. Zvese zviri intuitively zvakajeka, uye kunyangwe anotanga anogona kuzvifunga.
Nekuda kweizvozvo, iwe uchawana tafura seizvi:
Maitiro ekuchengetedza sero ruvara rwakafanana kunyangwe kukosha kuchichinja?
basa: Iwe unofanirwa kupendesa kumashure kuti isambochinja, kunyangwe iyo yekumashure ichichinja mune ramangwana.
mhinduro: tsvaga maseru ese ane chaiyo nhamba uchishandisa excel basa “Tsvaga zvese” "Tsvaga Zvese" kana kuwedzera-on "Sarudza special cells" ("Sarudza Masero Akakosha"), wobva wagadzirisa fomati yesero uchishandisa basa racho "Format cells" ("Format Cells").
Iyi ndeimwe yeaya akajairika mamiriro asina kuvharwa muExcel manual, uye kunyangwe paInternet, mhinduro yedambudziko iri inogona kuwanikwa kashoma. Izvo hazvishamisi, sezvo basa iri risiri chiyero. Kana iwe uchida kugadzirisa iyo yekumashure zvachose kuti isazochinja kudzamara yagadziriswa nemaoko nemushandisi wechirongwa, unofanirwa kutevedzera mirairo iri pamusoro.
Sarudza maseru ane chimwe chimiro
Pane nzira dzinoverengeka dzinogoneka, zvichienderana nekuti rudzii rwehukoshi huchawanikwa.
Kana iwe uchida kusarudza maseru ane yakakosha kukosha ine yakakosha kumashure, iwe unofanirwa kuenda kune iyo tab "Kumba" uye sarudza "Tsvaga uye sarudza" - "Tsvaga".
Pinda izvo zvinodiwa uye tinya pa “Wana Zvese”.
batsira: Unogona kudzvanya bhatani "Sarudzo" kurudyi kutora mukana wemamwe marongero ekuwedzera: pekutsvaga, maitiro ekuona, kana kuremekedza mavara makuru uye madiki, zvichingodaro. Iwe unogona zvakare kunyora mamwe mavara, senge asterisk (*), kuti uwane mitsetse yese ine izvi zvakakosha. Kana ukashandisa chiratidzo chemubvunzo, unogona kuwana chero munhu mumwe chete.
Mumuenzaniso wedu wapfuura, dai taida kuwana mafuta emafuta ese ari pakati pe$3,7 ne$3,799, taigona kukwenenzvera kutsvaga kwedu.
Zvino sarudza chero hupi hwehupi hunowanikwa nechirongwa pazasi pebhokisi rebhokisi uye tinya pane imwe yacho. Mushure meizvozvo, dzvanya kiyi yekubatanidza "Ctrl-A" kusarudza zvese zvabuda. Zvadaro, tinya bhatani "Vhara".
Heano maitiro ekusarudza ese maseru ane chaiwo kukosha uchishandisa iyo Tsvaga Yese chimiro. Mumuenzaniso wedu, tinoda kuwana mitengo yese yemafuta iri pamusoro pe $3,7, uye zvinosuruvarisa kuti Excel haibvumire kuita izvi uchishandisa Tsvaga uye Kutsiva basa.
"Dhora reuchi" rinouya pachena pano nokuti pane chimwe chishandiso chichabatsira pamabasa akaoma kudaro. Inonzi Select Special Cells. Iyi yekuwedzera (inoda kuiswa kuExcel zvakasiyana) ichabatsira:
- tsvaga maitiro ese mune imwe nhanho, semuenzaniso pakati -1 ne45,
- wana kukosha kwepamusoro kana kushoma muchikamu,
- tsvaga tambo kana rudzi,
- tsvaga maseru nemuvara wekumashure uye nezvimwe zvakawanda.
Mushure mekuisa iyo yekuwedzera, ingobaya bhatani "Sarudza nemutengo" ("Sarudza NeKukosha") uye wozonatsa mubvunzo wekutsvaga muhwindo readdon. Mumuenzaniso wedu, tiri kutsvaga nhamba dzinopfuura 3,7. Press "Sarudza" ("Sarudza"), uye mumasekondi iwe unowana mhedzisiro seizvi:
Kana iwe uchifarira iyo yekuwedzera, unogona kudhawunirodha vhezheni yekuedza pa kubatana.
Kuchinja kumashure kwemasero akasarudzwa kuburikidza ne "Format Cells" hwindo
Iye zvino, mushure mokunge masero ane humwe kukosha akasarudzwa achishandisa imwe yenzira dzakatsanangurwa pamusoro apa, inoramba ichitsanangura ruvara rwemashure kwavari.
Kuti uite izvi, unofanirwa kuvhura hwindo "Cell format"nekudzvanya Ctrl + 1 kiyi (iwe unogona zvakare kudzvanya-kurudyi pamaseru akasarudzwa uye kuruboshwe-kuruboshwe pane "sero fomati" chinhu) uye gadzirisa iyo fomati yaunoda.
Tichasarudza mumvuri weorenji, asi iwe unogona kusarudza chero imwe.
Kana iwe uchida kugadzirisa iyo yekumashure ruvara pasina kuchinja mamwe maitiro ekutaridzika, unogona kungodzvanya pairi "Colour fill" uye sarudza ruvara runokodzera iwe zvakakwana.
Mhedzisiro itafura yakaita seiyi:
Kusiyana nemaitiro ekare, pano ruvara rwesero haruzoshanduki kunyange kana kukosha kwakagadziriswa. Izvi zvinogona kushandiswa, semuenzaniso, kutevedzera mafambisirwo ezvigadzirwa mune rimwe boka remitengo. Kukosha kwavo kwakachinja, asi ruvara rwakaramba rwakafanana.
Kugadzirisa ruvara rwekumashure kwemaseru akakosha (asina kana zvikanganiso kana uchinyora fomula)
Zvakafanana nemuenzaniso wapfuura, mushandisi ane kugona kugadzirisa kumashure kwemavara emasero akakosha munzira mbiri. Pane static uye dynamic sarudzo.
Kushandisa Formula Kugadzirisa Background
Pano ruvara rwesero rinozogadziriswa otomatiki zvichienderana nekukosha kwayo. Iyi nzira inobatsira vashandisi zvakanyanya uye iri kudiwa mu99% yemamiriro ezvinhu.
Semuenzaniso, unogona kushandisa tafura yapfuura, asi ikozvino mamwe maseru anenge asina chinhu. Isu tinofanirwa kuona kuti ndedzipi dzisina chero kuverenga uye kugadzirisa iyo yekumashure ruvara.
1. Pane tab "Kumba" unofanira kudzvanya pa "Conditional Formatting" -> “Mutemo Mutsva” (zvakafanana nechikamu chechipiri chechikamu chekutanga "Shandura ruvara rwemashure zvine simba".
2. Zvadaro, unofanira kusarudza chinhu "Shandisa fomula kuona ...".
3. Isa fomula =IsBlank() (ISBLANK in the version), if you want to edit the background of an empty cell, or =IsError() (ISERROR in the version), if you need to find a cell where there is an erroneously written formula. Since in this case we need to edit empty cells, we enter the formula =IsBlank(), wobva waisa cursor pakati pemaparentheses wodzvanya bhatani riri padivi peformula yekuisa ndima. Mushure mekuita uku, huwandu hwemasero hunofanirwa kusarudzwa nemaoko. Mukuwedzera, iwe unogona kutsanangura chiyero iwe pachako, semuenzaniso, =IsBlank(B2:H12).
4. Dzvanya bhatani re "Format" uye sarudza ruvara rwemashure rwakakodzera uye ita zvose sezvinotsanangurwa mundima 5 yechikamu che "Dynamic cell background color change", wobva wadzvanya "OK". Ikoko iwe unogonawo kuona kuti ruvara rwesero ruchava chii. Hwindo richaita seizvi.
5. Kana iwe uchida kumashure kwesero, unofanira kudzvanya bhatani re "OK", uye shanduko dzichaitwa pakarepo patafura.
Kuchinja kwakasimba kwemuvara wekumashure wemasero akakosha
Mumamiriro ezvinhu aya, kana yangopihwa, ruvara rwekumashure rucharamba rukadaro, zvisinei nekuti sero rinoshanduka sei.
Kana iwe uchida kushandura zvachose maseru akakosha (asina kana ane zvikanganiso), tevera iyi mirairo:
- Sarudza gwaro rako kana maseru akati wandei wodzvanya F5 kuvhura iyo Go To hwindo, wobva wadzvanya bhatani “Simbisa”.
- In the dialog box that opens, select the “Blanks” or “Empty cells” button (depending on the version of the program – or English) to select empty cells.
- Kana iwe uchida kuratidza maseru ane mafomula ane zvikanganiso, iwe unofanirwa kusarudza chinhu "Nzira" uye siya bhokisi rekutarisa rimwechete padivi peshoko rekuti "Mhosho". Sezvinotevera kubva pascreenshot iri pamusoro, maseru anogona kusarudzwa zvinoenderana nechero paramita, uye imwe neimwe yakatsanangurwa marongero inowanikwa kana zvichidikanwa.
- Pakupedzisira, unofanirwa kushandura ruvara rwemashure emasero akasarudzwa kana kuagadzirisa neimwe nzira. Kuti uite izvi, shandisa nzira inotsanangurwa pamusoro apa.
Ingo rangarira kuti shanduko dzemafomati dzakaitwa nenzira iyi dzinoramba dzichienderera kunyangwe iwe ukazadza maburi kana kushandura yakakosha sero mhando. Zvechokwadi, hazvigoneki kuti mumwe munhu achada kushandisa nzira iyi, asi mukuita chero chinhu chinogona kuitika.
Nzira yekuwana zvakanyanya kubva muExcel?
Sevashandisi vanorema veMicrosoft Excel, iwe unofanirwa kuziva kuti ine akawanda maficha. Vamwe vavo vatinoziva uye tinovada, nepo vamwe vachiramba vasinganzwisisike kune avhareji mushandisi, uye nhamba huru yevablogi vari kuedza kuburitsa zvishoma zvishoma pavari. Asi kune mabasa akajairwa ayo mumwe nemumwe wedu achafanirwa kuita, uye Excel haiunze mamwe maficha kana maturusi ekugadzirisa mamwe maitiro akaomarara.
Uye mhinduro yedambudziko iri ndeye-add-ons (addons). Zvimwe zvacho zvinogoverwa pasina muripo, vamwe - nemari. Kune akawanda maturusi akafanana anogona kuita mabasa akasiyana. Semuenzaniso, tsvaga zvakapetwa mumafaira maviri asina cryptic formula kana macros.
Kana ukabatanidza maturusi aya nemusimboti mashandiro eExcel, unogona kuwana mibairo mikuru. Semuenzaniso, unogona kuona kuti ndeapi mitengo yemafuta yakachinja, uye wozowana zvakapetwa mufaira regore rapfuura.
Isu tinoona kuti conditional fomati chishandiso chinoshanda chinokutendera kuti uite otomatiki basa pamatafura pasina hunyanzvi chaihwo. Iwe zvino wave kuziva nzira yekuzadza maseru munzira dzinoverengeka, zvichienderana nezviri mukati mavo. Iye zvino chasara kungozviisa mukuita. Rombo rakanaka!