Lieferanten-Preisliste in Excel vergleichen
Eine neue Preisliste vom Lieferanten zeigt selten, was sich geändert hat. Meist kommt eine Datei mit ein paar hundert oder tausend Zeilen, ohne Markierung, welche Preise gestiegen sind. Mit drei Formeln finden Sie in Excel jede Preisänderung, jeden neuen und jeden entfallenen Artikel. Hier ist die Anleitung, und hier sind die Stellen, an denen der Vergleich oft still danebengeht.
1. Beide Listen in eine Datei
Kopieren Sie die bisherige Liste in ein Tabellenblatt namens Alt und die neue in ein Blatt namens Neu. Löschen Sie Titel- und Logozeilen über der Kopfzeile, damit jede Liste in Zeile 1 mit den Spaltenüberschriften beginnt. In den Beispielen steht die Artikelnummer in Spalte A und der Einkaufspreis in Spalte D. Passen Sie die Buchstaben an Ihre Liste an.
Prüfen Sie dabei gleich, ob beide Listen dieselbe Preisspalte meinen. Manche Lieferanten haben Listenpreis, Ihren Nettopreis und den unverbindlichen Verkaufspreis nebeneinander. Vergleichen Sie immer Nettopreis mit Nettopreis.
2. Artikelnummern vereinheitlichen
Hier passieren die meisten Fehler. Excel speichert 004711 gern als Zahl 4711, die andere Liste aber als Text. Dann findet keine Formel den Artikel wieder. Formatieren Sie die Spalte A in beiden Blättern gleich. Gibt es Leerzeichen am Ende, hilft eine Hilfsspalte mit:
=GLÄTTEN(A2)
Haben Artikelnummern führende Nullen, die in einer Liste fehlen, bringt =TEXT(A2;"000000") beide auf dieselbe Länge. Die Anzahl der Nullen entspricht der Länge der Artikelnummer.
3. Den alten Preis neben den neuen holen
Im Blatt Neu schreiben Sie in eine freie Spalte, zum Beispiel E, den bisherigen Preis. Ab Excel 2021 und in Microsoft 365:
=XVERWEIS(A2;Alt!A:A;Alt!D:D;"neu")
In älteren Excel-Versionen:
=WENNFEHLER(SVERWEIS(A2;Alt!A:D;4;FALSCH);"neu")
Ziehen Sie die Formel bis zur letzten Zeile nach unten. Wo „neu“ steht, ist der Artikel in der alten Liste nicht vorhanden. Wichtig beim SVERWEIS ist das FALSCH am Ende: Ohne diesen Parameter sucht Excel ungefähr und liefert bei unsortierten Listen falsche Preise, ohne eine Fehlermeldung.
4. Die Änderung in Prozent
In Spalte F rechnen Sie die Änderung aus und formatieren die Spalte als Prozent:
=WENN(ISTZAHL(E2);(D2-E2)/E2;"")
Dann sortieren Sie nach Spalte F absteigend. Die größten Erhöhungen stehen oben. Mit einer bedingten Formatierung, etwa rot ab 10 % und grün unter 0 %, sehen Sie die Ausreißer sofort. Sinkende Preise sind übrigens genauso interessant: Dort können Sie günstiger anbieten oder die zusätzliche Spanne mitnehmen.
5. Entfallene Artikel finden
Im Blatt Alt zeigt diese Formel, welche Artikel in der neuen Liste fehlen:
=WENN(ISTNV(VERGLEICH(A2;Neu!A:A;0));"entfällt";"")
Entfallene Artikel sind leicht zu übersehen, weil sie in der neuen Liste schlicht nicht mehr vorkommen. Für Ihren Restbestand und die Nachbestellung sind sie aber wichtig. Oft gibt es einen Nachfolgeartikel mit neuer Nummer, den der Lieferant als neuen Artikel führt.
6. Die neue Spanne dazurechnen
Wenn Sie Ihren aktuellen Verkaufspreis brutto in Spalte G eintragen oder aus Ihrer Warenwirtschaft holen, zeigt diese Formel die Spanne mit dem neuen Einkaufspreis bei 20 % Umsatzsteuer:
=WENN(ISTZAHL(G2);(G2/1,2-D2)/(G2/1,2);"")
Sortieren Sie nach dieser Spalte aufsteigend. Ganz oben stehen die Artikel, die Sie zuerst neu auszeichnen sollten. Wie Sie die Spanne lesen und den neuen Verkaufspreis rechnen, steht im Artikel Handelsspanne und Aufschlag berechnen.
Was Excel dabei nicht merkt
- Geänderte Packungsgrößen: Aus „100 Stück“ für 12,40 € werden „250 Stück“ für 29,50 €, der Preis steigt um 138 %. Die Formel meldet eine riesige Erhöhung, obwohl der Stückpreis sogar gesunken ist. Umgekehrt bleibt ein gleicher Preis für eine kleinere Packung unentdeckt. Mehr dazu: Versteckte Preiserhöhung durch Packungsgrößen.
- Doppelte Artikelnummern: XVERWEIS und SVERWEIS nehmen still den ersten Treffer.
- Geänderte Spalten: Hat der Lieferant eine Spalte eingefügt, stimmt die Spaltennummer im SVERWEIS nicht mehr, und Sie vergleichen plötzlich den Rabatt mit dem Preis.
- Der Aufwand: Jede neue Liste hat einen etwas anderen Aufbau, und die Formeln beginnen von vorne. Bei fünf Lieferanten mit zwei Listen im Jahr sind das zehn Nachmittage.
Das Ganze in einer Minute: Preiswacht erledigt diese Schritte automatisch, merkt sich die Spalten jedes Lieferanten, erkennt Packungswechsel, Dubletten und gekippte Spannen und erstellt die neuen Verkaufspreise als Datei für Ihre Kasse.
Häufige Fragen
Funktioniert das auch mit LibreOffice oder Google Tabellen?
Ja. SVERWEIS, VERGLEICH, WENNFEHLER und GLÄTTEN gibt es dort ebenfalls, je nach Spracheinstellung auch unter den englischen Namen wie VLOOKUP. XVERWEIS (XLOOKUP) gibt es in Google Tabellen und in LibreOffice ab Version 24.8. Achten Sie auf das Trennzeichen in Formeln: In der deutschen Einstellung ist es meist das Semikolon.
Was mache ich, wenn der Lieferant die Liste als PDF schickt?
Fragen Sie zuerst nach einer Excel- oder CSV-Datei. Die meisten Großhändler haben sie, schicken aber standardmäßig das PDF. Das Abtippen oder Kopieren aus einem PDF ist fehleranfällig, besonders bei Artikelnummern mit führenden Nullen.
Warum findet SVERWEIS meinen Artikel nicht, obwohl er in beiden Listen steht?
Fast immer, weil die Artikelnummer einmal als Zahl und einmal als Text gespeichert ist, oder weil ein Leerzeichen am Ende steht. Vereinheitlichen Sie die Spalte mit einer Hilfsspalte wie =GLÄTTEN(TEXT(A2;"0")) in beiden Listen und suchen Sie über diese Hilfsspalte.
Wie finde ich doppelte Artikelnummern in der Liste?
Mit =ZÄHLENWENN(A:A;A2) in einer Hilfsspalte. Alles über 1 kommt mehrfach vor. Klären Sie Dubletten, bevor Sie vergleichen, sonst nimmt die Formel still den ersten Treffer.
Wie oft sollte man Preislisten vergleichen?
Bei jeder neuen Liste, bevor Sie die Preise übernehmen. Viele Großhändler ändern ihre Preise ein- bis zweimal im Jahr, einzelne Warengruppen auch öfter. Wer erst beim nächsten Inventurabschluss vergleicht, hat Monate mit zu niedriger Spanne verkauft.