Die weit mehr als 450 Funktionen, die Excel in den aktuellen Versionen mitbringt, können Fluch und Segen zugleich sein. Einerseits gibt es kaum einen Bereich, für den es keine eingebaute Funktion gibt. Auf der anderen Seite ist es schwierig, den Überblick zu bewahren und die im Einzelfall benötigte Funktion überhaupt zu finden – geschweige denn zu wissen, ob es die Funktion überhaupt gibt.
Dabei schlummern einige echte Perlen in den Excel-Tiefen, die eigentlich fast jeder brauchen kann – aber nicht jeder kennt.
Der heutige Artikel gibt einen Überblick über die verschiedenen – wie ich sie nenne – Bedingungsfunktionen. Auch wenn du schon länger mit Excel arbeitest, wirst du in dieser Übersicht vielleicht noch ein paar neue Freunde finden…
Und los geht’s:
Als Bedingungsfunktionen bezeichne ich solche, die eine Berechnung an eine oder mehrere Bedingungen koppeln. Üblicherweise enden die Funktionsnamen auf ..WENN (falls nur eine Bedingung geprüft werden soll) oder WENNS (falls mehrere Bedingungen geprüft werden).
Es gibt momentan 5 solcher Funktionsgruppen (und einen Spezialfall, den ich in einem anderen Artikel noch vorstelle).
1. Summen
Die altbekannte SUMME-Funktion addiert stur sämtliche Werte im angegebenen Bereich. Sollen aber nur Werte summiert werden, die eine bestimmte Bedingung erfüllen, kommt die SUMMEWENN-Funktion zum Einsatz:
=SUMMEWENN(Kriterienbereich; Kriterium; Summenbereich)
Sollen jedoch zwei oder noch mehr Bedingungen/Kriterien erfüllt sein, ist das ein Fall für die SUMMEWENNS-Funktion:
=SUMMEWENNS(Summenbereich; Kriterienbereich 1; Kriterium 1; Kriterienbereich 2; Kriterium 2; ....)
Man beachte, dass hier der Summenbereich an erster Stelle genannt wird. Hier ein paar Beispiele:
Überblick: Summen berechnen
2. Zählen
Wer statt Summen nur die Anzahl von Elementen bestimmen möchte, verwendet dafür die ANZAHL-Funktion. Sollen nicht nur numerische Werte, sondern auch Texte gezählt werden, kommt die Funktion ANZAHL2 zum Einsatz.
Und auch hier gibt es Spezialfunktionen, wenn mit Bedingungen gearbeitet werden soll.
=ZÄHLENWENN(Bereich; Kriterium)
Wichtig:
Anders als bei SUMMEWENN gibt es hier keinen eigenen Kriterienbereich. Das Kriterium wird also direkt im Zählbereich überprüft.
Und wenn mehrere Kriterien zu prüfen sind:
=ZÄHLENWENNS(Kriterienbereich 1; Kriterium 1; Kriterienbereich 2; Kriterium 2; ...)
Warum die Funktionen nicht konsequenterweise ANZAHLWENN und ANZAHLWENNS heißen, weiß wohl nur Microsoft…
Auch hier ein paar Beispiele:
Überblick: Zählfunktionen
3. Durchschnitte
Während die oben vorgestellten Summen- und Zählfunktionen noch den meisten Excel-Anwendern bekannt sein dürften, ist die folgende nicht ganz so häufig im Einsatz.
Mit MITTELWERT berechnet man das arithmetische Mittel, umgangssprachlich also den Durchschnitt.
Die Funktion
=MITTELWERTWENN(Kriterienbereich; Kriterium; Mittelwert-Bereich)
berücksichtigt wieder ein Kriterium, während mit
=MITTELWERTWENNS(Mittelwert-Bereich; Kriterienbereich 1; Kriterium 1; Kriterienbereich 2; Kriterium 2; ...)
beliebig viele Kritieren abgefragt werden können.
Überblick: Mittelwert-Funktionen
4. Minimum
Bisher konnte man mit der MIN-Funktion in Excel das Minimum in einer Werteliste ermitteln. Eine passende …WENN-Funktion zur Abfrage einer Bedingung gab es bisher leider nicht.
Mit Excel 2016 wurde dieser Mangel behoben:
=MINWENNS()
Damit kann auch das Minimum unter Berücksichtigung einer oder mehrerer Bedingungen berechnet werden.
Neu: Minimum-Funktionen
5. Maximum
Und natürlich gibt es auch das passende Gegenstück für das Maximum.
Neu: Maximum-Funktionen
Auch diese Funktion ist erst mit Excel 2016 verfügbar.
Hier nochmal die vorgestellten Funktionen im Überblick:
Bedingungs-Funktionen im Überblick
Und im nächsten Beitrag werde ich noch einen Spezialfall einer Bedingungsfunktion vorstellen.
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 Martin,
danke für die wieder einmal tollen Ideen und die „einfache“ Darstellung.
Hier vielleicht noch ein kleiner Zusatztipp:
Die Auswahl der Kriterien nicht direkt in die Formel mit eintragen, sondern als varibles Feld mit einer Datenüberprüfung und entsprechenden Liste verknüpfen. Macht in Zusammenarbeit „Viel Spaß“.
z.B. =MITTELWERTWENNS(E5:E12;F5:F12;F2;G5:G12;G2)
Gruß
Lutz W.
Hallo Lutz,
vielen Dank für den Hinweis. Mir ging es im Artikel auch nur um die einfachere Nachvollziehbarkeit. Aber es ist natürlich immer eine gute Idee, anstelle von fixen Werten mit Zellbezügen zu arbeiten.
Schöne Grüße,
Martin
Erstmal vielen Dank für diese Excel-Goldgrube und die allerbesten Erklärungen, die ich je gelesen habe. 🙂
Mein Gedank zu den Funktionsnamen:
„Zählenwenn“ vermutlich weil die Funktion in englisch „Count“ heißt, was wiederum „Zählen“ bedeutet. „Anzahl“ wäre „Number“.
Eine schöne Zusammenstellung – die leider in der Praxis mit den Exclusiv-Formeln für 2016 – kaum sinnvoll nutzbar ist. MS Office ist Standard, weil es Kompatibilität herstellt. Die neuen 2016er Funktionen, die nicht per Patch für alte Versionen verfügbar sind, zerstören diese Kompatibilität.
Da man Dateien im Notfall immer Tauschen können muss (und sei es weil die Präsentation spontan auf dem vor Ort verfügbaren Laptop durchgeführt werden muss), darf man diese Funktionen nicht benutzen.
Hallo Jackie251,
du hast natürlich recht damit, dass diese Funktionen insofern mit Vorsicht zu genießen sind, als dass sie eben nur in der neuesten Excel-Version funktionieren. Das Problem besteht aber generell bei allen neuen Features, die mit einer neuen Excel-Version eingeführt werden. Dann dürfte man nie mit neuen Funktionen arbeiten, die es in älteren Versionen noch nicht gab (gilt übrigens nicht nur für Excel).
Der Anwender muss hier tatsächlich im Einzelfall entscheiden, ob ein Datenaustausch mit älteren Versionen notwendig ist oder nicht.
Schöne Grüße,
Martin
Hallo Martin
vielleicht ist noch ein Hinweis für die „neuen“ Befehle in Excel 2016 nötig:
=MAXWENNS()
=MINWENNS()
und auch die aus dem letzten Blogeintrag
=TEXTKETTE()
=TEXTVERKETTEN()
Diese Befehle existieren scheinbar nur beim OFFICE365-Abonnement und nicht in den „normalen“ Excel-Versionen. Hier ist dann anscheinend immer ein Microsoft-Konto nötig.
Bei meinem Excel (OFFICE PROFESSIONAL PLUS) gibt es diese Befehle jedenfalls nicht, oder sie sind super gut versteckt.
Ansonsten muss ich Jackie251 hier zustimmen. Man sollte diese Befehle nicht nutzen, solange Microsoft sie nicht in allen Excel-Versionen zur Verfügung stellt (na ja, zumindest ab Office 2007). Aber ich glaube nicht, dass das je geschehen wird. Microsoft kann oder will sich ja auch nicht vom 29.02.1900 trennen – und 1900 war nun mal kein Schaltjahr. Hier gibt es bessere Lösungen (siehe Libre Office, rechnet ab dem 15.10.1582 bis zum 26.06.9957). Es scheint also zu gehen.
Heinz-Jürgen
Hallo Heinz-Jürgen,
danke für den Hinweis. Ich setze in der Tat die Office-365-Version ein und war mir nicht bewusst, dass bestimmte Funktionen in der normalen 2016-Version nicht verfügbar sind.
Schöne Grüße,
Martin
Hallo HJ,
ich denke, MS will den 29.2.1900 nicht aufgeben. Die Denke wurde damals von Lotus übernommen, als viele noch dachten, 1900 wäre ein Schaltjahr. Das jetzt zu ändern wäre für viele Funktionen – auch in VBA – fatal. Natürlich auch wenn man kompatibel bleiben möchte,was ja nicht unbedingt das Hauptkriterium für Libre Office ist. Es trifft also nur die Leute hart, welche mit einem Datum arbeiten müssen, dass in den ersten 3 Monaten des Jahres 1900 liegt.
Und selbstverständlich gehe ich niemals davon aus, dass neue Funktionen rückwirkend eingebaut werden.
Mein Excelkosmos ist also noch in Ordnung 😉
Pingback: Die Befreiung von Klammern und Schachteln | Der Tabellen-Experte
Hallo,
vielleicht ein wenig kleinlich von mir:
Die Spaltenbezeichnungen in den Formel-Beispielen für minwenns und maxwenns sind falsch.
Ihre Erklärungen sind trotzdem stets gut verständlich.
Es grüßt Rudolf
Hallo Rudolf,
Pfusch am Bau! Das kommt davon, wenn man nachträglich noch Daten verschiebt…
Vielen Dank für den Hinweis, ich habe die Bilder nochmal angepasst. Jetzt sollte es stimmen.
Schöne Grüße,
Martin
Hi,
ich habe schon so viele nützliche Kniffe hier in diesem Forum gefunden, dafür schon mal danke. Ich bin aber heute auf ein Problem gestoßen und konnte es nicht wie sonst fix und elegant durch Ihre Hilfe lösen.
Und zwar wollte ich analog „Mittelwertwenn“ eine Standardabweichung gekoppelt an 3 Bedingungen Knüpfen (Bedingung 1: Fand die Messung in Jahr x statt; Bedingung 2: Welche Methode wurde zur Messung genutzt; Bedingung 3: Welcher Produktgruppe wurde untersucht). Nach langem Suchen konnte ich eine Lösung finden; wobei B6 die Anfangszelle meines Suchbereichs ist, B3 meine Erste Bedingungen enthält (alle weiteren Bedingungen habe ich mit „&“ dahinter eingefügt. Ich habe nur Stabw durch STABW.S ersetzt
=STABW(BEREICH.VERSCHIEBEN(B6;VERGLEICH(B3;A6:A14;0)-1;0;VERGLEICH(B3;A6:A14;1)-VERGLEICH(B3;A6:A14; 0)+1))
und das ganze noch schön als Matrix verpackt:
=STABW.S(BEREICH.VERSCHIEBEN(Tabelle2!B2;VERGLEICH($A3&B$1&B$2;Tabelle2!$A$2:$A$55236&Tabelle2!$F$2:$F$55236&Tabelle2!$I$2:$I$55236;0)-1;0;VERGLEICH($A3&B$1&B$2;Tabelle2!$A$2:$A$55236&Tabelle2!$F$2:$F$55236&Tabelle2!$I$2:$I$55236;1)-VERGLEICH($A3&B$1&B$2;Tabelle2!$A$2:$A$55236&Tabelle2!$F$2:$F$55236&Tabelle2!$I$2:$I$55236;0)+1))
Das Orginalproblem (ich war anscheinend nicht der erste) ist hier nachzulesen:
http://www.herber.de/forum/archiv/1056to1060/1059162_STABW_mit_Bedingung.html
Vielleicht können Sie das ganze nochmal verständlich für andere „Statistikopfer“ aufdröseln. Es würde jedenfalls Ihre „Wenns“ Sammlung noch abrunden.
Falls ich einfach nur zu unkreativ war, die in diesem Forum präsentierten Lösungen auf mein Problem anzuwenden, kann mir vielleicht jemand erklären, was mein Problem genauso gelöst hat – ich bin immer auf der Suche nach neuen eleganteren Wegen 😉
Viele Grüße
Hallo Martin,
vielen Dank für deine Antwort. Aber wie du schreibst „das PROBLEM“ sitzt davor. Die Formel funktioniert einfach nicht. Meine Laienhafte Vermutung ist, das ich in der Spalte Lieferanten z. b. stehen habe „Schäfer ReNr 1234“ nun suche ich aber nur den Namen Schäfer da sich ja die ReNr ändert.
Vile Grüße
Joe
Hallo Joachim,
mit deiner Vermutung liegst du genau richtig. Grundsätzlich erwarten die Funktionen immer eine exakte Übereinstimmung mit den Suchbegriffen. Aber das lässt sich auch umgehen, indem man mit dem Joker-Zeichen * arbeitet. In deinem Fall würde das Suchkriterium also „Schäfer*“ lauten. Dann sollte es auch klappen.
Schöne Grüße,
Martin
Hallo,
vielleicht kann mir jemand folgendes Problem erklären:
Wenn ich mit Summewenn mehrere Kriterien (ODER!) der gleichen Spalte abfragen will, geht das z.B. problemlos so:
=SUMMEWENN(B5:B24;“x“;C5:C24)+SUMMEWENN(B5:B24;“y“;C5:C24)
Das x und y kann ich auch super aus den Zellen dynamisch auslesen (hier E26 und E27)
=SUMMEWENN(B5:B24;E26;C5:C24)+SUMMEWENN(B5:B24;E27;C5:C24)
Ich kann das auch sehr schön in einer Matrixvariante lösen:
=SUMME(SUMMEWENN(B5:B24;{„x“;“y“};C5:C24))
Funktioniert einwandfrei.
Nur sobald ich hier jetzt das ganze dynamisch aus der Zelle erfassen möchte funktioniert es nicht mehr:
=SUMME(SUMMEWENN(B5:B24;E26:E27;C5:C24))
Da kommt eine 0 raus. Markiere ich den Bereich E26:27 und lasse ihn mit F9 ermitteln, steht dann {„x“;“y“} da, wie gewünscht und die Formel sollte doch funktionieren. Gehe ich aber in die Formelauswertung und lasse den ersten Schritt hier auswerten, kommt der Fehler #WERT statt {„x“;“y“}.
Ich habe es auch schon mit INDIREKT versucht, aber das ändert nichts am Problem. Kann jemand erklären, was da falsch läuft?
Danke.
Ralph
Hallo Ralph,
die SUMMEWENN-Formel kann normalerweise keine Zellbereiche als Kriterienparameter verarbeiten, sondern nur einzelne Zellen oder einzelne Werte. In der statischen Variante mit {„x“;“y“} erkennt die Funktion jedoch, dass es sich hier um eine Matrix handelt.
Wenn Du deine dynamische Formel auch als Matrixformel eingibst (also mit Strg+Umschalt+Eingabe) abschließt, dann wird auch diese Variante funktionieren.
(in Office 365/Microsoft 365 ist dies übrigens automatisch der Fall, also auch ohne die spezielle Tastenkombination)
Schöne Grüße,
Martin
Guten Tag, Herr Weiß,
ich bin immer wieder begeistert, wie einfach Sie komplexe Formeln erklären können. Schön, dass es Sie mit diesem Service gibt! 🙂
Viele Grüße
AHa
Hallo AHa,
sehr gerne geschehen und vielen Dank für das tolle Feedback!
Schöne Grüße,
Martin