Excel-Datenanalyse - Suchfunktionen

Sie können Excel-Funktionen verwenden, um -

• Suchen Sie Werte in einer Reihe von Daten - VLOOKUP und HLOOKUP
• Erhalten Sie einen Wert oder den Verweis auf einen Wert aus einer Tabelle oder einem Bereich - INDEX
• Ermitteln Sie die relative Position eines bestimmten Elements in einem Zellbereich - MATCH

Sie können diese Funktionen auch kombinieren, um die erforderlichen Ergebnisse basierend auf Ihren Eingaben zu erhalten.

Verwenden der VLOOKUP-Funktion

Die Syntax der VLOOKUP-Funktion lautet

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Wo

• lookup_value- ist der Wert, den Sie nachschlagen möchten. Lookup_value kann ein Wert oder eine Referenz auf eine Zelle sein. Lookup_value muss sich in der ersten Spalte des in table_array angegebenen Zellenbereichs befinden

• table_array- ist der Zellenbereich, in dem der VLOOKUP nach dem Lookup_Wert und dem Rückgabewert sucht. table_array muss enthalten

  • der lookup_value in der ersten Spalte und

  • den Rückgabewert, den Sie suchen möchten

    Note- Die erste Spalte mit dem Lookup_Wert kann entweder in aufsteigender Reihenfolge sortiert werden oder nicht. Das Ergebnis basiert jedoch auf der Reihenfolge dieser Spalte.

• col_index_num- ist die Spaltennummer im table_array, die den Rückgabewert enthält. Die Zahlen beginnen mit 1 für die am weitesten links stehende Spalte des Tabellenarrays

• range_lookup- ist ein optionaler logischer Wert, der angibt, ob VLOOKUP eine genaue oder eine ungefähre Übereinstimmung finden soll. range_lookup kann sein

  • weggelassen. In diesem Fall wird angenommen, dass es WAHR ist, und VLOOKUP versucht, eine ungefähre Übereinstimmung zu finden

  • TRUE. In diesem Fall versucht VLOOKUP, eine ungefähre Übereinstimmung zu finden. Mit anderen Worten, wenn keine genaue Übereinstimmung gefunden wird, wird der nächstgrößere Wert zurückgegeben, der kleiner als lookup_value ist

  • FALSE. In diesem Fall versucht VLOOKUP, eine genaue Übereinstimmung zu finden

  • In diesem Fall wird angenommen, dass es WAHR ist, und VLOOKUP versucht, eine ungefähre Übereinstimmung zu finden

  • 0, in diesem Fall wird angenommen, dass es FALSE ist, und VLOOKUP versucht, eine genaue Übereinstimmung zu finden

Note- Wenn range_lookup weggelassen wird oder TRUE oder 1, funktioniert VLOOKUP nur dann korrekt, wenn die erste Spalte in table_array in aufsteigender Reihenfolge sortiert ist. Andernfalls kann es zu falschen Werten kommen. Verwenden Sie in diesem Fall FALSE für range_lookup.

Verwenden der VLOOKUP-Funktion mit range_lookup TRUE

Betrachten Sie eine Liste der Noten von Schülern. Sie können die entsprechenden Noten mit VLOOKUP aus einem Array erhalten, das die Markierungsintervalle und die Bestehenskategorie enthält.

table_array -

Beachten Sie, dass die Markierungen der ersten Spalte, auf deren Grundlage die Noten erhalten werden, in aufsteigender Reihenfolge sortiert sind. Wenn Sie also TRUE für das Argument range_lookup verwenden, können Sie eine ungefähre Übereinstimmung erhalten, die erforderlich ist.

Nennen Sie dieses Array als Grades.

Es wird empfohlen, Arrays auf diese Weise zu benennen, damit Sie sich nicht an die Zellbereiche erinnern müssen. Jetzt können Sie die Note für die Liste der Noten nachschlagen, die Sie wie folgt haben:

Wie Sie beobachten können,

• col_index_num - gibt an, dass die Spalte des Rückgabewerts in table_array 2 ist

