Sverweis mit PLZ für Frachtkostenberechnung
Schritt-für-Schritt-Anleitung
-
Daten vorbereiten: Stelle sicher, dass du eine Tabelle mit Frachtarifen hast. Die Tabelle sollte mindestens die Spalten für die PLZ und die entsprechenden Frachtkosten enthalten.
-
PLZ formatieren: Achte darauf, dass die Postleitzahlen als Text formatiert sind, besonders wenn sie führende Nullen haben (z.B. Halle PLZ 06118). Gehe dazu auf die Zelle, wähle "Format Cells" und dann "Text".
-
SVERWEIS-Formel einfügen: Nutze die folgende Formel in der Zelle, in der du die Frachtkosten berechnen möchtest:
=SVERWEIS(LINKS(C2;1)*1;Frachttarife;3;FALSCH)*A2
Hierbei wird die Postleitzahl (C2) auf die erste Ziffer reduziert und mit den Frachtarifen abgeglichen.
-
Formel nach unten ziehen: Ziehe die Formel nach unten, um die Frachtkosten für alle PLZ in deiner Liste zu berechnen.
Häufige Fehler und Lösungen
-
Falsche PLZ-Formatierung: Wenn die PLZ nicht korrekt erkannt wird, überprüfe die Formatierung. Sie sollte als Text gespeichert sein.
-
SVERWEIS gibt #NV aus: Dies tritt auf, wenn die PLZ nicht in der Frachtariftabelle vorhanden ist. Stelle sicher, dass alle benötigten PLZ (z.B. 10000, 99999) in deiner Tabelle enthalten sind.
-
Formel gibt falsche Werte zurück: Überprüfe, ob die Spaltenreferenzen in der Formel korrekt sind und dass die PLZ den richtigen Bereich abdeckt.
Alternative Methoden
Eine Alternative zum SVERWEIS ist die Verwendung von INDEX und VERGLEICH. Diese Kombination kann flexibler sein:
=INDEX(Frachttarife;VERGLEICH(LINKS(C2;1)*1;Frachttarife!A:A;0);3)
Hierbei wird ebenfalls die erste Ziffer der PLZ verwendet, um den entsprechenden Frachtpreis zu finden.
Praktische Beispiele
Angenommen, du hast die folgende Frachtarife-Tabelle:
| PLZ |
Frachtkosten |
| 10 |
50 |
| 11 |
55 |
| 12 |
60 |
| 57 |
70 |
| 99 |
80 |
Wenn du in deiner Excel-Tabelle die PLZ 5200 eingibst, wird die Formel =SVERWEIS(LINKS(C2;1)*1;Frachttarife;2;FALSCH) dir die Frachtkosten für die Zone 5 zurückgeben.
Tipps für Profis
-
Verwendung von Named Ranges: Benenne deinen Bereich für Frachtarife, sodass du die Formeln leichter lesen und verwalten kannst. Zum Beispiel: =SVERWEIS(LINKS(C2;1)*1;Frachtarife;2;FALSCH) wird klarer, wenn du Frachtarife als benannten Bereich verwendest.
-
Datenvalidierung: Verwende Datenvalidierung für die Eingabe von PLZ, um sicherzustellen, dass nur gültige Werte eingegeben werden.
-
Dynamische Bereiche: Überlege, ob du mit dynamischen Bereichen arbeitest, um deine Daten aktuell zu halten, besonders wenn neue PLZ oder Frachtkosten hinzukommen.
FAQ: Häufige Fragen
1. Wie gehe ich mit PLZ um, die mit 0 beginnen?
Um sicherzustellen, dass führende Nullen angezeigt werden, formatiere die PLZ als Text. Verwende die Funktion LINKS() in deiner Formel, um die ersten Ziffern zu extrahieren.
2. Welche Excel-Version ist erforderlich?
Die beschriebenen Funktionen sind in den meisten modernen Excel-Versionen verfügbar, einschließlich Excel 2010 und neuer. Stelle sicher, dass du eine Version verwendest, die diese Funktionen unterstützt.