Si të përdorni funksionin INDEX dhe MATCH në Excel

2024 Autor: Abigail Brown | [email protected]. E modifikuara e fundit: 2023-12-17 07:01

Çfarë duhet të dini

Funksioni INDEX mund të përdoret vetëm, por futja e funksionit MATCH brenda tij krijon një kërkim të avancuar.
Ky funksion i ndërthurur është më fleksibël se VLOOKUP dhe mund të japë rezultate më shpejt.

Ky artikull shpjegon se si të përdorni funksionet INDEX dhe MATCH së bashku në të gjitha versionet e Excel, duke përfshirë Excel 2019 dhe Microsoft 365.

Cilat janë funksionet INDEX dhe MATCH?

INDEX dhe MATCH janë funksione të kërkimit në Excel. Ndërsa janë dy funksione krejtësisht të ndara që mund të përdoren më vete, ato gjithashtu mund të kombinohen për të krijuar formula të avancuara.

Funksioni INDEX kthen një vlerë ose referencë për një vlerë nga brenda një përzgjedhjeje të caktuar. Për shembull, mund të përdoret për të gjetur vlerën në rreshtin e dytë të një grupi të dhënash, ose në rreshtin e pestë dhe kolonën e tretë.

Ndërsa INDEX mund të përdoret shumë mirë vetëm, vendosja e MATCH në formulë e bën atë pak më të dobishëm. Funksioni MATCH kërkon për një artikull të caktuar në një varg qelizash dhe më pas kthen pozicionin relativ të artikullit në interval. Për shembull, mund të përdoret për të përcaktuar se një emër specifik është artikulli i tretë në një listë emrash.

INDEX dhe MATCH Sintaksa & Argumentet

Kështu duhet të shkruhen të dy funksionet në mënyrë që Excel t'i kuptojë ato:

=INDEX(arrit, row_num, [column_num])

array është diapazoni i qelizave që formula do të përdorë. Mund të jetë një ose më shumë rreshta dhe kolona, të tilla si A1:D5. Është e nevojshme.
row_num është rreshti në grup nga i cili duhet të kthehet një vlerë, si p.sh. 2 ose 18. Kërkohet përveç nëse është i pranishëm numri_kolona.
column_num është kolona në grup nga e cila duhet të kthehet një vlerë, si p.sh. 1 ose 9. Është opsionale.

=MATCH(vlera_kërkimi, grupi_kërkuesish, [lloji_përputhje])

lookup_value është vlera që dëshironi të përputheni në grupin lookup_. Mund të jetë një numër, tekst ose vlerë logjike që shtypet manualisht ose referohet nëpërmjet një referimi qelize. Kjo kërkohet.
lookup_array është diapazoni i qelizave për të parë. Mund të jetë një rresht i vetëm ose një kolonë e vetme, si A2:D2 ose G1:G45. Kjo kërkohet.
lloji_ndeshje mund të jetë -1, 0, ose 1. Ai specifikon se si lookup_value përputhet me vlerat në lookup_array (shih më poshtë). 1 është vlera e paracaktuar nëse ky argument hiqet.

Cili lloj ndeshjeje të përdoret
Lloji i ndeshjes	Çfarë bën	Rregulla	Shembull
1	Gjen vlerën më të madhe që është më e vogël ose e barabartë me lookup_value.	Vlerat e grupit të kërkimit duhet të vendosen në rend rritës (p.sh., -2, -1, 0, 1, 2; ose A-Z;, ose FALSE, TRUE.	lookup_value është 25, por mungon nga lookup_array, kështu që pozicioni i numrit tjetër më të vogël, si 22, kthehet në vend.
0	Gjen vlerën e parë që është saktësisht e barabartë me vlerën lookup_.	Vlerat e grupit të kërkimit mund të jenë në çfarëdo radhe.	lookup_value është 25, kështu që kthen pozicionin prej 25.
-1	Gjen vlerën më të vogël që është më e madhe ose e barabartë me lookup_value.	Vlerat e grupit të kërkimit duhet të vendosen në rend zbritës (p.sh., 2, 1, 0, -1, -2).	lookup_value është 25, por mungon nga lookup_array, kështu që pozicioni i numrit tjetër më të madh, si 34, kthehet në vend.

Përdorni 1 ose -1 për rastet kur duhet të kryeni një kërkim të përafërt përgjatë një shkalle, si p.sh. kur keni të bëni me numra dhe kur përafrimet janë në rregull. Por mbani mend se nëse nuk specifikoni match_type, 1 do të jetë parazgjedhja, gjë që mund të anonojë rezultatet nëse vërtet dëshironi një përputhje të saktë.

Shembull INDEX dhe Formula MATCH

Para se të shikojmë se si të kombinojmë INDEX dhe MATCH në një formulë, duhet të kuptojmë se si funksionojnë këto funksione më vete.

Shembuj INDEX

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEKSI(B1:B2, 1)

Në këtë shembull të parë, ka katër formula INDEX që mund t'i përdorim për të marrë vlera të ndryshme:

=INDEX(A1:B2, 2, 2) shikon përmes A1:B2 për të gjetur vlerën në kolonën e dytë dhe rreshtin e dytë, që është Stacy.
=INDEX(A1:B1, 1) shikon përmes A1:B1 për të gjetur vlerën në kolonën e parë, që është Jon.
=INDEX(2:2, 1) shikon gjithçka në rreshtin e dytë për të gjetur vlerën në kolonën e parë, që është Tim.
=INDEX(B1:B2, 1) shikon përmes B1:B2 për të gjetur vlerën në rreshtin e parë, që është Amy.

MATCH Shembuj

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

Këtu janë katër shembuj të thjeshtë të funksionit MATCH:

=MATCH("Stacy", A2:D2, 0) po kërkon për Stacy në rangun A2:D2 dhe kthen 3 si rezultat.
=MATCH(14, D1:D2) është duke kërkuar për 14 në diapazonin D1:D2, por duke qenë se nuk gjendet në tabelë, MATCH gjen vlerën tjetër më të madhe kjo është më pak ose e barabartë me 14, që në këtë rast është 13, që është në pozicionin 1 të lookup_array.
=MATCH(14, D1:D2, -1) është identike me formulën mbi të, por meqenëse grupi nuk është në rend zbritës siç kërkon -1, marrim një gabim.
=MATCH(13, A1:D1, 0) po kërkon 13 në rreshtin e parë të fletës, i cili kthen 4 pasi është artikulli i katërt në këtë grup.

Shembuj INDEX-MATCH

Këtu janë dy shembuj ku mund të kombinojmë INDEX dhe MATCH në një formulë:

Gjeni referencën e qelizës në tabelën

=INDEX(B2:B5, MATCH(F1, A2:A5))

Ky shembull po vendos formulën MATCH brenda formulës INDEX. Qëllimi është të identifikoni ngjyrën e artikullit duke përdorur numrin e artikullit.

Nëse shikoni imazhin, mund të shihni në rreshtat "Të ndara" se si formulat do të shkruheshin më vete, por duke qenë se po i vendosim foletë, kjo është ajo që po ndodh:

MATCH(F1, A2:A5) po kërkon vlerën F1 (8795) në grupin e të dhënave A2:A5. Nëse numërojmë mbrapsht kolonën, mund të shohim se është 2, kështu që funksioni MATCH sapo e kuptoi.
Rredha INDEX është B2:B5 pasi në fund po kërkojmë vlerën në atë kolonë.
Funksioni INDEX tani mund të rishkruhet kështu pasi 2 është ajo që gjeti MATCH: INDEX(B2:B5, 2, [numri_kolona]).
Meqenëse column_num është opsionale, mund ta heqim atë që të mbetet me këtë: INDEX(B2:B5, 2).
Pra tani, kjo është si një formulë normale INDEX ku po gjejmë vlerën e artikullit të dytë në B2:B5, e cila është e kuqe.

Kërkimi sipas rreshtave dhe titujve të kolonave

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

Në këtë shembull të MATCH dhe INDEX, ne po bëjmë një kërkim të dyanshëm. Ideja është të shohim se sa para kemi fituar nga artikujt e gjelbër në maj. Kjo është vërtet e ngjashme me shembullin e mësipërm, por një formulë shtesë MATCH është e vendosur në INDEX.

MATCH(G1, A2:A13, 0) është artikulli i parë i zgjidhur në këtë formulë. Po kërkon G1 (fjala "Maj") në A2:A13 për të marrë një vlerë të veçantë. Nuk e shohim këtu, por është 5.
MATCH(G2, B1:E1, 0) është formula e dytë MATCH, dhe është vërtet e ngjashme me të parën, por në vend të kësaj është duke kërkuar për G2 (fjala "E gjelbër") në titujt e kolonave në B1:E1. Kjo zgjidh në 3.
Tani mund ta rishkruajmë formulën INDEX si kjo për të vizualizuar se çfarë po ndodh: =INDEX(B2:E13, 5, 3). Kjo është duke kërkuar në të gjithë tabelën, B2:E13, për rreshtin e pestë dhe kolonën e tretë, e cila kthen $180.

Rregullat MATCH dhe INDEX

Ka disa gjëra që duhen mbajtur parasysh kur shkruani formula me këto funksione:

MATCH nuk është i ndjeshëm ndaj shkronjave të mëdha, kështu që shkronjat e mëdha dhe të vogla trajtohen njësoj kur përputhen vlerat e tekstit.
MATCH kthen N/A për arsye të shumta: nëse lloji_përputhje është 0 dhe vlera_kërkuese nuk gjendet nëse lloji_përputhje është -1 dhe grupi_kërkuesi nuk është në rend zbritës, nëse lloji_përputhje është 1 dhe grupi_kërkuesi nuk është në rritje rendit, dhe nëse lookup_array nuk është një rresht apo kolonë e vetme.
Mund të përdorni një karakter të shkretë në argumentin lookup_value nëse tipi_përputhje është 0 dhe vlera_kërkuese është një varg teksti. Një pikëpyetje përputhet me çdo karakter të vetëm dhe një yll përputhet me çdo sekuencë karakteresh (p.sh.g., =MATCH("Jo", 1:1, 0)). Për të përdorur MATCH për të gjetur një pikëpyetje ose yll të vërtetë, shkruani fillimisht ~.
INDEX kthen REF! nëse row_num dhe column_num nuk tregojnë një qelizë brenda grupit.

Funksione të ngjashme të Excel

Funksioni MATCH është i ngjashëm me LOOKUP, por MATCH kthen pozicionin e artikullit në vend të vetë artikullit.

VLOOKUP është një tjetër funksion kërkimi që mund të përdorni në Excel, por ndryshe nga MATCH që kërkon INDEX për kërkime të avancuara, formulave VLOOKUP kanë nevojë vetëm për atë funksion.