Zahlenfolge markieren wenn mehr als 5 Werte in Folge negativ sind

[felix1]

Hallo,

habe folgendes Problem:
In Spalte A befindet sich eine große Folge von Zahlen. Ich möchte nun alle Bereiche markieren, in denen mehr als 5 aufeinanderfolgende negative Werte auftreten, z.B. mit einer "1" in Spalte B.

Mein Problem ist, dass ich die Bereiche mit >5 negativen Werten in Folge ab dem ersten Wert markieren will, sonst könnte ich es leicht über eine wenn-Formel lösen. Außerdem sollen natürlich die Bereiche mit bis zu 5 negativen Werten in Folge nicht markiert werden.


So soll das aussehen:
[
Bild bitte so als Datei hochladen: Klick mich!
]

Wäre klasse, wenn mir jemand eine Anregung geben könnte (gerne auch ohne Makro) :69:

Gruß,
Felix

[neopa]

Hallo Felix,

zu.B. mit bedingter Formatierung in A1:A99:

=AGGREGAT(15;6;ZEILE(A1:A99)/(A2:A99>=0)*(A1<0);1)-WENNFEHLER(AGGREGAT(14;6;ZEILE(A1:A$2)/(A1:A$2>=0)*(A1<0);1);1)>5

[lupo1]

Wenn die Daten ab A7 bewertet werden dürfen (A1:A6 manuell), dann etwas kürzer, zuverlässiger und viel schneller:

B7: =N(SUMMENPRODUKT(--(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1;ZEILE($1:$6);;6);"<0")=6))>0)

benötigt bei 100.000 Datensätzen weniger als eine Sekunde.

Bei 100.000 Datensätzen stürzt bei mir (kleinstes Surface Pro 4 (4GB) mit Excel 2010) die AGGREGAT-Lösung, wie schon mehrfach beobachtet, auch hier ab. Vorher schickt sie sich an, eine Lösung in 1 Minute oder mehr zurückgeben zu wollen.

Das ist mir der Beginn in A7 wert.

Vermutlich kann neopa jedoch die Bezüge noch ausfeilen, damit immer nur 6 Werte geprüft werden.

[neopa]

Hallo lupo1,

ich hatte meine bed. Formatierungsformel  bewusst eingeschränkt auf A1:A99 (wobei diese bis ca. A1000 auch noch vertretbar schnell auswertet), weil in dieser Formel eben sehr viele  interne Operationen stattfinden und die Formel entsprechend oft kopiert wird.

Auf Deinen prima Lösungsansatz wäre ich wohl aber nicht gekommen, da ich mich nie wirklich mit BEREICH.VERSCHIEBEN() intensiver beschäftigt habe (und es auch nicht tun werde). Denn in den allermeisten Formellösungsansätze ist diese Funktion durch INDEX() ersetzbar.

Außerdem hatte ich mich einige Minuten mit der Problematik "abgequält", wie man eine Auswertungsformel auch ab A1 einsetzen kann.

Für das Erzeugen einer derartigen Matrix  wie sie hier (oder z.B. zum Auswerten von gefilterten Datensätzen) benötigt wird, ist BEREICH.VERSCHIEBEN() wahrscheinlich nicht wirklich durch eine nicht volatile Standardfunktion zu ersetzen. 

Allerdings würde ich in einer Nachbarspalte ab Zeile 7 folgende noch kürzere und schnellere Formel vorschlagen:  


=N(SUMME(INDEX((ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1;{1;2;3;4;5;6};;6);"<0")=6)+0;))>0)

worin SUMME(INDEX(...)) SUMMENPRODUKT() entspricht.

Diese Formel kann man allerdings in der bedingten Formatierung so nicht einsetzen. Dazu bedarf es in vorliegender Excelversion zuvor der Zuweisung zu einer benannten Formel. Dann kann sogar neben INDEX() auch N() entfallen.

Also benannte Formel namens z.B. Pruef

Pruef =SUMME((ZÄHLENWENN(BEREICH.VERSCHIEBEN(!A1;{1;2;3;4;5;6};;6);"<0")=6)+0)>0

und in der bedingten Formatierung ab A7 die Formel: =Pruef für den Zellbereich A7:A### zuweisen.

[lupo1]

Hallo neopa,

BEREICH.VERSCHIEBEN wurde durch die Nonvolatilitätsfetischisten kalt abgemurkst. Wieso soll man sich also noch damit befassen? Es gibt ja keine Befürworter.

Nein: Lieber ein nonvolatiles Modell haben - und dann trotzdem 1 Minute warten.

Wie lebt man mit volatilen Formeln? Gut! Und wenn tatsächlich mal nicht, dann: Jeweils erste Formel behalten, weitere platt machen. Und nur bei Bedarf ausfüllen (=neu rechnen) und wieder platt machen. Geht super bequem - auch mit VBA.

TE wollte übrigens keine bedF. Er wollte nur ne 1 haben.

Schick wieder Dein INDEX. Vielleicht nennst Du der Gemeinde ja die Fälle, wo das geht, und wo nicht. So weit ich mich entsinne, war snb wohl der, der das in letzter Zeit öfters gezeigt hat.

[felix1]

Hallo,

vielen Dank für eure schnellen und hilfreichen Antworten. Komme damit auf jeden Fall schon ans Ziel. Werde nun mal ausprobieren welcher eurer Vorschläge am besten zu meinem Problem passt.

Gruß
Felix

[neopa]

Hallo lupo1,

zunächst hab ich gerade bemerkt, dass ich das INDEX() bei meiner letzten Formelversion mit {1;2;3;4;5;6} schon nicht mehr benötige!

Also ausreichend ist schon in B7: =N(SUMME((ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1;{1;2;3;4;5;6};;6);"<0")=6)+0)>0)

INDEX() wird nur gebraucht, wenn in der Formel ZEILE(A$1:A$6) steht (was ich ursprünglich noch zu stehen hatte).
Aber wenn mit ZEILE() die Matrix definiert (werden muss), dann sollte besser ZEILE(X$1:X$6) der spaltenlosen Angabe ZEILE($1:$6) vorgezogen werden, wenn schon eine volatile Funktion zum Einsatz kommt. Die Auswirkungen (ohne Spaltenbezug)  spürt man natürlich erst dann richtig, wenn in den Zellen rechts der Spalte A im betreffenden Zeilenbereich auch noch weitere Formeln vorhanden sind. Je mehr dort stehen desto langsamer wird die Auswertung.

Felix wollte eine Markierung der entsprechenden Zellen und hatte nur beispielsweise eine 1 in Nachbarzellen angegeben. Also war und ist eine bedingte Formatierung durchaus möglich und mE auch sinnvoll.

Also mit INDEX() kann man in sehr vielen Fällen eine Matrix ohne den spez. MATRIXformelabschluss bzw. ohne eine eine Matrixfunktion wie z.B. SUMMENPRODUKT() auswertungsfähig gestalten. ich könnte Dir jetzt aber auf Anhieb nicht auflisten, wo es überall geht und wo nicht, obwohl ich garantiert schon seit mindestens 5 Jahren dies teilweise so anwende und so auch zig-fach gepostet habe.