Microsoft Excel

Herbers Excel/VBA-Archiv

Letzter Wert vor 6 Uhr

Betrifft: Letzter Wert vor 6 Uhr von: Tom
Geschrieben am: 08.09.2020 10:44:13

Hallo zusammen,

ich benötige eure Hilfe!
Und zwar möchte ich mittels einer Formel in Excel auf einen bestimmten Wert in einer Spalte zugreifen.
Der gewünschte Wert befindet sich in Spalte M.
Folgende Bedingungen müssen dabei erfüllt sein:
Das Datum in Spalte A muss einem vorgegebenen Datum, das in Zelle X1 steht entsprechen, und es muss der letzte Wert sein, der "vor 6 Uhr" geschrieben wird.

Könnt ihr mir weiterhelfen?

Vielen Dank vorab!

Grüße Tom

Betrifft: AW: Letzter Wert vor 6 Uhr
von: Tom
Geschrieben am: 08.09.2020 10:45:34

Die Uhrzeit steht in Spalte C, das habe ich vergessen zu erwähnen!

Betrifft: AW: ja wir können helfen, doch ...
von: neopa C
Geschrieben am: 08.09.2020 10:47:08

Hallo Tim,

... (D)eine kleine Beispieltabelle wäre hilfreich(er) und zusätzlich die Aussage mit welcher XL-Version Du arbeitest.

Gruß Werner
.. , - ...

Betrifft: AW: Letzter Wert vor 6 Uhr
von: Daniel
Geschrieben am: 08.09.2020 10:55:13

Hi

Wo steht die Uhrzeit?
Ist die Liste nach Datum und Uhrzeit aufsteigend sortiert?
Wie schon geschrieben, eine Beispieldatei würde helfen.

Gruß Daniel

Betrifft: AW: Letzter Wert vor 6 Uhr
von: Tom
Geschrieben am: 08.09.2020 11:10:14

Ich habe euch eine Beispiel-Datei angehängt.

In Zelle X1 steht das gewünschte Datum.

Ich möchte jetzt den letzten Wert in Spalte M, der dem Datum aus X1 entspricht, und vor 6 Uhr liegt (Uhrzeit aus Spalte B).

In dem Falle liegt der Wert in Zeile 26 und der Wert lautet "83".

Ich hoffe es ist verständlich!

Vielen Dank vorab.

https://www.herber.de/bbs/user/140116.xlsx

Betrifft: AW: Letzter Wert vor 6 Uhr
von: Daniel
Geschrieben am: 08.09.2020 11:23:26

Hi
da die Liste sortiert ist:
1. Hilfsspalte in Spalte N mit der Formel (ab Zeile 2:) =A2+C2
2. Auswertung mit dieser Formel: =INDEX(M:M;VERGLEICH(X1+ZEIT(6;0;-1);N:N;1))

Gruß Daniel

Betrifft: AW: mit INDEX() und AGGREGAT() ...
von: neopa C
Geschrieben am: 08.09.2020 11:26:12

Hallo Tom,

... so: =INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M99)/(A2:A99=X1)/(C2:C99<=1/4);1))

was aber so nur ab XL2010 geht. In XL2007 ist die Formel etwas anders.

Gruß Werner
.. , - ...

Betrifft: AW: mit INDEX() und AGGREGAT() ...
von: Tom
Geschrieben am: 08.09.2020 11:52:43

Hallo Werner,

ich glaube es funktioniert!
Kannst du mir auch sagen, wie die Formel aussehen würde den letzten Wert vor 5:30 Uhr statt 6:00 Uhr?

Vielen Dank!!

Betrifft: AW: mit beliebiger Uhrzeit ...
von: neopa C
Geschrieben am: 08.09.2020 11:58:46

Hallo Tom,

... so: =INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M99)/(A2:A99=X1)/(C2:C99<="5:30"+0);1))

Gruß Werner
.. , - ...

Betrifft: AW: mit beliebiger Uhrzeit ...
von: Tom
Geschrieben am: 08.09.2020 12:07:58

Funktioniert perfekt!

Ich danke dir.

Gruß
Tom

Betrifft: AW: gerne, bitteschön owT
von: neopa C
Geschrieben am: 08.09.2020 12:13:20

Gruß Werner
.. , - ...

Betrifft: AW: gerne, bitteschön owT
von: Tom
Geschrieben am: 08.09.2020 12:31:49

Werner, eine Frage hätte ich noch, bei der du vielleicht ebenfalls helfen kannst!

Der gesuchte Wert bezog sich ja auf ein bestimmtes Datum, das in X1 angegeben wird.
Ist es möglich, immer den letzten Wert aus Spalte M vor 5:30, eines jeden Tages im Monat (Monat wird ja in Spalte B angegeben) aufzusummieren? Den Wunschmonat möchte ich in Y1 eingeben.

Wenn du dazu noch helfen könntest, wäre super!

