Kupanga koyenera mu Excel - mwatsatanetsatane ndi zitsanzo

Ngati mukufuna kuwonetsa zambiri, ndiye kuti ndizosavuta kuchita izi pogwiritsa ntchito masanjidwe okhazikika. Wogwiritsa ntchito ntchitoyi amapulumutsa mphamvu ndi nthawi yambiri. Kuti mudziwe zofunikira, kuyang'ana mwamsanga kwa fayilo ndikokwanira.

Momwe mungapangire zolemba mu Excel

Mukhoza kupeza "Conditional Formatting" pa tabu yoyamba ya riboni mwa kupita ku gawo la "Masitayelo". 

Kenako, muyenera kupeza chithunzi cha muvi pang'ono kumanja ndi diso lanu, sunthani cholozera kwa icho. Kenako zoikamo zidzatsegulidwa, pomwe mutha kusintha magawo onse ofunikira. 

Kenako, muyenera kusankha woyendetsa woyenera kuti afanizire mtundu womwe mukufuna ndi nambala. Pali ofananitsa anayi - wamkulu kuposa, wocheperako, wofanana, ndi pakati. Iwo alembedwa mu malamulo menyu. 

Kenako, tidatchula manambala angapo mumtundu A1:A11.

Tiyeni tipange seti yoyenera ya data. Kenako timatsegula menyu ya "Conditional Formatting" ndikuyika zofunikira zosankhidwa za data. Kwa ife, mwachitsanzo, tidzasankha "Zambiri". 

Zenera lokhala ndi zosankha zambiri lidzatsegulidwa. Kumanzere kwa zenera pali gawo lomwe muyenera kufotokoza nambala 15. Pagawo lolondola, njira yowunikira zambiri ikuwonetsedwa, pokhapokha ngati ikugwirizana ndi zomwe zatchulidwa kale. Zotsatira zidzawonetsedwa nthawi yomweyo.

Kenako, malizitsani kasinthidwe ndi batani la OK.

Kodi kusanjidwa koyenera ndi chiyani?

Pamlingo wina, tinganene kuti ndi chida, titaphunzira kuti kugwira ntchito ndi Excel sikudzakhala kofanana. Izi zili choncho chifukwa zimapangitsa moyo kukhala wosavuta. M'malo mokhazikitsa pamanja masanjidwe a cell omwe amagwirizana ndi chikhalidwe china nthawi iliyonse, komanso kuyesa kudzifufuza nokha kuti mukutsatira izi, mumangofunika kukhazikitsa kamodzi, ndiyeno Excel ichita zonse yokha.

Mwachitsanzo, mutha kupanga ma cell onse omwe ali ndi nambala yopitilira 100 kukhala ofiira. Kapena, dziwani kuti ndi masiku angati omwe atsala kuti mubwezenso, ndiyeno kongoletsani ma cell obiriwira omwe tsiku lomaliza silinali kutali. 

Tiyeni titenge chitsanzo.

Gome ili likuwonetsa zinthu zomwe zili mgululi. Ili ndi mizati. monga chinthu, malonda apakati (amayezedwa mayunitsi pa sabata), katundu ndi masabata angati a mankhwalawa atsala.

Kuphatikiza apo, ntchito ya manejala wogula ndikuzindikira malowo, kuwonjezeredwa komwe kuli kofunikira. Kuti muchite izi, muyenera kuyang'ana gawo lachinayi kuchokera kumanzere, lomwe limalemba katundu wa katundu ndi sabata.

Tiyerekeze kuti mulingo wodziwira chomwe chimayambitsa mantha ndi osakwana masabata atatu owerengera. Izi zikusonyeza kuti tiyenera kukonzekera dongosolo. Ngati katundu wa katundu ali wosakwana milungu iwiri, ndiye kuti zikusonyeza kufunika mwamsanga kuyitanitsa. Ngati tebulo lili ndi maudindo ambiri, ndiye kuti kuyang'ana pamanja kuti ndi masabata angati omwe atsala ndizovuta. Ngakhale mutagwiritsa ntchito kufufuza. Ndipo tsopano tiyeni tiwone momwe tebulo likuwonekera, lomwe likuwonetseratu zinthu zomwe zimasoweka mofiira.

Ndipo ndithudi, ndikosavuta kuyenda. 

Zowona, chitsanzo ichi ndi chamaphunziro, chomwe chimakhala chosavuta poyerekeza ndi chithunzi chenicheni. Ndipo masekondi opulumutsidwa ndi mphindi zogwiritsa ntchito nthawi zonse matebulo otere amasintha kukhala maola. Tsopano ndizokwanira kuyang'ana patebulo kuti mumvetsetse kuti ndi zinthu ziti zomwe zikusoweka, komanso osagwiritsa ntchito maola ambiri ndikusanthula selo lililonse (ngati pali zinthu zambiri zamtunduwu).

Ngati pali katundu "wachikasu", ndiye kuti muyenera kuyamba kugula. Ngati malo ofanana ndi ofiira, ndiye kuti muyenera kuchita mwamsanga. 

Ndi mtengo wa selo lina

Tsopano tiyeni tione chitsanzo chothandiza chotsatirachi.

Tiyerekeze kuti tili ndi tebulo loterolo, ndipo tikuyang'anizana ndi ntchito yowunikira mizere yokhala ndi mfundo zina. 

Chifukwa chake, ngati polojekitiyo ikuchitikabe (ndiko kuti, sinamalizidwe, chifukwa chake idalembedwa ndi chilembo "P"), ndiye kuti maziko ake akhale ofiira. Ntchito zomwe zatsirizidwa zimasindikizidwa ndi zobiriwira.

Zochita zathu muzochitika izi zidzakhala motere:

1. Sankhani mitundu yosiyanasiyana. 
2. Dinani pa "Conditional Formatting" - "Pangani Rule".
3. Chonde dziwani kuti kwa ife, muyenera kugwiritsa ntchito fomula ngati lamulo. Pambuyo pake, timagwiritsa ntchito IFkuwunikira mizere yofananira. 

Kenako lembani mizere monga momwe yasonyezera pachithunzichi.

Zofunika! Zingwe ziyenera kufotokozedwa kwathunthu. Ngati titanena za selo, ndiye kuti pankhaniyi imasakanizidwa (ndi fixation column).

Momwemonso, lamulo limapangidwira ntchito zomwe sizinamalizidwe mpaka pano. 

Izi ndi momwe njira zathu zimawonekera.

Ndipo pomaliza, tili ndi database yotere chifukwa chake.

Zinthu zingapo

Kuti timveke bwino, tiyeni titenge mtundu A1:A11 patebulo kuti tiwonetse momwe, mu chitsanzo chenichenicho, mungagwiritse ntchito zinthu zingapo kuti mudziwe masanjidwe a ma cell.

Mikhalidwe yokhayo ili motere: ngati nambala yomwe ili muselo iposa mtengo wa 6, ndiye kuti imawonetsedwa mofiira. Ngati ndi wobiriwira, ndiye kuti mtunduwo ndi wobiriwira. Ndipo pomaliza, ziwerengero zazikulu, zopitilira 20, zidzawonetsedwa zachikasu. 

Pali njira zingapo momwe masanjidwe amagwiritsidwira ntchito molingana ndi malamulo angapo.

1. Njira 1. Timasankha mtundu wathu, pambuyo pake, muzosankha zowonetsera zokhazikika, sankhani lamulo losankha selo monga "Zambiri". Nambala 6 imalembedwa kumanzere, ndipo masanjidwe amaikidwa kumanja. Kwa ife, kudzazidwa kuyenera kukhala kofiira. Pambuyo pake, kuzungulira kumabwerezedwa kawiri, koma magawo ena akhazikitsidwa kale - oposa 10 ndi mtundu wobiriwira ndi oposa 20 ndi mtundu wachikasu, motero. Mudzapeza zotsatira zotere.
   

   

