Samstag, 6. Februar 2016
10:04 Uhr
10:04 Uhr
Excel: Verschiedene Möglichkeiten einen Mittelwert über Zahlen ohne Nullwerte zu ermitteln
Das Positive an Excel ist ja, dass es mehr als einen Weg gibt um per Formel das gewünschte Ergebnis zu erhalten So hatte ich eine Tabelle von Kostenstellen bei denen für einen bestimmten Wert ein Durchschnitt gebildet werden soll. Es handelte sich um zehn Kostenstellen und insgesamt vier Werten. Wobei bei den anderen Kostenstellen eine 0 ausgewiesen wurde.
Um nun einen Durchschnittwert für diesen Betrag zu erhalten, war mein erster Gedanke, dass ich die Formel Mittelwert verwende. Das Ergebnis brauchte, durch die Berücksichtigung der 0 bei einzelnen Kostenstellen nicht das von mir vorgesehene Ergebnis.
Anhand der abgebildeten Tabelle wird dieses deutlich:
In der Zelle B12 habe ich als Formel =MITTELWERT(B2:B11) eingetragen und als Durchschnittswert 80,00 € erhalten. Allerdings liegen ja die Einzelwerte allesamt über den Durchschnitt und für eine Planung wäre dieses dann eher ein ungünstiger Planansatz.
Die Formel ANZAHL war mein erster Ansatz, allerdings gibt es hier nicht die Möglichkeit, ein einschränkendes Argument einzugeben.
Durch einen Beitrag auf clevercalcul "So lässt sich die Funktion SUMMENPRODUKT nutzen, 13 Fälle" bin ich auf die Formel SUMMENPRODUKT gelandet, die auch weitere Argumente mit aufnehmen kann.
Hier ist eine ziemlich geniale Anwendung der Formel eine Überprüfung eines Bereiches mit zu übergeben wo die Werte ungleich 0 sind.
Die entsprechende Formel lautet:
Dabei wird ein WAHR als 1 und ein Falsch als 0 gezählt, so dass ich hier die Summe aus 0,1,0,1,0,0,1,0,1,0 mit 1 multipliziere und damit 4 erhalte.
Begeistert über diese Anwendung hatte ich diese ebenfalls im Kommentar eingegeben und wurde direkt auf zwei weitere, tatsächlich einfachere, Möglichkeiten hingewiesen.
Durch die Formel ZÄHLENWENN werden die nichtleeren Zellen eines Bereiches gezählt, deren Inhalt mit den Suchkriterium übereinstimmen. Konkret lautet die Formel dann:
Insgesamt erhalte ich bei beiden Formeln dann als Durchschnittswert 200, was schon eher meiner Vorstellung entspricht.
Die einfachste Möglichkeit wäre hier die Formel MITTELWERTWENN
Konkret würde diese wie folgt aufgebaut sein:
Hierzu wird die Formel
Die Formel wird von Exceel darauf zwischen zwei geschweiften Klammern { und } als Matrixformel dargestellt. Eine direkte Eingabe der Formel mit geschweiften Klammern vor und hinter der Formel konvertiert die Formel jedoch nicht in eine Matrixformel, weswegen diese direkt über die Tastenkombination abgeschlossen wird. Dieses gilt auch, wenn die Formel nachträglich geändert wird und dann erneut mit STRG + UMSCHALT (Shift) + EINGABETASTE (Enter) abgeschlossen werden muss.
Zur Verdeutlichung dient folgende Abbildung:
In jeden Fall ist hier dann tatsächlich der Mittelwert über die Einzelwerte 100, 200, 400 und 100 genommen werden und dürfte für einen Planansatz tatsächlich sinnvoller als das arithmetische Mittel von 80 sein.
Zur besseren Lesbarkeit der Formel, sollte ich aber tatsächlich künftig MITTELWERTWENN verwenden, da dieses dann auch schon anhand der Funktion beschreibt, welchen Durchschnitt ich hier berechnen möchte. Die verschiedenen mathematischen Mittelwertbetrachtungen (inkl. Häufigkeiten und Ausreißer) habe ich bei meiner Überlegung nun bewust ausgeblendet sondern lediglich das arithmetische Mittel ohne NULL betrachtet. Wie sagte mal ein Kollege "Als Controller sollte man nicht mehr als die Grundrechenarten anwenden +, -, / und * reichen in der Regel für 95 % aller Probleme aus...." . Insgesamt hat mir die Überlegung, trotz umständlichen Weg, sehr viel Spaß gemacht und auch der Austausch mit Gerhard Pundt von clevercalcul war hier sehr hilfreich, obgleich ich letzte Woche tatsächlich etwas durcheinander war und sein Beispiel statt ZÄHLENWENN mit ZÄHLEWENN versucht hatte nachzuvollziehen und über die Fehlermeldung erstaunt war... was manchmal so ein Buchstabe eben ausmacht ;-).
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionenzu finden. Mein Weiterbildungsangebot zu SAP Themen finden Sie auf unkelbach.expert.
Um nun einen Durchschnittwert für diesen Betrag zu erhalten, war mein erster Gedanke, dass ich die Formel Mittelwert verwende. Das Ergebnis brauchte, durch die Berücksichtigung der 0 bei einzelnen Kostenstellen nicht das von mir vorgesehene Ergebnis.
Anhand der abgebildeten Tabelle wird dieses deutlich:
In der Zelle B12 habe ich als Formel =MITTELWERT(B2:B11) eingetragen und als Durchschnittswert 80,00 € erhalten. Allerdings liegen ja die Einzelwerte allesamt über den Durchschnitt und für eine Planung wäre dieses dann eher ein ungünstiger Planansatz.
Die Formel MITTELWERT stellt mathematisch das arithmetische Mittel dar. Hierbei wird eine Summe über alle Einzelwerte gebildet und diese durch die Anzahl der Werte dividiert. Entsprechend werden hier auch die Nullwerte berücksichtigt.
Um nun aber einen Mittelwert ohne 0 Werte zu berechnen, muss ich bei der Anzahl der Werte die 0 ausschliessen.
Eigentlich gibt es hier verschiedene Lösungsansätze von denen ich einige vorstellen mag.
Anzahl Werte ohne Null
Meine erste Überlegung war es die Summe über die Einzelwerte zu bilden und dann in Excel die Anzahl der Werte zu ermitteln, die nicht 0 sind.Die Formel ANZAHL war mein erster Ansatz, allerdings gibt es hier nicht die Möglichkeit, ein einschränkendes Argument einzugeben.
Durch einen Beitrag auf clevercalcul "So lässt sich die Funktion SUMMENPRODUKT nutzen, 13 Fälle" bin ich auf die Formel SUMMENPRODUKT gelandet, die auch weitere Argumente mit aufnehmen kann.
Hier ist eine ziemlich geniale Anwendung der Formel eine Überprüfung eines Bereiches mit zu übergeben wo die Werte ungleich 0 sind.
Die entsprechende Formel lautet:
=SUMMENPRODUKT((B2:B11<>0)*1)
Dabei wird ein WAHR als 1 und ein Falsch als 0 gezählt, so dass ich hier die Summe aus 0,1,0,1,0,0,1,0,1,0 mit 1 multipliziere und damit 4 erhalte.
Begeistert über diese Anwendung hatte ich diese ebenfalls im Kommentar eingegeben und wurde direkt auf zwei weitere, tatsächlich einfachere, Möglichkeiten hingewiesen.
Durch die Formel ZÄHLENWENN werden die nichtleeren Zellen eines Bereiches gezählt, deren Inhalt mit den Suchkriterium übereinstimmen. Konkret lautet die Formel dann:
Wenn ich nun einen dieser beiden Werte dann als Divisor (Nenner) und die Summe der Einzelwerte als Dividend (Zähler) nehme erhalte ich durch Summe / Anzahl zu berücksichtigten Werte das von mir eigentlich erhoffte Ergebnis, welches auch in folgender Abbildung ersichtlich ist.ZÄHLENWENN(B2:B11;"<>0")
Insgesamt erhalte ich bei beiden Formeln dann als Durchschnittswert 200, was schon eher meiner Vorstellung entspricht.
Mittelwert ohne 0 Werte
Mit etwas Abstand zu dieser Berechnung gibt es allerdings auch in Excel nicht nur die indirekte Berechnung eines passenden Durchschnitt sondern auch zwei Möglichkeiten einen Mittelwert ohne Null zu berechnen.Die einfachste Möglichkeit wäre hier die Formel MITTELWERTWENN
Konkret würde diese wie folgt aufgebaut sein:
Hier wird tatsächlich nur ein Mittelwert gebildet, wenn die einzelnen Zellen keine 0 enthalten. Diese Formel ist ab Excel 2007 verfügbar. Davor war es erforderlich eine Matrixformel zu erstellen.MITTELWERTWENN(B2:B11;"<>0";B2:B11)
Hierzu wird die Formel
eingegeben und muss mit STRG, UMSCHALTTASTE (Shift) und EINGABETASTE (Enter) in eine Matrixformel umgewandelt werden.MITTELWERT(WENN(B2:B11<>0;B2:B11))
Die Formel wird von Exceel darauf zwischen zwei geschweiften Klammern { und } als Matrixformel dargestellt. Eine direkte Eingabe der Formel mit geschweiften Klammern vor und hinter der Formel konvertiert die Formel jedoch nicht in eine Matrixformel, weswegen diese direkt über die Tastenkombination abgeschlossen wird. Dieses gilt auch, wenn die Formel nachträglich geändert wird und dann erneut mit STRG + UMSCHALT (Shift) + EINGABETASTE (Enter) abgeschlossen werden muss.
Zur Verdeutlichung dient folgende Abbildung:
In jeden Fall ist hier dann tatsächlich der Mittelwert über die Einzelwerte 100, 200, 400 und 100 genommen werden und dürfte für einen Planansatz tatsächlich sinnvoller als das arithmetische Mittel von 80 sein.
Zur besseren Lesbarkeit der Formel, sollte ich aber tatsächlich künftig MITTELWERTWENN verwenden, da dieses dann auch schon anhand der Funktion beschreibt, welchen Durchschnitt ich hier berechnen möchte. Die verschiedenen mathematischen Mittelwertbetrachtungen (inkl. Häufigkeiten und Ausreißer) habe ich bei meiner Überlegung nun bewust ausgeblendet sondern lediglich das arithmetische Mittel ohne NULL betrachtet. Wie sagte mal ein Kollege "Als Controller sollte man nicht mehr als die Grundrechenarten anwenden +, -, / und * reichen in der Regel für 95 % aller Probleme aus...." . Insgesamt hat mir die Überlegung, trotz umständlichen Weg, sehr viel Spaß gemacht und auch der Austausch mit Gerhard Pundt von clevercalcul war hier sehr hilfreich, obgleich ich letzte Woche tatsächlich etwas durcheinander war und sein Beispiel statt ZÄHLENWENN mit ZÄHLEWENN versucht hatte nachzuvollziehen und über die Fehlermeldung erstaunt war... was manchmal so ein Buchstabe eben ausmacht ;-).
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Excel: Verschiedene Möglichkeiten einen Mittelwert über Zahlen ohne Nullwerte zu ermitteln« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 6.2.2016, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=675 (Abgerufen am 24.1.2025)
2 Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Bluesky 🌎 LinkedIn