Leej twg yog tub nkeeg dhau los lossis tsis muaj sijhawm nyeem - saib cov yeeb yaj kiab. Cov ntsiab lus thiab nuances yog nyob rau hauv cov ntawv hauv qab no.
Formulation ntawm qhov teeb meem
Yog li, peb muaj ob lub rooj - xaj rooj и daim ntawv teev nqi:
Lub luag haujlwm yog hloov tus nqi los ntawm tus nqi teev rau hauv lub rooj ntawm kev txiav txim txiav, tsom rau lub npe ntawm cov khoom kom tom qab ntawd koj tuaj yeem suav tus nqi.
tshuaj
Hauv Excel muaj nuj nqi teev, nyob rau hauv qeb References thiab arrays (Nrhiav thiab siv) muaj haujlwm VPR (VLOOKUP).Qhov kev ua haujlwm no saib rau tus nqi muab (hauv peb qhov piv txwv, qhov no yog lo lus "Apples") nyob rau hauv kab laug sab ntawm cov lus teev tseg (nqi teev) txav los ntawm sab saum toj mus rau hauv qab thiab, pom nws, qhia cov ntsiab lus ntawm lub xov tooj ntawm tes. (23 rubles).Schematically, lub lag luam ntawm no muaj nuj nqi yuav sawv cev Yog li:
Txhawm rau kom yooj yim ntawm kev siv ntxiv ntawm qhov ua haujlwm, ua ib yam ntawm ib zaug - muab qhov ntau ntawm cov hlwb hauv cov nqi teev koj tus kheej lub npe. Ua li no, xaiv tag nrho cov hlwb ntawm daim ntawv teev nqi tshwj tsis yog rau "header" (G3: H19), xaiv los ntawm cov ntawv qhia zaub mov Ntxig – Lub Npe – Muab (Insert — Name — Define) lossis nias CTRL + F3 thiab sau txhua lub npe (tsis muaj qhov chaw) nyiam nqe… Tam sim no, yav tom ntej, koj tuaj yeem siv lub npe no los txuas rau daim ntawv teev nqi.
Tam sim no peb siv lub luag haujlwm VPR… Xaiv lub cell uas nws yuav nkag mus (D3) thiab qhib lub tab Formulas – Function Insertion (Formulas - Insert Function)… Hauv qeb References thiab arrays (Saib thiab siv) nrhiav txoj haujlwm VPR (VLOOKUP) thiab nias OK… Ib lub qhov rais rau kev nkag mus rau cov lus sib cav rau txoj haujlwm yuav tshwm sim:
Peb sau lawv nyob rau hauv lem:
- Xav tau tus nqi (Nrhiav tus nqi) - lub npe ntawm cov khoom uas muaj nuj nqi yuav tsum pom nyob rau hauv kab laug sab ntawm daim ntawv teev nqi. Hauv peb cov ntaub ntawv, lo lus "Apps" los ntawm cell B3.
- rooj (Table Array) - ib lub rooj los ntawm qhov xav tau qhov tseem ceeb uXNUMXbuXNUMXbare coj, uas yog, peb daim ntawv teev nqi. Rau kev siv, peb siv peb tus kheej lub npe "Nqi" muab ua ntej. Yog tias koj tsis tau muab lub npe, koj tuaj yeem xaiv lub rooj, tab sis tsis txhob hnov qab nias lub khawm F4los pin qhov txuas nrog duas kos npe, vim tsis li ntawd, nws yuav swb thaum luam peb cov qauv mus rau lwm lub hlwb hauv kab D3:D30.
- Column_num (Kem Index Number) - serial tooj (tsis yog ib tsab ntawv!) ntawm kab ntawv nyob rau hauv daim ntawv teev nqi uas peb yuav coj tus nqi nqi. Thawj kem ntawm daim ntawv teev nqi nrog cov npe yog tus lej 1, yog li peb xav tau tus nqi los ntawm kab zauv 2.
- interval_lookup (Range Lookup) - Tsuas yog ob qhov txiaj ntsig tuaj yeem nkag rau hauv daim teb no: FALSE lossis TRUE:
- Yog tus nqi nkag 0 or dag (FALSE), ces qhov tseeb qhov no txhais tau hais tias tsuas yog kev tshawb fawb tau tso cai sib phim, piv txwv li yog tias qhov ua haujlwm tsis pom cov khoom tsis yog tus qauv tau teev tseg hauv cov lus txiav txim hauv daim ntawv teev nqi (yog tias "txiv maj phaub" nkag mus, piv txwv li), nws yuav tsim qhov # N / A (tsis muaj ntaub ntawv) yuam kev.
- Yog tus nqi nkag 1 or TRUE (TSIS YOG), ces qhov no txhais tau hais tias koj tso cai rau kev tshawb nrhiav tsis yog qhov tseeb, tab sis kwv yees sib phim, piv txwv li, nyob rau hauv cov ntaub ntawv ntawm "txiv maj phaub", muaj nuj nqi yuav sim mus nrhiav tau ib yam khoom nrog ib lub npe uas yog ze li sai tau rau "txiv maj phaub" thiab xa rov qab tus nqi rau lub npe no. Feem ntau, xws li ib qho kev hloov pauv tuaj yeem ua kom yuam kev rau tus neeg siv los ntawm kev hloov tus nqi ntawm cov khoom tsis raug uas tau muaj tiag! Yog li rau feem ntau cov teeb meem kev lag luam tiag tiag, kwv yees kev tshawb nrhiav yog qhov zoo tshaj tsis pub. Qhov tshwj xeeb yog thaum peb tab tom nrhiav cov lej thiab tsis yog ntawv nyeem - piv txwv li, thaum xam cov Kauj Ruam Nqis.
Txhua yam! Nws tseem yuav nias OK thiab luam cov haujlwm nkag mus rau tag nrho kab.
# N / A yuam kev thiab lawv kev tsuj
muaj nuj nqi VPR (VLOOKUP) rov #N/A yuam kev (#N/A) yog tias ib:
- Kev tshawb nrhiav tseeb tau qhib (kev sib cav Lub sijhawm saib = 0) thiab lub npe xav tau tsis nyob hauv rooj.
- Kev tshawb nrhiav ntxhib suav nrog (Lub sijhawm saib = 1), tab sis rooj, nyob rau hauv uas qhov kev tshawb fawb yog qhov chaw tsis txheeb nyob rau hauv ascending kev txiav txim ntawm lub npe.
- Cov qauv ntawm lub xov tooj ntawm tes uas yuav tsum muaj tus nqi ntawm lub npe los ntawm (piv txwv li, B3 hauv peb rooj plaub) thiab cov qauv ntawm cov cell ntawm thawj kab (F3: F19) ntawm lub rooj sib txawv (piv txwv li, cov lej thiab cov ntawv nyeem. ). Cov ntaub ntawv no tshwj xeeb tshaj yog thaum siv cov lej lej (tus lej lej, tus lej cim, hnub, thiab lwm yam) es tsis txhob siv cov npe ntawv. Hauv qhov no, koj tuaj yeem siv cov haujlwm Ч и TEXT hloov cov ntaub ntawv tawm tswv yim. Nws yuav zoo li no:
=VLOOKUP(TEXT(B3),price,0)
Koj tuaj yeem nyeem ntxiv txog qhov no ntawm no.
- Cov haujlwm tsis tuaj yeem nrhiav tus nqi uas yuav tsum tau ua vim tias cov lej muaj qhov chaw lossis pom tsis pom cov cim uas tsis tau luam tawm (kab tawg, thiab lwm yam). Hauv qhov no, koj tuaj yeem siv cov ntawv sau ua haujlwm TRIM (TRIM) и SAU(CLEAN) tshem lawv:
=VLOOKUP(TRIMSPACES(CLEAN(B3)), nqe, 0)
=VLOOKUP(TRIM(CLEAN(B3));price; 0)
Txhawm rau txwv cov lus yuam kev # N / A (#N/A) nyob rau hauv cov ntaub ntawv uas muaj nuj nqi nrhiav tsis tau ib tug meej match, koj muaj peev xwm siv lub functionality IFERROR (IFERROR)… Yog li, piv txwv li, qhov kev tsim kho no cuam tshuam ib qho yuam kev tsim los ntawm VLOOKUP thiab hloov lawv nrog xoom:
= IFERROR (VLOOKUP (B3, nqe, 2, 0), 0)
= IFERROR (VLOOKUP (B3; nqe; 2; 0); 0)
PS
Yog tias koj xav tau rho tawm tsis yog ib qho nqi, tab sis tag nrho cov teeb tsa ib zaug (yog tias muaj ntau qhov sib txawv), ces koj yuav tsum shamanize nrog cov qauv array. los yog siv XLOOKUP feature tshiab los ntawm Office 365.
- Ib qho kev txhim kho ntawm VLOOKUP muaj nuj nqi (VLOOKUP 2).
- Kev suav ceev ntawm cov kauj ruam (ntau) luv nqi siv VLOOKUP muaj nuj nqi.
- Yuav ua li cas thiaj ua tau "sab laug VLOOKUP" siv cov haujlwm INDEX thiab MATCH
- Yuav siv VLOOKUP li cas los sau cov ntaub ntawv nrog cov ntaub ntawv los ntawm cov npe
- Yuav ua li cas rub tawm tsis yog thawj zaug, tab sis tag nrho cov txiaj ntsig ntawm lub rooj ib zaug
- VLOOKUP2 thiab VLOOKUP3 ua haujlwm los ntawm PLEX add-on