2. Njira 2. Pitani ku mndandanda wa zoikamo zazikulu za chida cha Excel "Conditional Formatting". Kumeneko timapeza menyu "Pangani Malamulo" ndikudina kumanzere pa chinthu ichi.
   

   

    Pambuyo pake, Chotsatira, sankhani chinthucho "Gwiritsani ntchito chilinganizo ..." (chowonetsedwa pachithunzichi ndi chimango chofiira) ndikuyika chikhalidwe choyamba. Pambuyo pake, dinani OK. Ndiyeno kuzungulirako kumabwerezedwa kwa zinthu zotsatila mofanana ndi momwe tafotokozera pamwambapa, koma ndondomekoyi imagwiritsidwa ntchito.

   

Ndikofunika kuganizira izi. Muchitsanzo chathu, maselo ena amafanana ndi njira zingapo nthawi imodzi. Excel imathetsa mkanganowu motere: lamulo lomwe lili pamwambapa likugwiritsidwa ntchito poyamba. 

Ndipo apa pali skrini kuti mumvetsetse bwino.

Mwachitsanzo, tili ndi nambala 24. Imakwaniritsa zofunikira zonse zitatu panthawi imodzi. Pankhaniyi, padzakhala kudzazidwa komwe kumakwaniritsa chikhalidwe choyamba, ngakhale kuti kumagwera kwambiri pansi pachitatu. Choncho, ngati kuli kofunika kuti manambala pamwamba pa 20 adzazidwe ndi chikasu, chikhalidwe chachitatu chiyenera kuikidwa pamalo oyamba. 

Mapangidwe a Tsiku Lovomerezeka

Tsopano tiyeni tiwone momwe masanjidwe okhazikika amagwirira ntchito ndi madeti. Tili ndi mitundu yabwino kwambiri.

Pachifukwa ichi, muyenera kukhazikitsa lamulo lokhazikika ngati "Date".

Pambuyo pake, bokosi la zokambirana limatsegulidwa ndi zinthu zonse. Mutha kudziwana nawo mwatsatanetsatane pachithunzichi (alembedwa kumanzere kwa chinsalu ngati mndandanda).

Timangofunika kusankha yoyenera ndikudina batani "Chabwino".

Tikuwona kuti masiku omwe amatchula sabata yatha pa nthawi yomwe mndandandawu udasindikizidwa adawonetsedwa mofiira. 

Kugwiritsa ntchito ma formula

Magulu a malamulo okhazikika amapangidwe ndi akulu kwambiri. Koma mikhalidwe ndi yosiyana, ndipo mndandanda wanthawi zonse sungakhale wokwanira. Pankhaniyi, mutha kupanga lamulo lanu la masanjidwe pogwiritsa ntchito fomula. 

Kuti muchite izi, dinani batani la "Pangani Rule" mumenyu yokhazikika, ndikusankha zomwe zikuwonetsedwa pazithunzi.

Mizere (mwa mtengo wa selo)

Tiyerekeze kuti tikufunika kuwunikira mzere womwe uli ndi selo yokhala ndi mtengo wake. Pachifukwa ichi, tifunika kuchita zofanana ndi zomwe tafotokozazi, koma zingwe. Ndiko kuti, mutha kusankha mtundu wamtundu womwe muyenera kutsata. 

Momwe mungapangire lamulo

Kuti mupange lamulo, muyenera kugwiritsa ntchito njira yoyenera mu gawo la "Conditional Formatting". Tiyeni tione ndondomeko yeniyeni ya zochita. 

Choyamba muyenera kupeza chinthu "Conditional Formatting" pa riboni pa "Home" tabu. Itha kuzindikirika ndi mawonekedwe amitundu yosiyanasiyana yamitundu yosiyanasiyana yokhala ndi chithunzi pafupi ndi icho ndi chizindikiro chowoloka chofanana. 

Pali batani "Pangani Rule". Mukhozanso kudina batani la "Sinthani malamulo", pambuyo pake bokosi la zokambirana lidzatsegulidwa, momwe mungathenso kupanga lamulo mwa kuwonekera batani lolingana pamwamba pa ngodya yakumanzere. 

Mukhozanso kuwona pawindo ili malamulo omwe agwiritsidwa ntchito kale.

Malamulo osankha ma cell

Chabwino, timayankhula kwambiri za momwe tingapangire malamulo, koma ndi chiyani? Lamulo losankha ma cell limatanthawuza zinthu zomwe zimaganiziridwa ndi pulogalamuyo kusankha momwe angapangire ma cell omwe amawakwanira. Mwachitsanzo, mikhalidwe ikhoza kukhala yokulirapo kuposa nambala inayake, kuchepera pa nambala inayake, chilinganizo, ndi zina zotero. Pali gulu lonse la iwo. Mutha kudziwana nawo ndikuyesa kugwiritsa ntchito kwawo "mu sandbox". 

Kupanga ma cell onse kutengera makonda awo

Munthu akhoza kudziyika yekha pazigawo: mzere wonse, chikalata chonse, kapena selo limodzi. Pulogalamuyi imangoyang'ana pa mtengo wa cell imodzi. Izi zimawonjezera kusinthasintha kwa ndondomekoyi. 

Pangani ma cell apadera kapena obwereza

Pali malamulo ambiri omwe amalumikizidwa mwachindunji mu pulogalamuyi. Mutha kudziwana nawo pamenyu ya "Conditional Formatting". Makamaka, kuti masanjidwe agwire ntchito pazofanana pazotsatira kapena zapadera, pali njira yapadera - "Zobwerezabwereza".

Mukasankha chinthu ichi, zenera zokhala ndi zosintha zimatsegulidwa, pomwe mungasankhenso zosiyana - sankhani zofunikira zokhazokha. 

Masanjidwe amitundu mum'munsimu komanso pamwamba pa avareji

Kupanga zikhalidwe pansipa kapena kupitilira avareji, palinso njira yapadera pamndandanda womwewo. Koma muyenera kusankha submenu ina - "Malamulo osankha zoyambira ndi zomaliza". 

Kupanga zoyambira komanso zomaliza zokha

Mu submenu yomweyi, ndizotheka kuwunikira zoyambira ndi zomaliza zokha ndi mtundu wapadera, mafonti ndi njira zina zosinthira. Malingana ndi muyezo, pali mwayi wosankha zinthu zoyamba ndi khumi zomaliza, komanso 10% ya chiwerengero chonse cha maselo omwe akuphatikizidwa mumtundu uwu. Koma wogwiritsa ntchito amatha kudziwa yekha kuchuluka kwa maselo omwe ayenera kusankha.

Kupanga ma cell okha omwe ali ndi zinthu zinazake

Kuti mupange ma cell okha omwe ali ndi zomwe zili, muyenera kusankha lamulo la Equal To kapena Text Contains formatting. Kusiyanitsa pakati pawo ndikuti muzochitika zoyamba, chingwecho chiyenera kufanana ndi chiwerengero chonse, ndipo chachiwiri, pang'ono chabe. 

Monga mukuwonera, kusanjikiza kokhazikika ndi gawo lantchito zambiri za pulogalamu ya Excel yomwe imapereka zabwino zambiri kwa munthu amene wazidziwa bwino. Tikudziwa kuti poyamba, zonsezi zingawoneke zovuta. Koma m'malo mwake, manja amakopeka nayo ikangowoneka kuti ikuwonetsa gawo linalake lofiira (kapena sinthani mwanjira ina iliyonse), kutengera zomwe zili kapena malinga ndi mulingo wina. Ntchitoyi ikuphatikizidwa muzofunikira za chidziwitso cha Excel, popanda ngakhale ntchito yamasewera yokhala ndi maspredishithi ndi ma database ndizosatheka.