Home-theater-designers
Die meiste Zeit auf der Suche nach a Microsoft Excel Tabellenkalkulation ist ziemlich einfach. Wenn Sie nicht einfach die Zeilen und Spalten danach durchsuchen können, können Sie Strg + F verwenden, um danach zu suchen. Wenn Sie mit einer wirklich großen Tabellenkalkulation arbeiten, kann es viel Zeit sparen, eine dieser vier Nachschlagefunktionen zu verwenden.
Sobald Sie wissen, wie Sie in Excel mithilfe von Lookup suchen, spielt es keine Rolle, wie groß Ihre Tabellenkalkulationen werden, Sie werden immer etwas in Excel finden können!
1. Die SVERWEIS-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 das keinen Sinn macht, wird es gleich klar). Zwei Beispiele, in denen Sie dies tun können, sind das Suchen des Nachnamens eines Mitarbeiters anhand seiner Mitarbeiternummer oder das Auffinden einer Telefonnummer durch Angabe eines Nachnamens.
Hier ist die Syntax der Funktion:
=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])- [Lookup-Wert] ist die Information, die Sie bereits haben. Wenn Sie beispielsweise wissen möchten, in welchem Bundesland sich eine Stadt befindet, ist dies der Name der Stadt.
- [table_array] können Sie die Zellen angeben, in denen die Funktion nach den Lookup- und Rückgabewerten sucht. Stellen Sie bei der Auswahl Ihres Bereichs sicher, dass die erste Spalte in Ihrem Array diejenige ist, die Ihren Nachschlagewert 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 eingegebenen Wert oder der nächstniedrigeren Zahl. In der Abbildung unten gibt ein SVERWEIS, das nach einem SAT-Score von 652 sucht, 646 zurück, da dies die nächste Zahl in der Liste ist, die kleiner als 652 ist, und [range_lookup] ist standardmäßig auf 1.
Schauen wir uns an, wie Sie dies verwenden können. Diese Tabelle enthält ID-Nummern, Vor- und Nachnamen, Stadt-, Bundesstaat- und SAT-Ergebnisse. Angenommen, Sie möchten den SAT-Score einer Person mit dem Nachnamen 'Winters' ermitteln. SVERWEIS macht es Ihnen leicht. Hier ist die Formel, die Sie verwenden würden:
=VLOOKUP('Winters', C2:F101, 4, 0)Da die SAT-Werte die vierte Spalte nach der Nachnamensspalte sind, ist 4 das Spaltenindexargument. Beachten Sie, dass es eine gute Idee ist, [range_lookup] auf 0 zu setzen, wenn Sie nach Text suchen. Ohne sie können Sie schlechte Ergebnisse erzielen.
Hier ist das Ergebnis:
Es gab 651 zurück, die SAT-Punktzahl, die dem Studenten namens Kennedy Winters gehört, der sich in Zeile 92 befindet (im obigen Einschub angezeigt). Es hätte viel länger gedauert, durch die Suche nach dem Namen zu scrollen, als die Syntax schnell einzugeben!
Hinweise zu SVERWEIS
Bei der Verwendung von SVERWEIS sollten Sie sich einige Dinge merken. Stellen Sie sicher, dass die erste Spalte in Ihrem Bereich den Suchwert enthält. Wenn es nicht in der ersten Spalte steht, gibt die Funktion falsche Ergebnisse zurück. Wenn Ihre Spalten gut organisiert sind, sollte dies kein Problem sein.
Denken Sie auch daran, dass SVERWEIS immer nur einen Wert zurückgibt. Wenn Sie 'Georgia' als Suchwert verwendet hätten, hätte dies die Punktzahl des ersten Schülers aus Georgien zurückgegeben und keinen Hinweis darauf gegeben, dass tatsächlich zwei Schüler aus Georgien vorhanden sind.
2. Die HLOOKUP-Funktion
Wo SVERWEIS entsprechende Werte in einer anderen Spalte findet, findet HVERWEIS entsprechende Werte in einer anderen Zeile. Da es normalerweise am einfachsten ist, die Spaltenüberschriften zu durchsuchen, bis Sie die richtige gefunden haben, und einen Filter zu verwenden, um das Gesuchte zu finden, wird HLOOKUP am besten verwendet, wenn Sie sehr große Tabellenkalkulationen haben oder mit Werten arbeiten, die nach Zeit geordnet sind .
Hier ist die Syntax der Funktion:
=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])- [Lookup-Wert] 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] identisch mit SVERWEIS ist, lassen Sie es leer, um nach Möglichkeit den nächsten Wert zu erhalten, oder geben Sie 0 ein, um nur nach genauen Übereinstimmungen zu suchen.
Diese Tabelle enthält eine Zeile für jedes Bundesland sowie einen SAT-Score für die Jahre 2000–2014. Sie können HLOOKUP verwenden, um die durchschnittliche Punktzahl in Minnesota im Jahr 2013 zu ermitteln. So machen wir es:
=HLOOKUP(2013, A1:P51, 24)Wie Sie im Bild unten sehen können, wird die Punktzahl zurückgegeben:
Die Minnesotaner erreichten im Jahr 2013 eine durchschnittliche Punktzahl von 1014. Beachten Sie, dass 2013 nicht in Anführungszeichen steht, da es sich um eine Zahl und nicht um eine Zeichenfolge handelt. Außerdem kommt die 24 aus Minnesota, die in der 24. Reihe steht.
Hier ist So berechnen Sie den gewichteten Durchschnitt in Excel .
Hinweise zu HLOOKUP
Wie bei SVERWEIS 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 Nachschlagewert 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 das Durchsuchen einer großen Tabelle viel schneller machen. 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])- [Reihe] ist das Array, in dem Sie suchen.
- [Zeilennummer] und [Spaltennummer] kann verwendet werden, um Ihre Suche einzugrenzen (wir werden uns das gleich ansehen.)
=MATCH([lookup_value], [lookup_array], [match_type])- [Lookup-Wert] 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 genauen Begriff zurück und -1 gibt den kleinsten Wert zurück, der größer oder gleich Ihrem Suchbegriff ist.
Es ist vielleicht nicht klar, wie wir diese beiden Funktionen zusammen verwenden werden, also werde ich es hier darlegen. MATCH nimmt einen Suchbegriff und gibt einen Zellbezug 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 darin enthaltenen Wert zurück. Sie können hier sehen, dass INDEX 'Anchorage', den Wert aus Zeile 6, zurückgibt, wenn er aufgefordert wird, die sechste Zelle der Spalte 'City' zurückzugeben.
Was wir tun werden, ist die beiden zu kombinieren, sodass MATCH eine Zellreferenz zurückgibt und INDEX diese Referenz verwendet, um den Wert in einer Zelle zu suchen. Angenommen, Sie erinnern sich, dass ein Schüler mit Nachname Waters war, und Sie möchten die Punktzahl dieses Schülers sehen. Hier ist die Formel, die wir verwenden werden:
So verbergen Sie Ihre Nummer beim Anrufen
=INDEX(F:F, MATCH('Waters', C:C, 0))Sie werden feststellen, dass der Übereinstimmungstyp hier auf 0 gesetzt ist. Wenn Sie nach einer Saite suchen, sollten Sie diese verwenden. Folgendes erhalten wir, wenn wir diese Funktion ausführen:
Wie Sie im Einschub sehen können, hat Owen Waters 1720 Punkte erzielt, die Zahl, die angezeigt wird, wenn wir die Funktion ausführen. Dies mag nicht allzu nützlich erscheinen, wenn Sie sich nur ein paar Spalten ansehen können, aber stellen Sie sich vor, wie viel Zeit Sie sparen würden, wenn Sie dies 50 Mal an einem tun müssten große Datenbanktabelle die mehrere hundert Spalten enthielt!
Lassen Sie die Excel-Suchen beginnen
Microsoft Excel hat viele extrem leistungsstarke Funktionen zum Manipulieren von Daten, und die vier oben aufgeführten kratzen nur an der Oberfläche. Zu lernen, wie man sie benutzt, wird Ihr Leben viel einfacher machen.
Wenn Sie Microsoft Excel wirklich beherrschen möchten, können Sie wirklich davon profitieren, das Essential Excel Cheat Sheet griffbereit zu haben!
Bildquelle: Cico/ Shutterstock
Teilen Teilen Tweet Email Canon vs. Nikon: Welche Kameramarke ist besser?Canon und Nikon sind die beiden größten Namen in der Kamerabranche. Aber welche Marke bietet die bessere Auswahl an Kameras und Objektiven?
Weiter lesen Verwandte Themen- Produktivität
- Kalkulationstabelle
- Microsoft Excel
- Suchtricks
Ian Buckley lebt als freiberuflicher Journalist, Musiker, Performer und Videoproduzent in Berlin. Wenn er nicht gerade schreibt oder auf der Bühne steht, bastelt er an DIY-Elektronik oder Code, in der Hoffnung, ein verrückter Wissenschaftler zu werden.
Mehr von Ian BuckleyAbonniere unseren Newsletter
Abonnieren Sie unseren Newsletter für technische Tipps, Rezensionen, kostenlose E-Books und exklusive Angebote!
Klicken Sie hier, um zu abonnieren