txheem
Nag hmo hauv marathon 30 Excel ua haujlwm hauv 30 hnub peb muaj kev lom zem nrog lub luag haujlwm REPT (REPEAT) los ntawm kev tsim cov kab kos hauv ib lub xov tooj ntawm tes thiab siv nws rau kev suav yooj yim. Nws yog hnub Monday, thiab ib zaug ntxiv nws yog lub sijhawm rau peb los tso rau ntawm peb lub kaus mom kev xav.
Hnub 16 ntawm lub marathon, peb yuav kawm txog kev ua haujlwm VOJ (VIEW). Qhov no yog ib tug phooj ywg VLOOKUP (VLOOKUP) thiab HLOOKUP (GPR), tab sis nws ua haujlwm txawv me ntsis.
Yog li, cia peb kawm txoj kev xav thiab sim ua haujlwm hauv kev xyaum VOJ (VIEW). Yog tias koj muaj cov ntaub ntawv ntxiv lossis cov piv txwv ntawm kev siv cov yam ntxwv no, thov qhia rau lawv hauv cov lus.
Function 16: LOOKUP
muaj nuj nqi VOJ (LOOKUP) rov qab tus nqi los ntawm ib kab, ib kab, lossis los ntawm ib qho array.
Kuv tuaj yeem siv LOOKUP muaj nuj nqi li cas?
muaj nuj nqi VOJ (LOOKUP) rov qab qhov tshwm sim, nyob ntawm tus nqi koj tab tom nrhiav. Nrog nws pab koj yuav muaj peev xwm:
- Nrhiav tus nqi kawg hauv ib kab.
- Nrhiav lub hli tas los nrog kev muag khoom tsis zoo.
- Hloov cov tub ntxhais kawm qhov kev kawm tau los ntawm feem pua mus rau qib ntawv.
Syntax LOOKUP
muaj nuj nqi VOJ (LOOKUP) muaj ob hom syntactic - vector thiab array. Hauv daim ntawv vector, qhov ua haujlwm saib rau tus nqi hauv kab lossis kab, thiab hauv daim ntawv array, nws nrhiav tus nqi hauv thawj kab lossis kem ntawm array.
Daim ntawv vector muaj cov hauv qab no syntax:
LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)
- saib xyuas_valalue (lookup_value) - tuaj yeem yog ntawv nyeem, tus lej, boolean, npe, lossis txuas.
- nrhiav_vector (lookup_vector) - Ib qhov ntau uas muaj ib kab lossis ib kab.
- result_vector (result_vector) – ib tug ntau yam uas muaj ib kab los yog ib kem.
- sib cav ntau yam nrhiav_vector (lookup_vector) thiab result_vector (result_vector) yuav tsum yog tib qhov loj.
Daim ntawv array muaj cov hauv qab no syntax:
LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)
- saib xyuas_valalue (lookup_value) - tuaj yeem yog ntawv nyeem, tus lej, boolean, npe, lossis txuas.
- kev tshawb nrhiav yog ua raws li qhov loj me ntawm cov array:
- yog tias cov array muaj ntau kab ntau dua kab, ces qhov kev tshawb fawb tshwm sim thawj kab;
- yog tias tus naj npawb ntawm kab thiab kab yog tib yam lossis muaj ntau kab, ces qhov kev tshawb nrhiav tshwm sim hauv thawj kab.
- cov nuj nqi rov qab tus nqi kawg los ntawm cov kab/kem pom.
Traps LOOKUP (VIEW)
- Nyob hauv txoj haujlwm VOJ (BROWSE) tsis muaj kev xaiv los tshawb nrhiav qhov tseeb match, uas yog nyob rau hauv VLOOKUP (VLOOKUP) thiab hauv HLOOKUP (GPR). Yog tias tsis muaj tus nqi tshawb nrhiav, ces qhov kev ua haujlwm yuav rov qab tus nqi siab tshaj tsis tshaj qhov kev tshawb nrhiav tus nqi.
- Cov array lossis vector uas tau tshawb nrhiav yuav tsum tau txheeb xyuas hauv qhov kev txiav txim siab nce mus, txwv tsis pub cov haujlwm yuav rov ua qhov tsis raug.
- Yog tias thawj tus nqi hauv qhov array / vector raug ntsia ntau dua li tus nqi nrhiav, ces qhov kev ua haujlwm yuav tsim cov lus yuam kev #AT (#N/A).
Piv txwv 1: Nrhiav tus nqi kawg hauv ib kab
Nyob rau hauv daim ntawv ntawm ib tug array muaj nuj nqi VOJ (LOOKUP) tuaj yeem siv los nrhiav tus nqi kawg hauv kab.
Excel pab quotes tus nqi 9,99999999999999 E + 307 raws li tus lej loj tshaj plaws uas tuaj yeem sau rau hauv ib lub xovtooj ntawm tes. Hauv peb cov qauv, nws yuav raug teeb tsa raws li tus nqi xav tau. Nws yog xav tias tus lej loj no yuav tsis pom, yog li qhov ua haujlwm yuav rov qab tus nqi kawg hauv kab D.
Hauv qhov piv txwv no, cov lej hauv kab D tau tso cai tsis txhob muab cais, ntxiv rau, cov ntawv tseem ceeb tuaj yeem hla.
=LOOKUP(9.99999999999999E+307,D:D)
=ПРОСМОТР(9,99999999999999E+307;D:D)
Piv txwv 2: Nrhiav lub hli tas los nrog tus nqi tsis zoo
Hauv qhov piv txwv no, peb yuav siv cov duab vector VOJ (VIEW). Kem D muaj cov nqi muag khoom, thiab kab E muaj cov npe ntawm lub hli. Hauv qee lub hlis, tej yam tsis zoo, thiab cov lej tsis zoo tshwm sim hauv cov hlwb nrog cov nqi muag.
Txhawm rau nrhiav lub hli tas los nrog tus lej tsis zoo, cov qauv nrog VOJ (LOOKUP) yuav kuaj xyuas txhua tus nqi muag uas nws tsawg dua 0 (kev tsis sib xws hauv cov qauv). Tom ntej no, peb faib 1 ntawm qhov tshwm sim, peb xaus nrog ib qho 1, los yog lus yuam kev #DIV/0 (#SECTION/0).
Txij li qhov xav tau tus nqi yog 2 tsis pom, muaj nuj nqi yuav xaiv qhov kawg pom 1, thiab xa rov qab tus nqi sib thooj los ntawm kab E.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)
Piav qhia: Nyob rau hauv cov qauv no, tsis yog kev sib cav nrhiav_vector (lookup_vector) qhia hloov pauv 1/(D2:D8<0), uas tsim ib qho array nyob rau hauv lub computer lub RAM, muaj xws li 1 thiab cov nqi yuam kev #DIV/0 (#SECTION/0). 1 qhia tias cov xov tooj sib txuas hauv qhov ntau D2: D8 muaj tus nqi tsawg dua 0, thiab qhov yuam kev #DIV/0 (#DIV/0) - dab tsi ntau dua lossis sib npaug 0. Yog li ntawd, peb txoj haujlwm yog nrhiav qhov kawg 1 hauv qhov tsim virtual array, thiab raws li qhov no, rov qab lub npe ntawm lub hli los ntawm ntau E2: E8.
Piv txwv 3: Hloov cov tub ntxhais kawm qhov kev kawm tau los ntawm feem pua mus rau qib ntawv
Yav dhau los, peb twb tau daws qhov teeb meem zoo sib xws uas siv lub luag haujlwm VLOOKUP (VPR). Niaj hnub no peb yuav siv lub luag haujlwm VOJ (VIEW) hauv daim ntawv vector los hloov cov tub ntxhais kawm qhov kev kawm tau los ntawm feem pua rau qib ntawv. Tsis zoo li VLOOKUP (VLOOKUP) rau kev ua haujlwm VOJ (VIEW) Nws tsis muaj teeb meem yog tias feem pua yog nyob rau hauv thawj kab ntawm lub rooj. Koj tuaj yeem xaiv kiag txhua kab.
Hauv qhov piv txwv hauv qab no, cov qhab nia yog nyob rau hauv kab D, txheeb raws li kev nce qib, thiab lawv cov ntawv sib raug yog nyob rau hauv kab C, mus rau sab laug ntawm kem nrhiav.
=LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)
