txheem
Nag hmo hauv marathon 30 Excel ua haujlwm hauv 30 hnub peb pom cov kab ntawv siv cov haujlwm NRHIAV (Tshawb nrhiav) thiab kuj siv IFERROR (IFERROR) thiab ISNUMBER (ISNUMBER) nyob rau hauv cov xwm txheej uas lub luag haujlwm cuam tshuam qhov yuam kev.
Hnub 19 ntawm peb marathon, peb yuav kawm txog txoj haujlwm TIV THAIV (XAV). Nws saib tus nqi hauv ib qho array thiab, yog tias pom tus nqi, rov qab nws txoj haujlwm.
Yog li, cia peb tig mus rau cov ntaub ntawv siv ntawm lub luag haujlwm TIV THAIV (MATCH) thiab saib ob peb yam piv txwv. Yog tias koj muaj koj tus kheej cov piv txwv lossis cov hauv kev ua haujlwm nrog cov haujlwm no, thov qhia rau lawv hauv cov lus.
Function 19: MATCH
muaj nuj nqi TIV THAIV (MATCH) rov qab txoj hauj lwm ntawm tus nqi hauv ib qho array, lossis qhov yuam kev #AT (#N/A) yog tsis pom. Ib qho array tuaj yeem raug txheeb lossis tsis txheeb. Muaj nuj nqi TIV THAIV (MATCH) tsis yog case sensitive.
Koj tuaj yeem siv MATCH muaj nuj nqi li cas?
muaj nuj nqi TIV THAIV (MATCH) rov qab txoj hauj lwm ntawm ib lub caij hauv ib qho array, thiab qhov tshwm sim no tuaj yeem siv los ntawm lwm yam haujlwm xws li INDEX (INDEX) lub VLOOKUP (VPR). Piv txwv li:
- Nrhiav txoj hauj lwm ntawm ib lub caij hauv ib daim ntawv teev npe uas tsis tau txheeb xyuas.
- Siv nrog XAIV (SELECT) los hloov cov tub ntxhais kawm qhov kev ua tau zoo mus rau qib ntawv.
- Siv nrog VLOOKUP (VLOOKUP) rau kev xaiv kab hloov pauv.
- Siv nrog INDEX (INDEX) txhawm rau nrhiav tus nqi ze tshaj plaws.
Syntax MATCH
muaj nuj nqi TIV THAIV (MATCH) muaj cov syntax hauv qab no:
MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
- saib xyuas_valalue (lookup_value) - tuaj yeem yog ntawv nyeem, tus lej, lossis boolean.
- lookup_array (lookup_array) - ib qho array lossis array siv (cov hlwb nyob ib sab hauv tib kem lossis tib kab).
- match_type (match_type) tuaj yeem coj peb qhov tseem ceeb: -1, 0 or 1. Yog hais tias qhov kev sib cav raug tshem tawm, nws yog sib npaug rau 1.
Traps MATCH (MATCH)
muaj nuj nqi TIV THAIV (MATCH) rov qab txoj haujlwm ntawm qhov pom, tab sis tsis yog nws tus nqi. Yog tias koj xav rov qab tus nqi, siv TIV THAIV (MATCH) ua ke nrog kev ua haujlwm INDEX (INDEX).
Piv txwv 1: Nrhiav ib lub caij hauv ib daim ntawv teev npe tsis txheeb
Rau ib daim ntawv teev npe tsis txheeb, koj tuaj yeem siv 0 raws li kev sib cav nqi match_type (match_type) txhawm rau tshawb nrhiav qhov tseeb match. Yog tias koj xav nrhiav qhov tseeb ntawm cov kab ntawv, koj tuaj yeem siv cov cim cim hauv qhov kev tshawb nrhiav tus nqi.
Hauv qhov piv txwv hauv qab no, txhawm rau nrhiav txoj haujlwm ntawm ib hlis hauv ib daim ntawv teev npe, peb tuaj yeem sau lub npe ntawm lub hli, tag nrho lossis ib feem, siv cov ntawv cim cim.
=MATCH(D2,B3:B7,0)
=ПОИСКПОЗ(D2;B3:B7;0)
Raws li kev sib cav lookup_array (lookup_array) koj tuaj yeem siv array ntawm qhov tsis tu ncua. Hauv qhov piv txwv hauv qab no, lub hli xav tau nkag mus rau hauv cell D5, thiab cov npe ntawm lub hlis raug hloov pauv raws li qhov kev sib cav thib ob rau txoj haujlwm. TIV THAIV (MATCH) raws li ib qho array ntawm qhov tsis tu ncua. Yog tias koj nkag mus rau lub hlis tom qab hauv cell D5, piv txwv li, Oct (Lub Kaum Hli), ces qhov tshwm sim ntawm kev ua haujlwm yuav yog #AT (#N/A).
=MATCH(D5,{"Jan","Feb","Mar"},0)
=ПОИСКПОЗ(D5;{"Jan";"Feb";"Mar"};0)
Piv txwv 2: Hloov cov tub ntxhais kawm qib ntawm feem pua rau cov ntawv
Koj tuaj yeem hloov cov qhab nia ntawm cov tub ntxhais kawm mus rau ib tsab ntawv uas siv cov haujlwm TIV THAIV (MATCH) ib yam li koj ua nrog VLOOKUP (VPR). Hauv qhov piv txwv no, kev ua haujlwm yog siv ua ke nrog XAIV (CHOICE), uas rov qab qhov kwv yees peb xav tau. Kev sib cav match_type (match_type) yog teem sib npaug rau -1, vim hais tias cov qhab nia nyob rau hauv lub rooj yog txheeb nyob rau hauv descending order.
Thaum sib cav match_type (match_type) yog -1, qhov tshwm sim yog tus nqi tsawg tshaj plaws uas ntau dua lossis sib npaug rau qhov xav tau. Hauv peb qhov piv txwv, qhov xav tau tus nqi yog 54. Txij li thaum tsis muaj tus nqi zoo li no hauv daim ntawv teev cov qhab nia, lub caij sib xws rau tus nqi 60 raug xa rov qab. Txij li thaum 60 yog nyob rau hauv plaub qhov chaw hauv daim ntawv teev npe, qhov tshwm sim ntawm kev ua haujlwm XAIV (SELECT) yuav yog tus nqi uas nyob rau hauv 4 txoj hauj lwm, piv txwv li cell C6, uas muaj cov qhab nia D.
=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)
=ВЫБОР(ПОИСКПОЗ(B9;B3:B7;-1);C3;C4;C5;C6;C7)
Piv txwv 3: Tsim ib qho yooj yim xaiv kab rau VLOOKUP (VLOOKUP)
Txhawm rau muab qhov yooj yim dua rau kev ua haujlwm VLOOKUP (VLOOKUP) Koj tuaj yeem siv TIV THAIV (MATCH) txhawm rau nrhiav tus lej kab, tsis yog nyuaj-coding nws tus nqi rau hauv txoj haujlwm. Hauv qhov piv txwv hauv qab no, cov neeg siv tuaj yeem xaiv ib cheeb tsam hauv cell H1, qhov no yog tus nqi uas lawv tab tom nrhiav VLOOKUP (VPR). Tom ntej no, lawv tuaj yeem xaiv ib hlis hauv cell H2, thiab ua haujlwm TIV THAIV (MATCH) yuav rov qab tus lej kab sib raug rau lub hli ntawd.
=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)
=ВПР(H1;$B$2:$E$5;ПОИСКПОЗ(H2;B1:E1;0);ЛОЖЬ)
Piv txwv 4: Nrhiav tus nqi ze tshaj plaws siv INDEX (INDEX)
muaj nuj nqi TIV THAIV (MATCH) ua haujlwm zoo ua ke nrog kev ua haujlwm INDEX (INDEX), uas peb yuav saib ze ze me ntsis tom qab hauv no marathon. Hauv qhov piv txwv no, kev ua haujlwm TIV THAIV (MATCH) yog siv los nrhiav qhov ze tshaj plaws rau tus lej raug los ntawm ntau tus lej twv.
- muaj nuj nqi ABS rov qab cov modulus ntawm qhov sib txawv ntawm txhua tus kwv yees thiab raug tus lej.
- muaj nuj nqi MIN (MIN) pom qhov sib txawv me tshaj plaws.
- muaj nuj nqi TIV THAIV (MATCH) pom qhov chaw nyob ntawm qhov sib txawv me tshaj plaws hauv cov npe sib txawv. Yog tias muaj ntau qhov sib txuam hauv cov npe, thawj tus yuav raug xa rov qab.
- muaj nuj nqi INDEX (INDEX) xa rov qab lub npe sib raug rau txoj haujlwm no los ntawm cov npe ntawm cov npe.
=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))
=ИНДЕКС(B2:B5;ПОИСКПОЗ(МИН(ABS(C2:C5-F1));ABS(C2:C5-F1);0))
