txheem
10 hnub ntawm marathon 30 Excel ua haujlwm hauv 30 hnub peb yuav mob siab rau kev kawm txog kev ua haujlwm HLOOKUP (GPR). Qhov no feature yog heev zoo ib yam li VLOOKUP (VLOOKUP), tsuas yog nws ua haujlwm nrog cov npe ntawm kab rov tav.
Hmoov tsis muaj nuj nqi HLOOKUP (GLOW) tsis yog nrov li nws tus muam, txij li feem ntau cov ntaub ntawv nyob rau hauv cov ntxhuav yog teem vertically. Nco ntsoov lub sij hawm kawg uas koj xav mus nrhiav ib txoj hlua? Yuav ua li cas rov qab tus nqi ntawm tib kem, tab sis nyob rau hauv ib qho ntawm cov kab hauv qab no?
Xijpeem, cia peb muab cov yam ntxwv HLOOKUP (GPR) lub sijhawm zoo tsim nyog ntawm lub yeeb koob thiab ua tib zoo saib cov ntaub ntawv hais txog cov yam ntxwv no, nrog rau cov piv txwv ntawm nws siv. Nco ntsoov, yog tias koj muaj tswv yim nthuav lossis piv txwv, thov qhia rau lawv hauv cov lus.
Function 10: HLOOKUP
muaj nuj nqi HLOOKUP (HLOOKUP) saib tus nqi hauv thawj kab ntawm lub rooj thiab xa rov qab lwm tus nqi los ntawm tib kem hauv lub rooj.
Kuv tuaj yeem siv HLOOKUP (HLOOKUP) ua haujlwm li cas?
Txij li thaum muaj nuj nqi HLOOKUP (HLOOKUP) tuaj yeem pom qhov tseeb lossis kwv yees tus nqi hauv ib txoj hlua, ces nws tuaj yeem:
- Nrhiav cov khoom muag tag nrho rau cheeb tsam xaiv.
- Nrhiav qhov taw qhia uas cuam tshuam rau hnub xaiv.
HLOOKUP Syntax
muaj nuj nqi HLOOKUP (HLOOKUP) muaj cov syntax hauv qab no:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
- saib xyuas_valalue (lookup_value): Tus nqi yuav tsum pom. Yuav ua tau tus nqi los yog kev siv ntawm tes.
- table_cov (table): lookup table. Nws tuaj yeem yog qhov kev siv ntau yam lossis ib qho npe uas muaj 2 kab lossis ntau dua.
- row_index_num (line_number): Ib txoj hlua uas muaj tus nqi xa rov qab los ntawm kev ua haujlwm. Teem los ntawm kab zauv hauv lub rooj.
- range_lookup (range_lookup): Siv FALSE lossis 0 txhawm rau nrhiav qhov tseeb match; rau qhov kwv yees kev tshawb nrhiav, TRUE (TRUE) lossis 1. Nyob rau hauv qhov kawg, txoj hlua uas lub luag haujlwm tshawb nrhiav yuav tsum tau muab faib ua ntu zus.
Traps HLOOKUP (GPR)
zoo li VLOOKUP (VLOOKUP), muaj nuj nqi HLOOKUP (HLOOKUP) tuaj yeem ua tau qeeb, tshwj xeeb tshaj yog thaum tshawb nrhiav qhov sib tw ntawm cov kab ntawv hauv ib lub rooj tsis txheeb. Thaum twg ua tau, siv qhov kwv yees tshawb hauv ib lub rooj txheeb los ntawm thawj kab hauv kev txiav txim siab nce mus. Koj tuaj yeem ua ntej siv cov haujlwm TIV THAIV (Ntau tshaj tawm) los yog COUNTIF (COUNTIF) kom paub tseeb tias tus nqi koj tab tom nrhiav txawm tias muaj nyob hauv thawj kab.
Lwm yam nta xws li INDEX (INDEX) thiab TIV THAIV (MATCH) kuj tuaj yeem siv los khaws cov txiaj ntsig los ntawm lub rooj thiab ua haujlwm zoo dua. Peb mam li saib lawv tom qab hauv peb lub marathon thiab pom tias lawv muaj zog npaum li cas thiab hloov pauv tau.
Piv txwv 1: Nrhiav cov nqi muag khoom rau ib cheeb tsam xaiv
Cia kuv ceeb toom koj dua tias qhov ua haujlwm HLOOKUP (HLOOKUP) tsuas yog saib rau tus nqi hauv kab saum toj kawg nkaus ntawm lub rooj. Hauv qhov piv txwv no, peb yuav pom qhov muag tag nrho rau cheeb tsam xaiv. Nws yog ib qho tseem ceeb rau peb kom tau txais tus nqi raug, yog li peb siv cov kev teeb tsa hauv qab no:
- Lub npe cheeb tsam tau nkag rau hauv cell B7.
- Lub rooj saib xyuas hauv cheeb tsam muaj ob kab thiab nthuav dav C2: F3.
- Cov khoom muag tag nrho yog nyob rau hauv kab 2 ntawm peb lub rooj.
- Qhov kev sib cav zaum kawg yog teem rau FALSE kom pom qhov sib tw tiag tiag thaum tshawb nrhiav.
Cov mis hauv cell C7 yog:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
Yog tias lub npe ntawm thaj av tsis pom nyob rau hauv thawj kab ntawm lub rooj, qhov tshwm sim ntawm kev ua haujlwm HLOOKUP (GPR) yuav #AT (#N/A).
Piv txwv 2: Nrhiav ib qho kev ntsuas rau hnub xaiv
Feem ntau thaum siv qhov ua haujlwm HLOOKUP (HLOOKUP) xav kom muaj qhov sib tw, tab sis qee zaum qhov kwv yees qhov sib tw yog qhov tsim nyog dua. Piv txwv li, yog tias qhov ntsuas tau hloov thaum pib ntawm txhua lub hlis twg, thiab thawj hnub ntawm cov quarters no tau siv los ua cov kab lus hauv qab no (saib daim duab hauv qab). Hauv qhov no, siv lub luag haujlwm HLOOKUP (HLOOKUP) thiab kwv yees qhov sib tw, koj yuav pom qhov taw qhia uas cuam tshuam rau hnub tim. Hauv qhov piv txwv no:
- Hnub tim sau rau hauv cell C5.
- Qhov ntsuas ntsuas lub rooj muaj ob kab thiab nyob rau hauv qhov ntau C2: F3.
- Lub rooj saib yog txheeb los ntawm hnub kab nyob rau hauv ascending order.
- Cov ntsuas tau sau tseg hauv kab 2 ntawm peb lub rooj.
- Lub luag haujlwm qhov kev sib cav zaum kawg yog teem rau TRUE txhawm rau nrhiav qhov kwv yees sib tw.
Cov mis hauv cell D5 yog:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
Yog tias hnub tsis pom nyob rau hauv thawj kab ntawm lub rooj, muaj nuj nqi HLOOKUP (HLOOKUP) yuav pom qhov ze tshaj plaws tus nqi uas tsawg dua qhov kev sib cav saib xyuas_valalue (lookup_value). Hauv qhov piv txwv no, tus nqi xav tau yog Lub peb hlis ntuj 15. Nws tsis yog nyob rau hauv kab hnub, yog li tus qauv yuav coj tus nqi 1 Lub ib hlis ntuj thiab rov qab los 0,25.
