Bereiche /"Indirekt"-Funktion / Vektoren

Discussion:

(zu alt für eine Antwort)

Thomas Neukirch

2004-12-20 15:35:04 UTC

Hallo,

ich möchte möglichst effizient und zentral Bereiche (wie etwa B5:B70)
festlegen, deren Inhalt dann von einer Reihe (statistischer) Funktionen
ausgewertet wird.
Die Idee dabei ist, nicht die Bereiche (Argumente) in jeder einzelnen
statistischen Funktion eingeben zu müssen, sondern an genau einer Stelle, von
der aus sie dann an die stat. Funktionen übertragen werden. Ich habe dies wie
folgt implementiert:

Daten (Zeitreihe) im Bereich C28:C87

In Zelle C90: 28 (Anfangszeilen-Nummer der Zeitreihe)
In Zelle C91: 87 (Endzeilen-Nummer der Zeitreihe)

In Zelle C95 wird dann der Mittelwert berechnet:

=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))

In Zelle C97 die Standardabweichung:

=STABW(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))

usw.

(Mit der automat. Ausfüll-Option kann das ganze dann in andere Spalten
übertragen werden, ferner mit Copy+Paste an andere Stellen)

Meine FRAGE:

Was mache ich mit Funktionen wie der Korrelation, von der ich
KORREL(C29:C87;C28:C86) ausgewertet haben möchte?

Hier funktioniert die Verwendenung der Indirekt-Funktion nicht mehr (oder
ich weiss nicht wie...), da die Korrel-Funktion als Argument 2 Vektoren
erwartet.

Wer weiss hier Rat (oder eine andere elegantere Lösung)?

Gruss und danke

Thomas

Bernd Plumhoff

2004-12-20 15:51:01 UTC

Permalink

Hallo Thomas,

vielleicht hilft Dir http://www.whooper.co.uk/excelstuff.htm weiter. Ich
denke, Du verwendest einfach zwei oder mehr Indirekt() Aufrufe.

Viele Grüße,
Bernd

Thomas Neukirch

2004-12-20 16:31:07 UTC

Permalink

Hallo Bernd,

danke für den Link. Wenn ich korrel(indirekt(...)) eingebe, bekomme ich eine
Fehlermeldung wegen zu weniger Argumente (korrekt: korrel(vektor1;vektor2)).
Die Version korrel(indirekt(...);indirekt(...)) mag Excel nicht, da es
anstatt der Bereichsangaben offenbar Vektoren erwartet hat.

Gruss

Thomas

Post by Bernd Plumhoff
Hallo Thomas,
vielleicht hilft Dir http://www.whooper.co.uk/excelstuff.htm weiter. Ich
denke, Du verwendest einfach zwei oder mehr Indirekt() Aufrufe.
Viele Grüße,
Bernd

Frank Kabel

2004-12-20 16:46:12 UTC

Permalink

Hi Thomas
poste doch mal die komplette Formel
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Thomas Neukirch
Hallo Bernd,
danke für den Link. Wenn ich korrel(indirekt(...)) eingebe, bekomme
korrel(vektor1;vektor2)). Die Version
korrel(indirekt(...);indirekt(...)) mag Excel nicht, da es anstatt
der Bereichsangaben offenbar Vektoren erwartet hat.
Gruss
Thomas

Post by Bernd Plumhoff
Hallo Thomas,
vielleicht hilft Dir http://www.whooper.co.uk/excelstuff.htm weiter.
Ich denke, Du verwendest einfach zwei oder mehr Indirekt() Aufrufe.
Viele Grüße,
Bernd

Thomas Neukirch

2004-12-20 17:27:06 UTC

Permalink

Hi,

@Frank:

die Formel für Zugriff sieht "normalerweise" (bei einem Argument) so aus
(Bsp):

Daten (Zeitreihe) im Bereich C28:C87

In Zelle C90: 28 (Anfangszeilen-Nummer der Zeitreihe)
In Zelle C91: 87 (Endzeilen-Nummer der Zeitreihe)

In Zelle C95 wird dann der Mittelwert berechnet:

=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))

Wenn ich nun die sogenannte Autokorrelation der Datenreihe (das ist die
Korrelation der Datenreihe mit ihrem verzögertem Analogon, also
korrel(c29:c87;c28:c86)) berechnen möchte, so müsste ich etwas wie

=korrel(INDIREKT(ADRESSE(C90+1;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4));INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91-1;SPALTE(C95);4)))

eingeben, aber das funktioniert NICHT.

@Christian:

