Alle Geheimnisse der Excel-SVERWEIS-Funktion, um Daten in einer Tabelle zu finden und in eine andere zu extrahieren

2024 Autor: Malcolm Clapton | [email protected]. Zuletzt bearbeitet: 2023-12-17 03:51

Nach dem Lesen des Artikels erfahren Sie nicht nur, wie Sie Daten in einer Excel-Tabelle finden und in eine andere extrahieren, sondern auch Techniken, die in Verbindung mit der SVERWEIS-Funktion verwendet werden können.

Bei der Arbeit in Excel besteht sehr oft die Notwendigkeit, Daten in einer Tabelle zu finden und in eine andere zu extrahieren. Wenn Sie immer noch nicht wissen, wie das geht, erfahren Sie nach dem Lesen des Artikels nicht nur, wie es geht, sondern erfahren auch, unter welchen Bedingungen Sie die maximale Leistung aus dem System herausholen können. Die meisten der sehr effektiven Techniken, die in Verbindung mit der SVERWEIS-Funktion verwendet werden sollten, werden berücksichtigt.

Auch wenn Sie die SVERWEIS-Funktion seit Jahren nutzen, wird Ihnen dieser Artikel mit hoher Wahrscheinlichkeit nützlich sein und Sie nicht gleichgültig lassen. Als IT-Spezialist und später IT-Chef nutze ich beispielsweise SVERWEIS seit 15 Jahren, komme aber erst jetzt mit allen Nuancen klar, als ich anfing, den Leuten professionell Excel beizubringen.

SVERWEIS ist eine Abkürzung für vvertikal NSInspektion. Ebenso SVERWEIS - Vertikaler LOOKUP. Schon der Name der Funktion weist darauf hin, dass sie in den Zeilen der Tabelle sucht (vertikal - über die Zeilen iterieren und die Spalte fixieren) und nicht in den Spalten (horizontal - über die Spalten iterieren und die Zeile fixieren). Es sollte beachtet werden, dass SVERWEIS eine Schwester hat - ein hässliches Entlein, das nie ein Schwan wird - dies ist die HVERWEIS-Funktion. HLOOKUP führt im Gegensatz zu SVERWEIS horizontale Suchen durch, aber das Konzept von Excel (und tatsächlich das Konzept der Datenorganisation) impliziert, dass Ihre Tabellen weniger Spalten und viel mehr Zeilen haben. Aus diesem Grund müssen wir viel häufiger nach Zeilen suchen als nach Spalten. Wenn Sie die HLO-Funktion in Excel zu oft verwenden, haben Sie wahrscheinlich in diesem Leben etwas nicht verstanden.

Syntax

Die SVERWEIS-Funktion hat vier Parameter:

= SVERWEIS (;; [;]), Hier:

- der gewünschte Wert (selten) oder ein Verweis auf eine Zelle, die den gewünschten Wert enthält (in den allermeisten Fällen);

- Verweis auf einen Zellbereich (zweidimensionales Array), in dessen ERSTER (!) Spalte der Parameterwert gesucht wird;

- Spaltennummer im Bereich, aus dem der Wert zurückgegeben wird;

- Dies ist ein sehr wichtiger Parameter, der die Frage beantwortet, ob die erste Spalte des Bereichs aufsteigend sortiert ist. Wenn das Array sortiert ist, geben wir den Wert TRUE oder 1 an, ansonsten - FALSE oder 0. Wenn dieser Parameter weggelassen wird, wird standardmäßig 1 verwendet.

Ich wette, dass viele von denen, die die SVERWEIS-Funktion als flockig kennen, sich nach dem Lesen der Beschreibung des vierten Parameters möglicherweise unwohl fühlen, da sie es gewohnt sind, sie in einer etwas anderen Form zu sehen: Normalerweise sprechen sie von einer genauen Übereinstimmung, wenn Suche (FALSE oder 0) oder ein Bereichsscan (TRUE oder 1).

Jetzt müssen Sie sich anstrengen und den nächsten Absatz mehrmals lesen, bis Sie die Bedeutung des Gesagten bis zum Ende spüren. Da ist jedes Wort wichtig. Beispiele helfen Ihnen, es herauszufinden.

Wie genau funktioniert die SVERWEIS-Formel?

• Formeltyp I. Wenn der letzte Parameter weggelassen oder gleich 1 angegeben wird, geht SVERWEIS davon aus, dass die erste Spalte in aufsteigender Reihenfolge sortiert ist, sodass die Suche bei der Zeile endet, die unmittelbar vor der Zeile, die den Wert enthält, der größer als der gewünschte ist … Wenn keine solche Zeichenfolge gefunden wird, wird die letzte Zeile des Bereichs zurückgegeben.

  

• Formel II. Wenn der letzte Parameter als 0 angegeben ist, scannt der SVERWEIS sequentiell die erste Spalte des Arrays und stoppt die Suche sofort, wenn die erste genaue Übereinstimmung mit dem Parameter gefunden wird, andernfalls der # N / A (# N / A) Fehlercode ist zurück gekommen.

  

Formel-Workflows

VPR-Typ I

VPR-Typ II

Folgerungen für Formeln der Form I

1. Formeln können verwendet werden, um Werte über Bereiche zu verteilen.
2. Wenn die erste Spalte doppelte Werte enthält und richtig sortiert ist, wird die letzte der Zeilen mit doppelten Werten zurückgegeben.
3. Wenn Sie nach einem Wert suchen, der offensichtlich größer ist, als die erste Spalte enthalten kann, können Sie leicht die letzte Zeile der Tabelle finden, die sehr wertvoll sein kann.
4. Diese Ansicht gibt nur dann den Fehler # N / A zurück, wenn kein Wert gefunden wird, der kleiner oder gleich dem gewünschten ist.
5. Es ist ziemlich schwer zu verstehen, dass die Formel die falschen Werte zurückgibt, wenn Ihr Array nicht sortiert ist.

Folgerungen für Formeln vom Typ II

Wenn der gewünschte Wert mehrmals in der ersten Spalte des Arrays vorkommt, wählt die Formel die erste Zeile für den nachfolgenden Datenabruf aus.

SVERWEIS-Leistung

Sie haben den Höhepunkt des Artikels erreicht. Es scheint, was ist der Unterschied, wenn ich als letzten Parameter null oder eins angebe? Grundsätzlich gibt natürlich jeder Null an, denn das ist ganz praktisch: Sie müssen sich nicht um das Sortieren der ersten Spalte des Arrays kümmern, Sie sehen sofort, ob der Wert gefunden wurde oder nicht. Wenn Sie jedoch mehrere tausend SVERWEIS-Formeln auf Ihrem Blatt haben, werden Sie feststellen, dass SVERWEIS II langsam ist. Zur gleichen Zeit beginnt normalerweise jeder zu denken:

• Ich brauche einen leistungsfähigeren Computer;
• Ich brauche zum Beispiel eine schnellere Formel, die viele Leute von INDEX + MATCH kennen, die angeblich um mickrige 5-10% schneller ist.

Und nur wenige Leute denken, dass die Geschwindigkeit von SVERWEIS um das 57-fache erhöht wird, sobald Sie SVERWEIS vom Typ I verwenden und sicherstellen, dass die erste Spalte auf irgendeine Weise sortiert ist. Ich schreibe in Worten – siebenundfünfzig Mal! Nicht 57 %, sondern 5.700 %. Diesen Umstand habe ich recht zuverlässig überprüft.

Das Geheimnis dieser schnellen Arbeit liegt darin, dass auf ein sortiertes Array ein äußerst effizienter Suchalgorithmus angewendet werden kann, der als binäre Suche bezeichnet wird (Halbierungsmethode, Dichotomiemethode). SVERWEIS vom Typ I wendet es also an, und SVERWEIS vom Typ II sucht ohne jegliche Optimierung. Dasselbe gilt für die MATCH-Funktion, die einen ähnlichen Parameter enthält, und die LOOKUP-Funktion, die nur bei sortierten Arrays funktioniert und aus Gründen der Kompatibilität mit Lotus 1-2-3 in Excel enthalten ist.

Nachteile der Formel

Die Nachteile von SVERWEIS liegen auf der Hand: Erstens sucht es nur in der ersten Spalte des angegebenen Arrays und zweitens nur rechts von dieser Spalte. Und wie Sie verstehen, kann es durchaus vorkommen, dass sich die Spalte mit den erforderlichen Informationen links von der Spalte befindet, in der wir suchen. Die bereits erwähnte Formelkombination INDEX + MATCH weist diesen Nachteil nicht auf, was sie im Vergleich zu SVERWEIS (SVERWEIS) zur flexibelsten Lösung zur Extraktion von Daten aus Tabellen macht.

Einige Aspekte der Anwendung der Formel im wirklichen Leben

Bereichssuche

Ein klassisches Beispiel für eine Sortimentssuche ist die Aufgabe, einen Rabatt nach Bestellgröße zu ermitteln.

Suche nach Textzeichenfolgen

Natürlich sucht SVERWEIS nicht nur nach Zahlen, sondern auch nach Text. Es ist zu beachten, dass die Formel nicht zwischen der Groß-/Kleinschreibung unterscheidet. Wenn Sie Platzhalter verwenden, können Sie eine unscharfe Suche organisieren. Es gibt zwei Platzhalter: "?" - ersetzt ein beliebiges Zeichen in einer Textzeichenfolge, "*" - ersetzt eine beliebige Anzahl beliebiger Zeichen.

Kampfräume

