VLOOKUP Muaj nuj nqi hauv Excel - Pib Phau Ntawv Qhia: Syntax thiab Piv txwv

Niaj hnub no peb tab tom pib cov kab lus piav qhia txog ib qho ntawm cov txiaj ntsig zoo tshaj plaws ntawm Excel - VPR (VLOOKUP). Txoj haujlwm no, tib lub sijhawm, yog ib qho nyuaj tshaj plaws thiab nkag siab tsawg kawg nkaus.

Nyob rau hauv no nyeem ntawm VPR Kuv yuav sim nteg tawm cov ntsiab lus yooj yim li sai tau txhawm rau ua kom cov txheej txheem kev kawm kom meej li sai tau rau cov neeg siv tsis tau paub. Tsis tas li ntawd, peb yuav kawm ntau yam piv txwv nrog Excel cov qauv uas yuav ua rau pom qhov kev siv ntau tshaj plaws rau kev ua haujlwm VPR.

  • General piav qhia thiab syntax
  • VLOOKUP Piv txwv
    • Yuav siv VLOOKUP li cas los tshawb hauv lwm daim ntawv Excel
    • Nrhiav hauv lwm phau ntawv ua haujlwm nrog VLOOKUP
    • Yuav ua li cas siv cov npe lossis cov lus hauv cov qauv nrog VLOOKUP
    • Siv Wildcards hauv VLOOKUP Formulas
  • Muaj tseeb lossis kwv yees qhov sib tw hauv VLOOKUP muaj nuj nqi
  • VLOOKUP hauv Excel - koj yuav tsum nco ntsoov qhov no!

VLOOKUP muaj nuj nqi hauv Excel - kev piav qhia dav dav thiab syntax

Yog li ntawd yog dab tsi VPR? Zoo, ua ntej ntawm tag nrho cov, nws yog Excel muaj nuj nqi. Nws ua li cas? Nws saib tus nqi koj teev thiab xa rov qab tus nqi sib xws los ntawm lwm kab. Technically hais, VPR saib tus nqi hauv thawj kab ntawm qhov muab ntau thiab xa cov txiaj ntsig los ntawm lwm kab hauv tib kab.

Hauv kev siv ntau tshaj plaws, kev ua haujlwm VPR tshawb nrhiav cov ntaub ntawv rau ib tus cim tshwj xeeb thiab rho tawm qee cov ntaub ntawv ntsig txog nws los ntawm cov ntaub ntawv.

Thawj tsab ntawv hauv lub npe ua haujlwm VPR (VLOOKUP) txhais tau tias Вntsug (Vntsug). Los ntawm nws koj tuaj yeem paub qhov txawv VPR los ntawm GPR (HLOOKUP), uas tshawb nrhiav tus nqi nyob rau hauv kab saum toj kawg nkaus ntawm ntau yam − Гkab rov tav (Hkab rov tav).

muaj nuj nqi VPR Muaj nyob rau hauv Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, thiab Excel 2000.

Syntax ntawm VLOOKUP muaj nuj nqi

muaj nuj nqi VPR (VLOOKUP) muaj cov syntax hauv qab no:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Raws li koj tau pom, muaj nuj nqi VPR hauv Microsoft Excel muaj 4 txoj kev xaiv (lossis sib cav). Thawj peb yog qhov yuav tsum tau ua, qhov kawg yog xaiv tau.

  • saib xyuas_valalue (lookup_value) – Tus nqi nrhiav. Qhov no tuaj yeem yog tus nqi (tus lej, hnub tim, ntawv nyeem) lossis kev siv xovtooj ntawm tes (muaj tus nqi nrhiav), lossis tus nqi xa rov qab los ntawm qee qhov haujlwm Excel. Piv txwv li, cov qauv no yuav nrhiav tus nqi 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

Yog hais tias tus nqi lookup tsawg dua tus nqi tsawg tshaj plaws nyob rau hauv thawj kem ntawm qhov ntau tau ntsia, muaj nuj nqi VPR yuav qhia qhov yuam kev #AT (#N/A).

  • table_cov (table) – ob lossis ntau kab ntawm cov ntaub ntawv. Nco ntsoov, muaj nuj nqi VPR ib txwm saib rau tus nqi nyob rau hauv thawj kab ntawm qhov ntau muab rau hauv qhov kev sib cav table_cov ( rooj). Qhov kev pom tau tuaj yeem muaj ntau yam ntaub ntawv, xws li ntawv nyeem, hnub tim, tus lej, booleans. Qhov kev ua haujlwm yog cov ntaub ntawv tsis txaus ntseeg, lub ntsiab lus sab sauv thiab sab saud yog suav tias yog tib yam. Yog li peb cov qauv yuav nrhiav tus nqi 40 hauv cell los ntawm A2 rau A15, vim A yog thawj kab ntawm qhov ntau A2:B15 muab rau hauv qhov kev sib cav table_cov (tab):

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • Col_index_num (column_number) yog tus naj npawb ntawm kem hauv qhov muab ntau los ntawm qhov uas tus nqi hauv kab pom yuav raug xa rov qab. Cov kab laug sab hauv qhov muab ntau yog 1, kab thib ob yog 2, kab thib peb yog 3 thiab lwm yam. Tam sim no koj tuaj yeem nyeem tag nrho cov qauv:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    Formula nrhiav tus nqi 40 hauv qhov ntau A2: UA 15 thiab xa rov qab tus nqi sib thooj los ntawm kab B (vim B yog kab thib ob hauv qhov ntau A2: B15).

Yog tus nqi ntawm qhov kev sib cav Col_index_num (column_number) tsawg dua 1ces VPR yuav qhia qhov yuam kev #VALUE! (#VALUE!). Thiab yog hais tias nws yog ntau tshaj tus naj npawb ntawm kab nyob rau hauv ntau yam table_cov (cov lus), cov haujlwm yuav rov qab ua yuam kev #REF! (#LINK!).

  • range_lookup (range_lookup) – txiav txim seb yuav nrhiav dab tsi:
    • kev sib tw, kev sib cav yuav tsum sib npaug cuav (FALSE);
    • approximate match, sib cav sib npaug TXOJ CAI (TRUE) lossis tsis tau teev tseg.

    Qhov no parameter yog xaiv tau, tab sis tseem ceeb heev. Tom qab ntawd hauv qhov kev qhia no rau VPR Kuv yuav qhia koj qee qhov piv txwv piav qhia yuav ua li cas sau cov qauv rau kev nrhiav qhov tseeb thiab kwv yees qhov sib tw.

VLOOKUP Piv txwv

Kuv vam tias cov haujlwm VPR ua me ntsis meej rau koj. Tam sim no cia peb saib qee qhov kev siv VPR hauv cov qauv nrog cov ntaub ntawv tiag.

Yuav siv VLOOKUP li cas los tshawb hauv lwm daim ntawv Excel

Hauv kev xyaum, cov qauv nrog rau kev ua haujlwm VPR tsis tshua muaj siv los tshawb nrhiav cov ntaub ntawv ntawm tib daim ntawv ua haujlwm. Ntau zaus tshaj tsis, koj yuav tau saib thiab khaws cov txiaj ntsig sib xws los ntawm lwm daim ntawv.

Nyob rau hauv thiaj li yuav siv VPR, tshawb hauv lwm daim ntawv Microsoft Excel, Koj yuav tsum muaj kev sib cav table_cov (cov lus) qhia lub npe ntawm daim ntawv nrog rau qhov exclamation cim ua raws li ntau ntawm cov hlwb. Piv txwv li, cov qauv hauv qab no qhia tau hais tias qhov ntau A2: B15 yog nyob rau ntawm daim ntawv npe Ntawv 2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Tau kawg, daim ntawv npe tsis tas yuav tsum tau nkag mus manually. Cia li pib ntaus tus qauv, thiab thaum nws los txog rau qhov kev sib cav table_cov (lub rooj), hloov mus rau daim ntawv xav tau thiab xaiv qhov xav tau ntau ntawm cov hlwb nrog tus nas.

