In diesem Artikel erfahren Sie, wie Sie mithilfe eines Excel-Skripts den Benotungsprozess für Lehrer automatisieren.
Office Skript Übungsaufgabe in Excel: Notenrechner
Beschreibung:
Dieses Beispiel befasst sich mit einem Skript, das die Punkte einer Prüfung und die damit zusammenhängenden Noten einer Klasse validiert. Mit dem Skript wird nach Fehlern gesucht, sowie Werte nach bestimmten Kriterien hervorgehoben.
Übungsaufgabe:
In diesem Beispiel sind Sie ein Dozent, der die Abschlussnoten jedes Schülers zählt. Sie haben die Ergebnisse der Tests bereits erfasst. Jetzt ist es an der Zeit, über das Schicksal der Schüler zu entscheiden.
Sie entwickeln ein Skript, das die Noten für jede Punktkategorie zusammenfasst. Basierend auf der Gesamtsumme wird dann jedem Schüler eine Buchstabennote zugewiesen. Um die Genauigkeit sicherzustellen, fügen Sie ein paar Überprüfungen hinzu, um festzustellen, ob einzelne Bewertungen zu niedrig oder zu hoch sind. Wenn die Punktzahl eines Schülers unter Null oder über dem möglichen Punktwert liegt, markiert das Skript die Zelle mit einer roten Füllung und summiert die Punkte dieses Schülers nicht. Dies ist ein klarer Hinweis darauf, welche Datensätze Sie noch einmal überprüfen müssen. Außerdem fügen Sie den Noten einige grundlegende Formatierungen hinzu, damit Sie schnell den Anfang und das Ende des Kurses sehen können.
Anleitung zum Aufbau:
Schritt 1. Excel
Öffnen Sie die Arbeitsmappe in Excel.
Angenommen, Sie haben die folgenden Beispieldaten und möchten die Gesamtsumme in Spalte E und die Note in Spalte F berechnen.
Die Bedingung lautet: Wenn die Punktzahl eines Schülers unter Null oder über der möglichen Punktzahl liegt, markiert das Skript die Zelle mit einer roten Füllung und addiert nicht die Punkte dieses Schülers.
Schritt 2. Erstellen eines neuen Skripts
Wählen Sie auf der Registerkarte „Automatisieren“ die Option „Neues Skript“
Und fügen Sie das folgende Skript in den Editor ein.
function main(workbook: ExcelScript.Workbook) {
// Get the worksheet and validate the data.
let studentsRange = workbook.getActiveWorksheet().getUsedRange();
if (studentsRange.getColumnCount() !== 6) {
throw new Error(`The required columns are not present. Expected column headers: "Student ID | Assignment score | Mid-term | Final | Total | Grade"`);
}
let studentData = studentsRange.getValues();
// Clear the total and grade columns.
studentsRange.getColumn(4).getCell(1, 0).getAbsoluteResizedRange(studentData.length - 1, 2).clear();
// Clear all conditional formatting.
workbook.getActiveWorksheet().getUsedRange().clearAllConditionalFormats();
// Use regular expressions to read the max score from the assignment, mid-term, and final scores columns.
let maxScores: string[] = [];
const assignmentMaxMatches = (studentData[0][1] as string).match(/\d+/);
const midtermMaxMatches = (studentData[0][2] as string).match(/\d+/);
const finalMaxMatches = (studentData[0][3] as string).match(/\d+/);
// Check the matches happened before proceeding.
if (!(assignmentMaxMatches && midtermMaxMatches && finalMaxMatches)) {
throw new Error(`The scores are not present in the column headers. Expected format: "Assignments (n)|Mid-term (n)|Final (n)"`);
}
// Use the first (and only) match from the regular expressions as the max scores.
maxScores = [assignmentMaxMatches[0], midtermMaxMatches[0], finalMaxMatches[0]];
// Set conditional formatting for each of the assignment, mid-term, and final scores columns.
maxScores.forEach((score, i) => {
let range = studentsRange.getColumn(i + 1).getCell(0, 0).getRowsBelow(studentData.length - 1);
setCellValueConditionalFormatting(
score,
range,
"#9C0006",
"#FFC7CE",
ExcelScript.ConditionalCellValueOperator.greaterThan
)
});
// Store the current range information to avoid calling the workbook in the loop.
let studentsRangeFormulas = studentsRange.getColumn(4).getFormulasR1C1();
let studentsRangeValues = studentsRange.getColumn(5).getValues();
/* Iterate over each of the student rows and compute the total score and letter grade.
* Note that iterator starts at index 1 to skip first (header) row.
*/
for (let i = 1; i < studentData.length; i++) {
// If any of the scores are invalid, skip processing it.
if (studentData[i][1] > maxScores[0] ||
studentData[i][2] > maxScores[1] ||
studentData[i][3] > maxScores[2]) {
continue;
}
const total = (studentData[i][1] as number) + (studentData[i][2] as number) + (studentData[i][3] as number);
let grade: string;
switch (true) {
case total < 60:
grade = "F";
break;
case total < 70:
grade = "D";
break;
case total < 80:
grade = "C";
break;
case total < 90:
grade = "B";
break;
default:
grade = "A";
break;
}
// Set total score formula.
studentsRangeFormulas[i][0] = '=RC[-2]+RC[-1]';
// Set grade cell.
studentsRangeValues[i][0] = grade;
}
// Set the formulas and values outside the loop.
studentsRange.getColumn(4).setFormulasR1C1(studentsRangeFormulas);
studentsRange.getColumn(5).setValues(studentsRangeValues);
// Put a conditional formatting on the grade column.
let totalRange = studentsRange.getColumn(5).getCell(0, 0).getRowsBelow(studentData.length - 1);
setCellValueConditionalFormatting(
"A",
totalRange,
"#001600",
"#C6EFCE",
ExcelScript.ConditionalCellValueOperator.equalTo
);
["D", "F"].forEach((grade) => {
setCellValueConditionalFormatting(
grade,
totalRange,
"#443300",
"#FFEE22",
ExcelScript.ConditionalCellValueOperator.equalTo
);
})
// Center the grade column.
studentsRange.getColumn(5).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
}
/**
* Helper function to apply conditional formatting.
* @param value Cell value to use in conditional formatting formula1.
* @param range Target range.
* @param fontColor Font color to use.
* @param fillColor Fill color to use.
* @param operator Operator to use in conditional formatting.
*/
function setCellValueConditionalFormatting(
value: string,
range: ExcelScript.Range,
fontColor: string,
fillColor: string,
operator: ExcelScript.ConditionalCellValueOperator) {
// Determine the formula1 based on the type of value parameter.
let formula1: string;
if (isNaN(Number(value))) {
// For cell value equalTo rule, use this format: formula1: "=\"A\"",
formula1 = `=\"${value}\"`;
} else {
// For number input (greater-than or less-than rules), just append '='.
formula1 = `=${value}`;
}
// Apply conditional formatting.
let conditionalFormatting: ExcelScript.ConditionalFormat;
conditionalFormatting = range.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue);
conditionalFormatting.getCellValue().getFormat().getFont().setColor(fontColor);
conditionalFormatting.getCellValue().getFormat().getFill().setColor(fillColor);
conditionalFormatting.getCellValue().setRule({ formula1, operator });
}
Schritt 3. Speichern Sie das Skript
Speichern Sie das Skript unter dem Namen „Notenrechner“
Ausführen des Skripts
Sie können das Skript nun ausführen, nachdem Sie es gespeichert haben. Dieses Skript berechnet die Abschlussnote jedes Schülers. Wenn die Punktzahl eines Schülers kleiner als Null oder größer als die Höchstpunktzahl ist, füllt das Skript die Zelle rot aus und addiert die Punkte dieses Schülers nicht. Dadurch wird deutlich, welche Dokumente Sie noch einmal prüfen sollten.
Nach dem Ausführen des Skripts
Fazit:
Mit diesem Skript zeigen wir anhand eines einfachen Beispiels was mit einem Skript möglich ist. Durch die Automatisierung der Fehlerprüfung und Notenberechnung helfen Sie Lehrern und Administratoren, Zeit zu sparen.
Benötigen Sie einen VBA Programmierer?
Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).
Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular.
