Excel - Затенение всей строки на основе изменения стоимости

Ответ 1

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

Скажем, ваш новый столбец - столбец D, а значение, которое вы хотите посмотреть, находится в столбце A, начиная со строки 2.

В ячейке D2 положите: = MOD (IF (ROW() = 2,0, IF (A2 = A1, D1, D1 + 1)), 2)

Заполните это, насколько вам нужно (затем скройте столбец, если хотите).

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

На вкладке Главная нажмите Условное форматирование, затем Новое правило.

Выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать.

В "Форматирование значений, где эта формула истинна", поставьте = $D2 = 1

Нажмите кнопку Формат, перейдите на вкладку Заполнить, затем выберите цвет, который хотите изменить.

Примеры здесь:

Ответ 2

Если вы используете MS Excel 2007, вы можете использовать conditional formatting на вкладке Home, как показано на скриншоте ниже. Вы можете использовать параметр color scales по умолчанию, как я сделал здесь, или вы можете продолжить создание new rule на основе вашего набора данных.

Ответ 3

Используйте Условное форматирование.

В нем простейшая форма, вы говорите "для этой ячейки, если она равна X, а затем применяйте формат foo". Однако, если вы используете метод "formula", вы можете выбрать целую строку, ввести формулу и соответствующий формат, а затем использовать копию и вставку (только форматы) для остальной части таблицы.

Вы ограничены только 3 правилами в Excel 2003 или старше, поэтому вам может понадобиться определить шаблон для цветов, а не использовать необработанные значения. Что-то вроде этого должно работать, хотя:

Ответ 4

Я нашел простое решение для группирования по контенту в Pearson Software Consulting: допустим, заголовок от A1 до B1, данные таблицы от A2 до B5, контрольная ячейка находится в столбце A

1. Сделать новую колонку, C
2. Сначала закрашиваем первую строку в формуле = 2 в ячейке C2
3. Во второй строке сделайте формулу = IF (A3 = A2, C2, NOT (C2))
4. Заполните столбец до последней строки
5. Выберите диапазон данных
6. Выберите условное форматирование, выберите Использовать формулу... и в качестве формулы укажите = $ C2

Ответ 5

Этот меня озадачил меня целую вечность. Не нравится идея создания дополнительной (неуместной) строки/столбца только для вычисления форматирования. Наконец, вышло следующее правило:

=INDIRECT("A"&ROW())<>INDIRECT("A"&(ROW()-1))

Это создает ссылку A2<>A1 для строки 2, A3<>A2 для строки 3 и т.д. Откорректируйте букву "A" как столбец, который вы хотите сравнить.

Ответ 6

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

Это может быть не все, что логически отличается от другого ответа, но я думаю, что код выглядит намного лучше:

Dim Switch As Boolean
For Each Cell In Range("B2:B" & ActiveSheet.UsedRange.Rows.Count)
    If Not Cell.Value = Cell.Offset(-1, 0).Value Then Switch = Not (Switch)
    If Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Pattern = xlNone
    If Not Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Color = 14869218
Next

Мой код здесь идет по столбцу B, он предполагает строку заголовка, поэтому она начинается с 2, и я использую метод Chr (x + 64) для получения букв столбца (которые не будут работать после столбца Z; еще не нашел достаточно простого метода для преодоления этого).

Во-первых, логическая переменная будет чередоваться всякий раз, когда значение изменится на новое (использует Offset для проверки ячейки выше), и для каждого прохождения строка проверяется либо True, либо False и соответственно ее цвет.

Ответ 7

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

• Создайте новый столбец A
• Предполагая, что первая строка данных находится в строке 2 (строка 1 является заголовком), поместите 1 в A2
• Предполагая, что ваш файл отсутствует в столбце B, во второй строке (в данном случае A3) введите формулу =IF(B3=B2,A2,A2+1)
• Заполните/скопируйте ячейку A3 вниз по столбцу до последней строки (будьте осторожны, чтобы случайно не скопировать A2, который заполнит все ячейки 1)
• Выберите диапазон данных
• В ленте Home выберите Условное форматирование → Новое правило
• Выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать
• В ячейке формулы поместите =MOD($A1, 2)=1 в формулу
• Нажмите Формат, выберите вкладку Заполнить
• Выберите нужный цвет фона, затем нажмите OK
• Нажмите "ОК"

