Anzahl der "Einsen" in einer Spalte zählen mit Bedingung

[Natuuus]

Hallo,
Ich muss Messdaten asuwerten und einen Zeitanteil berechnen. Dabei interessieren mich allerdings nur Zeiträume länger als 15 Minuten und kürzer als 60 Minuten. Alle Zeiträume die in Frage kommen haben einen Grenzwert, der mit einer =Wenn() Funktion als 1 in eine weitere Spalte ausgegeben werden.
Wenn 15 "Einsen" hintereinander kommen, dann ist der Zeitraum relevant, wenn mehr als 60 "Einsen" kommen, dann fällt der Zeitraum wieder raus.
Bisher habe ich das in der nächsten Zeile mit der folgenden Wenn Funktion gemacht. 


=WENN(ODER(SUMME(M19:M33)=15;SUMME(M20:M34)=15;SUMME(M21:M35)=15;SUMME(M22:M36)=15;SUMME(M23:M37)=15;SUMME(M24:M38)=15;SUMME(M25:M39)=15;SUMME(M26:M40)=15;SUMME(M12:M26)=15;SUMME(M13:M27)=15;SUMME(M14:M28)=15;SUMME(M15:M29)=15;SUMME(M16:M30)=15;SUMME(M17:M31)=15;SUMME(M18:M32)=15;);1;"")

So wird jeder Fall abgedeckt und es wird eine "Eins" in der nächsten Zeile ausgegeben wenn 15 nacheinander zutreffen. 
Die "Einsen" zähle ich am Ende einfach nur zusammen und habe meinen Anteil. Bei 15 Zeilen geht das auch noch, aber bei 60 wird das ein wenig aufwendig, jeden Fall abzudecken.
Gibt es da eine einfachere Variante?

Vielen Dank schonmal!

Grüße
Natuuus

[BoskoBiati]

Hi,

mit einem Bild kann man da nichts anfangen. stelle mal eine Mustertabelle vor.

[Ego]

Hallo Natuuus,

ich habs so verstanden:

Code:

=WENN(UND(A2=1;(AGGREGAT(15;6;ZEILE(A3:$A$329)/(A3:$A$329<>1);1)-AGGREGAT(14;6;ZEILE($A$1:A1)/($A$1:A1<>1);1)-1)>=15;(AGGREGAT(15;6;ZEILE(A3:$A$329)/(A3:$A$329<>1);1)-AGGREGAT(14;6;ZEILE($A$1:A1)/($A$1:A1<>1);1)-1)<=60);1;"")

für Zeile 2 und 1en von A2 bis A328.

ps. Über die Aggregat-Funktionen werden die erste Zeile hinter der aktuellen Zeile und die letzte Zeile vor der aktuellen Zeile gesucht, die keine 1 enthalten.

[Natuuus]

Ja du hast es richtig verstanden! Ich habe die Funktion zwar noch nicht genau verstanden, aber das wird noch :D
Es klappt jedenfalls
Vielen Dank!

[Ego]

Hallo Natuuus,

hier die Formel einmal von Innen nach Aussen erklärt:


A1 = "ZEILE(A3:$A$329)"
Das ist eine Liste von Zeilennummern nach der aktuellen Zeile.

B1 = "(A3:$A$329<>1)"
Das ist eine Liste von Wahrheitswerten für die obigen Zeilen, die ein WAHR haben, wenn der Wert der Zeile keine 1 ist, ansonsten ein FALSCH.

C1 = "A1/B1"
Vor der Division werden Wahrheitswerte von B1 in 1en (für WAHR) und 0en (für FALSCH) ungewandelt. Nach der Division habe ich also eine Liste die die Zeilennummer beibehält, wenn der Eintrag keine 1 und ist einen Fehlerwert (Division durch 0) wenn der Eintrag eine 1 ist.

D1 = "AGGREGAT(15;6;C1;1)"
mit dieser Aggregatfunktion erhalte ich den kleinsten Wert der Liste, der kein Fehlerwert ist. Ich erhalte also die Zeilennummer der ersten Zeile nach der aktuellen Zeile die keine 1 enthält.

A2 = "ZEILE($A$1:A1)"
Das ist eine Liste von Zeilennummern vor der aktuellen Zeile.

B2 = "($A$1:A1<>1)"
Das ist eine Liste von Wahrheitswerten für die obigen Zeilen, die ein WAHR haben, wenn der Wert der Zeile keine 1 ist, ansonsten ein FALSCH.

C2 = "A2/B2"
Vor der Division werden Wahrheitswerte von B2 in 1en (für WAHR) und 0en (für FALSCH) ungewandelt. Nach der Division habe ich also eine Liste die die Zeilennummer beibehält, wenn der Eintrag keine 1 und ist einen Fehlerwert (Division durch 0) wenn der Eintrag eine 1 ist.

D2 = "AGGREGAT(14;6;C2;1)"
mit dieser Aggregatfunktion erhalte ich den grösten Wert der Liste, der kein Fehlerwert ist. Ich erhalte also die Zeilennummer der letzten Zeile vor  der aktuellen Zeile die keine 1 enthält.

E = D1-D2 - 1
Die Differenz zwischen der ersten Zeilennummer ohne 1 hinter der aktuellen Zeile und der letzten Zeilennummer ohne 1 vor der aktuellen Zeile minus eins, ist die Anzahl der zusammenhängenden 1en, wenn die aktuelle Zeile auch eine 1 enthält.

F =WENNFEHLER(UND(A2=1;E>= 15;E<=60);1;0)

Wenn die aktuelle Zeile eine 1 enthält und die Anzahl der zusammenhängenden Zeilen mit 1en zwischen 15 und 60 ist, wird eine 1 eingetragen.

[Elex]

Hi

Andere Variante. Ob sie einfacher zu Verstehen ist?  Formel mit strg+shift+Enter abschließen.

Code:

=WENN(A2=1;WENN(22,6>ABS(37,5-(1/MAX((A2:A$328=0)/ZEILE($A1:A$327))-MAX(($A$2:A2=0)*ZEILE($A$1:A1))-1));1;"");"")

Nach oben und unten bis zur nächsten 0 -> Zeile(max) - (Min) -1 = Anzahl 1en.
Von 37,5(Mittel von 15 und 60) - Anzahl
ABS davon nicht größer als 22,5

37,5 -60 = -22,5
37,5 - 15 =22,5

Gruß Elex

[Natuuus]

Ich hatte viel zu tun in den letzten Tagen. Vielen Dank aufjedenfall für die Erklärung!

Die zweite Variante probiere ich vielleicht bei der nächsten Auswertung aus! Auch danke dafür :) 

Gruß
Natuuus