Excel INDEX -funktion - Formelexempel + GRATIS video

Excel INDEX -funktion (exempel + video)

När ska du använda Excel INDEX -funktionen

Excel INDEX -funktionen kan användas när du vill hämta värdet från en tabelldata och du har datapunktens radnummer och kolumnnummer. Till exempel i exemplet nedan kan du använda INDEX -funktionen för att få märkena "Tom" i fysik när du känner till radnumret och kolumnnumret i datamängden.

Vad den returnerar

Det returnerar värdet från en tabell för det angivna radnumret och kolumnnumret.

Syntax

= INDEX (array, row_num, [col_num])
= INDEX (matris, radnummer, [kol_nummer], [områdenummer])

INDEX -funktionen har 2 syntaxer. Den första används i de flesta fall, men vid trevägsuppslag används den andra (täcks i exempel 5).

Inmatningsargument

• array - a cellintervall eller en arraykonstant.
• radnummer - radnumret från vilket värdet ska hämtas.
• [col_num] - kolumnnumret från vilket värdet ska hämtas. Även om detta är ett valfritt argument, men om radnummer inte tillhandahålls, måste det anges.
• [area_num] - (Valfritt) Om array -argument består av flera intervall, skulle detta nummer användas för att välja referens från alla intervall.

Ytterligare anteckningar (tråkiga saker … men viktigt att veta)

• Om radnumret eller kolumnnumret är 0 returnerar det värdena för hela raden respektive kolumnen.
• Om INDEX -funktionen används framför en cellreferens (t.ex. A1 :) returnerar den en cellreferens istället för ett värde (se exempel nedan).
• Mest använda tillsammans med MATCH -funktionen.
• Till skillnad från VLOOKUP kan INDEX -funktionen returnera ett värde från vänster om uppslagsvärdet.
• INDEX -funktionen har två former - matrisform och referensformulär
  • "Arrayform" är där du hämtar ett värde baserat på rad och kolumnnummer från en given tabell.
  • "Referensformulär" är där det finns flera tabeller, och du använder argumentet area_num för att välja tabellen och sedan hämta ett värde inom den med hjälp av raden och kolumnnumret (se liveexempel nedan).

Excel INDEX -funktion - Exempel

Här är sex exempel på hur du använder Excel INDEX -funktion.

Exempel 1 - Hitta Tom's Marks in Physics (en tvåvägsuppslagning)

Anta att du har en dataset som visas nedan:

För att hitta Toms betyg i fysik, använd formeln nedan:

= INDEX ($ B $ 3: $ E $ 10,3,2)

Denna INDEX -formel anger matrisen som $ B $ 3: $ E $ 10 som har märkena för alla ämnen. Sedan använder den radnumret (3) och kolumnnumret (2) för att hämta Toms betyg i fysik.

Exempel 2 - Gör LOOKUP -värdet dynamiskt med MATCH -funktionen

Det är inte alltid möjligt att ange radnumret och kolumnnumret manuellt. Du kan ha en enorm datauppsättning, eller så kanske du vill göra den dynamisk så att den automatiskt identifierar namnet och/eller ämnet som anges i celler och ger rätt resultat.

Något som visas nedan:

Detta kan göras med en kombination av INDEX och MATCH -funktionen.

Här är formeln som gör uppslagsvärdena dynamiska:

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

I formeln ovan, istället för att hårdkoda radnumret och kolumnnumret, används MATCH-funktionen för att göra det dynamiskt.

• Dynamiskt radnummer ges av följande del av formeln - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Den skannar namnet på elever och identifierar uppslagsvärdet ($ G $ 5 i detta fall). Det returnerar sedan radnumret för uppslagsvärdet i datamängden. Till exempel, om uppslagsvärdet är Matt, kommer det att returnera 1, om det är Bob, kommer det att returnera 2 och så vidare.
• Dynamiskt kolumnnummer ges av följande del av formeln - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Den skannar ämnesnamnen och identifierar uppslagsvärdet ($ H $ 4 i detta fall). Det returnerar sedan kolumnnumret för uppslagsvärdet i datamängden. Till exempel, om uppslagsvärdet är matematik, kommer det att returnera 1, om det är fysik, kommer det att returnera 2 och så vidare.

Exempel 3 - Använda rullgardinslistor som uppslagsvärden

I exemplet ovan måste vi ange data manuellt. Det kan vara tidskrävande och felbenäget, särskilt om du har en enorm lista med uppslagsvärden.

En bra idé i sådana fall är att skapa en rullgardinslista med uppslagsvärden (i det här fallet kan det vara elevnamn och ämnen) och sedan helt enkelt välja från listan. Baserat på valet skulle formeln uppdatera resultatet automatiskt.

Något som visas nedan:

Detta är en bra instrumentpanelskomponent eftersom du kan ha en enorm datauppsättning med hundratals elever i bakänden, men slutanvändaren (låt oss säga en lärare) kan snabbt få betyg på en elev i ett ämne genom att helt enkelt göra val från rullgardinsmenyn.

Så här gör du:

Formeln som används i detta fall är densamma som i exempel 2.

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

Sökvärdena har konverterats till listrutor.

Här är stegen för att skapa rullgardinsmenyn Excel:

• Välj den cell där du vill ha listrutan. I det här exemplet, i G4, vill vi ha studentnamnen.
• Gå till Data -> Dataverktyg -> Datavalidering.
• I rutan Datavalidering, på fliken Inställningar, välj Lista på rullgardinsmenyn Tillåt.
• Välj $ A $ 3: $ A $ 10 i källan
• Klicka på OK.

Nu har du rullgardinsmenyn i cell G5. På samma sätt kan du skapa en i H4 för ämnena.

