Momwe mungasinthire mtundu wa mizere mu Excel malinga ndi momwe zinthu ziliri

M'nkhaniyi, muphunzira momwe mungasinthire msanga mzere wa mzere potengera mtengo wake mu spreadsheet. Nawa malangizo ndi njira zosiyanasiyana zamalemba ndi manambala mu chikalata.

M'mbuyomu, tidakambirana njira zosinthira mtundu wakumbuyo wa cell kutengera mawu kapena manambala momwemo. Malingaliro adzaperekedwanso apa momwe mungawunikire mizere yofunikira m'mitundu yaposachedwa ya Excel, kutengera zomwe zili mu cell imodzi. Kuphatikiza apo, apa mupeza zitsanzo zama fomula omwe amagwira ntchito mofanana pamapangidwe onse a cell.

Momwe mungasinthire mawonekedwe a mzere kutengera nambala mu cell inayake

Mwachitsanzo, muli ndi chikalata chotsegulidwa ndi tebulo lazamalonda ngati ili.

Tiyerekeze kuti muyenera kuwonetsa mizere mumithunzi yosiyana, kuyang'ana zomwe zalembedwa mu selo mu gawo la Qty, kuti mumvetse bwino zomwe zimapindulitsa kwambiri. Kuti mukwaniritse izi, muyenera kugwiritsa ntchito "Conditional Formatting". Tsatirani malangizo a sitepe ndi sitepe:

1. Sankhani maselo omwe mukufuna kupanga.
2. Pangani lamulo latsopano la masanjidwe podina chinthu choyenera pamenyu yankhani yomwe imawonekera mutadina batani la "Conditional Formatting" pa "Home".

1. Pambuyo pake, bokosi la zokambirana lidzawonekera pomwe muyenera kusankha "gwiritsani ntchito fomula kuti mudziwe ma cell opangidwa." Kenako, lembani njira iyi: =$C2>4 mu bokosi lili pansipa. Mwachilengedwe, mutha kuyika adilesi yanu yam'manja ndi zolemba zanu, komanso kusintha chizindikirocho ndi <kapena =. Kuphatikiza apo, ndikofunikira kuti musaiwale kuyika chizindikiro cha $ kutsogolo kwa cell reference kuti mukonze pokopera. Izi zimapangitsa kuti zikhale zotheka kumangirira mtundu wa mzere ku mtengo wa selo. Apo ayi, pokopera, adilesi "idzatuluka".
2. Dinani pa "Format" ndikusintha ku tabu yomaliza kuti mufotokozere zomwe mukufuna. Ngati simukukonda mithunzi yomwe ikuwonetsedwa ndi pulogalamuyi, mutha kudina "More Colours" ndikusankha mthunzi womwe mukufuna.
3. Mukamaliza ntchito zonse, muyenera dinani kawiri pa batani "Chabwino". Mutha kukhazikitsanso mitundu ina ya masanjidwe (mtundu wa mafonti kapena mtundu wina wa malire a cell) pama tabu ena awindo ili.
4. Pansi pa zenera pali chithunzithunzi gulu kumene inu mukhoza kuwona mmene selo adzaonekera pambuyo masanjidwe.
5. Ngati zonse zikugwirizana ndi inu, dinani batani "Chabwino" kuti mugwiritse ntchito zosintha. Chilichonse, mutatha kuchita izi, mizere yonse yomwe maselo ali ndi nambala yaikulu kuposa 4 idzakhala ya buluu.

Monga mukuonera, kusintha mtundu wa mzere kutengera mtengo wa selo linalake mmenemo si ntchito yovuta kwambiri. Mukhozanso kugwiritsa ntchito mafomu ovuta kwambiri kuti mukhale osinthika pogwiritsa ntchito masanjidwe okhazikika kuti mukwaniritse cholinga ichi.

Tsatirani malamulo angapo molingana ndi zomwe amafunikira

Chitsanzo cham'mbuyocho chikuwonetsa njira yogwiritsira ntchito lamulo limodzi lokhazikika, koma mungafune kugwiritsa ntchito angapo nthawi imodzi. Zotani zikatero? Mwachitsanzo, mutha kuwonjezera lamulo malinga ndi mizere yomwe ili ndi nambala 10 kapena kupitilira apo idzawonetsedwa mu pinki. Apa m'pofunika kuwonjezera kulemba chilinganizo =$C2>9, ndiyeno khazikitsani zinthu zofunika kwambiri kuti malamulo onse azitsatiridwa popanda kutsutsana.

1. Pa "Home" pagulu la "Masitayelo", muyenera kudina "Conditional Formatting" ndi menyu yomwe ikuwonekera, sankhani "Sinthani Malamulo" kumapeto kwenikweni kwa mndandanda.
2. Kenako, muyenera kuwonetsa malamulo onse okhudzana ndi chikalatachi. Kuti muchite izi, muyenera kupeza mndandanda pamwamba "Onetsani malamulo masanjidwe a", ndi kusankha "tsamba ili" pamenepo. Komanso, kudzera mumenyu iyi, mutha kukhazikitsa malamulo osinthira ma cell osankhidwa. Kwa ife, tiyenera kuyang'anira malamulo a chikalata chonse.
3. Kenako, muyenera kusankha lamulo lomwe mukufuna kugwiritsa ntchito poyamba ndikusunthira pamwamba pa mndandanda pogwiritsa ntchito mivi. Mudzapeza zotsatira zotere.
4. Pambuyo poika zofunikira, muyenera dinani "Chabwino", ndipo tiwona momwe mizere yofananira yasinthira mtundu wawo, malinga ndi zomwe zimayambira. Choyamba, pulogalamuyo idayang'ana kuti awone ngati mtengo mu gawo la Qty unali waukulu kuposa 10, ndipo ngati sichoncho, ngati chinali chachikulu kuposa 4.

Kusintha mtundu wa mzere wonse kutengera zomwe zalembedwa mu cell

Tangoganizani kuti mukugwira ntchito ndi spreadsheet, mumavutika kuti muzindikire zinthu zomwe zaperekedwa kale komanso zomwe sizinaperekedwe. Kapena mwina zina ndi zachikale. Kuti ntchitoyi ikhale yosavuta, mutha kuyesa kusankha mizere kutengera mawu omwe ali mu "Delivery" cell. Tiyerekeze kuti tiyenera kukhazikitsa malamulo otsatirawa:

1. Ngati kuyitanitsa kwachedwa pakatha masiku angapo, mtundu wakumbuyo wa mzere wofananira udzakhala lalanje.
2. Ngati katundu waperekedwa kale, mzere wofananawo umasanduka wobiriwira.
3. Ngati kutumizidwa kwa katundu kwachedwa, ndiye kuti malamulo ogwirizanawo ayenera kuunikira mofiira.

M’mawu osavuta, mtundu wa mzerewo udzasintha malinga ndi mmene dongosololo lilili.

Mwambiri, malingaliro a zochita zoperekedwa ndi kuyitanitsa nthawi yayitali adzakhala chimodzimodzi monga momwe tafotokozera pamwambapa. Ndikofunikira kulembera mafomu pawindo lokhazikika la masanjidwe =$E2=»Zaperekedwa» и =$E2=»Nyengo Yapitayi» motsatira. Ntchito yovuta kwambiri pamabizinesi omwe atha pakangopita masiku ochepa.

Monga tikuonera, chiwerengero cha masiku chikhoza kusiyana pakati pa mizere, momwemo ndondomeko yomwe ili pamwambayi singagwiritsidwe ntchito.

Kwa nkhaniyi pali ntchito =KUPEZA(“Iyenera kulowa”, $E2)>0, kumene:

• mkangano woyamba m'mabulaketi ndi mawu omwe ali m'maselo onse ofotokozedwa,
• ndipo mkangano wachiwiri ndi adilesi ya selo yomwe mtengo wake mukufuna kuyendamo.

M'Chingerezi amatchedwa = SEARCH. Amapangidwa kuti azifufuza ma cell omwe amagwirizana pang'ono ndi zomwe zalowetsedwa.

Langizo: Zoyimira> 0 mu fomula zikutanthauza kuti zilibe kanthu kuti funso lolowera likupezeka pati m'mawu am'manja.

Mwachitsanzo, gawo la "Kutumiza" likhoza kukhala ndi mawu akuti "Zachangu, Zikuyenera Maola 6" ndipo selo lofananira likhoza kusinthidwa bwino.

Ngati kuli kofunikira kugwiritsa ntchito malamulo opangira mizere pomwe selo lofunikira limayambira ndi mawu omwe mukufuna, ndiye kuti muyenera kulemba =1 mu fomula m'malo mwa> 0. 

Malamulo onsewa akhoza kulembedwa mu bokosi lolingana la zokambirana, monga momwe zilili pamwambapa. Chifukwa chake, mupeza zotsatirazi:

Momwe mungasinthire mtundu wa cell kutengera mtengo wa cell ina?

Monga momwe zilili ndi mzere, masitepe omwe ali pamwambawa angagwiritsidwe ntchito pa selo imodzi kapena mndandanda wamtengo wapatali. Muchitsanzo ichi, masanjidwe amangogwiritsidwa ntchito pamaselo omwe ali mugawo la "Order number":

Momwe mungagwiritsire ntchito zinthu zingapo pakusanjikiza

Ngati mukufuna kugwiritsa ntchito malamulo angapo okhazikika pazingwe, m'malo molemba malamulo osiyana, muyenera kupanga imodzi ndi mafomu. =OR or =I. Dzina loyamba limatanthauza “limodzi mwa malamulo amenewa ndi loona,” ndipo lachiwiri limatanthauza kuti “malamulo onse awiriwa ndi oona.”

M'malo mwathu, timalemba zotsatirazi:

=ИЛИ($F2=»Iziyenera Kutha M'masiku Amodzi», $F1=»Zikuyenera M'masiku Atatu»)

=ИЛИ($F2=»Iziyenera Kutha M'masiku Amodzi», $F5=»Zikuyenera M'masiku Atatu»)

Ndipo formula =I ingagwiritsidwe ntchito, mwachitsanzo, kuti muwone ngati nambala yomwe ili mugawo la Qty ili. wamkulu kuposa kapena wofanana ndi 5 ndi wocheperapo kapena wofanana ndi 10.

Wogwiritsa ntchito amatha kugwiritsa ntchito zinthu zingapo pamapangidwe.

Tsopano mukudziwa zomwe muyenera kuchita kuti musinthe mtundu wa mzere kutengera selo linalake, mumamvetsetsa momwe mungakhazikitsire zinthu zingapo ndikuziyika patsogolo, komanso momwe mungagwiritsire ntchito mafomu angapo nthawi imodzi. Kenako, muyenera kusonyeza malingaliro.