Excel statistik & tests & regression

Anwendung statistischer Verfahrenin der Liegenschaftsbewertung

Theoretische Grundlagen und Anwendung mit Microsoft Excel

Mag. Michael Mayr - Statistiker

Agenda

Excel-Grundlagen Statistische Kennzahlen Konfidenzintervalle

Exc

el-

Bei

spie

le

Excel-Grundlagen

Excel Hilfe Arbeiten mit Namen Arbeiten mit Funktionen Analyse-Funktionen

Excel-Grundlagen

Excel Hilfe Aufruf

F1 oder Menüpunkt „?“ Register

Inhalt Antwort-Assistent Index

VERWENDEN SIE DIE HILFE!!!

Excel-Grundlagen

Excel Hilfe

Excel-Grundlagen

Excel Hilfe

„Statistische Funktionen“

Excel-Grundlagen

Arbeiten mit Namen

Namen können in Formeln anstelle von Bezügen (zB „Preis“ für Bereich A1:A8) verwendet werden

Namen verwenden absolute Bezüge Namen zuweisen erfolgt durch

Menü: Einfügen/Name/... Erfassen im Namenfeld

Excel-Grundlagen

Arbeiten mit Namen

Namenfeld

Bereich

Funktionen erfassen mit Funktionsassitenten

- Menüpunkt Einfügen/Funktion

- Schaltfläche

Direkt in Zellen „=MITTELWERT(...)“

Verwenden Sie Namen, weil dadurch die Lesbarkeit von Funktionen verbessert wird!

Excel-Grundlagen

Arbeiten mit Funktionen

Excel-Grundlagen

Arbeiten mit Funktionen

1. Funktions-assistent aktivieren

2. Funktion wählen

3. Werte erfassen

4. Ergebnis

Excel-Grundlagen

Analyse-Funktionen

Installation: Extras / Add-Ins-Manager... Analyse-Funktionen auswählen

Analyse-Funktion auswählen

Excel-Grundlagen

Analyse-Funktionen

Aufruf Extras / Analyse-Funktionen

Analyse-Funktion wählen OK

Excel-Grundlagen

Analyse-Funktionen Vorteile:

zusätzliche statistische Funktionen Berechnung mehrerer Ergebnisse

gleichzeitig Übersichtliche zum Teil grafische

Darstellung der Ergebnisse Nachteile

Müssen bei Veränderung der Daten neu ausgeführt werden!

Excel-Grundlagen

Analyse-Funktionen

Agenda

Excel-Grundlagen Statistische Kennzahlen Konfidenzintervalle Statistische Tests

Statistische Kennzahlen

Einfache Kennzahlen Lagekennzahlen Streuungskennzahlen

Statistische Kennzahlen

Einfache Kennzahlen

Anzahl Excel-Funktion: ANZAHL (Wert1;Wert2; ...)

Anzahl leere Zellen Excel-Funktion: ANZAHLLEEREZELEN()

Summe Excel-Funktion: SUMME (Zahl1;Zahl2; ...)

Statistische Kennzahlen

Einfache Kennzahlen Minimum / Maximum

Excel-Funktionen:- MIN(Zahl1;Zahl2; ...)

- MAX(Zahl1;Zahl2; ...)

k-kleinster / k-größter Wert Excel-Funktionen:

- KKLEINSTE(Matrix;k)

- KGRÖSSTE(Matrix;k) k=1 Minimum, k=n Maximum

Statistische Kennzahlen

Lage - Mittelwert Formel Mittelwert – arithmetisches Mittel

bzw.

Eigenschaften: Summe der Abweichungen vom Mittelwert ist 0 Summe der quadratischen Abweichungen vom

Mittelwert ist minimal Sensibel bei Ausreißern! Meist verwendete Lagekennzahl

Excel-Funktion: MITTELWERT

n

1iix

n

1x n21 xxx

n

1x

Statistische Kennzahlen

Lage - gewichteter Mittelwert Formel gewichteter Mittelwert

Eigenschaften: Unterschiedlich hohe Gewichtung der

Einzelwerte Alle Gewichte 1/n arithmetisches Mittel

Excel-Funktion: SUMME der gewichteten Werte

) 1w ( bei xwxn

1ii

n

1iii

w

Formel geometrisches Mittel:

Eigenschaften: Verwendung bei Werten, die eine „relative

Veränderung darstellen“ Sinnvoll bei Wachstumsraten

Excel-Funktion: GEOMITTEL

Statistische Kennzahlen

Lage - geometrisches Mittel

n32n

1 xxxxG

Formel Median – Zentralwert: Ordnet man die Werte nach der Größe, so gilt:

n ungerade / n gerade

Eigenschaften: 50% der Beobachtungswerte sind kleiner oder gleich

groß wie der Median Nicht sensibel bei Ausreißern Auch bei Ordnungszahlen verwendbar

Excel-Funktion: MEDIAN

Statistische Kennzahlen

Lage - Median

2

1nxx~

12

n

2

n xx2

1x~

α-Quantile ( 0 < α < 1 )

Eigenschaften: 100*α% der Beobachtungswerte sind kleiner oder

gleich groß wie das α-Quantil Auch bei Ordnungszahlen verwendbar Sonderformen: Quartile (25%, 50%, 75%)

α=0,25 1. Quartil / α=0,5 Median / α=0,75 3. Quartil

Excel-Funktionen: QUANTIL / QUARTILE (QUANTILSRANG)

Statistische Kennzahlen

Lage - α-Quantil (Quartil)

99,095,090,075,050,025,010,005,001,0 x~ /x~ /x~ /x~ /x~ /x~ /x~ /x~ /x~

Modalwert – Modus Häufigster Wert Excel-Funktion: MODALWERT

Gestutztes Mittel Mittelwert ohne Werte an den Rändern Variante zur Eliminierung von Ausreißern Excel-Funktion: GESTUTZTMITTEL

Statistische Kennzahlen

Lage - weitere Kennzahlen

Formel Varianz:

bzw. Eigenschaften:

Am häufigsten verwendetes Streuungsmaß Quadratisches Abstandsmaß

Excel-Funktion: VARIANZ, VARIANZEN

Statistische Kennzahlen

Streuung - Varianz

2n

1ii

2 xx1-n

1s

2n

1ii

2 xn

1

Formel Standardabweichung:bzw.

Eigenschaften: Hat im Unterschied zur Varianz die gleiche

Dimension wie die Beobachtungswerte!

Excel-Funktion: STABW, STABWN

Statistische Kennzahlen

Streuung - Standardabweichung

2ss 2

Formel Variationskoeffizient: Eigenschaften:

Verhältnis von Standardabweichung und Mittelwert

kann ohne Mittelwert interpretiert werden (Standardabweichung hängt von Größe der beobachteten Werte ab!)

Excel-Funktion: STABW / MITTELWERT

Statistische Kennzahlen

Streuung - Variationskoeffizient

x

s V

Spannweite (Range R) Abstand zwischen größtem und kleinsten

Wert R = Maximum – Minimum Sensibel bei Ausreißern!

Quartilsabstand Abstand zwischen dem 1. und dem 3.

Quartil 3. Quartil – 1. Quartil (50%)

Statistische Kennzahlen

Streuung – weitere Kennzahlen

Statistische Kennzahlen

Hilfe – Statistische Funktionen

Statistische Funktionen

Statistische Kennzahlen

Analyse-Funktionen Populations-

kenngrößen Rang und Quantil

Agenda

Excel-GrundlagenStatistische Kennzahlen Konfidenzintervalle

Konfidenzintervalle

Konfidenzintervalle Ausreißer

Ausgangssituation Schätzung des unbekannten Parameters

der Grundgesamtheit mittels Stichprobe Schätzwert (zB Mittelwert) liegt meist in der

Nähe des wahren Wertes Ermittlung eines Bereiches, in dem der

unbekannte Parameter mit vorgegebener Sicherheit liegt, anstatt des einzelnen Wertes bzw. Schätzers

Konfidenzintervalle

Konfidenzintervall allgemein

1 - α Konfidenzintervall Irrtumswahrscheinlichkeit α festlegen

(zB 5%) Sicherheit 1 - α = 95 % Untergrenze und Obergrenze ermitteln Wahrer Wert des Parameters liegt mit Sicherheit

1 - α im ermittelten Konfidenzintervall[Untergrenze ≤ Parameter ≤ Obergrenze]

Konfidenzintervalle werden auch als Vertrauensintervalle / Vertrauensbereiche bezeichnet

Konfidenzintervalle

Konfidenzintervall allgemein

Konfidenzintervalle

Konfidenzintervall allgemein

Varianz Grundgesamtheit bekannt

Normalverteilung Excel-Funktion: KONFIDENZ / NORMINV

Varianz Grundgesamtheit nicht bekannt Standardfall

t-Verteilung Excel-Funktion: TINV

Konfidenzintervalle

Konfidenzintervall für Mittelwert

]n

ux;n

ux[ 2/12/1

]n

stx;

n

stx[ 2/1;1n2/1;1n

Ausreißer haben massiven Einfluss auf: Mittelwert und Varianz (Standardabweichung) Konfidenzintervalle und statistische Tests

Behandlung von Ausreißern Ausreißer entfernen „cut off“ Fixe(r) Anzahl / %-Satz Extremwerte entfernen

(zB 2 größten und 2 kleinsten Werte) Ersetzen der Extremwerte durch die

nächstgelegenen Werte

Konfidenzintervalle

Ausreißer

Varianten zur Ermittlung von Ausreißern Grafische Darstellung der Einzelwerte

mögliche Ausreißer identifizieren Konfidenzintervall für den Mittelwert

außerhalb liegende Werte sind Ausreißer Ausreißertest von Grubbs

- oder

- Tabelle für Teststatistik Tn;1 - α Fixe Bandbreiten um Mittelwert (zB 15%)

Konfidenzintervalle

Ausreißer

1;n)1(

1 T s

xx T

1;n

)n(n T

s

xx T

Agenda

Excel-GrundlagenStatistische KennzahlenKonfidenzintervalle

Anwendung statistischer Verfahrenin der Liegenschaftsbewertung

Theoretische Grundlagen und Anwendung mit Microsoft Excel

Mag. Michael Mayr - Statistiker