Nur jede x. Zeile addieren 7

Mit der SUMMENPRODUKT-Funktion nur jede zweite (dritte, vierte) Zeile addieren.
 

Kürzlich hatte ich im Artikel Zählen und addieren mal ganz anders beschrieben, wie man eine Rechenoperation nur auf gerade oder nur auf ungerade Zahlen anwendet.

Das heutige Problem ist ähnlich gelagert: Diesmal soll jedoch nur mit jeder zweiten, dritten oder x-ten Zeile in der Tabelle gerechnet werden.

Auch hier ist die SUMMENPRODUKT-Funktion wieder unser Freund.

Und so geht’s:

Jede zweite Zeile

Zur Veranschaulichung habe ich wieder eine kleine Beispieltabelle vorbereitet. Für diese Tabelle soll nun die Summe über jede zweite Zeile, also über die blau markierten Zellen, ermittelt werden:

Beispieltabelle: Jede zweite Zeile addieren

Beispieltabelle: Jede zweite Zeile addieren

Gehen wir es langsam an und kennzeichnen wir in einer zusätzlichen Spalte mit Hilfe der REST-Funktion jede zweite Zeile:
=REST(ZEILE(B2)-1;2)

Diese Formel zieht von der jeweiligen Zeilennummer den Wert 1 ab, da unsere Tabelle eine Überschrift hat und somit erst in Zeile 2 beginnt. Dann wird dieser Wert durch 2 geteilt. Heraus kommt entweder ein Rest von 1 oder 0:

Hilfsspalte: Mit REST die relevante Zeile bestimmen

Hilfsspalte: Mit REST die relevante Zeile bestimmen

Um für die 1er-Zeilen jetzt die Summe in Spalte B zu bilden, bemühen wir die SUMMENPRODUKT-Funktion. In ihrer einfachsten Form multipliziert sie die Werte aus zwei Spalten und addiert dann die einzelnen Ergebnisse:
=SUMMENPRODUKT(B2:B21;C2:C21)

SUMMENPRODUKT über die Hilfsspalte

SUMMENPRODUKT über die Hilfsspalte

Da wir aber auf die Hilfsspalte gut verzichten können, muss die Formel ein wenig umgebaut werden. Genauer gesagt packen wir jetzt nur die REST-Funktion aus der Hilfsspalte direkt in die SUMMENPRODUKT-Funktion:
=SUMMENPRODUKT(B2:B21;(REST(ZEILE(B2:B21)-1;2)))

Und schon ist die Hilfsspalte überflüssig:

Alles kompakt in einer Formel

Alles kompakt in einer Formel

Jede dritte Zeile

Mit einer kleinen Anpassung funktioniert das Ganze auch, wenn jede dritte Zeile addiert werden soll. In der REST-Funktion verwenden wir jetzt als Teiler den Wert 3 und somit kann bei der Division ein Rest von 0, 1 oder 2 herauskommen. Deshalb wird geprüft, ob der Rest = 1 ist. Das Ergebnis dieser Prüfung ist ein Wahrheitswert WAHR oder FALSCH, den wir mit den doppelten Minuszeichen in 1 oder 0 umwandeln:
=--(REST(ZEILE(B2)-1;3)=1)

Die Summe unter Einbeziehung dieser Hilfsspalte wird wieder über SUMMENPRODUKT ermittelt:
=SUMMENPRODUKT(B2:B21;C2:C21)

REST-Funktion für jede dritte Zeile

REST-Funktion für jede dritte Zeile

Aber auch hier wollen wir natürlich wieder auf die Hilfsspalte verzichten und packen die REST-Funktion direkt in die SUMMENPRODUKT-Funktion:
=SUMMENPRODUKT(B2:B21;(--(REST(ZEILE(B2:B21)-1;3)=1)))

Und wieder kombiniert in einer Formel

Und wieder kombiniert in einer Formel

Dieses Spiel kannst Du jetzt bis zur Verblödung weitertreiben: Du musst lediglich den Teiler in der REST-Funktion anpassen:

Der Teiler macht den Unterschied

Der Teiler macht den Unterschied

Zugegeben, das sind keine Anwendungsfälle, über die du jeden Tag stolpern wirst. Aber sie zeigen die Vielseitigkeit der SUMMENPRODUKT-Funktion. Und wer weiß, vielleicht ist das für Dich ja der Beginn einer großen Freundschaft…

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

7 Gedanken zu “Nur jede x. Zeile addieren

  • Ruben

    Hallo Martin,

    vielen Dank für diesen Beitrag zum Thema Summenprodukt. Ich muss gestehen, die Art wie du die Formel nutz, nutze ich sie nie (Ich nutzte kein “;” sondern immer nur “*”.
    Kannst du mir eventuell sagen,warum du diesen Weg gewählt hast? Ich hätte es so gebaut:
    =SUMMENPRODUKT((B2:B21)*(REST(ZEILE(B2:B21)-1;2)=1))
    Nicht zuletzt, weil ich recht einfach eine Funktin draus bauen kann, die mir z.B. jede 2. und 4. Zeile addiert und ausgibt.
    Würde mich halt interessieren, was die vorteile von dem “;” in Summenprodukt ist

    Viele Grüße

    Ruben

    • Martin Weiß Autor des Beitrags

      Hallo Ruben,

      es gibt keinen speziellen Grund, warum ich das Semikolon verwendet habe. Beides hat hier den gleichen Effekt.

      Schöne Grüße,
      Martin

  • Marcel Pfeifer

    Das gleiche kann auch über eine Matrixformel gelöst werden:
    ={SUMME(WENN((REST(ZEILE(B2:B21)-1;2))=0;B2:B21;0))}
    ={SUMME(WENN((REST(ZEILE(B2:B21)-1;3))=0;B2:B21;0))}
    ={SUMME(WENN((REST(ZEILE(B2:B21)-1;4))=0;B2:B21;0))}

    Interessant wäre ein Vergleich der Performance.

    Schöne Grüße
    Marcel

  • Marcel Pfeifer

    Das gleiche kann auch mit Summenprodukt gelöst werden:
    =SUMMENPRODUKT((B2:B21)*(REST(ZEILE(B2:B21)-1;2)=0))
    =SUMMENPRODUKT((B2:B21)*(REST(ZEILE(B2:B21)-1;3)=0))
    =SUMMENPRODUKT((B2:B21)*(REST(ZEILE(B2:B21)-1;4)=0))

    Schöne Grüße
    Marcel

  • Maja Staus

    Es gibt einen Unterschied in der Reihenfolge der Ausführung.

    So wird bei =SUMMENPRODUKT(Matrix1*Matrix2) erst {Matrix1*Matrix2} gerechnet und anschließend die Vektorsumme berechnet (SUMMENPRODUKT mit einem Argument = Vektorsumme).

    =SUMMENPRODUKT(Matrix1;Matrix2) ist jedoch performanter, da der Zwischenweg über einen Hilfsvektor entfällt, somit Speicher gespart wird und zudem effizientere SSE2-Operationen auf dem Prozessor ausgeführt werden können.