Excel INDEX -funktion (eksempler + video)
Hvornår skal man bruge Excel INDEX -funktion
Excel INDEX -funktionen kan bruges, når du vil hente værdien fra en tabelform, og du har datapunktets rækkenummer og kolonnenummer. For eksempel i eksemplet herunder kan du bruge INDEX -funktionen til at få mærkerne "Tom" i fysik, når du kender rækkenummeret og kolonnetallet i datasættet.
Hvad den returnerer
Det returnerer værdien fra en tabel for det angivne rækkenummer og kolonnenummer.
Syntaks
= INDEX (array, row_num, [col_num])
= INDEX (array, row_num, [col_num], [area_num])
INDEX -funktionen har 2 syntaks. Den første bruges i de fleste tilfælde, men i tilfælde af trevejsopslag bruges den anden (dækket i eksempel 5).
Input argumenter
- array - -en område af celler eller en matrixkonstant.
- række_nummer - det rækkenummer, hvorfra værdien skal hentes.
- [col_num] - det kolonnenummer, hvorfra værdien skal hentes. Selvom dette er et valgfrit argument, men hvis row_num ikke er angivet, skal det angives.
- [områdenummer] - (Valgfrit) Hvis array -argument består af flere områder, vil dette nummer blive brugt til at vælge referencen fra alle områderne.
Yderligere bemærkninger (kedelige ting … men vigtigt at vide)
- Hvis rækkenummeret eller kolonnetallet er 0, returnerer det værdierne for henholdsvis hele rækken eller kolonnen.
- Hvis INDEX -funktionen bruges foran en cellereference (f.eks. A1 :) returnerer den en cellereference i stedet for en værdi (se eksempler herunder).
- Mest udbredt sammen med MATCH -funktionen.
- I modsætning til VLOOKUP kan INDEX -funktionen returnere en værdi fra venstre for opslagsværdien.
- INDEX -funktionen har to former - Array -form og Reference -formularen
- 'Arrayform' er, hvor du henter en værdi baseret på række- og kolonnenummer fra en given tabel.
- 'Referenceformular' er, hvor der er flere tabeller, og du bruger argumentet area_num til at vælge tabellen og derefter hente en værdi i den ved hjælp af rækken og kolonnummeret (se liveeksempel nedenfor).
Excel INDEX -funktion - eksempler
Her er seks eksempler på brug af Excel INDEX -funktion.
Eksempel 1 - Find Tom's Marks in Physics (et tovejsopslag)
Antag, at du har et datasæt som vist herunder:
For at finde Toms karakterer i fysik skal du bruge nedenstående formel:
= INDEX ($ B $ 3: $ E $ 10,3,2)
Denne INDEX -formel angiver arrayet som $ B $ 3: $ E $ 10, som har mærkerne for alle emnerne. Derefter bruger den rækkenummer (3) og kolonnenummer (2) til at hente Toms karakterer i fysik.
Eksempel 2 - Gør LOOKUP -værdien dynamisk ved hjælp af MATCH -funktionen
Det er muligvis ikke altid muligt at angive rækkenummeret og kolonnummeret manuelt. Du kan have et stort datasæt, eller du vil måske gøre det dynamisk, så det automatisk identificerer det navn og/eller emne, der er angivet i celler, og giver det korrekte resultat.
Noget som vist herunder:
Dette kan gøres ved hjælp af en kombination af INDEX og MATCH -funktionen.
Her er formlen, der vil gøre opslagsværdierne dynamiske:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
I ovenstående formel bruges MATCH-funktionen til at gøre det dynamisk i stedet for hårdt at kode rækketallet og kolonnetallet.
- Dynamisk rækkenummer er givet ved følgende del af formlen - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Det scanner elevernes navn og identificerer opslagsværdien ($ G $ 5 i dette tilfælde). Det returnerer derefter rækkenummeret for opslagsværdien i datasættet. For eksempel, hvis opslagsværdien er Matt, returnerer den 1, hvis det er Bob, returnerer den 2 og så videre.
- Dynamisk kolonnenummer er givet ved følgende del af formlen - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Den scanner emnets navne og identificerer opslagsværdien ($ H $ 4 i dette tilfælde). Derefter returnerer kolonnenummeret for opslagsværdien i datasættet. For eksempel, hvis opslagsværdien er matematik, returnerer den 1, hvis det er fysik, returnerer den 2 og så videre.
Eksempel 3 - Brug af rullelister som opslagsværdier
I eksemplet ovenfor skal vi manuelt indtaste dataene. Det kan være tidskrævende og fejlbehæftet, især hvis du har en enorm liste over opslagsværdier.
En god idé i sådanne tilfælde er at oprette en rulleliste med opslagsværdierne (i dette tilfælde kan det være elevnavne og emner) og derefter blot vælge fra listen. Baseret på valget opdaterer formlen automatisk resultatet.
Noget som vist herunder:
Dette gør en god instrumentbrætkomponent, da du kan have et stort datasæt med hundredvis af elever i bagenden, men slutbrugeren (lad os sige en lærer) kan hurtigt få karaktererne af en elev i et emne ved blot at vælge fra drop down.
Sådan gør du dette:
Formlen brugt i dette tilfælde er den samme som i eksempel 2.
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Opslagsværdierne er konverteret til rullelister.
Her er trinene til at oprette rullelisten Excel:
- Vælg den celle, du vil have rullelisten i. I dette eksempel, i G4, vil vi have elevnavnene.
- Gå til Data -> Dataværktøjer -> Datavalidering.
- I dialogboksen Datavalidering, under fanen Indstillinger, skal du vælge Liste på rullelisten Tillad.
- Vælg $ A $ 3 i kilden: $ A $ 10
- Klik på OK.
Nu har du rullelisten i celle G5. På samme måde kan du oprette en i H4 til emnerne.
Eksempel 4 - Returner værdier fra en hel række/kolonne
I eksemplerne ovenfor har vi brugt Excel INDEX-funktion til at lave et 2-vejs opslag og få en enkelt værdi.
Hvad nu hvis du vil få alle karakterer af en studerende. Dette kan gøre det muligt for dig at finde den maksimale/mindste score for den pågældende elev eller de samlede karakterer, der er scoret i alle fag.
På enkelt engelsk vil du først få hele rækken af scoringer for en elev (lad os sige Bob) og derefter inden for disse værdier identificere den højeste score eller summen af alle scoringer.
Her er tricket.
I Excel INDEX -funktion, når du indtaster kolonnenummer som 0, vil det returnere værdierne for hele rækken.
Så formlen for dette ville være:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Nu denne formel. hvis den bruges som den er, ville den returnere #VÆRDIEN! fejl. Selvom den viser fejlen, returnerer den i backend en matrix, der har alle scores for Tom - {57,77,91,91}.
Hvis du vælger formlen i redigeringstilstanden og trykker på F9, vil du kunne se den matrix, den returnerer (som vist nedenfor): 
På samme måde, baseret på hvad opslagsværdien er, når kolonnenummeret er angivet som 0 (eller efterlades tomt), returnerer det alle værdierne i rækken for opslagsværdien
For nu at beregne den samlede score opnået af Tom, kan vi ganske enkelt bruge ovenstående formel inden for SUM -funktionen.
= SUM (INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
På lignende linjer kan vi bruge MAX/LARGE for at beregne den højeste score, og for at beregne minimum kan vi bruge MIN/SMALL.
Eksempel 5 - Trevejsopslag ved hjælp af INDEX/MATCH
Excel INDEX-funktionen er bygget til at håndtere trevejsopslag.
Hvad er et trevejsopslag?
I ovenstående eksempler har vi brugt en tabel med score for elever i forskellige fag. Dette er et eksempel på et tovejsopslag, da vi bruger to variabler til at hente scoren (elevens navn og emnet).
Antag nu om et år, at en elev har tre forskellige niveauer af eksamener, enhedstest, midtvejseksamen og afsluttende eksamen (det var, hvad jeg havde, da jeg var studerende).
Et trevejsopslag ville være evnen til at få en elevs karakterer for et bestemt emne fra det angivne eksamensniveau. Dette ville gøre det til et trevejsopslag, da der er tre variabler (elevens navn, fagets navn og eksamensniveau).
Her er et eksempel på et trevejsopslag:
I eksemplet ovenfor kan du, bortset fra at vælge elevens navn og fagnavn, også vælge eksamensniveau. Baseret på eksamensniveau returnerer den matchende værdi fra en af de tre tabeller.
Her er formlen, der bruges 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)))
Lad os nedbryde denne formel for at forstå, hvordan den fungerer.
Denne formel tager fire argumenter. INDEX er en af de funktioner i Excel, der har mere end én syntaks.
= INDEX (array, row_num, [col_num])
= INDEX (array, row_num, [col_num], [area_num])
Hidtil i alle eksemplerne ovenfor har vi brugt den første syntaks, men for at lave et trevejsopslag skal vi bruge den anden syntaks.
Lad os nu se hver del af formlen baseret på den anden syntaks.
- array - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): I stedet for at bruge et enkelt array har vi i dette tilfælde brugt tre arrays inden for parentes.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): MATCH -funktion bruges til at finde placeringen af elevens navn i celle $ G $ 4 på listen over elevens navn.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): MATCH -funktion bruges til at finde emnets navn i cellen $ H $ 3 på listen over emnets navn.
- [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Arealværdien fortæller INDEX -funktionen, hvilken matrix der skal vælges. I dette eksempel har vi tre arrays i det første argument. Hvis du vælger Enhedstest fra rullemenuen, returnerer IF-funktionen 1, og INDEX-funktionerne vælger 1. array fra de tre arrays (hvilket er $ B $ 3: $ E $ 7).
Eksempel 6 - Oprettelse af en reference ved hjælp af INDEX -funktionen (dynamiske navngivne intervaller)
Dette er en vild brug af Excel INDEX -funktionen.
Lad os tage et enkelt eksempel.
Jeg har en liste med navne som vist herunder:
Nu kan jeg bruge en simpel INDEX -funktion til at få efternavnet på listen.
Her er formlen:
= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Denne funktion tæller simpelthen antallet af celler, der ikke er tomme, og returnerer det sidste element fra denne liste (det fungerer kun, når der ikke er nogen emner på listen).
Nu, hvad her kommer magien.
Hvis du sætter formlen foran en cellereference, returnerer formlen en cellereference for den matchende værdi (i stedet for selve værdien).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Du ville forvente, at ovenstående formel returnerer = A2: ”Josh” (hvor Josh er den sidste værdi på listen). Det returnerer dog = A2: A9, og derfor får du en række navne som vist herunder:
Et praktisk eksempel, hvor denne teknik kan være nyttig, er at skabe dynamiske navngivne områder.
Det er det i denne vejledning. Jeg har forsøgt at dække store eksempler på brug af Excel INDEX -funktionen. Hvis du gerne vil se flere eksempler tilføjet til denne liste, så lad mig vide det i kommentarfeltet.
Bemærk: Jeg har forsøgt mit bedste for at korrekturlæse denne vejledning, men hvis du finder fejl eller stavefejl, så lad mig det vide 🙂
Excel INDEX -funktion - Videoundervisning
- Excel VLOOKUP -funktion.
- Excel HLOOKUP -funktion.
- Excel INDIRECT -funktion.
- Excel MATCH -funktion.
- Excel OFFSET -funktion.
Du kan også lide følgende Excel -selvstudier:
- VLOOKUP Vs. INDEX/MATCH
- Excel Index Match
- Opslag og returner værdier i en hel række/kolonne.
