INDEX-MATCH a devenit un instrument mai popular pentru Excel, deoarece rezolvă limitarea funcției VLOOKUP și este mai ușor de utilizat. Funcția INDEX-MATCH din Excel are o serie de avantaje față de funcția VLOOKUP:

1. INDEX și MATCH sunt mai flexibile și mai rapide decât Vlookup
2. Este posibil să se execute căutare orizontală, căutare verticală, căutare bidirecțională, căutare la stânga, căutare sensibilă la majuscule și chiar căutări bazate pe mai multe criterii.
3. În datele sortate, INDEX-MATCH este cu 30% mai rapid decât VLOOKUP. Aceasta înseamnă că într-un set de date mai mare cu 30% mai rapid are mai mult sens.

Să începem cu conceptele detaliate ale fiecărui INDEX și MATCH.

Funcția INDEX

Funcția INDEX din Excel este foarte puternică, în același timp, un instrument flexibil care preia valoarea într-o anumită locație dintr-un interval. Cu un alt cuvânt, returnează conținutul unei celule, specificat prin offset de rând și coloană.

Sintaxă:

=INDEX(reference, [row], [column])>

Parametri:

referință: matricea de celule în care trebuie compensată. Poate fi un singur interval sau un întreg set de date într-un tabel de date. row [opțional]: numărul de rânduri compensate. Înseamnă că dacă alegem un interval de referință al tabelului ca A1:A5, atunci celula/conținutul pe care dorim să-l extragem este la cât de mult distanță verticală. Aici, pentru rândul A1 va fi 1, pentru rândul A2 = 2 și așa mai departe. Dacă dăm rând = 4, atunci se va extrage A4. Deoarece rândul este opțional, dacă nu specificăm niciun număr de rând, atunci extrage rânduri întregi din intervalul de referință. Adică de la A1 la A5 în acest caz. coloană [opțional]: numărul de coloane compensate. Înseamnă că dacă alegem un interval de referință al tabelului ca A1:B5, atunci celula/conținutul pe care dorim să o extragem este la cât de mult distanță orizontală. Aici, pentru A1 rândul va fi 1 și coloana va fi 1, pentru B1 rândul va fi 1, dar coloana va fi 2 în mod similar pentru A2 rând = 2 coloana = 1, pentru B2 rând = 2 coloana = 2 și așa mai departe. Dacă dăm rândul = 5 și coloana 2, atunci se va extrage B5. Deoarece coloana este opțională, dacă nu specificăm niciun rând nr. apoi va extrage întreaga coloană din intervalul de referință. De exemplu, dacă dăm rândul = 2 și coloana ca fiind goale, atunci se va extrage (A2:B2). Dacă nu specificăm Rând și coloană ambele, atunci va extrage întregul tabel de referință care este (A1:B5).

Tabel de referință: Următorul tabel va fi folosit ca tabel de referință pentru toate exemplele funcției INDEX. Prima celulă este la B3 (FOOD), iar Ultima celulă diagonală este la F10 (180).

Exemple: Mai jos sunt câteva exemple de funcții Index.

Cazul 1: Nu sunt menționate rânduri și coloane.

Comanda de intrare: =INDEX(B3:C10)

Cazul 2: Sunt menționate doar rândurile.

Comanda de intrare: =INDEX(B3:C10,2)

Cazul 3: Sunt menționate atât rândurile, cât și coloanele.

Comanda de intrare: =INDEX(B3:D10,4,2)

Cazul 4: Sunt menționate doar Coloanele.

Comanda de intrare: =INDEX(B3 : D10 , , 2)

Problemă cu funcția INDEX: Problema cu funcția INDEX este că este nevoie de a specifica rânduri și coloane pentru datele pe care le căutăm. Să presupunem că avem de-a face cu un set de date de învățare automată de 10000 de rânduri și coloane, atunci va fi foarte dificil să căutăm și să extragem datele pe care le căutăm. Aici apare conceptul de Funcție de potrivire, care va identifica rândurile și coloanele în funcție de anumite condiții.

Funcția MATCH

Acesta preia poziția unui element/valoare într-un interval. Este o versiune mai puțin rafinată a unui VLOOKUP sau HLOOKUP care returnează doar informațiile despre locație și nu datele reale. MATCH nu face distincție între majuscule și minuscule și nu îi pasă dacă intervalul este orizontal sau vertical.

Sintaxă:

=MATCH(search_key, range, [search_type])>

Parametri:

