Andreas Unkelbach
Logo Andreas Unkelbach Blog

Andreas Unkelbach Blog

ISSN 2701-6242

Artikel über Controlling und Berichtswesen mit SAP, insbesondere im Bereich des Hochschulcontrolling, aber auch zu anderen oft it-nahen Themen.


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.
SAP Weiterbildung
ein Angebot von Espresso Tutorials
SAP Weiterbildung - so wirksam wie eine gute Tasse Espresso

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 19.3.2024)

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; die angegebene E-Mail wird nicht veröffentlicht: beim Versenden als "Normaler Kommentar" ist die Angabe eines Namen erforderlich, gerne kann hier auch ein Pseudonyme oder anonyme Angaben gemacht werden (siehe auch Kommentare und Beiträge 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)





Ich nutze zum Schutz vor Spam-Kommentaren (reine Werbeeinträge) eine Wortliste, so dass diese Kommentare nicht veröffentlicht werden. Sollte ihr Kommentar nicht direkt veröffentlicht werden, kann dieses an einen entsprechenden Filter liegen.

Im Zweifel besteht auch immer die Möglichkeit eine Mail zu schreiben oder die sozialen Medien zu nutzen. Meine Kontaktdaten finden Sie auf »Über mich« oder unter »Kontakt«. Ansonsten antworte ich tatsächlich sehr gerne auf Kommentare und freue mich auf einen spannenden Austausch.












* Amazon Partnerlink/Affiliatelinks/Werbelinks
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Weitere Partnerschaften sind unter Onlineshop und unter Finanzierung und Transparenz aufgeführt. Hinauf






Logo Andreas-Unkelbach.de
Andreas Unkelbach Blog
ISSN 2701-6242

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

UStID-Nr: DE348450326 - Kleinunternehmer im Sinne von § 19 Abs. 1 UStG

Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter XING

Linkedin Mastodon Bluesky

Amazon Autorenwelt Librarything

Buchempfehlung
Schnelleinstieg ins SAP®-Controlling (CO) – 2., erweiterte Auflage

29,95 € Amazon* Autorenwelt

Espresso Tutorials

unkelbach.link/et.reportpainter/

unkelbach.link/et.migrationscockpit/

Privates

Kaffeekasse 📖 Wunschliste