Contents
Duk aikin bincike na gargajiya da nau'in maye gurbin VPR (VLOOKUP), GPR (HLOOKUP), MORE BAYYANA (MATSAYI) kuma irin su suna da sifa mai mahimmanci guda ɗaya - suna bincike daga farko zuwa ƙarshe, watau hagu-zuwa-dama ko sama-zuwa ƙasa a cikin bayanan tushen. Da zaran an sami wasan farko na daidaitawa, binciken yana tsayawa kuma kawai abin da ya faru na farko na sinadarin da muke bukata zai samu.
Menene za mu yi idan muna buƙatar samun ba na farko ba, amma abin da ya faru na ƙarshe? Misali, ma'amala ta ƙarshe don abokin ciniki, biyan kuɗi na ƙarshe, odar kwanan nan, da sauransu?
Hanyar 1: Nemo Layi na Ƙarshe tare da Tsarin Tsara
Idan tebur na asali ba shi da ginshiƙi tare da kwanan wata ko lambar jeri (oda, biyan kuɗi ...), to aikinmu shine, a zahiri, don nemo layin ƙarshe wanda ya gamsar da yanayin da aka bayar. Ana iya yin wannan tare da dabarar tsararru mai zuwa:
Nan:
- aiki IF (IF) yana duba duk sel a cikin ginshiƙi ɗaya bayan ɗaya Abokin ciniki kuma yana nuna lambar layin idan ya ƙunshi sunan da muke buƙata. An ba mu lambar layin da ke kan takardar ta aikin LINE (ROW), amma tunda muna buƙatar lambar layi a cikin tebur, kuma dole ne mu cire 1, saboda muna da taken a cikin tebur.
- Sai aikin MAX (MAX) yana zaɓar mafi girman ƙima daga kafaffen saitin lambobin jere, watau adadin layin kwanan nan na abokin ciniki.
- aiki INDEX (INDEX) yana dawo da abinda ke cikin tantanin halitta tare da samin lamba ta ƙarshe daga kowane ginshiƙin tebur da ake buƙata (Lambar oda).
Duk waɗannan dole ne a shigar dasu azaman dabarar tsararru, watau:
- A cikin Office 365 tare da sabbin sabuntawa da aka shigar da goyan bayan tsararraki masu ƙarfi, zaku iya danna kawai Shigar.
- A duk sauran nau'ikan, bayan shigar da dabarar, zaku danna gajeriyar hanyar madannai Ctrl+Motsi+Shigar, wanda zai ƙara maƙarƙashiya ta atomatik zuwa gare shi a cikin mashaya dabara.
Hanyar 2: Komawa bincike tare da sabon aikin LOOKUP
Na riga na rubuta dogon labari tare da bidiyo game da sabon fasali Duba (XLOOKUP), wanda ya bayyana a cikin sabbin nau'ikan Office don maye gurbin tsohon VLOOKUP (VLOOKUP). Tare da taimakon BROWSE, aikinmu yana warwarewa sosai, saboda. don wannan aikin (ba kamar VLOOKUP ba), zaku iya saita hanyar bincike a sarari: sama-ƙasa ko ƙasa - hujjarsa ta ƙarshe (-1) ita ce ke da alhakin wannan:
Hanyar 3. Nemo kirtani tare da sabuwar kwanan wata
Idan a cikin bayanan tushen muna da ginshiƙi tare da lambar serial ko kwanan wata da ke taka rawa iri ɗaya, to aikin yana canzawa - muna buƙatar samun layin ƙarshe (mafi ƙasƙanci) tare da wasa, amma layin tare da sabon ( iyakar) kwanan wata.
Na riga na tattauna dalla-dalla yadda ake yin wannan ta amfani da ayyuka na yau da kullun, kuma yanzu bari mu yi ƙoƙarin amfani da ikon sabbin ayyukan tsararru. Don ƙarin kyau da dacewa, muna kuma canza tebur na asali zuwa teburin “smart” ta amfani da gajeriyar hanyar madannai. Ctrl+T ko umarni Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur).
Tare da taimakonsu, wannan "ma'aurata masu kisa" suna magance matsalarmu da kyau:
Nan:
- Aiki na farko tace (TACE) yana zaɓar waɗancan layuka ne kawai daga teburin mu inda a cikin ginshiƙi Abokin ciniki – sunan da muke bukata.
- Sai aikin sa (SORT) yana tsara layuka da aka zaɓa ta kwanan wata a cikin tsari mai saukowa, tare da yarjejeniyar kwanan nan a saman.
- aiki INDEX (INDEX) ya cire layin farko, watau ya dawo da ciniki na ƙarshe da muke buƙata.
- Kuma, a ƙarshe, aikin FILTER na waje yana cire ƙarin ginshiƙai na 1st da 3rd daga sakamakon (Lambar oda и Abokin ciniki) kuma ya bar kwanan wata da adadin kawai. Don wannan, ana amfani da tsararru na dindindin. {0;1;0;1}, ayyana waɗanne ginshiƙai muke so (1) ko ba sa so (0) nunawa.
Hanyar 4: Nemo Matsala ta Ƙarshe a cikin Tambayar Wuta
To, don cikar cikawa, bari mu duba hanyar magance matsalar binciken mu ta baya ta amfani da add-in Query Query. Tare da taimakonta, komai yana warwarewa cikin sauri da kyau.
1. Bari mu canza teburin mu na asali zuwa “mai wayo” ta amfani da gajeriyar hanyar madannai Ctrl+T ko umarni Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur).
2. Loda shi a cikin Tambayoyin Wuta tare da maɓallin Daga Tebura/Range tab data (Bayanai - Daga Tebura/Range).
3. Muna tsarawa (ta cikin jerin zaɓuka na tacewa a cikin taken) tebur ɗinmu a cikin tsarin kwanan wata, ta yadda mafi yawan ma'amaloli sun kasance a saman.
4… A cikin tab Sake Kama zabi tawagar Rukuni ta (Canza - Rukuni By) kuma saita ƙungiyoyi ta abokan ciniki, kuma azaman aikin haɗaka, zaɓi zaɓi Duk layi (Duk layuka). Kuna iya suna sabon shafi duk abin da kuke so - alal misali details.
Bayan haɗawa, za mu sami jerin sunayen musamman na abokan cinikinmu kuma a cikin ginshiƙi details - tebur tare da duk ma'amaloli na kowane ɗayansu, inda layin farko zai zama sabon ma'amala, wanda shine abin da muke buƙata:
5. Ƙara sabon shafi mai ƙididdigewa tare da maɓallin Rukunin al'ada tab Sanya shafi (Ƙara shafi - Ƙara ginshiƙi na al'ada)kuma shigar da dabara mai zuwa:
nan details - wannan shi ne ginshiƙi daga abin da muka dauki tebur ta abokan ciniki, kuma 0 {} shine adadin layin da muke son cirewa (lambar lambobi a cikin Query Query yana farawa daga sifili). Muna samun ginshiƙi tare da rikodin (Record), inda kowace shigarwa ta kasance layin farko daga kowane tebur:
Ya rage don faɗaɗa abubuwan da ke cikin duk bayanan tare da maɓallin tare da kibau biyu a cikin rubutun shafi Yarjejeniyar ta ƙarshe zabar ginshiƙan da ake so:
… sannan kuma share ginshiƙin da ba a buƙata details ta danna dama akan takensa - Cire ginshiƙai (Cire ginshiƙai).
Bayan loda sakamakon zuwa takardar ta hanyar Gida - Rufe kuma lodi - Rufe kuma shigar da ciki (Gida - Rufe & Loda - Kusa & Loda zuwa…) za mu sami irin wannan tebur mai kyau tare da jerin ma'amaloli na baya-bayan nan, kamar yadda muke so:
Lokacin da kuka canza bayanan tushen, kada ku manta da sabunta sakamakon ta danna-dama akan su - umarnin Sabunta & Ajiye (Sake sabuntawa) ko gajeriyar hanyar keyboard Ctrl+alt+F5.
- Aikin LOOKUP zuriyar VLOOKUP ne
- Yadda ake amfani da sabbin ayyukan tsararru mai ƙarfi SORT, FILTER, da UNIC
- Nemo tantanin halitta mara sarari na ƙarshe a jere ko shafi tare da aikin LOOKUP