search_key: valoarea de căutat. De exemplu, 42, Pisici sau I24. interval: matricea unidimensională care trebuie căutată. Poate fi fie un singur rând, fie o singură coloană. ex->A1:A10 , A2:D2 etc. search_type [opțional]: metoda de căutare. = 1 (implicit) găsește cea mai mare valoare mai mică sau egală cu search_key atunci când intervalul este sortat în ordine crescătoare.
• = 0 găsește valoarea exactă când intervalul este nesortat.
• = -1 găsește cea mai mică valoare mai mare sau egală cu search_key atunci când intervalul este sortat în ordine descrescătoare.

Numărul rândului sau numărul coloanei pot fi găsite utilizând funcția de potrivire și îl pot folosi în interiorul funcției de index, astfel încât dacă există vreun detaliu despre un articol, atunci toate informațiile pot fi extrase despre articol găsind rândul/coloana articolului folosind potrivire apoi cuibărându-l în funcția index.

Tabel de referință: Următorul tabel va fi folosit ca tabel de referință pentru toate exemplele funcției MATCH. Prima celulă este la B3 (FOOD), iar ultima celulă diagonală este la F10 (180)

Exemple: Mai jos sunt câteva exemple ale funcției MATCH-

Cazul 1: Tip de căutare 0, înseamnă potrivire exactă.

Comanda de intrare: =POTIT (South Indian, C3:C10,0)

Cazul 2: Tipul de căutare 1 (implicit).

Comanda de intrare: =POCI (South Indian, C3:C10)

program python pentru căutare binară

Cazul 3: Tip de căutare -1.

Comanda de intrare: =POTIT (South Indian, C3:C10,-1)

INDEX-MATCH Împreună

În exemplele anterioare, valorile statice ale rândurilor și coloanelor au fost furnizate în funcția INDEX Să presupunem că nu există cunoștințe anterioare despre rândurile și poziția coloanei, atunci poziția rândurilor și coloanelor pot fi furnizate folosind funcția MATCH. Acesta este un mod dinamic de a căuta și de a extrage valoare.

Sintaxă:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Tabel de referință: Se va folosi următorul tabel de referință. Prima celulă este la B3 (FOOD), iar ultima celulă diagonală este la F10 (180)

Exemplu: Să presupunem că sarcina este de a găsi costul Masala Dosa. Se știe că coloana 3 reprezintă costul articolelor, dar poziția rândului Masala Dosa nu este cunoscută. Problema poate fi împărțită în doi pași -

Pasul 1: Găsiți poziția lui Masala Dosa folosind formula:

 =MATCH('Masala Dosa',B3:B10,0)>

Aici B3:B10 reprezintă coloana Alimente și 0 înseamnă Potrivire exactă. Va returna numărul de rând al lui Masala Dosa.

Pasul 2: Găsiți costul Masala Dosa. Utilizați funcția INDEX pentru a găsi costul Masala Dosa. Prin înlocuirea interogării funcției MATCH de mai sus în cadrul funcției INDEX în locul în care este necesară poziția exactă a lui Masala Dosa, iar numărul coloanei de cost este 3, care este deja cunoscut.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Căutare în două moduri cu INDEX-MATCH împreună

În exemplul anterior, poziția coloanei a atributului Cost a fost codificată. Deci, nu a fost complet dinamic.

Cazul 1: Să presupunem că nu există cunoștințe despre numărul coloanei Cost, apoi poate fi obținut folosind formula:

 =MATCH('Cost',B3:F3,0)>

Aici B3:F3 reprezintă coloana antet.

Cazul 2: Când rândul, precum și valoarea coloanei, sunt furnizate prin intermediul funcției MATCH (fără a furniza valoare statică), atunci se numește Căutare în două direcții. Se poate realiza folosind formula:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Căutare din stânga

Unul dintre avantajele cheie ale INDEX și MATCH față de funcția VLOOKUP este capacitatea de a efectua o căutare la stânga. Înseamnă că este posibil să extrageți poziția de rând a unui element folosind orice atribut din dreapta și valoarea unui alt atribut din stânga poate fi extrasă.

De exemplu, să presupunem că cumpărați alimente al căror cost ar trebui să fie de 140 Rs. În mod indirect, spunem cumpără Biryani. În acest exemplu, se cunoaște costul de 140 Rs/-, este nevoie de extragerea Hranei. Deoarece coloana Cost este plasată în dreapta coloanei Alimente. Dacă se aplică CĂUTARE V, acesta nu va putea căuta în partea stângă a coloanei Cost. De aceea, folosind VLOOKUP nu este posibil să obțineți Numele Alimentelor.

Pentru a depăși acest dezavantaj, se poate folosi funcția INDEX-MATCH Căutare din stânga.
Pasul 1: Extrageți primul rând poziția Cost 140 Rs folosind formula:

 =MATCH(140, D3:D10,0)>

Aici D3: D10 reprezintă coloana Cost în care se efectuează căutarea numărului rândului Cost 140 Rs.

Pasul 2: După obținerea numărului de rând, următorul pas este să utilizați funcția INDEX pentru a extrage Numele Alimentelor folosind formula:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Aici B3:B10 reprezintă coloana de alimente și 140 este costul produsului alimentar.

Căutare sensibilă la majuscule și minuscule

În sine, funcția MATCH nu face distincție între majuscule și minuscule. Aceasta înseamnă că dacă există un Nume de Aliment DHOKLA și funcția MATCH este utilizată cu următorul cuvânt de căutare:

1. Dhokla
2. dhokla
3. DhOkLA

Toate vor returna poziția de rând a lui DHOKLA. Cu toate acestea, funcția EXACT poate fi utilizată cu INDEX și MATCH pentru a efectua o căutare care respectă literele mari și mici.

Funcția exactă: Funcția Excel EXACT compară două șiruri de text, luând în considerare caracterele majuscule și minuscule și returnează TRUE dacă sunt aceleași și FALSE dacă nu. EXACT este sensibil la majuscule.

Exemple:

EXACT(DHOKLA,DHOKLA): Acesta va returna True. EXACT(DHOKLA,Dhokla): Aceasta va returna False. EXACT(DHOKLA,dhokla): Aceasta va returna False. EXACT(DHOKLA,DhOkLA): Aceasta va returna False.

Exemplu: Să presupunem că sarcina este să căutați tipul de mâncare Dhokla, dar în mod sensibil la majuscule și minuscule. Acest lucru se poate face folosind formula-

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Aici funcția EXACT va returna True dacă valoarea din coloana B3:B10 se potrivește cu Dhokla cu aceeași literă, altfel va returna False. Acum funcția MATCH se va aplica în coloana B3:B10 și va căuta un rând cu valoarea exactă TRUE. După aceea, funcția INDEX va prelua valoarea coloanei C3:C10 (Coloana tipului de alimente) la rândul returnat de funcția MATCH.

Căutare pe mai multe criterii

Una dintre cele mai dificile probleme din Excel este o căutare bazată pe mai multe criterii. Cu alte cuvinte, o căutare care se potrivește pe mai multe coloane în același timp. În exemplul de mai jos, funcția INDEX și MATCH și logica booleană sunt folosite pentru a se potrivi pe 3 coloane-

1. Alimente.
2. Cost.
3. Cantitate.

Pentru a extrage costul total.

Exemplu: Să presupunem că sarcina este de a calcula costul total al Pastelor unde

Mancare: Paste. Cost: 60. Cantitate: 1.

Deci, în acest exemplu, există trei criterii pentru a efectua o potrivire. Mai jos sunt pașii pentru căutarea bazată pe mai multe criterii-

Pasul 1: Potriviți pentru prima dată coloana de alimente (B3:B10) cu paste folosind formula:

 'PASTA' = B3:B10>

Aceasta va converti valorile B3:B10 (Coloana Alimentelor) în Boolean. Este adevărat acolo unde Mâncarea este Paste altfel Fals.

Pasul 2: După aceea, potriviți criteriile de cost în felul următor:

 60 = D3:D10>

Aceasta va înlocui valorile D3:D10 (Coloană Cost) ca boolean. Este adevărat unde Cost=60 altfel Fals.

Pasul 3: Următorul pas este să potriviți al treilea criteriu, care sunt Cantitate = 1, în felul următor:

 1 = E3:E10>

Aceasta va înlocui Coloana E3:E10 (Coloana Cantității) ca Adevărat, unde Cantitate = 1, altfel va fi Fals.

Pasul 4: Înmulțiți rezultatul primului, al doilea și al treilea criteriu. Aceasta va fi intersecția tuturor condițiilor și va converti Boolean True / False ca 1/0.

Pasul 5: Acum rezultatul va fi o coloană cu 0 și 1. Aici folosiți funcția MATCH pentru a găsi numărul de rânduri de coloane care conțin 1. Pentru că dacă o coloană are valoarea 1, înseamnă că îndeplinește toate cele trei criterii.

Pasul 6: După obținerea numărului rândului, utilizați funcția INDEX pentru a obține costul total al rândului respectiv.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Aici F3:F10 reprezintă coloana Cost total.