Ответ 8

В MS Excel сначала сохраните свою книгу как файл с поддержкой макроса, затем перейдите на вкладку "Разработка" и нажмите "Visual Basic". Скопируйте и вставьте этот код в объекты Excel "ThisWorkbook". Замените 2 значения G = и C = на число столбцов, содержащих ссылки, на которые ссылаются.

В вашем случае, если номер столбца с именем "Файл №" - это первый столбец (а именно столбец 1), замените G=6 на G=1 и C=6 на C-1. Наконец нажмите макрос, выберите и запустите его. Вуаля! Работает как шарм.

Sub color()
    Dim g As Long
    Dim c As Integer
    Dim colorIt As Boolean

    g = 6
    c = 6
    colorIt = True

    Do While Cells(g, c) <> ""
        test_value = Cells(g, c)
        Do While Cells(g, c) = test_value
            If colorIt Then
                Cells(g, c).EntireRow.Select
                Selection.Interior.ColorIndex = 15
            Else
                Cells(g, c).EntireRow.Select
                Selection.Interior.ColorIndex = x1None
            End If
            g = g + 1
        Loop
        colorIt = Not (colorIt)
    Loop
End Sub

Ответ 9

Как и по крайней мере 1 другой участник здесь, мне также никогда не нравилось добавлять дополнительный столбец "помощник", который может создавать некоторые неприятности в различных ситуациях. Я наконец нашел решение. Есть несколько различных формул, которые вы можете использовать в зависимости от потребностей и того, что находится в столбце, есть ли пустые значения и т.д. Для большинства моих потребностей я воспользовался следующей простой формулой для условного форматирования (CF) формула:

=MOD(Fixed(SUMPRODUCT(1/COUNTIF(CurrentRange,CurrentRange))),2)=0

Я создаю именованный диапазон с именем "CurrentRange", используя следующую формулу, где [Sheet] - лист, на котором отображаются ваши данные, [DC] - столбец со значениями, на которые вы хотите связать данные, а [FR] - первый строка, в которой находятся данные:

=[Sheet]!$[DC]$[FR]:INDIRECT("$[DC]$" & ROW())

Ссылка на лист и ссылка на столбец будут основываться на столбце, который имеет значения, которые вы оцениваете. ПРИМЕЧАНИЕ. Вы должны использовать именованный диапазон в формуле, потому что вы получите ошибку, если попытаетесь использовать ссылки на диапазон непосредственно в формуле правила CF.

По сути, формула работает путем оценки для каждой строки количества всех уникальных значений для этой строки и выше до верхней части вашего диапазона. Это значение для каждой строки обеспечивает восходящий уникальный идентификатор для каждого нового уникального значения. Затем он использует это значение вместо функции Row() в стандартной формуле функции CF MOD для простых чередующихся цветов строк (т.е. = Mod (Row(), 2) = 0).

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

В этом примере именованный диапазон CurrentRange определяется как:

=Sheet1$A$2:INDIRECT("$A$" & ROW())

Столбец Уникальный идентификатор содержит следующую часть формулы CF:

=Fixed(SUMPRODUCT(1/COUNTIF(CurrentRange,CurrentRange)))

Вы можете видеть, что по состоянию на строке 3 количество уникальных значений из этой строки и выше в столбце "Цвет" равно 2, и оно остается равным 2 в каждой последующей строке до строки 6, когда формула, наконец, встретит 3-е уникальное значение.

Столбец Band использует остаток формулы, ссылаясь на результат в столбце B =MOD(B2,2) чтобы показать, как он возвращает вас к 1 и 0, которые затем можно использовать для CF.

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

Надеюсь, что это поможет кому-то еще избежать разочарования, которое я испытывал годами!

Ответ 10

вы можете использовать этот формуляр для выполнения задания → получить CellValue для конкретной строки, набрав:   = косвенный ("$ A & Cell()) в зависимости от того, какой столбец вы должны проверить, вам нужно изменить $A

Пример → Вы можете использовать настраиваемую функцию VBA в фоновом режиме:

Публичная функция IstDatum (Zelle) As Boolean  IstDatum = False  Если IsDate (Zelle), то IstDatum = True Конечная функция

Мне нужно проверить дату ввода в столбце A:

= IstDatum (INDIREKT ( "$ А" & Zeile()))

посмотрите на изображение: