txheem
Qhov kev qhia me me no piav qhia yuav ua li cas ua haujlwm VPR (VLOOKUP) case-sensitive, qhia ob peb lwm cov qauv uas Excel tuaj yeem tshawb xyuas hauv qhov xwm txheej, thiab taw qhia qhov zoo thiab qhov tsis zoo ntawm txhua txoj haujlwm.
Kuv kwv yees txhua tus neeg siv Excel paub tias dab tsi ua haujlwm nrhiav ntsug. Yog lawm, nws yog ib txoj haujlwm VPR. Txawm li cas los xij, tsawg tus neeg paub qhov ntawd VPR tsis yog cov ntaub ntawv rhiab, piv txwv li cov ntawv qis thiab cov ntawv sau npe zoo ib yam rau nws.
Ntawm no yog ib qho piv txwv ceev qhia qhov tsis muaj peev xwm VPR paub sau npe. Xav tias hauv ib lub cell A1 muaj tus nqi "nqi" thiab lub xov tooj ntawm tes A2 - "Bill", tus qauv:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... yuav nres nws qhov kev tshawb fawb ntawm "nqi" txij li tus nqi ntawd los ua ntej hauv daim ntawv teev npe, thiab rho tawm tus nqi ntawm lub xov tooj ntawm tes B1.
Tom qab ntawv hauv tsab xov xwm no, kuv yuav qhia koj yuav ua li cas VPR case sensitive. Tsis tas li ntawd, peb yuav kawm ob peb txoj haujlwm ntxiv uas tuaj yeem ua qhov kev tshawb nrhiav qhov tseem ceeb hauv Excel.
Peb yuav pib nrog qhov yooj yim tshaj plaws - Saib (LOOKUP) thiab SUMPRODUCT (SUMPRODUCT), uas, hmoov tsis, muaj ntau yam kev txwv tseem ceeb. Tom ntej no, peb yuav ua tib zoo saib me ntsis ntau cov qauv INDEX + MATCH (INDEX + MATCH), uas ua haujlwm flawlessly nyob rau hauv txhua qhov teeb meem thiab nrog ib tug dataset.
VLOOKUP muaj nuj nqi yog case sensitive
Raws li koj twb paub lawm, cov haujlwm ib txwm muaj VPR yog case insensitive. Txawm li cas los xij, muaj ib txoj hauv kev los ua kom nws cov ntaub ntawv nkag siab. Txhawm rau ua qhov no, koj yuav tsum tau ntxiv ib qho kev pabcuam rau lub rooj, raws li qhia hauv qhov piv txwv hauv qab no.
Piv txwv li hauv ib kab B muaj cov khoom txheeb xyuas (khoom) thiab koj xav rho tawm tus nqi ntawm cov khoom thiab cov lus hais los ntawm kab C и D. Qhov teeb meem yog tias cov cim qhia muaj ob tus lej qis thiab tus lej loj. Piv txwv li, cov nqi ntawm tes B4 (001Tvci3u) thiab B5 (001Tvci3U) txawv tsuas yog nyob rau hauv cov ntaub ntawv ntawm tus cim kawg, u и U ntsig txog.
Raws li koj tuaj yeem xav, cov qauv tshawb nrhiav ib txwm muaj
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
yuav rov qab los $ 90, txij li tus nqi 001tv3 ua yog nyob rau hauv kev tshawb nrhiav ua ntej tshaj 001tvc3 ua. Tab sis qhov ntawd tsis yog qhov peb xav tau, puas yog?
Mus nrhiav nrog ib tug muaj nuj nqi VPR Nyob rau hauv Excel cov ntaub ntawv rhiab heev, koj yuav tsum tau ntxiv ib tug pab kem thiab sau nws cov hlwb nrog cov qauv hauv qab no (qhov twg B yog kab saib):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Cov qauv no rhuav tshem tus nqi xav tau rau hauv cov cim cais, hloov txhua tus cim nrog nws cov lej (piv txwv li, tsis yog A at 65, ua a code 97) thiab tom qab ntawd muab cov lej no tso rau hauv ib txoj hlua tshwj xeeb ntawm tus lej.
Tom qab ntawd, peb siv lub luag haujlwm yooj yim VPR rau kev tshawb nrhiav qhov tseeb:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Kev ua haujlwm zoo ntawm kev ua haujlwm VPR case-sensitive nyob ntawm ob yam:
- Cov pab pawg yuav tsum yog sab laug tshaj plaws hauv qhov pom tau.
- Tus nqi koj tab tom nrhiav yuav tsum muaj cov cim cim tsis yog tus nqi tiag.
Yuav ua li cas siv CODE muaj nuj nqi kom raug
Cov mis muab tso rau hauv lub hlwb ntawm pawg pabcuam xav tias txhua yam ntawm koj qhov kev tshawb nrhiav muaj cov cim tib yam. Yog tias tsis yog, koj yuav tsum paub tus lej tsawg tshaj plaws thiab loj tshaj plaws thiab ntxiv ntau yam nta IFERROR (IFERROR) muaj pes tsawg tus cim yog qhov sib txawv ntawm qhov luv tshaj thiab qhov ntev tshaj plaws nrhiav tus nqi.
Piv txwv li, yog tias tus nqi tshawb nrhiav luv tshaj plaws yog 3 tus cim thiab qhov ntev tshaj plaws yog 5 tus cim, siv cov qauv no:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Rau kev ua haujlwm PSTR (MID) Koj muab cov lus sib cav hauv qab no:
- 1st argument – ntawv nyeem (cov ntawv nyeem) yog cov ntawv nyeem lossis xovtooj siv uas muaj cov cim yuav tsum tau muab rho tawm (hauv peb rooj plaub nws yog B2)
- 2st argument – pib_num (start_position) yog txoj hauj lwm ntawm thawj cov cim uas tau muab rho tawm. koj nkag 1 hauv thawj lub luag haujlwm PSTR, 2 - hauv qhov ua haujlwm thib ob PSTR thiab lwm yam
- 3st argument – num_chars (number_of_characters) – Qhia tus naj npawb ntawm cov cim kom rho tawm ntawm cov ntawv nyeem. Txij li thaum peb tsuas xav tau 1 tus cim txhua lub sijhawm, hauv txhua txoj haujlwm peb sau 1.
LIMITATIONS: muaj nuj nqi VPR Nws tsis yog qhov kev daws teeb meem zoo tshaj plaws rau kev tshawb nrhiav cov ntaub ntawv hauv Excel. Ua ntej, yuav tsum tau ntxiv ib qho kev pabcuam. Qhov thib ob, cov qauv ua haujlwm zoo nkaus xwb yog tias cov ntaub ntawv yog homogeneous, lossis pes tsawg tus cim hauv cov txiaj ntsig tshawb tau paub. Yog tias qhov no tsis yog koj li, nws yog qhov zoo dua los siv ib qho kev daws teeb meem uas peb qhia hauv qab no.
LOOKUP muaj nuj nqi rau cov ntaub ntawv tshawb fawb
muaj nuj nqi Saib (LOOKUP) hais txog VPR, txawm li cas los xij nws cov syntax tso cai rau cov ntaub ntawv-sensitive tshawb nrhiav yam tsis tau ntxiv ib qho kev pabcuam. Txhawm rau ua qhov no, siv Saib ua ke nrog kev ua haujlwm NYEEM (TXOJ).
Yog tias peb coj cov ntaub ntawv los ntawm qhov piv txwv yav dhau los (tsis muaj kab ke), ces cov qauv hauv qab no yuav daws tau txoj haujlwm:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formula nrhiav nyob rau hauv ntau yam A2: UA 7 kev sib tw nrog tus nqi ntawm tes F2 case rhiab thiab xa rov qab tus nqi los ntawm kab B ntawm tib kab.
zoo li VPRmuaj nuj nqi Saib ua haujlwm sib npaug nrog cov ntawv nyeem thiab cov lej, raws li koj tuaj yeem pom hauv lub screenshot hauv qab no:
Tseem ceeb! Txhawm rau ua haujlwm Saib ua hauj lwm kom raug, qhov tseem ceeb nyob rau hauv lub lookup kem yuav tsum tau sorted nyob rau hauv ascending kev txiav txim, piv txwv li los ntawm tsawg tshaj plaws mus rau loj tshaj plaws.
Cia kuv piav me ntsis txog qhov ua haujlwm li cas NYEEM nyob rau hauv cov qauv qhia saum toj no, raws li qhov no yog lub ntsiab lus tseem ceeb.
muaj nuj nqi NYEEM muab piv rau ob lub ntsiab lus hauv nqe lus 1 thiab 2nd thiab rov TRUE yog tias lawv zoo ib yam, lossis FALSE yog tias tsis yog. Nws yog ib qho tseem ceeb rau peb uas muaj nuj nqi NYEEM case sensitive.
Cia saib seb peb cov qauv ua haujlwm li cas SAIB + EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- muaj nuj nqi NYEEM piv cov nqi ntawm tes F2 nrog tag nrho cov ntsiab lus hauv ib kab A (A2: A7). Rov qab TRUE yog tias pom qhov sib tw tiag tiag, txwv tsis pub FALSE.
- Txij li thaum koj muab thawj qhov kev sib cav Saib tus nqi TRUE, nws rho tawm tus nqi sib thooj los ntawm kab ntawv teev tseg (hauv peb cov ntaub ntawv, kab B) tsuas yog pom muaj qhov sib tw tiag tiag, cov ntaub ntawv nkag siab.
Kuv vam tias qhov kev piav qhia no tau meej thiab tam sim no koj nkag siab lub tswv yim tseem ceeb. Yog tias muaj, ces koj yuav tsis muaj teeb meem nrog lwm cov haujlwm uas peb yuav txheeb xyuas ntxiv, vim. lawv txhua tus ua haujlwm ntawm tib txoj cai.
LIMITATIONS: Cov ntaub ntawv nyob rau hauv kab saib yuav tsum tau txheeb nyob rau hauv ascending order.
SUMPRODUCT - pom cov ntawv tseem ceeb, cov ntaub ntawv nkag siab, tab sis rov qab tsuas yog tus lej
Raws li koj twb tau nkag siab los ntawm lub npe, SUMPRODUCT (SUMPRODUCT) yog lwm txoj haujlwm Excel uas yuav pab koj ua qhov kev tshawb nrhiav qhov tseem ceeb, tab sis tsuas yog rov qab cov lej muaj txiaj ntsig. Yog tias qhov kev xaiv no tsis haum rau koj, ces koj tuaj yeem tam sim ntawd mus rau lub nras INDEX + MATCH, uas muab kev daws teeb meem rau txhua kis thiab rau txhua hom ntaub ntawv.
Ua ntej, cia kuv piav qhia luv luv ntawm cov syntax ntawm lub luag haujlwm no, qhov no yuav pab koj nkag siab zoo txog cov qauv ntawm cov ntaub ntawv uas ua raws li hauv qab no.
muaj nuj nqi SUMPRODUCT multiplies lub ntsiab ntawm qhov muab arrays thiab xa cov lej ntawm cov txiaj ntsig. Lub syntax zoo li no:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Txij li thaum peb xav tau kev tshawb nrhiav qhov tseeb, peb siv lub luag haujlwm NYEEM (EXACT) los ntawm qhov piv txwv yav dhau los ua ib qho ntawm cov sib npaug:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Raws li koj nco, NYEEM piv cov nqi ntawm tes F2 nrog tag nrho cov ntsiab lus hauv ib kab A. Rov qab TRUE yog tias pom qhov sib tw tiag tiag, txwv tsis pub FALSE. Hauv kev ua lej, Excel siv TRUE li 1, thiab FALSE rau 0Ntxiv mus SUMPRODUCT muab cov lej no sib npaug thiab suav cov txiaj ntsig.
Zeros tsis suav vim tias thaum muab ntau ntau lawv ib txwm muab 0. Cia peb ua tib zoo saib seb yuav ua li cas thaum muaj kev sib tw tiag tiag hauv kab A pom thiab rov qab los 1… Muaj nuj nqi SUMPRODUCT multiplies tus lej hauv kab B on 1 thiab xa cov txiaj ntsig - raws nraim tib tus lej! Qhov no yog vim tias cov txiaj ntsig ntawm lwm cov khoom lag luam yog xoom, thiab lawv tsis cuam tshuam rau cov txiaj ntsig tau.
Hmoov tsis muaj nuj nqi SUMPRODUCT tsis tuaj yeem ua haujlwm nrog cov ntawv muaj nuj nqis thiab cov hnub raws li lawv tsis tuaj yeem muab ntau ntxiv. Hauv qhov no, koj yuav tau txais cov lus yuam kev #VALUE! (#VALUE!) as in a cell F4 hauv daim duab hauv qab no:
LIMITATIONS: Rov qab tsuas yog tus lej muaj nuj nqis.
INDEX + MATCH - kev tshawb nrhiav cov ntaub ntawv rau txhua hom ntaub ntawv
Thaum kawg, peb nyob ze rau qhov tsis muaj kev txwv thiab cov ntaub ntawv tshawb fawb uas ua haujlwm nrog txhua cov ntaub ntawv teev.
Qhov piv txwv no los txog zaum kawg, tsis yog vim qhov zoo tshaj plaws yog sab laug rau cov khoom qab zib, tab sis vim tias qhov kev paub tau los ntawm cov piv txwv yav dhau los yuav pab koj nkag siab txog cov qauv ntawm cov ntaub ntawv zoo dua thiab sai dua. INDEX + MATCH (INDEX + MATCH).
Raws li koj tej zaum twv, kev sib xyaw ua haujlwm XOV XWM ntxiv и INDEX siv hauv Excel ua qhov hloov pauv tau yooj yim dua thiab muaj zog rau lwm txoj hauv kev VPR. Tsab xov xwm Siv INDEX thiab MATCH es tsis txhob VLOOKUP yuav piav qhia meej tias cov haujlwm no ua haujlwm ua ke li cas.
Kuv tsuas yog rov sau cov ntsiab lus tseem ceeb:
- muaj nuj nqi XOV XWM ntxiv (MATCH) tshawb nrhiav tus nqi hauv ib thaj tsam thiab xa rov qab nws txoj haujlwm txheeb ze, uas yog, kab thiab / lossis kab zauv;
- Tom ntej no, ua haujlwm INDEX (INDEX) rov qab tus nqi los ntawm ib kab thiab / lossis kab.
Rau formula INDEX + MATCH tuaj yeem tshawb nrhiav cov ntaub ntawv-sensitively, koj tsuas yog yuav tsum tau ntxiv ib qho haujlwm rau nws. Nws tsis yog nyuaj rau twv seb nws yog dab tsi dua NYEEM (EXACT):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Hauv cov qauv no NYEEM ua haujlwm zoo ib yam li ua ke nrog kev ua haujlwm Saib, thiab muab cov txiaj ntsig zoo ib yam:
Nco ntsoov tias tus qauv INDEX + MATCH nyob rau hauv curly braces yog ib tug array formula thiab koj yuav tsum ua kom tiav nws los ntawm nias Ctrl + ua haujlwm + nkag.
Vim li cas INDEX + MATCH yog qhov kev daws teeb meem zoo tshaj plaws rau kev tshawb nrhiav qhov tseeb?
Lub ntsiab zoo ntawm cov pob INDEX и XOV XWM ntxiv:
- Tsis tas yuav ntxiv ib pawg pabcuam, tsis zoo li VPR.
- Tsis xav kom nrhiav kem yuav txheeb, tsis zoo li Saib.
- Ua haujlwm nrog txhua hom ntaub ntawv - tus lej, ntawv nyeem thiab hnub tim.
Cov mis no zoo tag nrho, puas yog? Qhov tseeb, tsis yog. Thiab yog vim li cas.
Nco ntsoov tias lub xov tooj ntawm tes hauv tus nqi rov qab kab uas cuam tshuam nrog tus nqi saib yog khoob. Cov qauv twg yuav rov qab los? Tsis muaj? Cia peb saib seb qhov formula tiag tiag rov qab los:
Oops, cov qauv rov qab xoom! Qhov no yuav tsis yog qhov teeb meem loj yog tias koj ua haujlwm nrog cov ntawv nyeem ntshiab. Txawm li cas los xij, yog tias lub rooj muaj cov lej, suav nrog "tiag" xoom, qhov no dhau los ua teeb meem.
Qhov tseeb, tag nrho lwm cov qauv nrhiav (VLOOKUP, LOOKUP, thiab SUMPRODUCT) peb tau tham ua ntej coj tus cwj pwm zoo ib yam. Tab sis koj xav tau tus qauv zoo meej, txoj cai?
Ua kom muaj cov ntaub ntawv tsim nyog INDEX + MATCH zoo meej, muab tso rau hauv ib txoj haujlwm IF (IF) uas yuav sim ib lub xovtooj nrog tus nqi xa rov qab thiab rov qab tau qhov txiaj ntsig yog tias nws khoob:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Hauv cov qauv no:
- B yog ib kem nrog cov nqi rov qab
- 1+ yog tus lej uas hloov qhov txheeb ze ntawm lub cell rov qab los ntawm kev ua haujlwm XOV XWM ntxiv, mus rau qhov chaw nyob ntawm tes. Piv txwv li, hauv peb txoj haujlwm XOV XWM ntxiv nrhiav array muab A2: UA 7, uas yog, tus txheeb ze txoj hauj lwm ntawm lub cell A2 yuav 1, vim nws yog thawj tus hauv array. Tab sis qhov tseeb txoj hauj lwm ntawm tes A2 hauv kem yog 2, yog li peb ntxiv 1los ua kom muaj qhov sib txawv thiab muaj lub luag haujlwm TSEEM CEEB (INDIRECT) tau muab tus nqi los ntawm lub xov tooj xav tau.
Cov duab hauv qab no qhia txog kev kho qhov teeb meem-sensitive formula INDEX + MATCH Hauv kev ua. Nws xa rov qab qhov tshwm sim npliag yog tias lub xov tooj rov qab yog khoob.
Kuv rov sau cov qauv rau hauv kab B:Dkom haum tus qauv bar ntawm lub screenshot.
Formula rov 0yog tias lub cell rov qab muaj xoom.
Yog koj xav tau qhov link INDEX и XOV XWM ntxiv tso tawm qee cov lus thaum tus nqi rov qab yog qhov khoob, koj tuaj yeem sau nws hauv cov lus kawg ("") ntawm cov qauv, piv txwv li, zoo li no:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
