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

SVERWEIS ist eine Excel -Funktion . Dieser Artikel wird annehmen, dass der Leser bereits über Excel-Funktionen verfügt und grundlegende Funktionen wie SUMME, DURCHSCHNITT und HEUTE verwenden kann. In der gebräuchlichsten Verwendung ist SVERWEIS eine Datenbank -Funktion, was bedeutet, dass es mit Datenbanktabellen funktioniert - oder einfacher mit Listen von Dingen in einem Excel-Arbeitsblatt. Was für Dinge? Nun, irgendeine Art von Sache. 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 diese Liste eine Spalte mit dem eindeutigen Bezeichner (oder "Schlüssel" oder "ID") enthalten, und muss diese Spalte sein die erste Spalte in der Tabelle . Unsere obige Beispieldatenbank erfüllt dieses Kriterium. Der schwierigste Teil der Verwendung von SVERWEIS ist, genau zu verstehen, wofür es ist. Lassen Sie uns zuerst sehen, ob wir das zuerst klarstellen können:

SVERWEIS ruft Informationen aus einer Datenbank / Liste basierend auf einer angegebenen Instanz des eindeutigen Bezeichners ab.

Im obigen Beispiel würden Sie die SVERWEIS-Funktion in eine andere Tabellenkalkulation einfügen ein Artikelcode, und er würde entweder die Beschreibung des entsprechenden Artikels, seinen Preis oder seine Verfügbarkeit (die Menge "auf Lager"), wie in Ihrer ursprünglichen Liste beschrieben, an Sie zurücksenden. Welche dieser Informationen werden Sie zurückgeben? Nun, Sie müssen 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 es. 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 immer wieder verwenden können fiktives Unternehmen. 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 wird die Beschreibung und den Preis jedes Artikels aus unserer Produktdatenbank abrufen. 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, suchen wir die Produktdatenbank auf einem separaten Blatt in derselben Arbeitsmappe:

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

Also haben wir unsere Produktdatenbank erstellt, die aussieht so:

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

Als nächstes verschieben wir die aktive Zelle in die Zelle, in der Wir möchten, dass Informationen, die von SVERWEIS aus der Datenbank abgerufen werden, gespeichert werden. 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 die 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. Klicken Sie zuerst auf die Registerkarte

Formeln und dann auf Funktion einfügen : Es wird ein Feld angezeigt, in dem Sie eine der in Excel verfügbaren Funktionen auswählen können.

Um den gesuchten zu finden, 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 fragt nach allen benötigten Argumenten (oder Parameter ) um die SVERWEIS-Funktion zu vervollständigen. Sie können sich diese Box als Funktion vorstellen, die uns die folgenden Fragen stellt: Welche eindeutige Kennung suchen Sie in der Datenbank?

1. Wo ist die Datenbank?
2. Welche Information aus der Datenbank ist zugeordnet? die eindeutige Kennung, möchten Sie für Sie abgerufen haben?
3. Die ersten drei Argumente werden

in Fettschrift angezeigt, was darauf hinweist, dass es sich um obligatorische Argumente handelt (die SVERWEIS-Funktion ist unvollständig ohne sie und geben keinen gültigen Wert zurück). Das vierte Argument ist nicht fett, was bedeutet, dass es optional ist: Wir vervollständigen die Argumente in der Reihenfolge von oben nach unten.

Das erste Argument, das wir vervollständigen müssen, ist das Argument

Lookup_value . Die Funktion benötigt, dass wir ihnen mitteilen, wo die eindeutige Kennung (in diesem Fall Artikelcode ) zu finden ist, an die die Beschreibung zurückgegeben werden soll. Wir müssen den Artikelcode auswählen, den wir vorher 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

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

Nun müssen wir einen Wert für das Argument

Table_array 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, also klicken wir zuerst auf diese Arbeitsblatt-Registerkarte: Als nächstes wählen wir die gesamte Datenbank ohne die Kopfzeile aus:

... und drücken

Enter . Der Zellenbereich, der die Datenbank repräsentiert (in diesem Fall "'Produktdatenbank'! A2: D7") wird für uns automatisch in das zweite Argument eingetragen. Jetzt müssen wir das dritte Argument eingeben,

Col_index_num . 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, dass die Beschreibung des Elements an uns zurückgegeben wird. Wenn Sie sich das Datenbank-Arbeitsblatt ansehen, werden Sie feststellen, dass die Spalte "Beschreibung" die zweite -Spalte in der Datenbank ist. Das bedeutet, dass wir in das Feld Col_index_num einen Wert von "2" eingeben müssen: Es ist wichtig zu beachten, dass wir hier keine "2" eingeben, da die Spalte "Description" in ist die

B -Spalte auf diesem Arbeitsblatt. Wenn die Datenbank zufällig in der Spalte K des Arbeitsblatts startet, geben wir immer noch eine "2" in dieses Feld ein, weil die Spalte "Beschreibung" die zweite Spalte in der Zellengruppe ist, die wir bei der Angabe des " Table_array ". Schließlich müssen wir entscheiden, ob ein Wert in das endgültige VLOOKUP-Argument eingegeben wird,

