Poukisa INDEX ak MATCH yo pi bon pase VLOOKUP nan Excel

Nou te deja eksplike débutan kòman yo sèvi ak fonksyon debaz yo nan VLOOKUP (angle VLOOKUP, abrevyasyon la vle di "vètikal Lookup fonksyon"). Ak itilizatè ki gen eksperyans yo te montre plizyè fòmil pi konplike.

Ak nan atik sa a nou pral eseye bay enfòmasyon sou yon lòt metòd pou travay ak rechèch vètikal.

Ou ka mande: "Poukisa sa nesesè?". Lè sa a se nesesè yo nan lòd yo montre tout metòd rechèch posib. Anplis de sa, anpil restriksyon VLOOKUP souvan anpeche jwenn rezilta a vle. Nan sans sa a, INDEX() MATCH() se pi plis fonksyonèl ak divès, epi yo gen tou mwens restriksyon.

Prensip de baz ENDEX MATCH

Piske objektif gid sa a se montre kouman karakteristik sa a bon, nou Ann gade enfòmasyon debaz yo konsènan prensip operasyon li yo. Epi nou pral montre egzanp, epi tou konsidere poukisa, li pi bon pase VLOOKUP ().

ENDEX Fonksyon Sentaks ak Itilizasyon

Fonksyon sa a ede jwenn valè vle a nan mitan zòn rechèch espesifye yo ki baze sou nimewo kolòn oswa liy lan. Sentaks:

=INDEX(etalaj, nimewo ranje, nimewo kolòn):

  • etalaj - zòn kote rechèch la pral fèt;
  • nimewo liy - nimewo liy yo dwe fouye nan etalaj espesifye a. Si nimewo ranje a pa konnen, yo dwe espesifye nimewo kolòn lan;
  • nimewo kolòn - nimewo kolòn yo ka jwenn nan etalaj la espesifye. Si valè a pa konnen, yon nimewo liy obligatwa.

Yon egzanp yon fòmil senp:

=INDEX(A1:S10,2,3)

Fonksyon an ap chèche nan seri ant A1 ak C10. Nimewo yo montre ki ranje (2) ak kolòn (3) pou montre valè vle a. Rezilta a pral selil C2.

Assez senp, vre? Men, lè w ap travay ak dokiman reyèl, ou pa gen anpil chans pou w gen enfòmasyon konsènan nimewo kolòn oswa selil yo. Se pou sa fonksyon MATCH() la.

MATCH Fonksyon Sentaks ak Itilizasyon

Fonksyon MATCH() chèche valè vle a epi li montre nimewo apwoksimatif li nan zòn rechèch espesifye a.

Sentaks searchpos() sanble sa a:

=MATCH(valè pou chèche, etalaj pou chèche, kalite matche)

  • valè rechèch - nimewo a oswa tèks yo dwe jwenn;
  • searched array - zòn kote rechèch la pral fèt;
  • Kalite matche ak - presize si yo gade pou valè egzak la oswa valè ki pi pre li:
    • 1 (oswa pa gen okenn valè espesifye) – retounen pi gwo valè ki egal a oswa mwens pase valè ki te espesifye a;
    • 0 - montre yon matche egzak ak valè rechèch la. Nan konbinezon INDEX() MATCH() ou pral prèske toujou bezwen yon match egzak, kidonk nou ekri 0;
    • -1 – Montre pi piti valè ki pi gran pase oswa egal a valè ki espesifye nan fòmil la. Se klasman fèt nan lòd desandan.

Pou egzanp, nan seri a B1: B3 New York, Paris, London yo anrejistre. Fòmil ki anba a pral montre nimewo 3 a paske London se twazyèm nan lis la:

=EKSPOZE(London,B1:B3,0)

Ki jan yo travay ak fonksyon INDEX MATCH la 

Ou te pwobableman deja kòmanse konprann prensip la pa ki travay ansanm nan fonksyon sa yo bati. An brèf, lè sa a INDEX() chèche valè vle a nan mitan ranje ak kolòn espesifye yo. Epi MATCH() montre nimewo valè sa yo:

=INDEX(kolòn kote valè a retounen, MATCH(valè pou chèche, kolòn pou chèche, 0))

Toujou gen yon tan difisil konprann ki jan li fonksyone? Petèt yon egzanp pral eksplike pi byen. Sipoze ou gen yon lis kapital mondyal ak popilasyon yo:

Pou konnen gwosè popilasyon an nan yon kapital sèten, pou egzanp, kapital la nan Japon, nou itilize fòmil sa a:

=INDEX(C2:C10, MATCH(Japon, A2:A10,0))

