Microsoft Excel

Herbers Excel/VBA-Archiv

kgrösste aus Datumsbereich

Betrifft: kgrösste aus Datumsbereich von: Robert Amstutz
Geschrieben am: 15.10.2020 18:43:48

Hallo liebe Excelfreunde

Ich habe das Forum durchsucht, jedoch nichts gefunden. Vielleicht habe ich auch nicht nach dem richtigen Begriff gesucht.


ich habe eine Tabelle, wo in Spalte B ein fortlaufendes Datum (mittlerweile ca. 20 Jahre) steht. In Spalte C werden täglich zum Datum die Gästezahlen einer Transportbahn notiert. In Spalte D das gleiche einfach für eine weitere Bahn, und so weiter und so fort.


Nun möchte bei einer Auswertung die grösste Frequenz (grössten Wert) innerhalb eines Datumbereichs ermitteln. So steht z.B. in Zelle Zelle H5 01.02.2019. Nun soll mir die Formel die grösste Frequenz im Bereich aus Zelle H5 bis zum Monatsende aus H5 herausgeben.


Ich habe es mit Aggregat, maxwenns, kgrösste versucht aber ich komme nicht auf den kleinsten Nenner um diese Zahl zu ermitteln. Kann mir da jemand helfen?


Gerne würde ich die Tabelle hochladen, aber bei mir kommt dabei immer eine Fehlermeldung.


Grüsse


Robert



Betrifft: AW: z.B. mit AGGREGAT() ...
von: neopa C
Geschrieben am: 15.10.2020 18:57:32

Hallo Robert,

... so: =AGGREGAT(14;6;C2:C9999/($B2:$B9999>=$H5)/($B2:$B9999<=MONATSENDE($H5;0));1) und Formel nach rechts ziehend kopieren für die anderen Bahnen.

Gruß Werner
.. , - ...

Betrifft: AW: z.B. mit AGGREGAT() ...
von: Robert Amstutz
Geschrieben am: 15.10.2020 19:35:25

Werner, wow
Danke vielmals für die super tolle Unterstützung. Genau was ich gesucht habe.
Liebe Grüsse Robert

Betrifft: AW: bitteschön owT
von: neopa C
Geschrieben am: 15.10.2020 20:11:02

Gruß Werner
.. , - ...

Betrifft: AW: kgrösste aus Datumsbereich
von: Daniel
Geschrieben am: 15.10.2020 19:07:32

Hi
wenn die Liste nach Datum sortiert ist:

=max(index(C:C;Vergleich(h5;B:B;1)):Index(D:D;Vergleich(Monatsende(h5;0);B:B;1)))

Gruß Daniel

Betrifft: AW: kgrösste aus Datumsbereich
von: Robert Amstutz
Geschrieben am: 15.10.2020 19:36:42

Danke vielmals Daniel
Du hast mir richtig geholfen. Genau was ich suche. Dass ich da nicht selber draufgekommen bin? Na ja...
Liebe Grüsse, Robert

Betrifft: AW: Du hast sicher beachtet ...
von: neopa C
Geschrieben am: 15.10.2020 20:18:24

Hallo Robert,

... dass mein Formelvorschlag eine Auswertung je Datenspalte vornimmt und Daniel die Auswertung über alle (im Beispiel 2) vornimmt. Beide Formeln lassen sich leicht auf die jeweilige andere Variante umstellen.

Daniels Vorschlag sollte performanter sein. Mein Formelvorschlag würde dagegen auch funktionieren, wenn die Daten in Spalte B nicht sortiert sind.

Gruß Werner
.. , - ...

Betrifft: AW: kgrösste aus Datumsbereich
von: chris b.
Geschrieben am: 15.10.2020 19:51:07

Hallo Daniel,
ich bin Excel Anfänger und will mich aber auch immer etwas informieren und besser auskennen.
Könntest du mir die Formel vielleicht mal in eine Datei packen und hier hochladen damit ich Sie mir mal anschauen kann. Also was sie macht und wie sie funktioniert.
Ich schaue mir auch immer die Excel fragen hier an in der Hoffnung auch mal helfen zu können aber leider kapiere ich meistens nicht einmal was der Fragensteller haben möchte.
Wäre toll wenn du das machen könntest. Vielen Dank dafür !
Grüße Chris

Betrifft: AW: kgrösste aus Datumsbereich
von: Daniel
Geschrieben am: 15.10.2020 19:55:14

hi
ich hab da keine Datei.
du könntest höchsten Robert nochmal bitten, sich die Anweisungen auf der Uploadseite nochmal genau durchzulesen und die Datei trotzdem hochzuladen.

die Formel macht nichts anderes, als die Zeilennummer des gegeben Datums und die Zeilennummer des Datums zum Monatsende zu ermitteln und dann mit dem Zellbereich zwischen diesen beiden Zeilen die Max-Auswertung befüllen.

Gruß Daniel

Betrifft: AW: kgrösste aus Datumsbereich
von: chris b.
Geschrieben am: 15.10.2020 19:58:55

Ich danke Dir und mache da jetzt kein größeres Fass auf...
Aber erklär mal das ?
die Formel macht nichts anderes, als die Zeilennummer des gegeben Datums und die Zeilennummer  _
des Datums zum Monatsende zu ermitteln und dann mit dem Zellbereich zwischen diesen beiden Zeilen die Max-Auswertung befüllen.
Also in welche beiden Zellen müsste ich Werte eingeben um diese Formel zu testen ?

Betrifft: AW: kgrösste aus Datumsbereich
von: Daniel
Geschrieben am: 15.10.2020 20:13:27

du musst in Spalte B eine Zahlenreihe mit Datumswerten eingeben, die mindestens 3 Monate umfasst und dann den 1. des mittleren Monats als Suchwert verwenden.

Gruß Daniel

Betrifft: AW: kgrösste aus Datumsbereich
von: chris b.
Geschrieben am: 15.10.2020 20:17:48

Daniel ich Danke Dir !

Betrifft: Monatserster bis Monatsende = kompletter Monat
von: WF
Geschrieben am: 15.10.2020 20:50:13

Hi,

{=MAX((MONAT(B:B)=MONAT(H5))*(JAHR(B:B)=JAHR(H5))*C:C)}

Oder war Dein Beispiel unglücklich gewählt?

WF

Betrifft: AW: Monatserster bis Monatsende = kompletter Monat
von: Daniel
Geschrieben am: 16.10.2020 11:15:18

unglücklich finde ich, ganze Spalten in Matrixformeln einzusetzen.
das führt zu hohen Berechnungszeiten, weil Excel dann jede Zeile berechnet und das sind mittlerweile mit c.a 1,04 Millionen sehr viele.
Besser ist es, in Matrixformeln die Zellbereiche so exakt wie möglich anzugeben.
Gruß Daniel

Betrifft: AW: Monatserster bis Monatsende = kompletter Monat
von: Robert Amstutz
Geschrieben am: 16.10.2020 12:55:53


Ich nehme die Daten aus einer intelligenten Tabelle und somit hält sich der Range noch in einem benutzerfreundlichen Rahmen.

Gruss Robert

Betrifft: AW: eine derartige Matrixformel wie hier, ...
von: neopa C
Geschrieben am: 16.10.2020 11:20:11

Hallo Robert, hallo Chris,

... die über den gesamten Zeilenbereich auswerten soll, ergibt das gleiche Ergebnis wie z.B. mein Formelvorschlag, der auch eine Matrixformel ist, nur in Excelversionen vor Office365 im Gegensatz zu der aufgezeigten MAX()-Formel keinen spez. Formeleingabeabschluss benötigt. Wenn man eine derartige Formeln definiert, sollte man aus Performansegründen möglichst nur die Auswertung auf den max notwendig auszuwertenden Bereich beschränken.

Und wenn hier die Datumswerte sortiert sein sollten, dann ist mE auch der Formelansatz effektiver, den Daniel vorgeschlagen hat, wohl egal in welcher XL-Version.

Gruß Werner
.. , - ...

Betrifft: AW: eine derartige Matrixformel wie hier, ...
von: chris b.
Geschrieben am: 16.10.2020 11:22:41

Dankeschön !

Betrifft: AW: bitteschön owT
von: neopa C
Geschrieben am: 16.10.2020 11:54:21

Gruß Werner
.. , - ...

Betrifft: die üblichen Mäkeleien
von: WF
Geschrieben am: 16.10.2020 13:09:53

und darauf dann die üblichen Verlängerungen:

=MAX(INDEX((MONAT(B1:B111)=MONAT(H5))*(JAHR(B1:B111)=JAHR(H5))*C1:C111;0))

WF

Betrifft: AW: die üblichen Mäkeleien
von: Daniel
Geschrieben am: 16.10.2020 13:14:23

was nützt eine kurze Formel, wenn sie lang und vorallem unnötigweise lang rechnet?
schnelles Rechnen sollte das Ziel sein, das macht sich bei jeder Anwendung der Datei bemerkbar, Kurze Formeln nur einmal beim Eingeben.