Maitiro ekushandura mutsara ruvara muExcel nemamiriro, zvichienderana nemamiriro

Muchikamu chino, iwe uchadzidza nzira yekukurumidza kushandura kumashure kwemutsara zvichibva pane chaiyo kukosha muspredishiti. Heano nhungamiro uye akasiyana mafomula ezvinyorwa uye manhamba mugwaro.

Kare, takakurukura nzira dzekushandura iyo yekumashure ruvara rwesero zvichienderana nemavara kana kukosha kwenhamba mairi. Zvinokurudzira zvicharatidzwawo pano pamusoro penzira yekusimbisa mitsara inodiwa mushanduro dzazvino dzeExcel, zvichienderana nezviri mukati mesero rimwe. Mukuwedzera, pano iwe unowana mienzaniso yemafomula anoshanda zvakaenzana kune ese anogona masero mafomati.

Maitiro ekushandura kutaridzika kwemutsara zvichienderana nenhamba mune imwe sero

Semuenzaniso, une gwaro rakavhurwa rine tafura yezvibvumirano zvesangano senge iyi.

Ngatiti iwe unoda kujekesa mitsara mumhando dzakasiyana-siyana, uchitarisa pane zvakanyorwa musero muQty column, kuti unzwisise zvakajeka kuti ndeipi yekutengeserana ndiyo inonyanya kubatsira. Kuti uwane chigumisiro ichi, unoda kushandisa "Conditional Formatting" basa. Tevera nhanho nhanho mirairo:

1. Sarudza maseru aunoda kufomatidza.
2. Gadzira mutemo mutsva wefomati nekudzvanya chinhu chakakodzera mumenu yemukati inooneka mushure mekudzvanya bhatani re "Conditional Formatting" pane "Kumba" tab.

1. Mushure meizvozvo, bhokisi rebhokisi richaonekwa paunoda kusarudza marongero "shandisa fomula kuona maseru akarongwa." Zvadaro, nyora pasi fomula inotevera: =$C2>4 mubhokisi pazasi. Nomuzvarirwo, unogona kuisa yako wega kero uye yako mavara, pamwe nekutsiva iyo > sign ne < or =. Mukuwedzera, zvakakosha kuti usakanganwa kuisa chiratidzo che $ pamberi pesero referensi kuitira kuti ugadzirise kana uchikopa. Izvi zvinoita kuti zvikwanise kusunga ruvara rwemutsara kune kukosha kwesero. Zvikasadaro, pakukopa, kero "inobuda".
2. Dzvanya pa "Format" uye shandura kune yekupedzisira tab kuti utaure mumvuri waunoda. Kana iwe usingade mimvuri inokurudzirwa nechirongwa, unogona kugara uchidzvanya pa "More Colors" uye sarudza mumvuri waunoda.
3. Mushure mekupedza mabasa ese, unofanirwa kudzvanya kaviri bhatani re "OK". Iwe unogona zvakare kuseta mamwe marudzi efomati (font type kana chaiyo cell border style) pane mamwe ma tabo eiyi hwindo.
4. Pazasi pehwindo pane preview panel paunogona kuona kuti sero rinozotaridzika sei mushure mekufomati.
5. Kana zvese zvichikukodzera, tinya bhatani rekuti "OK" kuisa shanduko. Zvose, mushure mekuita zviito izvi, mitsetse yose iyo masero ane nhamba yakakura kudarika 4 ichava yebhuruu.

Sezvaunogona kuona, kushandura hue yemutsara zvichienderana nekukosha kweimwe sero mairi haisi iyo inonyanya kuoma basa. Iwe unogona zvakare kushandisa mamwe akaomesesa mafomula kuti uwedzere kuchinjika mukushandisa ine mafomati yekuzadzisa chinangwa ichi.

Shandisa mitemo yakawanda maererano nezvavanokoshesa

Muenzaniso wapfuura wakaratidza sarudzo yekushandisa mutemo umwechete wekufometa, asi ungangoda kushandisa akati wandei kamwechete. Chii chekuita pakadai? Semuenzaniso, iwe unogona kuwedzera mutemo zvichienderana nekuti ndeapi mitsara ine nhamba 10 kana kupfuura ichasimbiswa mupink. Pano zvakakosha kuwedzera kunyora pasi fomula =$C2>9, wobva waisa zvinhu zvinokosha kuitira kuti mitemo yose ishandiswe pasina kupokana.

1. Pa "Kumba" tebhu muboka re "Styles", unofanirwa kudzvanya "Conditional Formatting" uye mune menyu inobuda, sarudza "Manage Mitemo" kumagumo echinyorwa.
2. Tevere, iwe unofanirwa kuratidza ese mitemo yakanangana negwaro iri. Kuti uite izvi, unofanirwa kuwana runyoro kumusoro "Ratidza mitemo yekugadzira", uye sarudza chinhu "Iri pepa" ipapo. Uyezve, kuburikidza nemenyu iyi, unogona kugadzirisa mitemo yefomati yemasero akasarudzwa. Muchiitiko chedu, tinoda kutarisira mitemo yegwaro rose.
3. Tevere, iwe unofanirwa kusarudza mutemo waunoda kushandisa kutanga uye wouendesa kumusoro kwechinyorwa uchishandisa miseve. Iwe uchawana mhedzisiro yakadaro.
4. Mushure mekuisa zvakakosha, iwe unofanirwa kudzvanya pa "OK", uye isu tichaona kuti mitsetse inoenderana yakashandura sei ruvara rwavo, maererano nekutanga. Chekutanga, chirongwa chakatariswa kuti chione kana kukosha kweiyo Qty column kwaive kwakakura kupfuura gumi, uye kana zvisiri, ingave yakakura kupfuura 10.

Kuchinja ruvara rwemutsara wese zvichibva pamashoko akanyorwa muchitokisi

Fungidzira kuti paunenge uchishanda nespredishiti, unonetseka nekukurumidza kuchengeta kuti ndezvipi zvinhu zvakatounzwa uye izvo zvisina. Kana kuti zvimwe ndezvechinyakare. Kuti basa iri rive nyore, unogona kuedza kusarudza mitsetse inobva pane zvinyorwa zviri mu "Delivery" cell. Ngatitii isu tinoda kumisa inotevera mirairo:

1. Kana kurongeka kwapera mushure memazuva mashoma, ruvara rwekumashure rwemutsara unoenderana uchave orenji.
2. Kana zvinhu zvatoendeswa, mutsara unoenderana unoshanduka girini.
3. Kana kuendeswa kwezvinhu kwapera, ipapo mirairo inoenderana inofanira kujekeswa mutsvuku.

Nemashoko akareruka, ruvara rwemutsara ruchachinja zvichienderana nemamiriro ehurongwa.

Kazhinji, pfungwa yezviito zvekuunzwa uye mirairo yakapfuura ichave yakafanana nemuenzaniso wakatsanangurwa pamusoro apa. Izvo zvinodikanwa kuti unyore mafomula mune ine conditional fomati hwindo =$E2=»Yaendeswa» и =$E2=»Zvakapera» zvichiteerana. Iro basa rakanyanya kuoma rezvibvumirano izvo zvinopera mukati memazuva mashoma.

Sezvatinoona, nhamba yemazuva inogona kusiyana pakati pemitsara, iyo iyo nzira iri pamusoro apa haigone kushandiswa.

Panyaya iyi pane basa =TSVAGA(“Inofanirwa kupinda”, $E2)>0, kupi:

• nharo yekutanga mumabhuraketi ndeye zvinyorwa zviri mumaseru akatsanangurwa,
• uye nharo yechipiri ikero yesero ine kukosha kwaunoda kufamba nayo.

Mushanduro yeChirungu inozivikanwa se =KUTSVAGA. Yakagadzirirwa kutsvaga maseru anoenderana nechero mubvunzo.

Zano: Iyo parameter > 0 mune fomula inoreva kuti hazvina basa kuti mubvunzo wekuisa uri papi musero zvinyorwa.

Semuyenzaniso, iyo "Delivery" column inogona kunge iine mavara ekuti "Urgent, due in 6 Hours" uye sero rinoenderana richiri kunyorwa nenzira kwayo.

Kana zvichidikanwa kushandisa mitemo yekufometa kumitsara panotangira sero rekiyi nemutsara waunoda, saka unofanira kunyora =1 mufomula pane > 0. 

Mitemo iyi yese inogona kunyorwa mubhokisi rebhokisi rinoenderana, semumuenzaniso uri pamusoro. Nekuda kweizvozvo, iwe unowana zvinotevera:

Nzira yekushandura sei ruvara rwesero zvichienderana nekukosha mune imwe sero?

Sezvakangoita nemutsara, matanho ari pamusoro anogona kuiswa kune imwe sero kana huwandu hwehuwandu. Mumuenzaniso uyu, mafomati anongoiswa kumaseru ari mu "Order number" column:

Maitiro ekushandisa akawanda mamiriro ekufomatidza

Kana iwe uchida kushandisa akati wandei mafomati emitemo kune tambo, saka pane kunyora akasiyana mitemo, iwe unofanirwa kugadzira imwe ine mafomula. =OR or =И. Yokutanga inoreva kuti “mumwe wemitemo iyi ndeyechokwadi,” uye yechipiri inoreva kuti “yose miviri iyi mitemo ndeyechokwadi.”

Muchiitiko chedu, tinonyora mafomula anotevera:

=ИЛИ($F2=»Inofanirwa mu1 Mazuva», $F2=»Inofanirwa muMazuva matatu»)

=ИЛИ($F2=»Inofanirwa mu5 Mazuva», $F2=»Inofanirwa muMazuva matatu»)

Uye formula =И inogona kushandiswa, semuenzaniso, kutarisa kana nhamba iri muQty column iri. mukuru kupfuura kana kuenzana na5 uye akaderera pane kana kuenzana na10.

Mushandisi anogona kushandisa zvinopfuura chimiro chimwe mumafomula.

Iye zvino iwe unoziva zvaunofanira kuita kuti uchinje ruvara rwemutsara zvichibva pane imwe sero, iwe unonzwisisa maitiro ekuisa akawanda mamiriro uye kuaisa pamberi, uye mashandisiro emafomu akawanda kamwechete. Zvadaro, unofanira kuratidza fungidziro.