txheem
Zaj lus qhia no piav qhia yuav ua li cas sai sai rau qhov xwm txheej uas muaj kev ua haujlwm VPR (VLOOKUP) tsis xav ua haujlwm hauv Excel 2013, 2010, 2007 thiab 2003, thiab yuav ua li cas txheeb xyuas thiab kho qhov yuam kev thiab kov yeej cov kev txwv VPR.
Hauv ob peb nqe lus dhau los, peb tau tshawb xyuas ntau yam ntawm cov haujlwm VPR hauv Excel. Yog tias koj tau nyeem lawv ua tib zoo, tam sim no koj yuav tsum yog tus kws tshaj lij hauv daim teb no. Txawm li cas los xij, nws tsis yog vim li cas ntau tus kws tshaj lij Excel ntseeg VPR ib tug ntawm cov complex nta. Nws muaj ib pawg ntawm cov kev txwv thiab nta uas dhau los ua qhov chaw ntawm ntau yam teeb meem thiab tsis raug.
Hauv tsab xov xwm no koj yuav pom cov lus piav qhia yooj yim ntawm qhov yuam kev #AT (#N/A), #NAME? (#NAME?) thiab #VALUE! (#VALUE!) uas tshwm sim thaum ua haujlwm nrog cov haujlwm VPR, nrog rau cov txheej txheem thiab cov txheej txheem ntawm kev ua haujlwm nrog lawv. Peb mam li pib nrog cov xwm txheej feem ntau thiab cov laj thawj pom tseeb tshaj plaws vim li cas. VPR tsis ua haujlwm, yog li nws yog qhov zoo dua los kawm cov piv txwv hauv qhov kev txiav txim uas lawv tau muab rau hauv tsab xov xwm.
Txhim kho #N / A yuam kev hauv VLOOKUP muaj nuj nqi hauv Excel
Hauv cov qauv nrog VPR lus yuam kev #AT (#N/A) txhais tau tias tsis muaj (tsis muaj ntaub ntawv) - tshwm sim thaum Excel nrhiav tsis tau tus nqi koj tab tom nrhiav. Qhov no tuaj yeem tshwm sim rau ntau yam.
1. Tus nqi xav tau yog sau yuam kev
Lub tswv yim zoo los xyuas cov khoom no ua ntej! Typos feem ntau tshwm sim thaum koj ua haujlwm nrog cov ntaub ntawv loj heev, suav nrog ntau txhiab kab, lossis thaum tus nqi koj tab tom nrhiav tau sau rau hauv cov qauv.
2. #N/A yuam kev thaum tshawb nrhiav qhov sib tw nrog VLOOKUP
Yog tias koj siv tus qauv nrog qhov kwv yees qhov kev sib tw tshawb nrhiav, piv txwv li kev sib cav range_lookup (range_lookup) yog TRUE lossis tsis tau teev tseg, koj cov qauv yuav qhia txog qhov yuam kev # N / A hauv ob qhov xwm txheej:
- Tus nqi los saib yog tsawg dua tus nqi tsawg tshaj plaws hauv cov array raug ntsia.
- Cov kab tshawb nrhiav tsis raug txheeb raws qhov nce mus.
3. #N/A yuam kev thaum nrhiav kev sib tw nrog VLOOKUP
Yog tias koj tab tom nrhiav rau qhov tseeb match, piv txwv li kev sib cav range_lookup (range_lookup) yog FALSE thiab tsis pom tus nqi tseeb, cov qauv tseem yuav qhia txog qhov yuam kev # N / A. Kawm paub ntxiv txog yuav ua li cas tshawb nrhiav qhov tseeb thiab kwv yees qhov sib tw nrog lub luag haujlwm VPR.
4. Tshawb nrhiav tsis yog sab laug
Raws li koj paub, ib qho ntawm cov kev txwv tseem ceeb tshaj plaws VPR nws yog tias nws tsis tuaj yeem tig ntsej muag mus rau sab laug, yog li cov lus saib hauv koj lub rooj yuav tsum yog sab laug tshaj plaws. Hauv kev xyaum, peb feem ntau tsis nco qab txog qhov no, uas ua rau cov mis tsis ua haujlwm thiab ua yuam kev. # N / A.
Kev txiav txim siab: Yog tias nws tsis tuaj yeem hloov cov ntaub ntawv qauv kom cov kab tshawb nrhiav sab laug tshaj plaws, koj tuaj yeem siv kev sib xyaw ua ke ntawm cov haujlwm. INDEX (INDEX) thiab XOV XWM ntxiv (MATCH) raws li qhov hloov pauv tau yooj yim dua rau VPR.
5. Cov naj npawb yog formatted li ntawv
Lwm qhov kev ua yuam kev # N / A hauv cov qauv nrog VPR yog cov lej hauv cov ntawv nyeem hauv lub rooj tseem ceeb lossis lub rooj saib.
Qhov no feem ntau tshwm sim thaum koj ntshuam cov ntaub ntawv los ntawm cov ntaub ntawv sab nraud, lossis thaum koj ntaus apostrophe ua ntej tus lej kom tus lej xoom.
Cov cim pom tseeb tshaj plaws ntawm tus lej hauv cov ntawv nyeem yog qhia hauv daim duab hauv qab no:
Ntxiv rau, cov lej tuaj yeem khaws cia hauv hom ntawv General (General). Nyob rau hauv cov ntaub ntawv no, tsuas muaj ib tug ceeb feature - cov zauv yog raws li nyob rau hauv lub sab laug ntug ntawm lub cell, thaum los ntawm lub neej ntawd lawv yog raws li nyob rau hauv txoj cai ntug.
Kev txiav txim siab: Yog tias nws yog ib tus nqi, tsuas yog nyem rau ntawm qhov yuam kev icon thiab xaiv Hloov ua tus lej (Hloov mus rau tus lej) los ntawm cov ntawv qhia zaub mov.
Yog tias qhov no yog qhov xwm txheej uas muaj ntau tus lej, xaiv lawv thiab nyem rau ntawm qhov chaw xaiv. Hauv cov ntawv qhia zaub mov uas tshwm, xaiv Hom Ntawv Hlwb (Format Cells) > tab Number (Number) > format Number (Tswj) thiab nias OK.
6. Muaj qhov chaw nyob ntawm qhov pib lossis qhov kawg
Qhov no yog qhov tsawg tshaj plaws vim li cas rau qhov yuam kev. # N / A hauv kev ua haujlwm VPR, vim nws tsis yooj yim pom pom cov chaw ntxiv no, tshwj xeeb tshaj yog thaum ua haujlwm nrog cov rooj loj, thaum feem ntau ntawm cov ntaub ntawv tawm ntawm qhov screen.
Tshuaj 1: Ntxiv qhov chaw hauv lub rooj loj (qhov twg VLOOKUP muaj nuj nqi)
Yog tias qhov chaw ntxiv tshwm nyob rau hauv lub rooj loj, koj tuaj yeem xyuas kom meej tias cov qauv ua haujlwm kom raug los ntawm kev kaw qhov kev sib cav saib xyuas_valalue (lookup_value) rau hauv kev ua haujlwm TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Kev daws 2: Ntxiv qhov chaw hauv lub rooj saib (hauv kab saib)
Yog tias qhov chaw ntxiv nyob hauv kab tshawb nrhiav - txoj hauv kev yooj yim # N / A hauv cov mis nrog VPR zam tsis tau. Hloov ntawm VPR Koj tuaj yeem siv cov qauv array nrog kev sib xyaw ua haujlwm INDEX (INDEX), XOV XWM ntxiv (MATCH) thiab TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Txij li qhov no yog ib qho array formula, tsis txhob hnov qab nias Ctrl + ua haujlwm + nkag hloov li qub Saunkag mus rau tus qauv kom raug.
yuam kev #VALUE! hauv cov qauv nrog VLOOKUP
Feem ntau, Microsoft Excel qhia txog qhov yuam kev #VALUE! (#VALUE!) thaum tus nqi siv hauv cov qauv tsis sib xws rau hom ntaub ntawv. Hais txog VPR, ces feem ntau muaj ob qho laj thawj rau qhov yuam kev #VALUE!.
1. Tus nqi koj tab tom nrhiav yog ntev tshaj 255 cim
Ceev faj: ua haujlwm VPR tsis tuaj yeem tshawb nrhiav qhov tseem ceeb uas muaj ntau dua 255 tus cim. Yog tias tus nqi koj tab tom nrhiav tshaj qhov txwv no, koj yuav tau txais cov lus yuam kev. #VALUE!.
Kev txiav txim siab: Siv ib pawg ntawm cov nta INDEX + MATCH (INDEX + MATCH). Hauv qab no yog ib qho qauv uas yuav ua tau zoo rau txoj haujlwm no:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Txoj kev tag nrho mus rau phau ntawv nrhiav haujlwm tsis tau teev tseg
Yog tias koj tab tom khaws cov ntaub ntawv los ntawm lwm phau ntawv ua haujlwm, koj yuav tsum qhia tag nrho txoj hauv kev rau cov ntaub ntawv ntawd. Tshwj xeeb tshaj yog, koj yuav tsum suav nrog lub npe ntawm phau ntawv ua haujlwm (nrog rau qhov txuas ntxiv) hauv square brackets [ ], ua raws li daim ntawv lub npe, ua raws li qhov exclamation point. Tag nrho cov kev tsim kho no yuav tsum tau muab kaw rau hauv apostrophes, yog tias phau ntawv lossis daim ntawv npe muaj qhov chaw.
Ntawm no yog cov qauv ua tiav ntawm cov haujlwm VPR mus nrhiav hauv lwm phau ntawv:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Tus qauv tiag tiag yuav zoo li no:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Cov qauv no yuav saib tus nqi ntawm tes A2 hauv ib kem B ntawm daim ntawv Ntawv 1 hauv phau ntawv ua haujlwm Tus nqi tshiab thiab rho tawm tus nqi sib thooj los ntawm kab D.
Yog tias ib feem ntawm txoj kab lus raug tshem tawm, koj txoj haujlwm VPR yuav tsis ua haujlwm thiab yuav qhia txog qhov yuam kev #VALUE! (txawm tias phau ntawv ua haujlwm nrog lub rooj saib xyuas tam sim no qhib).
Yog xav paub ntxiv txog kev ua haujlwm VPRxa mus rau lwm cov ntaub ntawv Excel, saib zaj lus qhia: Tshawb nrhiav lwm phau ntawv siv VLOOKUP.
3. Argument Column_num tsawg dua 1
Nws yog ib qho nyuaj rau xav txog qhov xwm txheej uas ib tug neeg nkag mus rau tus nqi tsawg dua 1los qhia cov kab uas yuav rho tawm tus nqi. Txawm hais tias nws muaj peev xwm yog tias tus nqi ntawm qhov kev sib cav no suav nrog lwm Excel ua haujlwm zes hauv VPR.
Yog li, yog tias nws tshwm sim qhov kev sib cav Col_index_num (column_number) tsawg dua 1muaj nuj nqi VPR tseem yuav qhia qhov yuam kev #VALUE!.
Yog qhov kev sib cav Col_index_num (column_number) yog ntau dua tus naj npawb ntawm kab hauv qhov muab array, VPR yuav qhia qhov yuam kev #REF! (#SSYL!).
yuam kev #NAME? hauv VLOOKUP
Qhov yooj yim tshaj yog qhov yuam kev #NAME? (#NAME?) - yuav tshwm sim yog tias koj yuam kev sau lub npe ua haujlwm nrog qhov yuam kev.
Txoj kev daws teeb meem yog pom tseeb - tshawb xyuas koj cov ntawv sau!
VLOOKUP tsis ua haujlwm (kev txwv, caveats thiab kev txiav txim siab)
Ntxiv nrog rau qhov nyuaj syntax, VPR muaj kev txwv ntau dua li lwm yam haujlwm Excel. Vim cov kev txwv no, seemingly yooj yim qauv nrog VPR feem ntau ua rau cov txiaj ntsig tsis tau xav txog. Hauv qab no koj yuav pom cov kev daws teeb meem rau ntau qhov xwm txheej uas VPR tsis yog lawm.
1. VLOOKUP tsis yog rooj plaub
muaj nuj nqi VPR tsis paub qhov txawv ntawm cov ntaub ntawv thiab lees txais cov tsiaj ntawv me thiab cov tsiaj ntawv loj zoo ib yam. Yog li ntawd, yog tias muaj ntau lub ntsiab lus hauv lub rooj sib txawv tsuas yog nyob rau hauv rooj plaub, VLOOKUP muaj nuj nqi yuav rov qab thawj lub ntsiab lus pom, tsis hais qhov twg.
Kev txiav txim siab: Siv lwm txoj haujlwm Excel uas tuaj yeem ua qhov kev tshawb nrhiav ntsug (LOOKUP, SUMPRODUCT, INDEX, thiab MATCH) ua ke nrog NYEEMIb qho uas distinguishes rooj plaub. Yog xav paub ntxiv, koj tuaj yeem kawm los ntawm zaj lus qhia - 4 txoj hauv kev los ua VLOOKUP case-sensitive hauv Excel.
2. VLOOKUP rov qab thawj tus nqi pom
Raws li koj twb paub, VPR rov qab tus nqi los ntawm kab ntawv muab coj mus rau thawj qhov sib tw pom. Txawm li cas los xij, koj tuaj yeem muaj nws rho tawm 2nd, 3rd, 4th, lossis lwm yam rov ua dua ntawm tus nqi koj xav tau. Yog tias koj xav tau rho tawm tag nrho cov txiaj ntsig duplicate, koj yuav xav tau kev sib xyaw ua haujlwm INDEX (INDEX), Tsawg (TSUM) thiab LINE (ROW).
3. Ib kem tau ntxiv lossis tshem tawm rau lub rooj
Hmoov tsis, cov qauv VPR nres ua haujlwm txhua zaus ib kab tshiab ntxiv lossis tshem tawm mus rau lub rooj saib. Qhov no tshwm sim vim lub syntax VPR xav kom koj qhia tag nrho cov kev tshawb fawb thiab cov kab ke tshwj xeeb rau kev rho tawm cov ntaub ntawv. Lawm, ob qho tib si muab ntau thiab kab zauv hloov thaum koj rho tawm ib kem lossis ntxig ib qho tshiab.
Kev txiav txim siab: Thiab dua ua haujlwm tau nrawm los pab INDEX (INDEX) thiab XOV XWM ntxiv (MATCH). Hauv cov qauv INDEX + MATCH Koj cais cov kev tshawb nrhiav thiab rov qab txhua kab, thiab yog li ntawd, koj tuaj yeem tshem tawm lossis ntxig ntau kab raws li koj xav tau yam tsis muaj kev txhawj xeeb txog kev hloov kho txhua yam kev tshawb nrhiav cov qauv.
4. Cov ntaub ntawv xovtooj ntawm tes yog garbled thaum luam cov qauv
Cov nqe lus no piav qhia lub ntsiab lus ntawm qhov teeb meem tas mus li, puas yog?
Kev txiav txim siab: Ib txwm siv cov ntaub ntawv ntawm tes (nrog rau lub cim $) ntawm cov ntaub ntawv qhov ntau, piv txwv li $A $2: $C $100 or $A: $C. Nyob rau hauv lub formula bar, koj tuaj yeem hloov pauv hom txuas sai sai los ntawm txhaj F4.
VLOOKUP - ua haujlwm nrog cov haujlwm IFERROR thiab ISERROR
Yog hais tias koj tsis xav kom ntshai cov neeg siv nrog cov lus yuam kev # N / A, #VALUE! or #NAME?, koj tuaj yeem qhia qhov khoob ntawm tes lossis koj tus kheej cov lus. Koj tuaj yeem ua qhov no los ntawm kev tso VPR rau hauv kev ua haujlwm IFERROR (IFERROR) hauv Excel 2013, 2010 thiab 2007 lossis siv ntau lub zog IF + ISERROR (IF + ISERROR) hauv cov ntawv ua ntej.
VLOOKUP: ua haujlwm nrog IFERROR muaj nuj nqi
Function syntax IFERROR (IFERROR) yog qhov yooj yim thiab hais lus rau nws tus kheej:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Ntawd yog, rau thawj qhov kev sib cav koj ntxig tus nqi los kuaj xyuas qhov yuam kev, thiab rau qhov kev sib cav thib ob koj qhia meej tias yuav rov qab yog dab tsi yog tias pom qhov yuam kev.
Piv txwv li, cov qauv no rov qab ib lub xov tooj khoob yog tias tus nqi koj tab tom nrhiav tsis pom:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Yog hais tias koj xav tso saib koj tus kheej cov lus es tsis txhob muaj nuj nqi tus txheej txheem yuam kev lus VPR, muab tso rau hauv quotes, zoo li li ntawd:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ua haujlwm nrog ISERROR muaj nuj nqi
Txij li thaum muaj nuj nqi IFERROR tshwm sim hauv Excel 2007, thaum ua haujlwm hauv cov ntawv ua ntej koj yuav tsum tau siv qhov sib xyaw ua ke IF (IF) thiab EOSHIBKA (ISERROR) zoo li no:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Piv txwv li, tus qauv YOG + ISERROR + VLOOKUP, zoo ib yam li cov qauv IFERROR + VLOOKUPqhia saum toj no:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Qhov ntawd yog txhua yam rau hnub no. Kuv vam tias qhov kev qhia luv luv no yuav pab koj daws txhua yam ua yuam kev. VPR thiab ua kom koj cov qauv ua haujlwm raug.