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

Median nach Datum plus Bedingung

Median nach Datum plus Bedingung
28.02.2019 14:24:09
MichaelG
Hallo,
ich habe ein Problem, welches ich auch mit Unterstützung von Google und diverser Forumseinträge leider nicht alleine lösen kann. Daher wende ich mich an die Experten hier.
In einer Liste mit Messwerten muss ich den Median der jeweils letzen 10 vorherigen Messerte ermitteln. Dabei gibt es verschiedene Regionen und der Median soll in Abhängigkeit der jeweilugen Region ermittelt werden.
Erschwerend kommt hinzu, dass die Liste jederzeit nach verschiedenen Kriterien sortiert und gefiltert werden kann, was die ermittleten Mediane nicht verändern soll.
Ich habe mich an verschiedenen Lösungsansätzen ais dem Netz versucht, komme aber auf keinen grünen Zweig. Ich würde sogar den wirklich nicht eleganten Weg gehen und neben der Datumsspalte 10 Hilfsspalten einfügen um dort schrittweise die 10 vorhergenden Messsdaten einzufügen um in Anschluß in einer anderen Spalte den Median zu bilden. Dabei scheitereich aber an der gleichzeitigen Abgrenzung von Datum und Region.
Ich habe mal eine Beispieltabelle angefügt und hoffe das mir jemand mit dem Problem weiter helfen kann.
https://www.herber.de/bbs/user/127995.xlsx

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit einer Matrixformel und ...
28.02.2019 15:12:04
neopa
Hallo Michel,
... am einfachsten mit einer zusätzlichen Hilfsspalte, um die gefilterten Werte einfacher zu berücksichtigen.
Allerdings wenn Du immer nur letzten 10 Werte auswerten willst, wird für einige Werte kein Median ermittelt.
In der Hilfsspalte (hier Spalte I) I3: =TEILERGEBNIS(2;C3) und Formel nach unten kopieren.
In G34:
{=WENNFEHLER(MEDIAN(WENN(($A$3:$A33=$A34)*(ZEILE(B$3:B33)>=
AGGREGAT(14;6;ZEILE(B$3:B$33)/($A$3:$A33=$A34)/($I$3:$I33);10));D$3:D33));"")}
Die geschweiften Klammern{} werden nicht eingegeben, sondern Formel mit Strg+Shift + Enter eingeben.
Formel nach unten und oben sowie links und rechts kopierbar.
Gruß Werner
.. , - ...
Anzeige
AW: mit einer Matrixformel und ...
28.02.2019 15:58:32
Günther
Moin,
und ich biete mal wieder mein Lieblings-Tool (Power Query) an ...
Ist zugegeben einiges an einmaligem Aufwand, dafür aber schön dynamisch.
Gruß
Günther
AW: die Formellösung ist doch dynamisch ...
28.02.2019 16:09:01
neopa
Hallo Günther,
... wenn die Datentabelle als "intelligente" Tabelle (inklusive der Hilfsspalte, die natürlich auch ausgeblendet werden kann) formatiert wurde. Und dies auch ohne Zusatzklick wie bei PQ erforderlich, wenn Datenerweiterungen vorgenommen werden.
Wenn es sich jedoch um eine echte Massendatenauswertung handeln sollte, also einige tausende Datensätze vorhanden sind, dann ist PQ natürlich vorzuziehen.
Gruß Werner
.. , - ...
Anzeige
AW: die Formellösung ist doch dynamisch ...
28.02.2019 16:34:50
Günther
Stimmt, Werner!
Ich hatte mich da auch nicht wirklich "glücklich" ausgedrückt, eine andere Formulierung wäre passender gewesen.
Gruß
Günther
AW: mit einer Matrixformel und ...
28.02.2019 16:46:04
Daniel
HI
es hieß doch, dass Sortierung und Filterung das Ergebnis nicht beeinflussen sollen.
also braucht man das mit dem Teilergebnis nicht und es reicht die Matrixformel (Eingabe immer mit STRG+SHIFT+ENTER abschließen):
=MEDIAN(WENN(A3:A34="BSK";WENN(B3:B34>=KGRÖSSTE(WENN(A3:A34="BSK";B3:B34);10);D3:D34)))
Gruß Daniel
AW: mit einer Matrixformel und ...
28.02.2019 17:58:39
MichaelG
Das mit den Teilergebnissen in der Hilfsspalte habe ich nicht versucht, weil ja das Ergebnis, wie Daniel schon bemerkt hat, eben nicht von der Filterfunktion beeinflusst werden soll.
Statt dessen habe ich die Formel von Daniel genommen und nur die Länge der Bereiche erweitert und die Strings gegen Zellverweise ausgetauscht.
Leider führt das (noch) nicht zum gewünschten Ergebnis. Für das markierte Beispiel ist der kalkulierte Median nämlich 0 statt 42. Dabei habe ich die Matrixformeleingabe mit SHIFT-STRG_EINGABE natürlich berücksichtigt (siehe angehängte Datei).
Auffallend ist, dasss gerade für die ältesten (ersten) Daten ein Median errechnet werden kann, nicht aber für die letzten (aktuellsten). Das wäre andersrum zu erwarten, weil für die ersten Werte keine älteren Analysedaten existieren von denen der Median errechnet werden könnte.
Obwohl mir bei Verfolgung der Formel relativ schnell der Kopf schwirrt scheint es mir doch so zu sein, dass der Median nicht aus den 10 Vorgängern des Datums der aktuellen Zeile ermittelt wird, sondern von den 10 größten (letzten) Daten im Spalte B überhaupt - aber zumindest schon mal in Abhängigkeit von der Region. Passt aber wie man am Beispiel sieht auch nicht.
Ich habe ein bisschen hin- und herprobiert, komme aber immer noch nicht auf die richtige Formel.
Gewünsch wäre also:
- Nimm das Datum in Spalte B und die Region in Spalte A der aktuellen Zeile
- ausgehen von diesem Datum und dieser region ermittle die nächsten 10 Vorgänger Daten (älteren Daten)
- ermittele den Median der Messwerte in Spalte D zu den soeben ermittelten 10 Daten
https://www.herber.de/bbs/user/128005.xlsx
Anzeige
AW: nun zwar klarer, allerdings ...
28.02.2019 19:51:29
neopa
Hallo Michael,
... wenn Du wirklich so 1000 Datensätze derartig auswerten willst, dürfte Dein PC ganz schön beschäftigt werden, denn Du hat Dein PC so, wie Du es anstrebst, bis zu 3000 Matrixformeln auszuwerten.
Theoretisch wäre es z.B. mit folgende Formel in G3 möglich
{=WENN($A3="";"";WENNFEHLER(MEDIAN(WENN(($A$3:$A$999=$A3)*($B$3:$B$999>=
AGGREGAT(14;6;$B$3:$B$999/($A$3:$A$999=$A3)/($B$3:$B$999
und Formel nach rechts, links und unten kopierst.
Wenn Du anstelle Deiner Listenauswertung der Mediane, diese immer nur für jeweils einen, z.B. über Dropdownzellen vorgegebenen Datenwert für Region und Datum auswertest, wären es lediglich drei Formeln.
Gruß Werner
.. , - ...
Anzeige
AW: nun zwar klarer, allerdings ...
01.03.2019 03:47:22
Günther
Ich komme zu diesem Ergebnis… ;-)
Und wenn es richtig ist, kommt es Oster-Sonntag in's Nest (meinen Blog).
Userbild
Gruß
Günther
AW: wahrscheinlich ...
01.03.2019 07:40:21
neopa
Guten Morgen Günther,
... sind das die gewünschte Ergebniswerte für die Beispieldaten.
Auf die komme ich auch, wenn ich in meiner letzten Formelangabe anstelle mit "kleiner" B3 mit "kleiner gleich" $B3 vergleiche. Die Dollarzeichen vor B3 fehlten nämlich in meiner Formel gestern noch. Außerdem hatte ich mich auch von der Teilaussage: "... Ermittlung des Median ohne Berücksichtigung des aktuellen Wertes in gleicher Zeile, (dann also ohne Zelle D34 und statt dessen incl. Zelle D15)" leiten lassen. Heute morgen sehe ich nun jedoch, dass vor seiner Aussage noch "Alternativ" steht. Was immer er damit auch genau gemeint hat.
Damit die Datentabelle beliebig sortiert werden kann, müsste diese noch so geändert werden, dass A1:B1 nach A2:B2 verschoben und C1:H1 an die Texte in C2:H2 angehangen werden. Damit danach die Datentabelle in eine "intelligente" Tabelle formatiert werden kann und eine Sortierung der Daten auch wirklich problemlos möglich ist ist.
Wenn dann immer noch eine Formellösung gewünscht sein sollte, lässt sich die Formel natürlich auf eine solche umdefinieren. Dies mit dem Vorteil, dass sich diese dann automatisch an Datenerweiterung anpasst.
Wenn aber wirklich 1000 Datensätze ausgewertet werden sollen, wäre wie bereits geschrieben, wohl Deine PQ vorzuziehen.
Gruß Werner
.. , - ...
Anzeige
AW: wahrscheinlich ...
01.03.2019 12:29:36
MichaelG
Herzlichen Dank an alle!
Die Lösung von neopa C ist genau das was gewünscht war. Sorry wenn ich mich diesbzgl. anfangs nicht genau genug ausgedrückt habe.
Mein Datenbereich von 1000 Zeilen war großzügig gewählt. In der Originaldatei die Oktober 2015 anfängt sind bis jetzt erst gut 400 Datenzeilen aufgelaufen. Zudem benötige ich die Formel ja nur in den jeweils letzten 10 Datensätzen einer Region. In älteren Datensätzen kann ich die Formeln regelmäßig gegen Festwerte austauschen, da sich an den Messwerten und Medianen im Nachhinein nichts mehr ändert.
Verstanden habe ich die Formel aber definiv noch nicht. Da muss ich mich in einer stillen Stunde mal drin vertiefen. Auf den ersten Blick erscheint bie mir beeindruckend verwirrend ;-)
Anzeige
AW: Median nach Datum plus Bedingung
06.03.2019 11:40:38
MichaelG
Hallo,
nachdem mir hier mit meinem Problem letztes mal so kompetent geholfen wurde wende ich mich nun noch mal mit einer kleinen Ertgänzung an Euch.
Es geht um die Gleiche Tabelle und printzipiell das gleiche Problem, nur mit einer anderen Funktion. Mein Versuch die bereits gefundene Formel durch austauschen der Funktion anzupassen ist leider gescheitert. Ich hänge erneut die bereits bekannte Beispieltabelle in geändeter Fassung an.
Ging es im ersten Anlauf darum den MEDIAN der letzten 10 Messwerte in Abhängigkeit eines Gebietes zu finden, so soll nun zusätzlich auch noch QUANTIL.EXKL und QUANTIL.INKL gefunden werden.
In angehängter Tabelle habe ich in den Spalten H und I die MEDIAN-Formel aus Spalte G entsprechend abgewandelt. Das führt abger zu keinem sinnvollen Ergebnis, obwohl doch die Abgrenzung hinsichtlich Datum und Region eigentlich die selbe ist.
In den Spalten S und U habe ich die letzten 10 Messwerte für die Region BSK herausgesucht und die Werte für QUANTIL.EXKL und QUANTIL.INKL ermittelt. Diese stimmen jedoch nicht mit denen in den Spalten H und I überein. Was also muss ich an der Formel noch ändern?
Ich danke schon mal vorab für jeden Hinweis.
https://www.herber.de/bbs/user/128134.xlsx
Anzeige
AW: Median nach Datum plus Bedingung
06.03.2019 12:13:23
MichaelG
hatte vergessen das Problem als "offen" zu kennzeichnen.
AW: mit einer geschachtelten AGGREGAT()-Formel ...
06.03.2019 13:40:25
neopa
Hallo Michael,
... für QUANTIL.EXKL so:
=AGGREGAT(18;6;D$3:D$999/($A$3:$A$999="BSK")/($B$3:$B$999&gt=AGGREGAT(14;6;$B$3:$B$999/($A$3:$A$999="BSK");10));0,66)
und für QUANTIL.INKL die gleiche Formel nur das 1. Argument der Formel anstelle 18 eine 17 ist.
Gruß Werner
.. , - ...
AW: mit einer geschachtelten AGGREGAT()-Formel ...
06.03.2019 15:02:58
MichaelG
Hallo Werner,
herzlichen Dank für Deine wirklich superschnelle Antwort. Ich muss weiterhin gestehen, dass ich die Formel mit der Aggregatfunktion nicht verstehe. Ich werde mich aber damit beschäftigen sobald ich ein wenig mehr Zeit finde. Jetzt ist mir zunächst daran gelegen möglichst schnell mit der Tabelle weiter zu kommen.
Ich habe Deine Formel eingesetzt und einen (Teil)Erfolg zu verzeichnen :-) Aber bei der Beschreibung dessen was gewünscht ist habe ich mich scheinbar erneut nicht eindeutig ausgedrückt - sorry dafür!
Die Formel für QUANTIL.EXKL rechnet scheinbar korrekt, aber sie scheint sich nicht wie (von mir) gedacht auf den korrekten Zeitraum zu beziehen. Es sollen jeweils die 10 Messwerte berücksichtigt werden, die zeitlich genau vor dem Datum ("B") der aktuellen Zeile liegen - natürlich auch unter Berücksichtigung der richtigen Region ("A"). Also analog dazu wie es beim MEDIAN umgesetzt ist.
Momentan erhalte ich für QUANTIL.EXKL in allen Zeilen den gleichen Wert, nämlich den, der für die jüngsten 10 Messwerte der Region BSK kalkuliert wird.
In angefügter Datei habe ich auf dem Blatt "Prüfung" mal die erwarteten Werte für zwei Daten der Region BSK in Spalte "B" zusammen gestellt.
Leider stimmt der kalkulierte Wert der geänderten Formel für QUANTIL.INKL gar nicht. Muss evtl die zweite Aggregatformel in der Zelle auch noch angepasst werden?
https://www.herber.de/bbs/user/128144.xlsx
Anzeige
AW: ja, ich erinnere mich ...
06.03.2019 15:29:22
neopa
Hallo Michael,
... dann folgende Formel für QUANTIL.EXKL in H34:
=WENNFEHLER(AGGREGAT(18;6;D$3:D$999/($A$3:$A$999=A34)/($B$3:$B$999&lt=B34)/($B$3:$B$999&gt=
AGGREGAT(14;6;$B$3:$B$999/($A$3:$A$999=A34)/($B$3:$B$999&lt=B34);10));M$10);"")

und nach oben ziehen (die 999 wieder auf das max notwendige anpassen)
und für QUANTIL.INKL die gleiche Formel nur anstelle der 18 nicht die 17 (sorry) sondern die 16 nehmen.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige