Office: (Office 2016) Summieren in bestimmten Bereichen

Moin moin,

ich hänge an einem Problem fest und überlege, ob sich das überhaupt lösen lässt:

Ich markiere Zeitspannen, die kürzer oder gleich 1 Stunde sind zwischen den Arbeitszeiten, ie. zwischen den Zeilen.
Es gibt dann 4 unterschiedliche Zustände für eine Zeile

• keine Übereinstimmung
• Übereinstimmung vorher in Spalte I mit Wert 1
• Übereinstimmung nachher in Spalte G mit Wert 1
• Übereinstimmung vorher und nachher in Spalte H mit Wert 1

Mein Ziel ist es nun, dass Excel die Dienstzeiten summiert ab "Übereinstimmung nachher" bis zur nächsten "Übereinstimmung vorher".
Also G7 zu I8, G13 zu I17, G20 zu I23.
Im Beispielbild sollte dann Zeile 7 und 8 5 Stunden ergeben.
Zeile 13 bis 17 6,75 Stunden.
Zeile 20 bis 23 11 Stunden.

Ist das überhaupt möglich?

Vielen Dank schon mal


Irgendwie bekomme ich tab2html nicht zum laufen, daher nur ein Bild

 

Hallo,

da du nirgends schreibst, wo die Summen gespeichert werden sollen, nehme ich im Folgenden an, dass diese in der Spalte J erfolgen sollen.
Ferner hast du für deine Beispieldaten die Summe über D10:D12 mit 1,33 unterschlagen - ich nehme an, dass du auch diese wünschst.

Du gibst Folgendes ein:

1. In Zelle J2 folgende Matrixformel:
   {=WENN(I2=1;SUMME(INDEX(D$1:D2; KGRÖSSTE(WENN(G$1:G2=1;ZEILE(G$1:G2));1);1):D2 );0)}
   (Achtung! Eine Matrixformel wird ohne die geschweiften Klammern in die Formeleditierzeile eingegeben, wobei die Formeleingabe nicht mit der EINGABE-Taste beendet wird, sondern mit der Tastenfolge UMSCHALT+Strg+EINGABE. Danach erscheint die Formel in geschweifte Klammern eingeschlossen (wie vorhin in der blauen Zeile)).
   .
2. Diese Formel aus J2 kopierst du in die Zellen darunter (durch Ziehen am Zellkopierpunkt in der rechten unteren Ecke von J2).
   .
3. Dann sollte in Zelle J8 eine 5, in Zelle J12 eine 1,33, in Zelle J17 eine 6,75 und in Zelle J23 eine 11 als Summen erscheinen, während in den restlichen Zellen der Spalte J lauter 0-Ergebnisse zu sehen sind, die du ja durch geeignete Zellformatierung ohne Schwierigkeiten wegblenden kannst.

 

Wow, danke dir vielmals Anton.

Und du hattest vollkommen Recht. Habe ich alles vergessen zu erwähnen und das bitte ich zu entschuldigen.
Je länger ich mir die Formel anschaue und auswerte, desto mehr Sinn ergibt sie auch. Danke auch noch mal für die Ausführung.

Ein paar Fragen darauf bezogen hätte ich noch:

• Warum müssen die Spalten in Zeile 1 starten? In meinem Beispiel klar, aber wenn ich oberhalb noch leere Zeilen einfüge, dann zerschieße ich die Berechnung immer mehr. Der Fehler ist mir nicht ersichtlich.
• In der dynamischen Tabelle scheint es auch ohne UMSCHALT+Strg+EINGABE zu funktionieren. Ist das normal?
• In deinem Beispiel ist ein Leerzeichen nach D2 (kurz vor'm Ende). Hat das eine besondere Bewandtnis?

 

Hallo,

in der beiliegenden Excel-Datei sind 3 Arbeitsblätter enthalten:

• Im Arbeitsblatt "Tabelle1" sind die Daten und die Formeln enthalten gemäß deinem 1. Posting und meiner Antwort.
• Im Arbeitsblatt "Tabelle2" sind die Daten und die Formeln enthalten, wie sie sich ergeben, wenn man vor den Daten 2 Leerzeilen einfügt.
• Im Arbeitsblatt "Tabelle3" liegt dieselbe Situation wie in "Tabelle2" vor - allerdings habe ich die Daten in eine (strukturierte) Tabelle umgewandelt und die Formeln an diesen Umstand angepasst.

Ferner habe ich dir in allen 3 Arbeitsblättern jeweils 3 Formelvarianten eingebaut, die jeweils das Gleiche bewirken:

• Formel1 (jeweils in Spalte J) ist die Formel, wie ich sie in meinem letzten Posting verwendet hatte. In "Tabelle2" kannst du die nötigen Veränderungen sehen, die sich aufgrund der 2 zusätzlichen Leerzeilen vor den Daten ergeben: Die Zellangaben verschieben sich um 2 Zeilen - aus $I2 wird $I4, aus $D$1:$D2 wird $D$3:$D4, aus $G$1:$G2 wird $G$3:$G4. Das Einzige, das nicht geändert werden darf, sind die Zeilennummern in ZEILE($G$1:$G2). Allerdings darf man hier das "G" (Spaltenbezeichnung) weglassen, denn innerhalb der ZEILE()-Funktion kommt es ausschließlich auf die Zeilennummern an.
• Formel2 (jeweils in Spalte K) ist fast identisch mit Formel1, jedoch wird dort statt Funktion "KGRÖSSTE(...)" die Funktion "AGGREGAT(14;6;...)" verwendet. Sie bewirkt praktisch dasselbe.
• Formel3 (jeweils in Spalte L) ist erheblich kürzer als die beiden ersten, weil der Ausdruck innerhalb der SUMME(...)-Funktion in meiner benutzerdefinierten Funktion "ZelleOberhalb(...)" besteht und dort den Zellbereichsanfang (anstelle der INDEX(...)-Funktion) liefert, ab dem summiert werden soll.
  Sie sieht folgendermaßen aus:
  Code:

  '
  'Die Funktion "ZelleOberhalb" sucht, beginnend ab der Zelle "AbZelle",
  '           in den über dieser Zelle liegenden Zellen nach der ersten Zelle, die nicht leer ist.
  '           Von dieser nicht leeren Zelle wird dann Anzahl "Spalten" nach rechts bzw. links gegangen
  '           und diese so ermittelte Zelle als Zellbezug zurückgegeben
  'Parameter: AbZelle (Pflichtangabe)  Zelle ab der nach der nächsten nicht leeren Zelle gesucht werden soll
  '           Spalten (optional; Defaultwert=-3) Spaltenoffset ab der nicht leeren Zelle
  'Rückgabe:  Anfangszelle des Zellbereiches, über den summiert werden soll.
  '
  Public Function ZelleOberhalb(AbZelle As Range, Optional Spalten = -3) As Range
    Dim AktZelle As Range
    Set AktZelle = AbZelle
    Do While IsEmpty(AktZelle)
      Set AktZelle = AktZelle.Offset(-1)
    Loop
    Set ZelleOberhalb = AktZelle.Offset(0, Spalten)
  End Function
  

• Ab Spalte N habe ich jeweils Exemplare der enthaltenen Formeln eingeblendet.

Vgl. dazu, was ich unter dem Punkt für "Formel1" gesagt habe.

Das hat, was das Funktionieren der Formel betrifft, überhaupt keine Bedeutung. Ich hatte ein weiteres Leerzeichen vor "KGRÖSSTE(" eingefügt deshalb, weil nämlich der Editor in diesem Forum die unangenehme Eigenschaft hat, von selbst an irgendwelchen Stellen Leerzeichen einzufügen, die dann zu einer Fehlfunktion führen, wenn sie unkritisch übernommen werden.

 

Man sieht den Wald vor lauter Bäumen nicht. Macht Sinn, dass man den Bezugspunkt einer Zeile nicht abändern sollte.

Mit Aggregat werde ich mich auch mal gerne beschäftigen. Das sieht sehr interessant aus.


Vielen Dank für die ausgiebige Erklärung. Ich konnte eine Menge mehr mitnehmen als ich brauchte