データが修正されたセルが分かるようにする条件付き書式の設定

Excelでセルの値が修正されたことが一目で分かるようにしておくと、便利なことはよくあります。

たとえば、各部署に既存の住所一覧を配布して、データが修正されたセルに背景色が付くようにしておくと、管理者は、配布した住所一覧を回収して、色のついたセルの値のみを修正すればいいですね。

条件付き書式を使って、データが修正されたら自動的に書式が設定されるようにしておくと便利です。

2つのワークシートの比較にも活用できます。

既存のデータシートをコピー

以下のような既存のデータ（住所一覧）のシートがあります。

［Ctrl］キーを押したまま、シート見出しをドラッグして、ワークシートをコピーします。

ワークシートの名前を変更します。

ここではサンプルとして、以下のように［修正用］と［元データ］としておきます。

参考住所録を効率よく入力する方法については、以下の記事で解説しています。既存の住所録にも応用できる機能があると思います。

条件付き書式の設定

ワークシート［修正用］に条件付き書式を設定します。

まず、データを範囲選択します。ここでは、セル［B2］からセル［E11］までですね。

条件付き書式の［新しいルール］を選択

［ホーム］タブの［スタイル］グループにある［条件付き書式］から［新しいルール］を選択します。

［新しい書式ルール］ダイアログボックスが表示されます。

［ルールの種類を選択してください］から［指定の値を含むセルだけを書式設定］を選択して、［ルールの内容を編集してください］では、［セルの値］と［次の値に等しくない］を選択します。

右端の数式ボックスにカーソルを置きます。（ボックス内でクリックします。）

［元データ］のシート見出しをクリックして、セル［B2］をクリックします。

数式ボックスには、［=元データ!$B$2］と表示されます。

書式ルールの意味は、セルの値が［元データ］シートの値と等しくなければ、書式を設定するということですね。

絶対参照を相対参照へ

数式を修正します。

絶対参照のマーク［$］を外して以下のようにします。

［元データ］はシート名です。シート名がシングルクォーテーション（'）で囲まれている場合は、シングルクォーテーションは外さないようにしてください。

参考絶対参照と相対参照については、以下の記事で解説しています。

［数式を使用して、書式設定するセルを決定］を選択する場合

上記の設定を［ルールの種類を選択してください］で［数式を使用して、書式設定するセルを決定］を選択する場合は、以下のようになります。

シート名によっては、シングルクォーテーション（'）で囲む必要があります。

注意値と等しくないという条件の場合（<>）となり、アルファベットの大文字と小文字の区別ができません。

一覧の中にアルファベットの文字があれば、EXCAT関数を使った方がいいです。

アルファベットの大文字と小文字の区別もチェックする場合

書式の設定

修正があった場合のセルの塗りつぶしの色を設定します。

［書式］ボタンをクリックします。

［セルの書式設定］ダイアログボックスが表示されます。

［塗りつぶし］タブをクリックして、任意の色を選択して、［OK] ボタンをクリックします。

参考文字の色を設定したい場合は、［フォント］タブで設定してください。

ただし、セルを空白にした場合は、塗りつぶしの色は設定されますが、フォントでは空白のままになります。

空白となることが想定されるのであれば、塗りつぶしの色を設定したほうがいいですね。

［新しい書式ルール］ダイアログボックスに戻ります。

設定した内容を確認して、［OK］ボタンをクリックして閉じます。

アルファベットの大文字と小文字の区別もチェックする場合

もし、一覧表の中にアルファベットを使っているセルがあれば、以下の設定にしてください。

［ホーム］タブの［スタイル］グループにある［条件付き書式］から［新しいルール］を選択します。

［新しい書式ルール］ダイアログボックスが表示されます。

［ルールの種類を選択してください］から［数式を使用して、書式設定するセルを決定］を選択して、［次の数式を満たす場合に値を書式設定］の数式ボックスには、以下のように入力します。

シート名によっては、シングルクォテーション（'）で囲む必要があります。

EXACT関数は、アルファベットの大文字と小文字が区別されます。

参考EXACT関数については、以下の記事で解説しています。

［元データ］シートを非表示

［元データ］シートは非表示にしておきます。

元データの値を編集されたら困りますからね。

［元データ］シート見出しで右クリックして、［非表示］をクリックします。

