Tag nrho cov lus zais ntawm Excel VLOOKUP muaj nuj nqi rau kev nrhiav cov ntaub ntawv hauv ib lub rooj thiab muab rho tawm mus rau lwm qhov

2024 Tus sau: Malcolm Clapton | [email protected]. Kawg hloov kho: 2023-12-17 03:58

Tom qab nyeem tsab xov xwm, koj yuav tsis tsuas kawm paub yuav ua li cas nrhiav tau cov ntaub ntawv hauv Excel spreadsheet thiab muab rho tawm mus rau lwm qhov, tab sis kuj muaj cov txheej txheem uas tuaj yeem siv ua ke nrog VLOOKUP muaj nuj nqi.

Thaum ua haujlwm hauv Excel, feem ntau yuav tsum tau nrhiav cov ntaub ntawv hauv ib lub rooj thiab muab rho tawm mus rau lwm qhov. Yog tias koj tseem tsis paub yuav ua li cas, tom qab nyeem tsab xov xwm, koj yuav tsis tsuas yog kawm yuav ua li cas thiaj ua tau qhov no, tab sis kuj paub tias nyob rau hauv cov xwm txheej twg koj tuaj yeem nyem qhov kev ua tau zoo tshaj plaws ntawm qhov system. Feem ntau ntawm cov txheej txheem zoo heev uas yuav tsum tau siv ua ke nrog VLOOKUP muaj nuj nqi raug txiav txim siab.

Txawm hais tias koj tau siv VLOOKUP muaj nuj nqi ntau xyoo, tom qab ntawd nrog cov qib siab ntawm qhov tshwm sim no tsab xov xwm yuav muaj txiaj ntsig zoo rau koj thiab yuav tsis tso koj tsis txaus ntseeg. Piv txwv li, ua tus kws tshaj lij IT, thiab tom qab ntawd ua tus thawj coj hauv IT, Kuv tau siv VLOOKUP tau 15 xyoo, tab sis kuv tsuas yog tswj hwm txhua qhov nuances tam sim no, thaum kuv pib qhia cov neeg Excel ntawm kev tshaj lij.

VLOOKUP yog ib tug abbreviation rau vntsug NSkev tshuaj xyuas. Ib yam li ntawd, VLOOKUP - Vertical LOOKUP. Lub npe ntawm cov haujlwm qhia peb tias nws tshawb hauv kab ntawm lub rooj (vertically - iterating hla kab thiab kho kab), thiab tsis nyob rau hauv kab (kab rov tav - iterating hla kab thiab kho kab). Nws yuav tsum tau muab sau tseg tias VLOOKUP muaj ib tug muam - ib tug dab tuag duckling uas yuav tsis dhau los ua ib tug swan - qhov no yog lub HLOOKUP muaj nuj nqi. HLOOKUP, as opposed to VLOOKUP, ua txoj kev tshawb nrhiav kab rov tav, tab sis lub tswv yim ntawm Excel (thiab qhov tseeb lub tswv yim ntawm cov ntaub ntawv koom haum) txhais tau tias koj cov ntxhuav muaj tsawg kab thiab ntau kab ntxiv. Tias yog vim li cas peb yuav tsum tau tshawb nrhiav los ntawm kab ntau zaus ntau dua li ntawm kab. Yog tias koj siv HLO ua haujlwm ntau dhau hauv Excel, nws zoo li koj tsis nkag siab qee yam hauv lub neej no.

Syntax

Lub VLOOKUP muaj nuj nqi muaj plaub yam tsis muaj:

= VLOOKUP (;; [;]), ntawm no:

- tus nqi xav tau (tsis tshua muaj) lossis siv rau lub xov tooj ntawm tes uas muaj tus nqi xav tau (feem ntau ntawm cov xwm txheej);

- siv rau ntau lub hlwb (ob-dimensional array), nyob rau hauv FIRST (!) kem uas tus nqi parameter yuav raug tshawb nrhiav;

- kab zauv nyob rau hauv ntau yam uas tus nqi yuav rov qab los;

- qhov no yog ib qho tseem ceeb heev uas teb cov lus nug seb puas yog thawj kab ntawm qhov ntau yog txheeb nyob rau hauv ascending kev txiav txim. Yog tias qhov array raug txheeb xyuas, peb qhia tus nqi TRUE lossis 1, txwv tsis pub - FALSE lossis 0. Yog tias qhov ntsuas no raug tshem tawm, nws ua rau 1.

Kuv twv tias ntau tus neeg uas paub VLOOKUP muaj nuj nqi zoo li flaky, tom qab nyeem cov lus piav qhia ntawm qhov thib plaub parameter, lawv yuav tsis xis nyob, vim lawv tau siv los pom nws hauv ib daim ntawv sib txawv me ntsis: feem ntau lawv tham txog qhov sib tw tiag tiag thaum tshawb nrhiav (FALSE lossis 0) lossis ntau yam scan (TRUE lossis 1).

Tam sim no koj yuav tsum tau strain thiab nyeem cov kab lus tom ntej ob peb zaug kom txog thaum koj hnov lub ntsiab lus ntawm qhov tau hais mus rau qhov kawg. Txhua lo lus tseem ceeb nyob ntawd. Piv txwv yuav pab koj xam nws.

VLOOKUP tus qauv ua haujlwm raws nraim li cas?

• Formula type I. Yog tias qhov ntsuas kawg raug tshem tawm lossis teev sib npaug li 1, ces VLOOKUP xav tias thawj kab yog txheeb raws li qhov nce mus, yog li kev tshawb nrhiav nres ntawm kab uas tam sim ntawd ua ntej kab uas muaj tus nqi ntau dua li qhov xav tau … Yog tias tsis pom cov hlua zoo li no, kab kawg ntawm qhov ntau yuav rov qab los.

  

• Formula II. Yog tias qhov ntsuas kawg tau teev tseg raws li 0, tom qab ntawd VLOOKUP scans thawj kab ntawm cov array ua ntu zus thiab tam sim ntawd nres qhov kev tshawb fawb thaum pom thawj qhov tseeb nrog qhov ntsuas, txwv tsis pub # N / A (# N / A) yuam kev code rov qab los.

  

Formulas workflows

VPR hom I

VPR type II

Corollaries rau cov qauv ntawm daim ntawv I

1. Cov qauv siv tau los faib cov txiaj ntsig thoob plaws ntau yam.
2. Yog tias thawj kab ntawv muaj qhov sib npaug thiab txheeb xyuas kom raug, ces qhov kawg ntawm cov kab uas muaj qhov sib npaug yuav raug xa rov qab.
3. Yog tias koj tshawb nrhiav tus nqi uas pom tseeb ntau dua li thawj kab tuaj yeem muaj, ces koj tuaj yeem yooj yim pom kab kawg ntawm lub rooj, uas tuaj yeem muaj txiaj ntsig zoo.
4. Qhov kev pom no yuav rov qab qhov yuam kev # N / A nkaus xwb yog tias nws tsis pom tus nqi tsawg dua lossis sib npaug rau qhov xav tau.
5. Nws yog qhov nyuaj rau kev nkag siab tias cov qauv rov qab qhov tsis raug yog tias koj cov array tsis raug txheeb xyuas.

Corollaries rau cov qauv ntawm hom II

Yog tias tus nqi xav tau tshwm sim ntau zaus hauv thawj kab ntawm cov array, ces tus qauv yuav xaiv thawj kab rau cov ntaub ntawv tom ntej.

VLOOKUP kev ua haujlwm

Koj tau mus txog qhov kawg ntawm tsab xov xwm. Nws yuav zoo li, zoo, qhov txawv yog dab tsi yog tias kuv teev xoom lossis ib qho raws li qhov ntsuas kawg? Yeej, txhua leej txhua tus qhia, ntawm chav kawm, xoom, txij li qhov no yog qhov ua tau zoo heev: koj tsis tas yuav txhawj xeeb txog kev txheeb xyuas thawj kab ntawm cov array, koj tuaj yeem pom tam sim ntawd seb tus nqi puas pom lossis tsis pom. Tab sis yog tias koj muaj ntau txhiab VLOOKUP cov qauv ntawm koj daim ntawv, koj yuav pom tias VLOOKUP II qeeb. Nyob rau tib lub sijhawm, feem ntau txhua tus pib xav tias:

• Kuv xav tau lub computer muaj zog dua;
• Kuv xav tau cov mis sai dua, piv txwv li, ntau tus neeg paub txog INDEX + MATCH, uas yog supposedly sai dua los ntawm measly 5-10%.

Thiab ob peb tus neeg xav tias sai li sai tau thaum koj pib siv VLOOKUP ntawm hom kuv thiab xyuas kom meej tias thawj kab yog txheeb los ntawm ib qho twg, qhov ceev ntawm VLOOKUP yuav nce 57 zaug. Kuv tabtom sau cov lus - FIFTY-SEVEN TIMES! Tsis yog 57%, tab sis 5,700%. Kuv tshawb xyuas qhov tseeb no ntseeg tau heev.

Cov lus zais ntawm kev ua haujlwm nrawm no yog nyob rau hauv qhov tseeb tias kev tshawb nrhiav algorithm muaj txiaj ntsig zoo tuaj yeem siv rau ntawm kev txheeb cais, uas yog hu ua binary search (halving method, dichotomy method). Yog li VLOOKUP ntawm hom kuv siv nws, thiab VLOOKUP ntawm hom II tshawb nrhiav yam tsis muaj kev ua kom zoo dua. Ib yam yog qhov tseeb rau MATCH muaj nuj nqi, uas suav nrog qhov tsis zoo sib xws, thiab LOOKUP muaj nuj nqi, uas tsuas yog ua haujlwm ntawm kev txheeb cais thiab suav nrog hauv Excel rau kev sib raug zoo nrog Lotus 1-2-3.

Qhov tsis zoo ntawm cov qauv

Qhov tsis zoo ntawm VLOOKUP yog qhov pom tseeb: thawj zaug, nws tshawb nrhiav tsuas yog hauv thawj kab ntawm cov kab lus teev tseg, thiab thib ob, tsuas yog mus rau sab xis ntawm kab ntawv no. Thiab raws li koj nkag siab, nws yuav tshwm sim zoo tias kab ntawv uas muaj cov ntaub ntawv tsim nyog yuav nyob rau sab laug ntawm kab uas peb yuav tau saib. Qhov twb tau hais ua ke ntawm cov qauv INDEX + MATCH yog tsis muaj qhov tsis zoo no, uas ua rau nws yooj yim tshaj plaws rau kev rho tawm cov ntaub ntawv los ntawm cov ntxhuav hauv kev sib piv nrog VLOOKUP (VLOOKUP).

Qee yam ntawm kev siv cov mis hauv lub neej tiag

Kev tshawb nrhiav ntau yam

Ib qho piv txwv classic ntawm kev tshawb nrhiav ntau yam yog txoj haujlwm ntawm kev txiav txim siab luv nqi los ntawm kev txiav txim loj.

Nrhiav cov kab ntawv

Tau kawg, VLOOKUP zoo nkaus li tsis yog rau cov lej xwb, tab sis kuj rau cov ntawv nyeem. Nws yuav tsum tau borne nyob rau hauv lub siab hais tias tus qauv tsis txawv ntawm cov ntaub ntawv ntawm cov cim. Yog tias koj siv wildcards, koj tuaj yeem teeb tsa kev tshawb nrhiav fuzzy. Muaj ob lub cim: "?" - hloov ib qho cim hauv kab ntawv, "*" - hloov ib tus lej ntawm cov cim.

Sib ntaus sib tua qhov chaw

Cov lus nug feem ntau tau tsa los daws qhov teeb meem ntawm qhov chaw ntxiv thaum tshawb nrhiav. Yog tias lub rooj saib xyuas tseem tuaj yeem tshem tawm ntawm lawv, ces thawj qhov ntsuas ntawm VLOOKUP cov mis tsis yog rau koj ib txwm. Yog li ntawd, yog tias muaj kev pheej hmoo ntawm clogging lub hlwb nrog qhov chaw ntxiv, ces koj tuaj yeem siv TRIM muaj nuj nqi los tshem nws.

Cov ntaub ntawv sib txawv

Yog tias thawj qhov tsis zoo ntawm VLOOKUP muaj nuj nqi hais txog lub xov tooj ntawm tes uas muaj tus lej, tab sis uas tau khaws cia rau hauv lub xov tooj ntawm tes raws li cov ntawv nyeem, thiab thawj kab ntawm cov array muaj cov lej hauv cov qauv raug, ces kev tshawb nrhiav yuav ua tsis tiav. Qhov xwm txheej tsis sib xws kuj ua tau. Qhov teeb meem tuaj yeem daws tau yooj yim los ntawm kev txhais cov parameter 1 rau hauv cov ntawv xav tau:

= VLOOKUP(−− D7; Cov khoom! $ A $ 2: $ C $ 5; 3; 0) - yog D7 muaj cov ntawv thiab cov lus muaj cov lej;

= VLOOKUP(D7 & ""); Cov khoom!$A$2:$C$5; 3; 0) - thiab vice versa.

Los ntawm txoj kev, koj tuaj yeem txhais cov ntawv rau hauv ib tus lej hauv ntau txoj hauv ib zaug, xaiv:

• Ob chav negation -D7.
• Kev sib tw los ntawm ib qho D7 * 1.
• Zero ntxiv D7 + 0.
• Nce mus rau thawj lub zog D7^1.

Hloov tus lej rau cov ntawv yog ua tiav los ntawm kev sib txuas nrog cov hlua khoob, uas yuam Excel los hloov cov ntaub ntawv hom.

Yuav Ua Li Cas # N / A

Qhov no yooj yim heev los ua nrog IFERROR muaj nuj nqi.

Piv txwv li: = IFERROR (VLOOKUP (D7; Khoom! $ A $ 2: $ C $ 5; 3; 0); "").

Yog tias VLOOKUP rov qab qhov yuam kev code # N / A, tom qab ntawd IFERROR yuav cuam tshuam nws thiab hloov qhov ntsuas 2 (qhov no, ib txoj hlua khoob), thiab yog tias tsis muaj qhov yuam kev tshwm sim, qhov haujlwm no yuav ua piv txwv tias nws tsis muaj nyob txhua qhov, tab sis muaj tsuas yog VLOOKUP uas rov qab qhov tshwm sim.

Array

Feem ntau lawv tsis nco qab ua qhov kev siv ntawm array kiag li, thiab thaum ncav lub array "floats". Nco ntsoov siv $ A $ 2: $ C $ 5 es tsis txhob A2: C5.

Nws yog ib lub tswv yim zoo los tso cov ntaub ntawv siv rau ntawm ib daim ntawv cais ntawm phau ntawv ua haujlwm. Nws tsis tau underfoot, thiab nws yuav muaj kev nyab xeeb dua.

Ib lub tswv yim zoo dua yuav yog tshaj tawm cov array no ua ib lub npe nrov.

Ntau tus neeg siv, thaum qhia txog ib qho array, siv kev tsim kho xws li A: C, qhia tag nrho cov kab. Txoj hauv kev no muaj txoj cai kom muaj nyob, txij li koj tau txais kev cawmdim los ntawm kev taug qab qhov tseeb tias koj cov array suav nrog tag nrho cov hlua uas xav tau. Yog tias koj ntxiv kab rau hauv daim ntawv nrog cov thawj array, ces qhov ntau teev raws li A: C tsis tas yuav hloov kho. Tau kawg, qhov kev tsim kho syntactic no yuam Excel ua haujlwm me ntsis ntau dua li qhov qhia meej meej, tab sis qhov nyiaj siv ua haujlwm no tuaj yeem tsis quav ntsej. Peb tab tom tham txog pua pua ntawm ib thib ob.

Zoo, ntawm lub verge ntawm ntse heev - los npaj cov array nyob rau hauv daim ntawv.

Siv cov COLUMN muaj nuj nqi los qhia cov kab ntawv kom rho tawm

Yog tias lub rooj uas koj tab tom khaws cov ntaub ntawv siv VLOOKUP muaj cov qauv zoo ib yam li cov lus nrhiav, tab sis tsuas muaj tsawg kab, ces koj tuaj yeem siv COLUMN () ua haujlwm hauv VLOOKUP kom suav cov lej ntawm cov kab yuav rov qab tau. Hauv qhov no, txhua tus qauv VLOOKUP yuav zoo ib yam (hloov rau thawj qhov ntsuas, uas hloov pauv tau)! Nco ntsoov tias thawj qhov parameter muaj cov kab ke tsis meej.

Tsim tus yuam sij sib xyaw nrog & "|" &

Yog tias nws tsim nyog los tshawb nrhiav los ntawm ob peb kab nyob rau tib lub sijhawm, nws yog qhov tsim nyog los ua ib qho tseem ceeb sib xyaw rau kev tshawb nrhiav. Yog tias tus nqi xa rov qab tsis yog cov ntawv nyeem (raws li qhov "Code" teb), tab sis tus lej, ces qhov yooj yim dua SUMIFS formula yuav haum rau qhov no thiab cov ntsiab lus sib xyaw yuav tsis tas yuav tsum tau ua txhua.

Nov yog kuv thawj tsab xov xwm rau Lifehacker. Yog tias koj nyiam, kuv caw koj tuaj xyuas, thiab kuj zoo siab nyeem hauv cov lus hais txog koj cov lus zais ntawm kev siv VLOOKUP muaj nuj nqi thiab lwm yam. Ua tsaug.:)