Excel INDEX -funksjon (eksempler + video)
Når skal jeg bruke Excel INDEX -funksjon
Excel INDEX -funksjonen kan brukes når du vil hente verdien fra tabelldata, og du har radnummeret og kolonnummeret til datapunktet. For eksempel, i eksemplet nedenfor, kan du bruke INDEX -funksjonen til å få merkene "Tom" i fysikk når du kjenner radnummeret og kolonnenummeret i datasettet.
Hva den returnerer
Den returnerer verdien fra en tabell for det angitte radnummeret og kolonnummeret.
Syntaks
= INDEX (matrise, radnummer, [kolnr])
= INDEX (matrise, radnummer, [kolnr], [områdenummer])
INDEX -funksjonen har 2 syntakser. Den første brukes i de fleste tilfeller, men ved treveis oppslag brukes den andre (dekket i eksempel 5).
Inngangsargumenter
- matrise - en rekkevidde av celler eller en matrisekonstant.
- radnummer - radnummeret som verdien skal hentes fra.
- [col_num] - kolonnetallet som verdien skal hentes fra. Selv om dette er et valgfritt argument, men hvis radnummer ikke er angitt, må det oppgis.
- [arealnummer] - (Valgfritt) Hvis array -argument består av flere områder, vil dette tallet bli brukt til å velge referansen fra alle områdene.
Ytterligere merknader (kjedelige ting … men viktig å vite)
- Hvis radnummeret eller kolonnenummeret er 0, returnerer det verdiene for henholdsvis hele raden eller kolonnen.
- Hvis INDEX -funksjonen brukes foran en cellereferanse (for eksempel A1 :) returnerer den en cellereferanse i stedet for en verdi (se eksempler nedenfor).
- Mest brukt sammen med MATCH -funksjonen.
- I motsetning til VLOOKUP kan INDEX -funksjonen returnere en verdi fra venstre for oppslagsverdien.
- INDEX -funksjonen har to former - Array -skjema og referanse -skjema
- ‘Arrayform’ er hvor du henter en verdi basert på rad- og kolonnenummer fra en gitt tabell.
- ‘Referanseskjema’ er der det er flere tabeller, og du bruker argumentet area_num til å velge tabellen og deretter hente en verdi i den ved hjelp av raden og kolonnenummeret (se eksempelet nedenfor).
Excel INDEX -funksjon - eksempler
Her er seks eksempler på bruk av Excel INDEX -funksjon.
Eksempel 1 - Finne Toms merker i fysikk (et toveis oppslag)
Anta at du har et datasett som vist nedenfor:
For å finne Toms karakterer i fysikk, bruk formelen nedenfor:
= INDEKS ($ B $ 3: $ E $ 10,3,2)
Denne INDEX -formelen spesifiserer matrisen som $ B $ 3: $ E $ 10 som har merkene for alle fagene. Deretter bruker den radnummeret (3) og kolonnenummeret (2) for å hente Toms karakterer i fysikk.
Eksempel 2 - Gjør LOOKUP -verdien dynamisk ved hjelp av MATCH -funksjonen
Det er ikke alltid mulig å spesifisere radnummeret og kolonnummeret manuelt. Du kan ha et stort datasett, eller du kan gjøre det dynamisk slik at det automatisk identifiserer navnet og/eller emnet som er angitt i celler og gir det riktige resultatet.
Noe som vist nedenfor:
Dette kan gjøres ved å bruke en kombinasjon av INDEX og MATCH -funksjonen.
Her er formelen som vil gjøre oppslagsverdiene dynamiske:
= INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
I formelen ovenfor, i stedet for hardkoding av radnummeret og kolonnetallet, brukes MATCH-funksjonen for å gjøre det dynamisk.
- Dynamisk radnummer er gitt av følgende del av formelen - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Den skanner navnet på studenter og identifiserer oppslagsverdien ($ G $ 5 i dette tilfellet). Den returnerer deretter radnummeret til oppslagsverdien i datasettet. For eksempel, hvis oppslagsverdien er Matt, vil den returnere 1, hvis det er Bob, vil den returnere 2 og så videre.
- Dynamisk kolonnenummer er gitt av følgende del av formelen - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Den skanner emnets navn og identifiserer oppslagsverdien ($ H $ 4 i dette tilfellet). Den returnerer deretter kolonnenummeret til oppslagsverdien i datasettet. For eksempel, hvis oppslagsverdien er matematikk, vil den returnere 1, hvis det er fysikk, vil den returnere 2 og så videre.
Eksempel 3 - Bruk nedtrekkslister som oppslagsverdier
I eksemplet ovenfor må vi angi dataene manuelt. Det kan være tidkrevende og utsatt for feil, spesielt hvis du har en enorm liste over oppslagsverdier.
En god idé i slike tilfeller er å lage en rullegardinliste med oppslagsverdiene (i dette tilfellet kan det være elevenavn og emner) og deretter velge fra listen. Basert på valget, vil formelen automatisk oppdatere resultatet.
Noe som vist nedenfor:
Dette er en god dashbordkomponent, ettersom du kan ha et stort datasett med hundrevis av elever i bakenden, men sluttbrukeren (la oss si en lærer) kan raskt få karakterene til en elev i et emne ved ganske enkelt å velge fra nedtrekkslisten.
Slik lager du dette:
Formelen som brukes i dette tilfellet er den samme som i eksempel 2.
= INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Oppslagsverdiene er konvertert til rullegardinlister.
Her er trinnene for å lage rullegardinlisten i Excel:
- Velg cellen du vil ha rullegardinlisten i. I dette eksemplet, i G4, ønsker vi studentnavnene.
- Gå til Data -> Dataverktøy -> Datavalidering.
- I dialogboksen Datavalidering, i kategorien Innstillinger, velger du Liste fra Tillat-rullegardinmenyen.
- Velg $ A $ 3 i kilden: $ A $ 10
- Klikk OK.
Nå har du rullegardinlisten i celle G5. På samme måte kan du opprette en i H4 for fagene.
Eksempel 4 - Returner verdier fra en hel rad/kolonne
I eksemplene ovenfor har vi brukt Excel INDEX-funksjonen for å gjøre et 2-veis oppslag og få en enkelt verdi.
Hva om du vil få alle karakterene til en student. Dette kan gjøre det mulig for deg å finne maks/minimum poengsummen til den eleven, eller totalkarakterene som er scoret i alle fag.
På enkelt engelsk vil du først få hele poengsummen til en elev (la oss si Bob) og deretter identifisere den høyeste poengsummen eller summen av alle poengsummene innenfor disse verdiene.
Her er trikset.
I Excel INDEX -funksjonen, når du skriver inn kolonnenummer som 0, vil den returnere verdiene for hele raden.
Så formelen for dette vil være:
= INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Nå denne formelen. hvis den brukes som den er, vil den returnere #VERDI! feil. Selv om den viser feilen, returnerer den i backend en matrise som har alle poengsummene for Tom - {57,77,91,91}.
Hvis du velger formelen i redigeringsmodus og trykker på F9, vil du kunne se matrisen den returnerer (som vist nedenfor): 
På samme måte, basert på hva oppslagsverdien er, når kolonnenummeret er angitt som 0 (eller er tomt), returnerer det alle verdiene i raden for oppslagsverdien
For å beregne den totale poengsummen oppnådd av Tom, kan vi ganske enkelt bruke formelen ovenfor i SUM -funksjonen.
= SUMME (INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
På lignende linjer, for å beregne den høyeste poengsummen, kan vi bruke MAX/LARGE og for å beregne minimum kan vi bruke MIN/SMALL.
Eksempel 5 - Treveis oppslag ved hjelp av INDEX/MATCH
Excel INDEX-funksjonen er bygget for å håndtere tre-veis oppslag.
Hva er en tre-veis oppslag?
I eksemplene ovenfor har vi brukt en tabell med score for studenter i forskjellige fag. Dette er et eksempel på en toveis oppslag da vi bruker to variabler for å hente poengsummen (elevens navn og emnet).
Anta at om et år har en student tre forskjellige eksamensnivåer, enhetstest, midtveiseksamen og avsluttende eksamen (det var det jeg hadde da jeg var student).
Et tre-veis oppslag ville være muligheten til å få elevens karakterer for et bestemt emne fra det angitte eksamensnivået. Dette vil gjøre det til et tre-veis oppslag, da det er tre variabler (elevens navn, fagets navn og eksamensnivå).
Her er et eksempel på en tre-veis oppslag:
I eksemplet ovenfor, bortsett fra å velge elevens navn og fagnavn, kan du også velge eksamensnivå. Basert på eksamensnivået returnerer den matchende verdien fra en av de tre tabellene.
Her er formelen som brukes i celle H4:
= INDEX (($ 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 = "Unit Test", 1, IF ($ H $ 2 = "Midterm", 2,3)))
La oss bryte ned denne formelen for å forstå hvordan den fungerer.
Denne formelen tar fire argumenter. INDEX er en av de funksjonene i Excel som har mer enn én syntaks.
= INDEX (matrise, radnummer, [kolnr])
= INDEX (matrise, radnummer, [kolnr], [områdenummer])
Så langt i alle eksemplene ovenfor har vi brukt den første syntaksen, men for å gjøre et tre-veis oppslag må vi bruke den andre syntaksen.
La oss nå se hver del av formelen basert på den andre syntaksen.
- array - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): I stedet for å bruke en enkelt matrise, i dette tilfellet, har vi brukt tre matriser innenfor parentes.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): MATCH -funksjonen brukes til å finne posisjonen til elevens navn i celle $ G $ 4 i listen over elevens navn.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): MATCH -funksjonen brukes til å finne plasseringen til emnets navn i celle $ H $ 3 i listen over emnets navn.
- [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Areanummer -verdien forteller INDEX -funksjonen hvilken matrise du skal velge. I dette eksemplet har vi tre matriser i det første argumentet. Hvis du velger Enhetstest fra rullegardinmenyen, returnerer IF-funksjonen 1 og INDEX-funksjonene velger 1. array fra de tre matrisene (som er $ B $ 3: $ E $ 7).
Eksempel 6 - Opprette en referanse ved hjelp av INDEX -funksjonen (dynamiske navngitte områder)
Dette er en vill bruk av Excel INDEX -funksjonen.
La oss ta et enkelt eksempel.
Jeg har en liste med navn som vist nedenfor:
Nå kan jeg bruke en enkel INDEX -funksjon for å få etternavnet på listen.
Her er formelen:
= INDEKS ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Denne funksjonen teller ganske enkelt antall celler som ikke er tomme og returnerer det siste elementet fra denne listen (det fungerer bare når det ikke er noen blanke i listen).
Nå, hva her kommer magien.
Hvis du setter formelen foran en cellereferanse, vil formelen returnere en cellereferanse med matchende verdi (i stedet for selve verdien).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Du forventer at formelen ovenfor returnerer = A2: "Josh" (hvor Josh er den siste verdien i listen). Den returnerer imidlertid = A2: A9, og derfor får du en rekke navn som vist nedenfor:
Et praktisk eksempel hvor denne teknikken kan være nyttig er å lage dynamiske navngitte områder.
Det er det i denne opplæringen. Jeg har prøvd å dekke viktige eksempler på bruk av Excel INDEX -funksjonen. Hvis du vil se flere eksempler lagt til denne listen, gi meg beskjed i kommentarfeltet.
Merk: Jeg har prøvd mitt beste for å korrekturlese denne opplæringen, men hvis du finner feil eller stavefeil, vennligst gi meg beskjed 🙂
Excel INDEX -funksjon - Videoopplæring
- Excel VLOOKUP -funksjon.
- Excel HLOOKUP -funksjon.
- Excel INDIRECT -funksjon.
- Excel MATCH -funksjon.
- Excel OFFSET -funksjon.
Du kan også like følgende Excel -opplæringsprogrammer:
- VLOOKUP Vs. INDEKS/MATCH
- Excel Index Match
- Slå opp og returner verdier i en hel rad/kolonne.
