この記事に出てくる言葉
- 条件付き書式 … 「100より大きければ赤」のように、中身に応じてセルの色を自動で変える機能
- ルール … その「◯◯なら△△の色」1本ぶんの設定。1枚のシートに何本でも置けます
- 適用先 … そのルールがどのセルに効くか。
$B$2:$B$120のような範囲で記録されています - 共同編集 … TeamsやOneDriveに置いたファイルを、複数の人が同時に開いて直すこと
結論：ルールの一覧を［このワークシート］に切り替えて、何本あるか見る
最初にやること
- 色がおかしいシートを開きます
- ［ホーム］タブの［スタイル］にある［条件付き書式］を押し、［ルールの管理］を選びます
- 画面の上のほうにある［書式ルールの表示］を［このワークシート］に変えます
- 一覧に並んだ行の数と、右側の［適用先］の欄を見ます
- 見るだけで終えるときは［キャンセル］を押して閉じます
［キャンセル］で閉じれば、この画面でしたことは何もシートに反映されません。
ここで見えたものが、次にやることを決めます。
| 一覧の見え方 | 状態 | 行き先 |
|---|---|---|
| 自分が作った本数と同じ | 増えていません | この記事の原因ではありません |
| 同じ内容の行が数本から十数本 | 増えています | 下の「数本なら、1本残して適用先を書き直す」 |
| 同じ内容の行が何十本も並ぶ | かなり増えています | 下の「何十本もあるなら、全部消して引き直す」 |
| ［適用先］がカンマ区切りで長く続く | 範囲が割れています | 同じく下の2つのどちらか |
［適用先］が $B$2:$B$7,$B$9:$B$40,$B$42:$B$118 のようになっていたら、範囲が割れています。
抜けている行にだけ色が付かないのは、これが理由です。
直す前に、控えを取る
⚠️ 共有ファイルは、直した瞬間に全員の画面が変わります
TeamsやOneDriveに置いたファイルは、自動保存（保存を押さなくても変更が随時保存される設定）が
オンだと、直した内容が数秒で他の人の画面に届きます。
［OK］を押してルールを消せば、その場で全員の色が消えます。
自動保存がオンのままでは、自分の画面だけで先に試すことはできません。
- ［ファイル］を開き、［コピーの保存］を押して、別の名前で保存します
- 保存したあと、画面の上に出ているファイル名を見ます。コピーのほうが開いていたら、元のファイルを開き直します。直すのは元のファイルで、コピーは控えとして置いておきます
- ［ルールの管理］を［このワークシート］にした状態で、一覧の画面を写真に撮るか、条件と色を書き写します
- 一覧の「ルール」の欄が途中で切れて読めないときは、その行を選んで［ルールの編集］を押します。条件の全文が出ます
戻したいときの行き先は2つあります。
押した直後なら、Ctrl と Z を押します。
保存されたあとなら、前の版を呼び出します。
Microsoft 365では画面上部のファイル名をクリックして［バージョン履歴］、
Excel 2016から2021では［ファイル］→［履歴］です。
これが使えるのは、OneDriveかSharePointに置いたファイルだけです。
前の版へ戻すときは、他の人が全員そのファイルを閉じるまで待ちます。
数本なら、1本残して適用先を書き直す
一覧の中で［適用先］がいちばん広い行が、たいてい元の1本です。
それを残して、同じ中身の行を消していきます。
1本だけ残す手順
消してよいのは、残す1本と中身が同じ行だけです。
条件も色も違うルールを消してしまったら、［OK］を押さずに［キャンセル］を押します。
この画面での削除は［OK］か［適用］を押すまでシートに反映されないので、消した行は戻ります。
一覧が元の本数に戻るので、消すところからやり直します。戻っていなければ Ctrl と Z を押します。
［OK］を押したあとで気づいたときは、Ctrl と Z を押します。
共有しているファイルなら、上の「直す前に、控えを取る」を先に済ませてください。
共有しているファイルで［OK］を押すと、消えた状態が数秒で全員の画面に届きます。
- ［条件付き書式］→［ルールの管理］を開き、［書式ルールの表示］を［このワークシート］にします
- 残す1本以外の行を1つクリックします
- ［ルールの削除］を押します。一覧からその行が消えます
- 残す1本だけになるまで、2と3を繰り返します
- 残した1本をクリックし、［適用先］の欄にある［ダイアログ縮小］を押します。画面が細長くなります
- シートの上で、色を付けたい範囲をドラッグして選び直します
- ［ダイアログ展開］を押して、元の大きさに戻します
- ［OK］を押します。ここでシートの色が変わります
- 色が付くはずのセルに色が付き、付かないはずのセルに付いていなければ終わりです
この画面には［重複ルール］というボタンが並んでいます。
名前とは逆で、重複を消すボタンではありません。
選んでいるルールをもう1本増やすボタンです。
減らしたいときに押すと、1本増えます。
何十本もあるなら、全部消して引き直す
一覧の行は、1つ選んで［ルールの削除］を押して消す形です。
何十本もあれば、その回数だけ繰り返すことになります。
一般の解説記事がここからマクロの話になるのは、そのためです。
画面の操作だけで進めるなら、いったん全部消して引き直すのが最後の手段になります。
⚠️ 消えるのはシート1枚ぶんの全部です
この操作で消えるのは条件付き書式のルールです。
手で塗った塗りつぶしや文字色は残ります。
消えるのは、そのシートにあるルールの全部です。
自分が作ったものと他の人が作ったものは区別されません。
共有しているファイルなら、消えた状態が数秒で全員の画面に届きます。
引き直すのは自分なので、内容を控えていないルールは戻せません。
上の「直す前に、控えを取る」を済ませてから押してください。
- ルールを消したいシートを開きます
- ［ホーム］タブの［条件付き書式］を押します
- ［ルールのクリア］にマウスを合わせ、［シート全体からルールをクリア］を選びます
- シートから条件付き書式の色が消えます。手で塗った色だけが残った状態になります
- 色を付けたい範囲をドラッグして選びます
- ［ホーム］タブの［条件付き書式］を押し、控えたのと同じ項目（［セルの強調表示ルール］や［新しいルール］）から、同じ条件と同じ色で作り直します
- ［ルールの管理］を［このワークシート］にして、本数が控えた数と同じか見ます
ここまでやってもファイルが重いままなら、原因は別のところにあります。
Excelのファイルが重い・空の行まで表が続くとき に、
最後のセルの直し方をまとめています。
直しても、また増えます
Microsoftは、条件付き書式が設定されたセルをコピーして貼り付けたり、
セルの右下をつまんで下まで引き伸ばしたり、［書式のコピー/貼り付け］を使ったりすると、
コピー先に新しい条件付き書式のルールが作成されることがある、と説明しています。
増やしているのは、ルールを触った人ではありません。ふつうに貼り付けた人です。
貼り付けが色の付いた範囲の途中に入ると、そのセルだけがルールから外れます。
外れた分だけ［適用先］が切れて、カンマ区切りの長い範囲になります。
行の挿入や削除、並べ替えでも同じことが起きることがあります。
条件の式に $A2 のような行番号付きの参照を使っていると、
貼り付け先では $A12 のように書き換わります。
式が違えば別のルールとして登録されるので、本数のほうが増えていきます。
片づけても、貼り付けが続くかぎり戻ります。
増やさないために、相手に何をお願いするか
本数が増えるのを止められるのは、貼り付ける側の操作だけです。
お願いする内容は1つで足ります。
そのまま送れる文
このファイルに貼り付けるときは、［値］で貼ってください。
やり方は次の2つのどちらでも同じです。
- 右クリックして、［貼り付けのオプション］の中の「123」と書かれたアイコンを押す
- Ctrl と Alt と V を押し、［値］を選んで［OK］を押す
数式のまま貼りたいときは、［値］の代わりに［数式］を選びます。
どちらも書式は持ち込まれません。
まちがえてふつうに貼ってしまったら、Ctrl と Z を押して戻し、上のどちらかで貼り直してください。
⚠️ 全員がやらないと止まりません。
1人がふつうに貼れば、その1回でまた増えます。
自分のシートだけ片づけても、翌日には戻っていることがあります。
貼り方を全員に頼めない相手なら、片づけるのは月に1回と決めて、
そのたびに上の「数本なら、1本残して適用先を書き直す」を回すほうが現実的です。
適用先を列全体にする案は、どこまで効くか
同じ相談への回答として、［適用先］を $A:$Z のように列ごと指定する案が出ています。
範囲が列の端から端までなら、行を足しても削っても切れ目ができない、という理屈です。
行の挿入や削除で範囲が割れるほうは、これで起きにくくなります。
一方、貼り付けで新しいルールが作られるのは別の動きなので、こちらは止まりません。
貼り方が変わらなければ、本数は同じように増えていきます。
列全体を指定すると、データの入っていない空のセルまで判定の対象になります。
使っていない行が100万行ぶん残るので、ファイルが重くなる原因として挙げられています。
表の下に少し余裕を持たせた範囲にしておくほうが無難です。
確認範囲
- Windows版のExcelの画面で書いています。Mac版とブラウザ版はボタンの並びが違います
- ボタンの名前は版によって変わることがあります。［重複ルール］は英語版の Duplicate Rule にあたります
- 前の版に戻せるのは、OneDriveかSharePointに置いたファイルだけです
- ［形式を選択して貼り付け］を開くショートカットは、Ctrl と Alt と V です
- ルールが増える原因は他にもあります。ここでは貼り付けと行の操作で増える場合を扱いました
コメント