Применение условного форматирования к диапазонам ExcelApply conditional formatting to Excel ranges

Библиотека JavaScript Excel предоставляет API для применения условного форматирования к диапазонам данных в книгах.The Excel JavaScript Library provides APIs to apply conditional formatting to data ranges in your worksheets. Эта функция упрощает визуальный анализ больших наборов данных.This functionality makes large sets of data easy to visually parse. Форматирование также динамически обновляется с учетом изменений в диапазоне.The formatting also dynamically updates based on changes within the range.

Примечание

В этой статье рассматривается условное форматирование в контексте надстроек JavaScript для Excel. В указанных ниже статьях представлены подробные сведения о всех возможностях условного форматирования в Excel.This article covers conditional formatting in the context of Excel JavaScript add-ins. The following articles provide detailed information about the full conditional formatting capabilities within Excel.

Программное управление условным форматированиемProgrammatic control of conditional formatting

Свойство Range.conditionalFormats — это коллекция объектов ConditionalFormat, применяемых к диапазону.The Range.conditionalFormats property is a collection of ConditionalFormat objects that apply to the range. Объект ConditionalFormat содержит несколько свойств, определяющих применяемый формат на основе ConditionalFormatType.The ConditionalFormat object contains several properties that define the format to be applied based on the ConditionalFormatType.

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

Примечание

У каждого из этих свойств форматирования есть соответствующий вариант *OrNullObject.Each of these formatting properties has a corresponding *OrNullObject variant. Узнайте больше об этом шаблоне в разделе * методы орнуллобжект .Learn more about that pattern in the *OrNullObject methods section.

Для объекта ConditionalFormat можно установить только один тип формата.Only one format type can be set for the ConditionalFormat object. Это определено свойством type, которое является значением перечисления объекта ConditionalFormatType.This is determined by the type property, which is a ConditionalFormatType enum value. Параметр type устанавливается при добавлении условного форматирования к диапазону.type is set when adding a conditional format to a range.

Создание правил условного форматированияCreating conditional formatting rules

Условное форматирование добавляется к диапазону с помощью conditionalFormats.add.Conditional formats are added to a range by using conditionalFormats.add. После добавления можно задать свойства, относящиеся к условному форматированию.Once added, the properties specific to the conditional format can be set. В примерах ниже показано создание различных типов форматирования.The following examples show the creation of different formatting types.

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

При условном форматировании значения ячейки применяется пользовательский формат на основе результатов одной или двух формул в ConditionalCellValueRule.Cell value conditional formatting applies a user-defined format based on the results of one or two formulas in the ConditionalCellValueRule. Свойство operator является оператором ConditionalCellValueOperator, который определяет, как итоговое выражение связано с форматированием.The operator property is a ConditionalCellValueOperator defining how the resulting expressions relate to the formatting.

В приведенном ниже примере показано применение красного шрифта ко всем значениям диапазона, которые меньше нуля.The following example shows red font coloring applied to any value in the range less than zero.

Диапазон с отрицательными числами красного цвета.

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

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

При условном форматировании с использованием цветовой шкалы применяется цветовой градиент в диапазоне данных.Color scale conditional formatting applies a color gradient across the data range. Свойство criteria в ColorScaleConditionalFormat определяет три точки ConditionalColorScaleCriterion: minimum, maximum и (при желании) midpoint.The criteria property on the ColorScaleConditionalFormat defines three ConditionalColorScaleCriterion: minimum, maximum, and, optionally, midpoint. У каждой точки условия есть три свойства:Each of the criterion scale points have three properties:

  • color — HTML-код цвета для конечной точки.color - The HTML color code for the endpoint.
  • formula — число или формула, представляющая значение конечной точки.formula - A number or formula representing the endpoint. Оно будет равным null, если type имеет значение lowestValue или highestValue.This will be null if type is lowestValue or highestValue.
  • type — способ оценки формулы.type - How the formula should be evaluated. highestValue и lowestValue относятся к значениям в форматируемом диапазоне.highestValue and lowestValue refer to values in the range being formatted.

В приведенном ниже примере показан диапазон, окрашенный с переходом от синего к желтому и красному цвету.The following example shows a range being colored blue to yellow to red. Обратите внимание, что minimum и maximum являются минимальным и максимальным значением соответственно, и для них используются формулы null.Note that minimum and maximum are the lowest and highest values respectively and use null formulas. Для значения midpoint используется тип percentage с формулой "=50", чтобы самая желтая ячейка соответствовала среднему значению.midpoint is using the percentage type with a formula of "=50" so the yellowest cell is the mean value.

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

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

ПользовательскоеCustom

При пользовательском условном форматировании применяется пользовательский формат к ячейкам на основе формулы произвольной сложности.Custom conditional formatting applies a user-defined format to the cells based on a formula of arbitrary complexity. Объект ConditionalFormatRule позволяет определять формулу в разных нотациях:The ConditionalFormatRule object lets you define the formula in different notations:

  • formula — стандартная нотация.formula - Standard notation.
  • formulaLocal — Локализовано на основе языка пользователя.formulaLocal - Localized based on the user's language.
  • formulaR1C1 — нотация R1C1.formulaR1C1 - R1C1-style notation.

В приведенном ниже примере зеленым цветом окрашен шрифт ячеек с более высокими значениями, чем в ячейках слева.The following example colors the fonts green of cells with higher values than the cell to their left.

Диапазон с числами, окрашенными в зеленый цвет, если значение в предшествующем столбце этой строки ниже.

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

ГистограммаData bar

При условном форматировании с использованием гистограмм они добавляются к ячейкам.Data bar conditional formatting adds data bars to the cells. По умолчанию минимальное и максимальное значения в диапазоне создают границы и пропорциональные размеры гистограмм.By default, the minimum and maximum values in the Range form the bounds and proportional sizes of the data bars. У DataBarConditionalFormat объекта есть несколько свойств для управления внешним видом полосы.The DataBarConditionalFormat object has several properties to control the bar's appearance.

В приведенном ниже примере используется форматирование с помощью гистограмм с заполнением слева направо.The following example formats the range with data bars filling left-to-right.

Диапазон с гистограммами позади значений в ячейках.

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

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

При условном форматировании с набором значков используются значки Excel для выделения ячеек.Icon set conditional formatting uses Excel Icons to highlight cells. Свойство criteria — это массив объекта ConditionalIconCriterion, определяющий добавляемый символ и условия для добавления.The criteria property is an array of ConditionalIconCriterion, which define the symbol to be inserted and the condition under which it is inserted. Этот массив автоматически заполняется элементами условия со свойствами по умолчанию.This array is automatically prepopulated with criterion elements with default properties. Отдельные свойства не могут быть перезаписаны.Individual properties cannot be overwritten. Вместо этого необходимо заменить весь объект условия.Instead, the whole criteria object must be replaced.

В приведенном ниже примере показано применение в диапазоне набора из трех значков с треугольниками.The following example shows a three-triangle icon set applied across the range.

Диапазон с зелеными треугольными треугольниками для значений выше 1000, желтые линии для значений между 700 и 1000, а треугольники Красного вниз для уменьшения значений.

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 = [
    {} as any,
      {
        type: Excel.ConditionalFormatIconRuleType.number,
        operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
        formula: "=700"
      },
      {
        type: Excel.ConditionalFormatIconRuleType.number,
        operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
        formula: "=1000"
      }
];

await context.sync();

Готовые условияPreset criteria

При условном форматировании с готовыми условиями применяется пользовательский формат к диапазону на основе выбранного стандартного правила.Preset conditional formatting applies a user-defined format to the range based on a selected standard rule. Эти правила определяются с помощью ConditionalFormatPresetCriterion в ConditionalPresetCriteriaRule.These rules are defined by the ConditionalFormatPresetCriterion in the ConditionalPresetCriteriaRule.

В следующем примере цвета белого шрифта задается в том месте, где значение ячейки равно по крайней мере одному стандартному отклонению от среднего.The following example colors the font white wherever a cell's value is at least one standard deviation above the range's average.

Диапазон с белым шрифтом в ячейках со значениями, превышающими среднее значение хотя бы на одно стандартное отклонение.

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

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

При условном форматировании со сравнением текста используется сравнение строк в качестве условия.Text comparison conditional formatting uses string comparisons as the condition. Свойство rule является объектом ConditionalTextComparisonRule, определяющим строку для сравнения с ячейкой и оператор для указания типа сравнения.The rule property is a ConditionalTextComparisonRule defining a string to compare with the cell and an operator to specify the type of comparison.

В примере ниже показано, как отформатировать красный цвет шрифта, если текст ячейки содержит "отсрочено".The following example formats the font color red when a cell's text contains "Delayed".

Диапазон с ячейками, содержащими слово Delayed красного цвета.

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

Верхнее или нижнее значениеTop/bottom

При условном форматировании верхнего или нижнего значения применяется форматирование к наибольшему или наименьшему значению в диапазоне.Top/bottom conditional formatting applies a format to the highest or lowest values in a range. Свойство rule, являющееся типом ConditionalTopBottomRule, указывает основание для условия (максимальное или минимальное значение), а также применение ранжированной или процентной оценки.The rule property, which is of type ConditionalTopBottomRule, sets whether the condition is based on the highest or lowest, as well as whether the evaluation is ranked or percentage-based.

В приведенном ниже примере применяется зеленое выделение к ячейке с максимальным значением в диапазоне.The following example applies a green highlight to the highest value cell in the range.

Диапазон с максимальным числом, выделенным зеленым цветом.

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

Разные форматирования и приоритетMultiple formats and priority

К диапазону можно применять несколько типов условного форматирования.You can apply multiple conditional formats to a range. Если форматы содержат конфликтующие элементы, например разный цвет шрифта, только один формат применяет этот конкретный элемент.If the formats have conflicting elements, such as differing font colors, only one format applies that particular element. Приоритет определяется свойством ConditionalFormat.priority.Precedence is defined by the ConditionalFormat.priority property. Приоритет — это число (равное индексу в ConditionalFormatCollection), которое можно установить при создании формата.Priority is a number (equal to the index in the ConditionalFormatCollection) and can be set when creating the format. Чем ниже значение priority, тем выше приоритет формата.The lowerer the priority value, the higher the priority of the format is.

В приведенном ниже примере показан выбор цвета шрифта при конфликте между двумя форматами.The following example shows a conflicting font color choice between the two formats. Для отрицательных чисел применяется полужирный шрифт, но НЕ красный, так как приоритет получает формат, устанавливающий для них синий цвет шрифта.Negative numbers will get a bold font, but NOT a red font, because priority goes to the format that gives them a blue font.

Диапазон с использованием для низких чисел полужирного красного шрифта, а для отрицательных — синего шрифта с зеленым фоном.

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

Взаимоисключающие условные форматированияMutually exclusive conditional formats

Свойство stopIfTrue объекта ConditionalFormat не позволяет применять к диапазону условное форматирование с более низким приоритетом.The stopIfTrue property of ConditionalFormat prevents lower priority conditional formats from being applied to the range. Если при сопоставлении с диапазоном применяется условное форматирование со свойством stopIfTrue === true, последующие условные форматирования не применяются, даже если их элементы не вступают в противоречие.When a range matching the conditional format with stopIfTrue === true is applied, no subsequent conditional formats are applied, even if their formatting details are not contradictory.

В приведенном ниже примере показано добавление в диапазон двух условных форматов.The following example shows two conditional formats being added to a range. Для отрицательных чисел будет использоваться синий шрифт со светло-зеленым фоном, независимо от того, выполняются ли условия другого формата.Negative numbers will have a blue font with a light green background, regardless of whether the other format condition is true.

Диапазон с применением полужирного красного шрифта для низких чисел, если они не являются отрицательными, так как в этом случае для них используется синий шрифт (не полужирный) с зеленым фоном.

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

См. такжеSee also