Exempel 4 - Returvärden från en hel rad/kolumn

I exemplen ovan har vi använt Excel INDEX-funktionen för att göra en tvåvägsuppslagning och få ett enda värde.

Tänk om du vill få alla betyg på en student. Detta kan göra det möjligt för dig att hitta den högsta/lägsta poängen för den eleven eller totala poängen i alla ämnen.

På enkel engelska vill du först få hela poängraden för en elev (låt oss säga Bob) och sedan inom dessa värden identifiera den högsta poängen eller summan av alla poäng.

Här är tricket.

I Excel INDEX -funktion, när du anger kolumnnummer som 0, kommer den att returnera värdena för hela raden.

Så formeln för detta skulle vara:

= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)

Nu denna formel. om den används som den är, skulle den returnera #VÄRDE! fel. Medan det visar felet returnerar det i backend en array som har alla poäng för Tom - {57,77,91,91}.

Om du väljer formeln i redigeringsläget och trycker på F9 kan du se matrisen som den returnerar (som visas nedan):

På samma sätt, baserat på vad uppslagsvärdet är, när kolumnnumret anges som 0 (eller lämnas tomt), returnerar det alla värden i raden för uppslagsvärdet

Nu för att beräkna den totala poängen som erhållits av Tom kan vi helt enkelt använda ovanstående formel inom SUM -funktionen.

= SUMMA (INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))

På liknande linjer kan vi använda MAX/LARGE för att beräkna den högsta poängen och för att beräkna minimum kan vi använda MIN/SMALL.

Exempel 5 - Trevägsuppslagning med hjälp av INDEX/MATCH

Excel INDEX-funktionen är byggd för att hantera trevägsuppslag.

Vad är en trevägsuppslagning?

I exemplen ovan har vi använt en tabell med poäng för elever i olika ämnen. Detta är ett exempel på en tvåvägsuppslagning eftersom vi använder två variabler för att hämta poängen (elevens namn och ämnet).

Antag nu att om ett år har en student tre olika nivåer av tentor, enhetstest, halvtids- och slutprov (det var vad jag hade när jag var student).

En trevägsuppslagning skulle vara möjligheten att få en elevs betyg för ett visst ämne från den angivna tentamen. Detta skulle göra det till en trevägsuppslagning eftersom det finns tre variabler (elevens namn, ämnesnamn och examinationsnivå).

Här är ett exempel på en trevägsuppslagning:

I exemplet ovan kan du, förutom att välja elevens namn och ämnesnamn, också välja examen. Baserat på examensnivån returnerar det matchningsvärdet från en av de tre tabellerna.

Här är formeln som används i cell 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)))

Låt oss bryta ner denna formel för att förstå hur det fungerar.

Denna formel tar fyra argument. INDEX är en av de funktioner i Excel som har mer än en syntax.

= INDEX (array, row_num, [col_num])
= INDEX (matris, radnummer, [kol_nummer], [områdenummer])

Hittills i alla exemplen ovan har vi använt den första syntaxen, men för att göra en trevägsuppslagning måste vi använda den andra syntaxen.

Låt oss nu se varje del av formeln baserad på den andra syntaxen.

• array - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): I stället för att använda en enda array har vi i det här fallet använt tre matriser inom parentes.
• row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): MATCH -funktionen används för att hitta positionen för elevens namn i cell $ G $ 4 i listan över elevens namn.
• col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): MATCH -funktionen används för att hitta positionen för ämnesnamnet i cell $ H $ 3 i listan över ämnesnamn.
• [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Områdesvärdet anger INDEX -funktionen vilken array som ska väljas. I det här exemplet har vi tre matriser i det första argumentet. Om du väljer Enhetstest från rullgardinsmenyn returnerar IF-funktionen 1 och INDEX-funktionerna väljer första matrisen från de tre matriserna (vilket är $ B $ 3: $ E $ 7).

Exempel 6 - Skapa en referens med INDEX -funktionen (dynamiska namngivna intervall)

Detta är en vild användning av Excel INDEX -funktionen.

Låt oss ta ett enkelt exempel.

Jag har en lista med namn enligt nedan:

Nu kan jag använda en enkel INDEX -funktion för att få efternamnet på listan.

Här är formeln:

= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Denna funktion räknar helt enkelt antalet celler som inte är tomma och returnerar det sista objektet från den här listan (det fungerar bara när det inte finns några ämnen i listan).

Nu, vad här kommer magin.

Om du sätter formeln framför en cellreferens, skulle formeln returnera en cellreferens med matchningsvärdet (istället för själva värdet).

= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Du förväntar dig att ovanstående formel ska returnera = A2: ”Josh” (där Josh är det sista värdet i listan). Det returnerar dock = A2: A9 och därför får du en rad namn som visas nedan:

Ett praktiskt exempel där denna teknik kan vara till hjälp är att skapa dynamiska namngivna intervall.

Det är det i den här självstudien. Jag har försökt att täcka stora exempel på att använda Excel INDEX -funktionen. Om du vill se fler exempel läggas till i den här listan, meddela mig i kommentarfältet.

Obs: Jag har försökt mitt bästa för att korrekturläsa denna handledning, men om du hittar några fel eller stavfel, vänligen meddela mig 🙂

Excel INDEX -funktion - Videohandledning

• Excel VLOOKUP -funktion.
• Excel HLOOKUP -funktion.
• Excel INDIRECT -funktion.
• Excel MATCH -funktion.
• Excel OFFSET -funktion.

Du kanske också gillar följande Excel -självstudier:

• VLOOKUP Vs. INDEX/MATCH
• Excel -indexmatchning
• Sök- och returvärden i en hel rad/kolumn.