✅ Excel-INDEX-Funktion - Formelbeispiele + KOSTENLOSES Video

Inhaltsverzeichnis

Excel INDEX-Funktion (Beispiele + Video)
Excel-INDEX-Funktion - Beispiele
Excel INDEX-Funktion - Video-Tutorial

Excel INDEX-Funktion (Beispiele + Video)

Wann Sie die Excel-INDEX-Funktion verwenden sollten

Die Excel-INDEX-Funktion kann verwendet werden, wenn Sie den Wert aus Tabellendaten abrufen möchten und die Zeilennummer und Spaltennummer des Datenpunkts haben. Im folgenden Beispiel können Sie beispielsweise die INDEX-Funktion verwenden, um die Noten von „Tom“ in Physik zu erhalten, wenn Sie die Zeilennummer und die Spaltennummer im Datensatz kennen.

Was es zurückgibt

Es gibt den Wert aus einer Tabelle für die angegebene Zeilennummer und Spaltennummer zurück.

Syntax

=INDEX (Array, Zeilennummer, [Spaltennummer])
=INDEX (Array, Zeilennummer, [Spaltennummer], [Bereichsnummer])

Die INDEX-Funktion hat 2 Syntax. Der erste wird in den meisten Fällen verwendet, im Fall von Drei-Wege-Lookups wird jedoch der zweite verwendet (in Beispiel 5 behandelt).

Eingabeargumente

Array - ein Zellbereich oder eine Array-Konstante.
row_num - die Zeilennummer, aus der der Wert geholt werden soll.
[Spaltenzahl] - die Spaltennummer, aus der der Wert geholt werden soll. Dies ist zwar ein optionales Argument, aber wenn row_num nicht angegeben wird, muss es angegeben werden.
[are_num] - (Optional) Wenn das Array-Argument aus mehreren Bereichen besteht, wird diese Zahl verwendet, um die Referenz aus allen Bereichen auszuwählen.

Zusätzliche Anmerkungen (langweiliges Zeug… aber wichtig zu wissen)

Wenn die Zeilennummer oder die Spaltennummer 0 ist, werden die Werte der gesamten Zeile bzw. Spalte zurückgegeben.
Wenn die INDEX-Funktion vor einem Zellbezug verwendet wird (z. B. A1:), gibt er anstelle eines Werts einen Zellbezug zurück (siehe Beispiele unten).
Am häufigsten zusammen mit der MATCH-Funktion verwendet.
Im Gegensatz zu SVERWEIS kann die INDEX-Funktion einen Wert links vom Nachschlagewert zurückgeben.
Die INDEX-Funktion hat zwei Formen - das Array-Formular und das Referenz-Formular
- In „Array-Form“ rufen Sie einen Wert basierend auf der Zeilen- und Spaltennummer aus einer bestimmten Tabelle ab.
- „Referenzformular“ ist, wenn es mehrere Tabellen gibt, und Sie verwenden das Argument area_num, um die Tabelle auszuwählen und dann einen Wert darin mithilfe der Zeilen- und Spaltennummer abzurufen (siehe Live-Beispiel unten).

Excel-INDEX-Funktion - Beispiele

Hier sind sechs Beispiele für die Verwendung der Excel-INDEX-Funktion.

Beispiel 1 – Auffinden von Toms Markierungen in der Physik (eine bidirektionale Suche)

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Um Toms Noten in Physik zu finden, verwenden Sie die folgende Formel:

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

Diese INDEX-Formel gibt das Array als $B$3:$E$10 an, das die Noten für alle Fächer enthält. Dann verwendet es die Zeilennummer (3) und die Spaltennummer (2), um Toms Noten in Physik abzurufen.

Beispiel 2 – Dynamischen LOOKUP-Wert mit der MATCH-Funktion

Es ist möglicherweise nicht immer möglich, die Zeilennummer und die Spaltennummer manuell anzugeben. Möglicherweise haben Sie einen riesigen Datensatz oder möchten ihn dynamisch gestalten, damit er automatisch den in den Zellen angegebenen Namen und/oder Betreff erkennt und das richtige Ergebnis liefert.

Etwas wie unten gezeigt:

Dies kann durch eine Kombination der INDEX- und der MATCH-Funktion erfolgen.

Hier ist die Formel, die die Lookup-Werte dynamisch macht:

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

In der obigen Formel wird die MATCH-Funktion verwendet, um sie dynamisch zu machen, anstatt die Zeilennummer und die Spaltennummer hart zu codieren.

Die dynamische Zeilennummer wird durch den folgenden Teil der Formel angegeben - MATCH($G$5,$A$3:$A$10,0). Es scannt die Namen der Schüler und identifiziert den Nachschlagewert (in diesem Fall $ G $ 5). Anschließend wird die Zeilennummer des Nachschlagewerts im Dataset zurückgegeben. Wenn der Lookup-Wert beispielsweise Matt ist, wird 1 zurückgegeben, wenn es Bob ist, wird 2 zurückgegeben usw.
Die dynamische Spaltennummer wird durch den folgenden Teil der Formel angegeben - MATCH($H$4,$B$2:$E$2,0). Es scannt die Subjektnamen und identifiziert den Nachschlagewert (in diesem Fall $H$4). Anschließend wird die Spaltennummer des Nachschlagewerts im Dataset zurückgegeben. Wenn der Lookup-Wert beispielsweise Math ist, wird 1 zurückgegeben, wenn es sich um Physics handelt, wird 2 zurückgegeben und so weiter.

Beispiel 3 – Verwenden von Dropdown-Listen als Nachschlagewerte

Im obigen Beispiel müssen wir die Daten manuell eingeben. Das kann zeitaufwändig und fehleranfällig sein, insbesondere wenn Sie eine riesige Liste von Nachschlagewerten haben.

In solchen Fällen ist es eine gute Idee, eine Dropdown-Liste der Nachschlagewerte zu erstellen (in diesem Fall könnten es Schülernamen und Fächer sein) und dann einfach aus der Liste auswählen. Basierend auf der Auswahl aktualisiert die Formel das Ergebnis automatisch.

Etwas wie unten gezeigt:

Dies macht eine gute Dashboard-Komponente aus, da Sie einen riesigen Datensatz mit Hunderten von Schülern im Backend haben können, aber der Endbenutzer (sagen wir ein Lehrer) kann schnell die Noten eines Schülers in einem Fach erhalten, indem er einfach die Auswahl trifft das Herunterfallen.

So machen Sie das:

Die in diesem Fall verwendete Formel ist die gleiche wie in Beispiel 2.

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

Die Lookup-Werte wurden in Dropdown-Listen umgewandelt.

Hier sind die Schritte zum Erstellen der Excel-Dropdown-Liste:

Wählen Sie die Zelle aus, in der die Dropdown-Liste angezeigt werden soll. In diesem Beispiel in G4 wollen wir die Schülernamen.
Gehen Sie zu Daten -> Datentools -> Datenvalidierung.
Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste aus der Dropdown-Liste Zulassen aus.
Wählen Sie in der Quelle $A$3:$A$10
OK klicken.

Jetzt haben Sie die Dropdown-Liste in Zelle G5. Ebenso können Sie in H4 eine für die Fächer erstellen.

Beispiel 4 – Rückgabewerte aus einer ganzen Zeile/Spalte

In den obigen Beispielen haben wir die Excel-INDEX-Funktion verwendet, um eine 2-Wege-Suche durchzuführen und einen einzelnen Wert zu erhalten.

Nun, was ist, wenn Sie alle Noten eines Schülers erhalten möchten. Auf diese Weise können Sie die Höchst-/Mindestpunktzahl dieses Schülers oder die Gesamtpunktzahl in allen Fächern ermitteln.

Im einfachen Englisch möchten Sie zuerst die gesamte Punktzahl für einen Schüler (sagen wir Bob) erhalten und dann innerhalb dieser Werte die höchste Punktzahl oder die Summe aller Punktzahlen identifizieren.

Hier ist der Trick.

In der Excel-INDEX-Funktion, wenn Sie die eingeben Spaltennummer als 0, gibt es die Werte der gesamten Zeile zurück.

Die Formel dafür wäre also:

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

Nun diese Formel. würde bei unveränderter Verwendung den #VALUE zurückgeben! Error. Während es den Fehler anzeigt, gibt es im Backend ein Array zurück, das alle Punktzahlen für Tom enthält - {57,77,91,91}.

Wenn Sie die Formel im Bearbeitungsmodus auswählen und F9 drücken, können Sie das zurückgegebene Array sehen (wie unten gezeigt):

Wenn die Spaltennummer als 0 angegeben (oder leer gelassen) wird, werden basierend auf dem Nachschlagewert alle Werte in der Zeile für den Nachschlagewert zurückgegeben

Um nun die von Tom erhaltene Gesamtpunktzahl zu berechnen, können wir einfach die obige Formel innerhalb der SUM-Funktion verwenden.

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

In ähnlicher Weise können wir zur Berechnung der höchsten Punktzahl MAX/LARGE und zur Berechnung des Minimums MIN/SMALL verwenden.

Beispiel 5 – Drei-Wege-Suche mit INDEX/MATCH

Die Excel-INDEX-Funktion wurde für die Drei-Wege-Suche entwickelt.

Was ist ein Drei-Wege-Lookup?

In den obigen Beispielen haben wir eine Tabelle mit Punktzahlen für Schüler in verschiedenen Fächern verwendet. Dies ist ein Beispiel für eine bidirektionale Suche, da wir zwei Variablen verwenden, um die Punktzahl abzurufen (Schülername und Fach).

Angenommen, in einem Jahr hat ein Student drei verschiedene Prüfungsstufen, Unit Test, Midterm und Final Examination (das hatte ich als Student).

Eine Drei-Wege-Suche wäre die Möglichkeit, die Noten eines Schülers für ein bestimmtes Fach aus dem angegebenen Prüfungsniveau zu erhalten. Dies würde es zu einer Drei-Wege-Suche machen, da es drei Variablen gibt (Schülername, Fächername und Prüfungsniveau).

Hier ist ein Beispiel für eine Drei-Wege-Suche:

Im obigen Beispiel können Sie neben dem Namen des Schülers und des Fachs auch die Prüfungsstufe auswählen. Basierend auf der Prüfungsstufe gibt es den passenden Wert aus einer der drei Tabellen zurück.

Hier ist die Formel, die in Zelle H4 verwendet wird:

=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)))

Lassen Sie uns diese Formel aufschlüsseln, um zu verstehen, wie sie funktioniert.

Diese Formel benötigt vier Argumente. INDEX ist eine dieser Funktionen in Excel, die mehr als eine Syntax hat.

=INDEX (Array, Zeilennummer, [Spaltennummer])
=INDEX (Array, Zeilennummer, [Spaltennummer], [Bereichsnummer])

Bisher haben wir in allen obigen Beispielen die erste Syntax verwendet, aber um eine Drei-Wege-Suche durchzuführen, müssen wir die zweite Syntax verwenden.

Sehen wir uns nun jeden Teil der Formel basierend auf der zweiten Syntax an.

array - ($B$3:$E$7,$B$11:$E$15,$B$19:$E$23): Anstelle eines einzelnen Arrays haben wir in diesem Fall drei Arrays in Klammern verwendet.
row_num - MATCH($G$4,$A$3:$A$7,0): Die MATCH-Funktion wird verwendet, um die Position des Schülernamens in Zelle $G$4 in der Liste der Schülernamen zu finden.
col_num - MATCH($H$3,$B$2:$E$2,0): Die MATCH-Funktion wird verwendet, um die Position des Subjektnamens in Zelle $H$3 in der Liste der Subjektnamen zu finden.
[area_num] - IF($H$2=”Unit Test”,1,IF($H$2=”Midterm”,2,3)): Der Bereichsnummerwert teilt der INDEX-Funktion mit, welches Array auszuwählen ist. In diesem Beispiel haben wir drei Arrays im ersten Argument. Wenn Sie Unit Test aus der Dropdown-Liste auswählen, gibt die IF-Funktion 1 zurück und die INDEX-Funktionen wählen das erste Array aus den drei Arrays (das ist $B$3:$E$7).

Beispiel 6 – Erstellen einer Referenz mit der INDEX-Funktion (dynamische benannte Bereiche)

Dies ist eine wilde Verwendung der Excel-INDEX-Funktion.

Nehmen wir ein einfaches Beispiel.

Ich habe eine Liste mit Namen wie unten gezeigt:

Jetzt kann ich eine einfache INDEX-Funktion verwenden, um den Nachnamen auf der Liste zu erhalten.

Hier ist die Formel:

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

Diese Funktion zählt einfach die Anzahl der Zellen, die nicht leer sind, und gibt das letzte Element aus dieser Liste zurück (sie funktioniert nur, wenn die Liste keine Leerzeichen enthält).

Nun, was hier kommt die Magie.

Wenn Sie die Formel vor einen Zellbezug setzen, würde die Formel einen Zellbezug des übereinstimmenden Werts (anstelle des Werts selbst) zurückgeben.

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

Sie würden erwarten, dass die obige Formel =A2:”Josh” zurückgibt (wobei Josh der letzte Wert in der Liste ist). Es gibt jedoch =A2:A9 zurück und daher erhalten Sie ein Array von Namen wie unten gezeigt:

Ein praktisches Beispiel, bei dem diese Technik hilfreich sein kann, ist das Erstellen dynamischer benannter Bereiche.

Das ist es in diesem Tutorial. Ich habe versucht, wichtige Beispiele für die Verwendung der Excel-INDEX-Funktion zu behandeln. Wenn Sie weitere Beispiele zu dieser Liste hinzufügen möchten, lassen Sie es mich im Kommentarbereich wissen.

Hinweis: Ich habe mein Bestes versucht, dieses Tutorial Korrektur zu lesen, aber falls Sie Fehler oder Rechtschreibfehler finden, lassen Sie es mich bitte wissen 🙂