„Excel INDEX“ funkcija (pavyzdžiai + vaizdo įrašas)
Kada naudoti „Excel INDEX“ funkciją
„Excel INDEX“ funkcija gali būti naudojama, kai norite gauti reikšmę iš lentelės duomenų ir turite duomenų taško eilutės numerį ir stulpelio numerį. Pavyzdžiui, toliau pateiktame pavyzdyje galite naudoti funkciją INDEX, kad fizikoje gautumėte „Tom“ ženklus, kai žinote duomenų rinkinio eilutės ir stulpelio numerį.
Ką grąžina
Ji grąžina nurodyto eilutės numerio ir stulpelio numerio lentelės vertę.
Sintaksė
= INDEX (masyvas, eilutės numeris, [stulpelio numeris])
= INDEX (masyvas, eilutės numeris, [stulpelio_numeris], [ploto_numeris])
Funkcija INDEX turi 2 sintaksę. Pirmasis naudojamas daugeliu atvejų, tačiau trijų krypčių paieškų atveju naudojamas antrasis (aprašytas 5 pavyzdyje).
Įvesties argumentai
- masyvas - a ląstelių diapazonas arba masyvo konstanta.
- eilutės numeris - eilutės numeris, iš kurio reikia gauti reikšmę.
- [col_num] - stulpelio numeris, iš kurio reikia gauti reikšmę. Nors tai neprivalomas argumentas, bet jei eilutės numeris nepateiktas, jį reikia pateikti.
- [area_num] - (Neprivaloma) Jei masyvo argumentą sudaro keli diapazonai, šis skaičius bus naudojamas norint pasirinkti nuorodą iš visų diapazonų.
Papildomos pastabos (nuobodūs dalykai … bet svarbu žinoti)
- Jei eilutės arba stulpelio numeris yra 0, tai grąžina atitinkamai visos eilutės ar stulpelio reikšmes.
- Jei funkcija INDEX naudojama prieš langelio nuorodą (pvz., A1 :), ji grąžina langelio nuorodą, o ne vertę (žr. Pavyzdžius žemiau).
- Plačiausiai naudojamas kartu su MATCH funkcija.
- Skirtingai nuo VLOOKUP, funkcija INDEX gali grąžinti vertę iš paieškos vertės kairės.
- Funkcija INDEX turi dvi formas - masyvo formą ir nuorodos formą
- „Masyvo forma“ - tai vieta, kurioje iš nurodytos lentelės paimate vertę pagal eilutės ir stulpelio numerį.
- „Nuorodos forma“ yra tada, kai yra kelios lentelės, o jūs naudojate argumentą area_num, kad pasirinktumėte lentelę, o tada gautumėte vertę, naudodami eilutės ir stulpelio numerį (žr. Tiesioginį pavyzdį žemiau).
„Excel INDEX“ funkcija - pavyzdžiai
Pateikiame šešis „Excel INDEX“ funkcijos naudojimo pavyzdžius.
1 pavyzdys - Tomo ženklų radimas fizikoje (dviejų krypčių paieška)
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:
Norėdami rasti Tomo fizikos ženklus, naudokite šią formulę:
= INDEKSAS ($ B $ 3: $ E $ 10,3,2)
Ši INDEX formulė nurodo masyvą kaip $ B $ 3: $ E $ 10, kuris pažymėtas visais objektais. Tada jis naudoja eilutės numerį (3) ir stulpelio numerį (2), kad gautų Tomo žymes fizikoje.
2 pavyzdys. „LOOKUP“ vertės dinamiškumo nustatymas naudojant funkciją MATCH
Ne visada įmanoma rankiniu būdu nurodyti eilutės numerį ir stulpelio numerį. Galite turėti didžiulį duomenų rinkinį arba padaryti jį dinamišką, kad jis automatiškai atpažintų ląstelėse nurodytą vardą ir (arba) temą ir pateiktų teisingą rezultatą.
Kažkas, kaip parodyta žemiau:
Tai galima padaryti naudojant INDEX ir MATCH funkcijos derinį.
Čia yra formulė, pagal kurią paieškos vertės bus dinamiškos:
= INDEKSAS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Aukščiau pateiktoje formulėje vietoj to, kad būtų koduojamas eilutės numeris ir stulpelio numeris, naudojama funkcija MATCH, kad ji taptų dinamiška.
- Dinaminis eilučių skaičius pateikiamas pagal šią formulės dalį - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Jis nuskaito studentų vardus ir nustato paieškos vertę (šiuo atveju $ 5 USD). Tada jis grąžina duomenų rinkinio peržiūros vertės eilutės numerį. Pvz., Jei paieškos vertė yra „Matt“, ji grąžins 1, jei Bobas - 2 ir pan.
- Dinaminis stulpelio numeris pateikiamas pagal šią formulės dalį - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Jis nuskaito temų pavadinimus ir nustato paieškos vertę (šiuo atveju $ 4 USD). Tada jis grąžina duomenų rinkinio paieškos vertės stulpelio numerį. Pavyzdžiui, jei paieškos vertė yra matematika, ji grąžins 1, jei fizika - 2 ir pan.
3 pavyzdys. Išskleidžiamųjų sąrašų naudojimas kaip paieškos vertės
Pirmiau pateiktame pavyzdyje turime rankiniu būdu įvesti duomenis. Tai gali užtrukti daug laiko ir daug klaidų, ypač jei turite didžiulį paieškos verčių sąrašą.
Tokiais atvejais gera idėja yra sukurti išskleidžiamąjį paieškos verčių sąrašą (šiuo atveju tai gali būti studentų vardai ir dalykai), o tada tiesiog pasirinkti iš sąrašo. Remiantis pasirinkimu, formulė automatiškai atnaujins rezultatą.
Kažkas, kaip parodyta žemiau:
Tai yra geras prietaisų skydelio komponentas, nes galite turėti didžiulį duomenų rinkinį, kurio gale yra šimtai mokinių, tačiau galutinis vartotojas (tarkime, mokytojas) gali greitai gauti dalyko pažymius, tiesiog pasirinkęs iš išskleidžiamasis.
Kaip tai padaryti:
Šiuo atveju naudojama formulė yra tokia pati kaip 2 pavyzdyje.
= INDEKSAS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Peržiūros vertės buvo konvertuotos į išskleidžiamuosius sąrašus.
Štai kaip sukurti išskleidžiamąjį „Excel“ sąrašą:
- Pasirinkite langelį, kuriame norite išskleidžiamojo sąrašo. Šiame pavyzdyje, G4, norime mokinių vardų.
- Eikite į Duomenys -> Duomenų įrankiai -> Duomenų patvirtinimas.
- Lauke Duomenų patvirtinimo dialogas, nustatymų skirtuko lape, išskleidžiamajame meniu Leisti pasirinkite Sąrašas.
- Šaltinyje pasirinkite $ A $ 3: $ A $ 10
- Spustelėkite Gerai.
Dabar langelyje G5 turėsite išskleidžiamąjį sąrašą. Panašiai galite sukurti vieną H4 dalykams.
4 pavyzdys - grąžinkite vertes iš visos eilutės/stulpelio
Anksčiau pateiktuose pavyzdžiuose mes naudojome „Excel INDEX“ funkciją, kad atliktume dviejų krypčių peržiūrą ir gautume vieną vertę.
Ką daryti, jei norite gauti visus studento pažymius. Tai gali padėti rasti maksimalų/mažiausią to studento balą arba bendrą visų dalykų įvertinimą.
Paprasta anglų kalba pirmiausia norite gauti visą mokinio (tarkime, Bobo) balų eilutę, o tada tose vertėse nustatyti aukščiausią balą arba visų balų sumą.
Čia yra triukas.
Funkcijoje „Excel INDEX“, kai įvedate stulpelio numeris yra 0, grąžins visos tos eilutės reikšmes.
Taigi to formulė būtų tokia:
= INDEKSAS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Dabar ši formulė. jei naudojamas kaip yra, grąžins #VALUE! klaida. Nors rodoma klaida, vidinėje sistemoje ji pateikia masyvą, kuriame yra visi Tomo balai - {57,77,91,91}.
Jei redagavimo režimu pasirinksite formulę ir paspausite F9, galėsite pamatyti masyvą, kurį jis grąžina (kaip parodyta žemiau): 
Panašiai, atsižvelgiant į tai, kokia yra paieškos vertė, kai stulpelio numeris nurodomas kaip 0 (arba paliekamas tuščias), jis grąžina visas peržiūros vertės eilutės reikšmes
Dabar, norėdami apskaičiuoti bendrą Tomo gautą rezultatą, galime tiesiog naudoti aukščiau pateiktą formulę SUM funkcijoje.
= SUMA (INDEKSAS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
Panašiose eilutėse, norėdami apskaičiuoti aukščiausią balą, galime naudoti MAX/LARGE, o minimalų - MIN/SMALL.
5 pavyzdys - trijų krypčių paieška naudojant INDEX/MATCH
„Excel INDEX“ funkcija skirta trijų krypčių paieškoms apdoroti.
Kas yra trijų krypčių paieška?
Anksčiau pateiktuose pavyzdžiuose mes naudojome vieną lentelę su balais skirtingiems dalykams. Tai yra dvipusio paieškos pavyzdys, nes mes naudojame du kintamuosius, kad gautume balą (studento vardą ir dalyką).
Dabar, tarkime, per metus studentas turi tris skirtingus egzaminų lygius: vieneto testą, vidurio kursą ir baigiamąjį egzaminą (tai aš turėjau būdamas studentas).
Trijų krypčių paieška būtų galimybė gauti studento pažymius už nurodytą dalyką iš nurodyto egzamino lygio. Tai būtų trijų krypčių paieška, nes yra trys kintamieji (studento vardas, dalyko pavadinimas ir egzamino lygis).
Štai trijų krypčių paieškos pavyzdys:
Aukščiau pateiktame pavyzdyje galite pasirinkti ne tik mokinio vardą ir dalyko pavadinimą, bet ir egzamino lygį. Remiantis egzamino lygiu, jis grąžina atitinkančią vertę iš vienos iš trijų lentelių.
Čia yra formulė, naudojama ląstelėje H4:
= INDEKSAS (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0), MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = "Vieneto testas", 1, IF ($ H $ 2 = "Vidurio laikotarpis", 2,3)))
Suskaidykime šią formulę, kad suprastume, kaip ji veikia.
Ši formulė apima keturis argumentus. INDEX yra viena iš tų „Excel“ funkcijų, kuri turi daugiau nei vieną sintaksę.
= INDEX (masyvas, eilutės numeris, [stulpelio numeris])
= INDEX (masyvas, eilutės numeris, [stulpelio_numeris], [ploto_numeris])
Iki šiol visame aukščiau pateiktame pavyzdyje mes naudojome pirmąją sintaksę, tačiau norėdami atlikti trijų krypčių peržiūrą, turime naudoti antrąją sintaksę.
Dabar pažiūrėkime kiekvieną formulės dalį pagal antrąją sintaksę.
- masyvas - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Užuot naudoję vieną masyvą, šiuo atveju skliausteliuose naudojome tris masyvus.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): funkcija MATCH naudojama mokinio vardo pozicijai rasti mokinio vardo sąrašo langelyje $ G $ 4.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): funkcija MATCH naudojama objekto pavadinimo pozicijai rasti objekto pavadinimo sąrašo langelyje $ H $ 3.
- [area_num] - IF ($ H $ 2 = ”Vieneto testas”, 1, IF ($ H $ 2 = ”Vidurio laikotarpis”, 2,3)): ploto skaičiaus reikšmė nurodo funkcijai INDEX, kurį masyvą pasirinkti. Šiame pavyzdyje pirmame argumente yra trys masyvai. Jei išskleidžiamajame meniu pasirenkate vieneto testą, funkcija IF grąžina 1, o INDEX funkcijos pasirenka 1-ąjį masyvą iš trijų masyvų (tai yra $ B $ 3: $ E $ 7).
6 pavyzdys. Nuorodos kūrimas naudojant funkciją INDEX (dinaminiai pavadinti diapazonai)
Tai vienas laukinis „Excel INDEX“ funkcijos naudojimas.
Paimkime paprastą pavyzdį.
Turiu vardų sąrašą, kaip parodyta žemiau:
Dabar galiu naudoti paprastą INDEX funkciją, kad gaučiau pavardę sąraše.
Čia yra formulė:
= INDEKSAS ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Ši funkcija tiesiog suskaičiuoja tuščių langelių skaičių ir grąžina paskutinį šio sąrašo elementą (ji veikia tik tada, kai sąraše nėra tuščių vietų).
Na, kas čia per magija.
Jei įdėsite formulę prieš langelio nuorodą, formulė grąžins atitinkančios vertės langelio nuorodą (o ne pačią vertę).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Tikėtumėte, kad aukščiau pateikta formulė grįš = A2: „Josh“ (kur Josh yra paskutinė sąrašo vertė). Tačiau jis grąžina = A2: A9, todėl gausite pavadinimų masyvą, kaip parodyta žemiau:
Vienas praktinis pavyzdys, kai ši technika gali būti naudinga, yra dinamiškų pavadintų diapazonų kūrimas.
Tai yra šioje pamokoje. Bandžiau pateikti pagrindinius „Excel INDEX“ funkcijos naudojimo pavyzdžius. Jei norite, kad prie šio sąrašo būtų pridėta daugiau pavyzdžių, praneškite man komentarų skiltyje.
Pastaba: aš stengiausi kuo geriau įrodyti, kad perskaitysite šią pamoką, bet jei radote kokių nors klaidų ar rašybos klaidų, praneškite man 🙂
„Excel INDEX“ funkcija - vaizdo įrašo pamoka
- „Excel VLOOKUP“ funkcija.
- „Excel HLOOKUP“ funkcija.
- „Excel“ netiesioginė funkcija.
- „Excel MATCH“ funkcija.
- „Excel OFFSET“ funkcija.
Jums taip pat gali patikti šios „Excel“ pamokos:
- VLOOKUP vs. RODIKLIS/RINKINYS
- „Excel“ rodyklių atitiktis
- Peržiūrėkite ir grąžinkite vertes visoje eilutėje/stulpelyje.
