Hanyoyi 2 don canza launi cika cell a cikin Excel dangane da ƙimar su

A cikin wannan labarin, za ku sami hanyoyi guda biyu masu sauri don canza launin tantanin halitta bisa ga darajarsa a cikin Excel 2013, 2010, da 2007. Har ila yau, za ku koyi yadda ake amfani da ƙididdiga a cikin Excel don canza launi na sel mara kyau. ko sel masu kurakuran tsari.

Kowa ya san cewa don canza launi na tantanin halitta guda ɗaya ko duka kewayo a cikin Excel, danna maɓallin Cika launi (Cika launi). Amma idan kuna buƙatar canza launi mai cika dukkan sel waɗanda ke ɗauke da takamaiman ƙima fa? Bugu da ƙari, idan kuna son cikar launi na kowane tantanin halitta ya canza ta atomatik yayin da abin da ke cikin tantanin halitta ke canzawa? Bugu da ari a cikin labarin za ku sami amsoshin waɗannan tambayoyin kuma ku sami wasu shawarwari masu amfani waɗanda zasu taimake ku zaɓi hanyar da ta dace don magance kowace takamaiman matsala.

• Canja cika launi na sel dangane da ƙimar su (a zahiri) - Launin cika zai canza ta atomatik lokacin da ƙimar tantanin halitta ta canza.
• Canja launi na tantanin halitta dangane da ƙimar sa na yanzu (a tsaye) - Da zarar an saita, launi mai cika ba ya canzawa, komai yadda darajar tantanin ta canza.
• Canja launin wasu sel (ba komai, tare da kurakurai, tare da dabaru)

Yadda ake canza launin tantanin halitta a cikin Excel bisa kimarsa

Launin cika zai canza dangane da ƙimar tantanin halitta.

matsala: Kuna da tebur ko kewayon bayanai kuma kuna son canza cika launi na sel dangane da ƙimar su. Bugu da ƙari, ya zama dole cewa wannan launi ya canza a hankali, yana nuna canje-canje a cikin bayanai a cikin sel.

Yanke shawara: Yi amfani da tsari na yanayi a cikin Excel don haskaka ƙimar da ta fi X, ƙasa da Y, ko tsakanin X da Y.

Bari mu ce kuna da jerin farashin gas a jihohi daban-daban, kuma kuna son farashin da ya fi girma $ 3.7, an yi alama da ja, kuma ƙarami ko daidai $ 3.45 - kore.

lura: An ɗauki hotunan hotunan wannan misalin a cikin Excel 2010, duk da haka, a cikin Excel 2007 da 2013, maɓallan, maganganu, da saitunan za su kasance daidai ɗaya ko ɗan bambanta.

Don haka, ga abin da kuke buƙatar yin mataki-mataki:

