Wie verwende ich SVERWEIS in Excel

SVOOKUP ist eine der nützlichsten Funktionen von Excel, und es ist auch eines der am wenigsten verstandenen. In diesem Artikel entmystifizieren wir SVERWEIS anhand eines realen Beispiels. Wir erstellen eine verwendbare -Rechnungsvorlage für eine fiktive Firma.

SVOOKUP ist eine Excel Funktion .Dieser Artikel wird davon ausgehen, dass der Leser bereits über Excel-Funktionen verfügt und grundlegende Funktionen wie SUMME, DURCHSCHNITT und HEUTE verwenden kann. In seiner gebräuchlichsten Verwendung ist SVERWEIS eine --Funktion, was bedeutet, dass sie mit Datenbanktabellen arbeitet - oder einfacher gesagt, listet von Dingen in einem Excel-Arbeitsblatt auf. Was für Dinge? Nun, irgendeine Art .Sie haben möglicherweise ein Arbeitsblatt mit einer Liste von Mitarbeitern, Produkten oder Kunden oder CDs in Ihrer CD-Sammlung oder Sterne am Nachthimmel. Es ist nicht wirklich wichtig.

Hier ist ein Beispiel für eine Liste oder Datenbank. In diesem Fall handelt es sich um eine Liste von Produkten, die unser fiktives Unternehmen verkauft:

Normalerweise haben Listen wie diese eine Art eindeutige Kennung für jedes Element in der Liste. In diesem Fall befindet sich der eindeutige Bezeichner in der Spalte "Artikelcode".Hinweis: Damit die SVERWEIS-Funktion mit einer Datenbank / Liste funktioniert, muss eine Spalte mit dem eindeutigen Bezeichner( oder "Schlüssel" oder "ID") enthalten, und muss diese Spalte die erste Spalte in der Tabelle sein.Unsere obige Beispieldatenbank erfüllt dieses Kriterium.

Der schwierigste Teil der Verwendung von SVERWEIS ist, genau zu verstehen, wofür es ist. Sehen wir uns an, ob wir das zuerst klarstellen können:

SVOOKUP ruft Informationen aus einer Datenbank / Liste ab, basierend auf einer gelieferten Instanz des eindeutigen Identifikators.

Im obigen Beispiel würden Sie die SVERWEIS-Funktion in eine andere Tabellenkalkulation mit einem Artikelcode einfügen, und es würde Ihnen entweder die Beschreibung des entsprechenden Artikels, seinen Preis oder seine Verfügbarkeit( die "Lagermenge") zurückgebenursprüngliche Liste. Welche dieser Informationen werden Sie zurückgeben? Nun, Sie können das entscheiden, wenn Sie die Formel erstellen.

Wenn Sie nur eine Information aus der Datenbank benötigen, wäre es sehr schwierig, eine Formel mit einer SVERWEIS-Funktion zu erstellen. In der Regel würden Sie diese Art von Funktionalität in einer wiederverwendbaren Tabelle, z. B. einer Vorlage, verwenden. Jedes Mal, wenn jemand einen gültigen Artikelcode eingibt, erhält das System alle notwendigen Informationen über den entsprechenden Artikel.

Lassen Sie uns ein Beispiel dafür erstellen: Eine -Rechnungsvorlage , die wir in unserem fiktiven Unternehmen immer wieder verwenden können.

Zuerst starten wir Excel, und wir erstellen uns eine leere Rechnung:

So wird es funktionieren: Die Person, die die Rechnungsvorlage verwendet, wird eine Reihe von Artikelcodes in Spalte "A" ausfüllen, und das System ruft die einzelnen Artikel abBeschreibung und Preis aus unserer Produktdatenbank. Diese Informationen werden verwendet, um die Zeilensumme für jeden Artikel zu berechnen( vorausgesetzt, wir geben eine gültige Menge ein).

Um dieses Beispiel einfach zu halten, werden wir die Produktdatenbank auf einem separaten Blatt in derselben Arbeitsmappe finden:

In Wirklichkeit ist es wahrscheinlicher, dass sich die Produktdatenbank in einer separaten Arbeitsmappe befindet. Es macht wenig Unterschied für die SVERWEIS-Funktion, die es nicht interessiert, ob sich die Datenbank auf demselben Blatt, einem anderen Blatt oder einer anderen Arbeitsmappe befindet.

Also haben wir unsere Produktdatenbank erstellt, die wie folgt aussieht:

Um die VLOOKUP-Formel zu testen, die wir schreiben wollen, geben wir zuerst einen gültigen Artikelcode in die Zelle A11 unserer leeren Rechnung ein:

Next, wirVerschieben Sie die aktive Zelle in die Zelle, in der Informationen gespeichert werden sollen, die von SVERWEIS aus der Datenbank abgerufen wurden. Interessanterweise ist dies der Schritt, bei dem sich die meisten Menschen geirrt haben. Zur weiteren Erläuterung: Wir erstellen gerade eine SVERWEIS-Formel, die die Beschreibung abruft, die dem Artikelcode in Zelle A11 entspricht. Wo soll diese Beschreibung stehen, wenn wir sie bekommen? In Zelle B11 natürlich. Also schreiben wir die SVERWEIS-Formel: in Zelle B11.Wählen Sie jetzt Zelle B11.

Wir müssen die Liste aller verfügbaren Funktionen finden, die Excel zu bieten hat, so dass wir SVERWEIS auswählen können und Hilfe beim Ausfüllen der Formel bekommen. Dies wird gefunden, indem Sie zuerst auf die Registerkarte klicken und dann auf klicken. Fügen Sie die Funktion :

ein

Es wird ein Feld angezeigt, in dem Sie eine der in Excel verfügbaren Funktionen auswählen können.

Um den zu finden, nach dem wir suchen, könnten wir einen Suchbegriff wie "lookup" eingeben( weil die Funktion, die uns interessiert, eine -Lookup--Funktion ist).Das System würde uns eine Liste aller nachschlagenbezogenen Funktionen in Excel zurückgeben. SVERWEIS ist der zweite in der Liste. Wählen Sie es mit einem Klick OK .

Das -Funktionsargumente -Feld erscheint und fordert uns auf, alle -Argumente ( oder -Parameter ) einzugeben, die zum Vervollständigen der SVERWEIS-Funktion benötigt werden. Sie können sich diese Box als Funktion vorstellen, die uns die folgenden Fragen stellt:

1. Welche eindeutige Kennung suchen Sie in der Datenbank?
2. Wo ist die Datenbank?
3. Welche Informationen aus der Datenbank, die der eindeutigen Kennung zugeordnet sind, möchten Sie für Sie abgerufen haben?

Die ersten drei Argumente werden in Fettschrift angezeigt, was darauf hinweist, dass es sich um obligatorische --Argumente handelt( die SVERWEIS-Funktion ist ohne diese unvollständig und gibt keinen gültigen Wert zurück).Das vierte Argument ist nicht fett, was bedeutet, dass es optional ist:

Wir werden die Argumente in der Reihenfolge von oben nach unten vervollständigen.

Das erste Argument, das wir vervollständigen müssen, ist das Lookup_value Argument. Die Funktion muss uns mitteilen, wo die eindeutige Kennung( in diesem Fall der -Artikelcode ) zu finden ist, an die die Beschreibung zurückgegeben werden soll. Wir müssen den Artikelcode auswählen, den wir früher eingegeben haben( in A11).

Klicken Sie auf das Auswahlsymbol rechts neben dem ersten Argument:

Klicken Sie dann einmal auf die Zelle mit dem Artikelcode( A11) und drücken Sie . Geben Sie ein:

Der Wert von "A11" wird in das erste Argument eingefügt.

Nun müssen wir einen Wert für das Table_array Argument eingeben. Mit anderen Worten, wir müssen SVERWEIS angeben, wo die Datenbank / Liste zu finden ist. Klicken Sie auf das Auswahlsymbol neben dem zweiten Argument:

Suchen Sie nun die Datenbank / Liste und wählen Sie die gesamte Liste - ohne die Kopfzeile .In unserem Beispiel befindet sich die Datenbank auf einem separaten Arbeitsblatt, daher klicken wir zuerst auf diese Arbeitsblatt-Registerkarte:

Als nächstes wählen wir die gesamte Datenbank aus, ohne die Kopfzeile:

. .. und drücken Geben Sie ein. Der Zellenbereich, der die Datenbank repräsentiert( in diesem Fall "'Produktdatenbank'! A2: D7") wird für uns automatisch in das zweite Argument eingetragen.

Nun müssen wir das dritte Argument Col_index_num eingeben. Wir verwenden dieses Argument, um SVERWEIS zu spezifizieren, welche Information aus der Datenbank mit unserem Artikelcode in A11 verknüpft ist, wir möchten zu uns zurückkehren. In diesem speziellen Beispiel möchten wir die -Beschreibung des Artikels an uns zurücksenden. Wenn Sie sich das Datenbankarbeitsblatt ansehen, werden Sie feststellen, dass die Spalte "Beschreibung" die zweite -Spalte von in der Datenbank ist. Dies bedeutet, dass wir in das Feld Col_index_num den Wert "2" eingeben müssen:

Es ist wichtig zu beachten, dass hier keine "2" eingegeben wird, da die Spalte "Description" in der Spalte B auf istdas Arbeitsblatt. Wenn die Datenbank zufällig in der Spalte K des Arbeitsblatts gestartet wird, geben wir in dieses Feld immer noch eine "2" ein, da die Spalte "Beschreibung" die zweite Spalte in den Zellen ist, die wir bei der Angabe von "Table_array" ausgewählt haben.

Schließlich müssen wir entscheiden, ob ein Wert in das endgültige SVERWEIS-Argument, Range_lookup , eingegeben werden soll. Dieses Argument erfordert entweder einen -True-- oder einen -False--Wert oder es sollte leer gelassen werden. Wenn Sie VLOOKUP mit Datenbanken verwenden( wie es in 90% der Fälle der Fall ist), können Sie wie folgt entscheiden:

