Vor ein paar Wochen hatte ich beschrieben, wie man in einer Tabelle Summen bilden kann, bei denen unterschiedliche Kriterien innerhalb einer Spalte erfüllt sein müssen. In meiner Lösung habe ich dafür die SUMMENPRODUKT-Funktion verwendet (falls du den Artikel verpasst hast, kannst du ihn gerne hier nachlesen).
Danach hat mich der Leser Matthias Rack in den Kommentaren auf eine alternative Lösung hingewiesen, vielen Dank nochmal für die Anregung! Diesen Hinweis nehme ich heute zum Anlass, um dir eine etwas in die Jahre gekommene, aber trotzdem nützliche und sehr flexible Funktion vorzustellen: DBSUMME
Und so geht’s:
Beispieldatei herunterladen
Die Datenbank-Funktionen
Excel bietet seit Menschengedenken eine ganze Reihe sogenannter DB-Funktionen (alle beginnen mit den Buchstaben DB für Datenbank). Allerdings sind diese Funktionen im Laufe der Jahre ziemlich in den Hintergrund getreten, in aktuellen Excel-Versionen sind sie nicht einmal mehr im Menüband „Formeln“ zu finden. Nichtsdestotrotz gibt es sie noch und sie haben durchaus auch weiterhin ihre Berechtigung.
DBSUMME ist wahrscheinlich die am ehesten genutzte Funktion aus dieser Familie. Darüber hinaus gibt es unter anderem auch DBANZAHL, DBMIN, DBMAX, DBMITTELWERT und noch einige mehr.
Das Grundkonzept bei diesen Funktionen ist aber immer gleich, folgende Argumente werden erwartet:
- ein Datenbankbereich. Das ist die eigentliche Datentabelle
- ein Datenbankfeld. Das ist die Spalte, auf die eigentliche Berechnung (Summe, Mittelwert, Anzahl) angewendet werden soll
- ein Kriterienbereich. Dort können für jedes Feld in der Datentabelle einschränkende Bedingungen festgelegt werden
Am Beispiel der Funktion DBSUMME kannst du hier sehen, wie das aussehen könnte:
Die Summe über alle Datensätze
Der im ersten Argument angegebenen Bereich A1:C21 ist hier der Datenbankbereich. Bei mir handelt es sich um eine ganz einfache Liste. Wichtig dabei ist, dass im angegebenen Bereich auch die Spaltenüberschriften enthalten sein müssen.
Ausgewertet werden sollen die Umsätze, daher wurde in der Formel als zweites Argument die Überschrift der betreffenden Spalte, also „Umsatz“ angegeben. Alternativ wäre es auch möglich, die Spaltennummer der Umsatzspalte innerhalb des Datenbankbereichs anzugeben:
=DBSUMME(A1:C21;3;F1:H2)
Der Zellenbereich F1:H2 als drittes Argument schließlich ist unser Kriterienbereich. Jede dort verwendete Spaltenüberschrift muss identisch mit einer Überschrift im Datenbankbereich sein. Es müssen aber nicht zwingend alle Felder im Kriterienbereich verwendet werden, die in der Datentabelle vorhanden sind.
Im oben gezeigten Beispiel wurden im Kriterienbereich keine Bedingungen eingetragen (Zeile 2 ist leer), daher wird die Summe über alle Umsätze gebildet.
Arbeiten mit Bedingungen
Schränken wir jetzt die Summe auf ein einzelnes Produkt ein, indem wir im Kriterienbereich das gewünschte Produkt eintragen:
Die Summe für das ausgewählte Produkt
Und natürlich lassen sich auch mehrere Kriterien miteinander kombinieren. Im folgenden Beispiel sollen nur Umsätze des Produkts „Carbonic“ summiert werden, die kleiner oder gleich 20.000 Euro sind:
Die Summe für das ausgewählte Produkte und Umsatzgröße
Wenn mehrere Kriterien verwendet werden, gilt dabei folgendes zu beachten:
- Kriterien in der gleichen Zeile werden über ein logisches UND verknüpft
- Kriterien in unterschiedlichen Zeilen werden über ein logisches ODER verknüpft
Hier ein Beispiel dazu:
Mehrere Kriterien über ein logisches ODER kombiniert
Der Kriterienbereich wurde um eine Zeile auf F1:H3 ausgeweitet. Summiert werden jetzt alle Carbonic-Produkte mit einem Umsatz von 20000 oder weniger. Dazu kommen alle Tango-Produkte, und zwar unabhängig von der Umsatzhöhe. Denn die Umsatzspalte in der zweiten Kriterienzeile ist leer, was soviel bedeutet wie „keine Einschränkung“.
Noch ein weiteres Beispiel:
Mehrere Kriterien über ein logisches ODER kombiniert
Wie kann man jetzt aber Umsatzbereiche als Kriterium festlegen? Also zum Beispiel alle Umsätze zwischen 15.000 und 20.000 Euro? Dazu muss das Umsatzfeld nur ein zweites Mal in den Kriterienbereich aufgenommen werden:
Zwei Umsatzfelder als Kriterien
Man muss immer nur aufpassen, dass der Kriterienbereich nur so viele Zeilen und Spalten umfasst, wie wirklich notwendig sind. Wer also sozusagen „auf Vorrat“ den Kriterienbereich um ein paar leere Zeilen erweitert, wird keine Freude haben. Denn eine leere Zeile bedeutet „keine Einschränkung“ und damit wird wieder alles summiert:
Vorsicht Falle: Leere Zeilen im Kriterienbereich
Noch mehr Flexibilität durch Joker
Bei der Angabe der Bedingungen lassen sich auch die Jokerzeichen * und ? verwenden. In allen mir sonst bekannten Einsatzgebieten für Joker bedeutet üblicherweise ein Stern eine beliebige Anzahl von beliebigen Zeichen, jedes Fragezeichen steht für jeweils ein einzelnes beliebiges Zeichen. Aus mir nicht bekannten Gründen ist das hier jedoch anders. Stern und Fragezeichen werden in DBSUMME offensichtlich synonym verwendet, das heißt beide stehen für eine beliebige Anzahl von beliebigen Zeichen:
Auch Jokerzeichen sind möglich
Fazit
Wie man an den Beispielen hoffentlich gesehen hat, ist DBSUMME wesentlich flexibler als SUMMEWENNS-Funktion. Zwar lassen sich auch mit letztgenannter Funktion mehrere Bedingungen nutzen, aber eben nur mit einem logischen UND, während DBSUMME die Bedingungen beliebig mit UND bzw. ODER kombinieren kann.
Hast du die DBSUMME-Funktion (oder eine andere DB-Funktion) schon im praktischen Einsatz gehabt? Lass es uns in den Kommentaren wissen.
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.
Hallo, danke für die Berücksichtigung der DB Funktionen. Man beachte noch, dass es sich dabei um Funktionen handelt, diese lassen sich kopieren um eine schnelle Auswertung vorzunehmen. Wenn man also die Auswerte-Funktionen (ich benutze auch gerne auch DBMittelwert ()und DBAnzahl()) und den Kriterienbereich kopiert und jeweils ein anderes Kriterium einträgt (hier zum Beispiel unterschiedliche Produkte) kann man auf sehr schnelle und auf eine elegante Art eine Liste komplett auswerten. Es muss dann auch nicht aktualisiert werden wie bei Pivot, denn es ist ja eine aktive Rechenfunktion.
LG Matthias
Hallo Matthias,
danke für die ergänzenden und hilfreichen Hinweise.
Schöne Grüße,
Martin
Wow, eine klasse Funktion, die scheinbar das Potenzial hat, meine unzähligen Matritzenformeln ( ={summe(wenn((…)*(…);…))} ) zu ersetzen!
Hallo Rene,
ja, das könnte eine gute Alternative sein.
Schöne Grüße,
Martin
Hallo Martin,
bei mir funktioniert die Unterscheidung von * und ? (Excel 2019, 64-Bit).
Als Ergänzung vielleicht noch der Hinweis, dass Groß- und Kleinschreibung nicht unterschieden wird.
Außerdem wird an das Ende des Kriteriums bei der Auswertung noch automatisch ein Sternchen angefügt. So werden bei dem Kriterium „Test“ sowohl die Zeilen mit „Test“ als auch die mit „testen“ summiert. Das kann zu ziemlich überraschenden Ergebnissen führen .
VG Jan
Hallo Jan,
das ist interessant zu hören, danke für die Info. Offensichtlich hat sich da in meiner Version von M365 irgendetwas geändert. Vielleicht wird das auch wieder korrigiert, macht ja nicht wirklich viel Sinn.
Schöne Grüße,
Martin
Hallo Martin,
ich kenne natürlich Deine Beispiele nicht, mit denen Du das ausprobiert hast. Aber kann es sein, dass das automatische *, dass Excel an das Ende hängt, den Eindruck vermittelt hat, dass ein ? wie ein * behandelt wird?
VG Jan
Hallo Jan,
du hast Recht, es liegt wirklich an dem automatischen (nicht sichtbaren) * das Excel anscheinend am Ende eines Suchkriteriums anfügt. Wenn ich mit den Fragezeichen innerhalb einer Textkette arbeite, dann klappt die Unterscheidung. Das ist ja wirklich mehr als verwirrend.
Schöne Grüße,
Martin
Guten Abend, auch ich bin begeistert. Wunderbar einfach. Werde die DB Funktionen bei meinen nächsten Auswertungen testen. Sie haben Potenzial. Werde sie gleich weiter senden.
Vielen Dank für den Hinweis.
Hallo Karin,
danke für dein schönes Feedback, freut mich zu hören.
Schöne Grüße,
Martin
Hallo Martin,
du schriebst „… Jede dort verwendete Spaltenüberschrift muss identisch mit einer Überschrift im Datenbankbereich sein. …“.
Das ist nicht korrekt. Du kannst die Spaltenüberschrift auch leer lassen oder einen beliebigen Text hineinschreiben, der den Filter erklärt. Als Kriterium kannst Du einen beliebigen Ausdruck als Formel eingeben, der zu wahr oder falsch ausgewertet wird.
Z. B. wenn Du Groß- und Kleinschreibung unterscheiden willst und auch kein Stern am Ende eingefügt haben willst, kannst Du in das Kriterienfeld folgende Formel schreiben:
=Identisch(A2;“Test“)
Wenn die Überschrift des Kriteriums nicht in dem Datenbereich vorkommt, geht Excel davon aus, dass sich das „A“ aus A2 auf die auszuwertende Spalte bezieht und die Zahl aus A2 die erste Zeile des Datenbereichs ist.
Die DBSUMME-Funktion würde also alle Zeilen in die Summierung einbeziehen, wo in Spalte A exakt „Test“ steht, wenn der Datenbereich der Tabelle in der 2. Zeile beginnt..
VG Jan
Hallo Jan,
ich habe deinen Hinweis gerade ausprobiert. Und es stimmt: Wenn man im Kriterienbereich mit einer wie von dir beschriebenen Formel arbeitet, dann spielt die Spaltenüberschrift keine Rolle. Diese Variante war mir bisher nicht bekannt.
Gibt man das Suckriterium jedoch fix in den Kriterienbereich ein (so wie in meinen Beispielen), dann spielt die Spaltenüberschrift sehr wohl eine Rolle und muss identisch mit der Überschrift im Datenbankbereich sein. Denn wie sollte Excel sonst wissen, welches Feld gemeint ist.
Oder habe ich irgendetwas übersehen?
Schöne Grüße,
Martin
Hallo Martin,
es ist so wie Du schreibst. Wenn man nur die Bedingungen für eine Spalte definieren will, muss man den Spaltennamen darüber schreiben. Wie in Deinen Beispielen.
Berechnet man das Kriterium mit einer Formel, darf man keinen Spaltennamen aus der Tabelle verwenden. Verwendet man relative Bezüge, sollte man die erste Zeile der jeweiligen Spalte nehmen, damit der Bezug auf alle Werte der Spalte angewendet wird. Absolute Bezüge gehen dagegen nur auf die genannten Zellen.
Viele Grüße
Jan
Viele Grüße
Jan
Tolle Funktion, sehr übersichtlich und einfach zu verstehen.
Habe sie bislang nicht gekannt. Werde sie bei nächster Gelegenheit einsetzen.
Vielen dank für`s bekanntmachen.
Mit besten Grüßen
Ulrich Spieth
Hallo Ulrich,
freut mich zu hören, wenn’s weiterhilft!
Schöne Grüße,
Martin
Guten Morgen Martin,
vielen Dank, dass du immer wieder Artikel über Funktionen veröffentlichst, mit denen direkt eine Vielzahl von Anwendungsmöglichkeiten entstehen.
Mir geht es gerade mit DBSUMME so, da ich diese als eine schöne Alternative zum derzeit genutzten Datenschnitt sehe und diese auch mit älteren Versionen von Excel noch funktionieren kann :-).
Viele Grüße
Andreas
Hallo Andreas,
das ist das Schöne an Excel, dass es meistens viele unterschiedliche Wege gibt, eine Aufgabe zu lösen. Und je nach konkreter Anforderung muss man sich nur das richtige Werkzeug herauspicken.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die Einführung, sehr interessant!
Ich habe eine grundlegende Frage, die wahrscheinlich stupid klingt. Woher weiß die Formel dass die Spalte Umsatz zu summieren ist?
Und was wäre es wenn es in dem Datenbankbereich mehr als eine Spalte mit Zahlen gibt, die summiert werden könnten, z.B. Umsatz und Menge? Welche Spalte wird dann summiert? Wie kann man selbst festlegen, welche Spalte zu summieren ist?
Danke und Gruß,
Wei
Hallo Wei,
danke für dein schönes Feedback. Was deine Frage angeht: Der zweite Parameter in der DBSUMME-Funktion legt fest, welche Spalte summiert werden soll. In meinem Beispiel habe ich die Spalte „Umsatz“ angegeben, aber es kann in großen Tabellen natürlich jede beliebige andere Spalte angegeben werden.
Schöne Grüße,
Martin
Hallo,
sehr interessante Funktion.
Eine Frage: Du schreibst „Man muss immer nur aufpassen, dass der Kriterienbereich nur so viele Zeilen und Spalten umfasst, wie wirklich notwendig sind. Wer also sozusagen „auf Vorrat“ den Kriterienbereich um ein paar leere Zeilen erweitert, wird keine Freude haben.“
Kann man mit „Indirekt“ (oder so) die Formel so bauen, dass der Kriterienbereich flexibel ausgelesen wird mit „finde die letzte Zeile in dem Bereich und diese übernimm für die Funktion“?
Hallo Tobias,
es wäre grundsätzlich vorstellbar, den Bereich dynamisch zu gestalten, also angepasst auf die verwendeten Zeilen im Kriterienbereich. Dürfte aber vermutlich relativ komplex werden, da ja immer mehrere Spalten zu berücksichtigen wären, die im Kriterienbereich gefüllt sein könnten. Eine konkrete Lösung dafür kann ich im Moment nicht anbieten.
Schöne Grüße,
Martin