Cov qauv qhia hauv lub screenshot hauv qab no saib rau cov ntawv "Cov Khoom 1" hauv kab A (nws yog 1 kab ntawm kab A2: B9) ntawm daim ntawv ua haujlwm nqi.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Thov nco ntsoov tias thaum tshawb nrhiav tus nqi ntawv, koj yuav tsum kaw nws hauv cov lus hais (""), raws li feem ntau ua hauv Excel cov qauv.

Rau kev sib cav table_cov (cov lus) nws yog ntshaw kom siv cov ntaub ntawv tseeb (nrog rau $ kos npe). Hauv qhov no, qhov kev tshawb nrhiav yuav nyob twj ywm tsis hloov thaum luam cov qauv mus rau lwm lub hlwb.

Nrhiav hauv lwm phau ntawv ua haujlwm nrog VLOOKUP

Ua haujlwm VPR ua haujlwm ntawm ob phau ntawv ua haujlwm Excel, koj yuav tsum qhia lub npe ntawm phau ntawv ua haujlwm hauv square brackets ua ntej lub npe ntawv.

Piv txwv li, hauv qab no yog ib qho qauv uas saib rau tus nqi 40 ntawm daim ntawv Ntawv 2 hauv phau ntawv Numbers.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Nov yog txoj hauv kev yooj yim tshaj plaws los tsim cov qauv hauv Excel nrog VPRuas txuas mus rau lwm phau ntawv ua haujlwm:

  1. Qhib ob phau ntawv. Qhov no tsis tas yuav tsum tau, tab sis nws yooj yim dua los tsim cov qauv no. Koj tsis xav sau lub npe ntawm phau ntawv ua haujlwm manually, koj puas yog? Tsis tas li ntawd, nws yuav tiv thaiv koj los ntawm kev yuam kev typos.
  2. Pib ntaus ntawv muaj nuj nqi VPRthiab thaum nws los txog rau kev sib cav table_cov (lub rooj), hloov mus rau lwm phau ntawv ua haujlwm thiab xaiv qhov kev tshawb nrhiav xav tau hauv nws.

Cov screenshot hauv qab no qhia cov qauv nrog cov kev tshawb nrhiav teeb tsa rau ntau yam hauv phau ntawv ua haujlwm PriceList.xlsx ntawm daim ntawv nqi.

muaj nuj nqi VPR yuav ua haujlwm txawm tias thaum koj kaw phau ntawv nrhiav haujlwm thiab tag nrho txoj hauv kev mus rau cov ntaub ntawv phau ntawv ua haujlwm tshwm nyob rau hauv daim ntawv qhia bar, raws li qhia hauv qab no:

Yog tias lub npe ntawm phau ntawv ua haujlwm lossis daim ntawv muaj qhov chaw, ces nws yuav tsum tau muab kaw rau hauv apostrophes:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Yuav ua li cas siv cov npe lossis cov lus hauv cov qauv nrog VLOOKUP

Yog tias koj npaj yuav siv tib qhov kev tshawb nrhiav ntau yam hauv ntau txoj haujlwm VPR, koj tuaj yeem tsim cov npe ntau thiab sau nws lub npe rau hauv cov qauv raws li kev sib cav table_cov ( rooj).

Txhawm rau tsim cov npe muaj npe, tsuas yog xaiv cov cell thiab sau lub npe tsim nyog hauv daim teb thawj lub npe, mus rau sab laug ntawm lub formula bar.

