Anwenden von bedingter Formatierung auf Excel-Bereiche

Die Excel JavaScript-Bibliothek stellt APIs zum Anwenden von bedingter Formatierung auf Datenbereiche in Ihren Arbeitsblättern bereit. Diese Funktionalität macht das visuelle Analysieren großer Datensets komfortabel. Die Formatierung wird außerdem auf der Grundlage von Änderungen innerhalb des Bereichs dynamisch aktualisiert.

Hinweis

In diesem Artikel wird bedingte Formatierung im Kontext von Excel JavaScript-Add-Ins behandelt. Die folgenden Artikel bieten detaillierte Informationen über die vollständigen Funktionen zur bedingten Formatierung in Excel.

• Hinzufügen, Ändern, Suchen oder Löschen bedingter Formate
• Verwenden von Formeln mit bedingter Formatierung

Programmgesteuerte Kontrolle von bedingter Formatierung

Die Range.conditionalFormats-Eigenschaft stellt eine Sammlung von ConditionalFormat-Objekten dar, die für den Bereich gelten. Das ConditionalFormat-Objekt enthält verschiedene Eigenschaften, die das auf der Grundlage von ConditionalFormatType anzuwendende Format definieren.

• cellValue
• colorScale
• custom
• dataBar
• iconSet
• preset
• textComparison
• topBottom

Hinweis

Jeder dieser Formatierungseigenschaften ist eine entsprechende *OrNullObject-Variante zugeordnet. Weitere Informationen zu diesem Muster erfahren Sie im Abschnitt *OrNullObject-Methoden.

Für das ConditionalFormat-Objekt kann nur ein Formattyp festgelegt werden. Dies wird durch die type-Eigenschaft bestimmt, bei der es sich um einen ConditionalFormatType-Enumerationstyp handelt. type wird beim Hinzufügen eines bedingten Formats zu einem Bereich festgelegt.

Erstellen von Regeln für die bedingte Formatierung

Bedingte Formate werden einem Bereich mithilfe von conditionalFormats.add hinzugefügt. Nach dem Hinzufügen können die für das bedingte Format spezifischen Eigenschaften festgelegt werden. In den folgenden Beispielen wird die Erstellung verschiedener Formatierungstypen dargestellt.

Zellwert

Bedingte Zellwertformatierung wendet ein benutzerdefiniertes Format auf der Grundlage der Ergebnisse einer oder zweier Formeln in der ConditionalCellValueRule an. Die operator-Eigenschaft ist ein ConditionalCellValueOperator, der definiert, in welchem Verhältnis die resultierenden Ausdrücke zur Formatierung stehen.

Das folgende Beispiel zeigt die Anwendung von roter Schriftfarbe auf jeden Wert im Bereich, der kleiner als null ist.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B21:E23");
    const conditionalFormat = range.conditionalFormats.add(
        Excel.ConditionalFormatType.cellValue
    );
    
    // Set the font of negative numbers to red.
    conditionalFormat.cellValue.format.font.color = "red";
    conditionalFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
    
    await context.sync();
});

Farbskala

Bedingte Farbskalenformatierung wendet einen Farbverlauf auf einen Datenbereich an. Die criteria-Eigenschaft von ColorScaleConditionalFormat definiert drei ConditionalColorScaleCriterion: minimum, maximum und optional midpoint. Jeder dieser Kriterienskalenpunkte weist drei Eigenschaften auf:

• color: Der HTML-Farbcode für den Endpunkt.
• formula: Eine Zahl oder Formel, die den Endpunkt darstellt. Dieser Wert ist null, wenn type entweder lowestValue oder highestValue ist.
• type: Weise, in der die Formel ausgewertet werden soll. highestValue und lowestValue verweisen auf Werte im zu formatierenden Bereich.

Das folgende Beispiel zeigt einen Bereich, der von Blau über Gelb bis hin zu Rot eingefärbt ist. Beachten Sie, dass minimum und maximum die niedrigsten bzw. höchsten Werte sind und null-Formeln verwenden. midpoint verwendet den percentage-Typ in Kombination mit der Formel "=50", so dass also die gelbste Zelle den Mittelwert darstellt.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:M5");
    const conditionalFormat = range.conditionalFormats.add(
          Excel.ConditionalFormatType.colorScale
    );
    
    // Color the backgrounds of the cells from blue to yellow to red based on value.
    const criteria = {
          minimum: {
               formula: null,
               type: Excel.ConditionalFormatColorCriterionType.lowestValue,
               color: "blue"
          },
          midpoint: {
               formula: "50",
               type: Excel.ConditionalFormatColorCriterionType.percent,
               color: "yellow"
          },
          maximum: {
               formula: null,
               type: Excel.ConditionalFormatColorCriterionType.highestValue,
               color: "red"
          }
    };
    conditionalFormat.colorScale.criteria = criteria;
    
    await context.sync();
});

Benutzerdefiniert

Benutzerdefinierte bedingte Formatierung wendet auf der Grundlage einer Formel beliebiger Komplexität ein benutzerdefiniertes Format auf Zellen an. Mithilfe des ConditionalFormatRule-Objekts können Sie die Formel in verschiedenen Notationen definieren:

• formula: Standardnotation.
• formulaLocal – Lokalisiert basierend auf der Sprache des Benutzers.
• formulaR1C1: Notation im R1C1-Format.

Im folgenden Beispiel wird die Schriftfarbe von Zellen, deren Werte höher als die ihrer linken Nachbarzellen sind, grün dargestellt.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.custom
    );
    
    // If a cell has a higher value than the one to its left, set that cell's font to green.
    conditionalFormat.custom.rule.formula = '=IF(B8>INDIRECT("RC[-1]",0),TRUE)';
    conditionalFormat.custom.format.font.color = "green";
    
    await context.sync();
});

Datenbalken

Bedingte Formatierung mit Datenbalken fügt den Zellen Datenbalken hinzu. Standardmäßig bilden die Minimal- und Maximalwerte im Bereich die Grenzen und die proportionale Größe der Datenbalken. Das DataBarConditionalFormat -Objekt verfügt über mehrere Eigenschaften, um die Darstellung der Leiste zu steuern.

Im folgenden Beispiel wird der Bereich mit Datenbalken formatiert, die von links nach rechts aufgefüllt werden.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.dataBar
    );
    
    // Give left-to-right, default-appearance data bars to all the cells.
    conditionalFormat.dataBar.barDirection = Excel.ConditionalDataBarDirection.leftToRight;
    await context.sync();
});

Symbolsatz

Bedingte Formatierung mit einem Symbolsatz verwendet Excel-Symbole zum Hervorheben von Zellen. Die criteria-Eigenschaft ist ein Array von ConditionalIconCriterion, das das einzufügende Symbol und die Bedingung definiert, unter der es eingefügt werden soll. Dieses Array wird automatisch mit Kriterienelementen mit Standardeigenschaften voraufgefüllt. Einzelne Eigenschaften können nicht überschrieben werden. Vielmehr muss das gesamte Kriterienobjekt ersetzt werden.

Das folgende Beispiel zeigt einen Symbolsatz aus drei Dreiecken, die über einen Bereich angewendet werden.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.iconSet
    );
    
    const iconSetCF = conditionalFormat.iconSet;
    iconSetCF.style = Excel.IconSet.threeTriangles;
    
    /*
       With a "three*" icon set style, such as "threeTriangles", the third
        element in the criteria array (criteria[2]) defines the "top" icon;
        e.g., a green triangle. The second (criteria[1]) defines the "middle"
        icon, The first (criteria[0]) defines the "low" icon, but it can often 
        be left empty as this method does below, because every cell that
       does not match the other two criteria always gets the low icon.
    */
    iconSetCF.criteria = [
        {},
          {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: "=700"
          },
          {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: "=1000"
          }
    ];

    await context.sync();
});

Vordefinierte Kriterien

Vordefinierte bedingte Formatierung wendet ein benutzerdefiniertes Format auf der Grundlage einer ausgewählten Standardregel auf den Bereich an. Diese Regeln werden durch das ConditionalFormatPresetCriterion in der ConditionalPresetCriteriaRule definiert.

Im folgenden Beispiel wird die Schriftart weiß farbig, wenn der Wert einer Zelle mindestens eine Standardabweichung über dem Mittelwert des Bereichs liegt.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:M5");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.presetCriteria
    );
    
    // Color every cell's font white that is one standard deviation above average relative to the range.
    conditionalFormat.preset.format.font.color = "white";
    conditionalFormat.preset.rule = {
         criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevAboveAverage
    };
    
    await context.sync();
});

Textvergleich

Bedingte Textvergleichsformatierung verwendet Vergleiche von Zeichenfolgen als Bedingung. Die rule-Eigenschaft ist eine ConditionalTextComparisonRule, die eine mit der Zelle zu vergleichende Zeichenfolge und einen Operator definiert, der den Typ des Vergleichs angibt.

Im folgenden Beispiel wird die Schriftfarbe Rot formatiert, wenn der Text einer Zelle "Delayed" enthält.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B16:D18");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.containsText
    );
    
    // Color the font of every cell containing "Delayed".
    conditionalFormat.textComparison.format.font.color = "red";
    conditionalFormat.textComparison.rule = {
         operator: Excel.ConditionalTextOperator.contains,
         text: "Delayed"
    };
    
    await context.sync();
});

Oberster/unterster

Die bedingte Formatierung für den obersten/untersten Wert wendet ein Format auf die höchsten oder niedrigsten Werte in einem Bereich an. Die rule-Eigenschaft, die den Typ ConditionalTopBottomRule aufweist, legt fest, ob die Bedingungen auf dem höchsten oder dem niedrigsten Wert basiert, und außerdem, ob die Auswertung nach Rangstufe oder Prozentsatz erfolgt.

Das folgende Beispiel wendet eine grüne Hervorhebung auf die Zelle mit dem höchsten Wert im Bereich an.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B21:E23");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.topBottom
    );
    
    // For the highest valued cell in the range, make the background green.
    conditionalFormat.topBottom.format.fill.color = "green"
    conditionalFormat.topBottom.rule = { rank: 1, type: "TopItems"}
    
    await context.sync();
});

Ändern von Regeln für die bedingte Formatierung

Das ConditionalFormat -Objekt bietet mehrere Methoden zum Ändern von Regeln für die bedingte Formatierung, nachdem sie festgelegt wurden.

• changeRuleToCellValue
• changeRuleToColorScale
• changeRuleToContainsText
• changeRuleToCustom
• changeRuleToDataBar
• changeRuleToIconSet
• changeRuleToPresetCriteria
• changeRuleToTopBottom

Das folgende Beispiel zeigt, wie Sie die changeRuleToPresetCriteria -Methode aus der vorherigen Liste verwenden, um eine vorhandene Regel für das bedingte Format in den voreingestellten Regeltyp kriterien zu ändern.

Hinweis

Der angegebene Bereich muss über eine regelbedingte Formatierung verfügen, um die Änderungsmethoden verwenden zu können. Wenn der angegebene Bereich keine Regel für das bedingte Format aufweist, wenden die Änderungsmethoden keine neue Regel an.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:M5");
    
    // Retrieve the first existing `ConditionalFormat` rule on this range. 
    // Note: The specified range must have an existing conditional format rule.
    const conditionalFormat = range.conditionalFormats.getItemOrNullObject("0");
    
    // Change the conditional format rule to preset criteria.
    conditionalFormat.changeRuleToPresetCriteria({
        criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevAboveAverage, 
    });
    conditionalFormat.preset.format.font.color = "red";
    
    await context.sync();
});

Mehrere Formate und Priorität

Sie können auf einen Bereich mehrere bedingte Formate anwenden. Wenn die Formate miteinander im Konflikt stehende Elemente aufweisen, wie etwa verschiedene Schriftfarben, wird auf das betreffende Element nur ein Format angewendet. Die Rangfolge wird durch die ConditionalFormat.priority-Eigenschaft definiert. Priorität ist eine Zahl (gleich dem Index in der ConditionalFormatCollection) und kann beim Erstellen des Formats festgelegt werden. Je niedriger der priority Wert, desto höher ist die Priorität des Formats.

Das folgende Beispiel zeigt die Wahl von im Konflikt stehenden Schriftfarben zwischen zwei Formaten. Negative Zahlen werden fett formatiert, aber NICHT in roter Schriftfarbe, da Priorität bei dem Format liegt, das ihnen eine blaue Schriftfarbe verleiht.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const temperatureDataRange = sheet.tables.getItem("TemperatureTable").getDataBodyRange();
    
    
    // Set low numbers to bold, dark red font and assign priority 1.
    const presetFormat = temperatureDataRange.conditionalFormats
        .add(Excel.ConditionalFormatType.presetCriteria);
    presetFormat.preset.format.font.color = "red";
    presetFormat.preset.format.font.bold = true;
    presetFormat.preset.rule = { criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevBelowAverage };
    presetFormat.priority = 1;
    
    // Set negative numbers to blue font with green background and set priority 0.
    const cellValueFormat = temperatureDataRange.conditionalFormats
        .add(Excel.ConditionalFormatType.cellValue);
    cellValueFormat.cellValue.format.font.color = "blue";
    cellValueFormat.cellValue.format.fill.color = "lightgreen";
    cellValueFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
    cellValueFormat.priority = 0;
    
    await context.sync();
});

Sich gegenseitig ausschließende bedingte Formate

Die stopIfTrue-Eigenschaft von ConditionalFormat verhindert die Anwendung von bedingten Formaten mit geringerer Priorität auf den Bereich. Wenn auf einen Bereich das bedingte Format angewendet wird, für das stopIfTrue === true festgelegt ist, werden nachfolgend keine weiteren bedingten Formate mehr angewendet, selbst wenn deren Formatierungsdetails nicht kontradiktorisch sind.

Das folgende Beispiel zeigt zwei bedingte Formate, die einem Bereich hinzugefügt werden. Negative Zahlen weisen eine blaue Schriftfarbe mit hellgrünem Hintergrund auf, unabhängig davon, ob die andere Formatbedingung erfüllt ist oder nicht.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const temperatureDataRange = sheet.tables.getItem("TemperatureTable").getDataBodyRange();
    
    // Set low numbers to bold, dark red font and assign priority 1.
    const presetFormat = temperatureDataRange.conditionalFormats
        .add(Excel.ConditionalFormatType.presetCriteria);
    presetFormat.preset.format.font.color = "red";
    presetFormat.preset.format.font.bold = true;
    presetFormat.preset.rule = { criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevBelowAverage };
    presetFormat.priority = 1;
    
    // Set negative numbers to blue font with green background and 
    // set priority 0, but set stopIfTrue to true, so none of the 
    // formatting of the conditional format with the higher priority
    // value will apply, not even the bolding of the font.
    const cellValueFormat = temperatureDataRange.conditionalFormats
        .add(Excel.ConditionalFormatType.cellValue);
    cellValueFormat.cellValue.format.font.color = "blue";
    cellValueFormat.cellValue.format.fill.color = "lightgreen";
    cellValueFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
    cellValueFormat.priority = 0;
    cellValueFormat.stopIfTrue = true;
    
    await context.sync();
});

Regeln für die bedingte Formatierung löschen

Um Formateigenschaften aus einer bestimmten Bedingungsformatregel zu entfernen, verwenden Sie die clearFormat-Methode des ConditionalRangeFormat -Objekts. Die clearFormat -Methode erstellt eine Formatierungsregel ohne Formateinstellungen.

Um alle Regeln für die bedingte Formatierung aus einem bestimmten Bereich oder einem gesamten Arbeitsblatt zu entfernen, verwenden Sie die clearAll-Methode des ConditionalFormatCollection -Objekts.

Im folgenden Beispiel wird gezeigt, wie Sie die gesamte bedingte Formatierung aus einem Arbeitsblatt mit der clearAll -Methode entfernen.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange();
    range.conditionalFormats.clearAll();

    await context.sync();
});

Siehe auch

• JavaScript-Objektmodell für Excel in Office-Add-Ins
• ConditionalFormat-Objekt (JavaScript-API für Excel)
• Hinzufügen, Ändern, Suchen oder Löschen bedingter Formate
• Verwenden von Formeln mit bedingter Formatierung