Ungawakhipha kanjani amaseli ayimpinda eshidini le-Excel

Sithola iqoqo ledatha, futhi ngesikhathi esithile siyabona ukuthi enye iyimpinda.

Kufanele sihlaziye idatha, sazi ukuthi ukuphindaphinda amaphutha.

Kulesi sihloko, sizobona izindlela ezintathu zokuqeda amaseli ayimpinda.

Susa amaseli ayimpinda ku-Excel

Ngezindlela ezichazwe ngezansi, sisebenzisa isipredishithi esilula esingezansi, esinohlu lwamagama kukholamu A.

Siqala ngokubonisa indlela yokusebenzisa umyalo we-Excel othi Khipha Izimpinda ukuze ukhiphe izimpinda, bese sikhombisa indlela yokusebenzisa Isihlungi Esithuthukile se-Excel ukufeza lo msebenzi. Ekugcineni, sibonisa indlela yokususa izimpinda usebenzisa umsebenzi Countif kwe-Excel .

Susa izimpinda usebenzisa umyalo we-Excel othi Khipha Izimpinda

Umyalo Susa izimpinda itholakala eqenjini elithi "Amathuluzi Edatha", ngaphakathi kwethebhu Dati yeribhoni ye-Excel.

Ukuze ususe amaseli ayimpinda usebenzisa lo myalo:

• Khetha noma iyiphi iseli ngaphakathi kwedathasethi ofuna ukususa kuyo izimpinda bese uchofoza inkinobho Susa izimpinda.
• Uzokwethulwa ngebhokisi elithi "Susa Izimpinda" eliboniswe ngezansi:

• Le ngxoxo ikuvumela ukuthi ukhethe ukuthi imaphi amakholomu kudathasethi yakho ofuna ukuwahlola okufakiwe okuyimpinda. Esibonelweni sespredishithi esingenhla, sinekholomu eyodwa kuphela yedatha (inkambu ethi "Igama"). Ngakho-ke sishiya inkambu ethi "Igama" ekhethiwe ebhokisini lengxoxo.
• Ngemva kokuqinisekisa ukuthi izinkambu ezidingekayo zikhethiwe ebhokisini lengxoxo, chofoza OK. I-Excel izobe isisusa imigqa eyimpinda, njengoba kudingekile, futhi ikwethule umlayezo, ikwazisa ngenani lamarekhodi asusiwe kanye nenani lamarekhodi ahlukile asele (bona ngezansi).

• Ngaphezulu komlayezo kukhona nethebula eliwumphumela wokususwa. Njengoba kuceliwe, impinda yeseli A11 (equkethe ukuvela kwesibili kwegama elithi “Dan BROWN”) ikhishiwe.

Qaphela ukuthi umyalo we-Excel othi Khipha Izimpinda ungasetshenziswa kumadathasethi anamakholomu amaningi. Isibonelo salokhu sinikezwe ekhasini elithi Susa Imigqa Eyimpinda.

Susa izimpinda usebenzisa isihlungi esithuthukisiwe se-Excel

Isihlungi Esithuthukisiwe se-Excel sinenketho ekuvumela ukuthi uhlunge amarekhodi ahlukile kusipredishithi futhi ukopishele uhlu oluhlungiwe oluwumphumela endaweni entsha.

Lokhu kunikeza uhlu oluqukethe ukwenzeka kokuqala kwerekhodi, kodwa aluqukethe ukwenzeka okwengeziwe.

Ukuze ususe izimpinda usebenzisa isihlungi esithuthukisiwe:

• Khetha ikholomu noma amakholomu ozowahlunga (ikholomu A kuspredishithi esingenhla);(Okunye, uma ukhetha noma iyiphi iseli ngaphakathi kwedathasethi yamanje, i-Excel izokhetha ngokuzenzakalelayo lonke uhla lwedatha lapho unika amandla isihlungi esithuthukisiwe.)
• Khetha inketho Yesihlungi Esithuthukile se-Excel kuthebhu Yedatha phezulu ebhukwini lakho le-Excel(noma ku-Excel 2003, lokhu inketho itholakala kumenyu Idatha → Hlunga ).

• Uzokwethulwa ngebhokisi lengxoxo elibonisa izinketho zesihlungi esithuthukisiwe se-Excel (bona ngezansi). Ngaphakathi kwaleli bhokisi lengxoxo:

Isipredishithi esiwumphumela, esinohlu olusha lwedatha kukholomu C, siboniswa ngenhla.

Ungase uqaphele ukuthi inani eliyimpinda elithi “Dan BROWN” lisusiwe ohlwini.

Manje usungakwazi ukususa amakholomu kwesokunxele sohlu lwakho olusha lwedatha (amakholomu AB kusipredishithi esiyisibonelo) ukuze ubuyele kufomethi yokuqala yesipredishithi.

Susa izimpinda usebenzisa umsebenzi we-Excel's Countif

Le ndlela izosebenza kuphela uma okuqukethwe kweseli kunezinhlamvu ezingaphansi kuka-256 ngobude, njengoba imisebenzi ye-Excel ingakwazi ukuphatha izintambo zombhalo ezinde.

Isinyathelo 1: Gqamisa izimpinda

Enye indlela yokukhipha izimpinda kuhlu lwamaseli e-Excel ukusebenzisa i- umsebenzi Countif kwe-Excel .

Ukubonisa lokhu, sizophinda sisebenzise isibonelo esilula sespredishithi, esinohlu lwamagama kukholamu A.

Ukuthola noma yiziphi izimpinda ohlwini lwamagama, sifaka umsebenzi Countif kukholamu B yesipredishithi (bheka ngezansi). Lo msebenzi ubonisa inani lezenzeko zegama ngalinye kuze kufike emugqeni wamanje.

Njengoba kuboniswe kubha yefomula yesipredishithi ngenhla, ifomethi yomsebenzi Ukubala kuseli B2 kunjalo :=COUNTIF( $A$2:$A$11, A2 )

Sicela uqaphele ukuthi lesi sici sisebenzisa inhlanganisela ye amareferensi amaseli aphelele futhi ahlobene. Ngenxa yale nhlanganisela yezitayela zokubhekisela, lapho ifomula ikopishwa kukholamu B, iba,

=COUNTIF( $A$2:$A$11, A2 ) =COUNTIF( $A$2:$A$11, A3 ) =COUNTIF( $A$2:$A$11, A4 ) njll

Ngakho-ke, ifomula kuseli B4 ibuyisela inani elingu-1 lokuvela kokuqala kochungechunge lombhalo oluthi “Laura BROWN,” kodwa ifomula kuseli B7 ibuyisela inani elingu-1 lokuvela kwesibili kwalolu chungechunge lombhalo.

Isinyathelo sesi-2: Susa imigqa eyimpinda

Manje njengoba sisebenzise umsebenzi we-Excel Countif Ukuze sigqamise izimpinda kukholomu A yesibonelo sesipredishithi, sidinga ukususa imigqa lapho isibalo singaphezu ko-1.

Kuspredishithi esilula esiyisibonelo, kulula ukubona nokususa umugqa owodwa oyimpinda. Nokho, uma unezimpinda eziningi, ungase ukuthole kushesha ukusebenzisa isihlungi esizenzakalelayo se-Excel ukususa yonke imigqa eyimpinda ngesikhathi esisodwa. Sebenzisa isihlungi esizenzakalelayo se-Excel ukususa imigqa eyimpinda

Izinyathelo ezilandelayo zibonisa indlela yokususa izimpinda eziningi ngesikhathi esisodwa (ngemuva kokuthi zigqanyiswe kusetshenziswa ifayela le- Countif):

• Khetha ikholomu equkethe umsebenzi Countif (ikholomu B kuspredishithi esiyisibonelo);
• Chofoza inkinobho isihlungi kuthebhu Dati yesipredishithi ukuze usebenzise isihlungi esizenzakalelayo se-Excel kudatha yakho;

• Sebenzisa isihlungi phezulu kwekholomu B ukuze ukhethe imigqa engalingani no-1. Okungukuthi, chofoza isihlungi futhi, ohlwini lwamanani, ungakhethi inani elingu-1;
• Uzosala nespredishithi lapho ukwenzeka kokuqala kwevelu ngayinye kufihlwe khona. Okusho ukuthi, amanani ayimpinda kuphela aboniswayo. Ungasusa le migqa ngokuyigqamisa, bese uchofoza kwesokudla bese ukhetha Susa imivimbo .

• Susa isihlungi futhi uzogcina usunesipredishithi, lapho okuphindeke kabili kususiwe khona. Manje usungakwazi ukususa ikholomu equkethe umsebenzi Countif ukuze ubuyele kufomethi yokuqala yesipredishithi.

Ercole Palmeri