Andreas Unkelbach
Werbung


Samstag, 6. Februar 2016
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:

Zehn Einzelwerte, davon teilweise welche mit 0

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:

ZÄHLENWENN(B2:B11;"<>0")

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.

Ermittlung der Anzahl an Werten, die nicht 0 sind

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:

MITTELWERTWENN(B2:B11;"<>0";B2:B11)

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.

Hierzu wird die Formel

MITTELWERT(WENN(B2:B11<>0;B2:B11))

eingegeben und muss mit STRG, UMSCHALTTASTE (Shift) und EINGABETASTE (Enter) in eine Matrixformel umgewandelt werden.

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:
Mittelwert ohne Null durch Mittelwertwenn oder Matrixformel

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 Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Excel Berichtswesen

2 Kommentare - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter 🌎 Google
Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Gerhard Pundt Homepage am 8.2.2016 um 16:39 Uhr
Hallo Andreas,
einen sehr gelungenen Artikel hast du da geschrieben. Ich kenne es selbst, wenn man erst mal beim Probieren ist, kann man manchmal nicht aufhoeren, immer neue Gedanken kommen.
Aber das ist es gerade, was mir mit Excel so gefaellt.
Vielen Dank fuer die Links und auf bald mal wieder.
Gerhard


Andreas Unkelbach Homepage am 8.2.2016 um 20:07 Uhr
Hallo Gerhard,

hin und wieder kommt man dafür aber auch auf Gedanken oder Formeln die wesentlich umständlicher sind, als die eigentlich einfache und gut lesbare Lösung, wie in diesen Falle.

Dennoch stimme ich dir zu, dass Excel ein unendlicher Raum an Möglichkeiten ist und hin und wieder bekommt man eine Excelformeln auch dahin, wo kein Mensch zuvor gewesen ist ;-).

Ich freue mich daher auch schon darauf die ein oder andere künftige Anregung auch auf deiner Seite zu lesen und werde mit der Zeit auch einmal hier einige Entwürfe fertig schreiben. In letzter Zeit kommt es doch relativ selten zu neuen Artikeln in der Rubrik Office.. :-)

Viele Grüße
Andreas


Auch kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert (siehe auch XII. Fremde Nutzung / Kommentarfunktion in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)

Name (sofern kein Name angegeben wird erscheint Anonym):


Normaler Kommentar

Name:

E-Mail (wird nicht veröffentlicht):

Homepage:

Kommentar:


Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste