Mittelwertwenn mit mehreren Optionen

[robert_lehner]

Hallo liebe Forummitglieder!

Ich bedanke mich im Voraus für eure sicherlich schnelle und kompetente Hilfe!

Mein Problem hört sich recht simpel an, jedoch habe ich nach langer Recherche noch immer keine Antwort gefunden:

Ausgangssituation: In meinem Tabellenblatt 1 habe ich eine Zelle. In diese Zelle möchte ich, dass der Mittelwert aus einem anderen Tabellenblatt kommt.
Die Tabelle nennt Excel Tabelle2. Sie handelt von den Noten. Ich möchte diese gerne in Teile aufsplitten (u.a. wissenschaftlicher Teil --> zb Mathe, Chemie, Physik,...)

Meine aktuelle Formel lautet:  
=MITTELWERTWENN(Tabelle2[Fach];"M";Tabelle2[Note JZ])

Ich möchte sie aber jetzt verändern, dass die Bedingung nicht nur M ist, sondern auch CH und PH. Außerdem soll sie mir den Mittelwert dann nicht nur aus der Spalte Note JZ (für Jahreszeignis), sondern auch aus der Spalte SN (für Schulnachricht) berechnen.

Gibt es da eine (möglichst einfache) Möglichkeit??

Noch mal vielen Dank und freundliche Grüße

[shift-del]

Hallo Robert

Es gibt MITTELWERTWENNS().
Ansonsten bitte eine Beispieldatei hochladen.

[robert_lehner]

Hallo Robert

Es gibt MITTELWERTWENNS().
Ansonsten bitte eine Beispieldatei hochladen.

Ich habe nun die Mittelwertwenns Funktion ausgiebig studiert und bin zu dem Ergebnis gekommen, dass sie für mich nicht anwendbar ist, da bei der Funktion alle Kriterien erfüllt sein müssen, bei mir jedoch nicht.

Ich habe nun eine realtiv einfache Beispieldatei hochgeladen. Es geht um Früchte, die in diesem Fall zwei Werte haben (in PHP würde das ziemlich einfach gehen  ;) ) und es soll der Mittelwert berechnet werden, wenn in der ersten Spalte "Erdbeere" oder "Heidelbeere" steht.

Ich hoffe es kann jemand mit der Datei etwas anfangen und vielen Dank für Eure Hilfe!!

Freundliche Grüße, Robert

[GMG-CC]

Moin Robert,
meinst du es so?

Hier noch der zugehörige Code:

PHP-Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Frucht", type text}, {"Wert1", Int64.Type}, {"Wert2", Int64.Type}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each ([Frucht] = "Erdbeere" or [Frucht] = "Heidelbeere")),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Gefilterte Zeilen", {"Frucht"}, "Attribut", "Wert"),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Entpivotierte andere Spalten",{"Attribut"}),
    #"Berechneter Durchschnitt" = List.Average(#"Entfernte Spalten"[Wert]),
    #"In Tabelle konvertiert" = #table(1, {{#"Berechneter Durchschnitt"}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"In Tabelle konvertiert",{{"Column1", "Mittelwert Beeren"}})
in
    #"Umbenannte Spalten" 


[lupo1]

=SUMME(MITTELWERTWENN(A2:A6;{"Erdbeere";"Heidelbeere"};B2:C6))/SUMME(ZÄHLENWENN(A2:A6;{"Erdbeere";"Heidelbeere"}))

Oder meinst Du was anderes?

[robert_lehner]

Moin Robert,
meinst du es so?

Hier noch der zugehörige Code:

PHP-Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Frucht", type text}, {"Wert1", Int64.Type}, {"Wert2", Int64.Type}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each ([Frucht] = "Erdbeere" or [Frucht] = "Heidelbeere")),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Gefilterte Zeilen", {"Frucht"}, "Attribut", "Wert"),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Entpivotierte andere Spalten",{"Attribut"}),
    #"Berechneter Durchschnitt" = List.Average(#"Entfernte Spalten"[Wert]),
    #"In Tabelle konvertiert" = #table(1, {{#"Berechneter Durchschnitt"}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"In Tabelle konvertiert",{{"Column1", "Mittelwert Beeren"}})
in
    #"Umbenannte Spalten" 


Wow vielen Dank, ich habe mich ehrlich gesagt noch nicht mit Pivot Tabellen beschäftigt (Das ist doch eine oder?)
Ich werde mir das anlernen versuchen und dann dein Beispiel vielleicht sogar verstehen. Das sieht echt komplex aus, vielen vielen Dank für deine Mühen!!

Freundliche Grüße Robert

[robert_lehner]

=SUMME(MITTELWERTWENN(A2:A6;{"Erdbeere";"Heidelbeere"};B2:C6))/SUMME(ZÄHLENWENN(A2:A6;{"Erdbeere";"Heidelbeere"}))

Oder meinst Du was anderes?

Da kommt leider das falsche Ergebnis heraus. (5,5 und es sollte 6 herauskommen)

Aber vielen Dank für deine Hilfe, ich werde mich nach dem Vorschlag von GMG-CC mit Pivot Tabellen befassen.

Freundliche Grüße Robert

PS: Das ist eine echt tolle Community hier! Hat mich echt fasziniert, ich hoffe, dass auch ich mal jemandem helfen kann.

[lupo1]

Stimmt, nicht zuende gerechnet:

=SUMMENPRODUKT(MITTELWERTWENN(
A2:A6;{"Erdbeere";"Heidelbeere"};BEREICH.VERSCHIEBEN(B2:B6;;ZEILE($1:$2)-1))/SUMME(ZÄHLENWENN(
A2:A6;{"Erdbeere";"Heidelbeere"})))

[robert_lehner]

Stimmt, nicht zuende gerechnet:

=SUMMENPRODUKT(MITTELWERTWENN(
A2:A6;{"Erdbeere";"Heidelbeere"};BEREICH.VERSCHIEBEN(B2:B6;;ZEILE($1:$2)-1))/SUMME(ZÄHLENWENN(
A2:A6;{"Erdbeere";"Heidelbeere"})))

Vielen Dank, jetzt habe ich sogar zwei Lösungen  

Freundliche Grüße Robert

[GMG-CC]

Moin Robert,

nein, das ist keine PivotTable sondern mit Power Query -> http://www.excel-ist-sexy.de/power-query-das-add-in/ erstellt. Ein ausgesprochen mächtiges Tool, was noch erheblich mehr kann ...