Tam sim no koj tuaj yeem sau cov qauv hauv qab no rau kev nrhiav tus nqi ntawm cov khoom Khoom 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Feem ntau cov npe ua haujlwm rau tag nrho Excel phau ntawv ua haujlwm, yog li tsis tas yuav qhia lub npe ntawm daim ntawv rau kev sib cav table_cov (cov lus), txawm tias cov qauv thiab cov kev tshawb fawb muaj nyob rau ntawm cov ntawv sib txawv. Yog tias lawv nyob hauv phau ntawv sib txawv, tom qab ntawd ua ntej lub npe ntawm ntau yam koj yuav tsum tau qhia lub npe ntawm phau ntawv ua haujlwm, piv txwv li, zoo li no:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Yog li cov qauv zoo li pom tseeb dua, pom zoo? Tsis tas li ntawd, siv cov npe muaj npe yog lwm txoj hauv kev zoo rau cov ntaub ntawv pov thawj tiag tiag vim tias cov npe npe tsis hloov pauv thaum koj luam cov qauv mus rau lwm lub hlwb. Qhov no txhais tau tias koj tuaj yeem paub tseeb tias qhov kev tshawb nrhiav ntau yam hauv cov qauv yuav nyob twj ywm kom raug.

Yog tias koj hloov ntau lub hlwb mus rau hauv daim ntawv qhia tag nrho Excel siv cov lus txib rooj (Table) tab Insertion (Ntxig), tom qab ntawd thaum koj xaiv ntau yam nrog tus nas, Microsoft Excel yuav cia li ntxiv cov npe kab (lossis lub npe lub rooj yog tias koj xaiv tag nrho cov lus) rau cov qauv.

Daim ntawv qhia tiav yuav zoo li no:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Los yog tej zaum txawm zoo li no:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Thaum siv cov npe muaj npe, cov kev sib txuas yuav taw tes rau tib lub hlwb txawm tias koj luam qhov twg los xij VPR hauv phau ntawv ua haujlwm.

Siv Wildcards hauv VLOOKUP Formulas

Raws li nrog ntau lwm cov haujlwm, VPR Koj tuaj yeem siv cov cim wildcard hauv qab no:

  • Lo lus nug cim (?) – hloov ib qho cim.
  • Asterisk (*) - hloov txhua ntu ntawm cov cim.

Siv Wildcards hauv Kev Ua Haujlwm VPR tuaj yeem pab tau ntau zaus, piv txwv li:

  • Thaum koj tsis nco qab raws nraim cov ntawv nyeem koj yuav tsum nrhiav.
  • Thaum koj xav nrhiav qee lo lus uas yog ib feem ntawm cov ntsiab lus ntawm lub xov tooj ntawm tes. Paub qhov ntawd VPR tshawb los ntawm cov ntsiab lus ntawm lub xov tooj ntawm tes tag nrho, zoo li qhov kev xaiv tau qhib Sib phim tag nrho cov ntsiab lus ntawm tes (Txhua lub xovtooj) hauv kev tshawb nrhiav Excel.
  • Thaum ib lub xovtooj muaj qhov chaw ntxiv ntawm qhov pib lossis qhov kawg ntawm cov ntsiab lus. Hauv qhov xwm txheej zoo li no, koj tuaj yeem rack koj lub hlwb rau lub sijhawm ntev, sim xyuas seb vim li cas cov mis tsis ua haujlwm.

Piv txwv 1: Nrhiav cov ntawv uas pib lossis xaus nrog qee cov cim

Cia peb hais tias koj xav tshawb nrhiav ib tus neeg siv khoom tshwj xeeb hauv cov ntaub ntawv hauv qab no. Koj tsis nco qab nws lub xeem, tab sis koj paub tias nws pib nrog "ack". Nov yog ib qho qauv uas yuav ua haujlwm zoo xwb:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Tam sim no koj paub tseeb tias koj tau pom lub npe raug, koj tuaj yeem siv tib lub qauv los nrhiav tus nqi them los ntawm cov neeg siv khoom no. Txhawm rau ua qhov no, tsuas yog hloov qhov kev sib cav thib peb ntawm txoj haujlwm VPR mus rau tus lej kab uas xav tau. Hauv peb qhov xwm txheej, qhov no yog kab C (3rd hauv qhov ntau):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Nov yog qee qhov piv txwv ntxiv nrog wildcards:

