Office: (Office 365) Verweis mit Datumszeitraum

Hallo,


Ich habe folgendes Problem:

Ich muss aus meiner Tabelle den Erzeuger-Preis auslesen(Das ist der Preis der dem Kunden in Rechnung gestellt wird). Dieser Preis ist abhängig vom von mir generierten eindeutigen Kundenstring und vom Leistungsdatum.


In einer zweiten Tabelle habe ich Preislisten. zB. 100€ vom 01.01.2023 bis 31.01.2023.


Wenn ich nun einen Verweis verwende habe ich das Problem, dass ich nur nach einem Datum, aber nicht nach einem Datumszeitraum suchen kann.

Normalerweise kann ich dann die Eigenschaft des Verweises nutzen, dass er immer das kleinste Datum dass er noch findet heranzieht.

=VERWEIS([@[Eindeutiger Name]] & [@Datum];Preistabelle[Eindeutiger Name]&Preistabelle[Gültig von];Preistabelle[Erzeugerpreis])

Das klappt aber nur so lange wie die Preistabelle nach Datum sortiert ist. Das ist Sie aber sobald mit dieser Datei gearbeitet ist nicht der Fall. Und genau hier hänge ich fest.

Siehe Eindeutiger Name "Kunde1Entsorger1Spedition1"
Hier haben wir 3 Versionen/Eintragungen in der Preistabelle. Je nach Datum verändert sich der Preis.(zweites Bild Zeile 2-4)


TLDR:
Wie kann ich den Preis zu einem bestimmten Tag auslesen, wenn die Quelltabelle einen Zeitraum angibt und nicht jeden Tag separat auflistet?
Das Ganze sollte so umsetzbar sein, dass die Lösung endlos mitwächst, dementsprechend nicht manuell nachgebessert und verändert werden muss.

P.S:
Der Preis hängt in meinem Fall von vier Variablen ab. Erzeuger(oder auch Kunde), Transporteur(oder auch Spedition) und Entsorgungsanlage habe ich mit & vereint um eine eindeutige Zuordnung der Preise gewährleisten zu können.
Vertraglich bedingt kann sich der Preis aber zu einem Stichtag ändern (Variable4).


Vielen Dank für eure Mühen,

Patrick

 

Hola,
=WENNFEHLER(INDEX('Preise 2023'!$E$2:$E$13;AGGREGAT(15;6;ZEILE('Preise 2023'!$E$2:$E$13)-1/(('Preise 2023'!$B$2:$B$13=D2)*('Preise 2023'!$K$2:$K$13<=E2)*('Preise 2023'!$L$2:$L$13>=E2));1));"")
Gruß,
steve1da

 

Danke, das muss ich jetzt erstmal verarbeiten und kapieren. ;)

 

Hallo Tyrannon,

die von steve1da aufgezeigte Formel kann man noch verkürzen, da es INDEX() hierzu nicht bedarf.

Aber es geht analog sehr wohl auch mit VERWEIS() und zwar auch ohne spezifische Sortierung der Daten.
Nachfolgend nur die VERWEIS()-Formel, die auch Deiner bisher noch nicht realisierten Forderung

Genüge trägt:

=WENNFEHLER(VERWEIS(9;1/(Preistabelle[Eindeutiger Name]=D2)/(Preistabelle[Gültig von]<=E2)/(Preistabelle[Gültig bis]>=E2);Preistabelle[Erzeugerpreis]);"")

Übrigens hätte es Deiner Hilfsspalten "Eindeutiger Name" dafür auch nicht bedurft. Die dort vorgenommene Verkettung hätte auch in der Formel integriert werden können.

 

Hallo,
Danke für die alternative Lösung.
Kannst du mir erklären wie du auf die "9" kommst und was da passiert?-> VERWEIS(9;1/(Preista.....

 

Hallo Tyrannon,

wichtig bei dieser spezifischen VERWEIS()- Formel ist, dass das 1. Argument größer sein sollte als der größte Nichtfehlerwert der Matrix für das 2. Argument der VERWEIS()-Formel. Die Matrix des 2. Argument ergibt sich durch: 1/[Bedingungsvergleichteilformel(n)] und somit nur aus 1sen und Fehlerwerten (#DIV/0!). Die Fehlerwerte ignoriert VERWEIS() und somit ist die größte Teilzahl der Matrix max eine 1. Als erstes Argument der Formel kann somit als Vergleichswert z.B. 1,1 oder 2 oder ... eingesetzt werden. In meinen entsprechenden Formeln verwende ich die 9.