VSuchlauf mit StopBedingung nach unten wie nach oben ?

Hallo Jacques

Für genau das von dir illustrierte Problem geben die beiden folgenden Formeln das korrekte Resultat:

VSuchlauf(B1:B9;Und(LfdZelle;Oder(Index(B2:B10;LfdIndex);Wenn(LfdIndex>1;Index(B1:B9;LfdIndex-1))));0;LfdResultat+LfdZelle)

VSuchlauf(B11:B17;Und(LfdZelle;Oder(Index(B12:B18;LfdIndex);LfdIndex>1;Index(B10:B16;LfdIndex)));0;LfdResultat+LfdZelle)

Was die Formeln genau machen: Sie schauen die LfdZelle des Suchlaufs an. Falls diese Zelle eine Zahl enthält UND gleichzeitig entweder die davor ODER danach folgende Zelle auch eine Zahl enthält, dann wird der Inhalt der LfdZelle zum LfdResultat addiert.

Stolpersteine:

1. Im ersten Fall muss für die erste LfdZelle die Beurteilung der vorangehenden Zelle entfallen, da sie den Index 0 hätte und der Vergleich somit zu einem REF-Fehler führen würde. Das wird mit der Wenn-Klausel unterdrückt. Zudem wird vorausgesetzt, dass die auf den abgesuchten Bereich folgende Zelle keine Zahl enthält. Ist diese Voraussetzung nicht gegeben, müsste eine analoge Wenn-Verriegelung für das Tabellenende eingebaut werden, da sonst ggf. die Zahl in B9 addiert würde, selbst wenn Zelle B8 leer wäre.

2. Analog gilt für den zweiten Fall, dass weder die Zelle vor dem abgesuchten Bereich, noch die Zelle nach diesem Bereich einen Wert enthalten darf, der logisch "wahr" ist.

Gruss, Thomas

Korrekturen/Ergänzungen zu meiner vorherigen Antwort:

Korrektur der zweiten Formel, die in der Oder-Funktion ein Argument zu viel enthielt:
VSuchlauf(B11:B17;Und(LfdZelle;Oder(Index(B12:B18;LfdIndex);Index(B10:B16;LfdIndex)));0;LfdResultat+LfdZelle)

Ergänzung zu den angeführten "Stolpersteinen". Die Zellen vor und nach dem abgesuchten Bereich dürfen auch keinen Inhalt haben, der bei der Prüfung auf "wahr/falsch" einen Fehler ergibt, also z.B. Text. Um dieses Problem zu vermeiden, könnte der Zelleninhalt auch mit "IstZahl" geprüft werden, also die zweite Formel:

VSuchlauf(B11:B17;Und(LfdZelle;Oder(IstZahl(Index(B12:B18;LfdIndex));IstZahl(Index(B10:B16;LfdIndex))));0;LfdResultat+LfdZelle)

Gruss, T

Hallo Jacques

Die von Jürgen angegebene Referenz führt auch bei mir zu einer Fehlermeldung.

Ich hoffe, ich habe diesmal dein Problem richtig verstanden. Folgende Formel liefert das gewünscht Ergebnis:

VSuchlauf(B:B;1;0;wenn(LfdZelle;Wenn(Oder(Und(LfdIndex>1;Index(B:B;LfdIndex-1));Index(B:B;LfdIndex+1));LfdResultat+LfdZelle);Wenn(LfdResultat;SetzeZelle(LfdResultat;A:A;LfdIndex)-LfdResultat)))

Hier die Schritte im Einzelnen:

VSuchlauf(B:B;1;0;
Der Suchlauf läuft über die ganze Spalte B - jede Zelle wird bedingungslos angeschaut. Der Startwert für LfdResultat ist 0.

Wenn(LfdZelle;
Abhängig davon, ob in der LfdZelle eine Zahl ist oder nicht, wird eine der beiden folgenden Operationen ausgeführt:

Wenn in der laufenden Zelle ein Zahl ist, dann wird die ggf. die Gruppensumme berechnet:
Wenn(Oder(Und(LfdIndex>1;Index(B:B;LfdIndex-1));Index(B:B;LfdIndex+1));LfdResultat+LfdZelle)
Falls in der vorangehenden Zelle (die Zelle mit Index 0 wird wegen des möglichen REF-Fehlers durch die Klausel "LfdIndex>1" ausgeschlossen) oder in der folgenden Zelle eine Zahl ist, so wird der Wert der laufenden Zelle zum LfdResultat addiert.

Ist dagegen die LfdZelle leer, so kommt dieser Teil der Formel zum Zug:
Wenn(LfdResultat;SetzeZelle(LfdResultat;A:A;LfdIndex)-LfdResultat)))
Falls das LfdResultat>0 ist, so wird die Summe der abgeschlossenen Gruppe in die Zelle in Spalte A geschrieben. Damit das LfdResultat wieder auf 0 zurückgesetzt wird, muss der aktuelle Wert von LfdResultat nach der SetzeZelle-Operation, welche ja den Wert von LfdResultat zuweist, von sich selbst abgezogen werden.

Folgende Bedingungen müssen erfüllt sein, damit die Lösung funktioniert:
- die Spalte B darf nur leere Zellen und positive Zahlen enthalten
- die Zelle B16000 darf keine Zahl enthalten

Hoffentlich ist das jetzt, was du gesucht hast.
Gruss, Thomas

Hallo Jacques

Jetzt versteh ich dein Problem nicht mehr. In deiner Illustration war in Zelle B1 eine Zahl, aber Zelle A2 war leer. Jetzt sagst du, dass eine Gruppe auch aus nur einer einzigen Zahl bestehen könne - also müsste Zelle A2 in deinem illustrierten Beispiel doch die Gruppensumme dieser ersten Gruppe, also den Wert 1 enthalten. Ausserdem hast du in deiner ursprünglichen Frage ausdrücklich gesagt: "nur die aneinanderliegenden Zellen mit einer Zahl müssen gerechnet werden". Diese Formulierung setzt voraus, dass mindestens zwei Zahlen aufeinanderfolgen. Was gilt jetzt? "Eine Gruppe kann auch nur aus einer einzigen Zahl bestehen", oder eben "nur die aneinanderliegenden Zellen mit einer Zahl müssen gerechnet werden"?

An und für sich ist es kein Problem, die Formel so abzuändern, dass leere Zellen und Zellen, welche die Zahl "0" enthalten unterschieden werden. Statt sich auf den logischen Wert einer Zahl≠0 zu verlassen, muss mit der Funktion "IstZahl" gearbeitet werden. Trotzdem gibt es ein Problem, das weniger leicht zu lösen ist: Um festzustellen, dass das Ende einer Gruppe erreicht ist, habe ich geprüft, ob das LfdResultat >0 ist. Wahrscheinlich muss, da eine "Gruppensumme" auch "0" sein kann, eine Hilfszelle verwendet werden. Aber das werde ich mir erst anschauen, wenn du den obigen Widerspruch geklärt hast.

Gruss, Thomas

Hallo Thomas,

Ich feststelle, dass meine Beschreibung leider nicht klar genug war, Entschuldigung!

also, in einer Spalte hat man :

/ 1 / nichts / 1 / : da sind zwei Gruppen, weil diese Zahlen nicht nebeneinander liegen; so erwartet man zwei Summen : 1 und 1

/ 1 / 1 /: das ist eine einzige Gruppe, die Summe ist 2

(die erste Zahl im Beispiel hatte ich vergessen…)

nochmals vielendank für deine Hilfe

Grüsse,
jacques

Hallo Jacques

Die Formel wird so viel einfacher - wenn ich es jetzt richtig verstanden habe. Verantwortlich dafür ist vor allem der Wegfall der Bedingung, dass eine Gruppe aus mindestens zwei Zahlen bestehen muss. Hier erst mal die Formel:

VSuchlauf(B:B;1;"";Wenn(IstZahl(LfdZelle);LfdResultat+LfdZelle;Wenn(SetzeZelle(LfdResultat;A:A;LfdIndex)≠"";"")))
(Für RagTime in der DE-Version natürlich einfache statt der doppelten Anführungszeichen!)

Auch hier werden im Suchlauf bedingungslos alle Zellen angeschaut. LfdResulat wird auf "leer" initialisiert. Steht in LfdZelle eine Zahl (auch 0), so wird sie zum LfdResultat addiert, welches dadurch von "leer" auf "Typ=Zahl" wechselt.

Stösst der Suchlauf auf eine Zelle, die keine Zahl enthält, so wird die Wenn-Funktion ausgeführt, die einen speziellen Trick enthält: In der Bedingung steht nämlich eine SetzeZelle-Funktion. Das mutet recht eigenartig an, macht aber Sinn! Einerseits wird dadurch das LfdResultat in Spalte A übertragen (was keinen Schaden anrichtet wenn das LfdResultat immer noch = "" (leer) ist , andernfalls aber die Gruppensumme in Spalte A einsetzt. Anderseits wird dadurch das LfdResultat nicht verändert, wie dies der Fall wäre, wenn die SetzeZelle-Funktion nicht in der Bedingung, sondern in einem der folgenden Funktionsargumente stehen würde. Die SetzeZelle-Funktion hat als Resultat den eingesetzten Wert, also den Wert von LfdResultat, welcher ja nicht "leer" ist, wenn die Gruppensumme eingesetzt wurde. Mit dem "WertFallsWahr" der Wenn-Funktion wird das LfdResultat wieder auf "leer" gesetzt und die Suche kann mit der nächsten Zelle weitergehen.

Haben wir's jetzt geschafft?

Gruss, Thomas

Hallo Thomas,

Schöne Lösung, auch leichter zu verfolgen und zu verstehen!

Und es funktioniert! (hat mal jemand in einem Buch über RagTime6 geschrieben ;-)

Herzliche Grüsse

jacques

Thomas,
Den Trick mit "SetzeZelle" in der Bedingung könnte nur ein Virtuose erfinden… es wäre gut, wenn man deine Originallösung in den Anleitungen-Seiten wiederfinden könnte, oder ?
Tschüss,
jacques

Hallo Jacques

Ich habe deine Anregung befolgt, das Beispiel noch etwas allgemeiner gemacht und als Expertenbeitrag unter "Beispiele" und "Anleitungen" publiziert:

http://experts.ragtime.de/experts/node/3453

Gruss, Thomas

Hallo Thomas,
Die automatische Löschung der veralteten Zwischensummen ist gerade was mir noch fehlte: genial!
Grüsse,
jacques