【Excel】条件付き書式でファイルが重い — ルールが増殖する原因と減らし方

この記事は約7分で読めます。

大きな表でもないのにスクロールがカクつく、保存が遅い。そんなときは、条件付き書式のルールが知らないうちに増えていることがあります。

この記事で分かること

  • 条件付き書式でファイルが重くなる仕組み
  • ルールがいくつあるかを確かめる方法
  • 増えたルールを安全に整理する手順

どんな場面で使うか

  • 行のコピー＆貼り付けを何年も繰り返してきた集計ファイル
  • 複数の人が代わる代わる編集している共有ファイル
  • 同じくらいの行数のファイルと比べて、サイズが明らかに大きい
  • 列を1つ挿入しただけで、数秒待たされる

基本説明

使うデータ

サンプルファイル（記事末尾）の「売上集計」シートを使います。A列が商品名、B列が売上、C列が担当、D列がエリアです。

B列には、「セルの値 > 80」という同じ条件のルールが24本、細切れの範囲に分かれて入っています。断片のあいだの行（5行目・8行目など）には、ルールがありません。「在庫」シートには、条件の違うルールが3本入っています（比べる用）。

コピペでルールが増える

条件付き書式は、「ルール（条件と書式）」と「適用する範囲」の組で保存されます。

行や列をコピー＆貼り付けすると、書式と一緒にルールも複製されます。 元は1つのルールが、貼り付けのたびに「範囲だけが違う別のルール」として増えていきます。

状態 適用先の例 ルール数
設定した直後 $B$2:$B$100 1
何度かコピペした後 $B$2:$B$4 ／ $B$6:$B$7 ／ $B$9 …（サンプルの状態） 十数〜数十
数年運用した後 断片がさらに分裂 数百〜数千

重くなる本当の原因

細切れに分かれても、判定するセルの合計は変わりません。ルールが増えて重くなるときの原因は、多くの場合次の2つです。

  1. ルール1本ごとの管理の手間（範囲の保持・再描画・ファイル内の定義）が、本数に比例して積み上がる
  2. コピペを繰り返すうちに、同じセルに複数のルールが重なることがある。この場合は、判定の回数も本数に比例して増える

見た目は何も変わらずに増えていくので、開いただけでは気づけません。まず数を確かめます。

手順

ルールがいくつあるかを数える

  1. 対象のシートを開く
  2. 「ホーム」タブ →「条件付き書式」→「ルールの管理」
  3. ダイアログ上部の「書式ルールの表示」を「現在の選択範囲」から「このワークシート」に切り替える
  4. 一覧に並んだ行数が、そのシートのルール数

一覧をスクロールしても終わらないなら、増えていると考えてよいでしょう。

適用先が断片化していないか確認する

一覧の「適用先」列を見ます。

同じ条件・同じ書式のルールが何行も並んでいれば、増えています。 適用先が $B$2:$B$4・$B$6:$B$7・$B$9 のような細切れの範囲のことも、同じ範囲の繰り返しのこともあります。本来1行で済むものが、何十行にもなっている状態です。

「ルールの管理」ダイアログで断片化を確認する

まとめて作り直す

断片を1つずつ結合するより、いったん全部消して、1つのルールで引き直すほうが速く確実です。

この操作は元に戻せないことがあります。先にファイルのコピーを取ってください。 消えるのは、いま選んでいる1シートのルールだけです。

  1. 「ルールの管理」の一覧を上から下まで見て、残したいルールをすべて控える（「ルールの編集」を開き、条件・書式・適用先を種類ごとにメモする）
  2. 対象シートのタブをクリックする
  3. 「条件付き書式」→「ルールのクリア」→「シート全体からルールをクリア」
  4. 対象範囲を選び直して、控えたルールを引き直す

今後増やさない貼り付け方

値だけを貼り付ければ、書式もルールも複製されません。

  • 貼り付け先のセルを選ぶ → Ctrl + Alt + V →「値」→「OK」

サンプルコード

ブック全体のルール数を、シートごとに表示します。削除はしないので、確認用に安全に実行できます。

ThisWorkbook を使っているので、調べたいブックそのもののVBEに貼り付けます。 新規ブックや個人用マクロブックに貼ると、貼った先のルールを数えてしまいます。

Option Explicit

Sub 条件付き書式のルール数を数える()
    Dim ws As Worksheet
    Dim msg As String
    Dim total As Long
    Dim cnt As Long

    For Each ws In ThisWorkbook.Worksheets
        cnt = ws.Cells.FormatConditions.Count
        msg = msg & ws.Name & " : " & cnt & " 件" & vbCrLf
        total = total + cnt
    Next ws

    MsgBox msg & vbCrLf & "合計 : " & total & " 件", vbInformation, "条件付き書式のルール数"
End Sub

数えるのは本数だけです。断片化や重なりは、「ルールの管理」の一覧で見ます。

大きな管理表なら、数十件あること自体は珍しくありません。数だけで決めず、「ルールの管理」の一覧で、同じ条件のルールが何行も並んでいないかを併せて見てください。

動作確認

  1. サンプルファイル（記事末尾）を開く
  2. Alt + F11 でVBEを開き、左のプロジェクトウィンドウで、開いたサンプルファイルのブックを選ぶ
  3. 「挿入」→「標準モジュール」を選び、上のコードを貼り付ける
  4. Sub 条件付き書式のルール数を数える() の中をクリックして、F5 を押す

メッセージボックスに「売上集計 : 24 件」「在庫 : 3 件」「合計 : 27 件」と表示されれば成功です。

0件だったり合計が合わなかったりするときは、コードを貼ったブックが違うことがあります（新規ブックや個人用マクロブックに貼っていないか）。

確認したら、保存せずに閉じます（.xlsx のまま保存すると、標準モジュールは残りません）。

よくあるエラー

ルールを消したら、色まで消えた

原因: 条件付き書式の色は、ルールが付けている色です。手で塗った色とは別物です。

直し方: 正常な動きです。ルールを引き直すと色が戻ります。

整理しても軽くならない

原因: ほかのシートにルールが残っているか、条件付き書式以外に原因がある可能性があります。未使用の行・列に残った書式、大量の図形・画像、切れた外部リンクなどです。

見分け方: サンプルコードで、シートごとの件数を見ます。Ctrl + End を押して、データの終わりから大きく離れたセルに飛ぶなら、未使用の範囲に書式などが残っています。

ルールが少ないのに重い（数式に INDIRECT・OFFSET・TODAY を使っている）

原因: これらは再計算が頻繁に走る関数です。条件付き書式の数式に入れると、ルールが少なくても重くなることがあります。

直し方: 作業列を1つ作ってそこで判定し、条件付き書式からは作業列を見る形にします。

「重複する値」を広い範囲に設定している

原因: 範囲内のセルを互いに比べるので、行が増えるほど重くなります。

直し方: 必要な列だけに範囲を絞ります。

まとめ

  • ルールが増えて重いときの原因は、コピペで増えたルールの数か、同じセルに重なったルールであることがほとんど
  • 「ルールの管理」で表示を「このワークシート」にして、件数と適用先を確かめる
  • 増えていたら、いったん消して1つのルールで引き直す（先にファイルのコピーを取る）
  • ふだんは値のみの貼り付けで、増えるのを防ぐ

関連記事

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました