Anzahl der "Einsen" in einer Spalte zählen mit Bedingung
#1
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


Angehängte Dateien Thumbnail(s)
       
Antworten Top
#2
Hi,

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

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#3
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.


Angehängte Dateien
.xlsx   von15bis60.xlsx (Größe: 20,47 KB / Downloads: 9)
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antworten Top
#4
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!
Antworten Top
#5
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.
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antworten Top
#6
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
Antworten Top
#7
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
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste