Beitrag aus SmartTools Excel Weekly
Aktuellen Einkaufspreis eines Artikels ermitteln
Excel 365 2024 2021
FRAGE In einem Tabellenblatt gebe ich die Einkaufspreise unserer für die Produktion benötigten Artikel ein: In Spalte A steht das Datum, in Spalte B die Teilenummer und in Spalte C der Einkaufspreis. Wenn sich neue Einkaufsmöglichkeiten ergeben oder wenn sich der Preis ändert, ersetze ich aber nicht einfach den letzten Eintrag eines Artikels. Vielmehr erweitere ich die Liste um eine neue Zeile mit dem entsprechenden Datum, der Teilenummer und dem neuen Preis. Nun möchte ich für alle Teile den aktuellen Einkaufspreis ermitteln. Das ist nicht automatisch der höchste Preis, weil der Einkaufspreis theoretisch auch sinken kann. Eigentlich brauche ich den Preis mit dem letzten/höchsten Datum der jeweiligen Teilenummer. Kann ich meine Liste so auswerten, dass ich zu jeder Teilenummer den aktuellen Preis erhalte?
B. Niemann
ANTWORT Mit den dynamischen Arrayfunktionen der neueren Excel-Versionen ist das problemlos möglich. Damit können Sie zunächst eine sortierte Liste aller erfassten Teilenummern erstellen – also eine Liste, in der jede Teilenummer nur einmal vorkommt. Und dann lassen Sie mit einer dynamischen Arrayformel zu jeder Teilenummer den letzten Preis ausgeben – also den Preis mit dem aktuellsten Datum.
Nehmen wir an, dass Sie die Einkaufspreise im Bereich A4:C750 erfasst haben. Die Datumswerte stehen in Spalte A im Bereich A4:A750, die Teilenummern stehen im Bereich B4:B750 und die jeweiligen Einkaufspreise stehen im Bereich C4:C750. Nun wollen Sie in den Spalten F und G die aktuellen Einkaufspreise ausgeben lassen: die sortierte Liste der Teilenummern in F4 und darunter und die jeweiligen Einkaufspreise in G4 und darunter.
Die sortierte Liste der Teilenummern erhalten Sie mit einer einzigen Formel, die Sie in Zelle F4 eingeben:
=SORTIEREN(EINDEUTIG(B4:B750))
Die Funktion EINDEUTIG sorgt dafür, dass alle Teilenummern im Bereich B4:B750 nur einmal vorkommen. Und die Funktion SORTIEREN sortiert die Teilenummern aufsteigend.
Diese gefilterte Liste der Teilenummern können Sie nun als Kriterium für die Suche nach dem aktuellsten Einkaufspreis verwenden. Da es sich beim Einkaufspreis um einen Zahlenwert handelt, bietet sich dafür eine SUMMEWENNS-Funktion an, denn es kann nur einen Einkaufspreis geben, der mit der Teilenummer und dem höchsten Datum zu dieser Teilenummer übereinstimmt. Insofern entspricht die Summe dem jeweiligen Einzelwert.
Geben Sie folgende Formel in Zelle G4 ein, um den letzten Einkaufspreis jeder Teilenummer zu ermitteln:
=SUMMEWENNS(C4:C750;A4:A750;MAXWENNS(A4:A750;
B4:B750;F4#);B4:B750;F4#)
Die Formel greift mehrfach auf den Überlaufbereich der dynamischen Arrayformel in Zelle F4 zurück – also auf die eindeutigen Teilenummern. Damit alle Teilenummern berücksichtigt werden, schließen Sie den gesamten Überlaufbereich ein, indem Sie das #-Zeichen an die Adresse der Formelzelle anhängen: F4#.
So sucht die SUMMEWENNS-Funktion im Bereich C4:C750 den Preis, der das höchste Datum einer Teilenummer besitzt:
A4:A750;MAXWENNS(A4:A750;B4:B750;F4#)
und bei dem gleichzeitig die Teilenummer mit dem aktuellen Eintrag in der Ausgabeliste übereinstimmt:
B4:B750;F4#
Das Ergebnis ist eine Liste der Teilenummern mit ihren aktuellen Einkaufspreisen.
