Beschreibung:

Dieses Szenario umfasst ein Skript, das die Aufzeichnungen eines Lehrers hinsichtlich der Noten seiner Klasse validiert. Es zeigt die Fähigkeiten der Fehlerprüfung, Zellformatierung und regulären Ausdrücke.

Szenario:

In diesem Szenario sind Sie ein Dozent, der die Abschlussnoten jedes Schülers zählt. Sie haben unterwegs die Ergebnisse für ihre Aufgaben und Tests eingegeben. 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.

Geschäftsverbindung:

Bildungseinrichtungen, von K-12-Schulen bis hin zu Universitäten, können von einem optimierten Benotungsprozess profitieren. Durch die Automatisierung der Fehlerprüfung und Notenberechnung helfen Sie Lehrern und Administratoren, Zeit zu sparen und eine genaue Aufzeichnung sicherzustellen. Dies erhöht die Effizienz des akademischen Managements und unterstützt Pädagogen dabei, sich auf die Lehre zu konzentrieren.

Anleitung zum Aufbau:

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 });
}

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.