Cum se utilizează funcția INDEX și MATCH în Excel

2024 Autor: Abigail Brown | [email protected]. Modificat ultima dată: 2023-12-17 06:59

Ce trebuie să știți

Funcția INDEX poate fi utilizată singură, dar imbricarea funcției MATCH în interiorul acesteia creează o căutare avansată.
Această funcție imbricată este mai flexibilă decât CĂUTARE V și poate da rezultate mai rapid.

Acest articol explică cum să utilizați împreună funcțiile INDEX și MATCH în toate versiunile de Excel, inclusiv Excel 2019 și Microsoft 365.

Ce sunt funcțiile INDEX și MATCH?

INDEX și MATCH sunt funcții de căutare Excel. Deși sunt două funcții complet separate, care pot fi folosite singure, ele pot fi, de asemenea, combinate pentru a crea formule avansate.

Funcția INDEX returnează o valoare sau referința la o valoare dintr-o anumită selecție. De exemplu, ar putea fi folosit pentru a găsi valoarea în al doilea rând al unui set de date sau în al cincilea rând și a treia coloană.

În timp ce INDEX ar putea fi foarte bine folosit singur, imbricarea MATCH în formulă îl face puțin mai util. Funcția MATCH caută un articol specificat într-un interval de celule și apoi returnează poziția relativă a articolului în interval. De exemplu, ar putea fi folosit pentru a determina că un anumit nume este al treilea element dintr-o listă de nume.

INDEX și MATCH Sintaxă și argumente

Așa trebuie scrise ambele funcții pentru ca Excel să le înțeleagă:

=INDEX (matrice, row_num, [column_num])

array este intervalul de celule pe care îl va folosi formula. Poate fi unul sau mai multe rânduri și coloane, cum ar fi A1:D5. Este obligatoriu.
row_num este rândul din matrice din care se returnează o valoare, cum ar fi 2 sau 18. Este necesar, cu excepția cazului în care nu este prezentă column_num.
column_num este coloana din matrice din care se returnează o valoare, cum ar fi 1 sau 9. Este opțional.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value este valoarea cu care doriți să o potriviți în lookup_array. Poate fi un număr, text sau valoare logică care este introdusă manual sau la care se face referire printr-o referință de celulă. Acest lucru este necesar.
lookup_array este intervalul de celule prin care trebuie să te uiți. Poate fi un singur rând sau o singură coloană, cum ar fi A2:D2 sau G1:G45. Acest lucru este necesar.
match_type poate fi -1, 0 sau 1. Specifică modul în care se potrivește lookup_value cu valorile din lookup_array (vezi mai jos). 1 este valoarea implicită dacă acest argument este omis.

