Njira 2 Zosinthira Mbiri mu Excel

M'nkhaniyi, muphunzira njira ziwiri zosavuta zosinthira ma cell kutengera zomwe ali m'mitundu yaposachedwa ya Excel. Mudzamvetsetsanso zomwe mungagwiritse ntchito kuti musinthe mthunzi wa maselo kapena ma cell omwe ma formula amalembedwa molakwika, kapena pomwe palibe chidziwitso.

Aliyense amadziwa kuti kusintha maziko a selo losavuta ndi njira yosavuta. Dinani pa "mtundu wakumbuyo". Koma bwanji ngati mukufuna kuwongolera mitundu potengera zomwe zili mu cell? Kodi ndingapange bwanji kuti izi zitheke? Chotsatira ndi mndandanda wazinthu zothandiza zomwe zidzakuthandizani kupeza njira yoyenera yochitira ntchito zonsezi.

Kusintha kwamtundu wakumbuyo kwa cell

Ntchito: Muli ndi tebulo kapena zikhalidwe, ndipo muyenera kusintha mtundu wakumbuyo wa ma cell kutengera nambala yomwe yalowetsedwa pamenepo. Muyeneranso kuonetsetsa kuti hue imayankha kusintha kwa makhalidwe.

Anakonza: Pa ntchitoyi, ntchito ya Excel ya "conditional formatting" imaperekedwa ku maselo amitundu okhala ndi manambala akulu kuposa X, ocheperapo kuposa Y, kapena pakati pa X ndi Y.

Tiyerekeze kuti muli ndi zinthu zingapo m'maiko osiyanasiyana ndi mitengo yawo, ndipo muyenera kudziwa kuti ndi ati omwe amawononga ndalama zoposa $3,7. Chifukwa chake, tidaganiza zowunikira zinthu zomwe zili pamwamba pa mtengowu mofiira. Ndipo maselo omwe ali ndi mtengo wofanana kapena wokulirapo, adaganiza zodetsa mumtundu wobiriwira.

Zindikirani: Chithunzicho chinatengedwa mu mtundu wa 2010 wa pulogalamuyi. Koma izi sizimakhudza chilichonse, chifukwa kutsatizana kwa zochita kumakhala kofanana, mosasamala kanthu za mtundu wanji - waposachedwa kapena ayi - womwe munthu amagwiritsa ntchito.

Ndiye izi ndi zomwe muyenera kuchita (gawo ndi sitepe):

1. Sankhani ma cell omwe mtunduwo uyenera kusinthidwa. Mwachitsanzo, osiyanasiyana $B$2:$H$10 (mayina a mgawo ndi gawo loyamba, lomwe limatchula mayina a boma, sizikuphatikizidwa mu chitsanzo).

2. Dinani "Pofikira" pagulu "style". Padzakhala chinthu "Conditional Formatting". Pamenepo muyenera kusankha “Lamulo Latsopano”. M'Chingerezi cha Excel, kutsatizana kwa masitepe kuli motere: "Home", "Styles Gulu", "Conditional Formatting> Lamulo Latsopano".

3. Pa zenera limene likutsegulidwa, chongani bokosilo "Sungani ma cell okha omwe ali" (“Sungani ma cell okhawo omwe ali” mu mtundu wa Chingerezi).

4. Pansi pa zenera ili pansi pa zolembedwa "Sungani maselo okhawo omwe akwaniritsa zotsatirazi" (Maselo amtundu okhawo ali nawo) mutha kugawa malamulo omwe masanjidwe azichitika. Tasankha mtundu wa mtengo womwe watchulidwa m'maselo, womwe uyenera kukhala wamkulu kuposa 3.7, monga mukuwonera pazithunzi: 

5. Kenako, dinani batani "Mtundu". Zenera lidzawoneka ndi malo osankha mtundu wakumbuyo kumanzere. Koma izi zisanachitike, muyenera kutsegula tabu Dzazani ("Dzazani"). Pankhaniyi, ndi wofiira. Pambuyo pake, dinani batani "Chabwino".

6. Kenako mudzabwerera ku zenera "New Formatting Rule", koma kale pansi pa zenerali mutha kuwoneratu momwe seloli lidzawonekera. Ngati zonse zili bwino, muyenera dinani batani "Chabwino".

