28.04.2009

1

Zeitreihen Datenerhebung und -aufbereitung,

I /E A lImport/Export, Analyse

Modul 241Modellierung von Systemen

Zeitreihen und Excel-EinführungINHALT:

ZeitreihenDefinitionen und BegriffeDefinitionen und BegriffeDatenformate und DatenaufbereitungGrundlegende statistische AnalyseZeitliche AggregierungVisualisierung von Zeitreihendaten

Einführung in ExcelEinführung in Excel

28.04.2009

2

Zeitreihendaten - BegriffeDefinition: Eine Zeitreihe (engl. time series data) ist eine zeitabhängige Folge von Datenpunkten. pDer Begriff Zeitreihe setzt voraus, dass die Daten diskret (d.h. in endlichen zeitlichen Abständen) vorliegen.Beispiele für Zeitreihen:

Klimadaten Abflussdaten Börsendaten

Zeitreihendaten - BegriffeWerden Daten kontinuierlich (=stetig) aufgezeichnet, entsteht eine Zeitreihe meist durch entsprechende zeitliche Aggregierung (Diskretisierung) der Werte(Diskretisierung) der Werte.

Beispiel: Kontinuierlich aufgezeichnete Pegeldaten werden häufig zu mittleren Tagesabflüssen aggregiert.

Die analoge Auf-zeichnung wirdzeichnung wirdimmer stärkerdurch digitaleTechniken (Datalogger)verdrängt.

28.04.2009

3

Zeitreihendaten - BegriffeDie Zeitpunkte (Zeitmarke, engl. time stamp) verteilen sich bei Zeitreihen entweder:

äquidistant – gleiche Zeitabstände (z.B. alle 5 Min. oder 1 Tag) oderunregelmäßig – z.B. alle 5 bis 7 Tage

Der einzelne Datenpunkte einer Zeitreihe enthält entweder:

einen einzelnen Wert (skalare Daten) odermehrere Werte in Form von Datentupeln (vektorielle Daten)

Äquidistante, vektorielle ZeitreihenMaxmimumTemperatur

[°C]

MinimumTemperatur

[°C]

MittlereTemperatur

[°C]

RelativeLuftfeuchte

[%]Windstärke

[Bft]

Sonnen-scheindauer

[h]

01.01.1990 -0.2 -3 -1.1 84 0.8 0

02.01.1990 -1 -1.8 -1.6 85 1.2 0

03.01.1990 -1.2 -3 -2.1 81 1.6 0

04.01.1990 0.2 -7.6 -4.1 76 1 3.5

05.01.1990 -0.3 -5.3 -2.2 88 1.7 0

06.01.1990 -1 -8.2 -4.7 91 1.3 4.7

07.01.1990 0.2 -7.1 -4.5 90 1.4 5.6

08.01.1990 -0.7 -9 -4 86 1.9 0

09.01.1990 2.5 -1.5 0.5 89 2.4 0

10.01.1990 4.4 0.7 2 85 2.1 0.2

11.01.1990 4 -0.7 1.7 82 2.9 0.1

28.04.2009

4

Unregelmäßige, skalare Zeitreihe

DATUM

Grundwasser-stand

[cm u.GOK]01-02-1986 -240,0008-02-1986 -250,0017-02-1986 -258,0022-02-1986 -266,0003-03-1986 -261,0008-03-1986 -256,9015 03 1986 264 0015-03-1986 -264,0022-03-1986 -266,0001-04-1986 -227,0009-04-1986 -234,9015-04-1986 -250,90

DatenformateJe nach Datenherkunft finden sich sehr unterschiedliche Datenformate.Häufig finden sich:

Textformate (ASCII)Komma, Semikolon, Tabulator, Leerzeichen getrenntWerte finden sich an bestimmten Positionen (Spalten)

MS kompatible Formate (z.B. xls Excel)Extensible Markup Language (XML)Häufige Formate von Zeitmarken sind:

08.07.1995 13:30 [tt.mm.jjjj hh:mm]07/08/1995 1:30 pm [mm/tt/jjjj hh:mm]9507081330 [jjmmtthhmm]UNIX time stamp (Sekunden seit 1.1.1970) Bsp.: 01.01.2005 00:00:00 = 1104559200Startzeitpunkt bei äquidistanten Zeitmarken

28.04.2009

5

Datenaufbereitung, DatenvorbereitungZeitreihendaten müssen für die weitere Verarbeitung zuerst in entsprechende Software überführt (importiert) werden. Hierzu muss der strukturelle Aufbau des Hierzu muss der strukturelle Aufbau des Ursprungformats verstanden sein.Fehlende Daten müssen eindeutig gekennzeichnet werden.Oft müssen noch einfache Nachberechnungen der Datenwerte durchgeführt werden.

Umwandlung der Zeitmarke in einen interpretierbaren Datumswert.Umwandlung von Ganzzahlen (Integer) in Dezimalzahlen.Umwandlung von Nominaldaten in höher skalierte Werte (z.B. Umrechnung von Windstärke in Windgeschwindigkeit)

Grundlegende statistische AnalyseZur Überprüfung und besseren Handhabbarkeit der Daten ist eine grundlegende statistische Analyse sehr g g ysinnvoll.Visuelle Prüfung, Prüfung auf DatenlückenBerechnung, bzw. Bestimmung von Lageparametern:

Mittelwert, Minimum, Maximum, ,

Berechnung von Streuungsparametern:Spannweite, Varianz, Standardabweichung, Variabilität

28.04.2009

6

Grundlegende statistische AnalyseERFURT Tmax Tmin Tmean

Mittelwert [°C] 12.76 4.68 8.71Minimum [°C] -15.20 -23.50 -18.20

Maximum [°C] 36.00 19.50 29.10Spannweite [°C] 51.20 43.00 47.30

Varianz [°C²] 76.06 44.74 57.61Standardabw. [°C] 8.72 6.69 7.59

Variabilität [%] 3.05 2.41 2.69

SCHMÜCKE Tmax Tmin TmeanMittelwert [°C] 8.24 2.39 4.97Minimum [°C] -14.10 -22.20 -19.20

Maximum [°C] 30.60 20.50 25.30Spannweite [°C] 44.70 42.70 44.50

Varianz [°C²] 68.10 44.66 52.45Standardabw. [°C] 8.25 6.68 7.24

Variabilität [%] 2.93 2.42 2.60

Zeitliche Aggregierung ...Je nach vorliegender zeitlicher Auflösung der Daten ist eine zeitliche Aggregierung sinnvoll um die Daten informativer zu gestalten. Die Form der Aggregierung hängt dabei vom Dateninhalt und der Zielstellung ab:

Zeitliche Mittelwerte (Monatsmittel, Jahresmittel, langjähriges Mittel)Zeitliche Summen (Monatssummen, Jahressummen, langjährige Summen)

28.04.2009

7

Visualisierung von Zeitreihendaten (stetig) ...

Klimastation Erfurt

30

35

40TmaxTminTmean

Legende

0

5

10

15

20

25

Tem

pera

tur i

n °C

-15

-10

-5

01.01

.90

01.02

.90

01.03

.90

01.04

.90

01.05

.90

01.06

.90

01.07

.90

01.08

.90

01.09

.90

01.10

.90

01.11

.90

01.12

.90

Achsenbeschriftung mit Einheiten

Visualisierung von Zeitreihendaten (diskret) ...

25

30

[mm / d] Station AStation B

Tagessummen des Niederschlags

10

15

20

0

5

01.01

.90

03.01

.90

05.01

.90

07.01

.90

09.01

.90

11.01

.90

13.01

.90

15.01

.90

17.01

.90

19.01

.90

21.01

.90

23.01

.90

25.01

.90

27.01

.90

29.01

.90

28.04.2009

8

Visualisierung von Zeitreihendaten (diskret/stetig) ...

-220

-210

-200Grundwasserstand im Versuchsfeld Schmücker Graben

-270

-260

-250

-240

-230

cm u

.GO

K

-300

-290

-280

01.02

.86

15.02

.86

01.03

.86

15.03

.86

29.03

.86

12.04

.86

26.04

.86

10.05

.86

24.05

.86

07.06

.86

21.06

.86

05.07

.86

19.07

.86

02.08

.86

16.08

.86

Einführung in ExcelDatenerhebung und -aufbereitung,

Import/Export, Analyse und Arbeiten mit E lExcel

Modul 241Modellierung von Systemen

28.04.2009

9

Excel Oberfläche

1 Akti Z ll

Excel starten über das Start-Menü oder durch Doppelklick auf eine Excel-Datei (Endung „xls“)

1. Aktive Zelle2. Spaltenüberschrift3. Zeilennummerierung4. Tabellenblätter1

2

3

4

GliederungTabellendokumentTabellenblattZeile: »1«-»65536«Spalte: »A«-»Z«, »AA«-»IV«Zelle:

Bezeichnung: »SpalteZeile« oder Tabellenblatt. SpalteZeile, z.B. A4 oder Tabelle1.A4Aktive Zelle: Eingaben immer in aktueller Zelle / aktuellen Zellen

Aufgabe:Starten Sie Excel und geben Sie folgende Datenreihen ein:Zellen B2 bis B6: 7, 3, 12, 65, 38Zellen C2 bis C6: 17, 26, 15, -30, -5Fügen Sie in Zelle B1 die Überschrift “Y-Wert”, in C1 die Überschrift “X-Wert ein.

28.04.2009

10

Markieren und Kopieren1. Gesamtes Tabellenblatt markieren: [Strg] + [A]Eine Spalte markieren: [Strg] + [Leertaste]Eine Zeile markieren: [Hochstelltaste] + [Leertaste]Zellbereich markieren: [Hochstelltaste] + [Pfeil ] (links Zellbereich markieren: [Hochstelltaste] + [Pfeil...] (links, rechts, hoch, runter) oder durch „Klicken & Ziehen“ mit der Maus.Markieren mehrerer Bereiche geschieht durch Halten von [Strg]

Aufgabe:Markieren Sie die Werte B1 bis B6 mit der Maus.Verschieben Sie den Bereich auf die Position D1 bis D6 (2 Möglichkeiten)

AusfüllenWird ein Zellinhalt in mehr als einer Zelle benötigt, wird durch Ziehen mit der Maustaste an dem kleinen Quadrat an der rechten unteren Ecke der aktiven Zelle der Inhalt in die markierten Zellen übertragen.Wird statt eines normalen Textes z.B. ein Monat in eine Zelle eingegeben, dann wird in jeder rot markierten Zelle der Folgemonat eingetragen.

Diese Automatik funktioniert bei Datumsangaben, Zahlen und Wochentagen, die Schrittweite ergibt sich aus dem Inhalt der markierten Zellen

Aufgabe:Fügen Sie in Spalte B die Monatsnamen August bis Dezember ein.

28.04.2009

11

Formeln eingebenDie Eingabe einer Formel beginnt mit dem Gleichheitszeichen (=). Alle weiteren Eingaben sind dann Funktionen, Zellbezüge und/oder Konstanten.

Die Eingabe wird mit der Eingabetaste oder dem grünen Häkchen abgeschlossen.Die Formel wird in der aktiven Zelle durch einen Doppelklick oder durch [F2] sichtbar (Bearbeitungsmodus)Doppelklick oder durch [F2] sichtbar (Bearbeitungsmodus)Die Zellauswahl kann auch mit der Maus erfolgen und je nach Funktion beliebig viele Zellen beinhalten

Aufgabe:Berechnen Sie in Zelle C7 und D7 die Summen der X und Y-Werte.Berechnen Sie in Zelle C8 und D8 die Mittelwerte von X und Y.

FunktionenDer Funktionsassistent wird über das Symbol aufgerufen(Achtung: Kategorieauswahl beachten!)

Die Mausunterstützung ist aktiv, um einzelne Zellen oder ganze Zellbereiche auszuwählen.

Aufgabe:Berechnen Sie in Zelle C9 und D9 die Varianz (VARIANZEN) von X und Y.Berechnen Sie in Zelle C10 und D10 die Standardabweichung (STABWN)von X und Y.

28.04.2009

12

Formeln bearbeiten… im Bearbeitungsmodus

Die farbige Darstellung der Zellen dient der Übersicht –Änderungen können so einfacher durchgeführt werden.

Aufgabe:

Berechnen Sie in Zelle C7 und D7 erneut die Summe und in C8 und D8erneut die Mittelwerte von X und Y. Nutzen Sie diesmal dieEntsprechenden Funktionen (SUMME und MITTELWERT).

ZelladressierungRelative Adressierung

z.B. A1 für Einzelzelle, A1:B2 für Zellen A1, A2, B1 und B2Bezug über relative Adressen (z B in Formeln) wird Bezug über relative Adressen (z.B. in Formeln) wird beim Kopieren angepasst

Absolute Adressierungz.B. $A$1 für Einzelzelle, $A$1:$B$2 für Zellen A1, A2, B1 und B2Bezug bleibt auch beim Kopieren immer erhaltenMischformen: $A1, A$1, $A1:$B2 usw.

Um h lten i hen el ti e nd b ol te Umschalten zwischen relativer und absoluter Adressierung mit [SHIFT+F4]Tabellübergreifende Bezüge

Tabelle2.A1, Tabelle3.$A1:$B2 usw.

28.04.2009

13

Vergleich Adressierungsarten1. Relativ

2. Absolut

Aufgabe:Tragen Sie in Zelle A1 die Zahl “0,3” ein. Formel in Zelle E2: X-Wert geteilt durch die Zahl in Zelle A1.Markieren Sie E2 und kopieren Sie in die Zellen E3 bis E6.Fügen Sie die Überschrift “Z-Wert” ein.

Bedingungen und Verweise

WENN-BedingungWENN(B4>1000;50;20)wenn der Wert in Zelle B4 größer als 1000 ist, dann sei das Ergebnis 50, sonst 20

VerweiseSVERWEISWVERWEISWVERWEIS

28.04.2009

14

Bedingungen und Verweise

Beispiel SVERWEIS

Diagramme IDarzustellende Zellen auswählenEinfügen-Diagramm oder Diagrammtyp wählenA i f fü i Ei llAssistent fortsetzen für weitere Einstellungenoder fertig stellenWeitere Einstellungen über Werkzeugleiste oder Kontextmenü (rechte Mousetaste auf Diagramm)

Aufgabe:Erstellen Sie ein Balkendiagramm, das die X und Y Werte enthält,eine Legende enthält und die Daten auf der X-Achse mit den Monats-namen beschriftet.Erstellen Sie ein Punkt(XY) Diagramm aus den X und Y Werten.

28.04.2009

15

Diagramme IIMenü DiagrammTrendlinie hinzufügenTrend-/Regressionstyp wählen(li i ll )(linear, potenziell etc.)Optionen festlegen(Schnittpunkt, Gleichung etc.)

Aufgabe:Fügen Sie dem Streudiagramm eine lineare Trendlinie hinzu und formatieren Sie diese in der Art, dass sie:rot ist und die Gleichung sowie das Bestimmtheitsmass angegeben werden

Datenaustausch

Zahlreiche Importfilter für verschiedenste Formate (ASCII-Text, XML-basierte Formate, …)Import durch Datei-Öffnen und Auswahl eines geeigneten Filters (Text CSV) für formatierten ASCII-Text)Weitere Optionen im darauffolgenden Dialog

Export analog durch Datei-Speichern unter

28.04.2009

16

Übungsaufgabe ...1. Überführen Sie die Daten von Erfurt, Artern und Schmücke in

Excel. Das Datenformat und der Dateiinhalt ist in der Datei: klima_dat_desc.txt beschrieben._ _

2. Rechnen Sie die Werte um, wo es notwendig ist.z.B. Überführen von Integerwerten in Dezimalwerte

3. Aggregieren Sie die Werte für Einzeljahre in neuen Tabellenblättern. So dass für jede Station und jedes Klimaelement geeignete Jahreswerte (Mittelwerte oder Summen) für 1990, 1991, 1992, ..., 2000 entstehen.

4 Erstellen Sie Diagramme für jedes Klimaelement mit den 4. Erstellen Sie Diagramme für jedes Klimaelement mit den aggregierten Jahreswerten für jede Station.

5. Berechnen Sie die grundlegenden statistischen Werte für jedes Klimaelement der gesamten Zeitreihen.

6. Fassen Sie die Statistik und die Diagramme in einem Kurzbericht zusammen. (Maximum 2 Seiten)