Preise bei verschiedenen Artikeln und Mengen zuordnen
Schritt-für-Schritt-Anleitung
-
Daten vorbereiten: Erstelle zwei Tabellenblätter. Im ersten Blatt (z.B. "Auswertung") trägst du die Artikel und die gekaufte Menge ein. Im zweiten Blatt (z.B. "Staffel 1" oder "Staffel 2") definierst du die Preisstaffeln für die Artikel.
-
Formeln anwenden:
- In Spalte D (Staffel) der "Auswertung" verwendest du die Formel:
=WENN(B2<BEREICH.VERSCHIEBEN(Staffel!$B$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); BEREICH.VERSCHIEBEN(Staffel!$B$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); WVERWEIS(B2;BEREICH.VERSCHIEBEN(Staffel!$B$2:$F$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); 1;WAHR))
- In Spalte E (Preis) nutze die folgende Formel:
=INDEX(BEREICH.VERSCHIEBEN(Staffel!$G$2:$K$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); 0;VERGLEICH(D2;BEREICH.VERSCHIEBEN(Staffel!$B$2:$F$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); 0))
-
Nächste Staffel ermitteln: Um die nächst höhere Staffel und den Preis anzuzeigen, kannst du in Spalte F und G die folgenden Formeln verwenden:
- Nächste Staffel:
=WENN(D2=MAX(BEREICH.VERSCHIEBEN(Staffel!$C$2:$F$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0)); D2;INDEX(BEREICH.VERSCHIEBEN(Staffel!$C$2:$F$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); 0;VERGLEICH(D2;BEREICH.VERSCHIEBEN(Staffel!$B$2:$F$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); 0))
- Preis der nächsten Staffel:
=INDEX(BEREICH.VERSCHIEBEN(Staffel!$G$2:$K$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); 0;VERGLEICH(F2;BEREICH.VERSCHIEBEN(Staffel!$B$2:$F$2;VERGLEICH(A2;Staffel!$A$3:$A$5;0); 0); 0))
Häufige Fehler und Lösungen
-
Fehler: „#NV“ in den Zellen: Dies kann auftreten, wenn die Artikelbezeichnung nicht exakt mit der in der Staffel-Tabelle übereinstimmt. Überprüfe die Schreibweise und stelle sicher, dass alle Artikel korrekt eingetragen sind.
-
Fehler: Falsche Preise angezeigt: Achte darauf, dass der Zellbereich der Preisstaffeln korrekt in den Formeln angegeben ist. Ein häufiger Fehler ist die falsche Referenzierung der Zellen.
Alternative Methoden
Falls du nicht mit den oben genannten Formeln arbeiten möchtest, kannst du auch die SVERWEIS-Funktion nutzen. Diese ist möglicherweise einfacher zu verstehen, funktioniert jedoch nur, wenn die Daten in einer bestimmten Reihenfolge vorliegen.
Ein Beispiel für die SVERWEIS-Formel könnte wie folgt aussehen:
=SVERWEIS(B2;Staffel!$B$2:$F$10;2;WAHR)
Praktische Beispiele
Angenommen, du hast folgende Daten in der Staffel-Tabelle:
| Artikel |
Menge |
Preis |
| aaa |
10.000 |
1.00 |
| aaa |
20.000 |
0.90 |
| aaa |
30.000 |
0.80 |
Wenn du für den Artikel „aaa“ 15.000 Einheiten kaufst, würde die Formel in Spalte D den Wert „10.000“ und in Spalte E den Preis „1.00“ anzeigen. Die nächste Staffel würde dann „20.000“ und der Preis „0.90“ in den Spalten F und G anzeigen.
Tipps für Profis
-
Namen definieren: Verwende benannte Bereiche für die Artikel und Preisstaffeln, um die Formeln übersichtlicher zu gestalten.
-
Datenüberprüfung: Setze Datenüberprüfungen für die Menge ein, um sicherzustellen, dass nur gültige Werte eingegeben werden.
-
Szenarien testen: Nutze das Szenario-Manager-Tool in Excel, um verschiedene Preisstaffeln und Mengen zu simulieren und die Ergebnisse zu analysieren.
FAQ: Häufige Fragen
1. Frage
Wie kann ich die Formeln anpassen, wenn ich mehr Artikel habe?
Antwort: Du kannst die Bereiche in den Formeln erweitern, indem du die Zellreferenzen anpasst, um alle Artikel abzudecken.
2. Frage
Gibt es eine Möglichkeit, die Formeln zu vereinfachen?
Antwort: Ja, du kannst die Formeln in Zwischenschritte aufteilen und die Ergebnisse in Hilfsspalten speichern, um die Hauptformeln zu vereinfachen.