Условное форматирование цветового градиента с жесткими остановками

Ответ 1

Мне не удалось найти способ сделать эту работу с использованием условного форматирования по умолчанию Excel. В VBA можно создать собственный условный алгоритм форматирования, который позволит включить эту функцию:

Sub UpdateConditionalFormatting(rng As Range)
    Dim cell As Range
    Dim colorValue As Integer
    Dim min, max As Integer

    min = WorksheetFunction.min(rng)
    max = WorksheetFunction.max(rng)

    For Each cell In rng.Cells
        If (cell.Value > 0) Then
            colorValue = (cell.Value / max) * 255
            cell.Interior.Color = RGB(255 - colorValue, 255, 255 - colorValue)
        ElseIf (cell.Value < 0) Then
            colorValue = (cell.Value / min) * 255
            cell.Interior.Color = RGB(255, 255 - colorValue, 255 - colorValue)
        End If

        Next cell
    End
End Sub

В приведенном выше коде будет создана следующая цветовая схема и может быть легко изменена, чтобы соответствовать любой палитре, которую вы имеете в виду:

Вы можете использовать этот код в макросе или перенести его в событие Worksheet_Change и обновить его автоматически (обратите внимание, что при установке в обработчике событий Worksheet_Change вы потеряете функциональность отмены):

Sub Worksheet_Change(ByVal Target As Range)
    UpdateConditionalFormatting Range("A1:A21")
End Sub

Ответ 2

Я думал, что это будет относительно легко, но это заняло больше мысли и не является изящным решением.

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

Предположение: диапазон сортируется Min-Max или Max-Min 'Это не работает в противном случае

Напишите макрос уровня листа, который обновляется при расчете или выборе - всякий раз, когда вы хотите обновить условное форматирование

в этом макросе вы определяете верхнюю и нижнюю границы вашего диапазона данных и местоположение средней точки

поэтому на рисунке выше будет

LB = A1
UP = A21
MP = A11

Затем вы просто примените два градиента w/оператор if вы сказали, что средняя точка никогда не будет точной, поэтому оператор if определит, относится ли средняя точка к верхнему или нижнему диапазону

тогда просто:

Range(LB:MP).Select .......apply traditional conditional format 1 (CF1)
Range(MP+1:UP).Select .......apply traditional conditional format 2 (CF2)
or
Range(LB:MP-1).Select .......apply traditional conditional format 1
Range(MP:UP).Select .......apply traditional conditional format 2

Я бы не использовал белый цвет MP, но в CF1, если это красный диапазон, я бы использовал светло-красный и темно-красный, а CF2 светло-зеленый - темно-зеленый

-------------------------------------- Edit ----- ---------------------------------

Я просто прочитал вашу дилемму своего рода.

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

Я использовал простую регрессию на RGB (еще проще, если вы только собираетесь G или R), чтобы фактически присвоить номер цвета каждому значению

MP = (0,1,0)
UP = (0,255,0)
MP-1 = (1,0,0)
LB = (255,0,0)

снова с тем же макросом листа и MP, если логика, как указано выше

а затем я просто повторил через ячейки и применил цвет

if cellVal < MP then CellVal*Mr+Br 'r for red, M & B for slope & intercept
if cellVal > MP then CellVal*Mg+Bg 'g for green, M & B for slope & intercept

Если это неясно, дайте мне знать, и снова, если вам нужна помощь с кодом, который я могу предоставить.

-E

Изменить 2:

Вы могли бы, и я рекомендовал бы вместо повторения всего диапазона, только итерации через видимый диапазон - это ускорит его еще больше, и вы можете добавить триггер в команду sort/filter вашей таблицы/набор данных - это также даст вам свободу выбора, если вы хотите, чтобы спектр цвета основывался на всех ваших данных или только на видимых данных - с последним вы могли бы сделать некоторые "крутые" вещи, такие как взгляд чуть выше 95-го процентиля и все еще видят различия в цветах, где, как и в случае с бывшими, они, скорее всего, будут G 250-255 и сложнее распознать

Ответ 3

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

Используя этот подход, вы можете заставить его работать без необходимости в VBA, и любая сортировка или редактирование листа все равно будут работать.

Я собрал (очень) примерный пример, который показывает, как это может сработать. Это немного грубо, так как он создаст новый условный формат для каждого значения; было бы более аккуратно создать один для каждого диапазона, который вас интересует (возможно, используя процентили), но это отправная точка.

Основная часть работы выполняется в следующих двух методах. Я добавил несколько сводных комментариев к ним, если им нужно больше объяснений, просто дайте мне знать.

/// <summary>
/// Adds a conditional format to the sheet based on the value passed in
/// </summary>
/// <param name="value">The value going into the cell</param>
/// <param name="minValue">The minimum value in the whole range of values going into the sheet</param>
/// <param name="maxValue">The maximum value in the whole range of values going into the sheet</param>
/// <param name="ignoreRangeLowValue">The lowest value in the mid-point. A value greater than or equal to this and less than or equal to the ignoreRangeHighValue will be unstyled</param>
/// <param name="ignoreRangeHighValue">The highest value in the mid-point. A value greater than or equal to the ignoreRangeLowValue and less than or equal to this value will be unstyled</param>
/// <param name="lowValuesMinColor">The colour of the lowest value below the mid-point</param>
/// <param name="lowValuesMaxColor">The colour of the highest value below the mid-point</param>
/// <param name="highValuesMinColor">The colour of the lowest value above the mid-point</param>
/// <param name="highValuesMaxColor">The colour of the highest value above the mid-point</param>
/// <param name="differentialFormats">A DifferentialFormats object to add the formats to</param>
/// <param name="conditionalFormatting">A ConditionalFormatting object to add the conditional formats to</param>
private static void AddConditionalStyle(decimal value,
                                decimal minValue,
                                decimal maxValue,
                                decimal ignoreRangeLowValue,
                                decimal ignoreRangeHighValue,
                                System.Drawing.Color lowValuesMinColor,
                                System.Drawing.Color lowValuesMaxColor,
                                System.Drawing.Color highValuesMinColor,
                                System.Drawing.Color highValuesMaxColor,
                                DifferentialFormats differentialFormats, 
                                ConditionalFormatting conditionalFormatting)
{
    System.Drawing.Color fillColor;

    if (value >= ignoreRangeLowValue && value <= ignoreRangeHighValue)
        return;

    if (value > ignoreRangeHighValue)
    {
        fillColor = GetColour(value, ignoreRangeHighValue, maxValue, highValuesMinColor, highValuesMaxColor);
    }
    else
    {
        fillColor = GetColour(value, minValue, ignoreRangeLowValue, lowValuesMinColor, lowValuesMaxColor);
    }

    DifferentialFormat differentialFormat = new DifferentialFormat();
    Fill fill = new Fill();
    PatternFill patternFill = new PatternFill();
    BackgroundColor backgroundColor = new BackgroundColor() { Rgb = fillColor.Name };
    patternFill.Append(backgroundColor);
    fill.Append(patternFill);
    differentialFormat.Append(fill);
    differentialFormats.Append(differentialFormat);

    ConditionalFormattingOperatorValues op = ConditionalFormattingOperatorValues.Between;
    Formula formula1 = null;
    Formula formula2 = null;

    if (value > maxValue)
    {
        op = ConditionalFormattingOperatorValues.GreaterThanOrEqual;
        formula1 = new Formula();
        formula1.Text = value.ToString();
    }
    else if (value < minValue)
    {
        op = ConditionalFormattingOperatorValues.LessThanOrEqual;
        formula1 = new Formula();
        formula1.Text = value.ToString();
    }
    else
    {
        formula1 = new Formula();
        formula1.Text = (value - 0.05M).ToString();
        formula2 = new Formula();
        formula2.Text = (value + 0.05M).ToString();
    }

    ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
    {
        Type = ConditionalFormatValues.CellIs,
        FormatId = (UInt32Value)formatId++,
        Priority = 1,
        Operator = op
    };

    if (formula1 != null)
        conditionalFormattingRule.Append(formula1);

    if (formula2 != null)
        conditionalFormattingRule.Append(formula2);

    conditionalFormatting.Append(conditionalFormattingRule);
}

/// <summary>
/// Returns a Color based on a linear gradient
/// </summary>
/// <param name="value">The value being output in the cell</param>
/// <param name="minValue">The minimum value in the whole range of values going into the sheet</param>
/// <param name="maxValue">The maximum value in the whole range of values going into the sheet</param>
/// <param name="minColor">The color of the low end of the scale</param>
/// <param name="maxColor">The color of the high end of the scale</param>
/// <returns></returns>
private static System.Drawing.Color GetColour(decimal value,
                                        decimal minValue,
                                        decimal maxValue,
                                        System.Drawing.Color minColor,
                                        System.Drawing.Color maxColor)
{
    System.Drawing.Color val;

    if (value < minValue)
        val = minColor;
    else if (value > maxValue)
        val = maxColor;
    else
    {
        decimal scaleValue = (value - minValue) / (maxValue - minValue);

        int r = (int)(minColor.R + ((maxColor.R - minColor.R) * scaleValue));
        int g = (int)(minColor.G + ((maxColor.G - minColor.G) * scaleValue));
        int b = (int)(minColor.B + ((maxColor.B - minColor.B) * scaleValue));

        val = System.Drawing.Color.FromArgb(r, g, b);
    }

    return val;
}

В качестве примера я создал это:

