Pse INDEX dhe MATCH janë më të mirë se VLOOKUP në Excel

Ne u kemi shpjeguar më parë fillestarëve se si të përdorin funksionet bazë të VLOOKUP (anglisht VLOOKUP, shkurtesa qëndron për "funksionin e kërkimit vertikal"). Dhe përdoruesve me përvojë iu treguan disa formula më të ndërlikuara.

Dhe në këtë artikull do të përpiqemi të japim informacion në lidhje me një metodë tjetër të punës me kërkim vertikal.

Ju mund të pyesni veten: "Pse është e nevojshme kjo?". Dhe kjo është e nevojshme për të treguar të gjitha metodat e mundshme të kërkimit. Për më tepër, kufizime të shumta VLOOKUP shpesh pengojnë marrjen e rezultatit të dëshiruar. Në këtë drejtim, INDEX( ) MATCH( ) është shumë më funksional dhe i larmishëm, dhe gjithashtu kanë më pak kufizime.

Bazat INDEKSI NDESHJE

Meqenëse qëllimi i këtij udhëzuesi është të tregojë se sa e mirë është kjo veçori, ne Le të shohim informacionin bazë në lidhje me parimet e funksionimit të tij. Dhe ne do të tregojmë shembuj, dhe gjithashtu do të shqyrtojmë pse, është më mirë se VLOOKUP ().

Sintaksa dhe përdorimi i funksionit INDEX

Ky funksion ndihmon në gjetjen e vlerës së dëshiruar midis zonave të specifikuara të kërkimit bazuar në numrin e kolonës ose linjës. Sintaksë:

=INDEX (vargu, numri i rreshtit, numri i kolonës):

• grup - zona në të cilën do të bëhet kërkimi;
• numri i linjës – numri i rreshtit që do të kërkohet në grupin e specifikuar. Nëse numri i rreshtit është i panjohur, duhet të specifikohet numri i kolonës;
• numri i kolonës – numri i kolonës që gjendet në grupin e specifikuar. Nëse vlera është e panjohur, kërkohet një numër rreshti.

Një shembull i një formule të thjeshtë:

=INDEX(A1:S10,2,3)

Funksioni do të kërkojë në intervalin nga A1 në C10. Numrat tregojnë se nga cili rresht (2) dhe kolonë (3) duhet të tregohet vlera e dëshiruar. Rezultati do të jetë qeliza C2.

Shumë e thjeshtë, apo jo? Por kur punoni me dokumente reale, nuk ka gjasa të keni informacion në lidhje me numrat e kolonave ose qelizat. Për këtë shërben funksioni MATCH().

Sintaksa dhe përdorimi i funksionit MATCH

Funksioni MATCH() kërkon vlerën e dëshiruar dhe tregon numrin e përafërt të saj në zonën e specifikuar të kërkimit.

Sintaksa searchpos() duket si kjo:

=MATCH (vlera për kërkim, grup për kërkim, lloji i përputhjes)

• vlera e kërkimit – numri ose teksti që do të gjendet;
• grup i kërkuar - zona ku do të bëhet kërkimi;
• lloji i përputhjes - specifikon nëse do të kërkohet vlera e saktë ose vlerat më të afërta me të:
  • 1 (ose asnjë vlerë e specifikuar) – kthen vlerën më të madhe që është e barabartë ose më e vogël se vlera që është specifikuar;
  • 0 - tregon një përputhje të saktë me vlerën e kërkuar. Në kombinimin INDEX() MATCH() do t'ju duhet pothuajse gjithmonë një përputhje e saktë, kështu që ne shkruajmë 0;
  • -1 - Tregon vlerën më të vogël që është më e madhe ose e barabartë me vlerën e specifikuar në formulë. Renditja kryhet në rend zbritës.

Për shembull, në rangun B1:B3 janë regjistruar New York, Paris, Londër. Formula e mëposhtme do të tregojë numrin 3 sepse Londra është e treta në listë:

=EXPOSE(Londër,B1:B3,0)

Si të punoni me funksionin INDEX MATCH 

Me siguri tashmë keni filluar të kuptoni parimin me të cilin ndërtohet puna e përbashkët e këtyre funksioneve. Me pak fjalë, atëherë INDEX() kërkon vlerën e dëshiruar midis rreshtave dhe kolonave të specifikuara. Dhe MATCH() tregon numrat e këtyre vlerave:

=INDEX(kolona nga e cila është kthyer vlera, MATCH(vlera për kërkim, kolona për kërkim, 0))

Ende e keni të vështirë të kuptoni se si funksionon? Ndoshta një shembull do ta shpjegojë më mirë. Supozoni se keni një listë të kryeqyteteve botërore dhe popullsisë së tyre:

Për të zbuluar madhësinë e popullsisë së një kryeqyteti të caktuar, për shembull, kryeqyteti i Japonisë, ne përdorim formulën e mëposhtme:

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

Shpjegim:

• Funksioni MATCH() kërkon vlerën – “Japan” në grupin A2:A10 dhe kthen numrin 3, sepse Japonia është vlera e tretë në listë. 
• Kjo shifër shkon nëNumri i linjes” në formulën INDEX() dhe i tregon funksionit të printojë një vlerë nga ky rresht.

Pra formula e mësipërme bëhet formula standarde INDEKSI(C2:C10,3). Formula kërkon nga qelizat C2 në C10 dhe kthen të dhëna nga qeliza e tretë në këtë interval, domethënë C4, sepse numërimi mbrapsht fillon nga rreshti i dytë.

Nuk doni të përshkruani emrin e qytetit në formulë? Më pas shkruajeni atë në çdo qelizë, le të themi F1, dhe përdorni atë si referencë në formulën MATCH(). Dhe përfundoni me një formulë kërkimi dinamik:

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

E rëndësishme! Numri i rreshtave në grup INDEX() duhet të jetë i njëjtë me numrin e rreshtave brenda grup i konsideruar në MATCH(), përndryshe do të merrni rezultatin e gabuar.

Prisni një minutë, pse të mos përdorni vetëm formulën VLOOKUP()?

=VLOOKUP(F1, A2:C10, 3, E gabuar)

 Cili është qëllimi për të humbur kohë duke u përpjekur të kuptoni të gjitha këto kompleksitete të INDEX MATCH?

Në këtë rast, nuk ka rëndësi se cilin funksion të përdorni. Ky është vetëm një shembull për të kuptuar se si funksionet INDEX() dhe MATCH() funksionojnë së bashku. Shembuj të tjerë do të tregojnë se çfarë janë në gjendje të bëjnë këto funksione në situata ku VLOOKUP është i pafuqishëm. 

INDEKSI MATCH ose VLOOKUP

Kur vendoset se cila formulë kërkimi të përdoret, shumë pajtohen që INDEX() dhe MATCH() janë shumë më të larta se VLOOKUP. Megjithatë, shumë njerëz ende përdorin VLOOKUP(). Së pari, VLOOKUP() është më e thjeshtë, dhe së dyti, përdoruesit nuk i kuptojnë plotësisht të gjitha avantazhet e punës me INDEX() dhe MATCH(). Pa këtë njohuri, askush nuk do të pranojë të kalojë kohën e tij duke studiuar një sistem kompleks.

Këtu janë avantazhet kryesore të INDEX() dhe MATCH() mbi VLOOKUP():

• Kërkoni nga e djathta në të majtë. VLOOKUP () nuk mund të kërkojë nga e djathta në të majtë, kështu që vlerat që kërkoni duhet të jenë gjithmonë në kolonat më të majta të tabelës. Por INDEX() dhe MATCH() mund ta trajtojnë këtë pa problem. Ky artikull do t'ju tregojë se si duket në praktikë: si të gjeni vlerën e dëshiruar në anën e majtë.

1. Shtimi ose heqja e sigurt e kolonave. Formula VLOOKUP() tregon rezultate të pasakta kur hiqni ose shtoni kolona sepse VLOOKUP() ka nevojë për numrin e saktë të kolonës për të pasur sukses. Natyrisht, kur kolonat shtohen ose hiqen, numri i tyre gjithashtu ndryshon. 

Dhe në formulat INDEX() dhe MATCH(), specifikohet një varg kolonash, jo kolona individuale. Si rezultat, ju mund të shtoni dhe hiqni me siguri kolona pa pasur nevojë të përditësoni formulën çdo herë.

1. Nuk ka kufizime në vëllimet e kërkimit. Kur përdorni VLOOKUP(), numri i përgjithshëm i kritereve të kërkimit nuk duhet të kalojë 255 karaktere ose do të merrni një #VALUE! Pra, nëse të dhënat tuaja përmbajnë një numër të madh karakteresh, INDEX() dhe MATCH() janë opsioni më i mirë.
2. Shpejtësi e lartë e përpunimit. Nëse tavolinat tuaja janë relativisht të vogla, atëherë nuk ka gjasa të vini re ndonjë ndryshim. Por, nëse tabela përmban qindra ose mijëra rreshta, dhe, në përputhje me rrethanat, ka qindra e mijëra formula, INDEX () dhe MATCH () do të përballen shumë më shpejt se VLOOKUP (). Fakti është se Excel do të përpunojë vetëm kolonat e specifikuara në formulë, në vend që të përpunojë të gjithë tabelën. 

Ndikimi i performancës së VLOOKUP() do të jetë veçanërisht i dukshëm nëse fleta juaj e punës përmban një numër të madh formulash si VLOOKUP() dhe SUM(). Kërkohen kontrolle të veçanta të funksioneve VLOOKUP() për të analizuar secilën vlerë në një grup. Pra, Excel duhet të përpunojë një sasi të madhe informacioni, dhe kjo ngadalëson ndjeshëm punën.

Shembuj të formulave 

Ne kemi kuptuar tashmë dobinë e këtyre funksioneve, kështu që mund të kalojmë në pjesën më interesante: aplikimin e njohurive në praktikë.

Formula për të kërkuar nga e djathta në të majtë

Siç u përmend tashmë, VLOOKUP nuk mund të kryejë këtë formë kërkimi. Pra, nëse vlerat e dëshiruara nuk janë në kolonën më të majtë, VLOOKUP() nuk do të prodhojë rezultat. Funksionet INDEX() dhe MATCH() janë më të gjithanshëm dhe vendndodhja e vlerave nuk luan një rol të madh që ato të funksionojnë.

Për shembull, ne do të shtojmë një kolonë renditjeje në anën e majtë të tabelës sonë dhe do të përpiqemi të kuptojmë se çfarë renditje për nga popullsia zë kryeqyteti i vendit tonë.

Në qelizën G1, ne shkruajmë vlerën që do të gjendet dhe më pas përdorim formulën e mëposhtme për të kërkuar në rangun C1:C10 dhe kthejmë vlerën përkatëse nga A2:A10:

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

Prompt. Nëse planifikoni të përdorni këtë formulë për shumë qeliza, sigurohuni që të rregulloni intervalet duke përdorur adresimin absolut (për shembull, $A$2: $A$10 dhe $C$2: 4C$10).

INDEKSI MË TË EKSPOZUAR MË TË EKSPOZUAR  për të kërkuar në kolona dhe rreshta

Në shembujt e mësipërm, ne kemi përdorur këto funksione si një zëvendësim për VLOOKUP() për të kthyer vlerat nga një gamë e paracaktuar rreshtash. Por çfarë nëse duhet të bëni një kërkim matricë ose të dyanshëm?

