Didaktik der Chemie / Universität Bayreuth

Stand: 09.05.14

Übung 1: Noten verwalten mit Excel (v2007)

Grundsätze:


Grundlegende Elemente des Excel-Bildschirms

  1. Eingegeben wird in Excel in der Regel in der Bearbeitungsleiste (mit fx beschriftet).
  2. Vor Text- und Zahleneingaben steht nichts.
  3. Vor Formeleingaben oder Befehlen steht ein = ("ist gleich").
  4. Markiert werden können
    Zeilen durch Klick auf die Zeilennummer
    Spalten durch Klick auf den Spaltenbuchstaben
    das ganze Blatt durch Klick auf die li ob Ecke.

Legende: zu wählende Funktionen (fett) und selbst einzutippende Angaben (kursiv).

Ziel dieser Übung: Erstellen eines Datenblattes unter Excel, das Notenschnitte nach den gesetzlichen Vorgaben für Zwischen- und Abschlusszeugnisse berechnet.

1. Schritt: Erstellen der Grundstruktur.

  1. Öffnen Sie die zur Verfügung gestellte Excel-Tabelle. Sie wurde auf der Grundlage eines Beispieles von Kollegen W. Schmalzl, RS Vohenstrauss, erstellt. Speichern Sie sie unter einem gewünschten Namen lokal für sich ab.
  2. Auf dem ersten Blatt: "Übung1" lernen Sie den Aufbau kennen und ändern. Nach der Lernphase können Sie das ganze Blatt löschen, da das vollständige Excel-Blatt unter dem Namen "Zeugnis" zur Verfügung steht.
  3. Formatieren Sie das gesamte Blatt nach Klick auf die linke obere Ecke in der Schriftart Arial.
  4. Tragen Sie in Feld A1 den Namen Ihrer Schule ein und formatieren Sie ihn in Ihrer Lieblingsfarbe, fett und Schriftgröße 20pt.
  5. Zeile 2 bleibt frei.
  6. Feld A3: Notenübersicht. Feld C3: Chemie. Feld F3: Klasse 11a. Feld I3: Schuljahr 2013/2014. Markieren Sie Zeile 3 durch Klicken auf die Zeilennummer und formatieren Sie die Schrift fett, Größe 12pt.
  7. Zeile 4 bleibt frei.
  8. Feld A5: Gewichtung. Feld C5: 2. Dies ist die übliche Gewichtung von schriftlichen Noten (Schulaufgaben) im Verhältnis zu den mündlichen (Rechenschaftsablage, Stegreifaufgabe, Unterrichtsbeitrag...). Feld E5: 1. Dies ist entsprechend die Gewichtung der mündlichen Noten. Feld G5: Schülerzahl. Feld I5: 10. Wir werden mit einem Mustersatz von 10 Schülern arbeiten.
  9. Formatieren Sie die Zeilen 5-25 auf Schriftgröße 10pt.
  10. Formatieren Sie Feld C5 mit einer Füllfarbe (Symbol), z.B. hellblau.
    Hinweis: Verwenden Sie NIEMALS Designfarben! Immer über Weitere Farben aus Palette Standard wählen.
  11. Für Feld E5 und I5 können Sie den Befehl über die Taste F4 wiederholen.
  12. Zeile 7 ist für diese Übung vorgegeben: Sie können demnach bis zu 4 Schulaufgaben und bis zu 9 mündliche Noten verwalten (mehr geht schon auch).
  13. Feld A8: 0 (dies ist ein Wert, den wir für die Spalte A noch benötigen werden).
  14. In Spalte A soll abwärts die laufende Nummer für Schüler erscheinen. Mit Tabellenkalkulationen kann man diese automatisieren. Feld A9: =A8+1. (Sie erinnern sich: am Zeichen "=" erkennt Excel, dass eine Formel folgt). In A9 sollte nun das Ergebnis von 0+1 stehen, nämlich 1.
  15. In die Spalte B sollen die Schülernamen. Geben Sie in Feld B9 den längsten zu erwartenden Schülernamen ein, z.B. Langstrumpf Pippilotta. Vergrößern Sie die Spalte durch Ziehen zwischen B und C so, dass der Name in das Feld passt.
  16. Formatieren Sie die Breite der Spalten C bis Q: markieren Sie durch klicken auf C und ziehen bis Q, dann Format, Spaltenbreite, Spaltenbreite 5. Die Zahl bezeichnet hineinpassende Stellen.
  17. Formatieren Sie in gleicher Weise oder durch Ziehen die Breite der Spalte A (3 Zeichen).
  18. Ein Satz von Noten ist vorgegeben, weil wir ihn für ein späteres Beispiel genauso wie angegeben benötigen.
  19. Für mündliche Noten (T1-M4) muss getrennt von schriftlichen Noten S1-S4 ein Durchschnitt gebildet werden. Dies geschieht in den Spalten P und Q (dS = Durchschnitt schriftlich).
  20. Schreiben Sie für die schriftlichen Noten in P9 die Formel: =mittelwert(, dann markieren Sie die Zellen C9 bis F9 durch ziehen und geben weiter ein ) (in Worten: Klammer zu) RETURN. Im Feld erscheint der Durchschnittswert der eingegebenen schriftlichen Noten, hier 2,5.
  21. Schreiben Sie für die mündlichen Noten in Q9 analog: =mittelwert(, dann markieren Sie die Zellen G9 bis O9 durch ziehen und geben weiter ein ) (in Worten: Klammer zu) RETURN. Im Feld erscheint der Durchschnittswert der eingegebenen schriftlichen Noten, hier ebenfalls 2,5.
  22. Markieren Sie die Felder P9 und Q9 und formatieren Sie das Format des Zelleninhalts als Zahlen: Rechtsklick, Zellen formatieren, Kategorie Zahl. Dezimalstellen bleiben wie vorgeschlagen bei 2. Die Zahlen erscheinen mit zwei Nachkommastellen: 2,50.
  23. In Spalte R soll dann die Gesamtdurchschnittsnote erscheinen. Berechnen Sie diese in R9 nach der Formel: =(P9*$C$5+Q9)/($C$5+$E$5). Dabei beziehen Sie die unterschiedliche Gewichtung der schriftlichen und mündlichen Noten ein.
    Hinweis 1: P9 ist das typische Format für relative Adressierung; wir werden sie in Schritt 2 noch näher kennen lernen.
    Hinweis 2: $C$5 ist das typische Format für absolute Adressierung; egal woher, Excel holt stets genau aus dieser Zelle den Wert für die Berechnung.
  24. Im Zeugnis muss in der Regel eine Note als ganze Zahl erscheinen. Der besseren Übersicht halber benötigen Sie eine weitere Spalte, S.
  25. Feld S9: =R9. Formatieren Sie beide Felder, R9 und S9 als Zahlen: Rechtsklick, Zellen formatieren, Kategorie Zahl. Dann stellen Sie für Feld S9, durch Eingabe einer 0 bei Dezimalstellen, eine ganze Zahl ein. Excel rundet 2,50 zu Note 3. Das wird laut Schulordnung in der Schule NICHT so gehandhabt.
  26. Lösen Sie das Problem, indem Sie vom Wert aus R9 eine kleine Zahl, max. 0,01 abziehen: =R9-0,01. Nun wird die Note 2 ausgegeben.
  27. Formatieren Sie Spalte R auf Breite 6, Spalte S auf Breite 3 Zeichen, fett und zentriert, Spalte A auf Breite 3.
  28. Zum Hervorheben wichtiger Spalten formatieren Sie die Zellen B9 und R9 hellgrau, S9 hellgrün (überlegern Sie: warum hell?).
  29. Speichern. Fertig.

2. Schritt: Erweitern auf mehrere Zeilen.

  1. Als Bespiel gehen wir hier mit einer idealen Klassenstärke von 10 Schülern um. Hierzu löschen Sie bitte die Inhalte der Zellen B9 bis O9: markieren und ENTF-Taste.
  2. Markieren Sie die Zeile 9, dann die linke untere Ecke des Rahmens bis Zeile 18 nach unten ziehen. Hier macht sich die relative Adressierung positiv bemerkbar: wenn Sie sich Zellen einer der Spalten P bis S in verschiedenen Zeilen anschauen, bemerken Sie, dass nicht einfach die Werte aus Zeile 9 kopiert, sondern dass die Zeilennummern automatisch verändert (richtig angepasst) wurden.
  3. Geben Sie 10 Namen ein und verteilen Sie Noten nach Belieben (so, wie Sie es im Unterricht auch gern tun würden :-)))). Beobachten Sie dabei die Spalten P bis S.

3. Schritt: Bilden von Notenschnitten über die Klasse.

  1. Hierzu sollen die Zeilen 20 und 21 dienen, Zeile 19 bleibt frei.
  2. Der besseren Übersicht halber (z.B. bei langen Listen mit 34 Schülern) sollte die Kopfzeile 7 nochmals erscheinen. Um aber Änderungen in Zeile 7 nicht nochmals unten ausführen zu müssen, wenden wir folgende Operation an: markieren Sie Feld A20 und geben Sie ein =A7. Markieren Sie nun die Zelle A20 und ziehen Sie die rechte untere Ecke des Rahmens bis S20. Wenn Sie wollen, können Sie die Zellen hellgrau unterlegen.
  3. In Zeile 21 folgen die Berechnungen analog den mündlichen und schriftlichen Mittelwerten. In C21: =mittelwert(C9:C18).
  4. Gleiche Berechnungen sind für die Spalten D bis O ebenfalls sinnvoll: Markieren Sie die Zelle C21 dann ziehen Sie die rechte untere Ecke des Rahmens bis O21. Da zwei Kommastellen erforderlich sind, weiter: Rechtsklick, Zellen formatieren, Zahlen, Zahl, Dezimalstellen, 2.
  5. Beschriften Sie A21 noch mit Schnitte.
  6. Speichern. Fertig.

4. Schritt: Warnfunktion über bedingte Formatierung.

  1. Bei vielen Noten je Schüler wäre eine Warnung, wenn der Schüler wegen Note 5 durchzufallen droht, hilfreich. Excel erlaubt eine solche Warnung über bedingte Formatierung.
  2. Markieren Sie Zelle S9, dann (in "Formatvorlagen"): Bedingte Formatierung, Regeln zum Hervorheben von Zellen, Größer als, 4,50, mit, rotemText, OK.
  3. Übertragen Sie die Formatierung auf die anderen Felder S: markieren Sie S9, ziehen Sie die untere rechte Ecke des Rahmens bis S18.
  4. Speichern. Fertig.
  5. Spielen Sie mit Noten und beobachten Sie das Ergebnis in den Spalten P bis S.

5. Schritt: Erstellung einer grafischen Übersicht (Balkengrafik 2D, z.B. Notenschnitte gegen die Arbeiten, Blatt "Klasse X", Zeilen 20 und 21).

  1. Löschen Sie den Inhalt von Zelle B20 (wenn diese Zelle nicht leer ist, erkennt Excel die Spalten- und Reihenbeschriftung nicht richtig).
  2. Markieren Sie die Zellen von B20 bis M21 durch Klicken und Ziehen.
  3. Einfügen, Säulen, 2D-Säulen, gruppierte Säulen.
  4. Stellen Sie sicher, dass eine Standardfarbe gewählt ist: auf einen Balken klicken; alle Balken sind angewählt. Dann re Maustaste, Datenreihen formatieren, Füllung, einfarbige Füllung, Farbe (wählen Sie eine aus dem Bereich Standardfarben). Schließen.

6. Schritt: Zusätzliche, automatische Namenspalte.

  1. Wenn die gesamte Tabelle sehr breit wird, ist eine weitere Namensliste ganz rechts hilfreich. Wie würden Sie die Aufgabe so lösen, dass Sie keine zusätzliche Tipparbeit haben?
  2. Klar, in Feld T7: =B7. Dann: T7 markieren, rechte untere Ecke bis T20 ziehen.

7. Schritt: Ausdruckformatierung.

  1. Die nun existierende breite Tabelle passt beim Ausdruck sicher nicht  auf eine Seite DIN A4 im Hochformat.
  2. Formatieren Sie: Seitenlayout, Orientierung, Querformat, Seitenränder, Benutzerdefinierte Seitenränder (alle Ränder auf 2cm, Kopf- und Fußzeile auf 0), OK, Papierformat, Anpassen aktivieren (1 Seite breit, 1 Seite hoch).
  3. Speichern. Fertig.
  4. Drucken Sie Ihre Seite ggf. (zu Hause) aus.

8. Schritt: Übernahmen von anderen Blättern. Aufgabe: die Arbeit X haben die Schüler Ihrer Klasse X (Blatt "Erg. Arbeit X") geschrieben. Die Namen der Schüler sollen automatisch aus "Übungsblatt 1" in das Blatt "Erg. Arbeit X" übernommen werden.

  1. Erweitern Sie das Blatt "Übungsblatt 1" auf 15 Schüler. Wenden Sie an, was Sie bis jetzt gelernt haben! Passen Sie alle Felder bis auf die Namen an!
  2. Die Namen können Sie sich wie folgt holen: markieren Sie in "Erg. Arbeit X" B6, geben Sie = ein und klicken Sie auf die Blattbezeichnung Übungsblatt 1, Feld B9, Return. Beachten Sie, welche Formel Excel in der Bearbeitungsleiste generiert hat: Das Blatt steht in einfachen Anführungszeichen und trägt als Abschluss ein "!".
  3. Erweitern Sie den Befehl von B6 bis B20. Erinnerung: Wie macht man das?

top

E-Mail: Walter.Wagner ät uni-bayreuth.de