☰

Excel: データの上に追加された新しい行に条件付き書式を自動的に拡張する

tag-icon • tag-icon microsoft-excel conditional-formatting
Excel: データの上に追加された新しい行に条件付き書式を自動的に拡張する

複数の行と列にまたがる多数の値があります。各行に特定の月のデータが表示され、最新の値が上部に表示されるように構造化されています。したがって、新しいデータで更新すると、既存のデータの上に新しい行が挿入されます。

古い範囲に適用した条件付き書式設定ルールが、新しい範囲 (1 行追加) に確実に適用されるようにするには、どうすればよいですか? テーブルまたは名前付き範囲を使用しようとしましたが、古いデータの上に行を追加したため、Excel は名前付き範囲/テーブルを自動的に拡張しません。

編集: これは既存のデータです

ここに画像の説明を入力してください

そして、これに 1 月 (赤でマーク) を追加したいと思います。すべてのサブカテゴリの上に空の行 (行 9 など) があれば、これを回避できますが、残念ながらそうではありません (行 5 を参照)。

ここに画像の説明を入力してください

答え1

データ構造を考慮すると、VBA を使用することでのみ目的を達成できると思います。データ構造に似たものを作成し、行が追加されるたびに実行されるスクリプトを作成しました。

  1. ファイルを次のように保存しますExcel マクロ対応ブック (.xlsm)* Excel マクロ対応ブック (*.xlsm)
  2. Ctrl+でVBAエディターを開くF11
  3. VBA エディターで、左側のペインにあるシートをダブルクリックします。
  4. 次のコードを貼り付けます:
プライベートサブ Worksheet_Change(ByVal Target As Range)
  Dim iTargRowsCnt As Long
  Dim oFCond As フォーマット条件
  Dim sFCondRanges() を文字列として
  暗く長く
  Dim blnFCondRangeModified As ブール値
  Dim sNewAppliesTo を文字列として

  Target.Address = Target.EntireRow.Addressの場合
    iTargRowsCnt = ターゲット行数
    Me.UsedRange.FormatConditions 内の各 oFCond について
      sFCondRanges() = Split(oFCond.AppliesTo.Address, ",")
      i = LBound(sFCondRanges) の場合、UBound(sFCondRanges) になります。
        Intersect(Me.Range(sFCondRanges(i))(1), Target(1).EntireRow.Offset(iTargRowsCnt)) が何もない場合
          sFCondRanges(i) = Range(Range(sFCondRanges(i)), Range(sFCondRanges(i)).Offset(-iTargRowsCnt)).Address
          blnFCondRangeModified = True
        終了条件
      次に
      blnFCondRangeModifiedの場合
        i = LBound(sFCondRanges) の場合、UBound(sFCondRanges) になります。
          sNewAppliesTo = sNewAppliesTo & sFCondRanges(i) & ","
        次
        sNewAppliesTo = 左(sNewAppliesTo、長さ(sNewAppliesTo) - 1)
        oFCond.ModifyAppliesToRange 範囲(sNewAppliesTo)
        blnFCondRangeModified = False
        sNewAppliesTo = ""
      終了条件
    次の'oFCond
  終了条件

終了サブ

このコードは、行を挿入するたびに自動的に実行されます。ただし、VBA を使用するときは注意してください。リスクを軽減しようとしましたが、頻繁に保存すると予期しない事態が発生する可能性があります。たとえば、元に戻す機能とうまく連携するコードを設計するのは困難です。この場合、行を挿入してから元に戻すを使用すると、条件付き書式が歪んでしまいます。

答え2

私は範囲だけに固執します。条件付き書式を適用するときに上部に追加の行を含め、それを非表示にするか、視覚的に好きなように操作します (個人的には、誤って削除されないように、バッファー行としての目的をどこかに書き留めておきます)。

もう 1 つの方法は、一番上の行をコピーし、新しい行に書式設定として貼り付けることです(現在コンピューター上にないため、条件付き書式設定でこれが機能するかどうかを確認する必要があります)。編集: O365 Business Plus: Excel 2016 で条件付き書式設定が機能することが確認されました。

答え3

範囲1:1000を使用すると、フル範囲（A1:AZ1000など）に変更される可能性がありますが、行や列を追加すると更新されるため、フル範囲のままになります。

答え4

同様の課題に苦労した後、「適用先:」エントリでテーブル名を参照するだけで、行の追加を含むテーブルの変更が自動的に調整されることを発見しました :)。

関連情報