Okuqukethwe
Konke ukusesha kwakudala kanye nemisebenzi yokushintsha uhlobo VPR (VLOOKUP), I-GPR (HLOOKUP), OKUNINGI KUDANWE (IMESHI) futhi labo abafana nabo banesici esisodwa esibalulekile - basesha kusukela ekuqaleni kuze kube sekupheleni, okungukuthi kwesokunxele kuye kwesokudla noma phezulu kuya phansi kudatha yomthombo. Ngokushesha nje lapho kutholwa okufanayo kokuqala, ukusesha kuyama futhi kutholakala isenzeko sokuqala kuphela sesici esisidingayo.
Yini okufanele siyenze uma sidinga ukuthola hhayi okokuqala, kodwa isenzakalo sokugcina? Isibonelo, umsebenzi wokugcina weklayenti, inkokhelo yokugcina, i-oda yakamuva, njll.?
Indlela 1: Ukuthola Umugqa Wokugcina Nge-Array Formula
Uma ithebula langempela lingenayo ikholomu enedethi noma inombolo ye-serial yomugqa (i-oda, inkokhelo ...), khona-ke umsebenzi wethu, empeleni, uwukuthola umugqa wokugcina owanelisa umbandela onikeziwe. Lokhu kungenziwa ngefomula yamalungu afanayo alandelayo:
Lapha:
- Umsebenzi IF (UMA) ihlola wonke amaseli kukholamu ngayinye ngayinye ikhasimende futhi ibonise inombolo yomugqa uma iqukethe igama esilidingayo. Inombolo yomugqa eshidini sinikezwa umsebenzi LINE (UMUGQA), kodwa njengoba sidinga inombolo yomugqa etafuleni, kufanele futhi sikhiphe u-1, ngoba sinesihloko etafuleni.
- Bese umsebenzi MAX (MAX) ikhetha inani eliphakeme kusethi eyakhiwe yezinombolo zomugqa, okungukuthi inombolo yomugqa wakamuva kakhulu weklayenti.
- Umsebenzi INDEX (INDEX) ibuyisela okuqukethwe kweseli nenombolo yokugcina etholiwe kunoma iyiphi enye ikholomu yethebula edingekayo (Ikhodi ye-oda).
Konke lokhu kufanele kufakwe njenge uhlu lwefomula, okungukuthi:
- Ku-Office 365 enezibuyekezo zakamuva ezifakiwe nokusekelwa kwamalungu afanayo aguqukayo, ungamane ucindezele Faka.
- Kuzo zonke ezinye izinguqulo, ngemva kokufaka ifomula, kuzodingeka ucindezele isinqamuleli sekhibhodi Ctrl+Shift+Faka, okuzokwengeza ngokuzenzakalelayo ama-brace asontekile kuyo kubha yefomula.
Indlela yesi-2: Hlehlisa ukubheka ngomsebenzi omusha we-LOOKUP
Sengivele ngibhale isihloko eside ngevidiyo mayelana nesici esisha VIEW (XLOOKUP), evele kuzinguqulo zakamuva ze-Office ukuze ingene esikhundleni se-VLOOKUP endala (VLOOKUP). Ngosizo lwe-BROWSE, umsebenzi wethu uxazululwa ngokwesisekelo, ngoba. kulo msebenzi (ngokungafani ne-VLOOKUP), ungasetha ngokucacile isiqondiso sokusesha: phezulu-phansi noma phansi-phezulu - impikiswano yayo yokugcina (-1) inesibopho salokhu:
Indlela 3. Sesha iyunithi yezinhlamvu enedethi yakamuva
Uma kudatha yomthombo sinekholomu enenombolo ye-serial noma idethi edlala indima efanayo, khona-ke umsebenzi uyashintshwa - asidingi ukuthola umugqa wokugcina (ophansi) onomdlalo, kodwa umugqa onakamuva ( esiphezulu) usuku.
Sengixoxile ngokuningiliziwe ukuthi ungakwenza kanjani lokhu usebenzisa imisebenzi yakudala, futhi manje ake sizame ukusebenzisa amandla emisebenzi emisha yohlelo olushintshayo. Ukuze kube nobuhle obukhulu nokunethezeka, siphinde siguqule ithebula langempela libe ithebula “elihlakaniphile” sisebenzisa isinqamuleli sekhibhodi Ctrl+T noma imiyalo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula).
Ngosizo lwabo, lo "mbhangqwana obulalayo" uxazulula inkinga yethu kahle kakhulu:
Lapha:
- Sebenza kuqala FILTER (ISIHLUNGI) ukhetha kuphela leyo migqa etafuleni lethu lapho kukholamu ikhasimende – igama esilidingayo.
- Bese umsebenzi LIBANGA (Hlunga) ihlunga imigqa ekhethiwe ngedethi ngohlelo olwehlayo, ngedili yakamuva kakhulu phezulu.
- Umsebenzi INDEX (INDEX) ikhipha umugqa wokuqala, okungukuthi ibuyisela ukuhweba kokugcina esikudingayo.
- Futhi, ekugcineni, umsebenzi we-ISIHLUNGI wangaphandle ususa amakholomu engeziwe 1st and 3rd emiphumeleni (Ikhodi ye-oda и ikhasimende) futhi ishiya kuphela usuku nenani. Kulokhu, kusetshenziswa uxhaxha lwama-constants. {0;1;0;1}, esichaza ukuthi yimaphi amakholomu esiwafunayo (1) noma esingafuni (0) aboniswe ngawo.
Indlela yesi-4: Ukuthola Ukufana Kokugcina Embuzweni Wamandla
Nokho, ngenxa yokuphelela, ake sibheke isixazululo senkinga yethu yokuhlehla yosesho sisebenzisa isengezo Sombuzo Wamandla. Ngosizo lwakhe, yonke into ixazululwa ngokushesha futhi kahle.
1. Masiguqule ithebula lethu langempela libe “elihlakaniphile” sisebenzisa isinqamuleli sekhibhodi Ctrl+T noma imiyalo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula).
2. Ilayishe kokuthi Umbuzo Wamandla ngenkinobho Kusukela kuThebula/Ububanzi ithebhu Idatha (Idatha - Kusuka Kuthebula/Ububanzi).
3. Sihlunga (ngohlu oluya phansi lwesihlungi kunhlokweni) ithebula lethu ngohlelo olwehlayo lwedethi, ukuze okwenziwayo kwakamuva kube phezulu.
4… Kuthebhu Uguquko khetha iqembu Hlanganisa ngu (Guqula — Iqembu Nge) bese usetha ukuqoqwa ngamakhasimende, futhi njengomsebenzi wokuhlanganisa, khetha inketho Yonke imigqa (Yonke imigqa). Ungaqamba ikholomu entsha noma yini oyithandayo - isibonelo Yekuchumana.
Ngemva kokwenza iqembu, sizothola uhlu lwamagama ahlukile amaklayenti ethu nakukholomu Yekuchumana - amatafula anakho konke ukuthengiselana kwawo ngakunye, lapho ulayini wokuqala uzoba ukuthengiselana kwakamuva, okuyikhona esikudingayo:
5. Engeza ikholomu entsha ebaliwe ngenkinobho Ikholomu yangokwezifiso ithebhu Faka ikholamu (Engeza ikholomu - Engeza ikholomu yangokwezifiso)bese ufaka ifomula elandelayo:
Lapha Yekuchumana - lena yikholomu esithatha kuyo amatafula ngamakhasimende, futhi 0 {} inombolo yomugqa esifuna ukuyikhipha (izinombolo zomugqa kokuthi Umbuzo Wamandla ziqala ziro). Sithola ikholomu enamarekhodi (Qopha), lapho okufakiwe ngakunye kuwumugqa wokuqala kuthebula ngalinye:
Kusele ukukhulisa okuqukethwe kwawo wonke amarekhodi ngenkinobho enemicibisholo ekabili enhlokweni yekholomu Idili lokugcina ukukhetha amakholomu oyifunayo:
… bese ususa ikholomu engasadingeki Yekuchumana ngokuchofoza kwesokudla esihlokweni sayo - Susa amakholomu (Susa amakholomu).
Ngemva kokulayisha imiphumela eshidini nge Ikhaya — Vala bese ulayisha — Vala bese ulayisha ngaphakathi (Ekhaya — Vala & Layisha — Vala & Layisha ku…) sizothola ithebula elihle kangaka elinohlu lwemisebenzi yakamuva, njengoba besifuna:
Uma ushintsha idatha yomthombo, akumele ukhohlwe ukubuyekeza imiphumela ngokuchofoza kwesokudla kuyo - umyalo Buyekeza & Londoloza (Vuselela) noma isinqamuleli sekhibhodi Ctrl+Alt+F5.
- Umsebenzi we-LOOKUP uyinzalo ye-VLOOKUP
- Isetshenziswa kanjani imisebenzi yamalungu afanayo ashukumisayo amasha okuthi SORT, FILTER, kanye ne-UNIC
- Ukuthola iseli lokugcina elingangenalutho kumugqa noma ikholomu ngomsebenzi we-LOOKUP