Excel: Автоматическое расширение условного форматирования на новую строку, добавленную над данными

Question

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

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

правка: Это существующие данные

И я хочу добавить январь (отмечен красным) к этому. Если бы каждая подкатегория имела пустую строку над собой (как строка 9), я мог бы обойти это, но, к сожалению, это не так (см. строку 5).

Answer 1

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

1. Сохраните ваш файл какКнига Excel с поддержкой макросов (.xlsm)*
2. Откройте редактор VBA с помощью Ctrl+F11
3. В редакторе VBA дважды щелкните по листу в левой панели.
4. Вставьте этот код:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim iTargRowsCnt как длинный
  Dim oFCond как FormatCondition
  Dim sFCondRanges() как строка
  Dim i As Long
  Dim blnFCondRangeModified как логическое значение
  Dim sNewAppliesTo как строка

  Если Цель.Адрес = Цель.ВсяСтрока.Адрес Тогда
    iTargRowsCnt = Целевые.Строки.Количество
    Для каждого oFCond в Me.UsedRange.FormatConditions
      sFCondRanges() = Split(oFCond.AppliesTo.Address, ",")
      Для i = LBound(sFCondRanges) Для UBound(sFCondRanges)
        Если Not Intersect(Me.Range(sFCondRanges(i))(1), Target(1).EntireRow.Offset(iTargRowsCnt)) — ничто Тогда
          sFCondRanges(i) = Диапазон(Диапазон(sFCondRanges(i)), Диапазон(sFCondRanges(i)).Смещение(-iTargRowsCnt)).Адрес
          blnFCondRangeModified = Истина
        Конец Если
      Следующий 'i
      Если blnFCondRangeModified Тогда
        Для i = LBound(sFCondRanges) Для UBound(sFCondRanges)
          sNewAppliesTo = sNewAppliesTo & sFCondRanges(i) & ","
        Следующий
        sNewAppliesTo = Left(sNewAppliesTo, Len(sNewAppliesTo) - 1)
        oFCond.ModifyAppliesToRange Диапазон(sNewAppliesTo)
        blnFCondRangeModified = Ложь
        sNewAppliesTo = ""
      Конец Если
    Следующий 'oFCond
  Конец Если

Конец субтитра

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

Answer 2

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

Другой метод — скопировать самую верхнюю строку, а затем вставить с форматированием поверх новой строки (сейчас не на компьютере, нужно проверить, работает ли это для условного форматирования или нет) . ПРАВКА: подтверждено, что работает для условного форматирования в O365 Business Plus: Excel 2016.

Answer 3

используйте диапазон 1:1000, он может измениться на полный диапазон (A1:AZ1000 или что-то в этом роде), но он будет обновляться по мере добавления новых строк или столбцов, поэтому он останется полным диапазоном

Answer 4

После решения аналогичной проблемы я обнаружил, что простое указание имени таблицы в поле «Применить к:» автоматически подстраивается под любые изменения в таблице, включая добавление строк :).