Es wird oft die Frage aufgeworfen, wie das Problem der zusätzlichen Leerzeichen bei der Suche gelöst werden kann. Wenn die Nachschlagetabelle immer noch davon gelöscht werden kann, liegt der erste Parameter der SVERWEIS-Formel nicht immer bei Ihnen. Wenn daher die Gefahr besteht, die Zellen mit zusätzlichen Leerzeichen zu verstopfen, können Sie dies mit der TRIM-Funktion löschen.

Unterschiedliches Datenformat

Wenn der erste Parameter der SVERWEIS-Funktion auf eine Zelle verweist, die eine Zahl enthält, die jedoch als Text in der Zelle gespeichert ist, und die erste Spalte des Arrays Zahlen im richtigen Format enthält, schlägt die Suche fehl. Auch die umgekehrte Situation ist möglich. Das Problem kann leicht gelöst werden, indem Parameter 1 in das erforderliche Format übersetzt wird:

= SVERWEIS (−− D7; Produkte! $ A $ 2: $ C $ 5; 3; 0) - wenn D7 Text enthält und die Tabelle Zahlen enthält;

= SVERWEIS (D7 & ""); Produkte $ A $ 2: $ C $ 5; 3; 0) - und umgekehrt.

Übrigens können Sie Text auf mehrere Arten gleichzeitig in eine Zahl übersetzen, wählen Sie:

• Doppelnegation -D7.
• Multiplikation mit einem D7 * 1.
• Nulladdition D7 + 0.
• Erhöhen in die erste Potenz D7 ^ 1.

Das Konvertieren einer Zahl in Text erfolgt durch Verkettung mit einer leeren Zeichenfolge, die Excel zwingt, den Datentyp zu konvertieren.

So unterdrücken Sie # N / A

Dies ist sehr bequem mit der IFERROR-Funktion.

Beispiel: = IFERROR (SVERWEIS (D7; Produkte! $ A $ 2: $ C $ 5; 3; 0); "").

Wenn SVERWEIS den Fehlercode # N / A zurückgibt, fängt IFERROR ihn ab und ersetzt Parameter 2 (in diesem Fall eine leere Zeichenfolge), und wenn kein Fehler auftritt, dann gibt diese Funktion vor, dass sie überhaupt nicht existiert, aber Es gibt nur SVERWEIS, das ein normales Ergebnis zurückgegeben hat.

Array

Oft vergessen sie, die Referenz des Arrays absolut zu machen, und wenn das Array gestreckt wird, "schwimmt". Denken Sie daran, $ A $ 2 zu verwenden: $ C $ 5 anstelle von A2: C5.

Es empfiehlt sich, das Referenzarray auf einem separaten Blatt der Arbeitsmappe zu platzieren. Es kommt nicht unter die Füße, und es wird sicherer.

Eine noch bessere Idee wäre, dieses Array als benannten Bereich zu deklarieren.

Viele Benutzer verwenden bei der Angabe eines Arrays eine Konstruktion wie A: C und geben ganze Spalten an. Dieser Ansatz hat eine Daseinsberechtigung, da Sie sich nicht merken müssen, dass Ihr Array alle erforderlichen Strings enthält. Wenn Sie dem Blatt mit dem ursprünglichen Array Zeilen hinzufügen, muss der als A:C angegebene Bereich nicht angepasst werden. Natürlich zwingt dieses syntaktische Konstrukt Excel dazu, etwas mehr Arbeit zu leisten, als den Bereich genau anzugeben, aber dieser Aufwand kann vernachlässigt werden. Wir sprechen von Hundertstelsekunden.

Nun, am Rande des Genies - um das Array in der Form anzuordnen.

Verwenden der COLUMN-Funktion, um die zu extrahierende Spalte anzugeben

Wenn die Tabelle, in die Sie Daten mit SVERWEIS abrufen, dieselbe Struktur wie die Nachschlagetabelle hat, aber nur weniger Zeilen enthält, können Sie mit der Funktion COLUMN() in SVERWEIS automatisch die Nummern der abzurufenden Spalten berechnen. In diesem Fall sind alle SVERWEIS-Formeln gleich (angepasst an den ersten Parameter, der sich automatisch ändert)! Beachten Sie, dass der erste Parameter eine absolute Spaltenkoordinate hat.

Erstellen eines zusammengesetzten Schlüssels mit & "|" &

Wenn gleichzeitig nach mehreren Spalten gesucht werden muss, ist es erforderlich, einen zusammengesetzten Schlüssel für die Suche zu erstellen. Wäre der Rückgabewert nicht textuell (wie beim Feld "Code"), sondern numerisch, dann wäre hierfür die bequemere SUMIFS-Formel geeignet und der zusammengesetzte Spaltenschlüssel wäre überhaupt nicht erforderlich.

Dies ist mein erster Artikel für einen Lifehacker. Wenn es Ihnen gefallen hat, lade ich Sie zu einem Besuch ein und lese auch gerne in den Kommentaren über Ihre Geheimnisse der Nutzung der SVERWEIS-Funktion und dergleichen. Vielen Dank.:)