Office: (Office 2013) Mittelwert der letzten 7 bzw 30 Tage

Hallo liebe Excel Profis, *cool.gif*

ich bräuchte ganz dringend Eure Hilfe!

Ich habe im Tabellenreiter "ProduktA" zu jedem Datum einen zugehörigen Wert (Liste verlängert sich täglich mit dem aktuellen Datum sowie einem Wert). An manchen Tagen kann es allerdings sein, dass kein Wert vorhanden ist und somit ein "-" in der Zelle steht.

Nun möchte ich einen Mittelwert der letzten 7 bzw. 60 Werte bilden, allerdings sollen dabei nur Zahlen berücksichtigt werden und "-" vernachlässigt werden (siehe Anhang).

Schon vorab ein riesen Dankeschön!

Viele Grüße

Mina

:)

 

Hallo Mina,

mein Vorschlag:
Code:

Gruß
Aloys

 

Hallo Aloys,

so würde es gehen, wenn ich immer manuell die letzten 7 bzw 60 Werte heraussuchen würde allerdings habe ich mich hier undeutlich ausgedrückt. Ich möchte, dass der Mittelwert aus den 7 bzw 60 Werten automatisch berechnet wird und das aus den Werten, welche sich im Reiter "ProduktA" befinden.
Die "Beispieltabelle" im Reiter Übersicht existiert in der Originaldatei nicht.

Viele Grüße

Mina

 

Moin Mina,
...und so geht es (natürlich dynamisch), wenn du Power Query einsetzt bzw. damit arbeitest:

 

Hallo Mina,

Power Query ist bestimmt eine ganz tolle Sache, wenn man mal geblickt hat, wie es funktioniert.

Ich habe mir die Lösung von Drogist angesehen und habe es mit meinem Kenntnisstand nicht geschafft, diese zu verstehen. weiterhin konnte ich feststellen, dass sich die Änderung des Stichtags und Änderungen der Mengen, nicht auf die Ergebnisse ausgewirkt haben.

Deshalb anbei eine klassische Lösung. Dazu habe ich die Datenreihe von Produkt A als Tabelle formatiert und eine Hilfsspalte zur Berechnung der Tage eingefügt.

Im Tabellenblatt "Übersicht" habe ich den Stichtag das Drehfeld definiert. weiterhin habe ich hier eine Spalte eingefügt, die dir zeigt, wie viele Tage für die Berechnung des Durchschnitts tatsächlich berücksichtigt wurden.

 

Hallo Mina,

und hier die formeltechnische Lösung ohne wenn und aber!
Zum vereinfachen, habe ich Dir B6 und B7 so umformatiert, dass Du nur 7 oder 60 eintippen musst, aber "7 Tage" angezeigt werden. Dadurch kann meine Formel sich einfacher auf die dort vorgegebene Anzahl an Tagen beziehen.

Achtung in C6 und C7 steht eine Matrixformel!
Geschweifte Klammern nicht mit eintippen, sondern durch STRG+SHIFT+ENTER erzeugen. Das gilt bei jedem reinklicken in die Zellformel!

LG Ingo

 

Hallo Ingo,

ich hatte mir natürlich schon gedacht, dass du das ohne Hilfspalte lösen wirst. Ich habe mich aber trotzdem damit auseinander gesetzt, weil es mir so tendenziell leichter fällt, andere Lösungen zu verstehen.

Aber dein "ohne wenn und aber" kann ich noch nicht so ganz beipflichten. Für Produkt "A" gibt es 56 Zeilen und 29 davon haben auch einen Wert.

Wenn keine 60 Werte vorhanden sind, dann soll ja der Durchschnitt den vorhandenen Werten gebildet werden. In diesem Fall ergibt dies 1283/29 = 44,24. Und dies muss auch erscheinen, wenn mehr als 29 Tage ausgewählt wurden. Bei deiner Lösung erscheint aber bei der Auswahl von 30 Tagen auch der für 29 Tage ermittelte Durchschnitt und ab der Auswahl 31 und mehr, erscheint dann in diesem Fall #ZAHL!

