Notenschlüssel-Tricks
Excel: Note der Klassenarbeit automatisch ermitteln mit SVERWEIS 28.04.2015, 22:20
Erstellen Sie Ihren individuellen Notenschlüssel und benutzen Sie ihn, um den Verrechnungspunkten Ihrer Schüler/innen automatisch die richtige Note zuordnen zu lassen. Drehen Sie bei Bedarf danach am Notenschlüssel, um Einfluss auf den Klassenschnitt zu nehmen. Ein äußerst Zeit sparendes Vorgehen mit der Excel-Funktion SVERWEIS.
Sie brauchen folgende Zutaten:
- Ihren individuellen Notenschlüssel in einer Excel-Tabelle (z.B. mit dem Lehrerfreund-Notenschlüsselrechner erzeugt).
- Die Klassenarbeitsergebnisse Ihrer Schüler/innen in einer Excel-Tabelle (ausführliche Erklärungen hier: Klassen-/Notenverwaltung mit Excel und Korrektur von Deutscharbeiten wird mit Excel leichter).
Problem: Aus Verrechnungspunkten Noten machen
Sie haben eine Klassenarbeit geschrieben. In Excel (oder sonstigem Tabellenkalkulationsprogramm) stehen die Verrechnungspunkte, die die einzelnen Schüler/innen erreicht haben (hier in Spalte E):
Sie möchten gerne automatisch die Note ermitteln lassen und dazu einen linearen oder einen angepassten Notenschlüssel verwenden (z.B. Knick-Notenschlüssel, IHK, Abi-Schlüssel).
Mit den folgenden zwei einfachen Schritten sparen Sie in Zukunft sehr, sehr viel Zeit.
1) Notenschlüssel definieren
Erzeugen Sie mit einem der Lehrerfreund-Notenschlüsselrechner einen für Sie passenden Punkte-Noten-Schlüssel und speichern Sie die CSV-Datei (Link unter der Ergebnistabelle). Kopieren Sie die Punkte-Noten-Tabelle aus dieser CSV-Datei in Ihre Klassenarbeitsdatei:
Bei Bedarf können Sie diesen Notenschlüssel manuell an der ein oder anderen Stelle verändern (jetzt oder auch später).
2) SVERWEIS-Funktion eintragen
Die Funktion SVERWEIS sucht in einem definierten Bereich nach Werten (z.B. nach Verrechnungspunkten) und gibt zugehörige Werte zurück (z.B. die Noten in der angrenzenden Spalte).
Das ist praktisch, weil wir damit in der eingefügten Notenschlüsseltabelle automatisiert "nachschauen" können, welche Note zu welchem Verrechnungspunkt gehört.
- Geben Sie in eine Zelle =SVERWEIS( ein.
- Klicken Sie auf eine (Verrechnungs-)Punktzahl, im Beispiel oben blau dargestellt. Das ist Ihr Suchkriterium. Strichpunkt eintippen.
- Markieren Sie die kopierte Punkte-Noten-Tabelle. Das ist die "Matrix", in der Sie suchen (im Beispiel oben grün dargestellt). Sorgen Sie dafür, dass vor den Zellbezeichnungen der Matrix ein Dollarzeichen steht (F4 drücken oder manuell eingeben). Aus H3:I16 wird $H$3:$I$16. Strichpunkt eintippen.
- Geben Sie als "Spaltenindex" die 2 an (= Ergebnis in der zweiten Spalte der Matrix suchen). Strichpunkt eintippen.
- Tippen Sie FALSCH ein (nur dann ein Ergebnis auswählen, wenn genau der gesuchte Wert in der Matrix gefunden wurde).
In der so präparierten Zelle sollte nun die der Verrechnungspunktzahl entsprechende Note auftauchen:
Falls die korrekte Note NICHT auftaucht, bedeutet das, dass der gesuchte Wert in Ihrem Punkte-Noten-Schlüssel nicht zu finden ist. Das kann daran liegen, dass die gesuchte Punktzahl (im Beispiel "10") möglicherweise Nachkommastellen hat, die Sie vielleicht gar nicht sehen (weil die Zelle z.B. auf 0 Dezimalstellen formatiert ist, was nur die Anzeige betrifft, nicht aber den Inhalt der Zelle).
Im folgenden Beispiel wurde für Aufgabe 1 statt 4,0 4,1 Punkte vergeben. Die Gesamtpunktzahl beträgt damit 10,1 Punkte - und dieser Wert findet sich nicht im Punkte-Noten-Schlüssel.
In diesem Fall runden Sie den Inhalt der Zelle bspw. auf eine Ganzzahl. In E3 im Beispiel oben steht nun also statt
=SUMME(B3:D3)
der gerundete Wert
=RUNDEN(SUMME(B3:D3);0)
Entsprechend können Sie bspw. auf Zehntelpunkte runden mit =RUNDEN(SUMME(B3:D3);1) oder auf halbe Punkte runden mit =RUNDEN(SUMME(B3:D3)/5;1)*5
Beachten Sie, dass die Zelle entsprechend als "Zahl" mit der korrekten Anzahl Dezimalstellen formatiert sein muss, damit Sie den wahrhaften Zellwert sehen können (rechte Maustaste auf die Zelle -> Zelle formatieren).
Kopieren Sie diese Zelle nun nach unten ("autoausfüllen"), damit die Berechnung auch für die anderen Schüler/innen ausgeführt wird.
Sie beachten, dass die Zelle mit den Verrechnungspunkten (im Beispiel Spalte E) mit nach unten rutscht, die Matrix jedoch rutscht nicht mit. Dies liegt an den Dollarzeichen ("absoluter Bezug").
Notenschlüssel mit Sockel
Was ist ein Sockel? Siehe Lehrerfreund-Notenschlüssel, Abschnitt "Sockel"
Wenn Sie einen Sockel einbauen, dann gibt der Notenschlüsselrechner die Verrechnungspunkte nur bis zur besten und schlechtesten Note aus. Die außerhalb liegenden Verrechnungspunkte sind nicht zu berechnen, da sie automatisch der besten bzw. schlechtesten Note entsprechen.
SVERWEIS findet nun in der Tabelle keinen Wert für diese außerhalb liegenden Verrechnungspunkte. Das können Sie einfach erledigen, indem Sie die Tabelle manuell erweitern. Im Beispielbild wurden einfach die zusätzlichen Punkte und die zugehörige Note eingefügt (autoausfüllen). Entsprechend muss natürlich in der SVERWEIS-Funktion die Matrix erweitert werden.
Welche Vorteile hat dieses Vorgehen?
Sie sparen Zeit: Den Notenschlüssel in Ihre Excel-Tabelle kopieren und den SVERWEIS korrekt eingeben geht deutlich schneller als für 25 Schüler/innen die Verrechnungspunkte in einer Tabelle nachzuschauen und manuell einzutragen.
Dynamisch-pädagogische Notengebung: Sie können jederzeit eine Änderung am Notenschlüssel vornehmen - diese Änderung schlägt sich sofort in den Schülernoten nieder (und natürlich auch entsprechend im Klassendurchschnitt usw.).
Digitale Auswertung: Mit der Seriendruckfunktion von Excel können Sie eine automatisierte Massenauswertung für die ganze Klasse vornehmen. Anleitung dazu hier: Rückmeldung zu Klassenarbeit + Leistungsstand mit Seriendruckfunktion
Archivierung: Alle zentralen Daten sind digital archiviert (beachten Sie die Datenschutzrichtlinien Ihres Landes).
Wie sich Änderungen am Notenschlüssel auf die Schülernoten und den Klassenschnitt auswirken, zeigt das folgende kurze Video:
