Ši pamoka išmokys jus, kaip naudoti „INDEX & MATCH“ derinį, kad būtų galima ieškoti „Excel“ ir „Google“ skaičiuoklėse.
INDEX & MATCH, Tobula pora
Pažvelkime atidžiau į kai kuriuos INDEX ir MATCH funkcijų derinimo būdus. Funkcija MATCH skirta grąžinti santykinę elemento vietą masyve, o INDEX funkcija gali nuskaityti elementą iš masyvo, kuriam suteikta tam tikra padėtis. Ši abiejų sąveika leidžia jiems atlikti beveik bet kokio tipo paiešką, kurios jums gali prireikti.
INDEX / MATCH derinys istoriškai buvo naudojamas kaip VLOOKUP funkcijos pakaitalas. Viena iš pagrindinių priežasčių yra galimybė ieškoti kairėn (žr. Kitą skyrių).
Pastaba: naujoji XLOOKUP funkcija dabar gali atlikti kairės išvaizdos paieškas.
Žvilgsnis į kairę
Panaudokime šią krepšinio statistikos lentelę:
Mes norime rasti Bobo žaidėją #. Kadangi grotuvas # yra pavadinimo stulpelio kairėje, negalime naudoti VLOOKUP.
Vietoj to, mes galime atlikti pagrindinę MATCH užklausą, kad apskaičiuotume Bobo eilutę
= ATITIKTI (H2, B2: B5, 0)
Taip bus ieškoma tiksli žodžio „Bobas“ atitiktis, todėl mūsų funkcija grąžins skaičių 2, nes „Bobas“ yra 2antra poziciją.
Toliau galime naudoti funkciją INDEX, kad grąžintume grotuvo #, atitinkantį eilutę. Kol kas tiesiog rankiniu būdu įveskite „2“ į funkciją:
= INDEKSAS (A2: A5, 2)
Čia INDEX nurodys A3, nes tai yra 2antra langelį A2: A5 diapazone ir grąžinkite rezultatą 42. Bendram tikslui pasiekti šiuos du dalykus galime sujungti į:
= INDEKSAS (A2: A5, MATCH (H2, B2: B5, 0))
Nauda čia ta, kad galėjome grąžinti rezultatą iš stulpelio, esančio kairėje nuo to, kur ieškojome.
Dviejų matmenų paieška
Pažvelkime į mūsų lentelę iš anksčiau:
Tačiau šį kartą norime pateikti konkrečią statistiką. Paskambinome, kad norime ieškoti atšokimų H1 langelyje. Užuot turėję parašyti kelis IF teiginius, kad nustatytumėte, iš kurio stulpelio gauti rezultatą, galite vėl naudoti funkciją MATCH. Funkcija INDEX leidžia nurodyti eilutės vertę ir stulpelio vertę. Čia pridėsime kitą MATCH funkciją, kad nustatytume, kokio stulpelio norime. Tai atrodys taip
= ATITIKTI (H1, A1: E1, 0)
Mūsų langelis H1 yra išskleidžiamasis meniu, leidžiantis pasirinkti, kokios kategorijos norime ieškoti, o tada mūsų MATCH nustato, kuriam lentelės stulpeliui priklauso. Prijunkime šią naują dalį prie ankstesnės formulės. Atminkite, kad pirmąjį argumentą turime pataisyti į du aspektus, nes nebenorime tik rezultato iš A stulpelio.
= INDEKSAS (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
Mūsų pavyzdyje norime rasti Čarlio atšokimus. Mūsų formulė tai įvertins taip:
= INDEKSAS (A2: E5, MATCH („Charlie“, B2: B5, 0), MATCH („Rebounds“, A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Dabar sukūrėme lanksčią sąranką, leidžiančią vartotojui iš lentelės gauti bet kokią norimą vertę, nerašant kelių formulių ar išsišakojančių IF teiginių.
Keli skyriai
Jis nėra dažnai naudojamas, tačiau INDEX turi penktąjį argumentą, kurį galima pateikti norint nustatyti, kurį srityje argumento vienas naudoti. Tai reiškia, kad mums reikia būdų, kaip į pirmąjį argumentą įtraukti kelias sritis. Tai galite padaryti naudodami papildomą skliaustų rinkinį. Šis pavyzdys parodys, kaip galite gauti rezultatus iš skirtingų lentelių darbalapyje naudodami INDEX.
Štai išdėstymas, kurį naudosime. Turime trijų skirtingų žaidimo kėlinių statistiką.
Ląstelėse H1: H3 mes sukūrėme išskleidžiamuosius duomenų patvirtinimo sąrašus, kad galėtume pasirinkti. Ketvirčio išskleidžiamasis meniu pateikiamas iš J2: J4. Mes naudosime tai kitam MATCH teiginiui, kad nustatytume, kurią sritį naudoti. Mūsų formulė H4 atrodys taip:
= INDEKSAS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Mes jau aptarėme, kaip veikia dvi vidinės MATCH funkcijos, todėl sutelkime dėmesį į pirmąjį ir paskutinį argumentus:
= INDEKSAS ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Funkcijai INDEX pirmame argumente pateikėme kelis masyvus, juos visus įtraukdami į skliaustus. Kitas būdas tai padaryti yra naudojant formules - apibrėžti vardą. Galite apibrėžti pavadinimą, pavadintą „MyTables“, su apibrėžimu
= INDEKSAS („MyTable“, MATCH (H2, 1347 lentelė [pavadinimas], 0), MATCH (H1, 1347 lentelė [#vadovai], 0), MATCH (H3, J2: J4,0))
Grįžkime prie viso teiginio. Mūsų įvairios MATCH funkcijos tiksliai nurodys INDEX funkciją, kur ieškoti. Pirma, mes nustatysime, kad „Čarlis“ yra 3rd eilė. Toliau norime „Atšokimų“, tai yra 4tūkst stulpelis. Galiausiai nusprendėme, kad norime rezultato iš 2antra lentelę. Formulė bus įvertinta taip:
= INDEKSAS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEKSAS ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEKSAS (A10: E13, 3, 4) = D13 = 14
Kaip minėjome šio pavyzdžio pradžioje, jūs apsiribojate tuo, kad lentelės būtų tame pačiame darbalapyje. Jei galite parašyti teisingus būdus, kaip pasakyti INDEX, iš kurios eilutės, stulpelio ir (arba) srities norite gauti duomenis, INDEX jums labai pasitarnaus.
„Google“ skaičiuoklės -RODIKLIS IR RINKINYS
Visi aukščiau pateikti pavyzdžiai „Google“ skaičiuoklėse veikia lygiai taip pat, kaip „Excel“.
