Office: Werteauswahlkriterium Sigma

Hallo zusammen, ich hoffe, dass Ihr mir helfen könnt:

Für eine Seminararbeit habe ich eine Zeitreihe in Bearbeitung.
Ich möchte nur für bestimmte Werte einen Durchschnitt bilden:

Es sollen aus der Reihe nur Werte kleiner +/- 2 Sigma gewählt werden.

Wie mache ich das in Excel?

 

Hallo!

Dein "Sigma" bedarf vielleicht noch etwas der Erläuterung...

Meinst du damit die Standardabweichung?
Und suchst du entgegen deiner Formulierung eigentlich den Mittelwert der Werte, deren Differenz zum Erwartungswert maximal die doppelte Standardabweichung beträgt, also Ausreißer nicht berücksichtigt?

 

Ok, hätte vielleicht den Sachverhalt mal angeben sollen =)

Hiermit hole ich das nach:

Es wurden zwei Zeitreihen miteinander verglichen.
Daraus berechnet wurde eine Elastizität (Quotient).
Alle drei Reihen werde in einem Diagramm dargestellt.

Dieser Elastizitätszeitreihe soll nun noch eine resultierende
Durchschnittskurve im Diagramm hinzugefügt werden.

O-Ton Prof.: Lassen Sie die Ausreisser größer als 2-Sigma raus und bilden
Sie für den Rest den Durchschnitt. Das Ergebnis wird eine lineare Gerade sein...

Das mit der Gerade usw ist klar, nur wie kann ich diese Eingrenzung der
zur Berechnung heranzuziehenden Werte, die kleiner als 2-Sigma sein sollen, in Excel realisieren?

Mehr Infos hab ich leider noch nicht...

 

Hallo!

Frag deinen Prof doch mal, was denn auch eine NICHT-lineare Gerade sein sollte... :-D :-D
Aber Gerade ist insofern klar, da es sich ja nur um einen einzigen Wert handelt.
Ich denke, es soll der Mittelwert der Elastizitäten ermittelt werden, oder?
Dann könntest du (je nachdem, wo deine Werte stehen) diese Formel probieren:

Code:

=MITTELWERT(WENN(ABS(A1:A20-MITTELWERT(A1:A20))<2*STABW(A1:A20);A1:A20))

Achtung Matrixformel: Die Eingabe der Formel mit der Tastenkombination Strg+Shift+Enter abschließen!

 

Ja, es soll ein Mittelwert der Elastizität ermittelt werden.

Da aber in einigen wenigen Zeitpunkten sehr sehr starke Ausreisser vorhanden sind, würden diese den Durchschnitt zu stark verzerren.

Daher sollen diese Werte über die 2-Sigma Restriktion ausgegrenzt werden...

€: Soweit so gut, mit der Formel, aber grad das nächste Problem:

Wie ich bemerken muss, ergibt sich für die Berechnung der Elastizität in einer Periode durch Rundungsdifferenzen (nur zwei Nachkommastellen), entgegen der Realität, in diesem Falle "#Div/0!".
Somit wirft mir die Formel gleichzeitig "#Div/0!" aus.
Wie kann ich einen einzelnen speziellen Wert ausschliessen, bei Berechnungen?

 

Uuups, du hast ja noch was drangehängt...
Dann versuch's mit einem weiteren Fehlerabfang:

Code:

=MITTELWERT(WENN(ISTZAHL(A1:A20);WENN(ABS(A1:A20-MITTELWERT(A1:A20))<2*STABW(A1:A20);A1:A20)))

 

Kommt immernoch "#Div/0!"

Wenn ich Werte bis zu der einen "Fehler-Zelle" nehme, kommt ein Wert raus,
sobald die Zelle dabei ist, kommt nur noch "#Div/0!"

 

Ja, stimmt...
Da müssen noch mehr Fehlerabfänge rein:

Code:

=MITTELWERT(WENN(ISTZAHL(A1:A20);WENN(ABS(A1:A20-MITTELWERT(WENN(ISTZAHL(A1:A20);A1:A20)))<2*STABW(WENN(ISTZAHL(A1:A20);A1:A20));A1:A20)))

 

Super, da kommt jetzt was raus =)

Ich geh davon aus, dass es so nun stimmt.
Muss mir nur die Formel mal auseinanderbröseln um zu verstehen, wie gerechnet wird ;)

 

Ich grab den Thread nochmal aus, da ich diesbezüglich nochmal Hilfe brauche:

Diesmal geht es um die Funktion "Steigung"

Excel soll mir aus den Zeitreihen B3:B102 und E3:E102 die Steigung (Regressionskoeffizient) berechnen, aber nur, wenn obige 2sigma-Bedingung eingehalten wird.

Es soll also eine Regression ohne Ausreisser nach der Definition 2sigma durchgeführt werden. Dies soll für abhängige und unabhängige Variable, also für beide Zeitreihen gelten.

Ich habe versucht, obige Matrixformel anzupassen, komme aber mit der zweiten Zeitreihe nicht klar, wie ich diese einbauen muss.
Ich glaube aber, dass es nur eine Unwesentliche Modifikation der Gleichung ist:

=Steigung(WENN(ISTZAHL(B3:B102);WENN(ABS(B3:B102-MITTELWERT(WENN(ISTZAHL(B3:B102);B3:B102)))<2*STABW(WENN(ISTZAHL(B3:B102);B3:B102));B3:B102)))

Die Formel Steigung sieht normal so aus:

=Steigung(B3:B102;E3:E102)

Für eine Hilfe in dieser Sache wäre ich mehr als dankbar!!!

 

Hallo!

Ich hab's zwar nicht getestet, aber theoretisch müsste es reichen, wenn man bei der zweiten Reihe nur den hintersten Bezug anpasst:

=STEIGUNG(WENN(ISTZAHL(B3:B102);WENN(ABS(B3:B102-MITTELWERT(WENN(ISTZAHL(B3:B102);B3:B102)))<2*STABW(WENN(ISTZAHL(B3:B102);B3:B102));B3:B102));WENN(ISTZAHL(B3:B102);WENN(ABS(B3:B102-MITTELWERT(WENN(ISTZAHL(B3:B102);B3:B102)))<2*STABW(WENN(ISTZAHL(B3:B102);B3:B102));E3:E102)))

 

Werden so dann Ausreisser (Abweichung größer 2sigma) in beiden Reihen ausgelassen?

€: Ich hab zur Kontrolle mal die Originaldaten inkl. Ausreisser durch SPSS laufen lassen. Normalerweise sollten die Ausreisser dort rausgenommen werden (denk ich einfach mal), aber der Regr. Koeffizient ist der selbe, wie in Excel mit Originaldaten vor der 2sigma-Bereinigung *grübel*

 

Hallo!

Wieso Ausreißer aus BEIDEN Reihen?
Es werden die Ausreißer aus Spalte B berücksichtigt.
In Spalte E stehen deine x-Werte und für diese gibt es doch keine Standardabweichung !?
SPSS besitze ich nicht und leider bin ich auch kein Stochastiker. :-(

 

Danke erstmal für die Hilfe Deinerseits, hab ich eben vergessen


Ich hab mich nicht gut genug ausgedrückt, mein Fehler!
Hier nochmal der etwas vollständigere Sachverhalt.
Bei Fragen: einfach melden!!!

Beide Reihen sind Daten vom stat. Bundesamt, also Zeitreihen.
Beide Reihen habe also eine feste Standardabweichung, welche ja nach Deiner Formel bei der B-Reihe (Y-Werte) zur Bereinigung genutzt wird.

Das selbe müsste für die E-Reihe (X-Werte) auch nochmal passieren und anschließend soll aus diesen Reihen eine Regression gerechnet werden (Steigungs-Funktion).

Ich stoße hier total an meine Grenzen...sorry für die Umstände

 

Hallo!

Ich leider auch...

Ich würde vorschlagen, du ermittelst in 2 Hilfsspalten eine Abfrage für die infrage kommenden Werte. Mit dem Autofilter kannst du dir dann diese Werte anzeigen lassen.
Diese kopierst du dann z.B. in ein anderes Blatt und hast so eine bereinigte Liste, mit der du dann vernünftig arbeiten kannst.