leere Zellen anhand Datum und vorherigen Zellenwert füllen

17.01.2025 10:40:10

Andreas Nittke

Hallo zusammen,

ich importiere von einer Steuerung einen Statuswechsel (0 oder 1) und das dazugehörige Datum inkl. Uhrzeit.
16.12.2024 00:00 1
17.12.2024 01:10 0
17.12.2024 04:06 1
18.12.2024 01:08 0
Über einen Let-Befehl (die Formel lautet: =LET(x;24*4;a;MIN(A:A)*x;b;MAX(A:A)*x;SEQUENZ(b-a;;a;1)/x)) ergänze ich das Datum zwischen ersten und letztem Wert, damit ich eine vollständige 15min Liste habe

16.12.2024 00:00
16.12.2024 00:15
16.12.2024 00:30
16.12.2024 00:45
16.12.2024 01:00
16.12.2024 01:15

Nun möchte ich, dass der Status 0 oder 1 hinter dem jeweiligen Datum ebenfalls bis zum nächsten Statuswechsel ausgefüllt wird.
Bei einem ähnlichen Projekt hatte ich das mal mit folgender Formel hinbekommen, da waren allerdings von Anfang an die Uhrzeiten immer exakt im 15min Wert
=WENN(SVERWEIS(C3#;A:A;1;1)=C3#;SVERWEIS(C3#;A:B;2;1);0)
Ich habe auch schon probiert und die "krummen" Uhrzeiten auf volle 15min umgewandelt. Brachte aber auch nicht das gewünschte Ergebniss.
Wie muss die Formel angepasst werden, bzw. sollte dies ganz anders gelöst werden?

vollständige Tabelle:
https://www.herber.de/bbs/user/174902.xlsx

16.12.2024 00:00 1
16.12.2024 00:15 1
16.12.2024 00:30 1
16.12.2024 00:45 1
16.12.2024 01:00 1
16.12.2024 01:15 1
16.12.2024 01:30 1
....
17.12.2024 00:15 1
17.12.2024 00:30 1
17.12.2024 00:45 1
17.12.2024 01:00 1
17.12.2024 01:15 0
17.12.2024 01:30 0
17.12.2024 01:45 0
17.12.2024 02:00 0
17.12.2024 02:15 0

AW: leere Zellen anhand Datum und vorherigen Zellenwert füllen

17.01.2025 11:30:03

Yal

Hallo Andreas,

Datum und Zeiten sind jeweils den Teil vor und nach dem Komma einer Zahl.
Heute ist 45674
Heute um Mittag, also die Hälfte des Tages dazu, ist 45674,5

Was nach dem Komma kommt, ist der Anteil an Stunden also durch 24:
8:00 = 8 / 24 = 0,333
Dasselbe mit Minuten und Sekunden

D.h. wenn Du dein Datum + Uhrzeit * 24 rundest und anschliessend wieder durch 24 teilst, eliminierst Du die Minuten (und Sekunden). Du rundest auf voller Stunde.
Wenn Du mit 96 multiplizierst ( = 24*4), rundest, durch 96 teilst, rundest Du auf volle Viertelstunde.
=RUNDEN(A1*96;0)/96

VG
Yal

AW: leere Zellen anhand Datum und vorherigen Zellenwert füllen

17.01.2025 12:10:01

Andreas Nittke

Hallo Yal,

danke, ich glaube Du hast meine Anfrage missverstanden (oder ich Dich). Es geht mir um das automatische Ausfüllen des Status 0/1 in Spalte D
Spalte A und B bekomme ich von einer Steuerung, in Spalte B wird der Statuswechsel aufgezeichnet und in Spalte A die dazugehörige Uhrzeit generiert.
In Spalte C wird durch eine Formel automatisch die Uhrzeit aufgelistet im 15min Takt. Der Start und Endzeitpunkt wird dabei von der ersten und letzten Uhrzeit von Spalte A genommen.
In Spalte D soll nun immer der Status bis zum nächsten Wechsel in B übernommen werden.
Also, alle Zeiten zwischen 16.12.2024 00:00 und 17.12.2024 01:10 mit Status 1. zwischen 17.12.2024 01:10 und 17.12.2024 04:06 Status 0, zwischen 17.12.2024 04:06 und 18.12.2024 01:08 Status 1, ...
Dafür hatte ich für ein ähnliches Thema in D folgende Formel drin (geht aber leider nicht)
=WENN(SVERWEIS(C3#;A:A;1;1)=C3#;SVERWEIS(C3#;A:B;2;1);0)

A B C D
16.12.2024 00:00 1 16.12.2024 00:00 1
17.12.2024 01:10 0 16.12.2024 00:15 0
17.12.2024 04:06 1 16.12.2024 00:30 0
18.12.2024 01:08 0 16.12.2024 00:45 0
18.12.2024 04:05 1 16.12.2024 01:00 0
19.12.2024 01:07 0 16.12.2024 01:15 0
19.12.2024 04:05 1 16.12.2024 01:30 0
20.12.2024 00:14 0 16.12.2024 01:45 0
20.12.2024 02:10 1 16.12.2024 02:00 0
21.12.2024 00:03 0 16.12.2024 02:15 0
21.12.2024 03:56 1 16.12.2024 02:30 0
21.12.2024 14:38 0 16.12.2024 02:45 0
21.12.2024 18:33 1 16.12.2024 03:00 0
21.12.2024 22:26 0 16.12.2024 03:15 0
22.12.2024 02:13 1 16.12.2024 03:30 0

AW: leere Zellen anhand Datum und vorherigen Zellenwert füllen

17.01.2025 12:38:41

Yal

Hallo Andreas,

ich schlage folgendes Verfahren vor (basierend auf deinem Beispiel):

in C3:C33 kommt die Formel
=RUNDEN(A3*96;0)/96

in eine leere Zelle (nicht im ersten Zeile, bei mir E3), kommt die Formel
=RUNDEN(SEQUENZ(AUFRUNDEN((MAX(A3:A33)-MIN(A3:A33))*24;0)*4;1;MIN(A3:A33);1/96)*96;0)/96
also zwischen min und max eine Sequenz in Viertelstunden + Bereinigung auf Viertelstunden als 96tel von Tag (zwecks Abgleich mit Spalte C)

in F3 kommt die Formel
=LET(x;VERGLEICH(E3;$C$3:$C$33;0);WENN(ISTFEHLER(x);F2;INDEX($B$3:$B$33;x;1)))
Wegen der Referenz auf F2, kann man keinen Spill einführen ("E3#"). Formel bis nach unten ziehen.

Fertig.

Persönlich hätte ich eine Lösung über Power Query bevorzugt. Vielleicht habe ich demnächst Zeit oder Langeweile und poste eine solche Lösung nach.

VG
Yal

Mist

17.01.2025 13:17:42

Yal

Vielleicht muss man eher RUNDEN anstatt AUFRUNDEN in
=RUNDEN(SEQUENZ(AUFRUNDEN((MAX(A3:A33)-MIN(A3:A33))*24;0)*4;1;MIN(A3:A33);1/96)*96;0)/96
verwenden. Es geht in dem Fall nur gut, weil der erste Eintrag in der erste Hälfte der Viertelstunde steht.
Dafür eine +1 um den letzten Eintrag zu sichern.

Also
=RUNDEN(SEQUENZ(RUNDEN((MAX(A3:A33)-MIN(A3:A33))*24;0)*4+1;1;MIN(A3:A33);1/96)*96;0)/96

VG
Yal

Die Power Query Lösung

17.01.2025 13:24:07

Yal

Moin,

anbei die PQ-Lösung (hat mich zu sehr gekitzelt, um es länger zu verschieben...)

Im Grund genommen genau dieselbe Verfahren wie mit Formel, nur dass, um die Handlung zu vereinfachen, die Zeiten zuerst *96 und gerundet (weil dann Ganzzahl), dann gejoint (entspricht Sverweis oder Index(..;Vergleich(..);1) und nur am Ende /96 und in DateTime gewandelt.

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

(Formel-Lösung ist auch vorhanden, jedoch noch mit Aufrunden)

VG
Yal

AW: leere Zellen anhand Datum und vorherigen Zellenwert füllen

17.01.2025 12:56:12

BoskoBiati2

Hi,

versuche mal das:

=LET(xa;MIN(A:A);xb;MAX(A:A);xc;(xb-xa)*96;xd;SEQUENZ(xc;;xa;15/1440);xe;MAP(xd;LAMBDA(y;SVERWEIS(y;A:B;2;1)));HSTAPELN(xd;xe))

Gruß

Edgar

AW: leere Zellen anhand Datum und vorherigen Zellenwert füllen

17.01.2025 14:45:58

Andreas Nittke

Yep - jetzt hat es funktioniert - Danke

leere Zellen anhand Datum und vorherigen Zellenwert füllen

Forumthread: leere Zellen anhand Datum und vorherigen Zellenwert füllen

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

leere Zellen anhand Datum und vorherigen Zellenwert füllen

Forumthread: leere Zellen anhand Datum und vorherigen Zellenwert füllen

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread