Verwenden Sie INDEX und MATCH für einfache Datenbankabfragen in Excel

Ursprünglich war Excel nicht als echte Datenbank konzipiert. Seine frühen Datenbankfunktionen waren in Quantität und Qualität begrenzt. Da alle Datensätze in einer Excel-Datenbank gleichzeitig auf dem Bildschirm angezeigt werden, also alle gleichzeitig im Speicher, müssen die Excel-Datenbanken sehr klein sein: mehrere Felder mit wenigen Datensätzen oder wenige Felder mit vielen Datensätzen. und minimale Berechnungen.

SVERWEIS (vertikal) und WVERWEIS (horizontal) waren die einzigen verfügbaren Funktionen, um eine Datenbank nach bestimmten Informationen abzufragen. Sie könnten beispielsweise Abfragen durchführen, um alle Datensätze zu suchen und zu extrahieren, die einen Umsatz von mehr als 1000 $, aber weniger als 5000 $ enthalten, aber nur für einfache Dateien (nur eine Datenbankmatrix).

Pivot-Tabellen wurden entwickelt, damit Benutzer relationale Datenbanken erstellen können einfacher abzufragen, weniger Speicher zu verwenden und genauere Ergebnisse zu liefern. Wenn Sie jedoch keine relationale Datenbank haben oder benötigen, aber leistungsfähigere und zuverlässigere Datenbankfunktionen benötigen, versuchen Sie dies zunächst einmal.

[Weiterführende Literatur: Ihr neuer PC benötigt diese 15 kostenlosen, ausgezeichneten Programme]

Index, Match und Index Match

In Excel gibt die Funktion INDEX ein Element von einer bestimmten Position (in einer Liste, Tabelle, Datenbank) zurück.

Die Funktion MATCH gibt die Position eines Wertes (in einer Liste) zurück , Tabelle, Datenbank). Und die zusammen verwendeten INDEX-MATCH-Funktionen machen das Extrahieren von Daten aus einer Tabelle zu einem Kinderspiel.

Die Syntax für die Funktion INDEX lautet: INDEX (Array, Zeilennummer, [Spaltennummer]). Das Array ist der Bereich der Zellen, mit denen Sie arbeiten. Row_num ist natürlich die Zeilennummer in dem Bereich, der die Daten enthält, nach denen Sie suchen. Column_num ist die Spaltennummer in dem Bereich, der die Daten enthält, nach denen Sie suchen. Die INDEX-Formel erkennt keine Spaltenbriefe, daher müssen Sie Zahlen verwenden (von links beginnend).

Die Syntax für die MATCH-Funktion lautet: MATCH (Suchwert, Sucharray, [Übereinstimmungstyp]). Der Suchwert ist die gesuchte Zahl oder der gesuchte Text. Dies kann ein Wert, ein logischer Wert oder eine Zellreferenz sein. Das lookup_array ist der Bereich der Zellen, mit denen Sie arbeiten. Der match_type bestimmt die MATCH-Funktion, dh eine exakte Übereinstimmung oder die nächste Übereinstimmung.

A. INDEX-Funktion

In unserem Beispiel hat der berühmte Commodore James Norrington eine Tabelle, die alle Piratenschiffe in der Karibik verfolgt. Norringtons Liste wird von den Kampfformationen der Schiffe zusammengestellt, die zu seinen Seekarten des Gebiets passen. Wenn er sieht, dass ein Schiff vorrückt, gibt er die Indexformel in seine Tabellenkalkulation ein, damit er das Schiff und seine Kapazitäten identifizieren kann. In dieser ersten Anfrage möchte Norrington wissen, welche Art von Schiff vorrückt.

1. Wählen Sie eine Position (Zelle oder Zellbereich) für Ihre Abfragen (dh Funktionen und Ergebnisse), und bewegen Sie den Cursor dann auf diese Zelle. Zum Beispiel: eine beliebige Zelle in Zeile 18.

2. Geben Sie die INDEX - Funktion (mit vorangestelltem Gleichheitszeichen) und eine öffnende runde Klammer ein und markieren (oder schreiben) Sie den Datenbank - / Tabellenbereich wie folgt: = INDEX (A2: I16

Hinweis: Wenn Sie eine absolute Referenz wünschen In diesem Fall bedeutet das, dass die Formel hart codiert wird, wenn der Kopierbereich nicht geändert wird. Drücken Sie einmal nach jeder Zellenreferenz F4. Sie könnten auch den Bereich markieren: Drücken Sie einmal F4, nachdem Sie den gesamten Bereich ausgewählt haben , und die vollständigen absoluten Referenzsymbole werden hinzugefügt.

3. Als nächstes geben Sie ein Komma ein, um die Argumente zu trennen (dh die einzelnen Formelstücke), geben Sie dann die Zeilennummer und ein Komma gefolgt von der Spaltennummer ein , muss es eine Zahl sein und nicht der übliche Spaltenbuchstabe) und die rechte Klammer (oder drücken Sie einfach Enter und lassen Sie Excel die Endklammer hinzufügen). Die vollständige Formel sieht so aus: = INDEX ($ A $ 2: $ I $ 16 , 15,2).

Hinweis: Die Zeilennummerierung beginnt mit der ersten Zahl im Bereich, nicht mit der ersten Zahl in der Tabelle, z Obwohl das Cavalleria-Piratenschiff in der Excel-Reihe 16 ist, ist es in unserer Formel tatsächlich die 15. Zeile, da unser Bereich auf A2 beginnt und durch I16 geht. Wenn A2 die Zeile 1 ist, dann ist A16 die Zeile 15).

4. Beachten Sie, dass der Typ des Schiffes, nach dem Norrington gesucht hat, eine War Sloop ist.

JD Sartain

Verwenden Sie die Indexfunktion, um bestimmte Informationen in Ihrer Datenbank zu finden.

B. INDEX-Bereiche

Jetzt können wir mit dieser Datenbanktabelle noch viel mehr tun. Sie müssen den Bereich nicht jedes Mal neu definieren, wenn Sie etwas wissen möchten. Um es einfach zu machen, definieren wir den Bereich einmal und benennen ihn dann. Dann können wir einfach den Namen des Bereichs in unsere Formeln eingeben.

1. Gehe zu A2 und markiere den Bereich A2 bis I16.

2. Wählen Sie auf der Registerkarte "Formeln" in der Gruppe "Definierte Namen" Name definieren .

3. Geben Sie im Popup-Dialogfeld einen Namen für Ihren Bereich in das Feld Name ein.

4. Geben Sie als Nächstes den Bereich ein, in dem sich der Bereich befindet. Dies ist entweder die Arbeitsmappe oder eines der Arbeitsblätter in der Arbeitsmappe.

5. Geben Sie ggf. einen Kommentar ein.

6. Stellen Sie als Letztes sicher, dass im Feld "Referenzieren nach" der korrekte Name und Bereich angezeigt wird, und klicken Sie dann auf OK .

7. Wenn Sie überprüfen möchten, ob Ihr Bereich tatsächlich in Excel gespeichert ist, versuchen Sie diesen kleinen Test: Drücken Sie Strg + G (der GoTo-Befehl). Wählen Sie im GoTo-Dialogfeld Ships , klicken Sie dann auf OK , und Excel hebt den Bereich A2: I16 erneut hervor.

So definieren und speichern Sie einen Bereich

C. INDEX mit Formeln für SUMME & DURCHSCHNITT

Norrington bewertet die Kampffähigkeiten der Flotte. Zuerst möchte er wissen, wie viele Kanonen die Piraten haben, die durchschnittliche Anzahl von Kanonen pro Schiff und die Gesamtzahl der Besatzungen dieser Piratenschiffe. Er gibt folgende Formeln ein:

1. = SUMME (INDEX (Schiffe, 8)) entspricht 334, die Gesamtzahl der Kanonen und

2. = DURCHSCHNITT (INDEX (Schiffe, 8)) entspricht 22,27 oder ungefähr 22,27 Kanonen pro Schiff.

3. = SUMME (INDEX (Schiffe, 7)) entspricht 2350, die Gesamtzahl aller Besatzungsmitglieder auf allen Schiffen.

Warum gibt es ein Komma, ein Leerzeichen, ein Komma zwischen den Schiffen und die Zahl 8, und was bedeuten diese Zahlen? Ships ist der Bereich (gefolgt von einem Komma), das Row-Argument ist leer (oder ein Leerzeichen), weil Norrington alle Zeilen möchte und die 8 die 8. Spalte darstellt (Spalte H, Cannons).

Manche fragen vielleicht , warum nicht einfach die Formeln SUMME und / oder DURCHSCHNITT am Ende dieser Spalten eingeben? In dieser winzigen Tabelle wäre es genauso einfach. Wenn die Tabelle jedoch 5000 Zeilen und 300 Spalten enthält, sollten Sie INDEX

JD Sartain

03 INDEX-Formeln mit SUM und AVERAGE verwenden.

Sobald der Bereich benannt ist, kann Norrington eine leere Tabelle öffnen Dieselbe Arbeitsmappe und schreiben Sie seine Abfragen (Formeln) in Spalte B (die die Ergebnisse anstelle der Formeln anzeigen) mit einer Beschreibung, die diese Abfragen in Spalte A definiert. (Hinweis: Spalte C zeigt die tatsächlichen Formeln, die in Spalte B sind).

Er muss seine riesige Datenbank von 5000 Datensätzen nicht visuell sehen oder einige Sekunden warten, während die Formeln berechnen. Er kann alle Informationen, die er benötigt, aus seinem Query Sheet beziehen. Denken Sie daran, je größer die Tabellenkalkulation ist, desto langsamer funktioniert sie, besonders wenn es viele Formeln gibt.

JD Sartain

04 Commodore James Norringtons Piratenschiffsinformation / Abfrageblatt.

D. INDEX MATCH mit MAX

Jetzt will Norrington wissen, wie viele Piraten sich auf dem bevölkerungsreichsten Schiff befinden und welches Schiff ist das? Er verwendet die Formel INDEX mit MAX, um die höchste Anzahl an Piraten zu erhalten, aber er muss auch wissen, welches Schiff sie trägt. Also benutzt er die INDEX / MATCH mit MAX-Formel, um herauszufinden, welches Schiff die meisten Piraten an Bord hat.

1. = MAX (INDEX (Schiffe, 7)) entspricht 300, die höchste Piratenzahl auf einem der Schiffe

2. = INDEX ($ A $ 2: $ A $ 16, MATCH (MAX (Schiffe), $ G $ 2: G $ 16, 0)) entspricht dem Royal James, dem Schiff mit den meisten Piraten an Bord

3. = INDEX ($ F $ 2: $ F $ 16, MATCH (MAX (Schiffe), $ G $ 2: G $ 16, 0)) entspricht Stede Bonnet, Kapitän der Royal James mit einer Piratenmannschaft von 300

JD Sartain

Verwenden Sie INDEX-MATCH und MAX, um bestimmte Informationen aus Ihrer Datenbank abzurufen.