Ce tip de potrivire să utilizați
Tip de potrivire	Ce face	Regulă	Exemplu
1	Găsește cea mai mare valoare care este mai mică sau egală cu lookup_value.	Valorile lookup_array trebuie plasate în ordine crescătoare (de exemplu, -2, -1, 0, 1, 2; sau A-Z; sau FALSE, TRUE.	lookup_value este 25, dar lipsește din lookup_array, astfel încât poziția următorului cel mai mic număr, cum ar fi 22, este returnată.
0	Găsește prima valoare care este exact egală cu lookup_value.	Valorile lookup_array pot fi în orice ordine.	lookup_value este 25, deci returnează poziția 25.
-1	Găsește cea mai mică valoare care este mai mare sau egală cu lookup_value.	Valorile lookup_array trebuie plasate în ordine descrescătoare (de exemplu, 2, 1, 0, -1, -2).	lookup_value este 25, dar lipsește din lookup_array, astfel încât poziția următorului cel mai mare număr, cum ar fi 34, este returnată.

Folosiți 1 sau -1 pentru momentele în care trebuie să efectuați o căutare aproximativă de-a lungul unei scale, cum ar fi atunci când aveți de-a face cu numere și când aproximările sunt în regulă. Dar rețineți că, dacă nu specificați tipul de potrivire, 1 va fi valoarea implicită, ceea ce poate modifica rezultatele dacă doriți cu adevărat o potrivire exactă.

Exemplu de formule INDEX și MATCH

Înainte de a ne uita la cum să combinăm INDEX și MATCH într-o singură formulă, trebuie să înțelegem cum funcționează aceste funcții singure.

INDEX Exemple

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

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

În acest prim exemplu, există patru formule INDEX pe care le putem folosi pentru a obține valori diferite:

=INDEX(A1:B2, 2, 2) caută prin A1:B2 pentru a găsi valoarea în a doua coloană și în al doilea rând, care este Stacy.
=INDEX(A1:B1, 1) caută prin A1:B1 pentru a găsi valoarea din prima coloană, care este Jon.
=INDEX(2:2, 1) caută tot ce se află în al doilea rând pentru a găsi valoarea din prima coloană, care este Tim.
=INDEX(B1:B2, 1) caută prin B1:B2 pentru a localiza valoarea din primul rând, care este Amy.

Exemple de potrivire

=MECI(„Stacy”, A2:D2, 0)

=MECI (14, D1:D2)

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

Iată patru exemple simple ale funcției MATCH:

=MATCH(„Stacy”, A2:D2, 0) caută Stacy în intervalul A2:D2 și returnează 3 ca rezultat.
=MATCH(14, D1:D2) caută 14 în intervalul D1:D2, dar deoarece nu este găsit în tabel, MATCH găsește următoarea valoare cea mai mare este mai mic sau egal cu 14, care în acest caz este 13, care se află în poziția 1 din lookup_array.
=MATCH(14, D1:D2, -1) este identică cu formula de mai sus, dar deoarece matricea nu este în ordine descrescătoare, așa cum necesită -1, primim o eroare.
=MATCH(13, A1:D1, 0) caută 13 în primul rând al foii, care returnează 4, deoarece este al patrulea element din această matrice.

Exemple de potrivire cu index

Iată două exemple în care putem combina INDEX și MATCH într-o singură formulă:

Găsiți referința celulei în tabelul

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

Acest exemplu este imbricarea formulei MATCH în formula INDEX. Scopul este de a identifica culoarea articolului folosind numărul articolului.

Dacă te uiți la imagine, poți vedea în rândurile „Separate” cum ar fi scrise formulele de la sine, dar din moment ce le imbricam, iată ce se întâmplă:

MATCH(F1, A2:A5) caută valoarea F1 (8795) în setul de date A2:A5. Dacă numărăm coloana inversă, putem vedea că este 2, așa că tocmai asta a descoperit funcția MATCH.
Matricea INDEX este B2:B5, deoarece în cele din urmă căutăm valoarea din acea coloană.
Funcția INDEX ar putea fi acum rescrisă astfel, deoarece 2 este ceea ce a găsit MATCH: INDEX(B2:B5, 2, [column_num]).
Deoarece column_num este opțional, îl putem elimina pentru a rămâne cu acesta: INDEX(B2:B5, 2).
Deci acum, aceasta este ca o formulă INDEX normală în care găsim valoarea celui de-al doilea element în B2:B5, care este roșu.

Căutare după titluri de rând și de coloană

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

În acest exemplu de MATCH și INDEX, facem o căutare bidirecțională. Ideea este să vedem câți bani am câștigat din articolele ecologice în luna mai. Acesta este într-adevăr similar cu exemplul de mai sus, dar o formulă de potrivire suplimentară este imbricată în INDEX.

MATCH(G1, A2:A13, 0) este primul element rezolvat în această formulă. Se caută G1 (cuvântul „mai”) în A2:A13 pentru a obține o anumită valoare. Nu îl vedem aici, dar este 5.
MATCH(G2, B1:E1, 0) este a doua formulă MATCH și este într-adevăr similară cu prima, dar caută în schimb G2 (cuvântul „Verde”) în titlurile coloanelor de la B1:E1. Acesta se rezolvă la 3.
Acum putem rescrie formula INDEX astfel pentru a vizualiza ce se întâmplă: =INDEX(B2:E13, 5, 3). Se caută în întregul tabel, B2:E13, al cincilea rând și a treia coloană, care returnează 180 USD.

Reguli de potrivire și INDEX

Există mai multe lucruri de reținut atunci când scrieți formule cu aceste funcții:

MATCH nu ține cont de majuscule, așa că literele mari și mici sunt tratate la fel atunci când se potrivesc valorile textului.
MATCH returnează N/A din mai multe motive: dacă match_type este 0 și lookup_value nu este găsită dacă potrivirea_type este -1 și lookup_array nu este în ordine descrescătoare, dacă potrivirea_type este 1 și lookup_array nu este în creștere ordine și dacă lookup_array nu este un singur rând sau coloană.
Puteți folosi un caracter wildcard în argumentul lookup_value dacă potrivirea_tip este 0 și lookup_value este un șir de text. Un semn de întrebare se potrivește cu orice caracter, iar un asterisc se potrivește cu orice succesiune de caractere (de ex.de exemplu, =MATCH(„Jo”, 1:1, 0)). Pentru a folosi MATCH pentru a găsi un semn de întrebare sau un asterisc real, tastați mai întâi ~.
INDEX returnează REF! dacă row_num și column_num nu indică o celulă din matrice.

Funcții Excel similare

Funcția MATCH este similară cu LOOKUP, dar MATCH returnează poziția articolului în locul articolului în sine.

CĂUTARE V este o altă funcție de căutare pe care o puteți utiliza în Excel, dar, spre deosebire de MATCH, care necesită INDEX pentru căutări avansate, formulele de căutare V. au nevoie doar de acea funcție.