Excel: Wie schließe ich leere Zellen aus Mittelwertberechnung aus?
Hi! Ich habe vor einiger Zeit mithilfe der Gutefrage.net User eine Tabelle erstellt, in der ich getrunkene Weine und verschiedene Faktoren (wie z.B. Rebsorten) eingetragen habe. Diese Weine habe ich bewertet, sodass ich jetzt für jede Rebsorte (Anbaugebiet, Jahrgang...) eine gemittelte Bewertung habe.
Nun habe ich die Tabelle erweitert und trage zu jedem Wein noch Variablen wie Körper, Beerenfrucht, Abgang usw. auf einer Skala von 1-5 ein.
Soll jetzt zum Beispiel der gemittelte Wert für "Körper" einer Rebsorte (hier in der Formel "Wein") berechnet werden, habe ich folgende Formel:
=SUMMENPRODUKT(('Bewertungsmappe Rot'!$L$2:$Q$550=[@Wein])*('Bewertungsmappe Rot'!U$2:U$550))/ZÄHLENWENN('Bewertungsmappe Rot'!$L$2:$Q$550;[@Wein])
In der "Bewertungsmappe" trage ich neue Weine ein, in den Spalten I und L stehen die Rebsorten, in der Spalte U trage ich den "Körper" ein *
Die gewährleistet, dass nur diejenigen Weine in die Berechnung einfließen, die auch aus der entsprechenden Rebsorte hergestellt wurden. Jetzt möchte ich gerne meine alte Datenbank übertragen, für die ich leider keine Werte in den Geschmacksvariablen (wie eben "Körper" usw.) habe. Außerdem hätte ich gerne die Möglichkeit, da auch was frei zu lassen. Nun führen leere Zellen aber dazu, dass der Mittelwert natürlich zu gering ist. Könnte mir einer helfen, dieses Problem zu umgehen? Stehe total auf dem Schlauch!
Für Hilfe wäre ich sehr dankbar! Viele Grüße, Lukas
3 Antworten
Hi nochmal! Da ich Bilder leider nur in der Antwort hochladen kann, versuche ichs auf diesem Wege, ich hoffe man möge mir verzeihen.
Also ich habs mit MITTELWERTWENNS probiert, da ging gar nichts. Bei MITTELWERTWENN verschieben sich die Werte irgendwie, wenn die Rebsorten in den Spalten nach rechts rücken. (Wie man sieht habe ich insgesamt 6 Spalten für Rebsorten...).
Vielen Dank für die bisherigen Bemühungen, vielleicht finden wir ja eine Lösung :-)
Vielen Dank!
Habs jetzt mal mit
=MITTELWERT(MITTELWERTWENN('Bewertungsmappe Rot'!$L:$L;$A2;'Bewertungsmappe Rot'!T:T)+MITTELWERTWENN('Bewertungsmappe Rot'!$M:$M;$A2;'Bewertungsmappe Rot'!T:T)+MITTELWERTWENN('Bewertungsmappe Rot'!$N:$N;$A2;'Bewertungsmappe Rot'!T:T)+MITTELWERTWENN('Bewertungsmappe Rot'!$O:$O;$A2;'Bewertungsmappe Rot'!T:T)+MITTELWERTWENN('Bewertungsmappe Rot'!$P:$P;$A2;'Bewertungsmappe Rot'!T:T)+MITTELWERTWENN('Bewertungsmappe Rot'!$Q:$Q;$A2;'Bewertungsmappe Rot'!T:T))
versucht, leider beschwert er sich (#DIV/0!).
Hmm, probier mal die Formelauswertung (bei Formelüberwachung). Vielleicht änderst du die Formel zu einem abgeschlossenen Bereich. Also statt $L:$L verwendest du $L10:$L20 usw.
Ich hab nochmal drüber nachgedacht... das äußere MITTELWERT ist eigentlich falsch, weil es die unterschiedliche Gewichtung nicht berücksichtigt.
Also normalerweiese werden bei der Funktion MITTELWERT leere Zellen ignoriert. Ab Excel2007 kannst du auch die Funktion MITTELWERTWENN bzw. MITTELWERTWENNS verwenden.
Hi vielen Dank für deine Antwort. Wie du siehst, habe ich für meine Tabelle nicht die Funktion MITTELWERT gebraucht, da ja je Rebsorte nur der Mittelwert derjenigen Weine berechnet werden soll, die diese bestimmte Rebsorte enthalten. Kann ich das auch mit
MITTELWERTWENN bzw. MITTELWERTWENNS hinbekommen?
Ich habe zwar deine Tabelle nicht vor mir, aber ich denke, das müsste gehen. Kleines einfaches Beispiel:
=MITTELWERTWENN(A:A;"Wein";B:B)
gibt den Mittelwert aller Zahlen von Spalte B aus, bei denen in Spalte A "Wein" steht.
Falls du mehr als ein Kriterium hast, verwendest du MITTELWERTWENNS.
Wie wir erst neulich wieder festgestellt haben, sind die irgendwasWENN-Funktionen viel schneller und perfomanter als das olle Summenprodukt, gerade bei großen Tabellen und Bereichen (auch wenn dies noch etwas vielseitiger einsetzbar ist).
Hallo! Wie deedee07 schon schreibt: Du denkst zu kompliziert. Die normale Formel MITTELWERT(Bereich) berechnet nur Zellen, in denen eine Zahl steht. Rechne mal ein Beispiel nach!
So ganz richtig ist das noch nicht. Zum einen verwendest du in deiner Formel als Kriterium eine ganze Spalte ($A:$A). Dabei wird allerdings ein Ausdruck erwartet, den man vergleichen kann.
Außerdem sind der Kriterienbereich (L:Q) und der Wertebereich (T:T) unterschiedlich groß. Excel toleriert das erstaunlicherweise, sinnvoll ist das aber nicht. Excel schneidet den überschüssigen Bereich dann einfach ab, siehe auch Hinweise bei https://support.office.com/de-de/article/MITTELWERTWENN-Funktion-faec8e2e-0dec-4308-af69-f5576d8ac642
Mich wundert es, dass die Summenproduktformel so bei dir korrekt funktioniert hat. Im allgemeinen ist es etwas schwierig, wenn gleichartige Daten z.T. mehrfach nebeneinander stehen, wie bei dir die Rebsorten. Du könntest versuchen, sie spaltenweise zu addieren z.B. für Zeile 40 (Tempranillo)
=MITTELWERT(
MITTELWERTWENN('Bewertungsmappe Rot'!$L:$L;$A40;'Bewertungsmappe Rot'!T:T)
+MITTELWERTWENN('Bewertungsmappe Rot'!$M:$M;$A40;'Bewertungsmappe Rot'!T:T)
+ usw
+MITTELWERTWENN('Bewertungsmappe Rot'!$Q:$Q;$A40;'Bewertungsmappe Rot'!T:T)
)
Diese Formel könntest du dann nach rechts und unten kopieren, die Zellbezüge passen sich an.
Vielleicht kann jemand das mit einer Matrixformel noch eleganter verkürzen, ich muss jetzt gleich weg und kann nicht mehr testen.