Ergänzung: Mit Stichtag 18.10.2017 ergibt sich für 7 Tage eine Summe von 410 und somit ein Durchschnitt von 58,57. Dein Formel zeigt aber einen Durchschnitt von 64,3333, was einer Summe von 450 entspricht. Anhand der Einzelwerte kann ich mir aber nicht erklären, wie die Differenz von 40 zustande kommt.

 

Stimmt.

Du weißt, dass du im rechten Seitenfenster durch Klick auf einen der Schritte nachvollziehen kannst, was da passiert? (Ohne Unterton und wortwörtlich gemeint: Es sind ja nur 5 Schritte)

Stichtag: Stimmt, denn ich habe einfach angenommen, dass der letzte Eintrag entscheidend ist. Auf C3 habe ich also gar nicht zugegriffen. Bei Bedarf hätte ich dann alles raus gefiltert, was jünger ist. Mengen: Da musst du in der Abfrage auf Aktualisieren klicken, erst dann wird (sinnvollerweise) neu berechnet.

Unbedingt wichtig, denn nicht jeder kann/will/darf Power Query einsetzen. Jede funktionierende Lösung hat ihre Berechtigung. Ein TE kann sich nur entscheiden, wenn mehrere zielführende Lösungen existieren.

 

Hallo zusammen,

vielen Dank für die schnellen und verschiedenen Lösungsansätze. *Smilie

Ich werde morgen mal einen Test in der Originaldatei vornehmen und euch ein Feedback geben.

Freue mich schon auf das Ergebnis!

Viele Grüße

Mina

 

Hallo WS-53!

Nun, davon hat der TS nichts geschrieben. Das mag auch sinnvoll sein, von mir aus auch üblich, aber ggfs. gerade nicht gewünscht, da man nicht weiß wann dann wirklich 60 Werte mit in die Berücksichtigung kamen. Und, weil der TS davon nichts schrieb, ging ich davon aus, dass das bei ihm gar nicht erst vorkommen kann. Aber natürlich lässt sich das Problem ganz leicht so abfangen:

PHP:

         {=MITTELWERT(INDEX(ProduktA!$C:$C;KGRÖSSTE(WENN((ProduktA!$C$2:$C$99<>"-")*(ProduktA!$C$2:$C$99<>"")*(ProduktA!$C$2:$C$99<=$C$3);ZEILE($C$2:$C$99));MIN(ANZAHL(ProduktA!$C$2:$C$99);$B6))):INDEX(ProduktA!$C:$C;VERGLEICH($C$3;ProduktA!$A:$A;0)))}


 

Hallo Ingo,

ich hatte die Aussage:

so interpretiert, dass wenn weniger als 7 bzw. 60 Zahlenwerte vorliegen, nur mit den vorhandenen gerechnet wird. Da dann aber die Durchschnittswerte nicht zu der Anzahl Tage passen würden, habe ich ja bei meiner Lösung ausgewiesen, mit welcher Anzahll gerechnet wurde.

Ich bin auch hier davon ausgegangen, dass du auch diese Anforderung noch in deine Formel untergringen kannst, aber diese Erweiterung hätte ich nicht geschafft. Trotz mitlerweile einiger Jahre, die ich mich nun etwas intensiver mit Excel beschäftige, gibt es halt immer noch erhebliche Defizite.

Ich habe ja in meiner Lösung, so wie eigentlich immer, die 3 zusätzlichen Kopzeilen (Titel, Link, Leerzeile) eingefügt und damit ich deine Lösung dann in meine direkt kopieren konnte, auch in deiner Mappe 3 Leezeilen eingefügt.

Dadurch hat sich dann in der Formel der Teil: ZEILE($C$2:$C$99) in ZEILE($C$5:$C$102) verschoben und somit für das falsche Ergebnis gesorgt.

 

Hallo Drogist,

Ja das hatte ich gesehen. Und ich habe auch riund eine halbe Stunde damit verbracht, an allen möglichen Stellen zu klicken (auch rechte Maustaste) um zu erfahren, wie es deiniert ist. Aber ich habe es schon nicht geschafft heruaszubekommen, wie due die zusätzliche Indexspalte eingefügt hast, mittels derer du dann ja bei absteigender Sortierung die letzten 7/60 Werte berücksichtigt hast.

Da ich die Aktualisierungstaste nicht gefunden habe, hatte ich es mit F9 (Neuberechnung) versucht, Die Ergebnisse zu aktualisieren. Dies hat aber nichts gebracht.

Ich nutze privat die Office365 Version mit 5 Lizenzen und war lange der Meinung, damit hinreichend versorgt zu sein. Leider ist es aber so, dass für die Version das AddIn Power Query nicht installiert werden kann. Ansonsten hätte ich mich in meiner Freizeit schon intensiver mit diesem leistungsfähigen Tool beschäftigen können.

Ich verstehe da auch im übrigen nich die aktuelle Politik von Microsoft. Denn früher hatte Microsoft das Office-Paket an Anwender, die es eigentlich gar nicht bräuchten, ja mehr oder weniger verschenkt.

Das hat dann mit dazu geführt, dass die, die sich damit auskannten und später Entscheider wurden, natürlich Microsoft favoriisiert haben.

Das mehr oder weniger verschenken, war ja auch kein Umsatzverlust für Microsoft, denn für diese Zielgruppe gab und gibt es ja genügend kostenfreie Alternativen.

 

Moin,
Index: In der Abfrage Alle Dateien: Spalte hinzufügen|Indexspalte|Von 1
Office 365: Ist im Prinzip 2016 (wird aber ständig erweitert). Alsi findest du die Power Query-Funktionalität unter Daten | Abrufen und transformieren. Vileeicht hilft dir ja dieser Blog-Beitrag etwas weiter: http://www.excel-ist-sexy.de/pq-basi...-kurzreferenz/

Was die MS-Politik betrifft: Das machen wohl alle Hersteller ähnlich, denn die Studi-Lehrenden-Versionen sind fast immer preislich weit unterhalb des normalen Straßenverkaufspreises angesiedelt. Und ich halte das sogar für legitim ...

 

Hallo Drogist,

ich habe nun noch etwas rumprobiert. Wie ich die INDEX-Spalte generieren kann und mit welchem Startwert sowie Schrittweite dieser erscheinen soll, habe ich nun gefunden, auch den Filter für die Anzahl der Tage habe ich gefunden. Allerdings sehe ich hier nicht die Möglichkeit, dass ich hier eine Zellreferenz eintragen kann. Denn dann könnte ich ja die Anzahl der Tage, die in die Berechnung einfließen sollen, dynamisch bestimmen.

Das mit der Aktualisierung habe ich nun auch gefunden. Aber es erscheint mir etwas unpraktisch, dass wenn ich in der Liste von Produkt A einen Wert verändere, der sich sowohl auf den Durchschnitt 7 Tage als auch auf den Durchschnitt 60 Tage auswirkt, ich bei Power Query-Ergebnisse einzeln aktualisieren muss!

Und wie, bzw. wo du aus den gefilterten Daten den Durchschnitt berechnest, habe ich auch noch nicht herausgefunden. Genauso wenig habe ich herausgefunden, wie das Ergebnis aus Power Query in das Tabellenblatt übernommen werden kann.

 

So, ich bin am überlegen, ob ich aus der Aufgabenstellung einen Blog-Beitrag mit der Lösung in Einzelschritten mache. Ich melde mich in der Sache Anfang der kommenden Woche noch einmal ...