See õpetus õpetab teile, kuidas kasutada kombinatsiooni INDEX & MATCH Exceli ja Google'i arvutustabelite otsimiseks.
INDEX & MATCH, täiuslik paar
Vaatame lähemalt mõningaid viise, kuidas saate funktsioone INDEX ja MATCH kombineerida. Funktsioon MATCH on loodud tagastama massiivis oleva elemendi suhtelise asukoha, samas kui funktsioon INDEX võib tuua objekti massiivist, millel on konkreetne asukoht. See sünergia nende kahe vahel võimaldab neil teha peaaegu igat tüüpi otsinguid, mida vajate.
INDEX / MATCH kombinatsiooni on ajalooliselt kasutatud funktsiooni VLOOKUP asendajana. Üks peamisi põhjusi on võimalus teha vasakpoolse välimusega otsing (vt järgmist jaotist).
Märkus: uus funktsioon XLOOKUP saab nüüd teha vasakpoolse välimusega otsinguid.
Otsige vasakule
Kasutame seda korvpallistatistika tabelit:
Soovime leida Bobi mängija #. Kuna mängija # on nimeveerust vasakul, ei saa me VLOOKUP -i kasutada.
Selle asemel võiksime Bobi rea arvutamiseks teha põhilise MATCH -päringu
= MATCH (H2, B2: B5, 0)
See otsib sõna "Bob" täpse vaste ja seega tagastab meie funktsioon numbri 2, kuna "Bob" on 2 -snd positsiooni.
Järgmisena saame funktsiooni INDEX abil reale vastava mängija #tagastada. Praegu sisestame funktsiooni lihtsalt käsitsi “2”:
= INDEKS (A2: A5, 2)
Siin viitab INDEX A3 -le, kuna see on 2nd lahtrisse A2: A5 ja tagastame tulemuse 42. Üldeesmärgi saavutamiseks saame need kaks ühendada järgmiselt:
= INDEKS (A2: A5, MATCH (H2, B2: B5, 0))
Kasu on see, et suutsime tagastada tulemuse otsitud veerust vasakule.
Kahemõõtmeline otsing
Vaatame meie tabelit varasemalt:
Seekord tahame aga tuua konkreetse statistika. Oleme välja kutsunud, et tahame lahtrist H1 otsida tagasilööke. Selle asemel, et kirjutada mitu IF -lauset, et määrata, millisest veerust tulemus saada, saate uuesti kasutada funktsiooni MATCH. Funktsioon INDEX võimaldab määrata rea väärtuse ja veeru väärtus. Lisame siia veel ühe MATCH -funktsiooni, et teha kindlaks, millist veergu me soovime. See näeb välja selline
= MATCH (H1, A1: E1, 0)
Meie lahtris H1 on rippmenüü, mis võimaldab meil valida, millist kategooriat otsida soovitakse, ja seejärel määrab meie MATCH, millisesse veergu tabelisse kuulub. Ühendame selle uue bitti oma eelmise valemiga. Pange tähele, et peame esimese argumendi muutma kahemõõtmeliseks, kuna me ei taha enam ainult veeru A tulemust.
= INDEKS (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
Meie näites tahame leida tagasilööke Charlie jaoks. Meie valem hindab seda järgmiselt:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("tagasilöögid", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Oleme nüüd loonud paindliku seadistuse, mis võimaldab kasutajal tabelist soovitud väärtuse tuua, ilma et peaksite kirjutama mitu valemit või hargnema IF -lauseid.
Mitu sektsiooni
Seda ei kasutata sageli, kuid INDEXil on viies argument, mille abil saab määrata, millise piirkonnas ühe argumendi piires kasutada. See tähendab, et me vajame viisi, kuidas esimesse argumenti mitmed valdkonnad lisada. Seda saate teha, kasutades täiendavat sulgude komplekti. See näide illustreerib, kuidas saaksite INDEXi abil töölehe eri tabelitest tulemusi tuua.
Siin on paigutus, mida me kasutame. Meil on statistika kolme erineva mänguveerandi kohta.
Lahtrites H1: H3 oleme erinevate valikute jaoks loonud andmete valideerimise ripploendid. Kvartali rippmenüü pärineb versioonist J2: J4. Kasutame seda teise MATCH -lause jaoks, et määrata, millist piirkonda kasutada. Meie valem H4 -s näeb välja selline:
= INDEKS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Oleme juba arutanud kahe sisemise MATCH -funktsiooni toimimist, nii et keskendume esimesele ja viimasele argumendile:
= INDEKS ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Oleme esimesele argumendile andnud funktsiooni INDEX mitu massiivi, lisades need kõik sulgudesse. Teine viis, kuidas seda teha, on valemite kasutamine - nime määramine. Saate määratleda nime nimega „MyTables” koos määratlusega
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Tuleme tagasi kogu avalduse juurde. Meie erinevad MATCH -funktsioonid ütlevad funktsioonile INDEX täpselt, kust otsida. Esiteks teeme kindlaks, et "Charlie" on 3rd rida. Järgmisena tahame tagasilööke, mis on 4th veerg. Lõpuks otsustasime, et tahame tulemust 2nd tabel. Valemit hinnatakse selle kaudu järgmiselt:
= 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
Nagu me selle näite alguses mainisime, piirdute ainult sellega, et tabelid oleksid samal töölehel. Kui saate kirjutada õigeid viise, kuidas öelda oma INDEXile, millisest reast, veerust ja/või piirkonnast soovite andmeid alla laadida, teenindab INDEX teid väga hästi.
Google'i arvutustabelid -INDEX & MATCH
Kõik ülaltoodud näited töötavad Google'i arvutustabelites täpselt samamoodi nagu Excelis.
