Archiv des LibreOffice- und OpenOffice.org-Wiki

[ÜberSicht] [TitelIndex] [WortIndex] [SeiteFinden

Der folgende Beitrag beschäftigt sich nicht allein mit der Funktion SUMMEWENN() sondern liefert auch Beispiele für Situationen, in denen die Bedingungsdefinitionen von SUMMEWENN() nicht ausreichen.

1. SUMMEWENN()

1.1. Bestimmte Werte (exakte Werte oder Wertebereiche) eines Bereichs addieren

In der Standardversion der Funktion bezieht sich die Wert-Prüfung auf die Zellen selbst, die addiert werden sollen. Der Ausdruck

=SUMMEWENN(A1:D14;5)

addiert den Inhalt aller Zellen im Bereich A1:D14, die den Wert 5 enthalten.

Außer einem exakten Wert können aber auch Operatoren wie Größer als/Kleiner als verwendet werden:

=SUMMEWENN(A1:D14;">10")
=SUMMEWENN(A1:D14;"<>17")

SUMMEWENN unterstützt auch reguläre Ausdrücke. Da man mit regulären Ausdrücken normalerweise Zeichenketten nach bestimmten Mustern durchsucht, scheint die Verwendung hier auf den ersten Blick unsinnig, da die Zellen ja Zahlen enthalten und keine Zeichenketten. Ein Beispiel, bei dem die Zahlen eines SUMMEWENN-Zellbereichs als Zeichenkette betrachtet werden, könnte z.B. folgende Bedingung sein:

Es sollen alle Zahlen addiert werden, die zwischen 100 und 999 liegen.
Dabei handelt es sich um 3-stellige Zahlen, welche Zahlen genau spielt dabei keine Rolle. Der reguläre Ausdruck für genau ein beliebiges Zeichen ist ein Punkt. SUMMEWENN löst die Aufgabe daher so:

=SUMMEWENN(A1:D14;"...")

Mit

=SUMMEWENN(A1:D14;"[12]..")

werden alle 3-stelligen Zahlen addiert, die entweder mit einer 1 oder einer 2 beginnen, also alle Zahlen, die zwischen 100 und 299 liegen.

1.2. Bedingungsbereich <> Wertebereich

1.2.1. Werte einer Spalte aufgrund des Inhalts einer anderen Spalte addieren

Bei zeilenweisem Tabellenaufbau z.B. in Form eines Journals, einer Ausgabenliste, einer Verkaufsstatistik u.ä. werden die Auswahlkriterien oft nicht durch die zu addierenden Zellwerte bestimmt, sondern befinden sich in einer anderen Spalte. Z. B. sollen alle Ausgaben addiert werden, die das Auto betreffen.

Die Tabelle soll in Spalte A die Klassifizierung der Ausgaben enthalten (Auto, Wohnung, Lebensmittel....), in Spalte B die Beträge:

=SUMMEWENN(A1:A14;"Auto";B1:B14)

Im ersten Parameter (A1:A14) wird der Bereich angegeben, auf den die Kriterien anzuwenden sind, im dritten Parameter dann der Bereich, der addiert werden soll.

Hier nun wird die Anwendung von regulären Ausdrücken noch interessanter als im vorigen Beispiel.

Vielleicht unterscheiden Sie in Ihrer Ausgabenliste zwischen den Ausgaben für Benzin und sonstigen Ausgaben rund ums Auto, möchten aber beide Kategorien bei Bedarf auch zusammenfassen. Man erreicht das mit der Formel:
=SUMMEWENN(A1:A14;"Auto";B1:B14)+SUMMEWENN(A1:A14;"Benzin";B1:B14)
oder einfacher:

=SUMMEWENN(A1:A14;"Auto|Benzin";B1:B14)

Oder in Ihrer Verkaufsstatistik sollen alle Verkaufszahlen (Spalte B) irgendwelcher Drucker zusammengefasst werden. Die Artikelbezeichnungen (Spalte A) sind unterschiedlich, enthalten aber immer die Zeichenkette Drucker oder drucker:

=SUMMEWENN(A1:A14;".*drucker.*";B1:B14)

1.2.2. Mehrere Spalten

Auch wenn Wertebereich und Bedingungsbereich mehr als 1 Spalte umfassen, kann SUMMEWENN() angewandt werden. Wertebereich und Bedingungsbereich müssen dabei auch nicht auf gleicher Zeilenhöhe stehen. Aber die Ausdehnung der Bereiche muss gleich sein.

A

B

C

D

1

ja

 

 

 

2

 

ja

 

 

3

ja

 

1

1

4

 

ja

1

1

5

 

ja

1

1

6

 

 

1

1

7

 

 

1

1

8

Anzahl "ja" in A1:A5:

Anzahl "ja" in B1:B5:

=SUMMEWENN(A1:B5;"ja";C3:D7):

9

2

3

5

2. Bedingungen werden mit weiteren CALC-Funktionen formuliert: Matrix-Formeln

Wenn die Bedingungen, die entscheiden sollen, ob ein Wert in die Summenformel aufzunehmen ist oder nicht, mit weiteren CALC-Funktionen definiert werden müssen, so muss man zu anderen Funktionen greifen, mit SUMMEWENN lassen sich keine Konstrukte bilden wie Alle Beträge zwischen 45 und 115 oder alle ungeraden Beträge oder alle Beträge, die mit der entsprechenden Zelle in einer anderen Spalte übereinstimmen.

Calc kann einige Funktionen zu Matrix-Funktionen erweitern und bietet darüber hinaus auch spezielle Matrixformeln an. Matrixformeln sind ein sehr mächtiges Instrument. Hier sollen nur einige einfache Beispiele aufgezeigt werden, ausführlichere Informationen finden sich auf der Seite CalcFunktionenMatrix.

2.1. SUMME() als Matrixformel

SUMME() wird zwar normalerweise nicht als Matrixformel geführt, da man in einer Summenfunktion aber meist mit Zellbereichen arbeitet, liegt es nahe, diese Funktion zu einer "richtigen" Matrixformel zu erweitern.

Wir fangen mit einer einfachen Aufgabenstellung an. Es sollen alle Werte der Spalte A addiert werden, die zwischen 90 und 120 liegen. Ob ein Wert innerhalb dieses Bereichs liegt, lässt sich einfach prüfen. Die Formel:

=A1>=90

liefert als Ergebnis eine 1, wenn die Bedingung erfüllt ist und eine 0, wenn das nicht der Fall ist.

Das gleiche gilt für

=A1<=120

Wenn man diese beiden Wahrheitswerte nun multipliziert, so erhält man wieder entweder eine 1 oder eine 0. Eine 1 erhält man nur, wenn beide Wahrheitswerte 1 sind, also beide Bedingungen zutreffen:
=(A1>=90)*(A1<=120)

Zurück zu unserer Anforderung: Addiere alle Werte zwischen 90 und 120. Wenn wir jetzt eine zusätzliche Spalte bilden würden:

Spalte B:
=(A1>=90)*(A1<=120)*A1
=(A2>=90)*(A2<=120)*A2
=(A3>=90)*(A3<=120)*A3
...

so würden wir eine Spalte bekommen, in der alle Zellen 0 enthalten, deren Pendant in Spalte A außerhalb des Bereichs 90-120 liegt, während alle Werte, welche die Bedingung erfüllen, nach zweimaliger Multiplikation mit 1 unverändert angezeigt würden. Auf diese Hilfsspalte könnten wir nun einfach die Funktion SUMME() anwenden.

Statt nun mit zusätzlichen Spalten zu arbeiten, erweitern wir die Funktion SUMME() zur Matrixformel:

{=SUMME((A1:A100>=90)*(A1:A100<=120)*(A1:A100))}

Die geschweiften Klammern dürfen dabei nicht direkt eingegeben werden, sondern werden von Calc gesetzt, wenn man die Formel mit CTRL+SHIFT+RETURN abschließt oder im Funktions-Assistenten das Feld Matrix aktiviert.

Ein weiteres Beispiel: Addiere alle geraden Beträge im Bereich B5:B21.

{=SUMME((ISTGERADE_ADD(B5:B21))*(B5:B21))}

Oder auch eine Formel die alle Zellen in Spalte A addiert, die mit dem Wert in Spalte B übereinstimmen.

{=SUMME((A1:A15=B1:B15)*(A1:A15))}

2.2. Die Matrixfunktion SUMMENPRODUKT

Die Funktion SUMMENPRODUKT() ist per Definition bereits eine Matrixformel und benötigt dadurch keine geschweiften Klammern. Auch muss hier nicht multipliziert werden, da auch das bereits Teil der Funktion ist.

Die Formel

=SUMMENPRODUKT(B5:B21;NICHT(ISTNV(C5:C21)))

addiert alle Zellen in Spalte B, deren Pendant in Spalte C nicht den Fehlerwert #NV enthält.

Ein weiteres Beispiel:
Spalte A enthält Datumswerte, Spalte B Beträge. Es sollen alle Beträge in Spalte B addiert weden, die dem Monat 2 zugeordnet werden können.

=SUMMENPRODUKT(MONAT(A5:A20)=2;B5:B20)

Oder als ABC-Statistik:

=SUMMENPRODUKT(C1:C21;(C1:C21*100/SUMME(C1:C21))>5)

Addiert sämtliche Zellen, deren Wert über 5% des Gesamtbetrags ausmachen.
Und wenn man dann noch wissen möchte, um wieviele Positionen es sich dabei handelt:

=SUMMENPRODUKT((C1:C21*100/SUMME(C1:C21))>5)

2.3. Siehe auch

3. Datenbankfunktionen

Die Datenbank-Funktionen arbeiten mit einem Kriterienbereich. Dabei können sowohl UND- wie auch ODER-Kriterien festgelegt werden, auf die sich dann die Funktion DBSUMME bezieht.

Die Funktion

=DBSUMME(A4:B20;"Betrag";F2:F3)

addiert aus dem Datenbereich A4:B20 alle Werte der Spalte Betrag, die sich in Zeilen befinden, welche durch die Auswahlkriterien im Kriterienbereich F2:F3 bestimmt werden.

Mehr zu den Datenbank-Funktionen auf der Seite CalcFunktionenDatenbank

4. Summe von gefilterten Daten

Eine einfache und effiziente Art, Datensätze zu filtern bietet der AutoFilter. Oft bildet man für bestimmte Datenspalten Ergebnisformeln, die dann aber nur die gefilterten Datensätze berücksichtigen sollen. Funktionen wie SUMME() oder ANZAHL() kümmern sich aber nicht um einen gesetzten Filter sondern berücksichtigen stets den gesamten Datenbereich. Etwas wie addieren wenn im Filter enthalten gibt es weder für SUMME noch für SUMMEWENN.

Dafür gibt es die Funktion TEILERGEBNIS(). Diese berücksichtigt in einem gefilterten Datenbereich nur diejenigen Zeilen, die nach der Filterung erhalten bleiben:

sw1.png

Dass TEILERGEBNIS() die Funktion SUMME() meint, wird durch den ersten Parameter der Formel bestimmt, im Beispiel also die 9. Es können aber auch andere Funktionen verwendet werden, wie MAX() oder ANZAHL(). Eine aktuelle Liste findet sich im Hilfetext.

KategorieCalc


LizenzBedingungen | AnbieterKennzeichnung | DatenschutzErklärung | Stand: 2013-04-28