Условная цветовая шкала Excel для нескольких строк

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

Я хочу использовать цветовую шкалу, чтобы было легко увидеть, когда запасы определенных продуктов будут заканчиваться. Минимальный, средний и максимальный баллы моей шкалы основаны на значении в другом столбце, и это значение отличается для каждого продукта. В зависимости от того, является ли это средней или максимальной точкой, она умножается. К сожалению, цветовая шкала не поддерживает относительные ссылки, и это означает, что мне приходится копировать условное форматирование из первой строки и изменять ссылки на минимальную, среднюю и максимальную точки для каждой второй строки. Есть ли способ обойти это, поскольку у меня есть сотни строк на моем листе?

Это то, что у меня есть на данный момент:

Пример 1

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

Пример 2


excel excel-formula vba excel-2016
person fejk    schedule 23.08.2018    source источник
comment
Можете ли вы отредактировать свой вопрос и опубликовать несколько примеров данных?   -  person cybernetic.nomad    schedule 23.08.2018
comment
Извините, я добавил несколько изображений, надеюсь, это поможет.   -  person fejk    schedule 23.08.2018

Ответы (1)


arrow_upward
2
arrow_downward

Вам понадобится отдельное правило для каждой строки, и вы можете автоматизировать создание этих правил с помощью vba.

Код ниже корректирует номер строки в формулах =$D$3*3 и =$D$3*5. Комментарии указывают, где вам может понадобиться изменить имя листа, количество строк и буквы столбцов.

Option Explicit
Sub ApplyConditionalFormatting()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") ' change to your sheet here
    Dim rw As Long
    Dim rng As Range

    For rw = 3 To 8 ' change to your respective rows
        With ws
            Set rng = .Range(.Cells(rw, "E"), .Cells(rw, "K")) ' change to your respective columns

            With rng
                .FormatConditions.AddColorScale ColorScaleType:=3
                .FormatConditions(.FormatConditions.Count).SetFirstPriority  ' now its index is 1, in case there already was cond formatting applied
            End With

            With rng.FormatConditions(1)
                With .ColorScaleCriteria(1)
                    .Type = xlConditionValueNumber
                    .Value = 0
                    .FormatColor.Color = 7039480
                End With

                With .ColorScaleCriteria(2)
                    .Type = xlConditionValueFormula
                    .Value = "='" & ws.Name & "'!$D$" & rw & "*3" ' References column D, change as needed
                    .FormatColor.Color = 8711167
                End With

                With .ColorScaleCriteria(3)
                    .Type = xlConditionValueFormula
                    .Value = "='" & ws.Name & "'!$D$" & rw & "*5" ' References column D, change as needed
                    .FormatColor.Color = 8109667
                End With
            End With
        End With
    Next rw
End Sub

До

введите здесь описание изображения

После — показывает правило для строки 8; обратите внимание, что формула относится к $D$8

введите здесь описание изображения

person BigBen    schedule 23.08.2018
comment
Фантастика, делает именно то, что я хотел, и ваши комментарии очень помогают адаптировать это к различным таблицам. Благодарю вас! - person fejk; 24.08.2018
comment
Большой! Рад помочь. - person BigBen; 24.08.2018