• das range_lookup ist wahr

  • Die erste Spalte mit dem Suchwert in den Tabellen table_array ist in aufsteigender Reihenfolge. Daher sind die Ergebnisse korrekt.

  • Sie können den Rückgabewert auch für ungefähre Übereinstimmungen abrufen. dh VLOOKUP berechnet wie folgt:

Markierungen	Pass-Kategorie
<35	Scheitern
> = 35 und <50	Dritte Klasse
> = 50 und <60	Zweite Klasse
> = 60 und <75	Erste Klasse
> = 75	Erste Klasse mit Auszeichnung

Sie erhalten folgende Ergebnisse -

Verwenden der VLOOKUP-Funktion mit range_lookup FALSE

Betrachten Sie eine Liste von Produkten, die die Produkt-ID und den Preis für jedes der Produkte enthält. Die Produkt-ID und der Preis werden bei jeder Einführung eines neuen Produkts am Ende der Liste hinzugefügt. Dies würde bedeuten, dass die Produkt-IDs nicht in aufsteigender Reihenfolge sein müssen. Die Produktliste könnte wie folgt aussehen:

table_array -

Nennen Sie dieses Array ProductInfo.

Sie können den Preis eines Produkts unter Angabe der Produkt-ID mit der Funktion VLOOKUP abrufen, da sich die Produkt-ID in der ersten Spalte befindet. Der Preis steht in Spalte 3 und daher sollte col_index_ num 3 sein.

• Verwenden Sie die VLOOKUP-Funktion mit range_lookup als TRUE
• Verwenden Sie die VLOOKUP-Funktion mit range_lookup als FALSE

Die richtige Antwort aus dem ProductInfo-Array lautet 171,65. Sie können die Ergebnisse überprüfen.

Sie beobachten, dass Sie -

• Das richtige Ergebnis, wenn range_lookup FALSE ist, und
• Ein falsches Ergebnis, wenn range_lookup TRUE ist.

Dies liegt daran, dass die erste Spalte im ProductInfo-Array nicht in aufsteigender Reihenfolge sortiert ist. Denken Sie daher daran, FALSE zu verwenden, wenn die Daten nicht sortiert sind.

Verwenden der HLOOKUP-Funktion

Sie können verwenden HLOOKUP Funktion, wenn sich die Daten eher in Zeilen als in Spalten befinden.

Beispiel

Nehmen wir das Beispiel von Produktinformationen. Angenommen, das Array sieht wie folgt aus:

• Nennen Sie diesen Array ProductRange. Sie können den Preis eines Produkts anhand der Produkt-ID mit der HLOOKUP-Funktion ermitteln.

Die Syntax der HLOOKUP-Funktion lautet

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Wo

• lookup_value - ist der Wert in der ersten Zeile der Tabelle

• table_array - ist eine Informationstabelle, in der Daten nachgeschlagen werden

• row_index_num - ist die Zeilennummer in table_array, von der der übereinstimmende Wert zurückgegeben wird

• range_lookup - ist ein logischer Wert, der angibt, ob HLOOKUP eine genaue oder eine ungefähre Übereinstimmung finden soll

• range_lookup kann sein

  • weggelassen, in diesem Fall wird angenommen, dass es WAHR ist, und HLOOKUP versucht, eine ungefähre Übereinstimmung zu finden

  • TRUE. In diesem Fall versucht HLOOKUP, eine ungefähre Übereinstimmung zu finden. Mit anderen Worten, wenn keine genaue Übereinstimmung gefunden wird, wird der nächstgrößere Wert zurückgegeben, der kleiner als lookup_value ist

  • FALSE. In diesem Fall versucht HLOOKUP, eine genaue Übereinstimmung zu finden

  • In diesem Fall wird angenommen, dass es WAHR ist, und HLOOKUP versucht, eine ungefähre Übereinstimmung zu finden

  • 0, in diesem Fall wird angenommen, dass es FALSE ist, und HLOOKUP versucht, eine genaue Übereinstimmung zu finden

Note- Wenn range_lookup weggelassen oder TRUE oder 1 ist, funktioniert HLOOKUP nur dann ordnungsgemäß, wenn die erste Spalte in table_array in aufsteigender Reihenfolge sortiert ist. Andernfalls kann es zu falschen Werten kommen. Verwenden Sie in diesem Fall FALSE für range_lookup.