Duket e ndërlikuar, por formula për llogaritje të tilla është e ngjashme me formulën standarde INDEX() MATCH(), me vetëm një ndryshim: formula MATCH() duhet të përdoret dy herë. Herën e parë për të marrë numrin e rreshtit dhe herën e dytë për të marrë numrin e kolonës:

=INDEX(vargu, MATCH (vlera vertikale e kërkimit, kolona e kërkimit, 0), MATCH (vlera horizontale e kërkimit, rreshti i kërkimit, 0))

Le të shohim tabelën më poshtë dhe të përpiqemi të bëjmë një formulë INDEX() EXPRESS() EXPRESS() për të shfaqur demografinë në një vend të caktuar për një vit të zgjedhur.

Vendi i synuar është në qelizën G1 (kërkim vertikal) dhe viti i synuar është në qelizën G2 (kërkim horizontal). Formula do të duket si kjo:

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

Si funksionon kjo formulë

Ashtu si çdo formula tjetër komplekse, ato janë më të lehta për t'u kuptuar duke i zbërthyer në ekuacione individuale. Dhe pastaj mund të kuptoni se çfarë bën secili funksion individual:

• Ndeshja (G1,A2:A11,0) – kërkon një vlerë (G1) në intervalin A2:A11 dhe tregon numrin e kësaj vlere, në rastin tonë është 2;
• KËRKO (G2,B1:D1,0) – kërkon një vlerë (G2) në diapazonin B1:D1. Në këtë rast, rezultati ishte 3.

Numrat e rreshtave dhe kolonave të gjetura dërgohen në vlerën përkatëse në formulën INDEX():

=INDEX(B2:D11,2,3)

Si rezultat, ne kemi një vlerë që është në një qelizë në kryqëzimin e 2 rreshtave dhe 3 kolonave në intervalin B2:D11. Dhe formula tregon vlerën e dëshiruar, e cila është në qelizën D3.

Kërkoni sipas kushteve të shumta me INDEX dhe MATCH

Nëse e keni lexuar udhëzuesin tonë për VLOOKUP(), ndoshta keni provuar formula të shumta kërkimi. Por kjo metodë kërkimi ka një kufizim të rëndësishëm - nevojën për të shtuar një kolonë ndihmëse.

Por lajmi i mirë është se Me INDEX() dhe MATCH() mund të kërkoni për kushte të shumta pa pasur nevojë të modifikoni ose ndryshoni fletën tuaj të punës.

Këtu është formula e përgjithshme e kërkimit me shumë kushte për INDEX() MATCH():

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

Shënimi: kjo formulë duhet të përdoret së bashku me shkurtoren e tastierës CTRL+SHIFT+ENTER.

Supozoni se ju duhet të gjeni vlerën që kërkoni bazuar në 2 kushte: Blerës и Produkt.

Kjo kërkon formulën e mëposhtme:

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

Në këtë formulë, C2:C10 është diapazoni në të cilin do të bëhet kërkimi, F1 - kjo gjendje, A2: A10 - është diapazoni për të krahasuar gjendjen, F2 - gjendja 2, V2: V10 – diapazoni për krahasimin e kushtit 2.

Mos harroni të shtypni kombinimin në fund të punës me formulën CTRL+SHIFT+ENTER – Excel do të mbyllë automatikisht formulën me kllapa kaçurrelë, siç tregohet në shembullin:

Nëse nuk dëshironi të përdorni një formulë grupi për punën tuaj, atëherë shtoni një tjetër INDEX() në formulë dhe shtypni ENTER, do të duket si në shembull:

Si funksionojnë këto formula

Kjo formulë funksionon në të njëjtën mënyrë si formula standarde INDEX() MATCH(). Për të kërkuar kushte të shumta, thjesht krijoni kushte të shumta False dhe True që përfaqësojnë kushtet individuale të sakta dhe të pasakta. Dhe pastaj këto kushte zbatohen për të gjithë elementët përkatës të grupit. Formula konverton argumentet False dhe True në 0 dhe 1, përkatësisht, dhe nxjerr një grup ku 1 është vlerat që përputhen që u gjetën në varg. MATCH() do të gjejë vlerën e parë që përputhet me 1 dhe do ta kalojë atë në formulën INDEX(). Dhe ajo, nga ana tjetër, do të kthejë vlerën e dëshiruar tashmë në rreshtin e specifikuar nga kolona e dëshiruar.

Një formulë pa një grup varet nga aftësia e INDEX() për t'i trajtuar ato vetë. INDEX() i dytë në formulë përputhet me falsitetin (0), kështu që e kalon të gjithë grupin me ato vlera në formulën MATCH(). 

Ky është një shpjegim mjaft i gjatë i logjikës që qëndron pas kësaj formule. Për më shumë informacion lexoni artikullin "NDESHJE INDEKSI me kushte të shumta'.

AVERAGE, MAX dhe MIN në INDEX dhe MATCH

Excel ka funksionet e veta të veçanta për gjetjen e mesatareve, maksimaleve dhe minimumeve. Por, çka nëse doni të merrni të dhëna nga qeliza e lidhur me ato vlera? Në këtë rast AVERAGE, MAX dhe MIN duhet të përdoren në lidhje me INDEX dhe MATCH.

INDEX MATCH dhe MAX

Për të gjetur vlerën më të madhe në kolonën D dhe për ta shfaqur atë në kolonën C, përdorni formulën: 

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

Ndeshja e INDEKSIT dhe MIN

Për të gjetur vlerën më të vogël në kolonën D dhe për ta shfaqur atë në kolonën C, përdorni formulën e mëposhtme:

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

INDEKSI I KËRKIMIT dhe Gjarpri

Për të gjetur vlerën mesatare në kolonën D dhe për të shfaqur këtë vlerë në C:

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

Në varësi të mënyrës se si shkruhen të dhënat tuaja, argumenti i tretë për MATCH() është ose 1, 0 ose -1:

• nëse kolonat janë renditur në rend rritës, vendosni 1 (atëherë formula do të llogarisë vlerën maksimale, e cila është më e vogël ose e barabartë me vlerën mesatare);
• nëse renditja është zbritëse, atëherë -1 (formula do të nxjerrë vlerën minimale që është më e madhe ose e barabartë me mesataren);
• nëse grupi i kërkimit përmban një vlerë që është saktësisht e barabartë me mesataren, atëherë vendoseni atë në 0. 

 Në shembullin tonë, popullsia është renditur në rend zbritës, kështu që vendosim -1. Dhe rezultati është Tokio, pasi vlera e popullsisë (13,189) është më e afërta me vlerën mesatare (000).

VLOOKUP() gjithashtu mund të kryejë llogaritje të tilla, por vetëm si formulë grupi: VLOOKUP me AVERAGE, MIN dhe MAX.

INDEX MATCH dhe ESND/IFERROR

Ju ndoshta keni vënë re tashmë se nëse formula nuk mund të gjejë vlerën e dëshiruar, ajo hedh një gabim # N / A. Ju mund ta zëvendësoni mesazhin standard të gabimit me diçka më informuese. Për shembull, vendosni argumentin në formulë Në datën XNUMX:

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

Me këtë formulë, nëse futni të dhëna që nuk janë në tabelë, formulari do t'ju japë mesazhin e specifikuar.

Nëse doni të kapni të gjitha gabimet, atëherë përveç Në datën XNUMX mund të përdoren IFERROR:

=IFERROR(INDEKS(C2:C10,MATCH(F1,A2:A10,0)), "Diçka shkoi keq!")

Por mbani mend se maskimi i gabimeve në këtë mënyrë nuk është një ide e mirë, sepse gabimet standarde raportojnë shkelje në formulë.

Shpresojmë që të keni gjetur të dobishëm udhëzuesin tonë për përdorimin e funksionit INDEX MATCH().