Eksplikasyon:

  • Fonksyon MATCH() la chèche valè - "Japon" nan etalaj A2:A10 la epi li retounen nimewo 3 a, paske Japon se twazyèm valè nan lis la. 
  • Figi sa a ale nannimewo liy lan” nan fòmil la INDEX () epi li di fonksyon an enprime yon valè nan ranje sa a.

Se konsa, fòmil ki anwo a vin fòmil estanda a ENDEX(C2:C10,3). Fòmil la chèche soti nan selil C2 rive nan C10 epi retounen done ki soti nan twazyèm selil nan seri sa a, se sa ki, C4, paske dekont la kòmanse nan dezyèm ranje a.

Ou pa vle preskri non vil la nan fòmil la? Apre sa, ekri li nan nenpòt selil, di F1, epi sèvi ak li kòm yon referans nan fòmil MATCH(). Epi ou fini ak yon fòmil rechèch dinamik:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Poukisa INDEX ak MATCH yo pi bon pase VLOOKUP nan Excel

Enpòtan! Kantite liy nan etalaj INDEX() dwe menm jan ak kantite ranje nan konsidere kòm etalaj nan MATCH(), sinon w ap jwenn move rezilta.

Tann yon minit, poukisa pa jis itilize fòmil VLOOKUP() la?

=RECHERCHEV(F1, A2:C10, 3, Fo)

 Ki sa ki nan pwen nan gaspiye tan ap eseye kalkile tout konplèks sa yo nan INDEX MATCH?

Nan ka sa a, li pa enpòtan ki fonksyon yo itilize. Sa a se jis yon egzanp pou konprann kijan fonksyon INDEX() ak MATCH() travay ansanm. Lòt egzanp pral montre kisa fonksyon sa yo kapab nan sitiyasyon kote VLOOKUP a pa gen fòs. 

ENDEX MATCH oswa VLOOKUP

Lè n ap deside ki fòmil rechèch pou itilize, anpil moun dakò ke INDEX () ak MATCH () yo pi wo pase VLOOKUP. Sepandan, anpil moun toujou itilize VLOOKUP(). Premyèman, VLOOKUP () se pi senp, epi dezyèmman, itilizatè yo pa byen konprann tout avantaj ki genyen nan travay ak INDEX () ak MATCH (). San konesans sa a, pèsonn p'ap dakò pase tan yo etidye yon sistèm konplèks.

Men avantaj kle INDEX() ak MATCH() sou VLOOKUP():

 

  • Rechèch de dwat a goch. VLOOKUP() pa ka fè rechèch de dwat a goch, kidonk valè w ap chèche yo dwe toujou nan kolòn ki pi goch nan tablo a. Men INDEX () ak MATCH () ka jere sa san pwoblèm. Atik sa a pral di w ki sa li sanble nan pratik: ki jan yo jwenn valè a vle sou bò gòch la.

 

  1. San danje adisyon oswa retire kolòn. Fòmil VLOOKUP() la montre rezilta kòrèk lè w retire oswa ajoute kolòn paske VLOOKUP() bezwen nimewo kolòn egzak la pou gen siksè. Natirèlman, lè yo ajoute oswa retire kolòn, nimewo yo chanje tou. 

Epi nan fòmil INDEX () ak MATCH (), yo espesifye yon seri kolòn, pa kolòn endividyèl yo. Kòm yon rezilta, ou ka san danje ajoute epi retire kolòn san yo pa bezwen mete ajou fòmil la chak fwa.

  1. Pa gen limit sou volim rechèch. Lè w ap itilize VLOOKUP(), kantite total kritè rechèch la pa dwe depase 255 karaktè oswa w ap jwenn yon #VALÈ! Se konsa, si done ou yo gen yon gwo kantite karaktè, INDEX () ak MATCH () se opsyon ki pi bon.
  2. Segondè vitès pwosesis. Si tab ou yo relativman piti, Lè sa a, ou pa gen anpil chans remake nenpòt diferans. Men, si tab la gen dè santèn oswa dè milye de ranje, epi, kòmsadwa, gen dè santèn ak dè milye de fòmil, INDEX () ak MATCH () ap fè fas pi vit pase VLOOKUP (). Reyalite a se ke Excel pral trete sèlman kolòn yo espesifye nan fòmil la, olye pou yo trete tab la tout antye. 

Enpak pèfòmans VLOOKUP() pral espesyalman aparan si fèy travay ou a gen yon gwo kantite fòmil tankou VLOOKUP() ak SUM(). Chèk separe nan fonksyon VLOOKUP() yo oblije analize chak valè nan yon etalaj. Se konsa, Excel gen trete yon gwo kantite enfòmasyon, ak sa a ralanti travay la anpil.

Egzanp fòmil yo 

Nou te deja kalkile itilite fonksyon sa yo, pou nou ka ale nan pati ki pi enteresan an: aplikasyon konesans nan pratik.

Fòmil pou fè rechèch de dwat a goch

Kòm deja mansyone, VLOOKUP pa ka fè fòm rechèch sa a. Se konsa, si valè yo vle yo pa nan kolòn ki pi goch la, VLOOKUP () pa pral pwodwi yon rezilta. Fonksyon INDEX () ak MATCH () yo pi versatile, ak kote valè yo pa jwe yon gwo wòl pou yo travay.

Pa egzanp, nou pral ajoute yon kolòn ran sou bò gòch tablo nou an epi eseye kalkile ki ran an tèm de popilasyon kapital peyi nou an okipe.

Nan selil G1, nou ekri valè a yo dwe jwenn, ak Lè sa a, sèvi ak fòmil sa a pou fè rechèch nan seri a C1: C10 epi retounen valè ki koresponn lan soti nan A2: A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Poukisa INDEX ak MATCH yo pi bon pase VLOOKUP nan Excel

Èd memwa. Si w gen plan pou itilize fòmil sa a pou plizyè selil, asire w ke ou ranje seri yo lè l sèvi avèk adrès absoli (pa egzanp, $A$2: $A$10 ak $C$2: 4C$10).

INDEX PLUS EXPOSED PLUS EXPOSED  pou fè rechèch nan kolòn ak ranje

Nan egzanp ki anwo yo, nou te itilize fonksyon sa yo kòm yon ranplasman pou VLOOKUP() pou retounen valè ki soti nan yon seri ranje predefini. Men, e si ou bezwen fè yon rechèch matris oswa de kote?

Li sanble konplike, men fòmil pou kalkil sa yo sanble ak fòmil estanda INDEX() MATCH(), ak yon sèl diferans: fòmil MATCH() dwe itilize de fwa. Premye fwa pou jwenn nimewo ranje a, epi dezyèm fwa pou jwenn nimewo kolòn lan:

=INDEX(etalaj, MATCH (valè rechèch vètikal, kolòn rechèch, 0), MATCH (valè rechèch orizontal, ranje rechèch, 0))

Ann gade nan tablo ki anba a epi eseye fè yon fòmil ENDEX() EXPRESS() EXPRESS() yo nan lòd yo montre demografik nan yon peyi espesifik pou yon ane chwazi.

Peyi sib la se nan selil G1 (recherche vètikal) ak ane sib la se nan selil G2 (recherche orizontal). Fòmil la pral sanble tankou sa a:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Poukisa INDEX ak MATCH yo pi bon pase VLOOKUP nan Excel

Ki jan fòmil sa a fonksyone

Menm jan ak nenpòt lòt fòmil konplèks, yo pi fasil pou konprann lè yo kraze yo an ekwasyon endividyèl yo. Apre sa, ou ka konprann sa chak fonksyon endividyèl fè:

  • MATCH(G1,A2:A11,0) – chèche yon valè (G1) nan seri A2:A11 epi li montre kantite valè sa a, nan ka nou an li se 2;
  • CHÈCH(G2,B1:D1,0) – chèche yon valè (G2) nan seri B1:D1. Nan ka sa a, rezilta a te 3.

Yo voye nimewo ranje ak kolòn yo jwenn nan valè ki koresponn lan nan fòmil INDEX() la:

=INDEX(B2:D11,2,3)

Kòm yon rezilta, nou gen yon valè ki nan yon selil nan entèseksyon 2 ranje ak 3 kolòn nan seri B2:D11. Ak fòmil la montre valè a vle, ki se nan selil D3.

Chèche pa plizyè kondisyon ak INDEX ak MATCH

Si ou te li gid nou pou VLOOKUP(), ou te pwobableman eseye plizyè fòmil rechèch. Men, metòd rechèch sa a gen yon sèl limit enpòtan - bezwen an ajoute yon kolòn oksilyè.

Men, bon nouvèl la se sa Avèk INDEX() ak MATCH() ou ka chèche plizyè kondisyon san yo pa bezwen modifye oswa chanje fèy travay ou a.

Men fòmil rechèch jeneral plizyè kondisyon pou INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Nòt la: fòmil sa a dwe itilize ansanm ak chemen kout klavye a CTRL+MAJ+ENTRE.

Sipoze ou bezwen jwenn valè w ap chèche a baze sou 2 kondisyon: Achtè и Pwodwi.

Sa a mande fòmil sa a:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Nan fòmil sa a, C2:C10 se seri kote rechèch la pral fèt, F1 - kondisyon sa a, A2:A10 - se seri a pou konpare kondisyon an, F2 - kondisyon 2, V2:V10 - ranje pou konparezon kondisyon 2.

Pa bliye peze konbinezon an nan fen travay la ak fòmil la Ctrl + MAJ + ANTRE - Excel pral otomatikman fèmen fòmil la ak aparèy òtopedik boukle, jan yo montre nan egzanp lan:

Poukisa INDEX ak MATCH yo pi bon pase VLOOKUP nan Excel

Si ou pa vle sèvi ak yon fòmil etalaj pou travay ou, Lè sa a, ajoute yon lòt INDEX () nan fòmil la epi peze ENTER, li pral sanble nan egzanp lan:

Poukisa INDEX ak MATCH yo pi bon pase VLOOKUP nan Excel

Ki jan fòmil sa yo travay

Fòmil sa a travay menm jan ak fòmil estanda INDEX() MATCH(). Pou chèche plizyè kondisyon, ou senpleman kreye plizyè kondisyon fo ak vrè ki reprezante kondisyon endividyèl kòrèk ak kòrèk. Lè sa a, kondisyon sa yo aplike nan tout eleman korespondan nan etalaj la. Fòmil la konvèti agiman Fo ak Vrè a 0 ak 1, respektivman, epi li bay yon etalaj kote 1 se valè matche yo ki te jwenn nan fisèl la. MATCH() ap jwenn premye valè ki matche ak 1 epi pase l nan fòmil INDEX(). Epi li, nan vire, pral retounen valè a deja vle nan liy ki espesifye soti nan kolòn nan vle.

Yon fòmil san yon etalaj depann de kapasite INDEX() pou okipe yo poukont li. Dezyèm INDEX() nan fòmil la matche ak falsy (0), kidonk li pase tout etalaj la ak valè sa yo nan fòmil MATCH (). 

Sa a se yon eksplikasyon byen long sou lojik ki dèyè fòmil sa a. Pou plis enfòmasyon li atik la "ENDEX MATCH ak plizyè kondisyon'.

MWAYEN, MAX ak MIN nan ENDEX ak MATCH

Excel gen pwòp fonksyon espesyal li pou jwenn mwayèn, maksimòm, ak minimòm. Men, e si ou vle jwenn done ki soti nan selil ki asosye ak valè sa yo? Nan ka sa AVERAGE, MAX ak MIN dwe itilize ansanm ak INDEX ak MATCH.

ENDEX MATCH ak MAX

Pou jwenn pi gwo valè a nan kolòn D epi montre li nan kolòn C, sèvi ak fòmil la: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH ak MIN

Pou jwenn pi piti valè a nan kolòn D epi montre li nan kolòn C, sèvi ak fòmil sa a:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

RECHÈCHE ENDEX ak sèpan

Pou jwenn valè mwayèn nan kolòn D epi montre valè sa a nan C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Tou depan de fason done ou yo ekri, twazyèm agiman MATCH() se swa 1, 0, oswa -1:

  • si kolòn yo klase nan lòd monte, mete 1 (Lè sa a, fòmil la pral kalkile valè a maksimòm, ki se mwens pase oswa egal a valè an mwayèn);
  • si sòt la ap desann, Lè sa a, -1 (fòmil la pral bay valè minimòm ki pi gran pase oswa egal a mwayèn);
  • si etalaj rechèch la gen yon valè ki egzakteman egal a mwayèn, Lè sa a, mete l sou 0. 

 Nan egzanp nou an, popilasyon an klase nan lòd desandan, kidonk nou mete -1. Ak rezilta a se Tokyo, depi valè popilasyon an (13,189) se pi pre valè an mwayèn (000).

Poukisa INDEX ak MATCH yo pi bon pase VLOOKUP nan Excel

VLOOKUP() kapab tou fè kalkil sa yo, men se sèlman kòm yon fòmil etalaj: VLOOKUP ak MWAYÈN, MIN ak MAX.

ENDEX MATCH ak ESND/IFERROR

Ou pwobableman deja remake ke si fòmil la pa ka jwenn valè a vle, li voye yon erè # N / A. Ou ka ranplase mesaj erè estanda a ak yon bagay ki pi enfòmatif. Pa egzanp, mete agiman an nan fòmil la Nan XNUMXyèm la:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Avèk fòmil sa a, si ou antre done ki pa nan tablo a, fòm nan ap ba ou mesaj espesifye a.

Poukisa INDEX ak MATCH yo pi bon pase VLOOKUP nan Excel

Si ou vle trape tout erè, lè sa a eksepte pou Nan XNUMXyèm la kapab itilize SI ERREUR:

=SI ERREUR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Yon bagay te mal!")

Men, sonje ke maske erè nan fason sa a se pa yon bon lide, paske erè estanda rapòte vyolasyon nan fòmil la.

Nou espere ou te jwenn gid nou an pou itilize fonksyon INDEX MATCH() itil.

Kite yon Reply