Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1556to1560
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

Standardabweichung für datumsreihe

Standardabweichung für datumsreihe
22.05.2017 20:57:37
bernhard
https://www.herber.de/bbs/user/113733.xlsx
Die Aufgabe: für jedes Monat soll ich die Standardabweichung berechnen. Ist prinzipiell kein problem mit "=stabw.n('daten vom dezember 2016')", allerdings hab ich keinen bock, das für weit über 100 Monate manuell zu machen.
leider fällt mir keine formel ein, um das zu automatisieren. ich denke die ganze zeit an eine formel, die das monat und jahr von der ziel-zelle (D5) mit der spalte A vergleicht, und nur die Werte nimmt, in denen das monat und jahr der beiden gleich ist.
bitte um hilfe in excel, wenn möglich ohne vba.

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
INDEX klappt hier wohl nicht ...
22.05.2017 21:12:20
lupo1
{=STABW(WENN(--TEXT(A1:A5;"JMM")=1705;B1:B5))} mit Strg-Umsch-Eingabe ohne {}
Nicht korrekt rechnet:
=STABW(INDEX(B1:B5*(--TEXT(A1:A5;"JMM")=1705);))
Die richtig angepasste Fml für E5ff lautet ...
22.05.2017 23:53:46
Luc:-?
…dann als singulare (1zellig-1wertige) MatrixFml so, Lupo & Bernhard:
E5[:E7]: {=STABW.N(WENN(TEXT(A$5:A$3490;"JJJJMM")=TEXT(D5;"JJJJMM");B$5:B$3490))}
Deine INDEX-Fml enthält auch 0en, Lupo,
deshalb klappt das nicht, während die Wahrheitswerte (FALSCH) aus der fktionierenden Fml ignoriert wdn.
Gruß, Luc :-?
Besser informiert mit …
Anzeige
Aber auch INDEX klappt, wenn man es richtig ...
23.05.2017 02:44:51
Luc:-?
…macht, Lupo (& Bernhard),
nur wird die Fml dann deutlich länger und ist immer noch eine MatrixFml. Hieran dürfte dann auch neopa mit seinen AGGREGAT-Matrix­funktion(alität)sfmln scheitern, weil so wohl die Erkennungskapazitäten von AGGREGAT für Datenfelder überschritten wdn (die von AggregateXk übrigens nicht!). Aber evtl hat er ja eine andere NormalFml-Idee… ;-)
Ich habe übrigens ein ganzes Bündel von Fmln unterschiedlicher Längen und Verfahren vorbereitet, das ich im Laufe des Tages einstellen könnte…
Morrn, Luc :-?
Dazu trotz anzunehmenden Desinteresses ...
23.05.2017 18:40:29
Luc:-?
…des Fragestellers und Werners (neopa) naheliegender TotalLösung noch meine Fml-Sammlung, allerdings nicht wieder angepasst, sondern von den Konkreta etwas abstrahiert:
 ABCDEFGHIJKL
17
28.04.20171Wähle Daten für den …STABW.N in 12 Varianten m.UDF u/o als (UDF-)MatrixfmlFormellängen  12.05.20172Monat des01.05.20170,8164965810,8164965810,8164965810,816496581726113914722.05.20173 zu F19:0,8164965810,8164965810,8164965810,8164965817710414415227.05.20174 0,8164965810,8164965810,8164965810,8164965810,816496581817014815604.06.20175 duale MxFmlNoErrRange-DATUMWENN-TEXTINDEX-VERGLEICH-TEXTINDEX-VERGL-DATUM    E18:{=STABW.N(NoErrRange(B17:B21;;DATUM(JAHR(A17:A21);MONAT(A17:A21);1)=D18))}E19:{=AGGREGAT(8;6;NoErrRange(B17:B21;;DATUM(JAHR(A17:A21);MONAT(A17:A21);1)=D18))}F18:{=STABW.N(WENN(TEXT(D18;"MMJJ")=TEXT(A17:A21;"MMJJ");B17:B21))}F19:{=STABW.N(WENNFEHLER(AGGREGAT(15;6;WENN(TEXT(D18;"MMJJ")=TEXT(A17:A21;"MMJJ");B17:B21);ZEILE(A1:A5));""))}G18:{=STABW.N(INDEX(B17:B21;VERGLEICH(TEXT(D18;"MMJJ");TEXT(A17:A21;"MMJJ");0)):INDEX(B17:B21;VERGLEICH(TEXT(D18;"MMJJ");TEXT(A17:A21;"MMJJ"))))}H18:{=STABW.N(INDEX(B17:B21;VERGLEICH(D18;DATUM(JAHR(A17:A21);MONAT(A17:A21);1);0)):INDEX(B17:B21;VERGLEICH(D18;DATUM(JAHR(A17:A21);MONAT(A17:A21);1))))}  D20:D21:{=WAHL(ZEILE(D1:D2);STABW.N(WENNFEHLER(AGGREGAT(15;6;INDEX(WENN(TEXT(D18;"MMJJ")=TEXT(A17:A21;"MMJJ");B17:B21););ZEILE(A1:A5));""));"duale MxFml")} G19:{=AGGREGAT(8;6;INDEX(B17:B21;VERGLEICH(TEXT(D18;"MMJJ");TEXT(A17:A21;"MMJJ");0)):INDEX(B17:B21;VERGLEICH(TEXT(D18;"MMJJ");TEXT(A17:A21;"MMJJ"))))} H19:{=AGGREGAT(8;6;INDEX(B17:B21;VERGLEICH(D18;DATUM(JAHR(A17:A21);MONAT(A17:A21);1);0)):INDEX(B17:B21;VERGLEICH(D18;DATUM(JAHR(A17:A21);MONAT(A17:A21);1))))}E20:=AggregateXk(-8;6;NoErrRange(B17:B21;;DATUM(JAHR(A17:A21);MONAT(A17:A21);1)=D18))F20:=AggregateXk(-8;6;WENN(TEXT(D18;"MMJJ")=TEXT(A17:A21;"MMJJ");B17:B21)) G20:=AggregateXk(-8;6;INDEX(B17:B21;VERGLEICH(TEXT(D18;"MMJJ");TEXT(A17:A21;"MMJJ");0)):INDEX(B17:B21;VERGLEICH(TEXT(D18;"MMJJ");TEXT(A17:A21;"MMJJ")))) H20:=AggregateXk(-8;6;INDEX(B17:B21;VERGLEICH(D18;DATUM(JAHR(A17:A21);MONAT(A17:A21);1);0)):INDEX(B17:B21;VERGLEICH(D18;DATUM(JAHR(A17:A21);MONAT(A17:A21);1))))
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Die Sammlung zeigt auch, dass in diesem Fall die einfache Lösung ala Lupo völlig ausreicht, während das unbedingte Einführen­wollen von INDEX (und ggf AGGREGAT) nur zu längeren Fmln führt, es sei denn, man verwendet eine bestimmte (Kombination von) UDF(s). Mit INDEX kann man hier auch keine Vermeidung der MatrixFml-Form erreichen, auch mit AGGREGAT (m/o INDEX) nicht, weil das 3.Argument zu komplex ist. Im Ggteil, es führt in einem Fall sogar zu einer dualen MatrixFml! Nur eine UDF wie AggregateXk, die so konstruiert ist, dass sie bei negativem 1.Argument intern die ganze Matrix zu Argument3 aufbaut, ist offensichtlich dazu imstande, ohne MatrixFml-Form auszukommen.
Luc :-?
Anzeige
AW: wie bereits geschrieben ...
24.05.2017 17:31:24
...
Hallo Luc,
... und zwar im anderen tread: wenn unbedingt eine Formellösung zum Einsatz kommen sollte (wobei ich keinen wirklichen Grund hierfür sehe), dann würde ich hier auch eine klassische Matrixformel einsetzen, weil die Funktion 7 und 8 von AGGREGAT() nicht wie die Funktionen ab der 14 eine Matrix richtig verarbeiten können.
Aber wie auch geschrieben, Deiner Aussage: "Mit INDEX kann man hier auch keine Vermeidung der MatrixFml-Form erreichen, auch mit AGGREGAT (m/o INDEX) nicht..." widerspreche ich. man kann sehr wohl auch mit INDEX() und AGGREGAT() in einer Matrixfunktion(alität)sformel das Ergebnis ermitteln.
Allerdings muss dann sowohl INDEX() als auch AGGREGAT() anders eingesetzt werden als von lupo1 und möglicherweise auch von Dir angedacht. Aber da die entsprechende Formel doppelt so lang wird als z.B. Deine {=STABW.N(WENN(TEXT(A$5:A$3490;"JJJJMM")=TEXT(D5;"JJJJMM");B$5:B$3490))} macht es mE keinen Sinn diese weiter zu diskutieren. Nur soviel, AGGREGAT() käme in einer solchen Formel nicht mit seiner Funktion 7 und oder 8 zum Einsatz.
Gruß Werner
.. , - ...
Anzeige
Fkt 8 benötigt unbedingt INDEX, aber dann so, ...
24.05.2017 20:25:31
Luc:-?
…wie von mir gezeigt, Werner,
und das führt zu einer singularen MatrixFml, während Deine übliche Form mit Fkt 14/15 und INDEX sogar eine duale MatrixFml verursacht. Wenn das trotzdem keine MatrixFml wdn soll, müsste das Innenleben von Arg3 wohl ganz anders konstruiert wdn. Die beiden hier verwendeten Varianten taugen dazu jedenfalls nicht!
Das es aber auch in diesem Fall möglich gewesen wäre, AGGREGAT entsprd zu konstruieren, haben wir bereits diskutiert und ich mit AggregateXk hier wieder gezeigt… ;-)
Gruß, Luc :-?
AW: offensichtlich hast Du überlesen, ...
25.05.2017 07:32:18
...
Hallo Luc,
... dass ich schrieb: "AGGREGAT() käme in einer solchen Formel nicht mit seiner Funktion 7 und oder 8 zum Einsatz"
In der die Kombination AGGREGAT() mit Fkt 14/15 und INDEX ist auch keine duale Matrixformel notwendig.
Gruß Werner
.. , - ...
Anzeige
Habe das nicht überlesen, ...
25.05.2017 21:16:06
Luc:-?
…Werner,
aber evtl Du das: Wenn das trotzdem keine MatrixFml wdn soll, müsste das Innenleben von Arg3 wohl ganz anders konstruiert wdn. Die beiden hier verwendeten Varianten taugen dazu jedenfalls nicht!
Das gilt dann natürlich auch im Falle der Vermeidung einer dualen MatrixFml. Aber, wenn die von Dir anvisierte Fml* doppelt so lang wie die bisher längste wird, wäre das in der Tat keine sinnvolle Alternative, schon gar nicht zu einer Pivot-Lösung!
Übrigens, alle solchen Extras wie Pivot und komplette Analysen wdn auch durch spezielle Pgmm erzeugt, die VBA-Prozeduren vglbar oder gar selbst welche sind oder ursprünglich waren. Und leider ist bei MS wohl noch niemand auf die Idee gekommen, derartige Kreuztabellen zur Grundlage von Diagrammen zu machen, wodurch ein einfaches Umschalten genügen könnte. Andere Anbieter schon…!
* Interessiert hätte mich aber schon, wie Du mit INDEX und AGGREGAT-Fktt 14/15 in diesem Fall eine „Matrixfunktion(alität)sformel“ erreichen wolltest…
SchöVaTAhmd, Luc :-?
Anzeige
AW: meine Aussage ...
26.05.2017 07:33:03
...
Hallo Luc,
... war wohl nur mit meiner Aussage: "Allerdings muss dann sowohl INDEX() als auch AGGREGAT() anders eingesetzt werden" im vorangegangenen Beitrag und möglicherweise auch nur für mich verständlich.
Nachfolgend komme ich nun Deinem kleingedruckten ;-) Interesse nach, meinen entsprechenden Formelansatz aufzuzeigen. Dies allerdings nicht ohne den nochmaligen Hinweis, dass ich natürlich die PIVOTlösung klar favorisiere.
=STABW.N(INDEX(B:B;AGGREGAT(15;6;ZEILE(A$5:A$3500)/(A$5:A$3500=D5);1)))
Nun dürfte auch verständlicher sein, was ich mit "...anders eingesetzt werden..." meinte.
Wie ich eben feststellte, ist diese Formel zwar länger als Dein erster Formelvorschlag: {=STABW.N(WENN(TEXT(A$5:A$3490;"JJJJMM")=TEXT(D5;"JJJJMM");B$5:B$3490))} aber er berücksichtigt mE korrekt die Leerzelle in B3490 (d.h. diese wird nicht als 0 gewertet). Die Formel scheint auch schneller zu rechnen als die Matrixformel.
Gruß Werner
.. , - ...
Anzeige
Ja, Du hast hier AGGREGAT auf die von Dir ...
28.05.2017 01:51:20
Dir
…häufigst benutzte und mittlerweile allerseits gewohnte Art eingesetzt, Werner, ;-)
dazu aber tatsächlich ein anderes „Innenleben“ benutzt, wie man es schon häufig bei Dir u.a. gesehen hat, dass ich aber nicht son­derlich mag, weil es ganz bewusst jede Menge #DIV/0!-Fehler erzeugt, und an die ich deshalb idR nicht denke. Allerdings bleibt Deine Fml trotz des für Laien nur mühsam nach­zuvoll­ziehenden Aufbaus immer noch längenmäßig unter meinen INDEX+AGGRE­GAT/Ag­gre­gateXk-Fmln, sowohl den normalen als auch den MatrixFmln, die ich deshalb auch nicht favorisiert hätte.
Wenn man meine Fmln richtig an die Originaldaten anpasst, liefern sie auch alle die richtigen Ergebnisse, allerdings hatte ich ver­ges­sen, bei den AggregateXk-TEXT-NormalFmln das TextArgument von TEXT in Original­Notation zu schreiben ("MMYY"), was mir an meinem Demo-Bsp nicht auffiel, da es dort keine Auswirkungen hatte, bei den OriginalDaten aber schon.
Aber wie gesagt, die Entscheidung für eine Pivot-Tab dürfte hier allemal richtiger sein, wenn man nicht nur mal eben die Daten weni­ger Monate herausgreifen, sondern alle berechnen will/muss.
Falls es sich bei diesen Daten allerdings um Vorausberechnungen künftiger PreisEntwicklungen von Finanz-„Produkten“ handeln sollte, ist die ganze Rechnerei ohnehin sinnlos und führt unweigerlich in die nächste - größere - Katastrophe…
SchöSo, Luc :-?
Anzeige
AW: ich sehe es wie folgt ...
28.05.2017 14:36:55
...
Hallo Luc,
... landläufig wird ja oft gesagt und geschrieben "(man sollte) aus Fehlern lernen". Ich sage aber, man kann auch "aus Fehlern profitieren" und tue dies mit meinen VERWEIS(9;1/(...))- und oder eben vielen meiner AGGREGAT()-Matrixfunktion(alität)sformeln. Und dies auch dann, wenn diese keinen Profit abwerfen ;-)
Gruß Werner
.. , - ...
Sehr schön, ...
29.05.2017 00:53:26
Luc:-?
…Werner, ;-)
aber leider tatsächlich brotlos, während denen, die die dicksten und noch dazu für die Allgemeinheit kreuz­gefähr­lichen Fehler machen, Millionen hinterhergeworfen wdn. Aber das könnte bald quasi zum Selbstmord dieser Akteure, leider unter Mitnahme ihrer Geiseln, führen…
Schon Mandelbrot hat in den 60ern nachgewiesen, dass künftige FinanzEreignisse hyperbolisch verlaufen und eben nicht der vermuteten Gaußschen Glockenkurve folgen. Aber das hat natürlich mit den von Deiner Fml generierten Fehlern gar nichts zu tun, die schon deshalb aber wunderbar zu diesem zukünftige Ggwarten berechnen wollenden Schwachsinn passen würde… ;-]
Gruß, Luc :-?
Anzeige
AW: es geht alles viel einfacher, schneller und...
23.05.2017 17:12:16
...
Hallo Bernhard,
... und zudem ist das Ergebnis auch flexibler nutzbar (sortier- und filterbar).
Ich bin durch Luc in einen anderen thread auf diesen hier aufmerksam gemacht geworden.
Mit einer PIVOTauswertung musst Du lediglich geschätzte max. 2 Dutzend Mausklicks vornehmen. Das einzige kleine Manko dieser Lösung ist, dass Du diese mit einem (nur) Mausklick aktualisieren musst, wenn Deine Daten ergänzt und oder geändert werden.
Das Ergebnis (Teilauszug) (m)einer PIVOTauswertung Deiner Daten sieht danach z.B. wie unten stehend aus:
 KLM
5JahreDateStand_Abw
62016Dez0,050590555
7 Nov0,023496390
8 Okt0,087954116
9 Sep0,045828037
10 Aug0,014304931
11 Jul0,028542742
12 Jun0,020531913
13 Mai0,006604443
14 Apr0,018820783
15 Mrz0,016356220
16 Feb0,024111326
17 Jan0,016251463
182015Dez0,012789591
19 Nov0,013681103
20 Okt0,015277281
21 Sep0,018934030
22 Aug0,017641192
23 Jul0,022206210
24 Jun0,022119569
25 Mai0,008489654
26 Apr0,013602064
27 Mrz0,019155969
28 Feb0,014369520
29 Jan0,015896441
302014Dez0,008549471
31 Nov0,017866703
32 Okt0,018109972

Gruß Werner
.. , - ...
Anzeige
Ah ja, klar, Pivot - naheliegend! ;-) Gruß owT
23.05.2017 18:19:30
Luc:-?
:-?

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige