Mittelwert ohne Filter anzeigen lassen

[DietmarD]

Hallo zusammen,

ich habe eine Tabelle mit ca. 516 Datensätzen!
Im Moment ermittel ich meinen Mittelwert aus Spalte N so, das ich zuerst in Spalte G nach einer bestimmten Farbe meines Produkts filtere, dann in Spalte F nach der Dicke meines Produkts filtere und mit der Funktion Teilergebnis (1) den Mittelwert aus N, in N517 erhalte.

Nun möchte ich das aber ohne Filter erreichen, das ich in einer Zelle z.B. 518 meine Dicke  aus F steht, in Q518 meine Farbe und dann in R518 der Mittelwert aus N zu diesen beiden Kriterien, ausgegeben wird.
 
Ich bin für jeden Hinweis Dankbar

[neopa]

Hallo Dietmar,

in Deiner Excelversion 2010 so: =MITTELWERTWENNS(N2:N999;F2:F999;P518;G2:G999;Q518)
und Formel nach unten kopieren.

Anstelle den Datenbereich bis zur Datenzeile 999 auswerten zu lassen, wie von mir in der Formel angegeben, ist es empfehlenswert Deine Datentabelle zu einer "intelligenten zu formatieren. Dann arbeitet nachfolgende Formel immer mit der wirklich vorhandenen Datensatzzahl, auch wenn es mehr als 999 Datensätze werden sollten.

Die Formel würde dann z.B. so lauten:
=MITTELWERTWENNS(Tabelle1[Fehleranteil];Tabelle1[Dicke];P518;Tabelle1[Farbe];Q518)

[LCohen]

... und übergreifend für Deine 3 Excelversionen so:

R518: {=MITTELWERT(WENN(($F1:$F999=P518)*($G1:$G999=Q518);N$1:N$999))} mit Nr. 5

Ich halte die einfache Mittlung von Anteilen (!) aber für bedenklich oder falsch, da jede Art von Gewichtung, die dem innewohnen könnte, fehlt.

Das ist so, wie wenn ich die kombinierte HIV-Rate von Jamaika 1,5% und Kuba 0,2% mittle/kombiniere: 0,85% käme bei Dir raus; stimmen tut aber 0,65%, da Kuba 10,7 und Jamaika 2,9 Einwohner in Mio hat.

[DietmarD]

Hallo Werner,

deine zweite Lösung ist das, was ich mir im zweiten Schritt vorgestellt hatte, denn es kommt vor, das am Anfang eines Monats weniger Datensätze vorhanden sind und das ich evtl. auch über die 999 kommen kann, nur leider bekomme ich bei deiner Formel immer eine Fehlermeldung, "Formel enthält einen Fehler" mit der Bestätigung auf OK wird Tabelle1[Fehleranteil] schwarz hinterlegt.

Hallo LCohen,


auch dir ein Danke für deinen Hinweis, dass das Ergebnis nicht wirklich stimmt, aber für mich reicht das Ergebnis dennoch aus. Deine Formel zeigt mir bei Format % das gewünschte Ergebnis, was ich auch bei Teilergebnis erziele.

Nun hätte ich das gerne noch so, das ich mehrere Spalten mit der Überschrift des Monats Jan-Dez anlege und mir das Ganze noch so angezeigt wird, als wenn ich nach Monat in Spalte A filtere.

Vielen Dank im Voraus!

[LCohen]

Die Variante 2 von neopa erzeugt man fehlerfrei, indem man die Variante 1 passgenau (=bis zur letzten Datenzeile bezogen) eingibt und dann Strg-L auf der Tabelle ausführt. Ab dann ist die "Automatik" wirksam. Ab Excel 2010.

[DietmarD]

Hallo zusammen,


das mit dem Formatieren der Tabelle in eine intelligente war mir so gar nicht bekannt, deshalb habe ich das wohl überlesen. Mit eurer Hilfe habe ich das nun hinbekommen und auch die Formel von Werner funktioniert wie gewünscht.

Jetzt noch mal zu meiner zweiten Frage!

Ich würde mir gerne die Mittelwerte Monatsweise anzeigen lassen. Das heißt extra Spalten Jan- Dez und darin die Mittelwerte, wie zuvor ermittelt.

Geht das überhaupt? Wenn Ja wie?

[LCohen]

Hab ich nicht geladen. Bei Mittelwertwenn kommen dafür 2 Kriterien dazu, bei meiner Lösung {=Mittelwert()} nur eines, da ich statt eines Intervalles =TEXT(Liste;"JJMM")=TEXT(Vergleichsdatum;"JJMM") einbauen kann.

Das Verwenden einer Pivot-Tabelle wird ab jetzt jedoch sinnvoller!

[DietmarD]

Hallo zusammen,

mit Pivottabellen habe ich noch nie gearbeitet, werde ich mich nun mal mit beschäftigen.
Wenn ich dann noch Fragen zu meinem Vorhaben habe, melde ich mich wieder und mache einen neuen Thread dazu auf!
An dieser Stelle setze ich diesen Thread dann erst einmal auf erledigt.

Ein Dank geht an alle Helfer hier. :18: