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:

Benutzerdefiniertes Sortieren einer Tabelle

Eine Liste lässt sich mit wenigen Klicks sortieren, alphabetisch von A-Z (aufsteigend) oder Z-A (absteigend). Allerdings reicht dies unter Umständen nicht aus, wenn Du eine individuelle (benutzerdefinierte) Sortierung nach Kategorien oder ähnlich anstrebst. Vielleicht hast Du Glück und die Kategorien sind zufälligerweise in einer Reihenfolge, die sich sinngemäss auf- oder absteigend sortieren lässt. Meist ist dies jedoch nicht der Fall (z.B. auch bei Monaten oder Wochentagen). In diesem Beitrag zeige ich Dir aber eine Variante, wie Du eine eigene Sortierung vorgeben kannst ohne Hilfsspalten zu verwenden.

Angenommen Du hast eine Liste mit Freizeitaktivitäten:

sortList_initial

Wenn Du diese Liste nach ihrer Priorität (Spalte B) sortierst, wirst Du entweder die Einträge mit "Ignorieren" oder "Prio. 2" zu oberst darstellen. Gerne hätten wir jedoch die Reihenfolge: Prio. 1; Prio. 2; Ignorieren.

Um dies zu erlangen, wenden wir die Sortierung nach einer benutzerdefinierten Liste an. Markiere zunächst Deine gesamte Tabelle mit allen Aktivitäten & deren Prioritäten (oder klicke auf z.B. eine Überschrift "Freizeitaktivität" - denn der mögliche Tabellenbereich wird automatisch erkannt und vorgeschlagen). Gehe anschliessen im Menüband "Daten" auf "Sortieren":

Im angezeigten Fenster zur Sortierung kannst Du dann wählen: Sortieren nach "Priorität" und als Reihenfolge wählst Du "Benutzerdefinierte Liste...":

sortList_customList

Dies öffnet Dir umgehend ein weiteres Fenster. Generiere dort eine neue Liste, indem Du einfach Listeneinträge nach ihrer Reihenfolge eingibst. Jedes Kriterium schreibst Du auf eine eigene Zeile und bestätigst jeweils mit "Enter", als Zeilenumbruch. Erst wenn Du alle Einträge in ihrer Reihenfolge eingetragen hast, gehst Du rechts auf "Hinzufügen". Nun hast Du deine eigene Liste definiert, nach der Du sortieren kannst.

sortList_customListValues

Auf der linken Seite findest Du auch weitere benutzerdefinierte Listen. Da sind weitere Anwendungsfälle ersichtlich, die Du früher allenfalls nur mit Hilfsspalten gelöst hast: Die Sortierung nach Wochentagen (Montag bis Sonntag) oder nach Monaten (Januar bis Dezember). Diese lassen sich ja auch nicht alphabetisch sinnvoll sortieren.

Sobald Du die Liste ausgewählt hast, greift diese Reihenfolge. Du kannst in den Sortierungseinstellungen weitere Ebenen einfügen (z.B. nach der Priorität dann die Freizeitaktivitäten alphabetisch sortieren). Füge dazu weitere Ebenen nach Deinen Bedürfnissen ein. So erhalten wir eine benutzerdefinierte, sortiere Tabelle:

UEFA EURO 2024 - Tippspiel-Vorlage

Diesen Sommer ist es wieder soweit: Die UEFA EURO 2024 wird am Freitag, 14. Juni in München (Deutschland) eröffnet. Wie bereits in den vergangenen Jahren habe ich Euch eine Tippspiel-Vorlage vorbereitet. Die Vorlage beinhaltet zudem den gesamten Spielplan (Quelle: UEFA EURO 2024: Wann und wo wird gespielt?).

Hier findest Du die aktuelle Vorlage:
Excelblog_EM2024_Tippspiel

Zurzeit sind fünf Tipp-Spieler eingetragen. Um weitere Teilnehmer hinzuzufügen, kannst Du einfach die komplette Spalte eines Spielers kopieren und nebenan einfügen. Dasselbe gilt für die "Punkteübersicht", wo Du dann die erzielten Punkte pro Spiel siehst. Ich empfehle Dir, einen Spieler in der Mitte (beispielsweise "Max") zu kopieren, damit die Trennlinien korrekt bestehen bleiben. Auch die Formel "RANG" zu unterst wird sich somit auf den korrekten Bereich beziehen. Falls Du Personen entfernen möchtest, kannst Du ganz einfach komplette Spalten löschen.

Ein potentielles Fehlerrisiko besteht in der Datei: Sollte ein Team mehr als 9 Tore erzielen, können die Tipps nicht richtig abgestimmt werden - in diesem Sonderfall müsstest Du für dieses Spiel die Punkte manuell verteilen. Die Summe- und Rang-Formeln zu unterst werden davon nicht betroffen sein.

Ich wünsche Dir bereits jetzt viel Spass & erfolgreiche Tipps!