Wenn die erste Spalte der Datenbank( die Spalte, die die eindeutigen Bezeichner enthält)ist alphabetisch / numerisch in aufsteigender Reihenfolge sortiert, dann ist es möglich, einen Wert von true in dieses Argument einzugeben oder es leer zu lassen.

Wenn die erste Spalte der Datenbank nicht sortiert oder in absteigender Reihenfolge sortiert ist, dann muss einen Wert von false in dieses Argument eingeben

Da die erste Spalte unserer Datenbank nicht ist, Wir geben false in dieses Argument ein:

Das war's! Wir haben alle Informationen eingegeben, die für SVERWEIS erforderlich sind, um den von uns benötigten Wert zurückzugeben. Klicken Sie auf die Schaltfläche OK , und beachten Sie, dass die dem Artikelcode "R99245" entsprechende Beschreibung korrekt in Zelle B11 eingegeben wurde:

Die für uns erstellte Formel sieht folgendermaßen aus:

Geben Sie einen anderen -Artikelcode in Zelle einA11, wir werden die Leistungsfähigkeit der VLOOKUP-Funktion sehen: Die Beschreibungszelle ändert sich entsprechend dem neuen Artikelcode:

Wir können ähnliche Schritte ausführen, um den -Preis des Artikels in Zelle E11 zurückzugeben. Beachten Sie, dass die neue Formel in Zelle E11 erstellt werden muss. Das Ergebnis sieht folgendermaßen aus:

. .. und die Formel sieht folgendermaßen aus:

Beachten Sie, dass der einzige Unterschied zwischen den beiden Formeln das dritte Argument ist( Col_index_num ) hat sich von "2" zu "3" geändert( weilwir wollen Daten aus der 3. Spalte in der Datenbank erhalten).

Wenn wir uns entscheiden würden, 2 dieser Artikel zu kaufen, würden wir eine "2" in Zelle D11 eingeben. Wir würden dann eine einfache Formel in Zelle F11 eingeben, um die Zeilensumme zu erhalten:

= D11 * E11

. .., die so aussieht. ..

Die Rechnungsvorlage vervollständigen

Bisher haben wir viel über SVERWEIS gelernt. Tatsächlich haben wir in diesem Artikel alles gelernt, was wir lernen werden. Es ist wichtig zu beachten, dass SVERWEIS in anderen Umständen als Datenbanken verwendet werden kann. Dies ist weniger verbreitet und wird möglicherweise in zukünftigen How-To Geek-Artikeln behandelt.

Unsere Rechnungsvorlage ist noch nicht vollständig. Um es zu vervollständigen, würden wir folgendes tun:

1. Wir würden den Probenartikelcode aus Zelle A11 und die "2" aus Zelle D11 entfernen. Dies führt dazu, dass in unseren neu erstellten SVERWEIS-Formeln Fehlermeldungen angezeigt werden:
   
   Dies können wir durch sinnvolle Verwendung der Excel IF() - und ISBLANK() -Funktionen beheben. Wir ändern unsere Formel von diesem. .. = SVERWEIS( A11, 'Produktdatenbank'! A2: D7,2, FALSCH) . .. zu diesem. .. = IF( ISBLANK( A11), "", SVERWEIS( A11, 'Produktdatenbank'! A2: D7,2, FALSE))
   
2. Wir würden die Formeln in den Zellen B11, E11 und F11 auf den Rest der Rechnungszeilen kopieren. Beachten Sie, dass die resultierenden Formeln nicht mehr korrekt auf die Datenbanktabelle verweisen, wenn wir dies tun. Wir könnten das beheben, indem wir die Zellreferenzen für die Datenbank auf absolute -Zellreferenzen ändern. Alternativ - und noch besser - könnten wir einen -Bereichsnamen für die gesamte Produktdatenbank( z. B. "Produkte") erstellen und diesen Bereichsnamen anstelle der Zellenreferenzen verwenden. Die Formel würde sich dadurch ändern. .. = IF( ISBLANK( A11), "", SVERWEIS( A11, 'Produktdatenbank'! A2: D7,2, FALSE)) . .. zu diesem. .. = IF( ISBLANK( A11), "" SVERWEIS( A11, Produkte, 2, FALSCH)) . .. und dann kopieren Sie die Formeln in die anderen Rechnungspositionszeilen.
3. Wir würden wahrscheinlich die Zellen sperren, die unsere Formeln enthalten( oder vielmehr die andere Zellen entsperren) und dann das Arbeitsblatt schützen, um sicherzustellen, dass unsere sorgfältig konstruierten Formeln nicht versehentlich überschrieben werden, wenn jemand zum Füllen kommtin der Rechnung.
4. Wir würden die Datei als eine Vorlage speichern, so dass sie von jedem in unserer Firma

wiederverwendet werden könnte. Wenn wir wirklich schlau würden, würden wir eine Datenbank aller unserer Kunden in einem anderen Arbeitsblatt erstellen und dann dieKunden-ID, die in Zelle F5 eingegeben wurde, um den Namen und die Adresse des Kunden automatisch in die Zellen B6, B7 und B8 einzugeben.

Wenn Sie mit SVERWEIS üben möchten, oder einfach unsere resultierende Rechnungsvorlage sehen möchten, können Sie sie von hier herunterladen.