Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1780to1784
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1780to1784
1780to1784
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Letzter Wert vor 6 Uhr

Letzter Wert vor 6 Uhr
08.09.2020 10:44:13
Tom
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

31
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Letzter Wert vor 6 Uhr
08.09.2020 10:45:34
Tom
Die Uhrzeit steht in Spalte C, das habe ich vergessen zu erwähnen!
AW: ja wir können helfen, doch ...
08.09.2020 10:47:08
neopa
Hallo Tim,
... (D)eine kleine Beispieltabelle wäre hilfreich(er) und zusätzlich die Aussage mit welcher XL-Version Du arbeitest.
Gruß Werner
.. , - ...
AW: Letzter Wert vor 6 Uhr
08.09.2020 10:55:13
Daniel
Hi
Wo steht die Uhrzeit?
Ist die Liste nach Datum und Uhrzeit aufsteigend sortiert?
Wie schon geschrieben, eine Beispieldatei würde helfen.
Gruß Daniel
AW: Letzter Wert vor 6 Uhr
08.09.2020 11:10:14
Tom
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
Anzeige
AW: Letzter Wert vor 6 Uhr
08.09.2020 11:23:26
Daniel
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
AW: mit INDEX() und AGGREGAT() ...
08.09.2020 11:26:12
neopa
Hallo Tom,
... so: =INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M99)/(A2:A99=X1)/(C2:C99&lt=1/4);1))
was aber so nur ab XL2010 geht. In XL2007 ist die Formel etwas anders.
Gruß Werner
.. , - ...
AW: mit INDEX() und AGGREGAT() ...
08.09.2020 11:52:43
Tom
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!!
AW: mit beliebiger Uhrzeit ...
08.09.2020 11:58:46
neopa
Hallo Tom,
... so: =INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M99)/(A2:A99=X1)/(C2:C99&lt="5:30"+0);1))
Gruß Werner
.. , - ...
Anzeige
AW: mit beliebiger Uhrzeit ...
08.09.2020 12:07:58
Tom
Funktioniert perfekt!
Ich danke dir.
Gruß
Tom
AW: gerne, bitteschön owT
08.09.2020 12:13:20
neopa
Gruß Werner
.. , - ...
AW: gerne, bitteschön owT
08.09.2020 12:31:49
Tom
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
AW: sollte auch realisierbar sein ...
08.09.2020 12:56:11
neopa
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
.. , - ...
Anzeige
AW: sollte auch realisierbar sein ...
08.09.2020 13:28:39
Tom
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!!
AW: diese Daten sind nicht sortiert ...
08.09.2020 13:43:36
neopa
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&lt="5:30"+0)*(C3&gt"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
.. , - ...
Anzeige
AW: diese Daten sind nicht sortiert ...
08.09.2020 14:00:37
Tom
Gibt es noch eine elegante(re) Lösung ohne Hilfsspalte?
Ich danke dir natürlich erstmal für deine Mühe!
Gruß Tom
AW: wie viele Datensätze liegen ca. max vor ?
08.09.2020 14:06:29
neopa
Gruß Werner
.. , - ...
AW: wie viele Datensätze liegen ca. max vor ?
08.09.2020 14:11:22
Tom
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
AW: die Hilfsspaltenformel nach unten kopieren ...
08.09.2020 14:18:20
neopa
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
.. , - ...
Anzeige
AW: die Hilfsspaltenformel nach unten kopieren ...
08.09.2020 14:31:26
Tom
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!
AW: dazu erweitere die Formel einfach mit ...
08.09.2020 14:47:13
neopa
Hallo Tom,
... in N2 mit =(C2&gt0)*(....)
Gruß Werner
.. , - ...
AW: dazu erweitere die Formel einfach mit ...
08.09.2020 14:50:22
Tom
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
AW: entsprechend analog ...
08.09.2020 14:56:55
neopa
Hallo Tom,
... so: =INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M99999)/(A2:A99999=X1)/(C2:C99999&gt0)/(C2:C99999&lt="5:30"+0);1))
Gruß Werner
.. , - ...
Anzeige
AW: entsprechend analog ...
08.09.2020 15:49:56
Tom
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
Anzeige
AW: das ist so aber noch nicht eindeutig ...
08.09.2020 16:08:05
neopa
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
.. , - ...
AW: das ist so aber noch nicht eindeutig ...
08.09.2020 21:59:25
Tom
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
AW: dann teste mal ...
09.09.2020 11:49:47
neopa
Hallo Tom,.
... folgende Formel:
=INDEX(M:M;AGGREGAT(14;6;ZEILE(M2:M99)/(A2:A99=X1-(REST(X1;7)+1)*(REST(X1;7)&lt2))/(C2:C99&gt0)/(C2:C99&lt="5:30"+"16:30"*(REST(X1;7)&lt2));1))
Gruß Werner
.. , - ...
Anzeige
AW: dann teste mal ...
09.09.2020 12:37:26
Tom
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
AW: für in die Hilfsspalte dann ...
09.09.2020 14:34:57
neopa
Hallo Tom,
... in N2 folgende erweiterte Formel:
=((REST(A2;7)&gt1)*(C2&lt="5:30"+0)*(C3&gt"5:30"+0)*(A2=A3)+(REST(A2;7)=6)*(A3&gtA2))*(C2&gt0)*(TEXT(A2;"MJ")=TEXT(Y$1;"MJ"))
und nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: für in die Hilfsspalte dann ...
09.09.2020 14:45:22
Tom
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
AW: freut mich, bitteschön owT
09.09.2020 14:48:12
neopa
Gruß Werner
.. , - ...
versionsunabhängig
08.09.2020 12:10:53
WF
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

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige