Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1576to1580
1576to1580
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Pivot Tabelle Monatsdurchschnitt berechnen

Forumthread: Pivot Tabelle Monatsdurchschnitt berechnen

Pivot Tabelle Monatsdurchschnitt berechnen
24.08.2017 10:01:07
Stefan
Hallo,
ich komme nicht so richtig weiter und bräuchte Hilfe von den Profis.
Ich habe eine Tabelle mit meinen Umsätzen des Online Bankings (15.000 Einträge der letzten 6 Jahre) in verschiedenen Kategorien.
#Datum #Betrag #Kategorie
01.01.2014 100,00 Kategor1
01.01.2015 150,00 Kategor1
01.01.2017 170,00 Kategor1
01.07.2017 150,00 Kategor1
Daraus habe ich mir eine Pivot Tabelle erstellen lassen inkl. Zeitachse:
#Kategorie #Summe Betrag
Kategor1 570,00
2017 320,00
2015 150,00
2014 100,00
Ich hätte gerne das neben dem SummenBetrag ein weiters Feld vorhanden ist, welches mir den Monatsdurchschnitt abhängig von der Zeitachse sowie von aus und einklappender Datumswerte (Jahr&Monat) entsprechend berechnet.
#Kategorie #Summe Betrag #MDurch Formel
Kategor1 570,00 17,81 (570/32)
2017 320,00 40,00 (320/8 da aktuelle August ist)
2015 150,00 12,50 (150/12)
2014 100,00 8,33 (100/12)
Vielen Dank für eure Bemühungen im vor raus.
Stefan
Anzeige

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit PIVOTauswertung ...
24.08.2017 10:05:38
...
Hallo Stefan,
... solltest Du auch gleich den Mittelwert auswerten lassen können. Die Datumswerte dazu entsprechend vorher gruppiere.
Gruß Werner
.. , - ...
AW: mit PIVOTauswertung ...
24.08.2017 10:11:27
Stefan
Hallo Werner,
danke für deine Antwort, also entweder ich bin auf dem völligen Holzweg oder habe einen Denkfehler.
Ich habe einfach einmal eine Kategorie mit den tatsächlichen Werten hergenommen.
#Lebenshaltung #Summe #Mittelwert
2017 4504,62 27,30
Mein gewünschter Durchschnitt im Monat den ich mir ausgeben lassen würde, wäre aber
4504,62 / 8 = 563,07
Daher ist der Mittelwert aus meiner Sicht nicht das richtige Ergebniss.
Weiterhin würde ich gerne über die Zeitachse mehrere Jahre hinzunehmen um dann aus den letzten 6 Jahren in den entsprechenden Kategorien einen Monatsdurchschnitt nicht den Mittelwert zu erhalten.
Gruß
Stefan
Anzeige
AW: eine Beispieldatei wäre schon hilfreicher ...
24.08.2017 10:30:32
...
Hallo Stefan,
... ein Auszug Deiner Datendatei mit Deiner bisherigen Pivotauswertung ist ausreichend aber es müssen schon ein paar mehr als nur ein Dutzend sein. Und daran solltest Du Dein Zielstellung noch mal erklären.
Vielleicht wird es dann klarer und es stellt sich heraus, dass ich bisher Deine Angaben falsch interpretiert habe.
Gruß Werner
.. , - ...
Anzeige
AW: eine Beispieldatei wäre schon hilfreicher ...
24.08.2017 10:58:30
Stefan
Hallo Werner,
anbei die Datei (bereinigt um die Werte und Angaben die nicht benötigt werden).
https://www.herber.de/bbs/user/115702.xlsx
Danke & Gruß
Stefan
AW: sorry, hab nur Excel 2010, thread offen ...
24.08.2017 11:04:30
...
Hallo Stefan,
... in meiner 2010er Version erkennt offensichtlich deshalb Deine Liste in Tabelle1 noch noch mal als eine Pivotauswertung.
Stelle den thread deshalb offen.
Gruß Werner
.. , - ...
Anzeige
AW: sorry, hab nur Excel 2010, thread offen ...
24.08.2017 11:12:28
Stefan
Hallo,
wenn ich es in .xls speichere wird die Datei über 300KB groß und ich kann Sie daher nicht mehr hochladen. Wie kann ich es denn noch deutlicher machen?
Stefan
AW: eine Beispieldatei wäre schon hilfreicher ...
24.08.2017 11:05:44
Stefan
Wenn man in der Beispiel Datei nach 2017 filtert dann erhält man nun folgende Anzeige
Kategorie Summe Mittelwert
Kommunikation 417,66 52,21
Lebenshaltung 4504,62 27,30
Lifestyle 1964,45 27,28
Strom 1274,35 182,05
Wohnen 1153,78 54,94
Meine Wunschansicht sollte dann aber wie folgt aussehen
Kategorie Summe Mittelwert Durchschnitt
Kommunikation 417,66 52,21 52,20 (=Summe / 8)
Lebenshaltung 4504,62 27,30 563,07
Lifestyle 1964,45 27,28 245,55
Strom 1274,35 182,05 159,29
Wohnen 1153,78 54,94 144,22
Ich hoffe das ich es noch einmal ein bisschen genauer erklären konnte.
Anzeige
AW: nicht nachvollziehbar ...
24.08.2017 11:47:30
...
Hallo Stefan,
... warum bzw. wie soll z.B. Dein "Durchschnitt" für die Lebenshaltung 563,07 werden. Er ergibt sich genau auch zu 27,20. Für mich ist Durchschnitt und Mittelwert das Gleiche.
Gruß Werner
.. , - ...
AW: Pivotauswertung in Excel 2010 ...
24.08.2017 12:14:15
...
Hallo Stefan,
... würde für Deine Daten für 2017 übrigens wie unten stehend aussehen (einen "Zeitachse" gibt es da nicht, so dass ich wohl deswegen Deine Pivotauswertung lediglich als Werte sehen kann).
In Excel 2010 kann ich aber über Gruppierung der Datumswerte trotzdem schnell Jahres und oder Monatswerte auswerten.
Wie ich dadurch feststellen konnte, ermittelst Du Deinen "Durchschnitt" immer mit der Anzahl der Beträge mit 8 (der für "#Kommunikation"). Für "#Lebenshaltung" sind es aber 165. Also meine ich, dass ich nicht auf den "Holzweg" war und bin.
 ZAAABAC
1Konto(Alle)  
2    
3DatumTagsSummeMittelwert
42017#Kommunikation-417,66 €-52,21 €
5 #Lebenshaltung-4.504,62 €-27,30 €
6 #Lifestyle-1.964,45 €-27,28 €
7 #Strom-1.274,35 €-182,05 €
8 #Wohnen-1.153,78 €-54,94 €
9Gesamtergebnis -9.314,86 €-34,12 €


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: Pivotauswertung in Excel 2010 ...
24.08.2017 14:27:45
Stefan
Hallo Werner,
danke für deine Mühen, ich versuche es noch einmal anders zu formulieren.
Lass uns doch einfach nur einmal die Kategorie Lebenshaltung für 2017 anschauen.
Summe in 8 Monaten 4504,62 € der Mittelwert der gesamten Beträge in 2017 ist 27,30€. Ich möchte aber wissen was ich im Durchschnitt im Monat für den Lebensunterhalt benötigte. Und ich komme mit 27,30€ im Monat nicht aus. Denn wenn ich 27,30€ x 8 Monate nehme komme ich auf 215,40€ die ich für Lebensmittel ausgeben dürfte. Davon würde ich nicht satt werden.
Also muss ich den Durchschnitt der Monate berechnen. 4504,62 € / 8 Monate = 563,07€. Das ist der Wert den ich im Durchschnitt im Monat für unseren Unterhalt bezahle.
Genau diesen möchte ich dynamisch in Abhängigkeit wo ich die Pivot gerade auf oder zuklappe berechnen lassen. Das ist die Funktion die ich gerade Suche.
Bei Kommunikation ist der Wert bei Mittelwert und Durchschnitt deshalb identisch, da ich jedes Monat die gleichen Ausgaben habe und diese auch nicht varieren.
Ich hoffe das man mich jetzt versteht und weiß was ich erreichen möchte.
Danke & Gruß
Stefan
Anzeige
AW: sorry, hatte da nicht richtig gelesen ...
24.08.2017 15:01:55
...
Hallo Stefan,
... obwohl Du es in Deinem Eröffnungsbeitrag eindeutig geschrieben hast. Diesen Beitrag hatte ich dann in meinen Beiträgen (wegen der Mittelwertberechnung) nicht mehr vor Augen. Sorry. Wer lesen kann ist eben klar im Vorteil.
In diesem Fall hätte ich in Excel 2010 eine Kombination Pivotauswertung kombiniert mit einer ergänzenden Standardformel vorgenommen. Für die Pivotauswertung in Excel 2010 hab ich nun Jahr und Monat gruppiert.
In meiner nachfolgender Auswertung muss die Formel in AD4 einfach weit genug nach unten kopiert werden.
Ob und inwieweit eine direkte Einarbeitung in neuerer Excelversion möglich ist, entzieht sich natürlich meiner Kenntnis.
 ZAAABACAD
