txheem
- Nrhiav hauv Excel los ntawm ntau cov txheej txheem
- Peb rho tawm qhov 2nd, 3rd, thiab lwm yam txiaj ntsig siv VLOOKUP
- Retrieve tag nrho repetitions ntawm tus nqi xav tau
- XNUMXD nrhiav los ntawm kab paub thiab kab
- Siv ntau VLOOKUPs hauv ib qho qauv
- Dynamic hloov cov ntaub ntawv los ntawm cov rooj sib txawv siv VLOOKUP thiab INDIRECT
Hauv qhov thib ob ntawm peb cov lus qhia txog kev ua haujlwm VPR (VLOOKUP) hauv Excel, peb yuav txheeb xyuas ob peb yam piv txwv uas yuav pab koj tswj txhua lub zog VPR los daws qhov kev xav tau tshaj plaws hauv Excel. Cov piv txwv xav tias koj twb muaj kev paub txog kev ua haujlwm li cas. Yog tias tsis yog, tej zaum koj yuav xav pib nrog thawj feem ntawm qhov kev qhia no, uas piav qhia txog cov syntax thiab kev siv yooj yim. VPR. Zoo, cia peb pib.
Nrhiav hauv Excel los ntawm ntau cov txheej txheem
muaj nuj nqi VPR hauv Excel yog lub cuab yeej muaj zog tiag tiag rau kev tshawb nrhiav rau ib qho nqi tshwj xeeb hauv cov ntaub ntawv. Txawm li cas los xij, muaj qhov txwv tseem ceeb - nws cov syntax tso cai rau koj los tshawb nrhiav tsuas yog ib qho txiaj ntsig. Yuav ua li cas yog tias koj xav tshawb nrhiav los ntawm ntau yam xwm txheej? Koj yuav pom cov tshuaj hauv qab no.
Piv txwv 1: Tshawb nrhiav los ntawm 2 yam sib txawv
Piv txwv tias peb muaj ib daim ntawv xaj thiab peb xav nrhiav Qhov ntau ntawm cov khoom (Qty.), raws li ob txoj hauv kev - Tus neeg siv khoom npe (Cov neeg siv khoom) thiab Khoom npe (Cov khoom lag luam). Qhov teeb meem yog qhov nyuaj los ntawm qhov tseeb tias txhua tus neeg yuav khoom xaj ntau hom khoom, raws li tuaj yeem pom los ntawm cov lus hauv qab no:
ua haujlwm tsis tu ncua VPR yuav tsis ua hauj lwm nyob rau hauv no scenario vim hais tias nws yuav rov qab thawj tus nqi nws pom uas phim tus nqi muab lookup. Piv txwv li, yog tias koj xav paub qhov ntau ntawm ib yam khoom Khoom qab zib'xaj los ntawm tus neeg yuav khoom Jeremy toj, sau cov qauv hauv qab no:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- cov qauv no yuav rov qab qhov tshwm sim 15sib raug rau cov khoom apples, vim nws yog thawj tus nqi uas phim.
Muaj ib qho yooj yim workaround - tsim ib kab ntawv ntxiv uas yuav muab tag nrho cov kev xav tau. Hauv peb qhov piv txwv, cov no yog cov kab Tus neeg siv khoom npe (Cov neeg siv khoom) thiab Khoom npe (Cov khoom lag luam). Tsis txhob hnov qab tias kem merged yuav tsum ib txwm yog kab laug sab hauv kev tshawb nrhiav ntau yam, vim nws yog kab laug sab uas ua haujlwm. VPR ntsia thaum nrhiav tus nqi.
Yog li, koj ntxiv ib pawg pabcuam rau lub rooj thiab luam cov qauv hauv qab no rau tag nrho nws cov hlwb: =B2&C2. Yog tias koj xav kom txoj hlua nyeem tau ntau dua, koj tuaj yeem cais cov txiaj ntsig sib xyaw nrog qhov chaw: =B2&» «&C2. Tom qab ntawd, koj tuaj yeem siv cov qauv hauv qab no:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Cell nyob qhov twg B1 muaj tus nqi concatenated ntawm qhov kev sib cav saib xyuas_valalue (lookup_value) thiab 4 - Kev sib cav Col_index_num (column_number), piv txwv li tus naj npawb ntawm kem uas muaj cov ntaub ntawv yuav tsum tau muab.
Piv txwv 2: VLOOKUP los ntawm ob lub ntsiab lus nrog lub rooj tau saib ntawm lwm daim ntawv
Yog tias koj xav tau hloov kho lub rooj loj (Main table) los ntawm kev ntxiv cov ntaub ntawv los ntawm lub rooj thib ob (Lookup table), uas nyob ntawm lwm daim ntawv lossis hauv lwm phau ntawv ua haujlwm Excel, ces koj tuaj yeem sau tus nqi xav tau ncaj qha rau hauv cov mis uas koj ntxig. rau hauv lub rooj loj.
Ib yam li hauv qhov piv txwv dhau los, koj yuav xav tau ib pawg pabcuam hauv cov lus Lookup nrog cov txiaj ntsig sib xyaw. Kab ntawv no yuav tsum yog kab laug sab hauv kev tshawb nrhiav ntau yam.
Yog li cov mis nrog VPR tuaj yeem ua tau zoo li no:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Ntawm no, kab B thiab C muaj cov npe ntawm cov neeg siv khoom thiab cov npe khoom, feem, thiab qhov txuas Orders!$A&$2:$D$2 txhais ib lub rooj los saib hauv lwm daim ntawv.
Txhawm rau ua kom cov mis nyeem tau ntau dua, koj tuaj yeem muab qhov pom ntau lub npe, thiab tom qab ntawd tus qauv yuav zoo li yooj yim dua:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Rau cov qauv ua haujlwm, qhov tseem ceeb hauv kab laug sab ntawm lub rooj koj tab tom saib yuav tsum tau ua ke raws nraim tib yam li hauv cov txheej txheem tshawb nrhiav. Nyob rau hauv daim duab saum toj no, peb ua ke cov txiaj ntsig u2bu2band muab qhov chaw nruab nrab ntawm lawv, tib yam li koj yuav tsum tau ua hauv thawj qhov kev sib cav ntawm kev ua haujlwm (BXNUMX& "" & CXNUMX).
Nco ntsoov! muaj nuj nqi VPR txwv rau 255 tus cim, nws tsis tuaj yeem tshawb nrhiav tus nqi uas ntau dua 255 tus cim ntev. Khaws qhov no hauv siab thiab xyuas kom meej tias qhov ntev ntawm qhov xav tau tsis pub dhau qhov kev txwv no.
Kuv pom zoo tias ntxiv ib qho kev pabcuam tsis yog qhov zoo nkauj tshaj plaws thiab tsis yog ib txwm siv tau. Koj tuaj yeem ua tib yam yam tsis muaj tus pab pawg, tab sis qhov ntawd yuav xav tau ntau txoj hauv kev nrog kev sib xyaw ua haujlwm INDEX (INDEX) thiab TIV THAIV (Ntau tshaj tawm).
Peb rho tawm qhov 2nd, 3rd, thiab lwm yam txiaj ntsig siv VLOOKUP
Koj twb paub qhov ntawd lawm VPR tuaj yeem xa rov qab tsuas yog ib qho txiaj ntsig sib xws, ntau qhov tseeb, thawj qhov pom. Tab sis yuav ua li cas yog tias tus nqi no rov ua dua ob peb zaug hauv qhov pom array, thiab koj xav rho tawm 2nd lossis 3rd ntawm lawv? Yuav ua li cas yog tag nrho cov nqi? Qhov teeb meem zoo li nyuaj, tab sis muaj kev daws teeb meem!
Piv txwv li ib kem ntawm lub rooj muaj cov npe ntawm cov neeg siv khoom (Cov Neeg Siv Khoom Lub Npe), thiab lwm kem muaj cov khoom (Cov Khoom) uas lawv yuav. Cia peb sim nrhiav 2nd, 3rd thiab 4th khoom yuav los ntawm tus neeg siv khoom.
Txoj kev yooj yim tshaj yog ntxiv ib qho kev pabcuam ua ntej ntawm kab neeg siv lub npe thiab sau nws nrog cov neeg siv khoom npe nrog tus lej rov qab ntawm txhua lub npe, piv txwv li, John Doe 1, John Doe 2 thiab lwm yam. Peb yuav ua kom yuam kev nrog tus lej siv cov haujlwm COUNTIF (COUNTIF), muab cov npe cov neeg siv khoom nyob hauv kab B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Tom qab ntawd koj tuaj yeem siv cov haujlwm qub VPRmus nrhiav qhov kev txiav txim yuav tsum tau. Piv txwv li:
- nrhiav 2: ncu cov khoom xaj los ntawm cov neeg siv khoom Dan xim av:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - nrhiav 3: ncu cov khoom xaj los ntawm cov neeg siv khoom Dan xim av:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Qhov tseeb, koj tuaj yeem nkag mus rau lub xovtooj siv raws li tus nqi nrhiav tsis yog cov ntawv nyeem, raws li pom hauv daim duab hauv qab no:
Yog koj tsuas nrhiav 2-e rov ua dua, koj tuaj yeem ua nws yam tsis muaj cov pab pawg los ntawm kev tsim cov qauv ntau dua:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Hauv cov qauv no:
- $F $2 - lub xov tooj ntawm tes uas muaj lub npe ntawm tus neeg yuav khoom (nws tsis hloov pauv, thov nco ntsoov - qhov txuas yog kiag li);
- $B $ – kem neeg siv lub npe;
- Table4 - Koj lub rooj (qhov chaw no tuaj yeem ua tau ntau yam);
- $ C16 - qhov kawg ntawm tes ntawm koj lub rooj lossis ntau yam.
Cov qauv no pom tsuas yog tus nqi sib tw thib ob xwb. Yog tias koj xav tau rho tawm cov lus rov qab, siv cov tshuaj yav dhau los.
Yog tias koj xav tau cov npe ntawm txhua qhov kev sib tw - qhov ua haujlwm VPR qhov no tsis yog tus pab, vim nws tsuas yog rov qab ib qho nqi ntawm ib lub sijhawm - lub sijhawm. Tab sis Excel muaj lub luag haujlwm INDEX (INDEX), uas tuaj yeem yooj yim tiv nrog txoj haujlwm no. Cov qauv zoo li no yuav zoo li cas, koj yuav kawm hauv qhov piv txwv hauv qab no.
Retrieve tag nrho repetitions ntawm tus nqi xav tau
Raws li hais saum toj no VPR tsis tuaj yeem rho tawm tag nrho cov txiaj ntsig duplicate los ntawm scanned ntau yam. Txhawm rau ua qhov no, koj xav tau ib qho qauv me ntsis ntxiv, ua los ntawm ntau Excel ua haujlwm, xws li INDEX (INDEX), ME (TSUM) thiab LEEJ (LINE)
Piv txwv li, cov qauv hauv qab no pom txhua qhov kev rov ua dua ntawm tus nqi ntawm tes F2 hauv qhov ntau B2: B16 thiab xa cov txiaj ntsig los ntawm tib kab hauv kab C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Sau cov qauv array no rau hauv ntau lub hlwb uas nyob ib sab, xws li cov hlwb f4:8 uaraws li qhia hauv daim duab hauv qab no. Tus naj npawb ntawm cov hlwb yuav tsum sib npaug los yog ntau dua qhov siab tshaj qhov ua tau rov ua dua ntawm tus nqi tshawb nrhiav. Tsis txhob hnov qab nias Ctrl + ua haujlwm + nkagnkag mus rau array formula kom raug.
Yog tias koj xav nkag siab tias nws ua haujlwm li cas, cia peb dhia mus rau hauv cov ntsiab lus ntawm cov qauv me ntsis:
Tshooj 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - Sib piv tus nqi hauv cell F2 nrog rau txhua qhov ntawm qhov tseem ceeb hauv thaj tsam B2: B16. Yog tias pom qhov sib tw, ces qhov kev qhia STRING(C2:C16)-1 rov qab tus naj npawb ntawm cov kab sib txuas (tus nqi -1 tso cai rau koj tsis suav nrog cov kab header). Yog tias tsis muaj qhov sib tw, ua haujlwm IF (IF) rov qab ib txoj hlua khoob.
Kev ua haujlwm tshwm sim IF (IF) yuav muaj xws li kab rov tav array: {1,"",3,"",5,"","","","","","",12,"","",""}
Tshooj 2:
ROW()-3
СТРОКА()-3
Ntawm no muaj nuj nqi LEEJ (LINE) ua raws li lub txee ntxiv. Txij li cov qauv tau theej rau hauv cov hlwb F4: F9, peb rho tus lej 3 los ntawm kev ua haujlwm tau txais txiaj ntsig 1 hauv cell F4 (kab 4, rho tawm 3) kom tau 2 hauv cell F5 (kab 5, rho tawm 3) thiab lwm yam.
Tshooj 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
muaj nuj nqi ME (KOJ) rov qab n aw tus nqi tsawg tshaj plaws hauv cov ntaub ntawv array. Hauv peb qhov xwm txheej, txoj haujlwm twg (los ntawm qhov tsawg tshaj plaws) rov qab los yog txiav txim siab los ntawm kev ua haujlwm LEEJ (LINE) (saib Ntu 2). Yog li, rau lub cell F4 muaj nuj nqi ME ({array}, 1) rov qab 1: ncu (smallest) array element, ie 1. Rau cell F5 rov qab 2: ncu qhov tsawg tshaj plaws nyob rau hauv lub array, uas yog 3, Thiab lwm yam
Tshooj 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
muaj nuj nqi INDEX (INDEX) tsuas yog xa rov qab tus nqi ntawm ib lub xov tooj tshwj xeeb hauv ib qho array C2: ua 16. Rau cell F4 muaj nuj nqi INDEX($C$2:$C$16) yuav rov qab los applesrau F5 muaj nuj nqi INDEX($C$2:$C$16) yuav rov qab los Khoom qab zib' thiab thiaj li nyob.
Tshooj 5:
IFERROR()
ЕСЛИОШИБКА()
Thaum kawg, peb muab cov qauv tso rau hauv qhov ua haujlwm IFERROR (IFERROR), vim tias koj tsis zoo li yuav txaus siab rau cov lus yuam kev #AT (#N/A) yog tias tus naj npawb ntawm cov cell uas tau theej cov qauv yog tsawg dua li tus naj npawb ntawm qhov sib npaug ntawm qhov ntau tau saib.
XNUMXD nrhiav los ntawm kab paub thiab kab
Ua kev tshawb nrhiav XNUMXD hauv Excel suav nrog kev tshawb nrhiav tus nqi los ntawm kab paub thiab kab zauv. Hauv lwm lo lus, koj tab tom rho tawm tus nqi ntawm tes ntawm kev sib tshuam ntawm ib kab thiab kab.
Yog li, cia peb tig mus rau peb lub rooj thiab sau ib qho qauv nrog cov haujlwm VPR, uas yuav pom cov ntaub ntawv hais txog tus nqi ntawm cov txiv qaub muag hauv lub Peb Hlis.
Muaj ntau txoj hauv kev los ua XNUMXD tshawb nrhiav. Tshawb xyuas cov kev xaiv thiab xaiv qhov uas haum koj tshaj plaws.
VLOOKUP thiab MATCH muaj nuj nqi
Koj tuaj yeem siv ntau yam haujlwm VPR (VLOOKUP) thiab XOV XWM ntxiv (MATCH) txhawm rau nrhiav tus nqi ntawm qhov kev sib tshuam ntawm cov teb Khoom npe (txoj hlua) thiab Hlis (kem) ntawm array hauv nqe lus nug:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Cov qauv saum toj no yog kev ua haujlwm tsis tu ncua VPR, uas saib rau qhov tseeb ntawm tus nqi "Lemons" hauv hlwb A2 txog A9. Tab sis txij li thaum koj tsis paub tias kab twg lub Peb Hlis kev muag khoom nyob rau hauv, koj yuav tsis tuaj yeem teeb tsa tus lej kab rau qhov kev sib cav thib peb. VPR. Hloov chaw, cov haujlwm yog siv XOV XWM ntxivlos txhais kab lus no.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Txhais ua lus tib neeg, cov qauv no txhais tau tias:
- Peb tab tom nrhiav rau cov cim "Mar" - kev sib cav saib xyuas_valalue (lookup_value);
- Saib hauv hlwb los ntawm A1 txog I1 - kev sib cav lookup_array (lookup_array);
- Rov qab qhov tseeb match - kev sib cav match_type (match_type).
siv 0 nyob rau hauv qhov kev sib cav thib peb, koj hais functions XOV XWM ntxiv nrhiav thawj tus nqi uas phim tus nqi uas koj tab tom nrhiav. Qhov no sib npaug rau tus nqi cuav (FALSE) rau qhov kev sib cav thib plaub VPR.
Qhov no yog li cas koj tuaj yeem tsim ob txoj kev tshawb nrhiav hauv Excel, tseem hu ua kev tshawb nrhiav ob sab lossis kev tshawb nrhiav bidirectional.
SUMPRODUCT muaj nuj nqi
muaj nuj nqi SUMPRODUCT (SUMPRODUCT) xa rov qab cov lej ntawm cov khoom ntawm cov xaiv arrays:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
INDEX thiab MATCH muaj nuj nqi
Hauv tsab xov xwm tom ntej no kuv yuav piav qhia txog cov haujlwm no kom ntxaws, yog li tam sim no koj tuaj yeem luam tawm cov qauv no:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Cov npe sib txawv thiab tus neeg teb xov tooj sib tshuam
Yog tias koj tsis nyob rau hauv tag nrho cov qauv Excel nyuaj, koj yuav nyiam qhov pom thiab nco txog txoj hauv kev:
- Xaiv lub rooj, qhib lub tab qauv (Formulas) thiab nias Tsim los ntawm Kev Xaiv (Tsim los ntawm kev xaiv).
- Kos rau kem Sab saum toj kab (ntawm kab saum toj no) thiab Sab laug (hauv kab ntawv sab laug). Microsoft Excel yuav muab cov npe rau cov kab los ntawm cov txiaj ntsig hauv kab saum toj kawg nkaus thiab sab laug ntawm koj daim ntawv nthuav qhia. Tam sim no koj tuaj yeem tshawb xyuas siv cov npe no ncaj qha yam tsis tau tsim cov qauv.
- Hauv ib lub xov tooj khoob, sau =row_name column_name, piv txwv li no:
= Lemons Mar
… los yog vice versa:
= Mar Lemons
Nco ntsoov tias kab thiab kab npe yuav tsum tau sib cais los ntawm qhov chaw, uas nyob rau hauv rooj plaub no ua haujlwm zoo li tus neeg teb xov tooj sib tshuam.
Thaum koj sau ib lub npe, Microsoft Excel yuav qhia cov lus qhia nrog cov npe sib xws, ib yam li thaum koj nkag mus rau tus qauv.
- Xovxwm Sau thiab xyuas qhov tshwm sim
Feem ntau, qhov twg ntawm cov txheej txheem saum toj no koj xaiv, qhov tshwm sim ntawm kev tshawb nrhiav ob sab yuav zoo ib yam:
Siv ntau VLOOKUPs hauv ib qho qauv
Nws tshwm sim tias lub rooj tseem ceeb thiab lub rooj saib tsis muaj ib kab hauv ib qho, thiab qhov no tiv thaiv koj los ntawm kev siv cov haujlwm li niaj zaus VPR. Txawm li cas los xij, muaj lwm lub rooj uas tsis muaj cov ntaub ntawv uas peb xav tau, tab sis muaj ib kab ke nrog lub ntsiab lus thiab lub rooj saib.
Cia peb saib cov piv txwv hauv qab no. Peb muaj ib lub rooj loj nrog ib sab SKU (tshiab), qhov twg koj xav ntxiv ib kem nrog tus nqi coj los ntawm lwm lub rooj. Tsis tas li ntawd, peb muaj 2 lub rooj saib xyuas. Thawj tus (Lookup table 1) muaj cov lej tshiab SKU (tshiab) thiab cov npe khoom, thiab qhov thib ob (Saib cov lus 2) - cov npe khoom thiab cov lej qub SKU (laus).
Txhawm rau ntxiv cov nqi ntawm lub rooj saib thib ob mus rau lub rooj tseem ceeb, koj yuav tsum ua qhov kev txiav txim hu ua ob npaug VPR los yog zes VPR.
- Sau ib txoj haujlwm VPR, uas pom cov khoom npe hauv lub rooj Nrhiav lub rooj 1siv SKU, raws li tus nqi xav tau:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Ntawm no New_SKU - npe range $A: $B hauv lub rooj Nrhiav lub rooj 1, 2 - qhov no yog kab B, uas muaj cov npe ntawm cov khoom (saib daim duab saum toj no)
- Sau tus qauv los ntxig tus nqi ntawm lub rooj Nrhiav lub rooj 2 raws li cov khoom muaj npe nrov. Ua li no, muab tshuaj txhuam cov mis uas koj tau tsim ua ntej raws li tus nqi nrhiav rau qhov ua haujlwm tshiab VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Ntawm no nqe - npe range $A: $C hauv lub rooj Nrhiav lub rooj 2, 3 yog kab C uas muaj nqi.
Daim duab hauv qab no qhia cov txiaj ntsig rov qab los ntawm cov qauv peb tsim:
Dynamic hloov cov ntaub ntawv los ntawm cov rooj sib txawv siv VLOOKUP thiab INDIRECT
Ua ntej, cia peb qhia meej tias peb txhais li cas los ntawm kev qhia "Dynamic hloov pauv cov ntaub ntawv los ntawm cov rooj sib txawv" kom paub tseeb tias peb nkag siab ib leeg kom raug.
Muaj cov xwm txheej thaum muaj ob peb nplooj ntawv nrog cov ntaub ntawv ntawm tib hom, thiab nws yog ib qho tsim nyog los rho tawm cov ntaub ntawv tsim nyog los ntawm ib daim ntawv, nyob ntawm tus nqi uas tau nkag rau hauv ib lub xov tooj ntawm tes. Kuv xav tias nws yooj yim dua los piav qhov no nrog ib qho piv txwv.
Xav txog tias koj muaj cov ntaub ntawv muag khoom rau ntau thaj tsam nrog cov khoom qub thiab tib hom. Koj xav nrhiav cov duab muag rau ib cheeb tsam tshwj xeeb:
Yog tias koj tsuas muaj ob daim ntawv tshaj tawm no, ces koj tuaj yeem siv cov qauv yooj yim tsis txaus ntseeg nrog cov haujlwm VPR и IF (IF) xaiv cov ntaub ntawv xav tau los tshawb nrhiav:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Qhov twg:
- $D $2 yog ib lub cell uas muaj lub npe ntawm cov khoom. Nco ntsoov tias peb siv cov lus qhia meej ntawm no kom tsis txhob hloov pauv tus nqi saib thaum luam cov qauv mus rau lwm lub hlwb.
- $D3 yog ib lub cell nrog lub npe ntawm cheeb tsam. Peb tab tom siv cov kab lus tseeb thiab cov kab txheeb ze siv vim tias peb npaj yuav luam cov qauv mus rau lwm lub hlwb hauv tib kem.
- FL_Sales и CA_Sales - cov npe ntawm cov ntxhuav (lossis cov npe muaj npe) uas muaj cov ntaub ntawv muag khoom sib xws. Koj tuaj yeem, ntawm chav kawm, siv cov npe ib txwm muaj thiab cov ntaub ntawv xov tooj ntawm tes, piv txwv 'FL Sheet'!$A$3:$B$10, tab sis cov npe ntau yam yooj yim dua.
Txawm li cas los xij, thaum muaj ntau lub rooj zoo li no, kev ua haujlwm IF tsis yog qhov kev daws teeb meem zoo tshaj plaws. Hloov chaw, koj tuaj yeem siv lub luag haujlwm TSEEM CEEB (INDIRECT) xa rov qab qhov xav tau tshawb nrhiav ntau yam.
Raws li koj tej zaum paub, muaj nuj nqi TSEEM CEEB yog siv los xa ib qhov txuas uas muab los ntawm ib txoj hlua ntawv, uas yog raws nraim qhov peb xav tau tam sim no. Yog li, ua siab tawv hloov hauv cov qauv saum toj no cov lus qhia nrog cov haujlwm IF mus txuas nrog function TSEEM CEEB. Ntawm no yog kev sib xyaw ua ke VPR и TSEEM CEEB ua haujlwm zoo nrog:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Qhov twg:
- $D $2 - qhov no yog lub xov tooj ntawm tes nrog lub npe ntawm cov khoom, nws tsis hloov pauv vim qhov kev sib txuas kiag li.
- $D3 yog lub xov tooj ntawm tes uas muaj thawj feem ntawm lub npe cheeb tsam. Hauv peb qhov piv txwv, qhov no FL.
- _Kev muag khoom - feem ntau ntawm lub npe ntawm tag nrho cov npe npe lossis cov rooj. Thaum ua ke nrog tus nqi hauv cell D3, nws tsim lub npe tsim nyog ntawm qhov xav tau ntau yam. Hauv qab no yog qee cov ntsiab lus rau cov neeg tshiab rau kev ua haujlwm TSEEM CEEB.
INDIRECT thiab VLOOKUP ua haujlwm li cas
Ua ntej, cia kuv nco koj cov syntax ntawm lub luag haujlwm TSEEM CEEB (INDIRECT):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Thawj qhov kev sib cav tuaj yeem yog kev siv ntawm tes (A1 lossis R1C1 style), ntau lub npe, lossis kab ntawv. Qhov kev sib cav thib ob txiav txim siab seb qhov style ntawm qhov txuas yog muaj nyob rau hauv thawj qhov kev sib cav:
- A1yog qhov kev sib cav TXOJ CAI (TRUE) lossis tsis tau teev tseg;
- r1c1 ua, yog FAS E (FALSE).
Hauv peb cov ntaub ntawv, qhov txuas muaj qhov style A1, yog li koj tuaj yeem tawm ntawm qhov kev sib cav thib ob thiab tsom mus rau thawj.
Yog li cia peb rov qab mus rau peb cov ntaub ntawv muag khoom. Yog tias koj nco qab, ces txhua daim ntawv tshaj tawm yog ib lub rooj sib cais nyob rau ntawm ib daim ntawv cais. Rau cov qauv ua haujlwm kom raug, koj yuav tsum sau koj lub rooj (lossis ntau yam), thiab txhua lub npe yuav tsum muaj ib feem. Piv txwv li, zoo li no: CA_Sales, FL_Kev muag khoom, TX_Sales thiab lwm yam. Raws li koj tuaj yeem pom, "_Sales" muaj nyob hauv txhua lub npe.
muaj nuj nqi TSEEM CEEB txuas tus nqi hauv kab D thiab kab ntawv "_Sales", yog li qhia VPR nyob rau hauv lub rooj mus nrhiav. Yog tias cell D3 muaj tus nqi "FL", tus qauv yuav tshawb xyuas lub rooj FL_Kev muag khoom, yog "CA" - nyob rau hauv lub rooj CA_Sales thiab thiaj li nyob.
Qhov tshwm sim ntawm cov haujlwm VPR и TSEEM CEEB yuav yog cov hauv qab no:
Yog tias cov ntaub ntawv nyob hauv cov phau ntawv Excel sib txawv, ces koj yuav tsum ntxiv lub npe ntawm phau ntawv ua ntej lub npe muaj npe, piv txwv li:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Yog muaj nuj nqi TSEEM CEEB hais txog lwm phau ntawv ua haujlwm, phau ntawv ua haujlwm yuav tsum qhib. Yog tias nws raug kaw, lub luag haujlwm yuav qhia txog qhov yuam kev. #REF! (#SSYL!).