txheem
Yog tias koj tsawg kawg paub txog kev ua haujlwm VPR (VLOOKUP) (yog tias tsis yog, ua ntej khiav ntawm no), tom qab ntawd koj yuav tsum nkag siab tias qhov no thiab lwm yam haujlwm zoo ib yam li nws (VIEW, INDEX thiab SEARCH, SELECT, thiab lwm yam) ib txwm ua rau qhov tshwm sim. tus nqi - tus lej, ntawv lossis hnub uas peb tab tom nrhiav hauv lub rooj muab.
Tab sis yuav ua li cas yog hais tias, es tsis txhob ntawm tus nqi, peb xav kom tau ib tug nyob hyperlink, los ntawm txhaj rau qhov uas peb yuav instantly dhia mus rau qhov pom match nyob rau hauv lwm lub rooj los saib nws nyob rau hauv ib tug general ntsiab lus teb?
Cia peb hais tias peb muaj cov lus txiav txim loj rau peb cov neeg siv khoom raws li cov lus qhia. Txhawm rau kom yooj yim (txawm tias qhov no tsis tsim nyog), Kuv hloov lub rooj mus rau qhov "smart" keyboard shortcut dynamic Ctrl+T thiab muab rau ntawm tab constructor (Tsim) nws lub npe tabOrders:
Ntawm ib daim ntawv cais Sib sau Kuv tsim ib lub rooj pivot (txawm hais tias nws tsis tas yuav yog ib lub rooj pivot - ib lub rooj yog haum raws li hauv paus ntsiab lus), qhov twg, raws li cov ntaub ntawv thawj zaug, kev muag khoom los ntawm lub hlis rau txhua tus neeg siv khoom suav nrog:
Cia peb ntxiv ib kem rau lub rooj txiav txim nrog cov qauv uas saib lub npe ntawm cov neeg siv khoom rau qhov kev txiav txim tam sim no ntawm daim ntawv Sib sau. Rau qhov no peb siv cov pawg classical ua haujlwm INDEX (INDEX) и XOV XWM ntxiv (MATCH):
Tam sim no cia peb qhwv peb cov qauv ua haujlwm CELL (CELL), uas peb yuav hais kom tso saib qhov chaw nyob ntawm lub cell nrhiav tau:
Thiab thaum kawg, peb muab txhua yam uas tau hloov mus ua haujlwm HYPERLINK (HYPERLINK), uas nyob rau hauv Microsoft Excel tuaj yeem tsim qhov hyperlink nyob rau hauv txoj kev muab (chaw nyob). Qhov tsuas yog qhov tsis pom tseeb yog tias koj yuav tsum tau nplaum lub cim hash (#) thaum pib mus rau qhov chaw nyob tau txais kom qhov txuas tau raug pom zoo los ntawm Excel li sab hauv (los ntawm daim ntawv mus rau daim ntawv):
Tam sim no, thaum koj nyem rau ntawm ib qho ntawm cov kev sib txuas, peb yuav tam sim dhia mus rau ntawm tes nrog lub npe ntawm lub tuam txhab ntawm daim ntawv nrog lub rooj pivot.
Txhawm rau ua kom nws zoo tiag tiag, cia peb txhim kho peb cov qauv me ntsis kom qhov kev hloov pauv tshwm sim tsis yog rau tus neeg siv lub npe, tab sis mus rau tus lej tshwj xeeb tus nqi raws nraim hauv lub hli kem thaum qhov sib txuas lus tau ua tiav. Ua li no, peb yuav tsum nco ntsoov tias lub luag haujlwm INDEX (INDEX) hauv Excel yog ntau yam thiab siv tau, ntawm lwm yam, hauv hom:
=INDEX( XNUMXD_ntau; Line_num; Column_num )
Ntawd yog, raws li thawj qhov kev sib cav, peb tuaj yeem hais qhia tsis yog kem nrog cov npe ntawm cov tuam txhab hauv pivot, tab sis tag nrho cov ntaub ntawv cheeb tsam ntawm lub rooj pivot, thiab raws li qhov kev sib cav thib peb, ntxiv cov naj npawb ntawm kab peb xav tau. Nws tuaj yeem suav tau yooj yim los ntawm kev ua haujlwm HLI (HNUB), uas xa rov qab lub hli tus lej rau hnub deal:
Kev txhim kho 2. Cov cim txuas zoo nkauj
Thib ob muaj nuj nqi sib cav HYPERLINK - cov ntawv nyeem uas tau tso tawm hauv lub xov tooj ntawm tes nrog qhov txuas - tuaj yeem ua tau zoo dua yog tias koj siv cov cim tsis zoo los ntawm Windings, Webdings fonts thiab lwm yam es tsis txhob siv cov cim banal ">>". Rau qhov no koj tuaj yeem siv lub luag haujlwm SYMBOL (CHAR), uas tuaj yeem tso saib cov cim los ntawm lawv cov lej.
Yog li, piv txwv li, tus cim code 56 hauv Webdings font yuav muab ob peb xub zoo rau ib qho hyperlink:
Kev txhim kho 3. Qhia cov kab tam sim no thiab cov xov tooj ntawm tes
Zoo, rau qhov kawg yeej ntawm kev zoo nkauj tshaj qhov kev nkag siab zoo, koj tuaj yeem xa mus rau peb cov ntaub ntawv yooj yim ntawm kev qhia txog kab tam sim no thiab lub xov tooj ntawm tes uas peb ua raws li qhov txuas mus. Qhov no yuav xav tau ib qho yooj yim macro, uas peb yuav dai los tuav qhov kev xaiv hloov pauv ntawm daim ntawv Sib sau.
Txhawm rau ua qhov no, right-click ntawm nplooj ntawv tab Summary thiab xaiv cov lus txib Saib code (Saib code). Muab cov cai hauv qab no tso rau hauv Visual Basic editor qhov rai uas qhib:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End
Raws li koj tuaj yeem pom tau yooj yim, ntawm no peb thawj zaug tshem tawm cov ntawv sau tag nrho, thiab tom qab ntawd sau tag nrho cov kab hauv cov ntsiab lus nrog daj (xim xim 6), thiab tom qab ntawd txiv kab ntxwv (code 44) nrog lub xovtooj tam sim no.
Tam sim no, thaum twg ib lub xov tooj hauv cov ntsiab lus ntawm tes raug xaiv (nws tsis muaj teeb meem - manually lossis los ntawm kev nyem rau ntawm peb qhov hyperlink), tag nrho cov kab thiab xov tooj nrog lub hli peb xav tau yuav raug qhia:
Kev zoo nkauj 🙂
PS Tsuas yog nco ntsoov khaws cov ntaub ntawv hauv hom macro-enabled (xlsm lossis xlsb).
- Tsim kev sib txuas sab nraud thiab sab hauv nrog HYPERLINK muaj nuj nqi
- Tsim cov email nrog HYPERLINK muaj nuj nqi