Verwenden der HLOOKUP-Funktion mit range_lookup FALSE

Sie können den Preis eines Produkts anhand der Produkt-ID mit der HLOOKUP-Funktion ermitteln, da sich die Produkt-ID in der ersten Zeile befindet. Der Preis ist in Zeile 3 und daher sollte row_index_ num 3 sein.

• Verwenden Sie die HLOOKUP-Funktion mit range_lookup als TRUE.
• Verwenden Sie die HLOOKUP-Funktion mit range_lookup als FALSE.

Die richtige Antwort aus dem ProductRange-Array lautet 171,65. Sie können die Ergebnisse überprüfen.

Sie beobachten, dass Sie wie im Fall von VLOOKUP haben

• Das richtige Ergebnis, wenn range_lookup FALSE ist, und

• Ein falsches Ergebnis, wenn range_lookup TRUE ist.

Dies liegt daran, dass die erste Zeile im ProductRange-Array nicht in aufsteigender Reihenfolge sortiert ist. Denken Sie daher daran, FALSE zu verwenden, wenn die Daten nicht sortiert sind.

Verwenden der HLOOKUP-Funktion mit range_lookup TRUE

Betrachten Sie das Beispiel der in VLOOKUP verwendeten Schülerzeichen. Angenommen, Sie haben die Daten in Zeilen anstelle von Spalten, wie in der folgenden Tabelle gezeigt -

table_array -

Benennen Sie dieses Array als GradesRange.

Beachten Sie, dass die Markierungen der ersten Zeile, auf deren Grundlage die Noten erhalten werden, in aufsteigender Reihenfolge sortiert sind. Wenn Sie also HLOOKUP mit TRUE für das Argument range_lookup verwenden, können Sie die Noten mit ungefährer Übereinstimmung erhalten, und das ist erforderlich.

Wie Sie beobachten können,

• row_index_num - gibt an, dass die Spalte des Rückgabewerts in table_array 2 ist

• das range_lookup ist wahr

  • Die erste Spalte mit dem Suchwert in den table_array-Noten ist in aufsteigender Reihenfolge. Daher sind die Ergebnisse korrekt.

  • Sie können den Rückgabewert auch für ungefähre Übereinstimmungen abrufen. dh HLOOKUP berechnet wie folgt:

Markierungen	<35	> = 35 und <50	> = 50 und <60	> = 60 und <75	> = 75
Pass-Kategorie	Scheitern	Dritte Klasse	Zweite Klasse	Erste Klasse	Erste Klasse mit Auszeichnung

Sie erhalten folgende Ergebnisse -

Verwenden der INDEX-Funktion

Wenn Sie über ein Datenarray verfügen, können Sie einen Wert im Array abrufen, indem Sie die Zeilennummer und Spaltennummer dieses Werts im Array angeben.

Berücksichtigen Sie die folgenden Verkaufsdaten, in denen Sie die Verkäufe der aufgelisteten Verkäufer in den Regionen Nord, Süd, Ost und West finden.

• Benennen Sie das Array als SalesData.

Mit der INDEX-Funktion können Sie finden:

• Der Verkauf eines Verkäufers in einer bestimmten Region.
• Gesamtumsatz in einer Region durch alle Verkäufer.
• Gesamtumsatz eines Verkäufers in allen Regionen.

Sie erhalten folgende Ergebnisse -

Angenommen, Sie kennen die Zeilennummern für die Verkäufer und die Spaltennummern für die Regionen nicht. Anschließend müssen Sie zuerst die Zeilennummer und die Spaltennummer ermitteln, bevor Sie den Wert mit der Indexfunktion abrufen können.

Sie können dies mit der MATCH-Funktion tun, wie im nächsten Abschnitt erläutert.

Verwenden der MATCH-Funktion

Wenn Sie die Position eines Elements in einem Bereich benötigen, können Sie die MATCH-Funktion verwenden. Sie können MATCH- und INDEX-Funktionen wie folgt kombinieren:

Sie erhalten folgende Ergebnisse -