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, para que seu script não precise 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

Observação

Execute esses exemplos diretamente no Editor de Código de Scripts do Office. Para abrir o Editor de códigos, acesse Automatizar> a criação denovo script>no Editor de códigos. Substitua o código padrão pelo código de exemplo que você deseja executar e selecione Executar.

Valor da célula

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

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

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

Escala de cores: a formatação condicional aplica um gradiente de cores em um intervalo. As células com os valores mínimo e máximo do intervalo usam as cores especificadas, com as demais células dimensionadas proporcionalmente. Uma cor do ponto médio opcional fornece mais contraste.

O exemplo a seguir aplica uma escala de cores vermelho, branco 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. O preenchimento completo da barra é definido 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 vêm de um conjunto especificado. Os ícones são aplicados com base em uma matriz ordenada de critérios, com cada critério mapeado para um único ícone.

O exemplo a seguir aplica o ícone "três semáforos" e define a 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 pela enumeração ConditionalFormatPresetCriterion .

O exemplo a seguir fornece um preenchimento amarelo para 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

Comparação de texto: a formatação condicional formata as células com base no conteúdo do texto. A formatação é aplicada quando o texto começa com, contém, termina com ou não contém a subcadeia de caracteres determinada.

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

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 porcentagens.

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 essa opção quando as outras opções não forem suficientes.

O exemplo a seguir define uma formatação condicional personalizada no intervalo selecionado. Uma fonte de preenchimento verde-claro e uma fonte em negrito são aplicadas 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()}`
    );
}