Durchschnitt mit Zusammenfassung von Werten

[Michal]

Sehr geehrte Excelexperten,

ich konnte per Suche leider keine passende Antwort finden, bitte um Entschuldigung, falls es diese schon gibt. Es ist mir auf der Arbeit nicht gestattet, etwas zu installieren, daher kann ich keinen Excel-HTML-Code bieten, wie im Neulingsthread empfohlen. Diesem Post ist eine Tabelle angehängt.

Ich möchte aktuellen Daten einen Durchschnitt aus Vergangenheitsdaten gegenüberstellen.

Ich habe ein Blatt namens aktuelle_Daten mit folgender Struktur:

Monat │ Code │ Kriterium1 │ Kriterium2 │ Wert │ ø
April │ A │ E │ (leer) │ 100 │ Hier liegt mein Problem
April │ B │ E │ (leer) │ 100 │ Hier liegt mein Problem

Dann habe ich ein Blatt namens Referenzdaten mit folgender Struktur:

Monat │ Code │ Kriterium1 │ Kriterium2 │ Wert
Januar │ A │ E │ N │ 80
Januar │ A │ E │ Y │ 90
Januar │ B │ E │ Y │ 80
Februar │ A │ E │ N │ 50
Februar │ A │ E │ Y │ 90
März │ A │ E │ N │ 70
März │ A │ E │ Y │ 50
März │ B │ E │ N │ 60
März │ B │ E │ Y │ 60

Der entscheidende Unterschied ist, dass in den Referenzdaten die Daten bezüglich Kriterium2 in "Y" und "N" aufgeteilt sind, während diese in den aktuellen Daten in einer Position zusammengefasst sind.

Ich hatte den Durchschnitt wie folgt gebildet (aktuelle_Daten F2):
=SUMIFS(Referenzdaten!E:E;Referenzdaten!B:B;aktuelle_Daten!B2;Referenzdaten!C:C;aktuelle_Daten!C2)/COUNTIFS(Referenzdaten!B:B;aktuelle_Daten!B2;Referenzdaten!C:C;aktuelle_Daten!C2)

Das Ergebnis lautet dann 71,7, weil die Summe aller Code-A-Einträge mit Kriterium1 = E von 430 durch die Anzahl aller Code-A-Einträge mit Kriterium1 = E von 6 geteilt wird. Er soll aber die Einträge, die sich nur bezüglich Kriterium2 unterscheiden, zusammenfassen. Dann gibt es in drei Monaten Code-A-Einträge mit Kriterium1 = E und das Vergleichsergebnis soll 430/3=143 lauten.

Ich bitte um Hilfe und danke im voraus für die Mühe.

[BoskoBiati]

Hallo,

http://excelformeln.de/formeln.html?welcher=44

zum zählen unterschiedlicher Einträge.

[Michal]

Vielen Dank Edgar,

ich habe einen Moment gebraucht, bis ich raushatte, wie ich die Codeabfrage da einbaue, aber nun läuft es. Für die Interessierten:

Blatt aktuelle_Daten, Zelle F2:
=SUMIFS(Referenzdaten!E:E;Referenzdaten!B:B;aktuelle_Daten!B2;Referenzdaten!C:C;aktuelle_Daten!C2)/SUMPRODUCT((MATCH(Referenzdaten!A$1:A$30&"x"&Referenzdaten!B$1:B$30;Referenzdaten!A$1:A$30&"x"&Referenzdaten!B$1:B$30;0)=ROW($1:$30))*((Referenzdaten!A$1:A$30<>"")*(Referenzdaten!B$1:B$30<>""))*(Referenzdaten!B$1:B$30=B2))

Der Zähler im Bruch (Summewenns auf Deutsch?) ist noch wie vorher, der Nenner (Zählenwenns?) wurde durch den Hinweis von Edgar ersetzt. Dabei sorgt die letzte Klammer dafür, dass der jeweilige Code gezählt wird.

Nochmals vielen Dank, Edgar.