Gruß Tom

Betrifft: AW: sollte auch realisierbar sein ...
von: neopa C
Geschrieben am: 08.09.2020 12:56:11

Hallo Tom,

... doch bevor ich mir selbst Testdaten zusammenstelle, stelle doch bitte doch Du eine Testdatei ein, die ein paar Daten von mindestens zwei verschiedenen Monaten beinhaltet und im Suchmonat mindestens Daten mit verschiedenen Zeiten in 3 Tagen hat. Dann sehe ich ich es mir an.

Gruß Werner
.. , - ...

Betrifft: AW: sollte auch realisierbar sein ...
von: Tom
Geschrieben am: 08.09.2020 13:28:39

Hallo Werner,

ich habe wie gewünscht eine Datei erstellt (Tabelle2).
https://www.herber.de/bbs/user/140122.xlsx

Es soll quasi die Summe der Werte in Spalte M gebildet werden, das dem in Y1 angegebenen Monat entspricht, aus jedem Tag, vor 5:30 Uhr.

Vielen Dank für deine Hilfe!!

Betrifft: AW: diese Daten sind nicht sortiert ...
von: neopa C
Geschrieben am: 08.09.2020 13:43:36

Hallo Tom,

... siehe A2:A4. Ist das nur der zusammengestellten Beispieldatei zuzuschreiben? Die Daten sollten jedenfalls nach Spalte +Spalte C aufwärts sortiert sein.

Dann wäre die einfachste und wohl auch die schnellste Lösung mit einer Hilfsspalte z.B. in Spalte N und darin folgender Formel in N2:

=(C2<="5:30"+0)*(C3>"5:30"+0)*(A2=A3)*(TEXT(A2;"MJ")=TEXT(Y$1;"MJ"))

und diese nach unten kopieren.

Dann einfach: =SUMMEWENN(N:N;1;M:M)

Gruß Werner
.. , - ...

Betrifft: AW: diese Daten sind nicht sortiert ...
von: Tom
Geschrieben am: 08.09.2020 14:00:37

Gibt es noch eine elegante(re) Lösung ohne Hilfsspalte?

Ich danke dir natürlich erstmal für deine Mühe!

Gruß Tom

Betrifft: AW: wie viele Datensätze liegen ca. max vor ?
von: neopa C
Geschrieben am: 08.09.2020 14:06:29

Gruß Werner
.. , - ...

Betrifft: AW: wie viele Datensätze liegen ca. max vor ?
von: Tom
Geschrieben am: 08.09.2020 14:11:22

Die Liste wird fortlaufend geschrieben, also können es schon ca. 100.000 Zeilen werden.
Eine andere Möglichkeit wäre gegebenenfalls noch, Die Formel, die du angegeben hast bis zur "allerletzten" Zeile fortzuführen. Wie würde das am Geschicktesten funktionieren?

Alternativ wäre natürlich schön, eine Lösung ohne die Hilfsspalte.

Gruß Tom

Betrifft: AW: die Hilfsspaltenformel nach unten kopieren ...
von: neopa C
Geschrieben am: 08.09.2020 14:18:20

Hallo Tom,

.. . dazu bedarf es lediglich eines Doppelklicks auf die rechte untere Zellecke (kleine(!) Fadenkreuz muss sichtbar sein) unmittelbar nach Formeleingabe und wenn die Hilfsspalte unmittelbar rechts neben einer ohne Leerzellen gefüllten Datenspalte liegt.

Eine Formelauswertung ohne die Hilfsspalte würde bei der Datenmenge möglicherweise in die Knie gehen.

Eine alternative Auswertung wäre in Excelversionen ab XL2013 möglich. Stichwort PowerQuery. Aus VBA-Lösungen halte ich mich außen vor.

Gruß Werner
.. , - ...

Betrifft: AW: die Hilfsspaltenformel nach unten kopieren ...
von: Tom
Geschrieben am: 08.09.2020 14:31:26

Danke dir Werner!

Eine letzte Anfrage zur Optimierung für die erste Frage, bei der ich den letzten Wert vor 5:30 Uhr in Spalte M gesucht hatte.

Kann ich in deine Formel die Bedingung integrieren, dass der letzte Wert vor 5:30, der ungleich Null ist, gesucht wird?

Vielen Dank vorab!

Betrifft: AW: dazu erweitere die Formel einfach mit ...
von: neopa C
Geschrieben am: 08.09.2020 14:47:13

Hallo Tom,

... in N2 mit =(C2>0)*(....)

Gruß Werner
.. , - ...

Betrifft: AW: dazu erweitere die Formel einfach mit ...
von: Tom
Geschrieben am: 08.09.2020 14:50:22

Ich meinte in dieser Formel:


=INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M100000)/(A2:A100000=X1)/(C2:C100000<="5:30"+0);1))


Gruß
Tom

