Datenauswertung mit Excel

II. Daten auswerten mit Excel - Boxplot und Co.

1. Daten nach Excel kopieren

In der Regel erhalten Schüler die auszuwertenden Daten in der E-Buchdatei im Schulbuch oder in einem anderen Worddokument, meist als Liste mit Trennung durch Komma oder Semikolon. Um sie gut weiterverarbeiten zu können, ist Kopieren unerlässlich.

Am einfachsten bereitet man die Daten in Word vor, sodass man sie direkt in Excel einfügen kann.

Dazu bietet sich folgendes Vorgehen an:

1. Die Daten werden untersucht: wie sind sie voneinander getrennt? (Häufig Semikolon)

2. Die Liste wird markiert.

3. Man wählt über das Einfügen Menü (Alt + I) den Button Tabelle an und wählt dann Text in Tabelle umwandeln. Es öffnet sich ein neues Dialogfenster.

Als Spaltenzahl trägt man 1 ein und man überprüft, ob das richtige Trennzeichen bei "Text trennen bei" eingetragen ist. Semikolon ist die Vorauswahl. Dann bestätigt man mit Eingabe.

4. Die Tabelle ist dann markiert und kann direkt mit STRG+C kopiert und mit STRG+V in Excel eingefügt werden.

(Daran denken: Daten sinnvollerweise in Spalte B kopieren, da in Spalte A üblicherweise die Rangplätze eingetragen werden)

Übung 1:

Übertrage die folgende Liste nach Excel und speichere die Datei als Uebung1.xlsx ab - hiermit arbeiten wir weiter!

23; 26; 5; 11; 2; 6; 16; 20; 16; 24; 14; 19; 19; 12; 17; 12; 11; 20; 23; 8; 25; 12; 17; 21; 18; 18; 22

Übung 2:

Übertrage die folgende Datenreihe in ein neues Tabellenblatt. Achtung: hier ist das Trennzeichen ein Komma! Etwas gemein ist, dass NACH dem Verstellen des Trennzeichens nochmals die Spaltenzahl überprüft werden muss.

3, 4, 6, 8, 10, 11, 12, 13, 14, 16, 16, 17, 19, 20

2. Daten in Excel aufbereiten

Hintergrundinformation:

Um statistische Erhebungen besser auswerten und vergleichen zu können, werden neben dem Mittelwert mithilfe einer Rangliste weitere Kennwerte ermittelt.

Der kleinste Wert einer Rangliste heißt _Minimum_, der größte _Maximum_.

Die Differenz aus dem Maximum und dem Minimum heißt _Spannweite_.

Der _Zentralwert_ beschreibt den Wert, der genau "in der Mitte" steht. 50% der Werte liegen oberhalb, 50% unterhalb.

Das _untere Quartil_ beschreibt den Wert, unterhalb dessen 25% der Werte liegen, das _obere Quartil_ den Wert überhalb dessen 25% der Werte liegen.

Die Auswertung

1. Liegen die Daten wie in Übung 1 unsortiert vor, so ist der erste Schritt das Sortieren. Nach dem Einfügen sind alle Daten noch markiert, daher kann man direkt sortieren. Dies kann man über das Menü START, Button Sortieren und Filtern erledigen; wir sortieren nach Größe (aufsteigend). Alternativ kann man direkt über das Kontextmenü  "Sortieren, nach Größe aufsteigend" anwählen.

Hat man KEINEN Titel über den Werten stehen, so muss man "Benutzerdefiniertes Sortieren" wählen und den Haken bei "meine Liste hat Kopfzeilen" entfernen und mit ENTER abschließen.

Übung 3

Führe die Sortierung in Uebung1.xlsx durch.

2. In Spalte A fügen wir nun die Rangplätze ein, in A1 schreiben wir eine 1, wechseln mit dem Cursor in B1 und springen mit STRG+PfeilAb zum letzten Eintrag. Mit PfeilLinks wechseln wir wieder in Spalte B und markieren mit UMSCHALT+STRG+PfeilAuf den Bereich, der ausgefüllt werden soll.

3. Anschließend wählen wir über das Menü START - Füllbereich - "Reihe" aus. Den Füllbereich sollte man sich in die Symbolleiste für den Schnellzugriff legen, da diese Funktion häufig benötigt wird. Excel erkennt in der Regel automatisch das Inkrement - mit ENTER bestätigen. Durch die Rangplätze habe ich nun direkt meine Anzahl der Werte erkennbar, diese benötige ich dringend für meine Berechnung.

4. Unterhalb des letzten Rangplatzes in Spalte A tragen wir die Namen der Kennwerte ein (Mittelwert, Min, Pos q_u, Pos Z, Pos q_o, Max), die Berechnung erfolgt dann in Spalte B.

Übung 4

Führe diese Aufbereitung in Uebung1.xlsx durch.

3. Berechnung von

a) Minimum und Maximum: =MIN(Bereich) bzw. =MAX(Bereich)

a) Mittelwert (Durchschnitt oder auch arithmetisches Mittel): Excel =Mittelwert(Bereich)

per Hand: Summe aller Werte geteilt durch die Anzahl der Werte

b) Zentralwert: Position bei max. Rangplatz *1/2, Wert muss in Spalte B abgelesen werden

- wenn es ungerade Anzahl an Werten ist, nimmt man den nächsthöheren Rangplatz und liest dort den Wert ab = Zentralwert

- wenn es eine gerade Anzahl an Werten ist, bestimmt man den Mittelwert zwischen dem Wert dieses RPs und des nächsthöheren RPs.

- Excelbefehl: =Median(Bereich)

c) unteres Quartil: Position bei max. Rangplatz *1/4 (alles Weitere wie beim Zentralwert), Wert muss abgelesen werden.

- Excelbefehl: =QUARTILE.EXKL(Bereich, 1)

d) oberes Quartil: Position bei max. Rangplatz *3/4 (alles Weitere wie beim Zentralwert), Wert muss abgelesen werden.

- Excelbefehl: =QUARTILE.EXKL(Bereich, 3)

e) Spannweite (Maximum - Minimum)

Ob die Excelbefehle für Z, q_u und q_o genutzt werden dürfen, sollte man je nach Kontext entscheiden.

Übung 5

Führe die Berechnung in Uebung1.xlsx durch.

(Das Beispielbild kann als Hilfe dienen)

4. Boxplot erstellen

Um einen Boxplot zu drucken (Schwellpapiervorlage oder optimierte Grafik) können die berechneten Werte in die Exceldatei Boxplot.xlsx eingetragen werden. Mehr dazu im Dokument "Mit Excel Diagramme erstellen".

III. Große Datenmengen auswerten

Im Ordner vbs-Tagung/Beispieldateien(csv) finden Sie exportierte Daten.

Wählen Sie für den ersten Versuch eine der Temperatur-Dateien an.

1. Datenimport

- Menü DATEN - "Externe Daten abrufen", "Aus Text"

- Import beginnen in Zeile 2 angeben, Weiter

- Trennzeichen Semikolon anwählen, "Fertig stellen" und "OK"

2. Datenanalyse

Bestimmen Sie Minimum, Maximum, oberes und unteres Quartil, den Mittelwert und den Zentralwert. Zur Datenanalyse benötigen Sie folgende Excel-Befehle (hier im Gegensatz zu Teil 1 der direkte Weg für Quartile):

Minimum: =MIN(Zellbereich)

Unteres Quartil: =QUARTILE.INKL(Datenbereich; 1)

Zentralwert: =MEDIAN(Datenbereich)

Oberes Quartil: =QUARTILE.INKL(Datenbereich; 3)

Maximum: =MAX(Datenbereich)

Mittelwert: =MITTELWERT(Datenbereich)

Übertragen sie die Kennwerte in die Datei Boxplot.xlsx und lassen Sie sich einen Boxpot davon erstellen.

3. Bedingte Formatierung

Bei großem Datenmengen ist eine optische Untergliederung für sehende Schüler häufig sinnvoll.

Über das Menü START - "Bedingte Formatierung", Farbskalen lässt sich

eine Vielzahl an möglichen Einfärbungen auswählen (z.B. siehe Rand).

Der Kontrast ist hier sicher nicht für jeden Schüler geeignet, aber die Veranschaulichung hilft ähnlich wie der Boxplot zu einem Grobüberblick über die Verteilung.

4. Weitere csv-Daten

Für die weiteren Daten ist folgendes zu beachten:

1. Die meisten Sensoren erfassen nicht nur eine Größe. Vor der Erstellung des Diagramms einfach die nicht gewünschten Spalten markieren (STRG+Leertaste) und mit "STRG -" löschen.

2. Teilweise wird bei der automatischen Erstellung die erste Datenreihe nicht als x-Achsen Beschriftung akzeptiert. Der einfachste Weg ist für der Erstellung des Diagramms den Titel (Zeit(s)) der Spalte mit den x-Werten zu entfernen. Dann nutzt Excel automatisch diese als Beschriftungswerte.

Alternativ kann man händisch nacharbeiten:

Hier wird die Zeit als eigene Datenreihe geführt, nicht als Beschriftung der x-Achse genutzt. Das kann man auch an der Markierung der Daten erkennen, keine Daten sind violett markiert.

- Rechtsklick auf das Diagramm, Daten auswählen

- Zeit auf der linken Seite entfernen und auf der rechten Seite Bearbeiten wählen.

- Die Zeitzellen markieren und EINGABE drücken.

- Anschließend ist die Zuordnung korrekt.

5. Filterung

Zur Reduktion der Datenfülle lässt sich der Datensatz nach bestimmten Kriterien filtern.

Je nach Dauer des Experiments, kann man z.B. nur bestimmte Zeitpunkte betrachten

Messwerte nur alle ganzen Sekunden anzeigen

- In Zelle D1: Rest eintragen, in D2: =REST(B2;1)

- hier wird auf Teilbarkeit untersucht - Excel teilt den Zeitwert durch 1 und gibt ggf. den Rest, welcher übrig bleibt zurück. Erscheint eine 0, so handelt es sich um eine ganz Zahl

- Diese Formel nach unten ausfüllen (START - "Füllbereich unten")

- DATEN - "Filter" auf Spalte D setzen - 0 anwählen

für andere Intervalle:

Alle 5 Sekunden: =REST(B2;5)

Alle 10 Sekunden: =REST(B2; 10)

Alle 0,1 Sekunden: =REST(B2 *10; 1)

Kleiner Tipp um Rechenlast einzusparen:

Kopiert man die angezeigten Werte nach der Filterung (STRG+A, STRG+C, STRG+N, STRG+V) in ein neues Dokument, so ist es für Excel deutlich leichter ein Diagramm zu erzeugen, ohne alle "ausgefilterten" Daten immer mitzuschleifen.

Besonders zum Tragen kommt dieser Tipp bei der Bearbeitung der Wetterdaten - hier sind soviele Einträge zu finden, dass es nicht verwundert, wenn ein Absturz erfolgt.