Office: (Office 365) Aufsummieren von Werte unter bestimmten Bedingungen

Hallo zusammen,

ich stoße an meine Excelkenntnisse und hoffe hier jemand eine Lösung für mich.

Ich habe einen Kalender im Tage- und Stundenlayout. In der Zeile steht das Stundenintervall, in der Spalte jeder Tag des Jahres. Da die Bedingung von einem Tag auf den anderen Tag springen kann, bringe ich den Kalender über MTRANS in eine Spalte, so dass alles schön zusammen hängt.

In dem Kalender gibt es leere Zellen, oder Zahlenwerte und auch Nullen. Jedes Mal, wenn ein Zahlenwert erscheint soll dieser aufsummiert werden - unter der Bedingung, dass nur aufsummiert wird, wenn mindestens 4 oder 6 mal (variabel, je nach Jahr) hintereinander ein Wert steht. Also alles was aufeinanderfolgt soll aufsummiert werden, solange mindestens 4 oder 6 Werte hintereinander. Danach soll der Zähler resetten, bis die Bedingung wieder erfüllt ist.

Ich habe es mit ZÄHLENWENN probiert und diese Rückgabe dann mit SUMMEWENN aufaddiert und mit der Bedingungen WENN(x>6). Das Problem ist, wenn die 6 einmal erreicht wird, ist es ja dann immer wahr, weil sich der Wert nicht resettet. Dann hab ich noch die Bedingung eingeführt, dass die nachfolgende Zahl größer sein muss, damit die Bedingung wieder gilt. Das tut sie ja aber schon bei 7 wieder. Also muss man einen 6er-Sprung einfügen, was aber nicht geht, weil man die nächste Blockgröße überhaupt nicht kennt. Kann ja auch 8 Mal sein.

Also alles in allem bin ich mit den Formeln, die ich kenne, nicht weiter gekommen. Ich habe mal eine kleine Beispieldatei eingefügt, wie es im kleinen Rahmen aussieht.

Danke für die Hilfe

 

Hallo,

1) ich habe in Zelle A1 das Jahr (=2021) eingefügt, ferner in B1 die Mindestanzahl der Zellen (=4), die Werte enthalten müssen, damit eine Summation erfolgt.

2) ferner habe ich in Zelle A4 folgende dynamische Array-Formel eingefügt:
=SEQUENZ(6;1;DATUM($A$1;1;1);1)
Sie bewirkt, dass im Bereich A4:A9 die ersten 6 Kalendertage des Jahres aus A1 angezeigt werden. Für ein ganzes Jahr müsste diese Formel =SEQUENZ(365;1;DATUM($A$1;1;1);1) lauten und würde dann den Bereich A4:A368 ausfüllen.

3) In der Zelle B3 habe ich folgende dynamische Array-Formel eingefügt:
=SEQUENZ(1;24;1/24;1/24)
Sie bewirkt, dass im Bereich B3:Y3 die Stunden eines Tages angezeigt werden - aufgrund einer passenden Formatierung ([hh]" Uhr").

4) Daraus ergibt sich, dass die linke obere Ecke des Summationsbereichs sich in Zelle B4 befindet (im aktuellen Musterbeispiel ist der Summationsbereich in B4:Y9)

5) Da du zwar von Summieren sprichst, aber nicht davon, wohin die Summenwerte auszugeben sind, habe ich angenommen, dass die linke obere Ecke dieses Ausgabebereich durch die aktive Zelle festgelegt wird, was in der Musterdatei die Zelle I12 ist.

6) Du sprichst ferner von leeren Zellen, Zellen mit Werten und Zellen mit Nullen, wobei die Werte zu summieren seien, falls die Mindestanzahl (=4) erreicht sei - aber aus der Musterdatei habe ich entnommen, dass offensichtlich auch die Nullen in die Mindestanzahl einzubeziehen sind.

7) Ich habe dir einen Button ("Summieren/Ausgeben") eingefügt, der mit einem VBA-Makro verknüpft ("Aufsummieren") ist, das in einem allgemeinen Codemodul "Modul1" enthalten ist.

8) Klickt man auf den Button, so werden ab der Zelle mit der aktiven Zelle (siehe Punkt 5) = Zelle I12) die Zählresultate (im Beispiel also im Bereich I12:K16) ausgegeben, wobei die 1.Spalte die Zellenanzahl enthält, die 2.Spalte die errechneten Summen und die 3.Spalte die Adresse der 1.Zelle des so ausgewerteten Bereiches.

 

Dachte mir schon, dass das ohne VBA nicht umsetzbar ist, aber das hätte ich ja nach 2 Wochen nicht hin bekommen. Ich habe ein bisschen mit rum"gespielt" und es tut genau das, was es soll. Sollte sich noch eine Frage/Änderung ergeben, würde ich mich nochmal melden, aber ansonsten sag ich mal vielen Dank für die den Aufwand und die sehr gute Nachvollzierbarkeit.

Viele Grüße!

 

Ein Problem ergibt sich tatsächlich noch. Die sich ergebenden Zahlenwerte stammen aus einer wenn-dann-Verknüpfung, die in den Zellen steht, die aufsummiert werden sollen. Dadurch erhalte ich einen einen "Laufzeitfehler "13" Typen unverträglich". Ich habe schon versucht das auszulagern und dann über Copy paste und "Werte einfügen" nur reine Zahlenwerte einzufügen, jedoch erscheint die gleiche Fehlermeldung. Das Makro funktioniert scheinbar nur mit per Hand eingegebenen Zahlenwerten, nicht mit welchen die aus Formeln kopiert wurden. Habe die Zellen schon mit "Zahl" formatiert, aber brachte keinen Erfolg. Werte, die vorher als Zahlenwerte hinterlegt waren und dann rein kopiert wurden funktionieren.

Ich schätze, dass man den Datentyp anpassen muss, aber ich bin mir unschlüssig wie.

Viele Grüße

 

Hola,
wie sieht denn die Wenn-Formel aus?
Was ergibt
=istzahl(Zelle_mit_einem_Wert_aus_WennFormel)
?
Gruß,
steve1da

 

Die WENN-Formel soll Zahlen wiedergeben (die händisch eingefügt werden), wenn eine variable Bedingung erfüllt ist. In diesem Fall (Wenn(A1="Ja";"";=Blatt1A1). Wenn dort nicht "Ja" steht, wird eine Zahl eingefügt. Diese Zahl wiederum, möchte ich auf das Tabellenblatt übertragen, welches das Makro beinhaltet. Also "=Blatt2A1". Die Zelle bleibt daraufhin leer, oder ein Zahlenwert wird eingefügt. Dann möchte ich das Makro ablaufen lassen. Bei einer standardmässigen leeren Zelle und händisch eingefügten Werten läuft das Makro ab, wie es soll. Wenn über "=A1" rein Werte eingefügt werden, läuft es auch. Sobald jedoch "=A1" eine leere Zelle bleibt, haut er den Fehler mit dem Datentyp raus.

Der Befehl =istzahl wirft für jede leere Zelle "falsch" und für jede Zelle mit einer Zahl "wahr" aus.

Um mal zu verdeutlichen, was das Programm tun soll. Ich habe einen Lastgang in Stundenbasis und ich habe ein Excel-Sheet mit den Werten "Ja" und "Nein". Die "Nein"-Werte sind relevant. Ich bringe beide Datenquellen in dieselbe Struktur. Jedes Mal, wenn ein "Nein" steht, soll er die Leistung aus dem Lastgang vermerken. Sobald 4 oder 6 Mal hintereinander "Nein" steht, soll er die Werte aufsummieren. Das löse ich mit einer Wenn-Formel, die die Zelle leer lässt, wenn "Ja" und die Werte einfügt, wenn "Nein". Die Werte übertrage ich und die soll das Makro aufsummieren

 

Hilft das schon?
=Wenn(A1="Ja";0;=Blatt1A1)

 

Nein, hilft nicht. Da das Makro alle Zahlenwerte ausliest, ob 0 oder 1,2,3 und alles summiert, sobald mindestens 4 erreicht

 

Hallo,

1) da ich in etwa das Problem schon kommen sah, habe für die Summationsvariable den Datentyp "Variant" verwendet, um alle irgendwie numerischen Größen summieren zu können.

2) Was die Verwendung einer Formel betrifft, steht dem überhaupt nichts im Wege.

3) Es ist eine Täuschung zu meinen, dass bei Verwendung vorgenannter Formel, nichts in der Zelle stünde, wenn Zelle A1 den Wert "Ja" habe. Es steht tatsächlich dann eine leere Zeichenkette in der Zelle und eine leere Zeichenkette ist eben keine Zahl und nicht-Zahlen brauchen laut ursprünglicher Vorgabe auch nicht summiert zu werden.

4) Ich habe das Makro leicht modifiziert an der kritischen Stelle, indem ich den das erste IF in der DO-LOOP-Schleife modifiziert habe:
Aus "If IsEmpty(ZelleSum) Then ..." habe ich gemacht: "If IsEmpty(ZelleSum) Or Len(ZelleSum) = 0 Then ..."
Ich hoffe, dass dies das Problem behebt. Zumindest bei meinem Test hat es funktioniert.
Tritt eine solche Zelle mit einer leeren Zeichenkette auf, bricht damit die Zählung/Summation ab, so als ob dort tatsächlich eine leere Zelle stünde.

 

Ja, das war mein Verschulden. Hatte ich nicht erörtert. Man kann ja nur mit Informationen arbeiten, die man auch hat.

Auf jeden Fall funktioniert es bisher nun so, wie ich es mir vorgestellt hatte.

Danke nochmals für das Anfertigen des Makros.

Viele Grüße