Ta vadnica vas bo naučila, kako uporabiti kombinacijo INDEX & MATCH za iskanje v Excelu in Google Preglednicah.
INDEX & MATCH, Popoln par
Oglejmo si podrobneje nekatere načine kombiniranja funkcij INDEX in MATCH. Funkcija MATCH je zasnovana tako, da vrne relativni položaj elementa v matriki, medtem ko lahko funkcija INDEX prinese element iz matrike z določenim položajem. Ta sinergija med njima omogoča izvajanje skoraj vseh vrst iskanj, ki jih morda potrebujete.
Kombinacija INDEX / MATCH se je v preteklosti uporabljala kot nadomestek funkcije VLOOKUP. Eden od glavnih razlogov je možnost iskanja v levo (glej naslednji razdelek).
Opomba: nova funkcija XLOOKUP lahko zdaj izvaja iskanje po levi strani.
Iskanje v levo
Uporabimo to tabelo košarkarskih statistik:
Želimo poiskati Bobovega igralca #. Ker je Player # levo od stolpca z imenom, ne moremo uporabiti VLOOKUP -a.
Namesto tega bi lahko naredili osnovno zahtevo MATCH za izračun Bobove vrstice
= UJEMA (H2, B2: B5, 0)
To bo iskalo natančno ujemanje besede "Bob", zato bi naša funkcija vrnila številko 2, saj je "Bob" v 2nd položaj.
Nato lahko s funkcijo INDEX vrnemo predvajalnik #, ki ustreza vrstici. Zaenkrat v funkcijo vnesite "2":
= INDEKS (A2: A5, 2)
Tukaj se bo INDEX skliceval na A3, saj je to 2nd celico v območju A2: A5 in vrne rezultat 42. Za naš splošni cilj lahko to dvoje združimo v:
= INDEX (A2: A5, MATCH (H2, B2: B5, 0))
Prednost tukaj je, da smo lahko vrnili rezultat iz stolpca levo od mesta, kjer smo iskali.
Dvodimenzionalno iskanje
Poglejmo našo mizo od prej:
Tokrat pa želimo pridobiti posebno statistiko. Vpoklicali smo, da želimo poiskati skoke v celici H1. Namesto da bi morali zapisati več stavkov IF, da ugotovite, iz katerega stolpca želite rezultat, lahko znova uporabite funkcijo MATCH. S funkcijo INDEX lahko določite vrednost vrstice in vrednost stolpca. Tukaj bomo dodali še eno funkcijo MATCH, da ugotovimo, kateri stolpec želimo. Tako bo videti
= UJEMA (H1, A1: E1, 0)
Naša celica v H1 je spustni meni, ki nam omogoča, da izberemo kategorijo, ki jo želimo iskati, nato pa naš MATCH določi, kateremu stolpcu v tabeli pripada. Priključimo ta novi del v našo prejšnjo formulo. Upoštevajte, da moramo prvi argument nastaviti na dve dimenziji, saj ne želimo več samo rezultata iz stolpca A.
= INDEKS (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
V našem primeru želimo poiskati skoke za Charlieja. Naša formula bo to ocenila tako:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Odskoki", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Ustvarili smo prilagodljivo nastavitev, ki uporabniku omogoča, da iz naše tabele pridobi poljubno vrednost, ne da bi mu bilo treba pisati več formul ali razvejati stavke IF.
Več odsekov
Ne uporablja se pogosto, vendar ima INDEX peti argument, ki ga lahko navedemo, da ugotovimo, kateri območje znotraj prvega argumenta za uporabo. To pomeni, da potrebujemo način, da v prvi argument prenesemo več področij. To lahko storite z dodatnim naborom oklepajev. Ta primer ponazarja, kako lahko z INDEX -om pridobite rezultate iz različnih tabel na delovnem listu.
Tu je postavitev, ki jo bomo uporabljali. Imamo statistiko za tri različne četrtine igre.
V celicah H1: H3 smo za različne izbire ustvarili spustne sezname Potrjevanje podatkov. Spustni meni za četrtletje prihaja iz J2: J4. To bomo uporabili za drugo izjavo MATCH, da ugotovimo, katero področje uporabiti. Naša formula v H4 bo videti tako:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
O tem, kako delujeta notranji funkciji MATCH, smo že razpravljali, zato se osredotočimo na prvi in zadnji argument:
= INDEX ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
V prvem argumentu smo funkciji INDEX dali več nizov, tako da smo jih vse zaprli v oklepaje. Drug način, da to storite, je uporaba formul - Določi ime. Lahko bi opredelili ime z imenom "MyTables" z definicijo
= INDEX (MyTable, MATCH (H2, Tabela1347 [Ime], 0), MATCH (H1, Tabela1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Vrnimo se k celotni izjavi. Naše različne funkcije MATCH bodo funkciji INDEX povedale, kje naj iščejo. Najprej bomo ugotovili, da je "Charlie" 3rd vrstici. Nato želimo »Odskoke«, to je 4th stolpec. Nazadnje smo ugotovili, da želimo rezultat 2nd miza. Formula bo tako ocenila:
= 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
Kot smo omenili na začetku tega primera, ste omejeni na to, da so tabele na istem delovnem listu. Če lahko napišete pravilne načine, kako svojemu indeksu povedati, iz katere vrstice, stolpca in/ali območja želite pridobiti podatke, vam bo INDEX zelo dobro služil.
Google Preglednice -INDEX & MATCH
Vsi zgornji primeri delujejo popolnoma enako v Google Preglednicah kot v Excelu.
