Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Friedrichroda /
Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.

Durchschnitt mit Zusammenfassung von Werten
#1
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.


Angehängte Dateien
.xlsx   Beispiel.xlsx (Größe: 9,35 KB / Downloads: 3)
to top
#2
Hallo,

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

zum zählen unterschiedlicher Einträge.
Gruß


Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Benutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • Michal
to top
#3
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.
[-] Folgende(r) 1 Benutzer sagt Danke an Michal für diesen Beitrag:
  • schauan
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste