3.2k Aufrufe
Gefragt in Tabellenkalkulation von

Guten Tag,

Meine Frage richtet sich in Anlehnung an folgenden Thread:

https://supportnet.de/forum/2266970/in-excel-blockweise-mittelwerte-bilden?show=2266970#q2266970

Primär wurde hier die Blockweise Mittelwertbildung in festen Abständen thematisiert.

Ähnlich wie es am Ende Maralia angesprochen hat geht es mir jetzt (dazu gab es leider keine Lösung mehr in dem o.g Thread)

Die nachfolgende Formel half mir vorerst gut:

=WENN(REST(ZEILE(A1);4)=0;MITTELWERT(BEREICH.VERSCHIEBEN(A1;;;-4;));"")

Nun habe ich habe auch Datenreihen, die nicht in regelmäßigen Blöcken angeordnet sind. 
Sprich in Spalte A sind Zeitangaben - immer mal anders, z.B. wurde in der 1. Stunde an 3 mal gemessen, in der 2. Stunde 5 mal, in der 3.Stunde 9 mal etc.  In Spalte B sind zugehörige Messwerte. Ich möchte für jede Stunde Mittelwerte bilden. Gibt es eine Möglichkeit die Mittelwertsbildung an die Stunden zu knüpfen. Also kann man Excel irgendwie dazu bringen, jeweils nur für eine Stunde MW zu bilden, wenn die Anzahl der Messwerte von Stunde zu Stunde verschieden sind?

LG

Bernd

13 Antworten

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Bernd,

hier wird der Mittelwert für 11.00 bis 11:59 Uhr ermittelt:

{=MITTELWERT(WENN(STUNDE(A1:A40)=11;B1:B40))}

Das ist eine Matrixformel, daher Eingabe mit STRG+UMSCHALT+RETURN abschließen. Die Bezüge musst du natürlich auf deine Bedürfnisse anpassen.

Gruß

M.O.
0 Punkte
Beantwortet von

Guten Morgen M.O.,

erstmal besten Dank für die Antwort aber leider verstehe ich das nicht ganz.
Hier mal vereinfacht dargestellt:

X Wert = Zeitwert ; Y Wert = Messwert

X        Y       MW

0,1     11
0,3     13
0,8     15 
1        16      Hier Mittelwert aus Stunde 1
1,1     19
1,5     22
2        25      Hier Mittelwert aus Stunde 2
2,1     30
2,7     32
2,8     34
2,9     35
3,0     36      Hier Mittelwert aus Stunde 3
 

Gibt es jetzt eine Formel, die ich in der MW Spalte runterziehen kann, sodass Excel mir immer zur vollen Stunde den Mittelwert ausgibt und die restlichen Felder leer bleiben? Wie gesagt der Abstand zum nächsten MW ist unregelmäßig und ich habe über 6000 Werte 

Besten Dank vorab :)
LG

Bernd

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Bernd,

was verstehst du unter dem Mittelwert der jeweiligen Stunde: 0,1 bis 0,9 (hierfür hätte ich eine Formellösung) oder 0,1 bis einschließlich 1?

Muss der Mittelwert in der vollen Stunde stehen, oder kann er ggf. auch in der letzten Zeitangabe der betreffenden Stunde stehen?

Gruß

M.O.
0 Punkte
Beantwortet von
Hallo M.O.,

also wie folgt: Nehmen wir an die o.g. X-Werte sind in Spalte A ,die Y-Werte in Spalte B und die MW in Spalte C.
Am liebsten hätte ich jetzt, dass der MW aus Spalte B immer bis zur vollen Stunde berechnet wird (0,1 bis 1; 1,1 bis 2; 2,1 bis 3 etc.) und dann direkt neben der vollen Stunde in der Spalte C ausgegeben wird.

Ist das nachvollziehbar?

Besten Dank für deinen Support :)

LG

Bernd
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Bernd,

im Beispiel fängt die Liste mit den Zahlen in Zeile 3 an.

Versuch mal die folgende Formel:

=WENN(GANZZAHL(A3)<>A3;"";MITTELWERTWENNS($B$3:B3;$A$3:A3;"<="&A3;$A$3:A3;">"&A3-1))

Ziehe die Formel so weit wie benötigt nach unten.

Gruß

M.O.
0 Punkte
Beantwortet von
Guten Tag M.O.,

BESTEN DANK!!!! Es funktioniert :)

Evtl. noch eine Nachfrage bzw. Anpassung (sofern es keine zu großen Umstände breitet):
Da ich so viele Datenmengen vorliegen habe, wäre eine MW für alle zwei Stunden ausreichend.
Bestehe folglich die Möglichkeit die Formel so anzupassen, dass Excel nur alle zwei Stunden (sprich MW nach 2,0, nach 4,0, nach 6,0 etc) ausrechnet und die restlichen Zellen leer bleiben?

Besten Dank!

LG

Bernd
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Bernd,

auch das geht:

=WENN(UND(REST(A3;2)=0;GANZZAHL(A3)=A3);MITTELWERTWENNS($B$3:B3;$A$3:A3;"<="&A3;$A$3:A3;">"&A3-2);"")

Gruß

M.O.
0 Punkte
Beantwortet von
Nabend M.O.,

Super vielen Dank!! Genau so wollte ich meine Tabelle haben :)

Ok im finalen Schritt möchte ich das Ganze in einem Diagramm darstellen.
X - Achse = Zeit, Y-Achse = MW.  
Wenn ich nun  in "Datenreihe bearbeiten" für die Y-Achse die Zeit komplett runterziehe und für die Y-Achse die Spalte der MW, dann übernimmt er automatisch auch die leeren Zeilen..
Gibt es hier einen Trick, sodass das Diagramm nur die "2 Stunden Werte" einbezieht?

LG

Bernd
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Bernd,

wenn du die Zeilen, in denen keine Mittelwerte vorhanden sind, ausblendest, werden diese auch nicht im Diagramm angezeigt.

Mit dem folgenden Makro kannst du die Zeilen ausblenden, bei denen in Spalte C keine Werte stehen:

Sub ausblenden()
Dim lngZeile As Long

For lngZeile = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
  If Cells(lngZeile, 3).Value = "" Then Rows(lngZeile).EntireRow.Hidden = True
Next lngZeile

End Sub

Sub alles_einblenden()

ActiveSheet.Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row, 3)).EntireRow.Hidden = False
 
End Sub

Mit dem zweiten Makro kannst du einfach wieder alle Zeilen einblenden.

Kopiere die Makros in ein allgemeines Modul.

Gruß

M.O.

0 Punkte
Beantwortet von
Guten Tag M.O.,

Haben Sie vielen Dank!

Es funktioniert alles so wie ich es mir vorgestellt habe :)

Beste Grüße

Bernd
...