Excel INDEX-functie - Formulevoorbeelden + GRATIS video

Excel INDEX-functie (voorbeelden + video)

Wanneer Excel INDEX-functie gebruiken?

Excel INDEX-functie kan worden gebruikt wanneer u de waarde uit tabelgegevens wilt halen en u het rijnummer en kolomnummer van het gegevenspunt hebt. In het onderstaande voorbeeld kunt u bijvoorbeeld de functie INDEX gebruiken om de tekens van 'Tom' in Natuurkunde te krijgen als u het rijnummer en het kolomnummer in de dataset weet.

Wat het teruggeeft

Het retourneert de waarde uit een tabel voor het opgegeven rijnummer en kolomnummer.

Syntaxis

=INDEX (matrix, rij_num, [col_num])
=INDEX (matrix, rij_num, [col_num], [area_num])

INDEX-functie heeft 2 syntaxis. De eerste wordt in de meeste gevallen gebruikt, maar in het geval van three-way lookups, wordt de tweede gebruikt (behandeld in voorbeeld 5).

Invoerargumenten

• reeks - een celbereik of een matrixconstante.
• rij_num - het rijnummer waaruit de waarde moet worden opgehaald.
• [col_num] - het kolomnummer waaruit de waarde moet worden opgehaald. Hoewel dit een optioneel argument is, maar als row_num niet is opgegeven, moet het worden opgegeven.
• [gebied_num] - (Optioneel) Als het array-argument uit meerdere bereiken bestaat, wordt dit nummer gebruikt om de verwijzing uit alle bereiken te selecteren.

Aanvullende opmerkingen (saaie dingen … maar belangrijk om te weten)

• Als het rijnummer of het kolomnummer 0 is, worden de waarden van respectievelijk de hele rij of kolom geretourneerd.
• Als de functie INDEX wordt gebruikt voor een celverwijzing (zoals A1:), retourneert deze een celverwijzing in plaats van een waarde (zie onderstaande voorbeelden).
• Het meest gebruikt samen met de MATCH-functie.
• In tegenstelling tot VERT.ZOEKEN kan de functie INDEX een waarde retourneren vanaf de linkerkant van de opzoekwaarde.
• INDEX-functie heeft twee vormen: matrixvorm en de referentievorm
  • 'Arrayvorm' is waar u een waarde ophaalt op basis van rij- en kolomnummer uit een bepaalde tabel.
  • 'Referentieformulier' is waar er meerdere tabellen zijn en u gebruikt het argument area_num om de tabel te selecteren en vervolgens een waarde erin op te halen met behulp van het rij- en kolomnummer (zie live voorbeeld hieronder).

Excel INDEX-functie - voorbeelden

Hier zijn zes voorbeelden van het gebruik van de Excel INDEX-functie.

Voorbeeld 1 - Tom's Marks vinden in de natuurkunde (een tweerichtingszoekopdracht)

Stel dat u een dataset heeft zoals hieronder weergegeven:

Gebruik de onderstaande formule om de cijfers van Tom in de natuurkunde te vinden:

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

Deze INDEX-formule specificeert de array als $B$3:$E$10 die de markeringen heeft voor alle onderwerpen. Vervolgens gebruikt het het rijnummer (3) en kolomnummer (2) om Tom's cijfers in Natuurkunde op te halen.

Voorbeeld 2 - De LOOKUP-waarde dynamisch maken met de MATCH-functie

Het is misschien niet altijd mogelijk om het rijnummer en het kolomnummer handmatig op te geven. U heeft misschien een enorme dataset, of u wilt deze dynamisch maken zodat deze automatisch de naam en/of het onderwerp identificeert die in cellen zijn gespecificeerd en het juiste resultaat geeft.

Iets zoals hieronder weergegeven:

Dit kan met een combinatie van de INDEX- en de MATCH-functie.

Hier is de formule die de opzoekwaarden dynamisch maakt:

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

In de bovenstaande formule, in plaats van het rijnummer en het kolomnummer hard te coderen, wordt de MATCH-functie gebruikt om het dynamisch te maken.

• Dynamisch rijnummer wordt gegeven door het volgende deel van de formule - MATCH($G$5,$A$3:$A$10,0). Het scant de naam van studenten en identificeert de opzoekwaarde ($G$5 in dit geval). Vervolgens wordt het rijnummer van de opzoekwaarde in de gegevensset geretourneerd. Als de opzoekwaarde bijvoorbeeld Matt is, retourneert deze 1, als het Bob is, retourneert deze 2 enzovoort.
• Dynamisch kolomnummer wordt gegeven door het volgende deel van de formule - MATCH($H$4,$B$2:$E$2,0). Het scant de onderwerpnamen en identificeert de opzoekwaarde ($H$4 in dit geval). Vervolgens wordt het kolomnummer van de opzoekwaarde in de gegevensset geretourneerd. Als de opzoekwaarde bijvoorbeeld Math is, retourneert het 1, als het Natuurkunde is, retourneert het 2 enzovoort.

Voorbeeld 3 - Vervolgkeuzelijsten gebruiken als opzoekwaarden

In het bovenstaande voorbeeld moeten we de gegevens handmatig invoeren. Dat kan tijdrovend en foutgevoelig zijn, vooral als je een enorme lijst met opzoekwaarden hebt.

In dergelijke gevallen is het een goed idee om een ​​vervolgkeuzelijst met de opzoekwaarden te maken (in dit geval kunnen het namen van leerlingen en vakken zijn) en vervolgens gewoon uit de lijst te kiezen. Op basis van de selectie zou de formule het resultaat automatisch bijwerken.

Iets zoals hieronder weergegeven:

Dit is een goede dashboardcomponent, omdat je een enorme dataset kunt hebben met honderden studenten aan de achterkant, maar de eindgebruiker (laten we zeggen een leraar) kan snel de cijfers van een student in een onderwerp krijgen door simpelweg de selecties te maken uit de drop-down.

Hoe maak je dit:

De formule die in dit geval wordt gebruikt, is dezelfde als in voorbeeld 2.

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

De opzoekwaarden zijn omgezet in vervolgkeuzelijsten.

Dit zijn de stappen om de vervolgkeuzelijst Excel te maken:

• Selecteer de cel waarin u de vervolgkeuzelijst wilt. In dit voorbeeld willen we in G4 de namen van de leerlingen.
• Ga naar Gegevens -> Gegevenshulpmiddelen -> Gegevensvalidatie.
• Selecteer in het dialoogvenster Gegevensvalidatie op het tabblad Instellingen de optie Lijst in de vervolgkeuzelijst Toestaan.
• Selecteer in de bron $A$3:$A$10
• Klik OK.

Nu heb je de vervolgkeuzelijst in cel G5. Op dezelfde manier kunt u er een maken in H4 voor de onderwerpen.

Voorbeeld 4 - Retourwaarden van een hele rij/kolom

In de bovenstaande voorbeelden hebben we de Excel INDEX-functie gebruikt om een ​​2-way lookup uit te voeren en een enkele waarde te krijgen.

Nu, wat als je alle cijfers van een student wilt halen. Dit kan u toelaten om de maximum/minimum score van die student te vinden, of het totaal aantal behaalde punten in alle vakken.

In eenvoudig Engels wil je eerst de hele rij scores voor een student krijgen (laten we zeggen Bob) en dan binnen die waarden de hoogste score of het totaal van alle scores identificeren.

Hier is de truc.

In Excel INDEX-functie, wanneer u de . invoert kolomnummer als 0, zal het de waarden van die hele rij retourneren.

Dus de formule hiervoor zou zijn:

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

Nu deze formule. indien gebruikt zoals het is, zou de #VALUE! fout. Terwijl het de fout weergeeft, retourneert het in de backend een array met alle scores voor Tom - {57,77,91,91}.

Als u de formule selecteert in de bewerkingsmodus en op F9 drukt, kunt u de matrix zien die wordt geretourneerd (zoals hieronder weergegeven):

Evenzo, op basis van wat de opzoekwaarde is, retourneert het, wanneer het kolomnummer is opgegeven als 0 (of leeg wordt gelaten), alle waarden in de rij voor de opzoekwaarde

Om nu de totale score van Tom te berekenen, kunnen we eenvoudig de bovenstaande formule gebruiken in de SOM-functie.

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

Op vergelijkbare lijnen, om de hoogste score te berekenen, kunnen we MAX/LARGE gebruiken en om het minimum te berekenen, kunnen we MIN/SMALL gebruiken.

Voorbeeld 5 - Drievoudig opzoeken met INDEX/MATCH

Excel INDEX-functie is gebouwd om drierichtingszoekopdrachten af ​​te handelen.

Wat is een three-way lookup?

In de bovenstaande voorbeelden hebben we één tabel gebruikt met scores voor studenten in verschillende vakken. Dit is een voorbeeld van een tweerichtingszoekopdracht, omdat we twee variabelen gebruiken om de score op te halen (naam van de student en het onderwerp).

Stel nu dat een student over een jaar drie verschillende niveaus van examens heeft, Unit Test, Midterm en Final Examination (dat is wat ik had toen ik student was).

Een drie-weg-zoekopdracht zou de mogelijkheid zijn om de cijfers van een student voor een bepaald onderwerp van het gespecificeerde examenniveau te krijgen. Dit zou het een drierichtingszoekopdracht maken, aangezien er drie variabelen zijn (naam van de student, de naam van het onderwerp en het examenniveau).

Hier is een voorbeeld van een zoekactie in drie richtingen:

In het bovenstaande voorbeeld kun je naast het selecteren van de naam van de student en het vak ook het niveau van het examen selecteren. Op basis van het examenniveau wordt de overeenkomende waarde uit een van de drie tabellen geretourneerd.

Hier is de formule die wordt gebruikt in cel 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="Eenheidstest",1,IF($H$2="Midterm",2,3)))

Laten we deze formule opsplitsen om te begrijpen hoe het werkt.

Voor deze formule zijn vier argumenten nodig. INDEX is een van die functies in Excel die meer dan één syntaxis heeft.

=INDEX (matrix, rij_num, [col_num])
=INDEX (matrix, rij_num, [col_num], [area_num])

Tot dusver hebben we in al het bovenstaande voorbeeld de eerste syntaxis gebruikt, maar om een ​​driewegzoekopdracht uit te voeren, moeten we de tweede syntaxis gebruiken.

Laten we nu elk deel van de formule bekijken op basis van de tweede syntaxis.

• array - ($B$3:$E$7,$B$11:$E$15,$B$19:$E$23): in plaats van een enkele array te gebruiken, hebben we in dit geval drie arrays tussen haakjes gebruikt.
• row_num - MATCH($G$4,$A$3:$A$7,0): MATCH-functie wordt gebruikt om de positie van de naam van de student te vinden in cel $G$4 in de lijst met de naam van de student.
• col_num - MATCH($H$3,$B$2:$E$2,0): MATCH-functie wordt gebruikt om de positie van de onderwerpnaam in cel $H$3 in de lijst met de naam van het onderwerp te vinden.
• [area_num] - IF($H$2=”Unit Test”,1,IF($H$2=”Midterm”,2,3)): De oppervlaktenummerwaarde vertelt de INDEX-functie welke array moet worden geselecteerd. In dit voorbeeld hebben we drie arrays in het eerste argument. Als u Unit Test selecteert in de vervolgkeuzelijst, retourneert de IF-functie 1 en selecteren de INDEX-functies de eerste array van de drie arrays (dat is $B$3:$E$7).

Voorbeeld 6 - Een referentie maken met behulp van de INDEX-functie (dynamische benoemde bereiken)

Dit is een wild gebruik van de Excel INDEX-functie.

Laten we een eenvoudig voorbeeld nemen.

Ik heb een lijst met namen zoals hieronder weergegeven:

Nu kan ik een eenvoudige INDEX-functie gebruiken om de achternaam op de lijst te krijgen.

Hier is de formule:

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

Deze functie telt eenvoudig het aantal cellen dat niet leeg is en retourneert het laatste item uit deze lijst (het werkt alleen als er geen lege plekken in de lijst zijn).

Nu, wat hier komt de magie.

Als u de formule voor een celverwijzing plaatst, zou de formule een celverwijzing van de overeenkomende waarde retourneren (in plaats van de waarde zelf).

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

Je zou verwachten dat de bovenstaande formule =A2:"Josh" retourneert (waarbij Josh de laatste waarde in de lijst is). Het retourneert echter =A2:A9 en daarom krijgt u een reeks namen zoals hieronder weergegeven:

Een praktisch voorbeeld waarbij deze techniek nuttig kan zijn, is het maken van dynamische benoemde bereiken.

Dat is het in deze tutorial. Ik heb geprobeerd belangrijke voorbeelden van het gebruik van de Excel INDEX-functie te behandelen. Als je meer voorbeelden aan deze lijst wilt zien, laat het me dan weten in het opmerkingengedeelte.

Opmerking: ik heb mijn best gedaan om deze tutorial te controleren, maar als je fouten of spelfouten vindt, laat het me dan weten 🙂

Excel INDEX-functie - Videozelfstudie

• Excel VERT.ZOEKEN Functie.
• Excel HORIZ.ZOEKEN-functie.
• Excel INDIRECTE Functie.
• Excel MATCH-functie.
• Excel OFFSET-functie.

Misschien vind je de volgende Excel-zelfstudies misschien ook leuk:

• VERT.ZOEKEN Vs. INDEX/MATCH
• Excel-indexovereenkomst
• Zoek en retourneer waarden in een hele rij/kolom.