Ich nutze keine "absoluten" Bereiche, weil ich die Funktionalität per "Nach
rechts ziehen" bzw. Copy+Paste auf andere Spalten / Orte übertragen möchte
und häufig auch unterschiedlich lange Datenreihen verwende.

Gruss und danke

Thomas

Gruss

Thomas

Post by Frank Kabel
Hi Thomas
poste doch mal die komplette Formel
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Bernd Plumhoff
Hallo Thomas,
vielleicht hilft Dir http://www.whooper.co.uk/excelstuff.htm weiter.
Ich denke, Du verwendest einfach zwei oder mehr Indirekt() Aufrufe.
Viele Grüße,
Bernd

Frank Kabel

2004-12-20 17:44:22 UTC

Permalink

Hi Thomas
nun ist alles klar :-)
Das muss man wissen :-) Die Funktionen SPALTE (und ZEILE) geben keinen Wert
sondern eine eins/eins Matrix (also z.B: {5} und nicht 5) zurück. Das führt
z.B.: dazu, dass innerhalb Deiner INDIREKT Funktion dann folgendes steht
...INDIREKT({"C3:C28"})...
und nicht
...INDIREKT("C3:C28")...
Du kannst das selber mit F9 in der Formelzeile ausprobieren.

Es gibt aber auch eine ganz einfach Lösung. Umschließe SPALTE/ZEILE mit
SUMME, um aus der Matrix einen Wert zu machen. Also in Deinem Fall:
=korrel(INDIREKT(ADRESSE(C90+1;SUMME(SPALTE(C95));4)&":"&ADRESSE(C91;SUMME(SPALTE(C95));4));INDIREKT(ADRESSE(C90;SUMME(SPALTE(C95));4)&":"&ADRESSE(C91-1;SUMME(SPALTE(C95));4)))
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Thomas Neukirch
Hi,
die Formel für Zugriff sieht "normalerweise" (bei einem Argument) so aus
Daten (Zeitreihe) im Bereich C28:C87
In Zelle C90: 28 (Anfangszeilen-Nummer der Zeitreihe)
In Zelle C91: 87 (Endzeilen-Nummer der Zeitreihe)
=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))
Wenn ich nun die sogenannte Autokorrelation der Datenreihe (das ist die
Korrelation der Datenreihe mit ihrem verzögertem Analogon, also
korrel(c29:c87;c28:c86)) berechnen möchte, so müsste ich etwas wie
=korrel(INDIREKT(ADRESSE(C90+1;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4));INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91-1;SPALTE(C95);4)))

Thomas Neukirch

2004-12-21 08:21:04 UTC

Permalink

Hi Frank,

erstens danke, es funktioniert. Zweitens würde ich aber doch gerne die Logik
dahinter verstehen. Denn im Falle von einem Argument, z.B. bei
=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))
funktioniert der selbe Aufruf.
Wieso?

Gruss und danke

Thomas

Post by Frank Kabel
Hi Thomas
nun ist alles klar :-)
Das muss man wissen :-) Die Funktionen SPALTE (und ZEILE) geben keinen Wert
sondern eine eins/eins Matrix (also z.B: {5} und nicht 5) zurück. Das führt
z.B.: dazu, dass innerhalb Deiner INDIREKT Funktion dann folgendes steht
....INDIREKT({"C3:C28"})...
und nicht
....INDIREKT("C3:C28")...
Du kannst das selber mit F9 in der Formelzeile ausprobieren.
Es gibt aber auch eine ganz einfach Lösung. Umschließe SPALTE/ZEILE mit
=korrel(INDIREKT(ADRESSE(C90+1;SUMME(SPALTE(C95));4)&":"&ADRESSE(C91;SUMME(SPALTE(C95));4));INDIREKT(ADRESSE(C90;SUMME(SPALTE(C95));4)&":"&ADRESSE(C91-1;SUMME(SPALTE(C95));4)))
--
Regards
Frank Kabel
Frankfurt, Germany

Frank Kabel

2004-12-21 09:53:02 UTC

Permalink

Hi Thomas
das liegt nicht daran, das Du nur einen Parameter hast, sondern das
MITTELWERT (im Gegensatz zu Correl) auch eine Matrix als Ergebnis akzeptiert.
Die Logik dahinter versteht wohl nur Microsoft :-)

Post by Thomas Neukirch
Hi Frank,
erstens danke, es funktioniert. Zweitens würde ich aber doch gerne die Logik
dahinter verstehen. Denn im Falle von einem Argument, z.B. bei
=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))
funktioniert der selbe Aufruf.
Wieso?
Gruss und danke
Thomas

Thomas Neukirch

2004-12-21 13:53:03 UTC

Permalink

Hi Frank,

aha! Wo kann ich denn (ausser hier im Forum) sowas - in komprimierter
effizienter Form - erfahren? Und: Wenn ich mir die Formel so anschaue, so
wirkt sie doch recht unelegant. Könnte ich das Problem auch anders angehen?

Gruss und danke

Thomas

Post by Frank Kabel
Hi Thomas
das liegt nicht daran, das Du nur einen Parameter hast, sondern das
MITTELWERT (im Gegensatz zu Correl) auch eine Matrix als Ergebnis akzeptiert.
Die Logik dahinter versteht wohl nur Microsoft :-)

Frank Kabel

2004-12-21 13:57:35 UTC

Permalink

Hi
ich würde das wahrscheinlich eher mit BEREICH.VERSCHIEBEN lösen. Das sollte
die Formel vereinfachen.

Re: Woher bekomme ich solche Inofs: Da ist dieses Forum tatsächlich die
beste Quelle (plus die englischen Foren)
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Thomas Neukirch
Hi Frank,
aha! Wo kann ich denn (ausser hier im Forum) sowas - in komprimierter
effizienter Form - erfahren? Und: Wenn ich mir die Formel so
anschaue, so wirkt sie doch recht unelegant. Könnte ich das Problem
auch anders angehen?
Gruss und danke
Thomas

Post by Frank Kabel
Hi Thomas
das liegt nicht daran, das Du nur einen Parameter hast, sondern das
MITTELWERT (im Gegensatz zu Correl) auch eine Matrix als Ergebnis
akzeptiert. Die Logik dahinter versteht wohl nur Microsoft :-)

Post by Thomas Neukirch
Hi Frank,
erstens danke, es funktioniert. Zweitens würde ich aber doch gerne
die Logik dahinter verstehen. Denn im Falle von einem Argument,
z.B. bei
=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))
funktioniert der selbe Aufruf.
Wieso?
Gruss und danke
Thomas

Thomas Neukirch

2004-12-21 16:27:02 UTC

Permalink

Ja,

das hab ich auch schon anvisiert. Aber mich stört, dass ich die Bereiche
immer so umständlich umdefinieren muss. Und: Kann ich so einen Bereich auch
reduzieren (für die korrel-Berechnung)?

Gruss

Thomas

Post by Frank Kabel
Hi
ich würde das wahrscheinlich eher mit BEREICH.VERSCHIEBEN lösen. Das sollte
die Formel vereinfachen.
Re: Woher bekomme ich solche Inofs: Da ist dieses Forum tatsächlich die
beste Quelle (plus die englischen Foren)
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Frank Kabel
Hi Thomas
das liegt nicht daran, das Du nur einen Parameter hast, sondern das
MITTELWERT (im Gegensatz zu Correl) auch eine Matrix als Ergebnis
akzeptiert. Die Logik dahinter versteht wohl nur Microsoft :-)

Post by Thomas Neukirch
Hi Frank,
erstens danke, es funktioniert. Zweitens würde ich aber doch gerne
die Logik dahinter verstehen. Denn im Falle von einem Argument,
z.B. bei
=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))
funktioniert der selbe Aufruf.
Wieso?
Gruss und danke
Thomas

Frank Kabel

2004-12-21 16:40:29 UTC

Permalink

Hi
z.B:
BEREICH.VERSCHIEBEN(C$1,C$90-1,0,C$91-C$90+1)
als ein Parameter Deiner Korrel Funktion
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Thomas Neukirch
Ja,
das hab ich auch schon anvisiert. Aber mich stört, dass ich die Bereiche
immer so umständlich umdefinieren muss. Und: Kann ich so einen Bereich auch
reduzieren (für die korrel-Berechnung)?
Gruss
Thomas

Post by Frank Kabel
Hi Thomas
das liegt nicht daran, das Du nur einen Parameter hast, sondern das
MITTELWERT (im Gegensatz zu Correl) auch eine Matrix als Ergebnis
akzeptiert. Die Logik dahinter versteht wohl nur Microsoft :-)

Post by Thomas Neukirch
Hi Frank,
erstens danke, es funktioniert. Zweitens würde ich aber doch gerne
die Logik dahinter verstehen. Denn im Falle von einem Argument,
z.B. bei
=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))
funktioniert der selbe Aufruf.
Wieso?
Gruss und danke
Thomas

Thomas Neukirch

2004-12-23 09:41:01 UTC

Permalink

Danke Frank,

werd' ich ausprobieren.

Kann ich die Bereiche auch durch Eingaben in Zellen definieren oder ist das
nur "manuell" durch Auswahl des Bereiches und Eingabe eines Namens im
Namensfeld links oben im Excel zu bewerkstelligen?

Gruss und danke

Thomas

Post by Frank Kabel
Hi
BEREICH.VERSCHIEBEN(C$1,C$90-1,0,C$91-C$90+1)
als ein Parameter Deiner Korrel Funktion
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Frank Kabel
Hi Thomas
das liegt nicht daran, das Du nur einen Parameter hast, sondern das
MITTELWERT (im Gegensatz zu Correl) auch eine Matrix als Ergebnis
akzeptiert. Die Logik dahinter versteht wohl nur Microsoft :-)

Post by Thomas Neukirch
Hi Frank,
erstens danke, es funktioniert. Zweitens würde ich aber doch gerne
die Logik dahinter verstehen. Denn im Falle von einem Argument,
z.B. bei
=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))
funktioniert der selbe Aufruf.
Wieso?
Gruss und danke
Thomas

Frank Kabel

2004-12-23 14:44:18 UTC

Permalink

Hi
wenn ein Name z.B.: in Zelle A1 steht, kannst du mit
=INDIREKT(A1)
auch darauf zugreifen
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Thomas Neukirch
Danke Frank,
werd' ich ausprobieren.
Kann ich die Bereiche auch durch Eingaben in Zellen definieren oder
ist das nur "manuell" durch Auswahl des Bereiches und Eingabe eines
Namens im Namensfeld links oben im Excel zu bewerkstelligen?
Gruss und danke
Thomas

Post by Frank Kabel
Hi
BEREICH.VERSCHIEBEN(C$1,C$90-1,0,C$91-C$90+1)
als ein Parameter Deiner Korrel Funktion
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Thomas Neukirch
Ja,
das hab ich auch schon anvisiert. Aber mich stört, dass ich die
Bereiche immer so umständlich umdefinieren muss. Und: Kann ich so
einen Bereich auch
reduzieren (für die korrel-Berechnung)?
Gruss
Thomas

Post by Frank Kabel
Hi
ich würde das wahrscheinlich eher mit BEREICH.VERSCHIEBEN lösen. Das sollte
die Formel vereinfachen.
Re: Woher bekomme ich solche Inofs: Da ist dieses Forum
tatsächlich die beste Quelle (plus die englischen Foren)
--
Regards
Frank Kabel
Frankfurt, Germany

Post by Thomas Neukirch
Hi Frank,
aha! Wo kann ich denn (ausser hier im Forum) sowas - in
komprimierter effizienter Form - erfahren? Und: Wenn ich mir die
Formel so anschaue, so wirkt sie doch recht unelegant. Könnte ich
das Problem auch anders angehen?
Gruss und danke
Thomas

Post by Frank Kabel
Hi Thomas
das liegt nicht daran, das Du nur einen Parameter hast, sondern
das MITTELWERT (im Gegensatz zu Correl) auch eine Matrix als
Ergebnis akzeptiert. Die Logik dahinter versteht wohl nur
Microsoft :-)

Post by Thomas Neukirch
Hi Frank,
erstens danke, es funktioniert. Zweitens würde ich aber doch
gerne die Logik dahinter verstehen. Denn im Falle von einem
Argument, z.B. bei
=MITTELWERT(INDIREKT(ADRESSE(C90;SPALTE(C95);4)&":"&ADRESSE(C91;SPALTE(C95);4)))
funktioniert der selbe Aufruf.
Wieso?
Gruss und danke
Thomas

Christian Wisspeintner

2004-12-20 15:57:08 UTC

Permalink

Hallo Thomas,

Post by Thomas Neukirch
Hallo,
ich möchte möglichst effizient und zentral Bereiche (wie etwa B5:B70)
festlegen, deren Inhalt dann von einer Reihe (statistischer) Funktionen
ausgewertet wird.

Wenn Du diese Bereiche nicht ständig ändern musst, kölnntest Du für die
Bereiche Namen vergeben (Einfügen --> Name --> Definieren). Du kannst dann
unter "Bezieht sich auf" Deinen Bereich eingeben und dafür dann einen Namen
vergeben, zB "Bereich1".

Dieser Name läßt sich dann in Formeln verwenden, zB =Mittelwert(Bereich1)

Gruss
Christian