Durch die Verwendung einer Arrayformel in Excel können Sie eine Suchformel erstellen, die mehrere Kriterien zum Suchen von Informationen in einer Datenbank oder in einer Datentabelle verwendet. Die Matrixformel beinhaltet das Verschachteln der Funktion MATCH in der Funktion INDEX.
Dieses Lernprogramm enthält ein schrittweises Beispiel für das Erstellen einer Suchformel, die mehrere Kriterien zum Suchen eines Lieferanten verwendetTitan Widgets in einer Beispieldatenbank. Folgen Sie den Anweisungen, um zu erfahren, wie einfach es ist, eigene Suchformeln zu erstellen.
Bereiten Sie Ihre Excel-Arbeitsmappe mit Daten vor
Um die Schritte in diesem Lernprogramm ausführen zu können, müssen Sie zunächst die Beispieldaten in die folgenden Zellen eingeben, wie in der Abbildung oben gezeigt. Die Zeilen 3 und 4 bleiben leer, um die während dieses Lernprogramms erstellte Array-Formel aufzunehmen.
- Geben Sie den oberen Datenbereich in Zellen ein D1 zu F2
- Geben Sie den zweiten Bereich in Zellen ein D5 zu F11
Das Tutorial enthält nicht die Formatierung, die im Bild angezeigt wird. Dies hat jedoch keinen Einfluss auf die Funktionsweise der Suchformel. Informationen zu den oben gezeigten Formatierungsoptionen finden Sie in diesem grundlegenden Excel-Formatierungs-Lernprogramm.
02 von 08Erstellen Sie eine INDEX-Funktion in Excel
Das INDEX Funktion ist eine der wenigen in Excel, die mehrere Formulare hat. Die Funktion hat eine Array-Formular und ein Referenzformular. Das Array-Formular gibt die tatsächlichen Daten aus einer Datenbank oder einer Datentabelle zurück, während das Referenzformular die Zellreferenz oder den Speicherort der Daten in der Tabelle angibt.
In diesem Lernprogramm verwenden wir das Array-Formular, da wir den Namen des Anbieters für Titan-Widgets und nicht die Zellenreferenz auf diesen Anbieter in unserer Datenbank erfahren möchten. Führen Sie die folgenden Schritte aus, um Ihre INDEX-Funktion zu erstellen:
- Klicken Sie auf Zelle F3 Um es zur aktiven Zelle zu machen - hier werden wir die verschachtelte Funktion eingeben.
- Klicke auf das Formeln Registerkarte des Menübandes.
- Wählen Nachschlagen und Referenz Über die Multifunktionsleiste öffnen Sie die Dropdown-Liste.
- Klicke auf INDEX in der Liste, um das aufzurufen Wählen Sie Argumente aus Dialogbox.
- Wählen Sie das Array, Zeilennummer, Spaltennummer Option im Dialogfeld.
- Klicken OK um das INDEX-Funktionsdialogfeld zu öffnen; Dadurch wird der Formel-Generator in Excel geöffnet.
- In dem Formula Builder, klicke auf das Array Linie.
- Markieren Sie Zellen D6 zu F11durch Klicken und Ziehen im Arbeitsblatt den Bereich in den Builder eingeben.
In älteren Versionen von Excel die Formula Builder wird durch das ersetzt Funktionsargumente Fenster. Wenden Sie die gleichen Schritte wie in diesem Tutorial im Fenster an.
03 von 08Starten Sie die Nested MATCH-Funktion
Beim Verschachteln einer Funktion in einer anderen ist es nicht möglich, den Formel-Generator der zweiten oder verschachtelten Funktion zu öffnen, um die erforderlichen Argumente einzugeben. Die verschachtelte Funktion muss als eins der sortierten der ersten Funktion eingegeben werden.
Bei der manuellen Eingabe von Funktionen werden die Argumente der Funktion durch ein Komma voneinander getrennt.
Der erste Schritt bei der Eingabe der verschachtelten MATCH-Funktion ist die Eingabe der Lookup-Wert Streit. Der Lookup_value-Wert ist der Ort oder die Zellenreferenz für den Suchbegriff, den Sie in der Datenbank abgleichen möchten.
Normalerweise akzeptiert der Lookup_value nur ein Suchkriterium oder einen Begriff. Um nach mehreren Kriterien zu suchen, müssen wir den Lookup_value erweitern; tDies geschieht durch Verketten oder Zusammenfügen von zwei oder mehr Zellbezügen unter Verwendung des Ampersand-Symbols. &.
- In dem Formula Builder, klicke auf das Row_num Linie.
- Geben Sie den Funktionsnamen ein SPIEL gefolgt von einer offenen runden Klammer.
- Klicken Sie auf Zelle D3 um diese Zellenreferenz in das Dialogfeld einzugeben.
- Geben Sie nach der Zellreferenz ein kaufmännisches Und ein D3 um eine zweite Zellreferenz hinzuzufügen.
- Klicken Sie auf Zelle E3 um diese zweite Zellreferenz einzugeben.
- Geben Sie nach der Zellenreferenz ein Komma ein E3 um die Eingabe der Funktion MATCH abzuschließen Lookup-Wert Streit.
Im letzten Schritt des Tutorials finden Sie unsere Nachschlagewerte wird in die Zellen D3 und E3 des Arbeitsblatts eingetragen.
04 von 08Schließen Sie die Nested MATCH-Funktion ab
Dieser Schritt umfasst das Hinzufügen von Lookup_array Argument für die verschachtelten SPIEL Funktion. Lookup_array ist der Zellbereich, den die Funktion MATCH durchsucht, um das Argument Lookup_value zu finden, das im vorherigen Schritt des Lernprogramms hinzugefügt wurde.
Da wir im Argument Lookup_array zwei Suchfelder identifiziert haben, müssen wir dasselbe für Lookup_array ausführen. Die Funktion MATCH durchsucht nur ein Array für jeden angegebenen Begriff. Um mehrere Arrays einzugeben, verwenden wir erneut das kaufmännische Und, um die Arrays miteinander zu verketten.
Diese Schritte sind nach dem Komma einzugeben, das im vorherigen Schritt in der eingegeben wurde Row_num Zeile in der INDEX Funktion.
- Klicke auf das Row_num Zeile nach dem Komma, um die Einfügemarke am Ende des aktuellen Eintrags zu platzieren.
- Markieren Sie Zellen D6 zu D11 Geben Sie im Arbeitsblatt den Bereich ein - dies ist das erste Array, in dem die Funktion suchen soll.
- Geben Sie nach den Zellverweisen ein kaufmännisches Und ein D6: D11 weil wir möchten, dass die Funktion zwei Arrays durchsucht.
- Markieren Sie Zellen E6 zu E11 Geben Sie im Arbeitsblatt den Bereich ein - dies ist das zweite Array, in dem die Funktion suchen soll.
- Geben Sie nach der Zellenreferenz ein Komma ein E3 um die Eingabe der SPIEL Funktion Lookup_array Streit.
- Lassen Sie die INDEX Funktionsdialogfeld für den nächsten Schritt des Lernprogramms geöffnet.
Fügen Sie das Argument MATCH Type hinzu
Das dritte und letzte Argument der Funktion MATCH ist das Match_type Streit; es sagt Excel, wie es mit dem übereinstimmt Lookup-Wert mit Werten im Lookup_array - die verfügbaren Optionen sind 1, 0 oder -1.
Dieses Argument ist optional. Wenn es weggelassen wird, verwendet die Funktion den Standardwert 1.
- Wenn die Match_type = 1 oder wird ausgelassen: MATCH ermittelt den größten Wert, der kleiner oder gleich dem Lookup_value ist. Die Lookup_array-Daten müssen aufsteigend sortiert werden.
- Wenn die Match_type = 0: MATCH findet den ersten Wert, der genau dem Lookup_value entspricht. Die Lookup_array-Daten können in beliebiger Reihenfolge sortiert werden.
- Wenn die Match_type = -1: MATCH findet den kleinsten Wert, der größer oder gleich dem Nachschlagwert ist. Die Lookup_array-Daten müssen in absteigender Reihenfolge sortiert werden.
Diese Schritte sind nach dem Komma einzugeben, das im vorherigen Schritt in der eingegeben wurde Row_num Zeile in der INDEX Funktion.
- Nach dem Komma auf der Row_num Zeile, geben Sie eine Null ein - 0 - da wir möchten, dass die verschachtelte Funktion exakte Übereinstimmungen mit den in Zellen eingegebenen Begriffen zurückgibt D3 und E3.
- Geben Sie eine schließende runde Klammer ein - ) - um das abzuschließen SPIEL Funktion.
- Lassen Sie die Formula Builder offen für den nächsten Schritt des Tutorials.
Beenden der INDEX-Funktion
Nun das das SPIEL Funktion fertig ist, gehen wir in die dritte Zeile des Formula Builder und geben das letzte Argument für den ein INDEX Funktion. Dieses dritte und letzte Argument ist das Column_num Argument, das Excel die Spaltennummer im Bereich angibt D6 zu F11; Hier finden Sie die Informationen, die von der Funktion zurückgegeben werden sollen. In diesem Fall kann ein Lieferant für Titan-Widgets.
- Klicke auf das Column_num Linie.
- Geben Sie die Zahl drei ein - 3 - in dieser Zeile, da wir in der dritten Spalte des Bereichs nach Daten suchen D6 zu F11.
- Wieder verlassen die Formula Builder offen für den nächsten Schritt des Tutorials.
Erstellen der Array-Formel
Vor dem Schließen des Formula BuilderWir müssen aus unserer verschachtelten Funktion eine Array-Formel machen. Dies ermöglicht unserer Funktion die Suche nach mehreren Begriffen in der Datentabelle. In diesem Tutorial möchten wir zwei Begriffe finden: Widgets aus Spalte 1 und Titan aus Spalte 2.
Das Erstellen einer Matrixformel in Excel erfolgt durch Drücken der Taste STRG, VERSCHIEBUNG, und EINGEBEN Tasten auf der Tastatur gleichzeitig. Nach dem Drücken wird die Funktion von geschweiften Klammern umgeben, was darauf hinweist, dass die Funktion jetzt ein Array ist.
- Schließe Formula Builder durch Klicken auf Erledigt Taste.
- Als nächstes wählen Sie die Zelle aus F4 und drücken Sie die Eingeben Taste, um die Formel anzuzeigen.
- Um die Formel in ein Array zu konvertieren, drücken Sie gleichzeitig STRG + UMSCHALT + EINGABETASTE auf deiner Tastatur.
- Wenn richtig gemacht a #N / A Der Fehler wird in Zelle F3 angezeigt - der Zelle, in die wir die Funktion eingegeben haben.
- Das #N / A Fehler erscheint in der Zelle F3 weil zellen D3 und E3 sind leer D3 und E3 sind die Zellen, in denen wir die Funktion angegeben haben, das zu finden Nachschlagewerte. Sobald Daten zu diesen beiden Zellen hinzugefügt wurden, wird der Fehler durch Informationen aus der Datenbank ersetzt.
Suchkriterien hinzufügen
Zum Schluss fügen wir die Suchbegriffe in unser Arbeitsblatt ein. Wie im vorigen Schritt erwähnt, versuchen wir, die Bedingungen zu erfüllen Widgets aus Spalte 1 und Titan aus Spalte 2. Wenn und nur wenn unsere Formel eine Übereinstimmung für beide Begriffe in den entsprechenden Spalten der Datenbank findet, gibt sie den Wert aus der dritten Spalte zurück.
- Klicken Sie auf Zelle D3.
- Art Widgets und drücken Sie die Eingeben Taste auf der Tastatur.
- Klicken Sie auf Zelle E3.
- Art Titan und drücken Sie die Eingeben Taste auf der Tastatur.
- Name des Lieferanten Widgets Inc. sollte in Zelle F3 angezeigt werden - dem Speicherort der Funktion, da es der einzige Anbieter ist, der Titanium-Widgets verkauft.
- Wenn Sie auf Zelle F3 klicken, wird die vollständige Funktion in der Formelleiste über dem Arbeitsblatt angezeigt.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
In unserem Beispiel gab es nur einen Lieferanten für Titan-Widgets. Wenn es mehr als einen Lieferanten gab, wird der in der Datenbank zuerst aufgelistete Lieferant von der Funktion zurückgegeben.