Range_lookup . Dieses Argument erfordert entweder einen true oder false Wert, oder es sollte leer gelassen werden. Bei der Verwendung von SVERWEIS mit Datenbanken (wie es in 90% der Fälle der Fall ist) kann man sich folgende Entscheidung überlegen: Wenn die erste Spalte der Datenbank (die Spalte, die den eindeutigen enthält) Bezeichner) ist alphabetisch / numerisch in aufsteigender Reihenfolge sortiert, dann kann ein Wert von

eingegeben werdentrue in dieses Argument ein, oder lassen Sie es leer. Wenn die erste Spalte der Datenbank

nicht sortiert ist oder in absteigender Reihenfolge sortiert ist, müssen Sie eingeben ein Wert von false in dieses Argument Da die erste Spalte unserer Datenbank nicht

sortiert ist, geben wir in dieses Argument false ein: Das ist es! 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 Beschreibung für den Artikelcode "R99245" korrekt in Zelle B11 eingegeben wurde: Die für uns erstellte Formel sieht folgendermaßen aus: Wenn wir eingeben ein

anderer

Artikel Code in Zelle A11, werden wir beginnen, die Macht der Funktion SVERWEIS zu sehen: Die Beschreibungszelle ändert sich mit dem neuen Artikel Code: Wir können eine ähnliche Reihe von Schritten zu erhalten der -Preis des Artikels

wurde in Zelle E11 zurückgegeben. Beachten Sie, dass die neue Formel in Zelle E11 erstellt werden muss. Das Ergebnis sieht so aus: ... und die Formel sieht so aus: Beachten Sie, dass der einzige Unterschied zwischen den beiden Formeln das dritte Argument ist (

Col_index_num

) hat sich von einem "2 "Bis zu einer" 3 "(weil wir Daten aus der dritten Spalte in der Datenbank erhalten möchten). Wenn wir uns dazu entschließen 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

... was so aussieht ...

Die Rechnungsvorlage vervollständigen

Wir haben viel über SVERWEIS gelernt bisher. 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:

Wir würden den Probenartikelcode aus Zelle A11 und die "2" aus Zelle D11 entfernen. Dies führt dazu, dass unsere neu erstellten SVERWEIS-Formeln Fehlermeldungen anzeigen:

Abhilfe schaffen wir durch sinnvolle Verwendung der Excel-Funktionen

1. IF ()
   

   

   
   und ISBLANK () . Wir ändern unsere Formel von diesem ... = SVERWEIS (A11, 'Produkt Datenbank'! A2: D7,2, FALSCH) ... zu diesem ... = IF (ISBLANK (A11), ", SVERWEIS) (A11, 'Produktdatenbank'! A2: D7,2, FALSE)) Wir würden die Formeln in den Zellen B11, E11 und F11 bis auf die restlichen 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 Zellbezüge für die Datenbank auf absolute
2. Zellbezüge ändern. Alternativ - und noch besser - könnten wir einen Bereichsnamen für die gesamte Produktdatenbank (wie "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)) ... dazu ... = IF (ISBLANK (A11), ", SVERWEIS (A11, Produkte, 2, FALSCH)) ... und dann Kopieren Sie die Formeln auf die anderen Rechnungspositionszeilen. Wir würde wahrscheinlich die Zellen "sperren", die unsere Formeln enthalten (oder eher entsperren
3. die anderen Zellen) und dann das Arbeitsblatt schützen, um sicherzustellen, dass unsere sorgfältig erstellten Formeln nicht versehentlich sind überschrieben, wenn jemand kommt, um die Rechnung auszufüllen. Wir würden die Datei als Vorlage
4. speichern, so dass sie von jedem in unserer Firma wiederverwendet werden könnte Wenn wir wirklich fühlen würden

schlau, wir würden eine Datenbank aller unserer Kunden in einem anderen Arbeitsblatt erstellen und dann die in F5 eingegebene Kunden-ID verwenden, um automatisch den Namen und die Adresse des Kunden in die Zellen B6, B7 und B8 einzugeben. Wenn Sie möchte mit SVERWEIS üben oder einfach unsere resultierende Rechnung T sehen emplate, kann von hier heruntergeladen werden.

Fehlerbehebung bei Ihrem Mac mit Activity Monitor

Nicht viele Gelegenheitsbenutzer kennen den Aktivitätsmonitor von OS X, und noch weniger wissen, wie es funktioniert und was es wirklich kann. Hier erfahren Sie, wie Sie mit Activity Monitor den Speicher Ihres Macs verwalten, langsame Anwendungen reparieren und verschiedene andere Probleme beheben können.

(how-to)

Synchronisieren Sie jeden Windows-Ordner mit Google Drive, OneDrive und Dropbox

Ab heute habe ich ein Cloud-Speicherkonto auf Amazon Drive, Google Drive, Microsoft OneDrive, iCloud Drive und Dropbox. Ich benutze hauptsächlich Dropbox insgesamt, aber ich benutze auch Amazon Drive auf meinem Kindle Fire, OneDrive auf Windows 10 und Google Drive für meine Fotos und Videos. Ein Problem, das ich immer hatte, ist, dass ich meine Ordner mit diesen Diensten synchronisieren möchte, aber ich mag es nicht, dass ich alle Dateien in ihre speziellen Containerordner kopieren oder verschieben muss.St

(How-to)