Ishadi ngeseli elikhethiwe

Ake sithi wena kanye nami sidinga ukubona ngeso lengqondo idatha evela kuthebula elilandelayo ngamavelu okuthengiswa kwezimoto ngamazwe ahlukene ngo-2021 (idatha yangempela ethathwe lapha, noma kunjalo):

Njengoba inani lochungechunge lwedatha (amazwe) likhulu, ukuzama ukuwahlanganisa wonke kugrafu eyodwa ngesikhathi esisodwa kuzoholela “eshadini le-spaghetti” elibi noma ekwakhiweni kwamashadi ahlukene ochungechunge ngalunye, okuyinto enzima kakhulu.

Isixazululo esihle sale nkinga kungaba ukuhlela ishadi kuphela kudatha evela kumugqa wamanje, okungukuthi umugqa lapho iseli elisebenzayo litholakala khona:

Ukwenza lokhu kulula kakhulu - udinga kuphela amafomula amabili kanye ne-macro eyodwa encane emigqeni emi-3.

Isinyathelo 1. Inombolo yomugqa wamanje

Into yokuqala esiyidingayo ububanzi obuqanjwe igama obubala inombolo yomugqa eshidini lapho iseli yethu esebenzayo isikhona manje. Ivula kuthebhu Amafomula – Isiphathi Segama (Amafomula — Umphathi wegama), chofoza inkinobho Dala (Dala) bese ufaka isakhiwo esilandelayo lapho:

Isinyathelo sesi-2. Xhuma esihlokweni

Ukuze ubonise izwe elikhethiwe esihlokweni nakulejendi yeshadi, sidinga ukuthola ireferensi yeseli enegama layo (lezwe) ukusuka kukholomu yokuqala. Ukwenza lokhu, sakha enye indawo (isb Indawo = ishidi lamanje, hhayi Incwadi!) ububanzi obuqanjwe ngale fomula elandelayo:

Lapha, umsebenzi we-INDEX ukhetha ebangeni elinikeziwe (ikholomu A, lapho kulele khona amazwe ethu okusayina) iseli elinenombolo yomugqa esiyinqume ngaphambilini.

Isinyathelo sesi-3. Xhuma kudatha

Manje, ngendlela efanayo, ake sithole isixhumanisi kububanzi esinayo yonke idatha yokuthengisa kusukela kumugqa wamanje, lapho iseli elisebenzayo manje litholakala khona. Dala obunye ububanzi obuqanjwe ngefomula elandelayo:

Lapha, i-agumenti yesithathu, enguziro, yenza i-INDEX ingabuyiseli inani elilodwa, kodwa wonke umugqa njengomphumela.

Isinyathelo 4. Ukufaka Izixhumanisi Eshadini

Manje khetha unhlokweni wethebula nomugqa wokuqala onedatha (ububanzi) bese wakhe ishadi elisekelwe kuwo usebenzisa Faka – Amashadi (Faka — Amashadi). Uma ukhetha umugqa onedatha eshadini, umsebenzi uzoboniswa kubha yefomula UMUGQA (UCHUNGECHUNGE) kuwumsebenzi okhethekile i-Excel ewusebenzisa ngokuzenzakalelayo lapho udala noma yiliphi ishadi ukuze libhekisele kudatha yangempela namalebula:

Masimiseleni ngokucophelela izimpikiswano zokuqala (isiginesha) nezesithathu (zedatha) kulo msebenzi ngamagama wobubanzi bethu ukusuka ezinyathelweni 2 no-3:

Ishadi lizoqala ukubonisa idatha yokuthengisa kusukela kumugqa wamanje.

Isinyathelo 5. Ukubala kabusha Imakhro

Ukuthinta kokugcina kusasele. I-Microsoft Excel ibala kabusha amafomula kuphela lapho idatha eseshidini ishintsha noma lapho ukhiye ucindezelwa F9, futhi sifuna ukubala kabusha kwenzeke lapho okukhethiwe kushintsha, okungukuthi lapho iseli esebenzayo ihanjiswa ngaphesheya kweshidi. Ukwenza lokhu, sidinga ukwengeza i-macro elula encwadini yethu yokusebenza.

Chofoza kwesokudla kuthebhu yeshidi ledatha bese ukhetha umyalo Umthombo (Ikhodi yomthombo). Ewindini elivulayo, faka ikhodi yesibambi esikhulu somcimbi wokushintsha okukhethiwe:

Njengoba ungacabanga kalula, konke ekwenzayo ukuqala ukubala kabusha kweshidi noma nini lapho kushintsha indawo yeseli esebenzayo.

Isinyathelo 6. Ukugqamisa Umugqa Wamanje

Ukuze uthole ukucaca, ungakwazi futhi ukwengeza umthetho wokufometha onemibandela ukuze ugqamise izwe eliboniswa eshadini okwamanje. Ukuze wenze lokhu, khetha ithebula bese ukhetha Ikhaya — Ukufometha Okunemibandela — Dala Umthetho — Sebenzisa Ifomula Ukuze Unqume Amaseli Okufanele Afomethwe (Ikhaya - Ukufometha okunemibandela - Umthetho omusha - Sebenzisa ifomula ukuze unqume ukuthi yimaphi amaseli ozofometha):

Lapha ifomula ihlola iseli ngayinye etafuleni ukuthi inombolo yomugqa wayo ifana nenombolo egcinwe ku-variable ye-TekRow, futhi uma kukhona okufanayo, khona-ke ukugcwaliswa ngombala okhethiwe kuyaqalwa.

Yilokho - elula futhi enhle, akunjalo?

amanothi

• Kumatafula amakhulu, bonke lobu buhle bunganciphisa ijubane - ukufometha okunemibandela kuyinto edinga kakhulu insiza, futhi ukubala kabusha kokukhethwa ngakunye kungase kube nzima.
• Ukuze uvimbele idatha ekunyamalale eshadini lapho iseli likhethwe ngephutha ngenhla noma ngaphansi kwethebula, ungakwazi ukwengeza isheke elengeziwe egameni le-TekRow usebenzisa imisebenzi ye-IF efakwe efomini:

  =IF(ICELL(“row”)<4,IF(CELL("row")>4,CELL(“row”)))
  

• Igqamisa amakholomu ashiwo eshadini
• Indlela yokudala ishadi elisebenzisanayo ku-Excel
• Ukukhetha Ukudidiyela