Zeile mit nächstgelegenem Datum finden

[Binary91]

Hallo zusammen,

ich habe eine Tabelle mit drei Spalten:
A = Parkhaus
B = Gebühren
C = Datum

In einer zweiten Tabelle stehen aufgelistet verschiedenste Parkhausbesuche an verschiedenen Tagen mit jeweiligem Datum:
A = Datum
B = Parkhaus

Nun geht es darum, die Gesamtgebühren für das Jahr X zu errechnen. Dafür sollte jedem Eintrag aus Tabelle2 der korrespondierende Gebührenbetrag zugeordnet werden.

Das knifflige ist, dass ein Parkhaus mehrmals in Tabelle1 vorkommen kann, da sich dessen Gebühren während des Jahres verändert haben können.

Es geht nun also darum, aus Tabelle1 alle Zeilen mit dem korrespondierenden Parkhaus zu suchen und alle diese Zeilen dann nach dem zum Datum aus Tabelle2 nächstgelegenen Datum zu durchsuchen.

Mit einer Matrixformel habe ich bereits herausgefunden, wie man das am naheliegensten Datum herausfinden kann über:

Code:

=MAX((DatumSpalte<=DatumAktuell)*DatumSpalte)

Allerdings weiß ich nicht, wie ich diese Formel nur über diejenigen Zeilen laufen lassen kann, in denen das gesuchte Parkhaus X steht.
So wie ich es bisher habe, liefert mir die Formel einfach das nächstgelegene Datum der gesamten Tabelle.

Hat jemand verstanden was ich vorhabe?

Vielen Dank bereits vorab.

PS: Bitte keine VBA-Lösungen

MfG
Martin

[shift-del]

Hallo Martin

Code:

=VERWEIS(42;1/(Tabelle1!$A$2:$A$100=B2)/(Tabelle1!$C$2:$C$100<=A2);Tabelle1!$B$2:$B$100)

Voraussetzung ist dass in Tabelle1 innerhalb eines Parkhauses das Datum aufsteigend sortiert ist.

[Binary91]

Hallo und danke für die schnelle Antwort.

Die Idee ist mir auch bereits gekommen, das erspart mir sogar die Matrixformel. Allerdings können auch nachträglich Einträge in die Tabelle erfolgen und somit kann eine sortierte Datumreihenfolge nicht garantiert werden.

Allerdings müsste es doch über ein paar Extraspalten mit automatischer Sortierung möglich sein, oder?

Stellt sich nur noch die Frage, wie ich über eine Formel sortiere.

Ich nutze parallel die SuFu, allerdings bin ich für Antworten trotzdem dankbar.

MfG

[WillWissen]

Hi Martin,

Allerdings müsste es doch über ein paar Extraspalten mit automatischer Sortierung möglich sein, oder?
Stellt sich nur noch die Frage, wie ich über eine Formel sortiere.

Mein Vorschlag:

Tabelle3

	A	B	C
1	Namen	HS	Namen sort.
2	Friedrich	6	Anton
3	Ida	9	Berta
4	Berta	2	Cäsar
5	Heinrich	8	Dora
6	Anton	1	Emil
7	Cäsar	3	Friedrich
8	Dora	4	Gustav
9	Emil	5	Heinrich
10	Gustav	7	Ida

Formeln der Tabelle

Zelle Formel B2 =ZÄHLENWENN($A$2:$A$10;"<"&$A2)+ZÄHLENWENN($A$2:$A2;"="&$A2) C2 =WENN(LÄNGE($A2)<>0;INDEX($A$2:$A$10;VERGLEICH(ZEILE(A1);$B$2:$B$10;0);1);"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[shift-del]

Die Idee ist mir auch bereits gekommen, das erspart mir sogar die Matrixformel.

Äh... das ist eine Matrixformel.

Allerdings können auch nachträglich Einträge in die Tabelle erfolgen und somit kann eine sortierte Datumreihenfolge nicht garantiert werden.

Du meinst im Jahre 2018 fällt jemanden ein dass am 04.08.2014 im Parkhaus am Park die Gebühr geändert wurde? Ein bisschen spät, oder?

[Binary91]

@WillWissen:
Super, danke! Die Indizes mit der Zeilennummer zu vergleichen ist praktisch.

Äh... das ist eine Matrixformel.

Das erklärt warum sie nicht funktionert hat.

Du meinst im Jahre 2018 fällt jemanden ein dass am 04.08.2014 im Parkhaus am Park die Gebühr geändert wurde? Ein bisschen spät, oder?

Es geht nicht um Parkhäuser und Parkgebühren. Einige Rechnungen kommen Monate später als andere und wenn sich Gebühren von März im Mai geändert haben, allerdings mittlerweile Oktober ist und schon hunderte weitere Einträge existieren, dann wird man sicher nicht das rumschieben anfangen

[shift-del]

Das erklärt warum sie nicht funktionert hat.

Genau, Matrixformeln funktionieren ja nie.

Es geht nicht um Parkhäuser und Parkgebühren.

Huch?! Dann fällt das leider nicht mehr in meine Zuständigkeit.

[Binary91]

@WillWissen:
Ein Problem ergibt sich bei der Sortierformel noch für mich. Leider kommen manche Daten mehrmals vor und somit kann nicht mehr jede Id einer Zeile zugeordnet bzw. doppelt zugeordnet werden.

Gibt es eine Möglichkeit, doppelte Werte zu inkrementieren und darauf basierend eben die darauffolgenden Werte ebenfalls? Mir fällt leider kein Weg ein, das zu realisieren.

Dann fällt das leider nicht mehr in meine Zuständigkeit.

Dachte ich mir.

[WillWissen]

Hi,

warum nicht?

Tabelle1

	A	B	C	D	E
1	ID	Namen	HS	ID	Namen sort.
2	100	Friedrich	7	102	Anton
3	101	Ida	9	102	Anton
4	102	Anton	1	104	Berta
5	103	Heinrich	8	104	Berta
6	104	Berta	3	105	Cäsar
7	105	Cäsar	5	106	Emil
8	104	Berta	4	100	Friedrich
9	106	Emil	6	103	Heinrich
10	102	Anton	2	101	Ida

Formeln der Tabelle

Zelle Formel C2 =ZÄHLENWENN($B$2:$B$10;"<"&$B2)+ZÄHLENWENN($B$2:$B2;"="&$B2) D2 =INDEX(A:A;VERGLEICH(E2;B:B;0)) E2 =WENN(LÄNGE($B2)<>0;INDEX($B$2:$B$10;VERGLEICH(ZEILE(B1);$C$2:$C$10;0);1);"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Es wird jeweils die entsprechende ID zugeordnet. Ich setze natürlich voraus, dass es für den jeweiligen Begriff auch die jeweilige eindeutige ID gibt.

[Binary91]

Hi,

danke für die schnelle Rückmeldung!

Das ist ja klasse! Danke, so passt das Ganze.

Liebe Grüße und einen schönen Sonntag wünsche ich :19: