Ovaj vodič će vas naučiti kako koristiti kombinaciju INDEKS & MATCH za izvođenje pretraživanja u Excelu i Google tablicama.
INDEX & MATCH, Savršen par
Pogledajmo pobliže neke od načina na koje možete kombinirati funkcije INDEKS i MATCH. Funkcija MATCH osmišljena je tako da vrati relativni položaj stavke unutar niza, dok funkcija INDEX može dohvatiti stavku iz niza s određenom pozicijom. Ova sinergija između njih omogućuje im obavljanje gotovo svih vrsta pretraživanja koje vam mogu zatrebati.
Kombinacija INDEX / MATCH povijesno se koristila kao zamjena za funkciju VLOOKUP. Jedan od primarnih razloga je mogućnost pretraživanja s lijeve strane (vidi sljedeći odjeljak).
Napomena: nova funkcija XLOOKUP sada može izvesti pretraživanja lijevog izgleda.
Potraga ulijevo
Upotrijebimo ovu tablicu košarkaških statistika:
Želimo pronaći Bobovog igrača #. Budući da je Player # lijevo od stupca s imenom, ne možemo koristiti VLOOKUP.
Umjesto toga, mogli bismo napraviti osnovni MATCH zahtjev za izračunavanje Bobovog reda
= MATCH (H2, B2: B5, 0)
Ovo će tražiti točno podudaranje riječi "Bob", pa bi naša funkcija vratila broj 2, budući da je "Bob" u 2nd položaj.
Zatim možemo upotrijebiti funkciju INDEX za vraćanje igrača #, koji odgovara retku. Za sada, samo ručno unesite "2" u funkciju:
= INDEKS (A2: A5, 2)
Ovdje će se INDEX pozivati na A3, budući da je to 2nd ćeliju unutar raspona A2: A5 i vratiti rezultat 42. Za naš opći cilj, tada možemo spojiti ovo dvoje u:
= INDEKS (A2: A5, MATCH (H2, B2: B5, 0))
Prednost je u tome što smo uspjeli vratiti rezultat iz stupca lijevo od mjesta gdje smo tražili.
Dvodimenzionalno pretraživanje
Pogledajmo našu tablicu od prije:
Ovaj put, međutim, želimo donijeti određenu statistiku. Nazvali smo da želimo potražiti skokove u ćeliji H1. Umjesto da morate pisati nekoliko IF naredbi kako biste odredili iz kojeg stupca želite dobiti rezultat, možete ponovno upotrijebiti funkciju MATCH. Funkcija INDEX omogućuje vam da navedete vrijednost retka i vrijednost stupca. Ovdje ćemo dodati još jednu funkciju MATCH kako bismo odredili koji stupac želimo. Tako će izgledati
= MATCH (H1, A1: E1, 0)
Naša ćelija u H1 je padajući izbornik koji nam omogućuje da odaberemo koju kategoriju želimo pretraživati, a zatim naš MATCH određuje kojem stupcu u tablici pripada. Uključimo ovaj novi dio u našu prethodnu formulu. Imajte na umu da prvi argument moramo podesiti tako da bude dvije dimenzije jer više ne želimo samo rezultat iz stupca A.
= INDEKS (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
U našem primjeru želimo pronaći skokove za Charlieja. Naša formula će to ocijeniti ovako:
= INDEKS (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Odskoci", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Sada smo stvorili fleksibilno postavljanje koje omogućuje korisniku da dohvati bilo koju vrijednost koju želi iz naše tablice, a da ne mora pisati više formula ili granati IF izraze.
Više odjeljaka
Ne koristi se često, ali INDEX ima peti argument koji se može navesti kako bi se utvrdilo koji području unutar prvog argumenta za korištenje. To znači da nam je potreban način za prenošenje više područja u prvi argument. To možete učiniti pomoću dodatnog skupa zagrada. Ovaj primjer ilustrirat će kako možete dohvatiti rezultate iz različitih tablica na radnom listu pomoću INDEX -a.
Evo izgleda koji ćemo koristiti. Imamo statistiku za tri različite četvrtine igre.
U ćelijama H1: H3 stvorili smo padajuće popise Validacija podataka za naše različite izbore. Padajući izbornik za tromjesečje dolazi iz J2: J4. To ćemo upotrijebiti za drugu naredbu MATCH kako bismo odredili koje područje koristiti. Naša formula u H4 će izgledati ovako:
= INDEKS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Već smo razgovarali o tome kako funkcioniraju unutarnje dvije funkcije MATCH, pa se usredotočimo na prvi i posljednji argument:
= INDEKS ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
U prvom smo argumentu dali funkciji INDEX više niza tako što smo ih sve zatvorili u zagrade. Drugi način na koji to možete učiniti je pomoću formula - Definirajte naziv. Mogli biste definirati naziv pod nazivom "Moje tablice" s definicijom
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Vratimo se cijeloj izjavi. Naše različite funkcije MATCH reći će funkciji INDEX točno gdje treba tražiti. Prvo ćemo utvrditi da je "Charlie" 3rd red. Zatim želimo "Oporavak", koji je 4th stupac. Konačno, odlučili smo da želimo rezultat od 2nd stol. Formula će se ovako ocijeniti:
= INDEKS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEKS ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEKS (A10: E13, 3, 4) = D13 = 14
Kao što smo spomenuli na početku ovog primjera, ograničeni ste na to da tablice budu na istom radnom listu. Ako možete napisati ispravne načine da svom INDEX -u kažete iz kojeg retka, stupca i/ili područja želite dohvatiti podatke, INDEX će vam poslužiti vrlo dobro.
Google tablice -INDEKS & UTAKMICA
Svi gornji primjeri rade potpuno isto u Google tablicama kao i u Excelu.