Betrifft: AW: entsprechend analog ...
von: neopa C
Geschrieben am: 08.09.2020 14:56:55

Hallo Tom,

... so: =INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M99999)/(A2:A99999=X1)/(C2:C99999>0)/(C2:C99999<="5:30"+0);1))

Gruß Werner
.. , - ...

Betrifft: AW: entsprechend analog ...
von: Tom
Geschrieben am: 08.09.2020 15:49:56

Hallo Werner,

mein Problem scheint noch nicht final gelöst zu sein.
Und zwar habe ich noch ein Problem mit dem Wochenende.
Alles funktioniert soweit, nur ist es so, dass am Wochenende in Spalte M immer eine Null erzeugt wird.
Freitags gibt es keine Nachtschicht, die bis zum Folgetag (Samstag um 6:00 Uhr) da ist. dementsprechend bräuchte ich zusätzlich die "Option", wenn es sich bei dem in X1 angegebenen Datum um einen Samstag/Sonntag handelt, dass der letzte Wert in Spalte M vom Freitag übernommen wird.

Siehst du da eine Möglichkeit?
Ist natürlich schwierig auszudrücken, in meiner Beispieldatei siehst du es aber ja recht anschaulich, wenn du dir in Spalte A einen Samstag suchst und dann den Wert aus Spalte M betrachtest.

Gruß
Tom

Betrifft: AW: das ist so aber noch nicht eindeutig ...
von: neopa C
Geschrieben am: 08.09.2020 16:08:05

Hallo Tom,

... geht Dir es wirklich um den letzter Wert vor einem WE, der ja z.B. vor dem WE 29/30.8 um 21:59 war oder soll auch da der letzte Wert von Freitag vor 6:00 geholt werden? Oder?

Gruß Werner
.. , - ...

Betrifft: AW: das ist so aber noch nicht eindeutig ...
von: Tom
Geschrieben am: 08.09.2020 21:59:25

Hallo Werner,

Sehr guter Hinweis!
Es geht in diesem Fall, bei dieser Formel nur um den letzten Wert vor dem Wochenende, also um den von dir angesprochenen Wert von 21:59.

Gruß
Tom

Betrifft: AW: dann teste mal ...
von: neopa C
Geschrieben am: 09.09.2020 11:49:47

Hallo Tom,.


... folgende Formel:

=INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M99)/(A2:A99=X1-(REST(X1;7)+1)*(REST(X1;7)<2))/(C2:C99>0)/(C2:C99<="5:30"+"16:30"*(REST(X1;7)<2));1))

Gruß Werner
.. , - ...

Betrifft: AW: dann teste mal ...
von: Tom
Geschrieben am: 09.09.2020 12:37:26

Hallo Werner,

es scheint tatsächlich, zumindest ich das soweit getestet habe, zu funktionieren.
Dafür schon mal ein riesen Dankeschön!

Das einzige, wo das noch nicht funktioniert, ist bei der "Monatsberechnung".
Dort habe ich ja die Hilfsspalte angelegt, mit den Formeln, die du mir gegeben hast.
Dort schreibt er jetzt aber natürlich die 1 immer noch in der falschen Zeile, und der "Gesamtwert für den Monat" stimmt dadurch nicht.

Hast du hier noch einen Ansatz?

Danach bin ich wunschlos glücklich! :-)

Gruß Tom

Betrifft: AW: für in die Hilfsspalte dann ...
von: neopa C
Geschrieben am: 09.09.2020 14:34:57

Hallo Tom,

... in N2 folgende erweiterte Formel:

=((REST(A2;7)>1)*(C2<="5:30"+0)*(C3>"5:30"+0)*(A2=A3)+(REST(A2;7)=6)*(A3>A2))*(C2>0)*(TEXT(A2;"MJ")=TEXT(Y$1;"MJ"))

und nach unten kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: für in die Hilfsspalte dann ...
von: Tom
Geschrieben am: 09.09.2020 14:45:22

Hallo Werner,

ich glaube du hast es geschafft!!!

Vielen lieben Dank für die super Hilfe und deine 2-tägige Mühe!

Gruß
Tom

Betrifft: AW: freut mich, bitteschön owT
von: neopa C
Geschrieben am: 09.09.2020 14:48:12

Gruß Werner
.. , - ...

Betrifft: versionsunabhängig
von: WF
Geschrieben am: 08.09.2020 12:10:53

Hi,

folgende Arrayformel:
{=INDEX(M:M;MAX((A1:A99=X1)*(C1:C99<1/4)*ZEILE(X1:X99)))}

bzw. für die {}-Gegner mit Index-Verlängerung:
=INDEX(M:M;INDEX(MAX((A1:A99=X1)*(C1:C99<1/4)*ZEILE(X1:X99));0))

WF

Beiträge aus dem Excel-Forum zum Thema "Letzter Wert vor 6 Uhr"