Auflisten vom ersten und letzen Wert ohne Leerzeilen

[TC-Tronik]

Falls das jemand schon mal gemacht hat, könnte er mir freundlicherweise einen Ansatz geben. Wie ich das realisieren kann.
Ich möchte eine Auswertung generieren. Es soll mir die erste Zeit wo eine Null dahinter steht, unter "von" und die Uhrzeit der
letzten Null unter "Bis" eingetragen werden. Das ganze dann fortlaufend unter einander aufgelistet.
Mein Problem ist das der Status Null über einen Zeitraum von 24 Stunden mehrmals auftreten kann und ich nicht weis wie man
Sie untereinander ohne Leerzeilen und den ganzen Zeiten zwischen der ersten Null und der letzten Null auflisten kann.
Im Bild ist auf der rechten Seite die Darstellung wie es aussehen soll dargestellt. Ich hoffe es verständlich erklärt zu haben.
Ich bin für jede Hilfe dankbar.



   

Mit freundlichen Gruß
TC-Tronik

[daNorbert]

Hallo,

Anbei mal eine Möglichkeit mit Hilfsspalten (Die Du ja dann ausblenden kannst)

Füge zuerst vor Deiner Spalte D noch 2 Spalten ein.

Folgende Formel in C2 und dann runterkopieren.

=Zeile(A2)

In Spalte D und E werden die Zeilen des Start und Endwerts angegeben.

In E1 bitte 1 schreiben

In D2:  =SVERWEIS(0;INDIREKT("B" & E1+1 & ":C1000");2;FALSCH)
In E2:  =SVERWEIS(1;INDIREKT("B" & D2+1 & ":C1000");2;FALSCH)-1

=> D2 und E2 nach unten kopieren.

Deine Werte kannst Du Dir dann über Indirekt anzeigen lassen.
 F4: =INDIREKT("A"&D2)
 G4: =INDIREKT("A"&E2)

F4 und G4 dann runter kopieren.

LG
Norbert

[TC-Tronik]

Vielen herzlichen Dank.
Es funktioniert super.

Jetzt im Nachhinein kommt mir die Vorgehensweise irgendwie bekannt vor.
Das habe ich vor langer Zeit bestimmt schon mal gemacht.

Aber mein Respekt, es ist wirklich sehr gut erklärt und läßt sich eins zu eins ohne Probleme umsetzen.
Desweiteren finde ich es auch toll wie schnell jemanden geholfen wird.


Bis zum nächsten Besuch.

Mit freundlichen Gruß
TC-Tronik

[HKindler]

Hi,

weil ich gerade Lust dazu hatte, hier eine Ein-Formel-Lösung für aktuelle Excel (den Ausgabebereich als Zeit formatieren):

Code:

=LET(x;A2:B51;
     z;SPALTENWAHL(x;1);
     s;SPALTENWAHL(x;2);
     s_p1;VSTAPELN(WEGLASSEN(s;1);"");
     s_m1;VSTAPELN("";WEGLASSEN(s;-1));
     v;(s=0)*(s_m1=1)*z;
     b;(s=0)*(s_p1=1)*z;
     HSTAPELN(FILTER(v;v);FILTER(b;b))
)

x ist der Ausgangsbereich (hier stehen die Zeit in A2:A51 und der Status in B2:B51, in A1 steht "Zeit" und in B1 "Status")
z ist die Spalte mit der Zeit
s ist die Spalte mit dem Status
s_p1 ist die Spalte mit Status um 1 nach oben verschoben (so kann der Status ganz einfach mit dem folgenden Status verglichen werden). Dazu löscht WEGLASSEN(s;1) die erste Zeile von s und VSTAPELN(...;"") fügt am Ende einen leeren String ein.
s_m1 ist die Spalte mit Status um 1 nach unten verschoben (so kann der Status ganz einfach mit dem vorherigen Status verglichen werden). Dazu löscht WEGLASSEN(s;-1) die letzte Zeile von s und VSTAPALN("";...) fügt am Anfang einen Leerstring ein.
v ist die von-Zeit falls der aktuelle Stauts 0 und der vorherige Status 1 ist, ansonsten bleibt es bei 0
b ist die bis-Zeit falls der aktuelle Status 0 und der nachfolgende Status 1 ist, ansonsten bleibt es bei 0
FILTER(v;v) schmeißt die 0-en bei v weg
FILTER(b;b) schmeißt die 0-en bei b weg
HSTAPELN(...) gibt die beiden Spalten als Matrix aus

[TC-Tronik]

Auch hier vielen herzlichen Dank für den Lösungsvorschlag.

Werde auch mal das Makro aus probieren. Eine Makrolösung war mir bis heute unbekannt.
Es ist ja nie schlecht wenn man auch noch eine Alternative hat.
Die zudem noch eleganter ist, weil keine zusätzlichen Spalten benötigt werden. Hat aber auch den
Nachteil das wenn keine Makros zugelassen sind, die Berechnungen nicht ausgfeührt werden.


Mit freundlichen Gruß
TC-Tronik

[HKindler]

Hi,

das ist kein Makro sondern eine ganz gewöhnliche Formel. Allerdings braucht man wegen LET(), SPALTENWAHL(), VSTAPELN(), HSTAPELN(), WEGLASSEN() und FILTER() ein aktuelles Excel.

Wenn du es testen willst, kannst du das mit Excel im Web machen.

[HKindler]

Hi,

noch eine Anmerkung zur Lösung von @daNorbert...

INDIREKT() ist eine volatile Funktion. Man sollte sie daher meiden wie der Teufel das Weihwasser.

also statt
D2: =SVERWEIS(0;INDIREKT("B" & E1+1 & ":C1000");2;FALSCH)
E2: =SVERWEIS(1;INDIREKT("B" & D2+1 & ":C1000");2;FALSCH)-1
F4: =INDIREKT("A"&D2)
G4: =INDIREKT("A"&E2)

besser
D2: =SVERWEIS(0;INDEX(B:B;E1+1):C1000;2;FALSCH)
E2: =SVERWEIS(1;INDEX(B:B;D2+1):C1000;2;FALSCH)-1
F4: =INDEX(A:A;D2)
G4: =INDEX(A:A;E2)

[BoskoBiati]

Hi,

oder mit älteren Versionen:


  Auflisten_20230614.xlsx (Größe: 10,5 KB / Downloads: 7)

[HKindler]

Hi,

um mit meiner Formel auf Edgars Ergebnis zu kommen, muss man bei den VSTAPELN()-Teilen die "" durch 1 ersetzen. Und man muss den korrekten Bereich setzen, also x auf A2:B34. Das liegt daran, dass in meiner Test-Datei die Daten jeweils mit 1 gestartet und geendet sind, bei Edgars Datei aber jeweils mit 0 starten und enden.

[TC-Tronik]

Danke @HKindler für den Hinweis, in meiner Excel Version wird mindestens der Befehl LET nicht unterstützt. Daher dachte ich das es sich um ein Makro handelt, unter anderem aufgrund der Code Darstellung. So lernt man wieder mal etwas dazu. Danke
Werde auch deine Anmerkung zur Lösung von @daNorbert... beherzigen. Zuerst wollte ich die Formeln anpassen, habe dann aber die Aggregat Lösung von @BoskoBiati gesehen und mich vorerst dafür entschieden.

Diese finde ich noch besser weil zum einen keine weiteren Spalten benötigt werden und nach einem ersten Testlauf mit der Lösung von @daNorbert... es auf einen Fehler in Berechnung gelaufen ist sobald die letzte verfügbare Bis Zeit kein Status eins hat.
Beispiel: 14:02:22 (Status 0) - (14:30:23 (Status 0) das ist die letzte verfügbare Uhrzeit, da ein Messergebnis mit dem Status 1 nicht mehr vorliegt, wird automatisch die nächste Zelle mit 00:00:00 Uhr und dem Status 0 ausgefüllt.) Bei der Berechnung der Zeitdifferenz kommen so dann nur Raute Zeichen und umgerechnet auf Minuten -64923256 als Ergebnis heraus.

Falls ich einen Denkfehler haben sollte oder mich missverständlich ausdrücke, so kann sich gerne jeder melden.

Mit freundlichen Gruß
TC-Tronik