Okuqukethwe
Uma okungenani ujwayelene nomsebenzi VPR (VLOOKUP) (uma kungenjalo, qala uqalise lapha), khona-ke kufanele uqonde ukuthi lokhu neminye imisebenzi efana nayo (BUKA, INHLOKO kanye nokuthi SESHA, KHETHA, njll.) njalo unikeza njengomphumela Inani – inombolo, umbhalo noma usuku esilufunayo kuthebula elinikeziwe.
Kodwa kuthiwani uma, esikhundleni senani, sifuna ukuthola isixhumanisi esibukhoma, ngokuchofoza lapho esingagxuma khona ngaso leso sikhathi siye kumeshi etholiwe kwelinye ithebula ukuze sikubuke kumongo ojwayelekile?
Ake sithi sinetafula elikhulu loku-oda lamakhasimende ethu njengokufaka. Ukuze kube lula (yize lokhu kungenasidingo), ngiguqule ithebula laba isinqamuleli sekhibhodi "ehlakaniphile" esiguqukayo. Ctrl+T futhi wanikeza kuthebhu umakhi (Umklamo) egameni lakhe tabOrders:
Eshidini elihlukile Kuhlanganisiwe Ngakhe ithebula le-pivot (yize kungadingeki ukuthi libe ncamashi ithebula le-pivot - noma iliphi ithebula lifaneleka ngokomgomo), lapho, ngokuya ngedatha yokuqala, amandla okuthengisa ngezinyanga eklayenti ngalinye kubalwa:
Ake sengeze ikholomu kuthebula le-oda elinefomula ebheka igama lekhasimende nge-oda lamanje eshidini. Kuhlanganisiwe. Kulokhu sisebenzisa inqwaba yemisebenzi ye-classical INDEX (INDEX) и OKUNINGI KUDANWE (IMESHI):
Manje ake sigoqe ifomula yethu ibe umsebenzi ISELULEKILE (ISELI), esizocela ukuveza ikheli leseli elitholiwe:
Futhi ekugcineni, sibeka konke okuye kwaphenduka umsebenzi I-HYPERLINK (HYPERLINK), okuthi ku-Microsoft Excel kungakha isixhumanisi esibukhoma sendlela ethile (ikheli). Okuwukuphela kwento engabonakali ukuthi kuzodingeka unamathisele uphawu lwe-hashi (#) ekuqaleni ekhelini elitholiwe ukuze isixhumanisi sibonwe kahle yi-Excel njengesangaphakathi (kusuka eshidini kuya eshidini):
Manje, uma uchofoza kunoma yiziphi izixhumanisi, sizogxumela ngokushesha esitokisini ngegama lenkampani eshidini elinethebula le-pivot.
Ukuze siyenze ibe yinhle ngempela, ake sithuthukise ifomula yethu kancane ukuze inguquko ingenzeki egameni leklayenti, kodwa ibe enanini lenombolo ethile ncamashi kukholamu yenyanga lapho i-oda elihambisanayo seliqediwe. Ukuze wenze lokhu, kufanele sikhumbule ukuthi umsebenzi INDEX (INDEX) ku-Excel iguquguquka kakhulu futhi ingasetshenziswa, phakathi kwezinye izinto, ngefomethi:
=INDEX( XNUMXD_ububanzi; Inombolo_yomugqa; Inombolo_yekholomu )
Okusho ukuthi, njengempikiswano yokuqala, asikwazi ukucacisa ikholomu enamagama ezinkampani ku-pivot, kodwa yonke indawo yedatha yethebula le-pivot, futhi njengengxabano yesithathu, sengeza inombolo yekholomu esiyidingayo. Kungabalwa kalula ngomsebenzi INYANGA (INYANGA), ebuyisela inombolo yenyanga yedethi yedili:
Intuthuko 2. Uphawu oluhle lwesixhumanisi
Ukuphikisana komsebenzi wesibili I-HYPERLINK - umbhalo ovezwa kuseli enesixhumanisi - ungenziwa ube muhle kakhulu uma usebenzisa izinhlamvu ezingajwayelekile ezivela ku-Windings, amafonti e-Webdings nokunye okunjalo esikhundleni sezimpawu zokubhana ">>". Ukuze wenze lokhu ungasebenzisa umsebenzi SYMBOL (CHAR), engabonisa izinhlamvu ngekhodi yazo.
Ngakho-ke, isibonelo, ikhodi yezinhlamvu 56 kufonti ye-Webdings izosinika imicibisholo ephindwe kabili emnandi yesixhumanisi:
Ukuthuthukiswa 3. Gqamisa irowu yamanje neseli esebenzayo
Nokho, ekunqobeni kokugcina kobuhle phezu komqondo ojwayelekile, ungakwazi futhi ukunamathisela kufayela lethu inguqulo eyenziwe lula yokugqamisa umugqa wamanje kanye neseli esilandela isixhumanisi sayo. Lokhu kuzodinga i-macro elula, esizoyilenga ukuphatha umcimbi wokushintsha okukhethiwe eshidini Kuhlanganisiwe.
Ukuze wenze lokhu, chofoza kwesokudla kuthebhu yeshidi Ukufingqa bese ukhetha umyalo Buka Ikhodi (Buka ikhodi). Namathisela ikhodi elandelayo efasiteleni lomhleli we-Visual Basic elivulekayo:
I-Private Sub Worksheet_SelectionChange(ByVal Target As Range) Amaseli.Interior.ColorIndex = -4142 Amaseli(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Njengoba ubona kalula, lapha siqala ngokususa ukugcwaliswa kulo lonke ishidi, bese sigcwalisa umugqa wonke esifingqini ngophuzi (ikhodi yombala 6), bese kuba osawolintshi (ikhodi 44) ngeseli yamanje.
Manje, uma noma iyiphi iseli engaphakathi kweseli yesifinyezo ikhethiwe (akunandaba – ngokwenza noma ngenxa yokuchofoza isixhumanisi sethu), wonke umugqa neseli enenyanga esiyidingayo izogqanyiswa:
Ubuhle 🙂
PS Vele ukhumbule ukulondoloza ifayela ngefomethi enikwe amandla amakhulu (xlsm noma xlsb).
- Idala izixhumanisi zangaphandle nezangaphakathi ngomsebenzi we-HYPERLINK
- Ukudala ama-imeyili ngomsebenzi we-HYPERLINK