Wer zu faul ist oder keine Zeit zum Lesen hat – schaut sich das Video an. Details und Nuancen sind im Text unten.
Formulierung des Problems
Wir haben also zwei Tische – Bestelltabelle и Preisliste:
Die Aufgabe besteht darin, Preise aus der Preisliste automatisch in die Bestelltabelle zu ersetzen, wobei der Schwerpunkt auf dem Namen des Produkts liegt, damit Sie später die Kosten berechnen können.
Lösung
Im Excel-Funktionssatz unter der Kategorie Referenzen und Arrays (Nachschlagen und Referenz) es gibt eine funktion VPR (SVERWEIS).Diese Funktion sucht einen bestimmten Wert (in unserem Beispiel das Wort „Äpfel“) in der linken Spalte der angegebenen Tabelle (Preisliste) von oben nach unten und zeigt, nachdem sie ihn gefunden hat, den Inhalt der angrenzenden Zelle an (23 Rubel) .Schematisch kann die Funktionsweise dieser Funktion so dargestellt werden:
Damit Sie die Funktion bequem weiter nutzen können, tun Sie eines gleich – geben Sie dem Zellenbereich in der Preisliste einen eigenen Namen. Dazu alle Zellen der Preisliste bis auf die „Kopfzeile“ (G3: H19) markieren, aus dem Menü auswählen Einfügen – Name – Zuordnen (Einfügen – Name – Definieren) oder drücken Sie STRG + F3 und geben Sie einen beliebigen Namen (ohne Leerzeichen) ein, z PREISLISTE… Unter diesem Namen können Sie nun zukünftig auf die Preisliste verlinken.
Jetzt verwenden wir die Funktion VPR… Wählen Sie die Zelle aus, in die es eingegeben werden soll (D3) und öffnen Sie die Registerkarte Formeln – Einfügen von Funktionen (Formeln – Funktion einfügen)… in der Kategorie Referenzen und Arrays (Suche und Referenz) Funktion finden VPR (SVERWEIS) und drücken Sie OK… Es erscheint ein Fenster zur Eingabe von Argumenten für die Funktion:
Wir füllen sie der Reihe nach aus:
- Gewünschter Wert (Lookup-Wert) – der Name des Produkts, das die Funktion in der linken Spalte der Preisliste finden soll. In unserem Fall das Wort „Äpfel“ aus Zelle B3.
- Tisch (Tabellenarray) – eine Tabelle, aus der die gewünschten Werte entnommen werden, also unsere Preisliste. Als Referenz verwenden wir unseren eigenen Namen „Preis“, der zuvor angegeben wurde. Wenn Sie keinen Namen angegeben haben, können Sie die Tabelle einfach auswählen, aber Vergessen Sie nicht, den Knopf zu drücken F4um den Link mit Dollarzeichen zu fixieren, da es sonst beim Kopieren unserer Formel nach unten in die restlichen Zellen in Spalte D3:D30 rutscht.
- Spaltennummer (Spaltenindexnummer) – Seriennummer (kein Buchstabe!) Der Spalte in der Preisliste, aus der wir die Preiswerte entnehmen. Die erste Spalte der Preisliste mit den Namen ist mit 1 nummeriert, daher benötigen wir den Preis aus der Spalte mit der Nummer 2.
- Intervall_Lookup (Bereichssuche) – In dieses Feld können nur zwei Werte eingetragen werden: FALSE oder TRUE:
- Wenn ein Wert eingegeben wird 0 or LÜGNERISCH (FALSCH), dann bedeutet dies tatsächlich, dass nur die Suche erlaubt ist genaue Übereinstimmung, dh wenn die Funktion den in der Bestelltabelle angegebenen Sonderartikel nicht in der Preisliste findet (zB bei Eingabe von „Kokosnuss“), generiert sie den Fehler #NV (keine Daten).
- Wenn ein Wert eingegeben wird 1 or TRUE (WAHR), dann bedeutet dies, dass Sie die Suche nicht nach dem genauen zulassen, sondern ungefähre Übereinstimmung, dh im Fall von „Kokosnuss“, versucht die Funktion, ein Produkt mit einem Namen zu finden, der „Kokosnuss“ so ähnlich wie möglich ist, und gibt den Preis für diesen Namen zurück. In den meisten Fällen kann eine solche ungefähre Substitution dem Benutzer einen Streich spielen, indem er den Wert des tatsächlich vorhandenen falschen Produkts ersetzt! Für die meisten realen Geschäftsprobleme sollte die ungefähre Suche daher am besten nicht zugelassen werden. Die Ausnahme ist, wenn wir nach Zahlen und nicht nach Text suchen – zum Beispiel bei der Berechnung von Stufenrabatten.
Alles! Es bleibt zu drücken OK und kopieren Sie die eingegebene Funktion in die gesamte Spalte.
# N / A Fehler und deren Unterdrückung
Funktion VPR (SVERWEIS) gibt den Fehler #NV zurück (#N / A) wenn ein:
- Exakte Suche aktiviert (argument Intervallansicht = 0) und der gewünschte Name ist nicht drin Tisch.
- Grobsuche inklusive (Intervallansicht = 1), Aber Tisch, in dem gesucht wird, ist nicht nach Namen aufsteigend sortiert.
- Das Format der Zelle, aus der der erforderliche Wert des Namens stammt (z. B. B3 in unserem Fall) und das Format der Zellen der ersten Spalte (F3: F19) der Tabelle sind unterschiedlich (z. B. numerisch und Text ). Dieser Fall ist besonders typisch, wenn anstelle von Textnamen numerische Codes (Kontonummern, Kennungen, Daten usw.) verwendet werden. In diesem Fall können Sie die Funktionen verwenden Ч и TEXT Datenformate zu konvertieren. Es wird in etwa so aussehen:
=SVERWEIS(TEXT(B3),Preis,0)
Hier können Sie mehr darüber lesen.
- Die Funktion kann den erforderlichen Wert nicht finden, da der Code Leerzeichen oder unsichtbare nicht druckbare Zeichen (Zeilenumbrüche usw.) enthält. In diesem Fall können Sie Textfunktionen verwenden TRIMMEN (TRIMMEN) и IHR EIGENES LOGO(SAUBER) um sie zu entfernen:
=VLOOKUP(TRIMSPACES(CLEAN(B3)),Preis,0)
=VLOOKUP(TRIM(CLEAN(B3));Preis;0)
Um die Fehlermeldung zu unterdrücken #N / A (#N / A) In Fällen, in denen die Funktion keine exakte Übereinstimmung finden kann, können Sie die Funktion verwenden IFERROR (IFERROHR)… Diese Konstruktion fängt also beispielsweise alle Fehler ab, die vom SVERWEIS generiert werden, und ersetzt sie durch Nullen:
= IFERROR (VLOOKUP (B3, Preis, 2, 0), 0)
= IFERROR (VLOOKUP (B3; Preis; 2; 0); 0)
PS
Wenn Sie nicht einen Wert, sondern den gesamten Satz auf einmal extrahieren müssen (wenn es mehrere verschiedene gibt), müssen Sie mit der Array-Formel schamanisieren. oder verwenden Sie die neue XLOOKUP-Funktion von Office 365.
- Eine verbesserte Version der SVERWEIS-Funktion (SVERWEIS 2).
- Schnelle Berechnung von Stufenrabatten (Bereichsrabatten) mit der SVERWEIS-Funktion.
- Wie man mit den Funktionen INDEX und MATCH einen „linken SVERWEIS“ erstellt
- So verwenden Sie die SVERWEIS-Funktion, um die Formulare mit Daten aus der Liste auszufüllen
- So ziehen Sie nicht den ersten, sondern alle Werte auf einmal aus der Tabelle
- SVERWEIS2- und SVERWEIS3-Funktionen aus dem PLEX-Add-on