Mittelwert in Abhängigkeit vom Datum

Forumthread: Mittelwert in Abhängigkeit vom Datum

Jürgen

Hallo liebe Excelperten und Excelpertinnen,
habe da gerade wieder was kniffliges was ich gerade nicht zustande kriege. Vielleicht hat ja einer von Euch hierfür eine Lösung.
Zur Problemstellung:
Ich führe eine Excel-Liste in der ich unter anderem die mittlere Herzfrequenz, oder auch die mittlere Geschwindigkeit bei meinen Radtouren führe. In Spalte A steht da jeweils immer das Datum der Tour und in Spalte H die mittlere Herzfrequenz.
In einer Übersichtstabelle die da "Übersicht" heißt, möchte ich nun die Mittelwerte der Mittelwerte eines bestimmten Jahres anzeigen. Da gibt es also eine Zeile, in der steht das Jahr, also z.B. 2009 und in einer der Spalten daneben steht die mittlere Herzfrequenz. Hier möchte ich den Mittelwert aller in der anderen Tabelle ermittelten mittleren Herzfrequenzen anzeigen. Da ich Radtourendaten fortlaufend für alle Jahre eintrage, sollen halt nur die Zellen für die Mittelwertbildung herangezogen werden, die auch zum richtigen Jahr gehören. Ich hoffe ich konnte mich deutlich genug ausdrücken.
Zur Veranschaulichung hab ich mal noch ein Beispiel hier angehängt, wie das dann aussehen soll.

Die Datei https://www.herber.de/bbs/user/69799.xlsx wurde aus Datenschutzgründen gelöscht

Ich danke schon jetzt für eure Hilfe.
schönen Sonntag noch
Jürgen

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Mittelwert in Abhängigkeit vom Datum

30.05.2010 11:57:53

Josef

Hallo Jürgen,

	B	C	D	E
3	Jahr	Gesamt-Km	Max.Puls	mittlerer Puls
4	2009	1776,30	173	166
5	2010	2684,10	185	179
6	2011	0,00	0	0
7	2012	0,00	0	0
8	2013	0,00	0	0

Formeln der Tabelle

Zelle	Formel
C4	=SUMMENPRODUKT((JAHR(Datenerfassung!$A$3:$A$100)=B4)*Datenerfassung!$B$3:$B$100)
D4	{=MAX(WENN(JAHR(Datenerfassung!$A$3:$A$100)=B4;Datenerfassung!$C$3:$C$100))}
E4	{=WENN(SUMME((JAHR(Datenerfassung!$A$3:$A$100)=B4)*1)>0;MITTELWERT(WENN(JAHR(Datenerfassung!$A$3:$A$100)=B4;Datenerfassung!$C$3:$C$100)); 0)}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Sepp

Korrektur!

30.05.2010 11:59:54

Josef

Hallo Jürgen,
du wolltest ja den Mittelwert vom mittleren Puls.

	B	C	D	E
3	Jahr	Gesamt-Km	Max.Puls	mittlerer Puls
4	2009	1776,30	173	155
5	2010	2684,10	185	156
6	2011	0,00	0	0
7	2012	0,00	0	0
8	2013	0,00	0	0

Formeln der Tabelle

Zelle	Formel
C4	=SUMMENPRODUKT((JAHR(Datenerfassung!$A$3:$A$100)=B4)*Datenerfassung!$B$3:$B$100)
D4	{=MAX(WENN(JAHR(Datenerfassung!$A$3:$A$100)=B4;Datenerfassung!$C$3:$C$100))}
E4	{=WENN(SUMME((JAHR(Datenerfassung!$A$3:$A$100)=B4)*1)>0;MITTELWERT(WENN(JAHR(Datenerfassung!$A$3:$A$100)=B4;Datenerfassung!$D$3:$D$100)); 0)}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Sepp

AW: Korrektur!

30.05.2010 12:56:29

Jürgen

Hallo Sepp,
vielen, vielen Dank für Deine Hilfe. Kann es sein, dass der Tabellenname in den Formeln in Anführungsstrichen (Hochkomma) stehen muss......bei mir geht da immer der Explorer auf wenn ich die Formel bestätigen will.
Jetzt habe ich aber noch eine Zusatzfrage. Wie sähe die Formel aus, wenn man das ganze in der Übersicht nicht nach dem Jahr, sondern nach dem Monat im Jahr berechnen lassen würde ? Also wenn man vom Januar, Februar 2009 etc. die ganzen Werte ermitteln will ?
Nochmals vielen Dank
Gruß
Jürgen

AW: Korrektur!

30.05.2010 13:09:00

Josef

Hallo Jürgen,
die ' sind nur erfordrlich, wenn im Blattnamen Leerzeichen vorkommen, was in deiner Datei nicht der Fall ist.
Habe dir mal eine Auswertung mit Start-/Enddatum eingefügt.
https://www.herber.de/bbs/user/69804.xlsx

Gruß Sepp

AW: Korrektur!

30.05.2010 16:28:34

Jürgen

Hallo Sepp,
Du hast recht.....in meiner Originaldatei ist der Tabellenname "Datenerfassung Rennrad", also mit Leerzeichen......und schwupp.......wieder was gelernt. Vielen Dank.
Vielen Dank auch für Deine ausgefüllte Datei. Versuche sie gleich mal zu analysieren und zu verstehen. Du hast dort ja ein Anfang und Enddatum verwendet. Mal schauen ob ich´s hinbekomme das auf den Monat umzusetzen, weil ich bräuchte wie gesagt die Daten jeweils für die kompletten Monate. Wenn´s nicht anders geht, könnte ich zwar auch miteiner Hilfsspalte arbeiten und dort jeweils das Monatsende eintragen, aber vielleicht geht´s ja auch einfacher. Wenn Du da eine schnellere Lösung hast wär ich Dir dankbar.
Wenn es Dir möglich ist, fänd ich´s auch super wenn Du mir für die Formel etwas erklären könntest, wie sie arbeitet, weil irgendwie komm ich nicht dahinter.
Gruß
Jürgen

AW: Korrektur!

30.05.2010 17:08:57

Josef

Hallo Jürgen,
meinst du so?
https://www.herber.de/bbs/user/69806.xlsx

Gruß Sepp

AW: Korrektur!

31.05.2010 23:59:00

Jürgen

Hallo Sepp,
that´s it........super....genau so hatte ich es mir vorgestellt. Klasse, das bringt mich echt weiter. Vielen, vielen Dank....und Dir noch ne schöne Restwoche.
viele Grüße
Jürgen

Infobox / Tutorial

Mittelwert in Abhängigkeit vom Datum berechnen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Datenstruktur anlegen: Stelle sicher, dass Deine Datentabelle wie folgt strukturiert ist:
- Spalte A: Datum der Tour
- Spalte H: Mittlere Herzfrequenz
Übersichtstabelle erstellen: Erstelle eine neue Tabelle, die die Jahre (z.B. 2009, 2010) in einer Spalte auflistet, und reserviere die benachbarte Spalte für die Berechnung des Mittelwerts.
Formel für den Mittelwert: Verwende die folgende Formel in der Übersichtstabelle, um den Mittelwert der Herzfrequenzen für ein bestimmtes Jahr zu berechnen:
```
=WENN(SUMME(WENN(JAHR(Datenerfassung!$A$3:$A$100)=B4;1))>0;MITTELWERT(WENN(JAHR(Datenerfassung!$A$3:$A$100)=B4;Datenerfassung!$H$3:$H$100)); 0)
```
Stelle sicher, dass Du die Formel mit STRG + SHIFT + ENTER abschließt, da es sich um eine Matrixformel handelt.

Anpassen der Formel für Monate: Wenn Du die Mittelwerte nach Monaten berechnen möchtest, kannst Du die Formel anpassen:

=WENN(SUMME(WENN(JAHR(Datenerfassung!$A$3:$A$100)=B4;MONAT(Datenerfassung!$A$3:$A$100)=C4))>0;MITTELWERT(WENN(JAHR(Datenerfassung!$A$3:$A$100)=B4;MONAT(Datenerfassung!$A$3:$A$100)=C4;Datenerfassung!$H$3:$H$100)); 0)

Hierbei steht C4 für die Monatsspalte.

Häufige Fehler und Lösungen

Fehler bei der Formelbestätigung: Wenn der Explorer geöffnet wird, überprüfe, ob der Tabellenname in Anführungszeichen gesetzt ist, falls Leerzeichen vorhanden sind.
Keine Ergebnisse: Stelle sicher, dass die Daten in der Datentabelle korrekt eingetragen sind und die Datumsformate stimmen.
Matrixformel nicht richtig eingegeben: Denke daran, die Formel mit STRG + SHIFT + ENTER zu bestätigen, um die Matrixformel korrekt zu aktivieren.

Alternative Methoden

Pivot-Tabellen: Eine Pivot-Tabelle kann ebenfalls nützlich sein, um die Mittelwerte nach Jahr oder Monat zu aggregieren. Du kannst das Datum als Zeilenfeld und die Herzfrequenz als Wertefeld verwenden, um die Mittelwerte einfach zu berechnen.
Hilfsspalten: Eine zusätzliche Spalte kann verwendet werden, um das Monatsende automatisch zu berechnen. So kannst Du die Daten effizienter filtern.

Praktische Beispiele

Jahresmittelwert: Angenommen, Du möchtest den Mittelwert für das Jahr 2009 berechnen. In der Übersichtstabelle gibst Du in die Jahr-Spalte „2009“ ein und in die nebenstehende Spalte die oben genannte Formel ein.
Monatsmittelwert: Für eine monatliche Berechnung gibst Du in die Jahr-Spalte „2009“ und in die Monatsspalte „1“ (für Januar) ein und verwendest die angepasste Formel.

Tipps für Profis

Verwendung von Namen: Verwende benannte Bereiche, um die Formeln klarer und leichter zu verwalten. So kannst Du anstelle von Zellreferenzen klare Namen verwenden.
Datenvalidierung: Setze Datenvalidierung ein, um sicherzustellen, dass nur gültige Datumsangaben in die Datentabelle eingegeben werden.
Bed Conditional Formatting: Nutze bedingte Formatierung, um die Zellen hervorzuheben, die die Mittelwerte darstellen, um die Übersichtlichkeit zu erhöhen.

FAQ: Häufige Fragen

1. Muss ich den Tabellenname in Anführungszeichen setzen?
Ja, nur wenn der Tabellenname Leerzeichen enthält. Wenn nicht, kannst Du ihn ohne Anführungszeichen verwenden.

2. Wie kann ich die Mittelwerte nach Monaten berechnen?
Du kannst die Formel entsprechend anpassen, indem Du die MONAT-Funktion in der WENN-Bedingung verwendest, um die Berechnungen nach Monaten zu filtern.

3. Was ist eine Matrixformel?
Eine Matrixformel ermöglicht es Dir, mehrere Berechnungen in einer einzigen Formel durchzuführen. Sie wird durch die Kombination von STRG + SHIFT + ENTER aktiviert.

Beliebteste Forumthreads (12 Monate)

Mittelwert in Abhängigkeit vom Datum

Forumthread: Mittelwert in Abhängigkeit vom Datum

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Mittelwert in Abhängigkeit vom Datum berechnen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread