Excel Index Funktion und Spiel-Funktion
INDEX und MATCH Einführung
- Die INDEX-Funktion kann ein Element aus einer bestimmten Position in einer Liste zurück.
- Die Funktion MATCH kann die Position eines Wertes in einer Liste zurück.
- Die INDEX / MATCH-Funktionen können zusammen verwendet werden, als ein flexibles und leistungsfähiges Werkzeug für die Daten aus einer Tabelle zu extrahieren.
Video: INDEX Funktion
In diesem Video werden Sie sehen, wie Sie die INDEX-Funktion verwenden, um:
- Finden Verkaufsbetrag für Monat
- Erhalten Bezugnahme auf bestimmte Zeile, Spalte-Bereich
- Erstellen Sie einen Dynamikbereich basierend auf Zahl
- Sortieren Textspalte in alphabetischer Reihenfolge
Es gibt andere Beispiele INDEX unter dem Video. siehe zu diesem Video schriftliche Anweisungen, den Kontexturen Blog-Artikel: 30 Excel-Funktionen in 30 Tagen: 24 - INDEX
INDEX-Funktion - Argumente
Die INDEX Funktion hat drei Argumente:
- Array. Wo ist die Liste? Wenn Sie eine absolute Referenz ($ A $ 2: $ B $ 4) verwenden, statt eine relative Referenz (A2: B4), wird es leichter sein, Formel auf andere Zellen zu kopieren. Oder nennen Sie die Lookup-Tabelle, und bezeichnen sie mit Namen.
- row_num. Welche Zeile hat sich der Wert zurückgegeben werden soll? In diesem Beispiel wird das Element in der dritten Zeile zurückgegeben werden.
- [Column_num]. Welche Spalte hat sich der Wert zurückgegeben werden soll? In diesem Beispiel wird das Element in der zweiten Spalte zurückgegeben werden.
INDEX-Funktion - Beispiel
Die INDEX-Funktion kann ein Element aus einer bestimmten Position in einer bestimmten Spalte in einer Liste zurück. Zum Beispiel, was ist das dritte Element in der zweiten Spalte in der Liste unten?
- Markieren Sie die Zelle, in der Sie das Ergebnis wollen
- Geben Sie ein Gleichheitszeichen, die INDEX Funktionsnamen und eine öffnende Klammer:
= INDEX ( - Markieren Sie die Zellen, die die Liste enthalten - Zellen A2: B4 in diesem Beispiel
- Drücken Sie die F4-Taste auf der Tastatur, die Bezugnahme auf eine absolute Referenz zu ändern: $ A $ 2: $ B $ 4. Dann, wenn Sie die Formel in eine andere Zelle kopieren, wird es auch weiterhin die Liste der Elemente verweisen.
- Geben Sie ein Komma zu trennen, die Argumente
- Geben Sie die Anzahl der Artikel, den Sie zurückkehren wollen - in diesem Beispiel 3
- Geben Sie ein Komma zu trennen, die Argumente
- Geben Sie die Nummer der Spalte, die Sie zurückkehren wollen - in diesem Beispiel 2
- Fertig mit einer schließenden Klammer. Die fertiggestellte Formel lautet:
= INDEX ($ A $ 2: $ B $ 4,3,2) - Drücken Sie die Enter-Taste um das Ergebnis zu sehen.
Interaktive INDEX Beispiel
In der eingebetteten Excel-Datei unten, können Sie die Funktion INDEX Beispiel. Auf Blatt 1 sind die Zeilen- und Spaltennummern in der Formel eingegeben - hartcodiert.
HINWEIS: Die interaktive Datei auf allen Geräten oder Browsern nicht sichtbar sein könnte.
Auf Blatt 2 sind die Zeilen- und Spaltennummern in Arbeitsblatt-Zellen. Ändern der Zeilen- oder Spaltennummer die Formel Ergebnis zu ändern.
MATCH-Funktion - Argumente
Die MATCH-Funktion hat drei Argumente:
- Lookup-Wert. Welchen Wert möchten Sie in der Liste finden? Sie können den Wert eingeben oder auf eine Zelle, die den Wert enthält.
- Suchmatrix. Wo ist die Liste?
- [Match_type]. Match_type kann -1, 0, oder 1. Es sagt Excel, wie die lookup_value auf Werte in der Suchmatrix entsprechen.
1 - finden der größte Wert kleiner oder gleich dem Suchkriterium
(Die Liste muss aufsteigend sein)
0 - finden Sie den ersten Wert genau gleich dem Suchkriterium ist. Suchmatrix
(Die Liste kann in beliebiger Reihenfolge)
-1 - der kleinste Wert größer als oder gleich finden Suchkriterium ist.
(Die Liste ist in absteigender Reihenfolge sein)
Hinweis: Wenn match_type weggelassen wird, wird angenommen, 1 zu sein.
MATCH-Funktion - Beispiel
Die MATCH-Funktion kann einen Wert in einer Liste finden, und seine Position zurück. Wo zum Beispiel ist „Jacket“ in der Liste unten?
- Markieren Sie die Zelle, in der Sie das Ergebnis wollen
- Geben Sie ein Gleichheitszeichen, die MATCH-Funktionsnamen und eine öffnende Klammer:
= MATCH ( - Geben Sie den Wert zu finden, oder klicken Sie auf die Zelle, die den Wert enthält - Zelle A5 in diesem Beispiel
- Geben Sie ein Komma zu trennen, die Argumente
- Wählen Sie den Bereich, der die Liste enthält - einen Bereich namens ItemList in diesem Beispiel
- Geben Sie ein Komma zu trennen, die Argumente
- Geben Sie die Nummer für den Spieltyp, den Sie verwenden möchten - 0 in diesem Beispiel verwendet wurde, weil eine genaue Übereinstimmung erforderlich ist.
- Fertig mit einer schließenden Klammer. Die fertiggestellte Formel lautet:
= MATCH (A5, ItemList, 0) - Drücken Sie die Enter-Taste um das Ergebnis zu sehen.
INDEX / MATCH - Beispiel 1
Ein Vorteil der INDEX / MATCH-Funktionen ist, dass der Lookup-Wert in jeder Spalte in der Anordnung sein kann, im Gegensatz zu der Funktion SVERWEIS, bei dem der Referenzwert in der ersten Spalte liegen.
In diesem INDEX / MATCH Beispiel wird die MATCH-Funktion die Position der „Jacket“ in Spalte B finden, und die Funktion INDEX wird der Code von der gleichen Position in Spalte A zurückkehren
- Stellen Sie das Arbeitsblatt nach oben wie rechts dargestellt
- Geben Sie die folgende Formel in Zelle B6:
INDEX = ($ A $ 2: $ A $ 4, MATCH (A6, $ B $ 2: B $ $ 4,0)) - Drücken Sie die Enter-Taste um das Ergebnis zu sehen.
Die MATCH-Funktion MATCH (A6, $ B $ 2: $ B $ 4,0). 2 liefert, die die Position „Jacket“ in der Liste ist.
Dann wird die Funktion INDEX INDEX ($ A $ 2: $ A $ 4,2). returns "JK002", die das zweite Element im Bereich von $ A $ 2: $ A $ 4.
INDEX / MATCH - Beispiel 2
Die MATCH-Funktion kann Werte verwendet werden, für die in der INDEX-Funktion sowohl für die row_num und column_num Argumente zurückzukehren.
Das erste Spiel Funktion, MATCH (B7, $ A $ 2: 4,0 $ A $). 3 zurück, die die Position der „Hosen“ in der Liste Elemente ist.
Die zweite MATCH-Funktion, MATCH (A7, B $ $ 1: $ D $ 1,0). 2 zurück, die die Position des „Med“ in der Liste Größe ist.
Dann wird die Funktion INDEX INDEX ($ B $ 2: $ D $ 4,3,2). returns "30", das das dritte Element in der zweiten Spalte im Bereich von $ B $ 2: 4 $ $ D.
INDEX / MATCH - Beispiel 3
Um das vorherige Beispiel noch flexibler zu machen, können Sie die Funktion INDEX in der MATCH-Funktion verwenden, um Werte in der ersten Zeile oder Spalte einer benannten Tabelle zu suchen. (Dieses INDEX / MATCH Beispiel stammt aus einer Newsgroup Posting von Peo Sjoblom)
Stellen Sie das Arbeitsblatt, wie oben gezeigt,
- Die grünen Zellen sind eine Reihe namens Tabelle). (Anweisungen zur Namensgebung eines Bereichs)
- Geben Sie die folgende Formel in Zelle C7:
INDEX = (Tabelle, MATCH (B7, INDEX (Tabelle ,, 1), 0), MATCH (A7, INDEX (Tabelle, 1,), 0)) - Drücken Sie die Enter-Taste um das Ergebnis zu sehen.
Die erste MATCH-Funktion, MATCH (B7, INDEX (Tabelle ,, 1), 0). sucht nach „Hosen“ in der ersten Spalte der Tabelle Bereich: 4 (A1 A4) und kehrt zurück.
Die zweite MATCH-Funktion, MATCH (A7, INDEX (Tabelle, 1,), 0). sucht „Med“ in der ersten Zeile der Tabelle Bereichs (A1: D1) und gibt 3.
Dann wird der INDEX Funktion INDEX (Tabelle 4,3). returns „30“, die in der vierten Reihe in der dritten Spalte in der genannten Tabelle Bereich ist.
INDEX / MATCH - Beispiel 4
Video: Lookup Mit mehreren Kriterien
Ihr Browser kann diesen Rahmen nicht zeigen. Hier ist ein Link auf die Seite
In diesem INDEX / MATCH Beispiel anstelle von Spaltenüberschriften von Klein, Med, Großen und Ganzen ist die Größe in der Spalte C gelagert
Sie müssen den Preis von Spalte D finden, wenn Jacke in Spalte B ist, und Med ist in Spalte C
- Stellen Sie das Arbeitsblatt nach oben wie rechts dargestellt
- Geben Sie die folgende Formel in Zelle C13:
= INDEX ($ D $ 2: $ D $ 10, MATCH (1, (A13 = $ B $ 2: $ B $ 10) * (B13 = $ C $ 2: $ C $ 10), 0)) - Dies ist eine Matrixformel, halten so Strg + Shift und drücken Sie die Enter-Taste um das Ergebnis zu sehen.
Geschweiften Klammern werden automatisch auf die Formel hinzugefügt werden (sie sie selbst nicht geben!), So dass das Endergebnis wird wie folgt aussehen:
Als Nächstes erstellen Sie in Zelle D13, eine ähnliche INDEX / MATCH Formel den richtigen Code aus Spalte A zu erhalten
Einzelheiten darüber, wie diese Formel funktioniert, können Sie die Erklärung auf dem Kontexturen Blog lesen: Überprüfen mehrere Kriterien mit Excel INDEX und MATCH
Video: Hier finden Sie am besten mit dem Index, MATCH, MIN
Durch die Kombination der Funktion INDEX mit MATCH und MIN, können Sie den Namen des Ladens zeigen, der den niedrigsten Preis. MIN berechnet den niedrigsten Preis und MATCH ortet, dass der Preis in der Reihe. INDEX gibt den Speichernamen für die ausgewählte Spalte.
Bei schriftlichen Anleitungen für diese Funktion Tutorial Excel INDEX finden Sie in dem Kontexturen Blog-Artikel: Hier finden Sie am besten mit Excel INDEX und MATCH
Video: Finden Distanz zwischen den Städten mit INDEX / MATCH
Von einer Lookup-Tabelle mit Entfernungen zwischen den Städten, können Sie die Funktionen INDEX und VERGLEICH verwenden die Kilometerleistung zwischen zwei ausgewählten Städten zu zeigen.
Dieses Video sehen Sie die Schritte, um zu sehen, und Sie können die Videos Beispieldatei herunterladen, um zu sehen, wie es funktioniert.
Fehlerbehebung für die MATCH Formel
Text vs. Anzahl
Wenn möglich, wandelt den Text in Zahlen, mit einem der Methoden, die hier gezeigt:
Konvertieren Sie Text in Zahlen
Wenn Sie die Daten nicht konvertieren können, können Sie den Lookup-Wert in der MATCH Formel umwandeln:
Lookup-Werte sind Text und die Tabelle enthält Zahlen
Wenn die Lookup-Array Zahlen enthält, und der Wert zu sehen ist Text, verwenden Sie eine Formel ähnlich den folgenden:
Das Doppel einstellige (-) wandelt Text in eine Zahl, und funktioniert auch dann korrekt, wenn die Nachschlagewerte Zahlen sind.
Lookup Werte sind Zahlen, und die Tabelle enthält Text
Wenn der Lookup-Array Text enthält, und den Wert zu sehen ist numerisch, eine Formel ähnlich den folgenden verwenden:
MATCH = (A5 - ""), ItemList, 0)
ODER
= MATCH (TEXT (A5, "00000"), ItemList, 0)
Die Funktion TEXT konvertiert eine Zahl in Text, und funktioniert auch dann korrekt, wenn die Nachschlagewerte Text ist. Im ersten Beispiel wird der - erstellt Operator eine Textzeichenfolge aus einer unformatierten Nummer. In dem zweiten Beispiel wird eine Reihe mit führenden Nullen formatiert (z.B. 00123) würde mit führenden Nullen einen Text „number“ entsprechen.
Spaces in einem Wert, und nicht die anderen
Wenn möglich, entfernen Sie die nicht benötigten Räume und die MATCH Formel sollte korrekt funktionieren. Wenn Sie die Räume nicht entfernen können, verwenden Sie die TRIM-Funktion in der MATCH, was zu entfernen, Hinter oder Leerzeichen duplizieren. Beispielsweise:
MATCH = (TRIM (A5), ItemList, 0)