1Konto(Alle)   
2     
3JahreDatumTagsSummeMonatschnitt
42017Jan#Lebenshaltung-601,08 € 
5 Feb#Lebenshaltung-490,93 € 
6 Mrz#Lebenshaltung-734,86 € 
7 Apr#Lebenshaltung-650,78 € 
8 Mai#Lebenshaltung-682,87 € 
9 Jun#Lebenshaltung-526,68 € 
10 Jul#Lebenshaltung-391,64 € 
11 Aug#Lebenshaltung-425,78 € 
12Gesamtergebnis  -4.504,62 €-563,08 €
13     

Formeln der Tabelle
ZelleFormel
AD4=WENN(LINKS(Z4;3)="Ges";AC4/ANZAHL(AC$4:AC4); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: sorry, hatte da nicht richtig gelesen ...
24.08.2017 15:59:46
Stefan
Hallo Werner,
ist doch kein Problem, ich hatte nur zwischenzeitlich an mir gezweifelt ob ich nur mit meinen Infos im Kopf es nicht richtig erklärt habe. Aber ist ja alles bestens. ;-)
Jetzt bräuchte ich nur noch einmal Hilfe mit deinem Absatz:
In diesem Fall hätte ich in Excel 2010 eine Kombination Pivotauswertung kombiniert mit einer ergänzenden Standardformel vorgenommen. Für die Pivotauswertung in Excel 2010 hab ich nun Jahr und Monat gruppiert.
Damit kann ich jetzt nichts anfangen. Ist das in der Pivot oder in der Ursprungstabelle? Ich bin ein bisschen ratlos.
Gruß
STEFAN
Anzeige
AW: dazu nun ...
24.08.2017 16:40:41
...
Hallo Stefan,
... ich weiß nicht, welche Möglichkeiten Dir in Deiner Excelversion 2013 die dortige PIVOTauswertung anbietet.
Deshalb hab ich Dir mal meinen Lösungsvorschlag in Excel 2010 mit Deinen Daten als Datei https://www.herber.de/bbs/user/115721.xlsx hier beigefügt. In dieser erkennst Du meine spez. Aufstellung zur Pivotauswertung und die dazu in AD aufgestellte Auswertungsformel.
Mit dieser jetzt noch entsprechend erweiterten Formel, kannst auch eine beliebige Monatszusammenstellung (im Beispiel alle Monate mit 31 Tagen in bestimmten ausgewählten Jahren) auswerten.
Gruß Werner
.. , - ...
Anzeige

Forumthreads zu verwandten Themen

Pivot Tabellen Elemente anzeigen
thomas

Hallo,
hat einer von euch ein Beispiel für die Funktion "Ausgeblendete Elemente in Gruppierungsfunktionen anzeigen" in Pivottabellen. Ich schaffe es nicht das Icon zu aktivieren bzw. die Funktion zu nutzen
Ich krieg es auch nicht hin die Auswirkungen der Option "Elemente ohne Daten anze...
02.12.2008 14:46:51
Thread-ID: 1029521
3 Beiträge
Anzeige
Pivottabelle Spalten berechnen
karsten

Hallo Zusammen,
ich habe folg.Frage.:
Ich habe eine Tabelle mit 2 Spalten.
1.Spalte: DATUM
2.Spalte: CODE
DATUM CODE
01.01.2009 A
08.02.2009 M
23.02.2009 C
09.08.2009 F
Wie kann ich in einer Pivottabelle die Datumsangaben bereits in Monaten eingeteilt dar...
23.09.2009 11:15:14
Thread-ID: 1104846
10 Beiträge
Anzeige
PivotTabelle - Durchschnittsspalte
Jonathan

Hallo zusammen!
Ich habe eine umfangreiche Pivot Tabelle erstellt. 12 Spalten (Monate), Zeilen mit Kategorien und Werte jeweils die Ausgaben.
Ist es irgendwie möglich, am Ende eine zusätzliche Spalte einzufügen, in welcher der Durchschnittswert der jeweiligen Zeile steht?
Danke Euch...
01.06.2010 00:00:43
Thread-ID: 1159708
6 Beiträge
Pivot Tabellen Bestwerte anzeigen
Friedhelm

Hallo,
ich habe eine Tabelle mit Ergebnissen von Läufern und möchte in den Altergruppen und den verschiedenen Laufstrecken die Bestzeiten der Läufer darstellen.
Als Beispiel habe ich eine Tabelle beigefügt, die soweit den Pivot Auszug darstellt. Hier möchte ich in den Lauflängen nur die...
13.09.2010 11:02:06
Thread-ID: 1177216
8 Beiträge
Anzeige
Pivot Tabelle Gesamtergebnis nicht anzeigen
Annette

Hallo,
Ich möchte das Gesamtergebnis der Pivot Tabelle nicht anzeigen lassen.
Wie geht das?
Grüße
Annette
12.06.2011 14:39:40
Thread-ID: 1218212
3 Beiträge
Anzeige
Std. Abweichung pro Stunde und Tag berechnen
Michael

Hallo Experten,
ich stehe vor einem Problem das ich irgendwie nicht lösen kann. Dabei versuche ich mittels einer UDF den Mittelwert und die Std. Abweichung pro Std. berechnen. Leider ergibt mir meine Funktion den Wert 0% und nicht wie mauell berechnet 0.00004%. Ich verstehe nicht woran das...
18.09.2012 17:31:59
Thread-ID: 1279026
7 Beiträge
ToolTipText in PivotTabellen anzeigen
Andreas

Hallo Excel und VBA Profis,
ab Excel 2007 haben sich die Filtermöglichkeiten von PivotFeldern erheblich verbessert. Ich kann mit mehreren Filtern in einem Feld schnell Schnittmengen erzeugen. Sukzessive habe ich jedoch festgestellt, daß eine hohe Anzahl verschiedener Filter und Sortierungen...
12.06.2013 10:11:07
Thread-ID: 1317226
2 Beiträge
Durchschnitt der letzten 30 Tage berechnen
SabrinaB

Hi zusammen,
ich habe nochmals eine Frage zu VBA in Access.
Und zwar möchte ich die durchschnittliche Produktivität der letzten 30 Tage berechnen. Die 30 Tage sollen anhand des Datums bestimmt werden. Und es sollen Feiertage herausgerechnet werden.
Welche Syntax brauche ich dafür...
05.09.2014 14:42:33
Thread-ID: 1380180
3 Beiträge
Falsches Gesamtergebnis Pivotabelle
Martin

Hallo,
ich gruppiere in einer Pivottabelle Arbeitstunden und möchte wissen wieviel Mehr- oder Minderstunden pro Monat gearbeitet wurden. Dafür verwende ich eine berechnetes Feld in dem ich die Fixxtunden pro Monat(173)von den tatsächlichen abziehe. Das macht Excel auch prima. Nur leider gib...
21.08.2015 09:54:14
Thread-ID: 1443583
4 Beiträge
Filtertabelle Mittelwerte berechnen
Marco

Hallo,
ich habe ein Problem mit einer Excel-Tabelle und weiß leider nicht mehr weiter. Hoffe ihr könnt mir helfen.
Ich habe eine Tabelle mit mehreren Tausendzahlen, wir filtern diese auf einen definierten Zellenwertbereich und müssen anschließend separate Mittelwerte von der oberen und...
21.09.2015 09:14:35
Thread-ID: 1448407
5 Beiträge
Pivottabelle Mehrere Zellen anzeigen
Stefan

Hallo Zusammen,
ich habe eine Pivottabelle, in der ich in einem Feld Bestellungen angezeigt bekomme.
Zu diesen Bestellungen möchte ich aber gleichzeitig in der nächsten Spalte z.B. das Bestelldatum oder weitere Informationen zu der Bestellung angezeigt bekommen ohne dass ich die Zellen...
05.11.2015 10:27:01
Thread-ID: 1456422
5 Beiträge
Elemente einer Pivot-Tabelle berechnen
Michael

Hallo zusammen,
ich habe ein Problem bei der Berechnung eines Elements für eine Pivottabelle.
Die Fragestellung ist dabei folgende: Ich habe verschiedene Kunden, beispielweise Kunde A bis Kunde D, denen jeweils ein Wert zugewiesen wird. Ich hätte nun gerne als Elemente Kunde A und Kun...
03.08.2016 13:30:20
Thread-ID: 1507587
9 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Pivot Tabelle: Monatsdurchschnitt berechnen

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Stelle sicher, dass Deine Daten in einer Excel-Tabelle organisiert sind. Die Spalten sollten Datum, Betrag und Kategorie beinhalten.

  2. Pivot Tabelle erstellen:

    • Wähle Deine Daten aus und gehe zu Einfügen > PivotTable.
    • Wähle den Ort, an dem die Pivot-Tabelle platziert werden soll (neues Arbeitsblatt oder bestehendes).

  3. Pivot Tabelle konfigurieren:

    • Ziehe das Feld „Kategorie“ in den Bereich „Zeilen“.
    • Ziehe das Feld „Betrag“ in den Bereich „Werte“ und stelle sicher, dass die Zusammenfassungsfunktion auf „Summe“ eingestellt ist.

  4. Monatsdurchschnitt berechnen:

    • Klicke auf das Dropdown-Menü in der Pivot-Tabelle und wähle „Wertfeldeinstellungen“.
    • Wähle „Mittelwert“ aus, um den durchschnittlichen Betrag pro Kategorie anzuzeigen.
    • Um den monatlichen Durchschnitt zu berechnen, kannst Du eine Hilfsspalte in Deiner Datenquelle erstellen, die die Anzahl der Monate zählt, und dann die Summe durch diese Anzahl teilen.

  5. Zeitachse hinzufügen:

    • Füge eine Zeitachse hinzu, indem Du auf PivotTable-Analyse > Zeitachse einfügen klickst. Wähle das Datumsfeld aus, um die Daten nach Monat oder Jahr zu filtern.

Häufige Fehler und Lösungen

  • Pivot Tabelle zeigt keinen Mittelwert an: Stelle sicher, dass Du in den Werteinstellungen „Mittelwert“ ausgewählt hast.
  • Zeitachse erkennt Datum nicht: Überprüfe, ob Deine Datumswerte im richtigen Format vorliegen. Manchmal kann das Datum als Text formatiert sein. Verwende die Funktion DATWERT, um dies zu beheben.
  • Falsche Berechnungen: Wenn Deine Berechnungen nicht korrekt sind, überprüfe die Anzahl der gefilterten Monate. Verwende die Funktion ANZAHL, um sicherzustellen, dass die korrekten Werte verwendet werden.

Alternative Methoden

  • Verwendung von Formeln: Du kannst auch außerhalb der Pivot-Tabelle den Durchschnitt mit einer Formel berechnen. 
    =SUMME(Bereich)/ANZAHL(Bereich)
  • Power Query: Wenn Du Excel 2016 oder neuer verwendest, kannst Du Power Query nutzen, um Deine Daten vor dem Erstellen der Pivot-Tabelle zu transformieren und den Monatsdurchschnitt direkt zu berechnen.

Praktische Beispiele

  1. Beispiel für die Berechnung des monatlichen Durchschnitts: Angenommen, Du hast die folgende Tabelle:

    Datum       Betrag   Kategorie
01.01.2023 100,00   Lebensmittel
01.02.2023 150,00   Lebensmittel
01.03.2023 200,00   Lebensmittel

    Du kannst den monatlichen Durchschnitt mit der Formel =SUMME(B2:B4)/ANZAHL(B2:B4) berechnen.

  2. Pivot Tabelle für verschiedene Kategorien: Wenn Du mehrere Kategorien hast, kannst Du die Pivot Tabelle nutzen, um die Summen und Mittelwerte für jede Kategorie anzuzeigen, und dann den monatlichen Durchschnitt für jede Kategorie berechnen.

Tipps für Profis

  • Verwende gewichtete Mittelwerte: Wenn Du unterschiedliche Anzahlungen pro Monat hast, könnte ein gewichteter Mittelwert sinnvoll sein. Du kannst hierfür die Funktion SUMMENPRODUKT nutzen.

  • Dynamische Berechnungen: Nutze Dynamische Arrays oder Power Pivot, um Deine Berechnungen dynamisch zu gestalten und auf die Filter in der Pivot-Tabelle zu reagieren.

FAQ: Häufige Fragen

1. Wie kann ich den Durchschnitt aus einer Pivot Tabelle anzeigen?
Du kannst in den Wertfeldeinstellungen „Mittelwert“ auswählen. Alternativ kannst Du eine Hilfsspalte in Deiner Datenquelle erstellen.

2. Was ist der Unterschied zwischen Durchschnitt und Mittelwert in Excel?
In Excel sind Durchschnitt und Mittelwert meist gleichbedeutend. Der Durchschnitt ist die Summe aller Werte geteilt durch die Anzahl der Werte.

3. Wie kann ich die Anzahl der Monate in meiner Pivot Tabelle zählen?
Verwende die Funktion ANZAHL, um die Anzahl der Monate zu zählen, die Du in Deiner Pivot Tabelle anzeigen möchtest.

4. Kann ich in Excel 2010 eine Pivot Tabelle zur Berechnung des Monatsdurchschnitts verwenden?
Ja, in Excel 2010 kannst Du Pivot Tabellen nutzen und Datumswerte gruppieren, um Monatsdurchschnittswerte zu berechnen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige