Acest tutorial vă va învăța cum să utilizați combinația INDEX & MATCH pentru a efectua căutări în Excel și Foi de calcul Google.
INDEX & MATCH, perechea perfectă
Să aruncăm o privire mai atentă asupra câtorva moduri în care puteți combina funcțiile INDEX și MATCH. Funcția MATCH este concepută pentru a returna poziția relativă a unui element într-un tablou, în timp ce funcția INDEX poate prelua un articol dintr-un tablou având o poziție specifică. Această sinergie dintre cele două le permite să efectueze aproape orice tip de căutare de care ați putea avea nevoie.
Combinația INDEX / MATCH a fost folosită în mod istoric ca înlocuitor al funcției VLOOKUP. Unul dintre principalele motive este abilitatea de a efectua o căutare privitoare la stânga (vezi secțiunea următoare).
Notă: noua funcție XLOOKUP poate efectua acum căutări în stânga.
Căutați în stânga
Să folosim acest tabel cu statistici de baschet:
Vrem să găsim jucătorul lui Bob. Deoarece numărul de jucător se află în stânga coloanei cu numele, nu putem folosi o VLOOKUP.
În schimb, am putea face o cerere de bază MATCH pentru a calcula rândul lui Bob
= MATCH (H2, B2: B5, 0)
Acest lucru va căuta o potrivire exactă a cuvântului „Bob”, astfel încât funcția noastră ar returna numărul 2, deoarece „Bob” este în 2nd poziţie.
Apoi putem folosi funcția INDEX pentru a returna playerul #, corespunzător unui rând. Deocamdată, să introducem manual „2” în funcție:
= INDEX (A2: A5, 2)
Aici, INDEX va face referire la A3, deoarece acesta este 2nd celulă din intervalul A2: A5 și returnăm rezultatul de 42. Pentru obiectivul nostru general, putem combina apoi aceste două în:
= INDEX (A2: A5, MATCH (H2, B2: B5, 0))
Avantajul aici este că am reușit să returnăm un rezultat dintr-o coloană din stânga locului în care căutam.
Căutare în două dimensiuni
Să ne uităm la masa noastră dinainte:
Cu toate acestea, de data aceasta, vrem să aducem o statistică specifică. Am strigat că dorim să căutăm Rebounds în celula H1. În loc să trebuiască să scrieți mai multe instrucțiuni IF pentru a determina din ce coloană obțineți rezultatul, puteți utiliza din nou o funcție MATCH. Funcția INDEX vă permite să specificați valoarea rândului și valoarea coloanei. Vom adăuga aici o altă funcție MATCH pentru a determina ce coloană dorim. Asta va arăta așa
= MATCH (H1, A1: E1, 0)
Celula noastră din H1 este o listă derulantă care să ne permită să alegem ce categorie dorim să căutăm, iar apoi MATCH-ul nostru determină coloana din tabelul căruia îi aparține. Să conectăm acest nou bit la formula noastră anterioară. Rețineți că trebuie să modificăm primul argument pentru a fi două dimensiuni, deoarece nu mai dorim doar un rezultat din coloana A.
= INDEX (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
În exemplul nostru, vrem să găsim Rebounds pentru Charlie. Formula noastră va evalua acest lucru astfel:
= INDEX (A2: E5, MATCH („Charlie”, B2: B5, 0), MATCH („Rebounds”, A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Am creat acum o configurare flexibilă care permite utilizatorului să obțină orice valoare dorită din tabelul nostru fără a fi nevoie să scrie mai multe formule sau instrucțiuni IF de ramificare.
Mai multe secțiuni
Nu este adesea folosit, dar INDEX are un al cincilea argument care poate fi dat pentru a determina care zonă în cadrul argumentului de utilizat. Aceasta înseamnă că avem nevoie de o modalitate de a transfera mai multe zone în primul argument. Puteți face acest lucru folosind un set suplimentar de paranteze. Acest exemplu va ilustra modul în care ați putea obține rezultate din diferite tabele pe o foaie de lucru folosind INDEX.
Iată aspectul pe care îl vom folosi. Avem statistici pentru trei sferturi diferite de joc.
În celulele H1: H3, am creat liste derulante Validarea datelor pentru diferitele noastre opțiuni. Meniul derulant pentru trimestru vine din J2: J4. Vom folosi acest lucru pentru o altă declarație MATCH, pentru a determina ce zonă să utilizăm. Formula noastră din H4 va arăta astfel:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Am discutat deja cum funcționează cele două funcții MATCH interioare, așa că să ne concentrăm pe primul și ultimul argument:
= INDEX ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Am dat funcției INDEX mai multe matrice în primul argument, încadrându-le pe toate între paranteze. Celălalt mod în care ați putea face acest lucru este folosind Formule - Define Name. Puteți defini un nume numit „MyTables” cu o definiție a
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Să revenim la întreaga declarație. Diferitele noastre funcții MATCH vor spune funcției INDEX exact unde să caute. Mai întâi, vom stabili că „Charlie” este al 3-leard rând. Apoi, vrem „Rebounds”, care este 4a coloană. În cele din urmă, am stabilit că dorim rezultatul de la 2nd masa. Formula va evalua astfel:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEX ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEX (A10: E13, 3, 4) = D13 = 14
Așa cum am menționat la începutul acestui exemplu, vă limitați doar ca tabelele să fie pe aceeași foaie de lucru. Dacă puteți scrie modalități corecte de a spune INDEX-ului dvs. din care rând, coloană și / sau zonă doriți să preluați date, INDEX vă va servi foarte bine.
Foi de calcul Google -INDICE & MATCH
Toate exemplele de mai sus funcționează exact la fel în Foi de calcul Google ca în Excel.
