MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 18.10.2017, 19:15   #1
MiiNA_*
Neuer Benutzer
Neuer Benutzer
Standard Excel 2013 - Mittelwert der letzten 7 bzw 30 Tage

Hallo liebe Excel Profis,

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
Angehängte Dateien
Dateityp: xlsx Mappe1.xlsx (13,2 KB, 10x aufgerufen)
MiiNA_* ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 18.10.2017, 21:47   #2
aloys78
MOF Meister
MOF Meister
Standard

Hallo Mina,

mein Vorschlag:
Code:

=MITTELWERTWENN(J6:J18;"<>"&"-";J6:J18)
Gruß
Aloys
aloys78 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.10.2017, 06:56   #3
MiiNA_*
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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
MiiNA_* ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.10.2017, 11:35   #4
Drogist
MOF User
MOF User
Standard

Moin Mina,
...und so geht es (natürlich dynamisch), wenn du Power Query einsetzt bzw. damit arbeitest:
Angehängte Dateien
Dateityp: xlsx Mittelwerte 7-60.xlsx (33,0 KB, 13x aufgerufen)

__________________

Internette Grüße
Drogist

(Gut) Gefunden bei Storax: "This isn't a code writing service, you need to do some research and have a go at writing some of your own code."
Ich verwende Excel 2016.Wenn du keine Angaben über deine Version gemacht hast gehe ich davon aus, dass auch du mindestens diese Version hast.
Drogist ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.10.2017, 14:59   #5
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.
Angehängte Dateien
Dateityp: xlsx mof - Mittelwert der letzten 7 bzw 30 Tage.xlsx (20,4 KB, 3x aufgerufen)

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.10.2017, 15:28   #6
Superingo2
MOF Koryphäe
MOF Koryphäe
Standard

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
Angehängte Dateien
Dateityp: xlsx Mittelwert_der_letzte_x_Werte_die_gefüllt_sind.xlsx (14,8 KB, 6x aufgerufen)

__________________

Viel Spaß


.....ein Feedback wäre nett.....
Superingo2 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.10.2017, 16:45   #7
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.

Geändert von WS-53 (19.10.2017 um 17:01 Uhr).
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.10.2017, 18:33   #8
Drogist
MOF User
MOF User
Standard

Zitat: von WS-53 Beitrag anzeigen

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

Stimmt.

Zitat:

Ich habe mir die Lösung von Drogist angesehen und habe es mit meinem Kenntnisstand nicht geschafft, diese zu verstehen.

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)

Zitat:

weiterhin konnte ich feststellen, dass sich die Änderung des Stichtags und Änderungen der Mengen, nicht auf die Ergebnisse ausgewirkt haben.

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.

Zitat:

Deshalb anbei eine klassische Lösung.

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.

__________________

Internette Grüße
Drogist

(Gut) Gefunden bei Storax: "This isn't a code writing service, you need to do some research and have a go at writing some of your own code."
Ich verwende Excel 2016.Wenn du keine Angaben über deine Version gemacht hast gehe ich davon aus, dass auch du mindestens diese Version hast.
Drogist ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.10.2017, 20:44   #9
MiiNA_*
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo zusammen,

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

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
MiiNA_* ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 20.10.2017, 08:33   #10
Superingo2
MOF Koryphäe
MOF Koryphäe
Standard

Hallo WS-53!

Zitat:

Wenn keine 60 Werte vorhanden sind, dann soll ja der Durchschnitt den vorhandenen Werten gebildet werden.

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-Code:

{=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)))} 
Das hättest Du aber auch alleine geschafft

Zitat:

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.

Hier kann ich Dir nicht helfen, denn bei mir wird 58,57 errechnet (Kannst Du auch leicht prüfen: Mach einfach nochmal meine Datei neu heruntergeladen von hier, neu auf!) Das Problem musst Du selber erzeugt haben

Aber es freut mich immer wieder, wenn sich einer konstruktiv und intensiv mit meiner Lösung beschäftigt. Danke dafür.

Kollegiale Grüße
Ingo

__________________

Viel Spaß


.....ein Feedback wäre nett.....
Superingo2 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 20.10.2017, 09:53   #11
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

Hallo Ingo,

ich hatte die Aussage:

Zitat:

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).

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.

Zitat:

Das hättest Du aber auch alleine geschafft

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.

Zitat:

Hier kann ich Dir nicht helfen, denn bei mir wird 58,57 errechnet (Kannst Du auch leicht prüfen: Mach einfach nochmal meine Datei neu heruntergeladen von hier, neu auf!) Das Problem musst Du selber erzeugt haben.

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.

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 20.10.2017, 10:14   #12
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

Hallo Drogist,

Zitat:

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)

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.

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 20.10.2017, 10:54   #13
Drogist
MOF User
MOF User
Standard

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 ...

__________________

Internette Grüße
Drogist

(Gut) Gefunden bei Storax: "This isn't a code writing service, you need to do some research and have a go at writing some of your own code."
Ich verwende Excel 2016.Wenn du keine Angaben über deine Version gemacht hast gehe ich davon aus, dass auch du mindestens diese Version hast.
Drogist ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 20.10.2017, 12:00   #14
WS-53
MOF Koryphäe
MOF Koryphäe
Standard

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.

__________________

VG, WS-53


>>> Ein Spezialist kann nicht viel, dies aber gut. Die Steigerung ist, noch weniger noch besser zu können. Die Krönung ist, nichts zu können, aber darin perfekt zu sein! Es gibt aber auch Naturtalente, die überspringen die Ersten beiden Stufen. <<<

Übrigens, Feedback, egal welcher Art, ist immer hilfreich. Und erledigte Beiträge sollten auch den Status "erledigt" erhalten.
WS-53 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 20.10.2017, 16:32   #15
Drogist
MOF User
MOF User
Standard

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 ...

__________________

Internette Grüße
Drogist

(Gut) Gefunden bei Storax: "This isn't a code writing service, you need to do some research and have a go at writing some of your own code."
Ich verwende Excel 2016.Wenn du keine Angaben über deine Version gemacht hast gehe ich davon aus, dass auch du mindestens diese Version hast.
Drogist ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 18:29 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.