excel statistik & tests & regression
DESCRIPTION
TRANSCRIPT
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