1. Zaɓi tebur ko kewayon da kuke son canza launi cika tantanin halitta. A cikin wannan misali, mun haskaka $B$2:$H$10 (ba a zabar rubutun shafi da shafi na farko da ke dauke da sunayen jihohin ba).
2. danna Gida (Gida), a cikin sashe styles (Styles) danna Tsarin Yanayi (Tsarin Yanayi)> Sabbin Dokoki (Ƙirƙiri doka).
3. A saman akwatin maganganu Sabuwar Dokar Tsarin (Ƙirƙiri Tsarin Tsara) a cikin filin Zaɓi Nau'in Doka (Zaɓi nau'in doka) zaɓi Tsara sel waɗanda suka ƙunshi kawai (Ka tsara sel waɗanda suka ƙunshi kawai).
4. A kasan akwatin maganganu a cikin akwatin Tsara Kwayoyin Kawai tare da (tsarin sel kawai waɗanda suka cika yanayin da ke gaba) Saita sharuɗɗan ƙa'ida. Mun zaɓi tsara sel kawai tare da yanayin: Darajar salula (darajar salula) - mafi girma daga (Kara) - 3.7kamar yadda aka nuna a hoton da ke ƙasa.Sannan danna maballin size (Format) don zaɓar wane launi mai cika ya kamata a yi amfani da shi idan ƙayyadadden yanayin ya cika.
5. A cikin akwatin maganganu da ya bayyana Tsarin Sel (Format Cells) tab Cika (Cika) kuma zaɓi launi (mun zaɓi ja) kuma danna OK.
6. Bayan haka zaku koma taga Sabuwar Dokar Tsarin (Ƙirƙirar ƙa'idar tsarawa) inda a cikin filin Preview (Sample) zai nuna samfurin tsarin ku. Idan kun gamsu, danna OK.

Sakamakon tsarin saitin ku zai yi kama da haka:

Tun da muna buƙatar saita wani yanayin da zai ba mu damar canza launi mai cika zuwa kore don sel tare da ƙimar ƙasa da ko daidai. 3.45, sannan danna maɓallin sake Sabbin Dokoki (Ƙirƙiri Doka) kuma maimaita matakai na 3 zuwa 6, saita tsarin da ake so. A ƙasa akwai samfurin ƙa'idar tsara sharadi na biyu da muka ƙirƙira:

Lokacin da komai ya shirya - danna OK. Yanzu kuna da tebur da aka tsara da kyau wanda zai ba ku damar ganin matsakaicin da mafi ƙarancin farashin iskar gas a cikin jihohi daban-daban a kallo. Yayi kyau a gare su a can, a Texas! 🙂

tip: Hakazalika, zaku iya canza launin rubutu dangane da ƙimar tantanin halitta. Don yin wannan, kawai buɗe shafin font (Font) a cikin akwatin maganganu Tsarin Sel (Format cell) kamar yadda muka yi a mataki na 5 kuma zaɓi launin font ɗin da ake so.

Yadda ake saita launi tantanin halitta akai-akai dangane da ƙimar sa na yanzu

Da zarar an saita, launi na cika ba zai canza ba, komai yadda abin da ke cikin tantanin halitta ke canzawa a nan gaba.

matsala: Kuna son daidaita launin tantanin halitta bisa darajarsa na yanzu, kuma kuna son cika launi ya kasance iri ɗaya koda darajar tantanin halitta ta canza.

Yanke shawara: Nemo duk sel masu takamaiman ƙima (ko ƙima) ta amfani da kayan aiki Nemo Duk (Nemi Duk) sannan canza tsarin sel da aka samo ta amfani da akwatin maganganu Tsarin Sel (tsarin salula).

Wannan yana ɗaya daga cikin waɗannan ayyuka masu wuyar gaske waɗanda babu wani bayani a cikin fayilolin taimako na Excel, forums ko shafukan yanar gizo, kuma waɗanda babu mafita kai tsaye. Kuma wannan abu ne mai fahimta, tun da wannan aikin ba na al'ada ba ne. Kuma duk da haka, idan kuna buƙatar canza launin tantanin halitta na dindindin, wato, sau ɗaya kuma gaba ɗaya (ko har sai kun canza shi da hannu), bi waɗannan matakan.

Nemo kuma zaɓi duk sel waɗanda suka dace da yanayin da aka bayar

Abubuwa da yawa suna yiwuwa a nan, ya danganta da irin ƙimar da kuke nema.

Idan kuna son canza launin sel tare da takamaiman ƙima, misali, 50, 100 or 3.4 – sannan a kan tab Gida (Gida) a cikin sashin Editing (Editing) danna Nemo Zaɓi (Nemo kuma haskaka) > Find (Nemo).

Shigar da ƙimar da ake so kuma danna Nemo Duk (Nemo duka).

tip: A gefen dama na akwatin maganganu Nemi kuma Sauya (Nemo kuma Sauya) akwai maɓalli Zabuka (Zaɓuɓɓuka), ta danna waɗanda za ku sami dama ga yawancin saitunan bincike na ci gaba, kamar Match Case (harsashi mai hankali) da Daidaita duka abun cikin tantanin halitta (Dukkan cell). Kuna iya amfani da haruffan kati kamar alamar tambari (*) don dacewa da kowane nau'in haruffa, ko alamar tambaya (?) don dacewa da kowane hali ɗaya.

Game da misalin da ya gabata, idan muna buƙatar nemo duk farashin mai daga 3.7 to 3.799, to za mu saita ma'auni masu zuwa:

Yanzu danna kowane ɗayan abubuwan da aka samo a ƙasan akwatin maganganu Nemi kuma Sauya (Nemi kuma Sauya) kuma danna Ctrl + Adon haskaka duk abubuwan da aka samo. Bayan haka danna maɓallin kusa da (Kusa).

Wannan shine yadda zaku iya zaɓar duk sel tare da ƙimar da aka bayar (darajar) ta amfani da zaɓi Nemo Duk (Nemi Duk) a cikin Excel.

Koyaya, a zahiri, muna buƙatar nemo duk farashin mai da ya wuce $ 3.7. Abin takaici kayan aiki Nemi kuma Sauya (Nemo da Sauya) ba zai iya taimaka mana da wannan ba.

Canja Cika Launuka na Zaɓaɓɓun Kwayoyin Ta amfani da Akwatin Magana na Tsarin Kwayoyin

Yanzu kuna da duk sel waɗanda aka zaɓa tare da ƙimar da aka bayar, mun yi wannan kawai tare da kayan aiki Nemi kuma Sauya (Nemo ku maye gurbin). Duk abin da za ku yi shine saita launi mai cika don sel da aka zaɓa.

Bude akwatin maganganu Tsarin Sel (Format cell) ta kowace hanya guda 3:

• latsa Ctrl + 1.
• ta danna kowane tantanin halitta da aka zaɓa tare da maɓallin linzamin kwamfuta na dama kuma zaɓi abu daga menu na mahallin Tsarin Sel (tsarin salula).
• tab Gida (Gida) > Kwayoyin halitta. Kwayoyin halitta. (sel) > size (Format) > Tsarin Sel (tsarin salula).

Na gaba, daidaita zaɓuɓɓukan tsarawa yadda kuke so. A wannan lokacin za mu saita launi mai cika zuwa orange, kawai don canji 🙂

Idan kuna son canza launi mai cika kawai ba tare da taɓa sauran zaɓuɓɓukan tsarawa ba, zaku iya danna maɓallin kawai Cika launi (Cika launi) kuma zaɓi launi da kuke so.

Anan ga sakamakon canje-canjen tsarin mu a cikin Excel:

Ba kamar hanyar da ta gabata ba (tare da tsara yanayin sharadi), cikar launi da aka saita ta wannan hanyar ba za ta taɓa canza kanta ba tare da sanin ku ba, komai yadda ƙimar ke canzawa.

Canza launi mai cika don sel na musamman (ba komai, tare da kuskure a cikin dabara)

Kamar yadda yake a cikin misalin da ya gabata, zaku iya canza launi na takamaiman sel ta hanyoyi biyu: a tsaye da a tsaye.

Yi amfani da dabara don canza cika launi na sel na musamman a cikin Excel

Launin tantanin halitta zai canza ta atomatik dangane da ƙimar tantanin halitta.

Wataƙila za ku yi amfani da wannan hanyar magance matsalar a cikin kashi 99% na lokuta, wato, cikon sel zai canza daidai da yanayin da kuka ayyana.

Misali, bari mu sake daukar teburin farashin man fetur, amma a wannan karon za mu kara wasu jihohi biyu, mu mai da wasu kwayoyin halitta fanko. Yanzu duba yadda zaku iya nemo waɗannan sel marasa komai kuma ku canza launin cikarsu.

1. A kan Babba shafin Gida (Gida) a cikin sashin styles (Styles) danna Tsarin Yanayi (Tsarin Yanayi)> Sabbin Dokoki (Ƙirƙiri doka). Kamar dai a mataki na 2 na misali Yadda ake canza launin tantanin halitta a hankali bisa kimarsa.
2. A cikin akwatin maganganu Sabuwar Dokar Tsarin (Ƙirƙiri tsarin tsarawa) zaɓi zaɓi Yi amfani da dabara don tantance wane sel da format (Yi amfani da dabara don tantance waɗanne sel don tsarawa). Ci gaba a cikin filin Tsara ƙima inda wannan dabara ta kasance gaskiya (Format values ​​which the following form is true) shigar da ɗaya daga cikin dabarun:
   • don canza cikar sel mara komai

     =ISBLANK()

     =ЕПУСТО()

   • don canza inuwar sel masu ɗauke da dabara waɗanda ke dawo da kuskure

     =ISERROR()

     =ЕОШИБКА()

   Tun da muna son canza launi na sel mara kyau, muna buƙatar aikin farko. Shigar da shi, sannan sanya siginan kwamfuta tsakanin maƙallan kuma danna gunkin zaɓin kewayo a gefen dama na layin (ko rubuta kewayon da ake so da hannu):

   =ISBLANK(B2:H12)

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

   

3. latsa size (Tsarin), zaɓi launi mai cika da ake so akan shafin Cika (Cika), sannan danna OK. An bayar da cikakkun bayanai a mataki na 5 na misali "Yadda ake canza launin tantanin halitta bisa kimarsa." Misalin tsarin yanayin da kuka kafa zai yi kama da wani abu kamar haka:
4. Idan kuna farin ciki da launi, danna OK. Za ku ga yadda za a yi amfani da ƙa'idar halitta nan da nan a kan tebur.

Canja cika launi na sel na musamman a tsaye

Da zarar an saita, cikawar ba zai canza ba, ko da kuwa darajar tantanin halitta.

Idan kana son saita launi mai cike da dindindin don sel mara komai ko sel tare da dabaru waɗanda ke ɗauke da kurakurai, yi amfani da wannan hanyar:

1. Zaɓi tebur ko kewayon kuma danna F5don buɗe maganganun Ku Don (Jump), sannan danna maballin Special (Haske).
2. A cikin akwatin maganganu Je zuwa Special (Zaɓi ƙungiyar sel) duba zaɓin blanks (Blank Cells) don zaɓar duk sel mara komai.Idan kana son haskaka sel masu ɗauke da ƙididdiga tare da kurakurai, duba zaɓin dabarbari (Formula) > kurakurai (Kurakurai). Kamar yadda kuke gani a hoton da ke sama, akwai wasu saituna da yawa da kuke da su.
3. A ƙarshe, canza cika sel da aka zaɓa ko saita kowane zaɓin tsarawa ta amfani da akwatin maganganu Tsarin Sel (Format Cells), kamar yadda aka bayyana a Canza Cika Zaɓaɓɓun Sel.

Kar a manta cewa za a adana saitunan tsarawa da aka yi ta wannan hanyar koda lokacin da babu komai a cikin sel sun cika da ƙima ko kurakurai a cikin dabara. Yana da wuya a yi tunanin cewa wani yana iya buƙatar tafiya ta wannan hanyar, sai dai don dalilai na gwaji 🙂