SVERWEIS mit als Text formatierter Zahl

Exporte und die Daten aus Systemen können in unterschiedlichen Formaten daherkommen. Teilweise sind Angaben wie "Produktnummern" als Text formatiert, in einer Referenztabelle (z.B. einer Produktliste) sind diese jedoch nicht. Das kann dazu führen, dass Verweise wie der "SVERWEIS" kein Resultat findet. Nachfolgend wird ein simpler Weg aufgezeigt, wie die Formel erweitert werden kann, ohne die Input-Daten (im Textformat) zunächst als Zahl umformatieren zu müssen.

Angenommen Du möchtest aus einer Liste mit diversen Produktnummern den Stückpreis hinzufügen. Diesen würden wir mittels "SVERWEIS" aus einer anderen Tabelle, der Produkttabelle, anreichern. Die ist ein Beispiel der Produkttabelle:

Produktliste

Wenn wir nach einem Produkt wie beispielsweise "5712471" suchen, sollten wir den Stückpreis von 105.-- zurückerhalten. Da unsere eine Liste jedoch als Text formatierte Zahlen enthält, können wir mittels "eigentlich" korrekt gesetzter Formel dennoch kein Resultat zurückerhalten. Der SVERWEIS gibt also #NV als Fehlermeldung zurück:

SVERWEIS_Fehlermeldung

Dies wäre die Formel eines "simplen" SVERWEIS:

=SVERWEIS(A2;Produkttabelle!A:C;3;0)

Anstatt dass wir nun die Produktnummer in unserer Suche umformatieren, können wir die Formel leicht ergänzen. Wenn wir eine als Text formatierte Zahl mit *1 multiplizieren, dann erhalten wir eine korrekt formatierte Zahl. Der SVERWEIS funktioniert dann. Dies wäre das gewünschte Resultat:

SVERWEIS_korrekt

Wobei wir die oben aufgeführte Formel nur leicht anpassen, indem wir A2 mit *1 multiplizieren:

=SVERWEIS(A2*1;Produkttabelle!A:C;3;0)

Weitere Tipps & Tricks

Auf Excelblog.ch findest Du diverse weitere Tipps & Tricks zu Verweisen wie dem SVERWEIS, aber auch zum Umgang mit als Text formatierten Zahlen. Siehe beispielsweise:

(Komplexere) Vorhaben in Excel umsetzen

Dieser Beitrag stellt Dir ein mögliches Vorgehen dar, wie Du komplexere Vorhaben in Excel umsetzen kannst. Dieser Beitrag knüpft auch zu Teilen an Professionelles Präsentieren von Tabellen an, wo gewisse grundsätzliche Tipps dargestellt wurden.

Nehmen wir an, Du möchtest einen eigenen Report konstruieren, der Deine Verkäufe auf Tagesbasis darstellt und vergleicht. Damit ist bereits ein entscheidender Schritt getan: Wir wissen in etwa, was unser Ziel ist. Natürlich kann man sich bereits ein Bild davon machen, wie der Report aussehen soll. Eine grobe Idee zu haben, ist sicherlich nicht schlecht. Bevor ich aber mit Diagrammen und Kennzahlen loslege, beschäftige ich mich mit der Datenbasis. Woher stammen meine Zahlen und in welcher Form stehen sie mir zur Verfügung? Insbesondere, da es sich um eine Tagesauswertung handelt, sollte die Aufbereitung möglichst vereinfacht sein. Mit der effektiven Automatisierung von einzelnen Schritten beschäftige ich mich jedoch ebenfalls erst später, da dies sonst unnötig die Komplexität erhöht. Ich möchte das Ganze erst einmal "zum Laufen bringen". Sobald alles funktioniert und ich auch mögliche Schwierigkeiten oder Gefahren von meinem Reporting erkannt habe, widme ich mich der Automatisierung.

Sobald Du weisst, welche Daten verarbeitet werden, kannst Du auch den möglichen Output bestimmen. Es gilt nun abzugleichen, ob dies den Erwartungen und dem definierten Ziel entspricht. Falls nicht, kann man eventuell die Datenbasis um weitere Werte ergänzen.

Ich arbeite generell mit separaten Tabellenblättern - ein eigenes für den fertigen Report, ein eigenes für den "Input"/die Datenbasis. Nun können anhand von Beispiel- oder Testdaten die gewünschten Kennzahlen ermittelt und allfällige Diagramme erstellt werden. Bei Formeln gehe ich Schritt für Schritt vor. Nehmen wir als Beispiel eine Wenn-Dann-Formel. Ich erarbeite mir zuerst die Überprüfung (das "Wenn") und erfasse danach erst das "Dann". Insbesondere bei verschachtelten Wenn-Dann-Formeln (falls bei "Sonst" gleich eine nächste Wenn-Dann-Formel angehängt wird - also verschiedene Fälle abgedeckt werden sollen) ist ein solches Vorgehen hilfreich. Auch eine Skizze kann nützlich sein, um sämtliche Szenarien aufzuzeigen. Unmittelbar in einem Atemzug eine verschachtelte Wenn-Dann-Formel zu erstellen, ist extrem anspruchsvoll.

Zwischendurch zusätzliche Beispieldaten aufzunehmen kann helfen, um die spätere Aufbereitung des Berichts zu simulieren. Es zeigt aber auch auf, wie beispielsweise negative oder sogar fehlerhafte Werte den Report beeinflussen. Darauf kannst Du zu diesem Zeitpunkt gut reagieren und Sicherheiten einbauen. Mit Dropdown-Listen, respektive der Datenüberprüfung (Daten → Datenüberprüfung), können manuell vorgenommene Eintragungen auf vordefinierte Formate oder Werte eingeschränkt werden (beispielsweise, dass in der Spalte "Datum" zwingend auch ein solches eingetragen wird).

Sobald sämtliche Kennzahlen korrekt ermittelt sind, widme ich mich der tatsächlichen Anordnung und Aspekten der Ästethik. Welche Informationen sollen wo angegeben, welche Farben (Corporate Design) sollen verwendet werden, was soll der Titel beinhalten et cetera. Auch Überlegungen zum Druckbereich können an dieser Stelle gemacht werden.

Da wir nun genau wissen, wie unser Report funktioniert, können wir uns mit der Automatisierung befassen - also die Exceltabelle dynamisch machen. Formeln wie "=HEUTE()" und "=BEREICH.VERSCHIEBEN" sowie bedingte Formatierungen kommen hier zum Einsatz. Schau Dir hierzu auch die beiden Beiträge Dynamische Ergebnisse sowie Dynamische Titel an.  Auch mittels Makro kann selbstverständlich die Erstellung des Berichts vereinfacht oder gar komplett automatisiert werden.

Zu einem späteren Zeitpunkt müssen oftmals weitere Kennzahlen in den Bericht aufgenommen oder wiederum entfernt werden. Es kann auch sein, dass sich die Datenquelle verschiebt oder verändert, worauf es zu reagieren gilt. Die da vorzunehmenden Tätigkeiten entsprechen jedoch stets dem oben erklärten Vorgehen. Und eines ist garantiert: Durch Konzeption und Konstruktion von solchen Tabellen verbessert man sein Handwerk in Excel enorm!