Примеры условного форматирования

Условное форматирование в Excel применяет форматирование к ячейкам на основе определенных условий или правил. Эти форматы автоматически изменяются при изменении данных, поэтому скрипт не нужно выполнять несколько раз. Эта страница содержит коллекцию сценариев Office, демонстрирующих различные параметры условного форматирования.

Этот пример книги содержит листы, готовые к тестированию с помощью примеров скриптов.

Скачивание примера книги

Значение ячейки

Условное форматирование значения ячейки применяет формат к каждой ячейке, содержащей значение, удовлетворяющее заданным условиям. Это помогает быстро обнаружить важные точки данных.

В следующем примере к диапазону применяется условное форматирование значения ячейки. Любое значение меньше 60 будет иметь цвет заливки ячейки, а шрифт выделен курсивом.

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

Цветовая шкала

Условное форматирование цветовой шкалы применяет цветовой градиент в диапазоне. Ячейки с минимальным и максимальным значениями диапазона используют указанные цвета, а другие ячейки масштабируются пропорционально. Необязательный цвет средней точки обеспечивает большую контрастность.

В следующем примере к выбранному диапазону применяется красная, белая и синяя цветовая шкала.

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

Гистограмма

Условное форматирование панели данных добавляет частично заполненную полосу в фоновом режиме ячейки. Полнота полосы определяется значением в ячейке и диапазоном, заданным форматом .

В следующем примере создается условное форматирование панели данных для выбранного диапазона. Масштаб панели данных от 0 до 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);
}

Набор значков

Условное форматирование набора значков добавляет значки в каждую ячейку в диапазоне. Значки поступают из указанного набора. Значки применяются на основе упорядоченного массива критериев, при этом каждый критерий сопоставляется с одним значком.

В следующем примере для условного форматирования для диапазона применяется значок "три светофора".

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

Предустановленных

Предустановленное условное форматирование применяет указанный формат к диапазону на основе распространенных сценариев, таких как пустые ячейки и повторяющиеся значения. Полный список предустановленных условий предоставляется перечислением ConditionalFormatPresetCriterion .

Следующий пример дает желтую заливку для любой пустой ячейки в диапазоне.

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

Сравнение текста

Условное форматирование сравнения текста форматирует ячейки на основе их текстового содержимого. Форматирование применяется, когда текст начинается с, содержит, заканчивается на или не содержит указанную подстроку.

В следующем примере помечается любая ячейка в диапазоне, содержащая текст "review".

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

Сверху или снизу

Условное форматирование верхнего или нижнего значений помечает наибольшее или наименьшее значение в диапазоне. Максимумы и минимумы основаны на необработанных значениях или процентах.

В следующем примере применяется условное форматирование для отображения двух самых высоких чисел в диапазоне.

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. */
    });
}

Пользовательские условия

Пользовательское условное форматирование позволяет сложным формулам определять, когда применяется форматирование. Используйте его, если других параметров недостаточно.

Следующий пример задает пользовательское условное форматирование в выбранном диапазоне. Светло-зеленая заливка и полужирный шрифт применяются к ячейке, если значение больше значения в предыдущем столбце строки.

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