4 Excel-Suchfunktionen zum effizienten Durchsuchen von Arbeitsblättern

• Produktivität

Werbung

Meistens ist das Durchsuchen einer Microsoft Excel-Tabelle ziemlich einfach. Wenn Sie nicht einfach die Zeilen und Spalten danach durchsuchen können, können Sie mit Strg + F danach suchen. Wenn Sie mit einer sehr großen Tabelle arbeiten, kann die Verwendung einer dieser vier Suchfunktionen viel Zeit sparen.

Schalte jetzt den Spickzettel "Essential Excel Formulas" frei!

Hiermit melden Sie sich für unseren Newsletter an

Sobald Sie wissen, wie Sie mit Hilfe der Suche in Excel suchen, spielt es keine Rolle, wie groß Ihre Tabellenkalkulationen werden, Sie können immer etwas in Excel finden!

1. Die VLOOKUP-Funktion

Mit dieser Funktion können Sie eine Spalte und einen Wert angeben und einen Wert aus der entsprechenden Zeile einer anderen Spalte zurückgeben (wenn dies keinen Sinn ergibt, wird dies sofort klar). Zwei Beispiele, bei denen Sie dies tun könnten, sind das Nachschlagen des Nachnamens eines Mitarbeiters anhand seiner Mitarbeiternummer oder das Suchen einer Telefonnummer anhand eines Nachnamens.

Hier ist die Syntax der Funktion:

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

• [lookup_value] ist die Information, über die Sie bereits verfügen. Wenn Sie beispielsweise wissen möchten, in welchem ​​Bundesstaat sich eine Stadt befindet, ist dies der Name der Stadt.
• Mit [table_array] können Sie die Zellen angeben, in denen die Funktion nach den Such- und Rückgabewerten sucht. Achten Sie bei der Auswahl Ihres Bereichs darauf, dass die erste in Ihrem Array enthaltene Spalte Ihren Suchwert enthält!
• [col_index_num] ist die Nummer der Spalte, die den Rückgabewert enthält.
• [range_lookup] ist ein optionales Argument und nimmt 1 oder 0 an. Wenn Sie 1 eingeben oder dieses Argument weglassen, sucht die Funktion nach dem von Ihnen eingegebenen Wert oder der nächstniedrigeren Zahl. In der folgenden Abbildung gibt ein VLOOKUP, der nach einem SAT-Wert von 652 sucht, 646 zurück, da dies die nächstliegende Zahl in der Liste ist, die kleiner als 652 ist, und [range_lookup] ist standardmäßig 1.

Lassen Sie uns einen Blick darauf werfen, wie Sie dies verwenden könnten. Diese Tabelle enthält ID-Nummern, Vor- und Nachnamen, Stadt, Bundesland und SAT-Ergebnisse. Angenommen, Sie möchten die SAT-Punktzahl einer Person mit dem Nachnamen „Winters“ ermitteln. VLOOKUP vereinfacht dies. Hier ist die Formel, die Sie verwenden würden:

 =VLOOKUP("Winters", C2:F101, 4, 0) 

Da die SAT-Werte die vierte Spalte über der Nachname-Spalte sind, ist 4 das Argument für den Spaltenindex. Beachten Sie, dass es eine gute Idee ist, [range_lookup] auf 0 zu setzen, wenn Sie nach Text suchen. Ohne das können Sie schlechte Ergebnisse erzielen.

Hier ist das Ergebnis:

Es gab 651 zurück, die SAT-Punktzahl des Schülers namens Kennedy Winters, der sich in Zeile 92 befindet (siehe Abbildung oben). Es hätte viel länger gedauert, durch den Namen zu scrollen, als die Syntax schnell einzugeben!

Hinweise zu VLOOKUP

Ein paar Dinge sollten Sie beachten, wenn Sie VLOOKUP verwenden. Stellen Sie sicher, dass die erste Spalte in Ihrem Bereich Ihren Suchwert enthält. Wenn es nicht in der ersten Spalte ist, gibt die Funktion falsche Ergebnisse zurück. Wenn Ihre Kolumnen gut organisiert sind, sollte dies kein Problem sein.

Beachten Sie auch, dass VLOOKUP immer nur einen Wert zurückgibt. Wenn Sie "Georgia" als Nachschlagewert verwendet hätten, hätte dies die Punktzahl des ersten Schülers aus Georgia zurückgegeben und keinen Hinweis darauf gegeben, dass es tatsächlich zwei Schüler aus Georgia gibt.

2. Die HLOOKUP-Funktion

Wo VLOOKUP entsprechende Werte in einer anderen Spalte findet, findet HLOOKUP entsprechende Werte in einer anderen Zeile. Da es in der Regel am einfachsten ist, die Spaltenüberschriften zu durchsuchen, bis Sie die richtige gefunden haben, und mithilfe eines Filters zu finden, wonach Sie suchen, empfiehlt sich HLOOKUP, wenn Sie über sehr große Tabellen verfügen oder mit nach Zeit geordneten Werten arbeiten .

Hier ist die Syntax der Funktion:

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

• [lookup_value] ist der Wert, den Sie kennen und für den Sie einen entsprechenden Wert finden möchten.
• [table_array] sind die Zellen, in denen Sie suchen möchten.
• [row_index_num] gibt die Zeile an, aus der der Rückgabewert stammt.
• [range_lookup] ist dasselbe wie in VLOOKUP. Lassen Sie dieses Feld leer, um den nächstgelegenen Wert zu erhalten, oder geben Sie 0 ein, um nur nach genauen Übereinstimmungen zu suchen.

Diese Tabelle enthält eine Zeile für jeden Bundesstaat sowie eine SAT-Bewertung für die Jahre 2000 bis 2014. Sie können HLOOKUP verwenden, um die durchschnittliche Punktzahl in Minnesota im Jahr 2013 zu ermitteln. So wird es gemacht:

 =HLOOKUP(2013, A1:P51, 24) 

Wie Sie im Bild unten sehen können, wird die Punktzahl zurückgegeben:

Minnesotaner hatten 2013 einen Durchschnittswert von 1014. Beachten Sie, dass 2013 nicht in Anführungszeichen steht, da es sich um eine Zahl und keine Zeichenfolge handelt. Auch die 24 kommt aus Minnesota und befindet sich in der 24. Reihe.

Hinweise zu HLOOKUP

Wie bei VLOOKUP muss sich der Nachschlagewert in der ersten Zeile Ihres Tabellenarrays befinden. Dies ist bei HLOOKUP selten ein Problem, da Sie normalerweise einen Spaltentitel für einen Suchwert verwenden. HLOOKUP gibt auch nur einen einzelnen Wert zurück.

3-4. Die INDEX- und MATCH-Funktionen

INDEX und MATCH sind zwei verschiedene Funktionen, aber wenn sie zusammen verwendet werden, können sie die Suche in einer großen Tabelle erheblich beschleunigen. Beide Funktionen haben Nachteile, aber wenn wir sie kombinieren, bauen wir auf den Stärken beider auf.

Zunächst jedoch die Syntax beider Funktionen:

 =INDEX([array], [row_number], [column_number]) 

• [array] ist das Array, in dem Sie suchen.
• Mit [row_number] und [column_number] können Sie Ihre Suche eingrenzen (wir werden uns das gleich ansehen.)

 =MATCH([lookup_value], [lookup_array], [match_type]) 

• [Nachschlagewert] ist ein Suchbegriff, der eine Zeichenfolge oder eine Zahl sein kann.
• [lookup_array] ist das Array, in dem Microsoft Excel nach dem Suchbegriff sucht.
• [match_type] ist ein optionales Argument, das 1, 0 oder -1 sein kann. 1 gibt den größten Wert zurück, der kleiner oder gleich Ihrem Suchbegriff ist. 0 gibt nur Ihren exakten Begriff zurück, und -1 gibt den kleinsten Wert zurück, der größer oder gleich Ihrem Suchbegriff ist.

Es ist möglicherweise nicht klar, wie wir diese beiden Funktionen zusammen verwenden werden, daher werde ich es hier erläutern. MATCH nimmt einen Suchbegriff und gibt eine Zellreferenz zurück. In der Abbildung unten sehen Sie, dass MATCH bei einer Suche nach dem Wert 646 in Spalte F 4 zurückgibt.

INDEX hingegen macht das Gegenteil: Es nimmt eine Zellreferenz und gibt den Wert darin zurück. Hier sehen Sie, dass INDEX, wenn Sie aufgefordert werden, die sechste Zelle der Spalte City zurückzugeben, den Wert "Anchorage" aus Zeile 6 zurückgibt.

Was wir tun werden, ist die beiden zu kombinieren, so dass MATCH einen Zellverweis zurückgibt und INDEX diesen Verweis verwendet, um den Wert in einer Zelle nachzuschlagen. Angenommen, Sie erinnern sich, dass es einen Schüler gab, dessen Nachname Waters war, und Sie möchten sehen, wie hoch die Punktzahl dieses Schülers war. Hier ist die Formel, die wir verwenden:

 =INDEX(F:F, MATCH("Waters", C:C, 0)) 

Sie werden feststellen, dass der Übereinstimmungstyp hier auf 0 gesetzt ist. Wenn Sie nach einer Zeichenfolge suchen, sollten Sie diese verwenden. Folgendes erhalten wir, wenn wir diese Funktion ausführen:

Wie Sie dem Einschub entnehmen können, erzielte Owen Waters 1720, die Zahl, die angezeigt wird, wenn wir die Funktion ausführen. Dies scheint nicht besonders nützlich zu sein, wenn Sie sich nur ein paar Spalten ansehen können. Stellen Sie sich jedoch vor, wie viel Zeit Sie sparen würden, wenn Sie dies 50 Mal in einer großen Excel-Datenbank-Tabelle tun müssten. Access - Kann eine Tabellenkalkulation eine Datenbank ersetzen? Excel Vs. Access - Kann eine Tabellenkalkulation eine Datenbank ersetzen? Welches Tool sollten Sie zum Verwalten von Daten verwenden? In Access und Excel können Daten gefiltert, sortiert und abgefragt werden. Wir zeigen Ihnen, welches für Ihre Bedürfnisse am besten geeignet ist. Lesen Sie mehr, die mehrere hundert Spalten enthielten!

Lassen Sie die Excel-Suche beginnen

Microsoft Excel verfügt über eine Reihe äußerst leistungsfähiger Funktionen zum Bearbeiten von Daten. Wenn Sie lernen, wie man sie benutzt, wird Ihnen das Leben viel leichter fallen.

Wenn Sie Microsoft Excel wirklich beherrschen möchten, können Sie wirklich davon profitieren, dass Sie das Cheat Sheet für Essential Excel beibehalten. Das Cheat Sheet für Essential Microsoft Excel-Formeln und -Funktionen. Das Cheat Sheet für Essential Microsoft Excel-Formeln und -Funktionen weltweit beliebtestes Tabellenkalkulationsprogramm. Lesen Sie mehr in unmittelbarer Nähe!

Bildnachweis: Cico / Shutterstock

Erfahren Sie mehr über: Microsoft Excel, Suchtricks, Tabellenkalkulation.