Clever-Excel-Forum

Hallo zusammen,

ich suche seit ein paar Stunden eine Lösung für folgendes Problem unter Excel 2013:

Tabelle 1 enthält eine Datumsspalte und eine Spalte mit einem konkreten Wert:


Nicht für jeden Tag ist also ein Wert eingetragen.

Tabelle 2 enthält eine Zusammenfassung nach Monaten:

Die Monate in Tabelle 2 sind jeweils al 01.01.2017, 01.02.2017 usw. eingetragen und mit MMMM formatiert.

Trotz langer Suche und viel googeln ist es mir nicht gelungen, eine Formel für den Durschnitts-Wert zu finden.

Am nächsten dran war ich einmal mit einer Formel ähnlich wie dieser:
=SUMMENPRODUKT((MONAT(Tabelle1!A:A)=MONAT(A1))*Tabelle1!B2:B13)/SUMMENPRODUKT(--(MONAT(Tabelle1!A2:A13)=MONAT(A1)))

Aber es funktioniert nicht damit. Der Divisor, das zweite Summenprodukt, zählt auch die nicht ausgefüllten Felder mit, was den Mittelwert verfälscht.

Auch die MITTELWERT-Funktionen haben  mir nicht geholfen.

Gibt es vielleicht eine einfache, ganz triviale Lösung für das Problem?

Hallo

Lösungsansatz Nummer 1 ist die Pivot-Tabelle.
Datum in den Zeilenbereich und nach Jahre und Monate gruppieren.
Wert in den Wertebereich und auf Mittelwert ändern.

Danke für die Antwort.

Hab es einmal mit einer Pivot-Tabelle versucht. Damit funktioniert es tatsächlich. Überhaupt scheint diese Tabellenart ja einiges Potential zu haben Muss ich erst noch voll dahinter steigen.

Eine kurze Frage hätte ich noch: Wenn ich mit der Pivot-Tabelle statt dem Mittelwert die Summe einer Spalte nach Monaten bilde, gibt es da eine Möglichkeit, diese Summe durch einen festen Betrag zu dividieren?

Grund: Die Spalte in Tabelle 1 enthält jetzt Minuten, einfach als ganze Zahl eingetragen. Die Summe nach Monaten in der Pivot-Tabelle soll "Stunden:Minuten" anzeigen, deswegen habe ich sie mit [h]:mm formatiert. Damit die Summe in diesem Format richtig angezeigt wird, muss ich sie ": 24 : 60", also durch 1440 dividieren.

Abseits von Pivot, welches tatsächlich aber immer zu empfehlen ist, wenn es einsetzbar ist:

=SUMMENPRODUKT((MONAT(A1:A99)=1)*B1:B99)
/SUMMENPRODUKT((MONAT(A1:A99)=1)*(B1:B99<>""))

ergibt den bedingten Mittelwert für wertgefüllte Januar-Datümer egal welchen Jahres.

Soll das ganze für Januar 2017 gerechnet werden, dann:

=SUMMENPRODUKT((--TEXT(A1:A99;"JMM")=1701)*B1:B99)
/SUMMENPRODUKT((--TEXT(A1:A99;"JMM")=1701)*(B1:B99<>""))

Hallo,

wäre das nicht ein Fall für MITTELWERTWENNS?

=MITTELWERTWENNS(B:B;A:A;">=1.1.17";A:A;"<=31.1.17")

oder auch so:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D
1	01.01.2017	1,46	Januar	1,37
2	02.01.2017	1,09	Februar
3	03.01.2017	1,00
4	04.01.2017	1,13
5	05.01.2017	1,95
6	06.01.2017	1,66
7	07.01.2017	1,13
8	08.01.2017	1,31
9	09.01.2017	1,32
10	10.01.2017	1,78
11	11.01.2017	1,32
12	12.01.2017	1,17
13	13.01.2017	1,71
14	14.01.2017	1,11
15	15.01.2017	1,39
16	16.01.2017	1,11
17	17.01.2017	1,26
18	18.01.2017	1,93
19	19.01.2017	1,24

Zelle	Formel
D1	=MITTELWERTWENNS(B:B;A:A;">="&C1;A:A;"<="&MONATSENDE(C1;0))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Hallo,

Zitat:Möglichkeit, diese Summe durch einen festen Betrag zu dividieren?

ja, das ist über ein berechnetes Feld möglich.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1	Datum	Minuten
2	06.02.2017	67
3					Zeilenbeschriftungen	Summe von Minuten	Summe von Stunden
4					06.02.2017	67	1:07
5					Gesamtergebnis	67	1:07

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Über den Punkt 'Felder, Elemente und Gruppen' kannst du ein Berechnetes Feld einfügen - in diesem Fall würde die Formel einfach =Minuten/1440 lauten.

Zitat:Über den Punkt 'Felder, Elemente und Gruppen' kannst du ein Berechnetes Feld einfügen - in diesem Fall würde die Formel einfach =Minuten/1440 lauten.

Das werde ich gleich mal ausprobieren.

Überhaupt scheint es ja viele Lösungen für das Problem zu geben. Sehr inspirierende Antworten, danke

(06.02.2017, 10:57)Peter schrieb: [ -> ]Über den Punkt 'Felder, Elemente und Gruppen' kannst du ein Berechnetes Feld einfügen

Berechnete Felder/Elemente haben ihr Tücken. Deshalb würde ich zuerst versuchen ob es nicht möglich wäre die Berechnung schon in den Quelldaten vorzunehmen.