Exemplos de formatação condicional

A formatação condicional no Excel aplica a formatação a células com base em condições ou regras específicas. Esses formatos se ajustam automaticamente quando os dados são alterados, portanto, o script não precisa ser executado várias vezes. Esta página contém uma coleção de Scripts do Office que demonstram várias opções de formatação condicional.

Esta pasta de trabalho de exemplo contém planilhas prontas para teste com os scripts de exemplo.

Baixar a pasta de trabalho de exemplo

Valor da célula

A formatação condicional de valor celular aplica um formato a cada célula que contém um valor que atende a determinados critérios. Isso ajuda a identificar rapidamente pontos de dados importantes.

O exemplo a seguir aplica a formatação condicional de valor celular a um intervalo. Qualquer valor menor que 60 terá a cor de preenchimento da célula alterada e a fonte tornada itálica.

function main(workbook: ExcelScript.Workbook) {
    // Get the range to format.
    const sheet = workbook.getWorksheet("CellValue");
    const ratingColumn = sheet.getRange("B2:B12");
    sheet.activate();
    
    // Add cell value conditional formatting.
    const cellValueConditionalFormatting =
        ratingColumn.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue).getCellValue();
    
    // Create the condition, in this case when the cell value is less than 60
    let rule: ExcelScript.ConditionalCellValueRule = {
        formula1: "60",
        operator: ExcelScript.ConditionalCellValueOperator.lessThan
    };
    cellValueConditionalFormatting.setRule(rule);
    
    // Set the format to apply when the condition is met.
    let format = cellValueConditionalFormatting.getFormat();
    format.getFill().setColor("yellow");
    format.getFont().setItalic(true);
}

Escala de cores

A formatação condicional de escala de cores aplica um gradiente de cor em um intervalo. As células com os valores mínimos e máximos do intervalo usam as cores especificadas, com outras células dimensionadas proporcionalmente. Uma cor opcional do ponto médio fornece mais contraste.

Este exemplo a seguir aplica uma escala de cores vermelha, branca e azul ao intervalo selecionado.

function main(workbook: ExcelScript.Workbook) {
    // Get the range to format.
    const sheet = workbook.getWorksheet("ColorScale");
    const dataRange = sheet.getRange("B2:M13");
    sheet.activate();

    // Create a new conditional formatting object by adding one to the range.
    const conditionalFormatting = dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.colorScale);

    // Set the colors for the three parts of the scale: minimum, midpoint, and maximum.
    conditionalFormatting.getColorScale().setCriteria({
        minimum: {
            color: "#5A8AC6", /* A pale blue. */
            type: ExcelScript.ConditionalFormatColorCriterionType.lowestValue
        },
        midpoint: {
            color: "#FCFCFF", /* Slightly off-white. */
            formula: '=50', type: ExcelScript.ConditionalFormatColorCriterionType.percentile
        },
        maximum: {
            color: "#F8696B", /* A pale red. */
            type: ExcelScript.ConditionalFormatColorCriterionType.highestValue
        }
    });
}

Barra de dados

A formatação condicional da barra de dados adiciona uma barra parcialmente preenchida no plano de fundo de uma célula. A integridade da barra é definida pelo valor na célula e pelo intervalo especificado pelo formato.

O exemplo a seguir cria a formatação condicional da barra de dados no intervalo selecionado. A escala da barra de dados vai de 0 a 1200.

function main(workbook: ExcelScript.Workbook) {
    // Get the range to format.
    const sheet = workbook.getWorksheet("DataBar");
    const dataRange = sheet.getRange("B2:D5");
    sheet.activate();

    // Create new conditional formatting on the range.
    const format = dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.dataBar);
    const dataBarFormat = format.getDataBar();

    // Set the lower bound of the data bar formatting to be 0.
    const lowerBound: ExcelScript.ConditionalDataBarRule = {
        type: ExcelScript.ConditionalFormatRuleType.number,
        formula: "0"
    };
    dataBarFormat.setLowerBoundRule(lowerBound);

    // Set the upper bound of the data bar formatting to be 1200.
    const upperBound: ExcelScript.ConditionalDataBarRule = {
        type: ExcelScript.ConditionalFormatRuleType.number,
        formula: "1200"
    };
    dataBarFormat.setUpperBoundRule(upperBound);
}

Conjunto de ícones

A formatação condicional do conjunto de ícones adiciona ícones a cada célula em um intervalo. Os ícones são provenientes de um conjunto especificado. Os ícones são aplicados com base em uma matriz ordenada de critérios, com cada mapeamento de critério em um único ícone.

O exemplo a seguir aplica o ícone "três semáforos" definir formatação condicional a um intervalo.

function main(workbook: ExcelScript.Workbook) {
    // Get the range to format.
    const sheet = workbook.getWorksheet("IconSet");
    const dataRange = sheet.getRange("B2:B12");
    sheet.activate();

    // Create icon set conditional formatting on the range.
    const conditionalFormatting = dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.iconSet);

    // Use the "3 Traffic Lights (Unrimmed)" set.
    conditionalFormatting.getIconSet().setStyle(ExcelScript.IconSet.threeTrafficLights1);
    conditionalFormatting.getIconSet().setCriteria([
      { // Use the red light as the default for positive values.
        formula: '=0', operator: ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
        type: ExcelScript.ConditionalFormatIconRuleType.number
      },
      { // The yellow light is applied to all values 6 and greater. The replaces the red light when applicable.
        formula: '=6', operator: ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
        type: ExcelScript.ConditionalFormatIconRuleType.number
      },
      { // The green light is applied to all values 8 and greater. As with the yellow light, the icon is replaced when the new criteria is met.
        formula: '=8', operator: ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
        type: ExcelScript.ConditionalFormatIconRuleType.number
      }
    ]);
}

Predefinição

A formatação condicional predefinida aplica um formato especificado a um intervalo com base em cenários comuns, como células em branco e valores duplicados. A lista completa de critérios predefinidos é fornecida pelo enumeração ConditionalFormatPresetCriterion .

O exemplo a seguir fornece um preenchimento amarelo a qualquer célula em branco no intervalo.

function main(workbook: ExcelScript.Workbook) {
    // Get the range to format.
    const sheet = workbook.getWorksheet("Preset");
    const dataRange = sheet.getRange("B2:D5");
    sheet.activate();
    
    // Add new conditional formatting to that range.
    const conditionalFormat = dataRange.addConditionalFormat(
    ExcelScript.ConditionalFormatType.presetCriteria);
    
    // Set the conditional formatting to apply a yellow fill.
    const presetFormat = conditionalFormat.getPreset();
    presetFormat.getFormat().getFill().setColor("yellow");
    
    // Set a rule to apply the conditional format when cells are left blank.
    const blankRule: ExcelScript.ConditionalPresetCriteriaRule = {
        criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks
    };
    presetFormat.setRule(blankRule);
}

Comparação de texto

A formatação condicional de comparação de texto formata células com base no conteúdo de texto. A formatação é aplicada quando o texto começa com, contém, termina ou não contém a subconserção fornecida.

O exemplo a seguir marca qualquer célula no intervalo que contém o texto "revisão".

function main(workbook: ExcelScript.Workbook) {
    // Get the range to format.
    const sheet = workbook.getWorksheet("TextComparison");
    const dataRange = sheet.getRange("B2:B6");
    sheet.activate();

    // Add conditional formatting based on the text in the cells.
    const textConditionFormat = dataRange.addConditionalFormat(
        ExcelScript.ConditionalFormatType.containsText).getTextComparison();

    // Set the conditional format to provide a light red fill and make the font bold.
    textConditionFormat.getFormat().getFill().setColor("#F8696B");
    textConditionFormat.getFormat().getFont().setBold(true);

    // Apply the condition rule that the text contains with "review".
    const textRule: ExcelScript.ConditionalTextComparisonRule = {
        operator: ExcelScript.ConditionalTextOperator.contains,
        text: "review"
    };
    textConditionFormat.setRule(textRule);
}

Parte superior/inferior

A formatação condicional superior/inferior marca os valores mais altos ou mais baixos em um intervalo. Os altos e baixos são baseados em valores brutos ou percentuais.

O exemplo a seguir aplica a formatação condicional para mostrar os dois números mais altos do intervalo.

function main(workbook: ExcelScript.Workbook) {
  // Get the range to format.
  const sheet = workbook.getWorksheet("TopBottom");
  const dataRange = sheet.getRange("B2:D5");
  sheet.activate();

    // Set the fill color to green and the font to bold for the top 2 values in the range.
    const topBottomFormat = dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.topBottom).getTopBottom();
    topBottomFormat.getFormat().getFill().setColor("green");
    topBottomFormat.getFormat().getFont().setBold(true);
    topBottomFormat.setRule({
      rank: 2, /* The numeric threshold. */
      type: ExcelScript.ConditionalTopBottomCriterionType.topItems /* The type of the top/bottom condition. */
    });
}

Condições personalizadas

A formatação condicional personalizada permite que fórmulas complexas definam quando a formatação é aplicada. Use isso quando as outras opções não forem suficientes.

O exemplo a seguir define uma formatação condicional personalizada no intervalo selecionado. Um preenchimento verde-claro e uma fonte em negrito são aplicados a uma célula se o valor for maior que o valor na coluna anterior da linha.

function main(workbook: ExcelScript.Workbook) {
    // Get the range to format.
    const sheet = workbook.getWorksheet("Custom");
    const dataRange = sheet.getRange("B2:H2");
    sheet.activate();
    
    // Apply a rule for positive change from the previous column.
    const positiveChange = dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.custom).getCustom();
    positiveChange.getFormat().getFill().setColor("lightgreen");
    positiveChange.getFormat().getFont().setBold(true);
    positiveChange.getRule().setFormula(
        `=${dataRange.getCell(0, 0).getAddress()}>${dataRange.getOffsetRange(0, -1).getCell(0, 0).getAddress()}`
    );
}