シートを非表示にした後に、［校閲］タブの［保護］グループにある［ブックの保護］をクリックして、パスワードを設定しておくと、パスワードを知らない人は再表示ができなくなります。

解除する場合は、再度［ブックの保護］をクリックして、パスワードを入力して解除します。

参考［ブックの保護］の詳細については、以下の記事で解説しています。

シートの再表示

シートを再表示する場合は、シート見出しで右クリックして、ショートカットメニューから［再表示］をクリックします。

参考ブックの保護をかけていると、［再表示］などのメニューが無効状態になっています。

［再表示］ウィンドウが表示されますので、［表示するシート］を選択して、［OK］ボタンをクリックします。

参考ワークシートは、複数のシートをまとめて非表示にできますが、再表示するときは、まとめてできません。なので、もし複数シートを非表示設定している場合は、［ユーザー設定のビュー］に登録しておくと便利です。

Microsoft 365のExcel と Excel 2021では、まとめて再表示ができるようになっています。

複数のシートをまとめて再表示できる

［修正用］シートで操作して条件付き書式を確認

［修正用］シートのセルでデータを修正して、設定した塗りつぶしの色になるかを確認してみてください。

2つのシートを比較したい場合にも使えますね。

参考条件付き書式設定で、なぜか塗りつぶしが反映されないということがありました。

［セルの書式設定］で一度塗りつぶしを設定することで解決しています。

条件付き書式設定の塗りつぶしが反映されないセルがあった時に解決した方法

INQUIREというアドインを使用して、2つのファイルを比較して違いを表示することもできます。

条件付き書式をより強化するには

完成した表を配布する場合は、文字列のみの修正をして塗りつぶしの設定は勝手にしないようにアナウンスする必要もありますね。

同じ色の塗りつぶしを設定されたら困りますからね。

ユーザー側で設定した塗りつぶしが反映されないようにするには、以下の条件を追加しておくことをお勧めします。

［同じ値ならば、塗りつぶしは［色なし］］というに条件付き書式を追加します。

塗りつぶしの色は［白］を選択しても構いませんが、塗りつぶしで白を設定すると、目盛り線（枠線）が消えてしまいますので、罫線を設定していない場合は［色なし］がお勧めです。

条件付き書式は、後から追加したものが優先されます。

こうしておくと、塗りつぶしを設定されても条件付き書式の塗りつぶしが有効になるため、ミスを防ぐことができます。

条件付き書式の適用順位は、△▽の移動ボタンで変更できます。ここでの設定の場合は、適用順位は関係ありません。

参考条件付き書式を設定したブックを繰り返し使うような場合は、テンプレートとして保存しておくと便利です。バックアップにもなります。

以下の記事では、ここで作成したブックをもとに解説していますので、分かりやすいと思います。

条件付き書式の編集と削除

条件付き書式の内容を編集したい場合は、条件付き書式を設定している範囲内のセルをアクティブにして、［ホーム］タブの［スタイル］グループにある［条件付き書式］から［ルールの管理］をクリックします。

［条件付き書式ルールの管理］ダイアログボックスが表示されます。

［ルールの編集］や［ルールの削除］ボタンがありますので、必要な操作をおこなってください。

注意この時、［書式ルールの表示］が既定では［現在の選択範囲］となります。

▽ボタンをクリックして、［このワークシート］を選択して確認してください。選択範囲が異なる場合、目的の条件付き書式が表示されません。

［ルールの編集］ボタンをクリックすると、［書式ルールの編集］ダイアログボックスが表示されます。

ここで書式設定などを変更できます。

条件付き書式が設定された範囲を確認するには

条件付き書式が設定された範囲を確認する方法です。

［ホーム］タブの［編集］グループにある［検索と置換］をクリックして、メニューから［条件付き書式］をクリックします。

条件付き書式が設定された範囲を確認できます。

時々、書式設定ができないという質問をいただくことがありますが、原因は条件付き書式だったということが多いです。

セルの色を消したいのに消せない

参考新規データを入力したら罫線を自動的に追加されるようにするのも条件付き書式で設定できます。

同じシートにある2つの表を比較して重複した値をチェックする場合も条件付き書式が便利です。

条件付き書式は、コピーもできます。

以下は、条件付き書式に関するページです。