Übung 2: Noten verwalten
mit Excel (v2016)
Ziel dieser Übung: Auswertung und grafische Darstellung.
Übungsblatt laden. Bearbeitung
aktivieren.
Legende: zu wählende Funktionen (fett) und
selbst einzutippende Angaben
(kursiv).
Schritt 1: Noten automatisch aus Rohpunkten
- Führen Sie die Übung am Tabellenblatt "Übung2"
durch. (Datei sollte auf USB liegen).
- In Zeile 2 und 3 können Sie nähere Angaben
zur jeweiligen Klausur machen (Bsp. siehe Blatt: "Erg. Arbeit X"). Zeile
4 bleibt frei.
- In Zeile 6-9 werden die Daten von 4 Lernenden erfasst:
denken Sie sich welche aus.
- Berechnen Sie nun in Spalte H6-9 die Summe
der jeweilig erreichten Gesamtpunkte.
Erinnerung: Wie mach man das?
- Die Zeilen 10-12 liefern Ihnen statistische
Daten über die Arbeit der Klasse:
in Zeile 10 soll die max. zu erreichende Punktezahl stehen.
Aufgabe:
Berechnen Sie in H10 die Gesamtzahl der erreichbaren Punkte.
- In Zeile 11 berechnen Sie den Punkteschnitt
für die Aufgaben 1-5 (C-G11) bzw. für die gesamte Arbeit (H11), auch
für die Noten (I11).
Erinnern Sie sich: wie macht man das?
- In Zeile 12 berechnen Sie, welcher Prozentsatz von der
Maximalpunktzahl im Schnitt erreicht wurde.
Aufgabe: wie würden Sie das berechnen?
- Formel =(C11*100)/C10, dann bis H12 übernehmen.
Erinnerung: Wie macht man das?
- Die Nummerierung der Schüler in A6-9 sollte automatisch
erfolgen.
Erinnerung: Wie macht man das?
- Die erreichten Verrechnungspunkte (Spalte
H6-9) sollen nun der entsprechenden Note aus dem Notenschlüssel
(siehe Tabellenblatt "Notenschlüssel") zugeordnet werden. Dazu müssen
Sie mit der Formel =SVERWEIS arbeiten:
- Die Formel
=SVERWEIS(H6;Notenschlüssel!$A$4:$C$15;3,1) muss in der Zelle, in der die Note stehen soll (z.B. I6)
eingegeben werden.
- Dabei ist
"H6" ist die Zelle in der die erreichten Punkte
stehen, d.h. durch Mausklick auf die Zelle kann die Zellenposition in die Formel eingefügt
werden;
- "Notenschlüssel!" ist die Bezeichnung für das entsprechende
Tabellenblatt, d.h. durch Mausklick auf das Tabellenblatt wird die
Bezeichnung und durch Ziehen der Bereich A4:C15 eingefügt. Nun muss
man noch auf
absoluter Adressierung "per Hand" umstellen.
- "3" bezeichnet die
(dritte!) Spalte, in der die ausgegebene Note steht;
- "1" bezeichnet die (erste!) Spalte,
in der die Rohpunkte stehen.
- Die Formel bis I9 übernehmen.
Erinnerung:
Wie macht man das?
- Beachten Sie: H6 wird angepasst auf die jeweilige Zeile, $A$4
nicht ($ verhindert dies).
Schritt 2: Notenübersicht
grafisch erstellen
- Hierzu muss erst ermittelt
werden, wie viele Noten von jeder Sorte vorkommen. Das geht über den
ZÄHLENWENN-Befehl: =ZÄHLENWENN('Erg. Arbeit
X'!$I$6:$I$9;"5,0") für die Zelle D5 im Tabellenblatt
Notenschlüssel. Dabei bedeutet:
- "'Erg. Arbeit X'!" das
Tabellenblatt, in dem die Anzahl der vorkommenden Noten
aufgelistet ist.
- "$I$6:$I$9" die absolute Adressierung für den
Zellbereich, in dem diese Noten stehen (I6 - I9) und
- ""5,0""
das Suchkriterium; da es sich dabei auch um einen Text
handeln kann, steht die Zeichenkette in "". Führen Sie den ZÄHLENWENN-Befehl
für die noch ausstehenden Noten 4,0 -1,0 aus.
- In das Tabellenblatt "Erg. Arbeit X" soll
nun die grafische Darstellung der Ergebnisse eingefügt werden. Dazu
gehen Sie auf Menü Einfügen klappen Säulen- und
Balkendiagramme
auf und wählen aus 2D Gruppierte Säulen.
Mit Rechtsklick auf einen beliebigen Balken wählen
Sie im Menü Daten Daten auswählen. In das nun erschienene Dialogfeld
werden links (Legendeneinträge (Reihen)) unter Hinzufügen in
das Feld Reihenwerte der Bereich der gezählten Noten ausgewählt:
='Notenschlüssel'!$D$5:$D$15. "'Notenschlüssel'!" bezeichnet das
Tabellenblatt, in dem die Werte stehen, und "$D$5:$D$15" ist die
absolute Adressierung für den ausgewählten Bereich. Unter Reihen-Name
kann eine Bezeichnung für das Diagramm eingetragen werden wie z.B. :
="Erreichte Noten". Im Dialogfeld rechts (Horizontale
Achsenbeschriftung Rubrik) unter Bearbeiten im Feld
Achsenbeschriftungsbereich die Noten 1,0 - 5,0 als horizontale
Achsenbeschriftung angeben: =Notenschlüssel!$C$5:$C$15.
Beenden Sie das Dialogfeld mit "OK".
- Da es ja bis jetzt noch keine halben Lernenden gibt ;-),
sollten Sie ggf. die Intervalle der y-Achse des Diagramms als ganze
Zahlen formatieren. Dazu klicken Sie auf die linke senkrechte Achse (y-Achse), es
erscheint
ein Auswahlfeld. Dort Achse
formatieren auswählen. Es öffnet sich ein Formatierungsbereich
mit Achsenoptionen. Dort tragen Sie unter Hauptintervall im Feld dahinter 1,0
ein.
- Notenbereiche können auch farbig
hervorgehoben werden. So können Sie z.B. den Bereich 4,0 rot
markieren, indem Sie auf die entsprechende Säule zwei mal (nicht Doppelklick)
klicken (eine einzige Säule wird ausgewählt, nicht die ganze Reihe),
dann Rechtsklick und aus Füllung z.B. rot
wählen.
Hinweis: Verwenden Sie NIEMALS Designfarben!
Immer über Weitere Farben aus Palette Standard wählen.
Schritt 3: Bedingte
Berechnungen bzw. Bezeichnung
Das geht über den WENN-Befehl
(engl. Version: IF): das Format ist
=WENN(Feld>X;"Ausgabe1";"Ausgabe2").
Das bedeutet: wenn der Wert in "Feld" größer ist
als X, wird "Ausgabe1" geschrieben, wenn nicht "Ausgabe 2".
Beispiel: =WENN(A5="m";"männlich";"weiblich")
Also: wenn in Feld A5 der Buchstabe m steht (Zahlen müssen nicht in
Anführungszeichen), wird in der Zelle, in der der WENN-Befehl steht,
"männlich" ausgegeben, in allen anderen Fällen "weiblich".
Außer "=" kann auch ">" und "<" verwendet werden (macht bei "männlich"
nicht viel Sinn, oder?). Oder doch: wie löst man das Problem mit
"divers"?
Wenden Sie den Befehl auf "bestanden" und "nicht
bestanden" an.
Mögliche Erweiterungen (ggf. zum üben zu
Hause):
- Wenn Ihr Schulleiter eine Statistik zu jeder schriftlichen Arbeit haben
möchte, kostet Sie das wenige Mausklicks und ein müdes Lächeln, dank
Herrn Kollegen Schmalzl. Betrachten Sie bitte das Blatt
"Musterlösung Abgabeblatt". Dort müssten Sie nur Feld C8 markieren, und
eingeben =, dann auf das erste Feld jener Arbeit, die Sie darstellen
wollen, im Blatt Musterlösung Klasse klicken, z.B. C9, dann RETURN. Füllen
Sie die gesamte Spalte aus: markieren Sie C8, ziehen Sie die rechte
untere Ecke des Rahmens bis C17. Fertig.
- Wenn Ihr Schulleiter ein Deckblatt zu jeder schriftlichen Arbeit haben
möchte, kostet Sie das einen Ausdruck und ein müdes Lächeln, dank Herrn
Kollegen Schmalzl. Betrachten Sie bitte das Blatt Musterlösung Deckblatt. Dort
müssten Sie nur jene Felder auf dem Ausdruck handschriftlich ausfüllen,
die sich nicht mit vertretbarem Aufwand automatisieren lassen; die
Notenstatistik Ihrer ausgewählten Klasse aus Musterlösung
Abgabeblatt ist schon enthalten.
- Zur optische Verschönerung können Sie in Excel zeichnen: Trennlinien,
Pfeile etc. Einfügen, Illustrationen, Formen, ...
- Für Übersichten mit Halbjahres-, Trimester- und/oder Jahresnoten müssen
lediglich Spalten wie dZ eingefügt werden.
- Falls gewünscht, können Sie verschiedene
kleine Noten
unterschiedlich gewichten.
- Sehr hilfreich und sinnvoll ist das Erfassen
des Datums, vor allem für die mündlichen Noten. Kopieren Sie hierfür
das Blatt Zeugnis sobald es fertig ist, markieren Sie es mit Rechtsklick, Verschieben/kopieren, gewünschtes
Blatt auswählen, Kopie erstellen abhaken. Nun steht Ihnen
für jede Note das entsprechende Feld für ein Datum zur Verfügung.
Formatieren Sie die früheren Notenfelder über Rechtsklick, Zellen
formatieren, Datum, wählen Sie einen Typ, OK.
|
|
 |
|
E-Mail:
Walter.Wagner ät uni-bayreuth.de
|