~ Nrhiav ib lub npe xaus hauv "txiv neej":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Nrhiav ib lub npe uas pib nrog "ad" thiab xaus nrog "tus tub":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Peb pom thawj lub npe hauv cov npe, suav nrog 5 tus cim:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Ua haujlwm VPR nrog wildcards ua haujlwm kom raug, raws li qhov kev sib cav thib plaub uas koj yuav tsum tau siv tas li cuav (FALSE). Yog tias qhov kev tshawb nrhiav muaj ntau dua ib tus nqi uas phim cov lus tshawb fawb nrog cov ntawv cim, ces thawj tus nqi pom yuav raug xa rov qab.

Piv txwv 2: Muab cov ntawv cim cim thiab cov ntawv xov tooj ntawm tes hauv VLOOKUP cov qauv

Tam sim no cia saib ib qho piv txwv me ntsis ntawm kev tshawb nrhiav siv cov haujlwm VPR los ntawm tus nqi hauv ib lub cell. Xav txog tias kab A yog ib daim ntawv tso cai yuam sij, thiab kab B yog ib daim ntawv teev npe uas muaj daim ntawv tso cai. Tsis tas li ntawd, koj muaj ib feem (ntau lub cim) ntawm qee yam ntawm daim ntawv tso cai yuam sij hauv cell C1, thiab koj xav nrhiav lub npe ntawm tus tswv.

Qhov no tuaj yeem ua tiav siv cov qauv hauv qab no:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Cov qauv no saib tus nqi ntawm tes C1 hauv qhov muab ntau thiab xa rov qab tus nqi sib thooj los ntawm kab B. Nco ntsoov tias hauv thawj qhov kev sib cav, peb siv cov ampersand (&) cim ua ntej thiab tom qab lub xov tooj siv los txuas cov kab ntawv.

Raws li koj tuaj yeem pom hauv daim duab hauv qab no, lub luag haujlwm VPR rov qab "Jeremy Hill" vim tias nws daim ntawv tso cai yuam sij muaj cov cim ntawm cov xov tooj ntawm tes C1.

Nco ntsoov tias qhov kev sib cav table_cov (table) nyob rau hauv lub screenshot saum toj no muaj lub npe ntawm lub rooj (Table7) es tsis txhob qhia ib tug ntau ntawm cov hlwb. Nov yog qhov peb tau ua hauv qhov piv txwv yav dhau los.

Muaj tseeb lossis kwv yees qhov sib tw hauv VLOOKUP muaj nuj nqi

Thiab thaum kawg, cia peb saib ze dua ntawm qhov kev sib cav zaum kawg uas tau teev tseg rau txoj haujlwm VPR - range_lookup (interval_view). Raws li tau hais thaum pib ntawm zaj lus qhia, qhov kev sib cav no tseem ceeb heev. Koj tuaj yeem tau txais cov txiaj ntsig sib txawv hauv tib lub mis nrog nws tus nqi TXOJ CAI (TRUE) lub cuav (FALSE).

Ua ntej, cia saib seb Microsoft Excel txhais li cas los ntawm qhov tseeb thiab kwv yees qhov sib tw.

  • Yog qhov kev sib cav range_lookup (range_lookup) yog sib npaug rau cuav (FALSE), tus qauv saib rau qhov sib tw tiag tiag, piv txwv li tib tus nqi raws li tau muab rau hauv qhov kev sib cav saib xyuas_valalue (lookup_value). Yog hais tias nyob rau hauv thawj kab ntawm qhov ntau ttau_array ( rooj) ntsib ob lossis ntau qhov tseem ceeb uas phim qhov kev sib cav saib xyuas_valalue (search_value), ces thawj tus yuav raug xaiv. Yog tias tsis pom qhov sib tw, lub luag haujlwm yuav qhia txog qhov yuam kev #AT (#N/A). Piv txwv li, cov qauv hauv qab no yuav qhia txog qhov yuam kev #AT (#N/A) yog tsis muaj nqi nyob rau hauv qhov ntau A2:A15 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • Yog qhov kev sib cav range_lookup (range_lookup) yog sib npaug rau TXOJ CAI (TUS TSEEM CEEB), tus qauv saib rau qhov kwv yees sib tw. Ntau precisely, ua ntej lub function VPR nrhiav ib qho kev sib tw tiag tiag, thiab yog tias tsis pom, xaiv ib qho kwv yees. Qhov kwv yees qhov sib tw yog qhov loj tshaj tus nqi uas tsis tshaj tus nqi teev hauv qhov kev sib cav. saib xyuas_valalue (lookup_value).

Yog qhov kev sib cav range_lookup (range_lookup) yog sib npaug rau TXOJ CAI (TRUE) los yog tsis tau teev tseg, ces qhov tseem ceeb nyob rau hauv thawj kem ntawm qhov ntau yuav tsum tau txheeb nyob rau hauv ascending kev txiav txim, uas yog, los ntawm tsawg tshaj plaws mus rau loj tshaj plaws. Txwv tsis pub, muaj nuj nqi VPR tej zaum yuav rov qab ua yuam kev.

Kom nkag siab zoo dua qhov tseem ceeb ntawm kev xaiv TXOJ CAI (TRUTH) lub cuav (FALSE), cia peb saib qee cov qauv ntxiv nrog cov haujlwm VPR thiab saib cov txiaj ntsig.

Piv txwv li 1: Nrhiav qhov tseeb Match nrog VLOOKUP

Raws li koj nco qab, txhawm rau tshawb nrhiav qhov tseeb, qhov kev sib cav thib plaub ntawm qhov ua haujlwm VPR yuav tsum muaj teeb meem cuav (FALSE).

Cia peb rov qab mus rau lub rooj los ntawm thawj qhov piv txwv thiab nrhiav seb tus tsiaj twg tuaj yeem txav tau ntawm qhov nrawm 50 mais ib teev. Kuv ntseeg tias cov qauv no yuav tsis ua rau koj muaj teeb meem:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Nco ntsoov tias peb qhov kev tshawb nrhiav (kem A) muaj ob qhov tseem ceeb 50 - hauv cell A5 и A6. Cov qauv xa rov qab tus nqi los ntawm cell B5. Vim li cas? Vim hais tias thaum tab tom nrhiav rau qhov tseeb match, muaj nuj nqi VPR siv thawj tus nqi pom uas phim tus uas tau tshawb nrhiav.

Piv txwv 2: Siv VLOOKUP los nrhiav qhov kwv yees sib tw

Thaum koj siv lub luag haujlwm VPR mus nrhiav ib qho approximate match, piv txwv li thaum sib cav range_lookup (range_lookup) yog sib npaug rau TXOJ CAI (TRUE) los yog tshem tawm, thawj qhov uas koj yuav tsum tau ua yog txheeb qhov ntau los ntawm thawj kab hauv kev nce qib.

Qhov no tseem ceeb heev vim muaj haujlwm VPR rov qab tus nqi loj tshaj plaws tom ntej tom qab muab ib qho, thiab tom qab ntawd qhov kev tshawb nrhiav nres. Yog tias koj tsis quav ntsej qhov kev txheeb xyuas qhov tseeb, koj yuav xaus nrog cov txiaj ntsig coj txawv txawv lossis cov lus yuam kev. #AT (#N/A).

Tam sim no koj tuaj yeem siv ib qho ntawm cov qauv hauv qab no:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Raws li koj tuaj yeem pom, kuv xav paub seb cov tsiaj twg muaj qhov ceev tshaj plaws 69 mais ib teev. Thiab ntawm no yog qhov tshwm sim ua haujlwm rov qab rau kuv VPR:

Raws li koj tuaj yeem pom, cov qauv rov qab ua qhov tshwm sim Antelope (Antelope), uas nws ceev 61 mais ib teev, txawm tias cov npe kuj suav nrog tsov pom teev (Cheetah) uas khiav ceev 70 mais ib teev, thiab 70 yog ze rau 69 tshaj 61, puas yog? Vim li cas qhov no tshwm sim? Vim lub luag haujlwm VPR thaum tshawb nrhiav qhov kwv yees qhov sib tw, rov qab tus nqi loj tshaj plaws uas tsis ntau dua qhov uas tau tshawb nrhiav.

Kuv vam tias cov piv txwv no tso qee qhov teeb meem ntawm kev ua haujlwm nrog kev ua haujlwm VPR hauv Excel, thiab koj tsis saib nws li tus neeg sab nraud lawm. Tam sim no nws tsis ua mob rau luv luv rov hais dua cov ntsiab lus tseem ceeb ntawm cov khoom peb tau kawm txhawm rau txhim kho nws hauv kev nco zoo dua.

VLOOKUP hauv Excel - koj yuav tsum nco ntsoov qhov no!

  1. muaj nuj nqi VPR Excel tsis tuaj yeem saib sab laug. Nws ib txwm saib rau tus nqi nyob rau hauv kab laug sab ntawm qhov ntau muab los ntawm kev sib cav table_cov ( rooj).
  2. Nyob hauv txoj haujlwm VPR tag nrho cov txiaj ntsig yog cov ntaub ntawv tsis txaus ntseeg, piv txwv li cov ntawv me thiab cov ntawv loj sib npaug.
  3. Yog tias tus nqi koj tab tom nrhiav tsawg dua qhov tsawg kawg nkaus tus nqi nyob rau hauv thawj kab ntawm qhov ntau tau ntsia, qhov ua haujlwm VPR yuav qhia qhov yuam kev #AT (#N/A).
  4. Yog 3rd sib cav Col_index_num (column_number) tsawg dua 1muaj nuj nqi VPR yuav qhia qhov yuam kev #VALUE! (#VALUE!). Yog tias nws ntau dua li cov kab hauv kab table_cov (cov lus), cov haujlwm yuav qhia txog qhov yuam kev #REF! (#LINK!).
  5. Siv cov ntaub ntawv xovtooj tsis tseeb hauv kev sib cav table_cov ( rooj) kom qhov kev tshawb nrhiav qhov tseeb yog khaws cia thaum luam cov qauv. Sim siv cov npe npe lossis cov ntxhuav hauv Excel ua lwm txoj hauv kev.
  6. Thaum ua qhov kwv yees qhov kev sib tw tshawb nrhiav, nco ntsoov tias thawj kab hauv kab uas koj tab tom nrhiav yuav tsum tau txheeb raws li qhov nce mus.
  7. Thaum kawg, nco ntsoov qhov tseem ceeb ntawm qhov kev sib cav thib plaub. Siv tus nqi TXOJ CAI (TRUTH) lub cuav (FALSE) txhob txwm tshaj tawm thiab koj yuav tshem tau ntau qhov mob taub hau.

Hauv cov lus hauv qab no ntawm peb cov kev qhia ua haujlwm VPR Hauv Excel, peb yuav kawm cov piv txwv siab dua, xws li ua ntau yam kev suav nrog VPR, rho tawm qhov tseem ceeb ntawm ntau kab, thiab ntau dua. Ua tsaug rau koj nyeem cov lus qhia no thiab kuv vam tias yuav ntsib koj dua lub lim tiam tom ntej!

Sau ntawv cia Ncua