Chifukwa chake, mupeza zinthu monga izi:

Kenako, tifunika kuwonjezera chikhalidwe chimodzi, ndiye kuti, kusintha maziko a ma cell okhala ndi zinthu zosakwana 3.45 kukhala zobiriwira. Kuti mugwire ntchitoyi, muyenera dinani "New Formatting Rule" ndi kubwereza masitepe pamwambapa, chikhalidwe chokhacho chiyenera kukhazikitsidwa ngati "zochepa kuposa, kapena zofanana" (m'Chingerezi "zocheperako kapena zofanana ndi", ndiyeno lembani mtengo wake. Pamapeto pake, muyenera dinani batani "Chabwino".

Tsopano tebulo lakonzedwa motere.

Imawonetsa mitengo yamafuta apamwamba kwambiri komanso otsika kwambiri m'maiko osiyanasiyana, ndipo mutha kudziwa nthawi yomweyo komwe kuli bwino kwambiri (ku Texas, kumene).

Malangizo: Ngati kuli kofunikira, mutha kugwiritsa ntchito njira yofananira yojambulira, kusintha osati maziko, koma mawonekedwe. Kuti muchite izi, pawindo la masanjidwe lomwe lidawonekera gawo lachisanu, muyenera kusankha tabu "Fonti" ndipo tsatirani zomwe zaperekedwa pawindo. Chilichonse chikuwoneka bwino, ndipo ngakhale wongoyamba akhoza kuzizindikira.

Zotsatira zake, mupeza tebulo ngati ili:

Momwe mungasungire mtundu wa cell kukhala wofanana ngakhale mtengo ukusintha?

Ntchito: Muyenera kukongoletsa maziko kuti asasinthe, ngakhale mazikowo asintha mtsogolo.

Anakonza: pezani ma cell onse okhala ndi nambala yeniyeni pogwiritsa ntchito ntchito ya Excel "Pezani zonse" "Pezani Zonse" kapena kuwonjezera "Sankhani ma cell apadera" ("Sankhani Maselo Apadera"), ndiyeno sinthani mtundu wa selo pogwiritsa ntchito ntchitoyi "Format cell" ("Maselo a Format").

Ichi ndi chimodzi mwazochitika zomwe sizikufotokozedwa m'buku la Excel, ndipo ngakhale pa intaneti, yankho la vutoli limapezeka kawirikawiri. Zomwe sizosadabwitsa, chifukwa ntchitoyi siili yokhazikika. Ngati mukufuna kusintha maziko mpaka kalekale kuti asasinthe mpaka atasinthidwa ndi wogwiritsa ntchito pulogalamuyi, muyenera kutsatira malangizo omwe ali pamwambapa.

Sankhani maselo onse omwe ali ndi vuto linalake

Pali njira zingapo zomwe zingatheke, kutengera mtundu wamtengo wapatali womwe ungapezeke.

Ngati mukufuna kusankha ma cell okhala ndi mtengo wapadera wokhala ndi maziko apadera, muyenera kupita ku tabu "Pofikira" ndi kusankha "Pezani ndi kusankha" - "Pezani".

Lowetsani zofunikira ndikudina “Pezani Zonse”.

Thandizeni: Mutha dinani batani “Zosankha” kumanja kugwiritsa ntchito zina zowonjezera: komwe mungasaka, momwe mungawonere, kulemekeza zilembo zazikulu ndi zazing'ono, ndi zina zotero. Mukhozanso kulemba zilembo zowonjezera, monga nyenyezi (*), kuti mupeze mizere yonse yomwe ili ndi izi. Ngati mugwiritsa ntchito chizindikiro, mutha kupeza chilembo chilichonse.

Muchitsanzo chathu cham'mbuyo, tikadafuna kupeza mitengo yonse yamafuta pakati pa $3,7 ndi $3,799, titha kusintha zomwe tikufuna.

Tsopano sankhani chilichonse chomwe pulogalamuyo idapeza pansi pabokosi la zokambirana ndikudina chimodzi mwazo. Pambuyo pake, dinani makiyi ophatikizira "Ctrl-A" kuti musankhe zotsatira zonse. Kenako, dinani batani "Tsegulani". 

Umu ndi momwe mungasankhire ma cell onse okhala ndi zinthu zenizeni pogwiritsa ntchito gawo la Pezani Zonse. Mu chitsanzo chathu, tifunika kupeza mitengo yonse yamafuta pamwamba pa $3,7, ndipo mwatsoka Excel samakulolani kuchita izi pogwiritsa ntchito Pezani ndi Kusintha.

"Mtsuko wa uchi" umawonekera apa chifukwa pali chida china chomwe chingathandize pa ntchito zovuta zoterezi. Amatchedwa Sankhani Maselo Apadera. Zowonjezera izi (zomwe ziyenera kukhazikitsidwa ku Excel padera) zithandiza:

• pezani zikhalidwe zonse mumtundu wina, mwachitsanzo pakati pa -1 ndi 45,
• pezani mtengo wokwanira kapena wochepera pagawo,
• pezani chingwe kapena mtundu,
• pezani ma cell ndi mtundu wakumbuyo ndi zina zambiri.

Pambuyo khazikitsa kuwonjezera-pa, kungodinanso batani "Sankhani ndi mtengo" ("Sankhani ndi Mtengo") ndiyeno yeretsani funso losaka pawindo la addon. Mu chitsanzo chathu, tikuyang'ana manambala oposa 3,7. Press "Sankhani" ("Sankhani"), ndipo pakamphindi mudzapeza zotsatira monga izi:

Ngati mukufuna kuwonjezera-on, mukhoza kukopera woyeserera pa kugwirizana.

Kusintha maziko a maselo osankhidwa kudzera pawindo la "Format Cells".

Tsopano, maselo onse omwe ali ndi mtengo wapatali atasankhidwa pogwiritsa ntchito njira imodzi yomwe tafotokozera pamwambapa, imakhalabe kuti iwonetsere mtundu wakumbuyo kwa iwo.

Kuti muchite izi, muyenera kutsegula zenera "Mawonekedwe a cell"mwa kukanikiza Ctrl + 1 kiyi (mungathenso kudina kumanja pamaselo osankhidwa ndikudina kumanzere pa chinthu cha "maselo amtundu") ndikusintha mawonekedwe omwe mukufuna.

Tidzasankha mthunzi wa lalanje, koma mutha kusankha china chilichonse.

Ngati mukufuna kusintha mtundu wakumbuyo popanda kusintha mawonekedwe ena, mutha kungodinanso "color fill" ndikusankha mtundu womwe umakuyenererani bwino.

Zotsatira zake ndi tebulo ngati ili:

Mosiyana ndi njira yapitayi, apa mtundu wa selo sudzasintha ngakhale mtengo utasinthidwa. Izi zingagwiritsidwe ntchito, mwachitsanzo, kufufuza kayendetsedwe ka katundu mu gulu linalake la mtengo. Mtengo wawo wasintha, koma mtundu wakhalabe womwewo.

Kusintha mtundu wakumbuyo kwa ma cell apadera (opanda kanthu kapena opanda zolakwika polemba fomula)

Mofanana ndi chitsanzo chapitachi, wogwiritsa ntchito amatha kusintha mtundu wakumbuyo wa maselo apadera m'njira ziwiri. Pali njira zosasinthika komanso zosinthika.

Kugwiritsa Ntchito Fomula Kuti Musinthe Mbiri Yakale

Apa mtundu wa selo udzasinthidwa zokha malinga ndi mtengo wake. Njirayi imathandiza ogwiritsa ntchito kwambiri ndipo ikufunika mu 99% yazochitika.

Mwachitsanzo, mungagwiritse ntchito tebulo lapitalo, koma tsopano maselo ena adzakhala opanda kanthu. Tiyenera kudziwa kuti ndi ati omwe alibe zowerengera ndikusintha mtundu wakumbuyo.

1. Pa tabu "Pofikira" muyenera dinani "Conditional Formatting" ->  “Lamulo Latsopano” (mofanana ndi gawo 2 la gawo loyamba "Sinthani mtundu wakumbuyo mwachangu".

2. Kenako, muyenera kusankha chinthucho "Gwiritsani ntchito formula kuti mudziwe ...".

3. Lowetsani chilinganizo =Palibe() (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 =Palibe(), ndiyeno ikani cholozera pakati pa zolembera ndikudina batani lomwe lili pafupi ndi gawo lolowetsa fomula. Pambuyo pakusintha kumeneku, ma cell angapo amayenera kusankhidwa pamanja. Kuphatikiza apo, mutha kudzifotokozera nokha, mwachitsanzo, =Zopanda kanthu(B2:H12).

4. Dinani pa batani la "Format" ndikusankha mtundu woyenerera wakumbuyo ndikuchita zonse monga momwe tafotokozera mu ndime 5 ya gawo la "Dynamic cell background color change", ndiyeno dinani "Chabwino". Kumeneko mungathe kuonanso mtundu wa selo udzakhala. Zenera lidzawoneka chonchi.

5. Ngati mumakonda maziko a selo, muyenera dinani batani la "Chabwino", ndipo zosintha zidzapangidwa nthawi yomweyo patebulo.

Kusintha kosasunthika kwa mtundu wakumbuyo wa ma cell apadera

Munthawi imeneyi, ikangoperekedwa, mtundu wakumbuyo upitilirabe kukhala momwemo, ngakhale selo lisintha bwanji.

Ngati mukufuna kusintha ma cell apadera (opanda kanthu kapena okhala ndi zolakwika), tsatirani malangizo awa:

1. Sankhani chikalata chanu kapena ma cell angapo ndikusindikiza F5 kuti mutsegule zenera la Go To, kenako dinani batani “Unikani”.
2. 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.
3. Ngati mukufuna kuwunikira ma cell omwe ali ndi mafomula omwe ali ndi zolakwika, muyenera kusankha chinthucho "Mitundu" ndipo siyani bokosi loyang'ana limodzi pafupi ndi mawu akuti "Zolakwika". Motere kuchokera pazithunzi pamwambapa, ma cell amatha kusankhidwa malinga ndi magawo aliwonse, ndipo makonda aliwonse omwe afotokozedwa amapezeka ngati kuli kofunikira.
4. Pomaliza, muyenera kusintha mtundu wakumbuyo wa maselo osankhidwa kapena kuwasintha mwanjira ina iliyonse. Kuti muchite izi, gwiritsani ntchito njira yomwe tafotokozayi.

Ingokumbukirani kuti kusintha kwa masanjidwe otere kupitilirabe ngakhale mutadzaza mipata kapena kusintha mtundu wapadera wa selo. Inde, sizingatheke kuti wina angafune kugwiritsa ntchito njirayi, koma pochita chilichonse chingachitike.

Kodi mungapeze bwanji zambiri kuchokera ku Excel?

Monga ogwiritsa ntchito kwambiri Microsoft Excel, muyenera kudziwa kuti ili ndi zambiri. Ena mwa iwo omwe timawadziwa ndi kuwakonda, pamene ena amakhalabe osadziwika kwa ogwiritsa ntchito wamba, ndipo ambiri olemba mabulogu akuyesera kuwunikira pang'ono pa iwo. Koma pali ntchito zofala zomwe aliyense wa ife ayenera kuchita, ndipo Excel siyambitsa zina kapena zida zosinthira zochita zovuta.

Ndipo njira yothetsera vutoli ndi zowonjezera (zowonjezera). Zina mwa izo zimagawidwa kwaulere, zina - ndalama. Pali zida zambiri zofanana zomwe zimatha kugwira ntchito zosiyanasiyana. Mwachitsanzo, pezani zobwerezedwa m'mafayilo awiri opanda mawonekedwe achinsinsi kapena ma macros.

Mukaphatikiza zida izi ndi magwiridwe antchito a Excel, mutha kupeza zotsatira zabwino kwambiri. Mwachitsanzo, mutha kudziwa kuti ndi mitengo iti yamafuta yomwe yasintha, ndikupeza zobwereza mufayilo chaka chatha.

Tikuwona kuti masanjidwe okhazikika ndi chida chothandiza chomwe chimakulolani kuti muzitha kugwira ntchito pamatebulo popanda luso linalake. Tsopano mukudziwa kudzaza maselo m'njira zingapo, kutengera zomwe zili mkati mwake. Tsopano zatsala kokha kuziyika muzochita. Zabwino zonse!