PR
私が使っているPCガジェット類
作業環境で実際に使っている・気になっている周辺機器などをまとめました
※ 一部のリンクは広告(アフィリエイト)を含みます
Excelの条件付き書式が増える原因 マクロなしで減らす方法
Excelの条件付き書式が増える現象に、心当たりはありませんか？
ルールの管理を開いたら、中身のほとんど同じルールが何十行も並んでいた、という場面です
結論から言うと、増える引き金そのものはMicrosoftが公式に書いている動きで、Excelの不具合ではありません
原因がはっきりしているので、マクロを使わなくても掃除できます
私は条件付き書式をなるべく使わない派なので、使わずに済ませる手も後半に置きました
ジャベ雄私も月次の帳票を開いて、同じ画面で固まりました
目次
条件付き書式が増えるとどうなるか
- 「条件付き書式ルールの管理」に、ほとんど同じルールが何十行も並ぶ
- 適用先が $A$2:$A$5 / $A$6 / $A$7:$A$9 のように細切れになる
- セルを1つ書き換えただけで画面の更新がもたつく
- 色を変えようとルールを直したのに、一部の行だけ前の色のまま残る
実務でこたえるのは下の2つで、直したのは1本目のルールだけ・残りは手つかず、という形で出ます
なぜ増えるのか コピーと貼り付けが引き金
いちばん確かな原因はコピーと貼り付けです
Microsoftの公式ページ「条件付き書式を使用して情報を強調表示する」に、そのままの説明があります
条件付き書式が設定されたセルをコピーして貼り付ける・範囲をフィルする・書式のコピー/貼り付けを使う
これらの操作をすると、コピー元のセルに基づいてコピー先に新しいルールが作成される、と書かれています
日本語版は優先順位の変化とまとめて1文になっていますが、英語版を見るとルールが作られる部分は言い切りです
可能性がかかっているのは優先順位のほうだけです
貼り付け先のルールが置き換わるのではなく、新しいルールが足されるところが要点です
先月のシートをコピーして今月分にする、という使い方を繰り返せば、増殖と呼びたくなる形で層になって積み上がります
相対参照だと別のルールとして増える
条件式に相対参照を使っていると、コピー先では参照がずれた式になり、Excelから見れば別のルールなので1本にまとまらない、と説明されています
=AND($D2<TODAY(), $E2="未完了")これは窓の杜が挙げている例で、コピーのたびに参照が D12・D13 と変化していきます
行の挿入や削除・適用先の分割についても、解説サイト側に同じ説明があります
相対参照・行の挿入や削除・適用先の分割の3つは、Microsoftの明文を見つけられなかったので、そう説明されている、というところまでにしておきます
ジャベ雄コピーで増える、まずはここだけ押さえれば十分です
どこまで増えるのか・どこが重くなるのか
Excelは構造上、数十万個のルールを抱えられます
Microsoftのパフォーマンス改善の資料(英語)には、44,000セルで合計386,000個のルールを持つブックが出てきます
ただしこれはコピー性能を測るために作られたテスト検体で、実務のブックがこうなるという話ではありません
ルール数の上限は仕様と制限のページに見当たらないので、何個までなら大丈夫という基準は判断できません
遅くなる理由は再計算のほうにある
パフォーマンス最適化のドキュメント(英語)に、条件付き書式とデータの入力規則は便利だが大量に使うと計算が著しく遅くなるという一文があります
表示中のセルは計算のたびに、そして画面が更新されるたびに数式が評価される、とも書かれています
一緒に名前が挙がる入力規則は、選んだ分類で候補を絞り込む使い方なら気にしなくて大丈夫です
反証も置いておきます
同じ資料では、条件付き書式を含むセルのコピー貼り付けが Excel 2010 で70秒、2016 では7秒まで縮んでいるので、入れると激重になるとは言い切れません
速くなったのはコピー処理のほうで、計算のたびに評価される話は現行のドキュメントにも残ったままです
ファイルサイズは分けて考えます
Microsoftが条件付き書式と肥大を結びつけているのは条件付き書式が破損しているときだけで、サイズの原因としてまず挙がっているのは列全体・行全体への色や罫線です
増えたルールを掃除する(マクロなし)
マクロを動かせない環境もあるので、ここではExcelの標準機能だけで片付ける手順を並べます
まず全体を見る
STEP
ルールの管理を開く
ホームタブの「条件付き書式」から「ルールの管理」を選びます
Microsoftのページでも案内されている入口です
STEP
表示を切り替える
「書式ルールの表示」の欄を、シート全体が対象になるように切り替えます
選択範囲だけが対象のままだと、選んだセルにかかるルールしか出てきません
STEP
適用先の列を見る
同じ書式のルールが細かい範囲に分かれて並んでいたら、増えている状態です
条件付き書式が付いているセルの場所を知りたいときは、公式が案内している2手の操作を使います
まず条件付き書式が設定されていないセルをクリックして、それからホームタブの「検索と選択」で「条件付き書式」を選ぶ、という順です
1手目のセル選びを飛ばすと結果が変わります
1本だけ残して適用先を広げる
ルールの中身が同じなら残すのは1本で足ります
残した1本の適用先を本来当てたい範囲まで広げ、あとは削除する、という直し方です
適用先はダイアログ右側のボックスで編集します
ボックス横のボタンでダイアログを縮め、シート上で範囲を選び直す方法が案内されています
この直し方がそのまま通じるのは、条件式が絶対参照だけでできているルールです
数式は適用先の左上のセルを基準に相対参照が読まれると説明されているので、範囲を広げると指す行がずれます
エラーにならず色だけ違う行に付く形で出るので、広げたあとは左上のセルと条件式の行番号が噛み合っているか見てください
重複したルールをまとめる機能は案内されていません
ルールの管理は1ルールずつ適用先を直す作りなので、掃除は手作業になります
数が多いなら全部消して貼り直す
ルールが数十本まで育っているなら、1本ずつ直すよりいったん全部消すほうが早いです
STEP
控えを取る
別名で保存しておきます
クリアした直後なら取り消しで戻せますが、保存して閉じたあとは戻せません
STEP
ルールをクリアする
条件付き書式の「ルールのクリア」から「シート全体からルールをクリア」を選びます
範囲を絞るなら「選択したセルからルールをクリア」です
STEP
範囲を選んでから1本だけ作る
使う予定の行を先に全部選んだ状態で、正しいルールを1本だけ作ります
ここで書式のコピー/貼り付けを使うと、増える引き金をそのまま踏むことになります
クリアはそのシートの条件付き書式を全部消します
残したいルールがあるなら、条件式と書式の中身を控えてから実行してください
この「全部消して貼り直す」型は、解説サイトで繰り返し出てくる直し方です
Microsoft自身も、ブックを軽くするための資料(英語)で同じ順を番号付きで案内しています
バックアップ → シート全体からルールをクリア → 別名で保存 → 症状の確認、という流れです
ただしこれはファイルが重い・壊れている疑いを切り分けるための手順なので、増えたルールの整理には読み替えて使うことになります
整理用のマクロを使うなら、ブックでマクロを動かせる状態にするところから先に片付けることになります
ジャベ雄掃除の前に別名保存、ここだけは省かないでください
そもそも増やさない使い方
掃除より、増やさない側に手を打つほうが手間は少なくて済みます
貼り付ける中身を選ぶ
引き金が貼り付けなので、貼り付ける中身を選べば理屈のうえでは増えません
Microsoftの「貼り付けのオプション」には「値」「値と数値の書式」が並んでいます
条件付き書式を結合して貼る項目も同じ一覧にあります
値のみで貼れば適用範囲の分割を防げる、という説明は解説サイト側のものです
公式に書かれているのは選択肢の存在までなので、どれくらい防げるかは自分のブックで一度試してみてください
適用先と条件式を先に整える
行を足すたびに書式をコピーするから増えるので、使う予定の範囲まで最初に当てておけばコピーが要りません
先に100行ぶん指定しておく、といった運用が紹介されています
ただし広く取るほど良い話ではないので、必要な範囲にとどめるのが無難です
相対参照が原因なら、参照がずれない書き方にすれば増えません
紹介されているのはINDEXとROWで行を指す形で、式の見た目が変わらないぶんルールが1本のまま保たれるという理屈です
=AND(INDEX($D:$D,ROW())<TODAY(),INDEX($E:$E,ROW())="未完了")私はまだ手元で確かめていないので、採用する前に自分のブックで動きを見てもらえればと思います
表示形式で代わりを作る
金額の色分け・マイナスの見せ方・ゼロを消す、この辺りは表示形式(ユーザー定義)で足ります
セルを選んで Ctrl + 1、分類の「ユーザー定義」に書式コードを入れる場所です
書式コードは最大4セクション
書式コードはセミコロンで区切った最大4セクションで、Microsoftのガイドラインによると順番は 正の数 / 負の数 / ゼロ / 文字列 です
つまずくのは全部書かなかったときです
2つしか書かないと1つ目が正の数とゼロの両方に使われるので、ゼロを消すなら3つ目のセクションを空にします
飛ばすセクションの終わりのセミコロンは残す決まりです
#,##0;-#,##0;;@指定できる色は黒・青・水・緑・紫・赤・白・黄の8色だけで、▲のような文字は二重引用符で囲みます
書式コードの中に条件が書ける
あまり知られていませんが、書式コードの中には簡単な条件も書けます
角括弧に比較演算子を入れる形です
[赤][<=100]#,##0;[青][>100]#,##0100以下なら赤・それより大きければ青、という色分けがセルの属性だけでできます
肝心なのはここで、表示形式はコピーしても書式が移るだけなのでルールとして積み上がりません
セルの属性なので、数式の計算チェーンにも乗りません
この書式コードを実際の帳票に当てた例は、見積書を関数だけで組む記事のほうに置いてあります
金額欄のゼロ非表示や、値引き行を赤い▲で出す書き方はそのまま真似できると思います
あわせて読みたい
Excelで見積書を作るなら知っておきたい関数と機能
エクセルで見積書を作るときに要る関数と機能を、作り方の手順ではなく部品ごとに整理しました テーブル・XLOOKUP・FILTER・表示形式・入力規則・シート保護まで、マクロを使わず関数とExcel標準機能だけで組む前提でまとめています
色を使わず、判定用の列を1本作って文字で出す手もあります(白黒で印刷しても伝わります)
あわせて読みたい
Excelのスピルとは 使い方と動的配列関数まとめ
Excelに実装されたスピルですが、最初のころは気にしていなかったし、なんなら使わないようにしていたぐらいでした 意識して使ってみると結構便利だったので、自分の深…
それでも条件付き書式が勝る場面
表現力は条件付き書式のほうが上で、表示形式では届かないところがあります
スクロールできます
| 観点 | 表示形式(ユーザー定義) | 条件付き書式 |
|---|---|---|
| 使える色 | 8色のみ | 自由に指定できる |
| セルの塗りつぶし | できない | できる |
| 条件の書き方 | 書式コード内に簡単な条件 | 数式で複雑な条件 |
| データバー・カラースケール | なし | あり |
| コピー貼り付け | 書式が移るだけ | 新しいルールが作られる |
| 再計算 | セルの属性なので対象にならない | 表示中は毎回評価される |
重複チェックの色付け・進捗のデータバー・ヒートマップのように、セルの塗りで見せる表現には条件付き書式が要ります
切り分けとしては手元で分析する表なら条件付き書式・コピーして配りまわす帳票なら表示形式、と考えると迷いにくいと思います
私が帳票に条件付き書式を入れないのは、機能が悪いからではありません
コピーで増える性質と、毎月コピーして使う運用が噛み合わないからです
ジャベ雄自分だけが見る分析用の表では、私も条件付き書式を使います
まとめ 条件付き書式が増える前にできること
- 増える原因はコピー・貼り付け・フィル・書式のコピー、ここは公式に書かれた動きなので不具合ではない
- 掃除は1本残して適用先を広げるか、全部クリアして貼り直すかの2択
- 増やさないなら貼り付ける中身を選ぶ、適用先は使う範囲まで先に取っておく
- 帳票の色分けは表示形式で代わりが作れる、こちらはコピーしても増えない
ルールの管理を開いて数が多いと感じたら、いきなり消す前に別名で保存しておいてください
残すルールを1本決めるところから始めれば安全です
最後に・・・
PythonとExcelを中心に仕事に役立つ業務ツールや自動化、スクレイピングツールの作成を受注していて、クラウドワークスでは気が付けば100件以上のお仕事を受注してきました！
会社員をやりながらの副業なので時間の捻出は相応ですが、クライアントの方々と近い立場でこちらからも提案しながら活動していますのでお悩みあれば是非ご相談ください
コメント