Excel: Dynamische Range für Korrelationsanalyse

[Kalusha9]

Hallo zusammen,

ich möchte mit Hilfe der Excel-Funktion KORREL(Matrix1;Matrix2) den Korrelationskoeffizient von zwei Datenreihen über verschiedene Zeiträume berechnen, einmal über die vergangenen 30 Werktage und einmal über die vergangenen 90 Werktage. Meine Range beginnt also heute und geht dann jeweils 30 bzw. 90 Werktage zurück.

Bei der Berechnung der dynamischen Range (=Matrix) benötige ich jetzt Hilfe bei der Erstellung der Formel.

Meine Daten liegen jewel wie folgt vor:
Tabelle1: Datum | Wert
Tabelle2: Datum | Wert

Meine Idee ist jetzt, die dynamische Range mit Hilfe der Index-Funktion (und ggf. der Indirekt-Funktion?) zu ermitteln.

Die nachfolgende Funktion liefert mir den letzten Datensatz aus Tabelle1:
=INDEX(fCorrelations!K:K;ANZAHL2(fCorrelations!G:G))

Die nachfolgende Funktion liefert mir den Datensatz von vor 15 Tagen aus Tabelle1:
=INDEX(fCorrelations!K:K;ANZAHL2(fCorrelations!G:G)-Date!H1)

Was ich jetzt als Rückgabe benötige sind nicht die jeweilgen Werte aus Tabelle1, sondern die Zellbezüge damit ich damit eine Range zum einsetzen in die Korrel-Funktion bauen kann.

Kann hier jemand helfen ?

Danke.

 

[deineMudda]

Warum benötigst du einen dynamischen Bereich? Benutze doch einfach 2 Zellen einmal mit der 30 Tage und einmal mit der 90 Tage Formel.

 

[Kalusha9]

@deineMudda

Ich benötige den dynamischen Bereich, damit ich gegebenfalls die Range verändern kann. Aber ob das jetzt 30 bzw. 90 Tage sind spielt auch weniger eine Rolle.

Mein Problem ist vielmehr, dass meine Ausgangstabelle jeden Tag um einen Datensatz erweitert wird, der Bereich also gewissermaßen rollierend ist. Hierzu finde ich mit oben genannter Formel den letzten Eintrag der Tabelle und zähle dann von dort 30/90 Einträge rückwärts. Sowohl den letzten also auch den Eintrag 30/90 Tage vorher finde ich, aber ich benötige nicht den Wert, sondern ich benötige als Rückgabe die Zelle des Wertes, damit ich damit die Range (z.B. A265:A295) konstruieren kann. Diese Range möchte ich dann in die Korrel-Funktion einsetzen.

 

[Scientist]

Den Index eines gesuchten Wertes innerhalb einer Matrix findest du mit Vergleich().
Wenn du den Offset durch die Position der Matrix dazu addiert, erhaelst du die Zeilennummer.
Mit Indirekt() kannst du dir anschließend die variablen Bezug zusammenbasteln, den du fuer die Korrelation benoetigst.

Siehe Beispiel im Anhang.

 

[Kalusha9]

@Scientist

Vielen Dank, das geht schon einmal in die richtige Richtung.

Jetzt habe ich noch das "Problem", dass die Daten in einem separaten Tabellenblatt stehen. Ich muss also nicht nur variabel auf die Zelle referenzieren, sondern auch den Namen des Tabellenblatts mitnehmen.

Falls du dazu noch eine idee hast, lass es mich gerne wissen. Ansonsten probiere ich mit deinem Beispiel selber einmal rum.

Vielen Dank.

 

[Scientist]

Wenn der Blattname bekannt und fest ist, einfach in der Indirekt Funktion als Text ergaenzen (Ausrufezeichen und ggf. Hochkomma nicht vergessen).
Wenn der Blattname unbekannt ist, aber immer an derselben Stelle, wird es etwas komplizierter.

 

[Kalusha9]

@Scientist

Mit dieser Formel habe ich jetzt das gewünschte Ergebnis erreicht:

=KORREL(INDIREKT("fCorrelations!E"&ZELLE("zeile";INDEX(fCorrelations!$E:$E;ANZAHL2(fCorrelations!$A:$A)-Date!$H$1))):INDIREKT("fCorrelations!E"&ZELLE("zeile";INDEX(fCorrelations!$E:$E;ANZAHL2(fCorrelations!$A:$A))));INDIREKT("fCorrelations!CE"&ZELLE("zeile";INDEX(fCorrelations!$CE:$CE;ANZAHL2(fCorrelations!$CA:$CA)-Date!$H$1))):INDIREKT("fCorrelations!CE"&ZELLE("zeile";INDEX(fCorrelations!$CE:$CE;ANZAHL2(fCorrelations!$CA:$CA)))))

Vielen Dank dir für deine Hilfe

Jetzt habe ich noch eine weitere Idee die ich gerne in Excel umsetzen würde, bei der du mir vielleicht ebenfalls weiterhelfen kannst:

Ich habe wieder eine einfache Tabelle mit Daten (Datum, Wert). Diese Tabelle wird täglich um einen neuen Eintrag für den jeweils aktuellen Tag ergänzt. Ich möchte jetzt a.) täglich den rollierenden Durchschnitt der letzten 20 Tage berechnen (einfache Teil, bereits umgesetzt) und b.) mir den Maximalwert des rollierenden Durchschnitts der vergangenen 20 Tage ausgeben.

Als Beispiel:
Der rollierende Durchschnitt ist heute 100, gestern war dieser 94, am Tag davor 91 und wiederum einen Tag vorher 103. Ich möchte jetzt, das mir die rollierende Durchschnitte der letzten 20 Tage berechnet werden und ich von diesen 20 Werte den Maximalwert ausgworfen bekomme. Und das alles wenn möglich ohne Hilfsspalte, d.h. alles in einer Formel.

Hast du eine Idee ?

 

[Scientist]

• Alles in eine Zelle werfen -> (nervig zu erstellen/bearbeiten, hohe Fehlerwahrscheinlichkeit -> schlechte Idee)
• Hilfszellen verwenden und ausblenden
• eigene Funktion in VBA erstellen