static uint formatId = 0U;
public static void CreateSpreadsheetWorkbook(string filepath)
{
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();

    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
        AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet()
    {
        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = "FormattedSheet"
    };
    sheets.Append(sheet);

    WorkbookStylesPart stylesPart = workbookpart.AddNewPart<WorkbookStylesPart>();
    stylesPart.Stylesheet = new Stylesheet();

    Fills fills = new Fills() { Count = (UInt32Value)20U }; //this count is slightly out; we should calculate it really

    //this could probably be more efficient - we don't really need one for each value; we could put them in percentiles for example
    DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)20U };

    ConditionalFormatting conditionalFormatting = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1:A21" } };

    for (decimal i = 1; i > -1.1M; i -= 0.1M)
    {
        AddConditionalStyle(i, -0.8M, 0.8M, 0M, 0M,
                            System.Drawing.Color.FromArgb(152, 83, 84),
                            System.Drawing.Color.FromArgb(244, 221, 221),
                            System.Drawing.Color.FromArgb(214, 244, 214),
                            System.Drawing.Color.FromArgb(20, 134, 33),
                            differentialFormats,
                            conditionalFormatting);
    }

    worksheetPart.Worksheet.Append(conditionalFormatting);
    stylesPart.Stylesheet.Append(differentialFormats);

    uint rowId = 1U;
    for (decimal i = 1; i > -1.1M; i -= 0.1M)
    {
        Cell cell = new Cell();
        cell.DataType = CellValues.Number;
        cell.CellValue = new CellValue(i.ToString());

        Row row = new Row() { RowIndex = rowId++ };
        row.Append(cell);
        sheetData.Append(row);
    }

    workbookpart.Workbook.Save();

    spreadsheetDocument.Close();
}

Создает таблицу, которая выглядит так:

Ответ 4

Возможно, вы можете использовать тип num на cfvo, чтобы определить среднюю точку как ноль с белым цветом. Затем установите min на красный, а max на зеленый.

Что-то вроде этого, например

<conditionalFormatting sqref="A1:A21">
   <cfRule type="colorScale" priority="1">
      <colorScale>
         <cfvo type="min" />
         <cfvo type="num" val="0" />
         <cfvo type="max" />
         <color rgb="ff0000" />
         <color rgb="ffffff" />
         <color rgb="00ff00" />
      </colorScale>
   </cfRule>
</conditionalFormatting>

дает результат, который выглядит так:

Ответ 5

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

Предположим, что мы смотрим на столбец A, который будет содержать цифры от -100 до 100. Предположим, что вы хотите, чтобы что-то -100 или хуже было ярко-красным, с постепенным исчезновением до нуля около 0. Затем, скажем, от +5 до -5, вам нужен бесцветный белый цвет. Затем около 0 оно должно быть светло-зеленого, до ярко-зеленого на +100.

Сначала установите правило относительно раздела "0". Что-то вроде:

=ROUND(A1,0)=0

Примените это правило в приоритетном порядке и установите его, чтобы сделать ячейку белой. Обратите внимание, что вы также можете использовать это для "белых" удаленных случаев. Что-то вроде:

=OR(ROUND(A1,0)=0,ROUND(A1,0)>100,ROUND(A1,0)<-100)

Это правило сделает ячейки в 0 белых и вне вашего желаемого белого диапазона -100- > 100.

Затем примените второе правило, которое включает в себя ваши градиенты. Это установило бы 3 цвета, с белым на 0 (даже если ваше жестко закодированное "округленное до 0" правило применило бы вскрытие, исключающее постепенный цвет вокруг числа 0), красный на -100 и зеленый на 100.

На этой основе все вне диапазона диапазона -100- > 100 будет белого цвета, все, что округляет до 0, будет белым, а любое другое число в диапазоне будет равномерно перемещаться от ярко-красного, белого до ярко-зеленого.

Ответ 6

Я просто начинающий vba, и я подумал, что это интересный вопрос. Я не уверен, как правила применяются для публикации решений "мозгового штурма", поэтому, если я наступаю на некоторые пальцы ног, дайте мне знать, и я удалю свой ответ и научусь избегать этого в будущем. Смиренное введение:

Можете ли вы изменить условное форматирование через vba? Я бы исследовал, можно ли это сделать: чтение свойств форматирования и объектов в условном форматировании vba представляется возможным использовать TintAndShade

Правило №1: Цвет все, что больше, чем желаемый серебристый оттенок серебра (по формуле)

Правило №2: Противоположная сторона в моноколорном красном

Правило № 3: трехсторонний градиент → изменен в vba

Измените его - удалите ссылки, но добавьте строку, в которой вы установили

 .TintAndShade = .Gradient 

внутри IF

IF .Gradient < 0.3 Then .TintAndShade = 0.3 Else .TintAndShade = .Gradient End if

(0.3 - это мое предложение. По крайней мере, на моем экране зеленый цвет @0,3 оттенка заметно отличается от красного @0.3)