Google検索

Office2019 Excel2019

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

投稿日:2020年2月7日 更新日:

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

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

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

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

修正されたセルに塗りつぶしを設定

既存のデータ一覧をコピー


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

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

シート見出しをコピー

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

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

シートの名前を編集

条件付き書式の設定

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

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

範囲選択

条件付き書式の[新しいルール]を選択

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

[条件付き書式]から[新しいルール]を選択

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

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

[新しい書式ルール]ダイアログボックス

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

数式ボックスでクリック

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

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

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

数式の設定

絶対参照を相対参照へ

数式を修正します。

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

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

=元データ!B2

絶対参照を外して相対参照へ

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

相対参照と絶対参照と複合参照は【 F4 】キーで切替

セルの参照方法は、相対参照、絶対参照、複合参照があります。 絶対参照と複合参照では、[$]を挿入する必要があります。 このとき、直接入力して ...

続きを見る

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

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

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

=B2<>元データ!B2

[ルールの種類を選択してください]で[数式を使用して、書式設定するセルを決定]を選択

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

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

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

書式の設定

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

[書式]ボタンをクリックします。

[新しい書式ルール]の[書式]ボタン

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

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

[セルの書式設定]ダイアログボックス

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

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

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

[セルの書式設定]の[フォント]タブ

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

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

[新しい書式ルール]ダイアログボックス

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

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

アルファベットを含むセルがある住所一覧

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

[条件付き書式]の[新しいルール]

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

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

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

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

=EXACT(B2,元データ!B2)=FALSE

[新しい書式ルール]ダイアログボックスで数式を使用して設定

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

2つのセルの文字列を比較して同じ文字列かどうかを確認する方法

Excelで2つのセルに入力された文字列が合っているかどうかを確認する方法です。 下のようにB列とC列に文字列が入力されています。 B1=C ...

続きを見る

[元データ]シートを非表示

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

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

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

シートの非表示

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

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

[校閲]タブの[ブックの保護]

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

シートの操作(シート名の変更・再表示など)を制限するブックの保護

Excelの[校閲]タブにある[ブックの保護]とは、シートに関しての操作を制限する機能です。 シートの挿入や削除や、シート名の変更、表示/非 ...

続きを見る

シートの再表示

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

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

シート見出しで右クリック

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

[再表示]ウィンドウ

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

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

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

条件付き書式の確認

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

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

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

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

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

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

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

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

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

=EXACT(B2,元データ!B2)=TRUE

書式ルールの編集

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

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

条件付き書式ルールの管理

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

[条件付き書式ルールの管理]の移動ボタン

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

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

[条件付き書式]の[ルールの管理]

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

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

[条件付き書式ルールの管理]ダイアログボックス

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

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

[書式ルールの表示]で[このワークシート]を選択

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

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

[書式ルールの編集]ダイアログボックス

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

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

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

[検索と置換]の[条件付き書式]

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

条件付き書式が設定された範囲

時々、書式設定が出来ないという質問をいただくことがあります。

条件付き書式が設定されていたことが原因だったということが多いです。

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

参考条件付き書式は、コピーすることができます。

条件付き書式のコピーとルールの編集

Excel2010に限ったことではありませんが、条件付き書式は、コピー、貼り付けして使用することができます。 条件付き書式の設定 たとえば、 ...

続きを見る

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

アクティブセルの行に色を付ける

Excelで横や縦に長い表の場合、アクティブセルのある行全体や列全体に塗りつぶしを設定すると、見やすくなることがあります。行番号、列番号を見 ...

続きを見る

条件付き書式のデータバーを活用して点数をグラフ化

Excelの条件付き書式のデータバーを使用すると、セルの中に数値をグラフのように表示することができます。 Excel2010で試験の点数をデ ...

続きを見る

条件付き書式のアイコンセットの区分け

Excel2010以降の条件付き書式の[アイコンセット]は便利ですが、アイコンセットのアイコンの種類を区分けする範囲は既定で決められています ...

続きを見る

空白セル(0表示)をグラフに反映させたくない

Excelで下のような表を元に複合グラフを作成しました。 2011年度のデータは、7月までしかありません。 E列では、累積を求める式を入れて ...

続きを見る

2つの列の差分を求める(重複しない一意の値を求める)

Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。 たとえば、下のようにA列には分析用データとして別 ...

続きを見る

セル範囲の中から数値に見える文字列のセルを見つけるには

Excelのセルに数値が入力されていると思っても、実際は文字列だったということがあります。 見た目は数値に見えても表示形式は文字列になってい ...

続きを見る

小数点の位置を揃えるには(小数点以下の異なる桁数が混在する場合)

一列の複数セルに小数点以下の桁数の異なる数値が並んでいる場合、小数点の位置は揃いません。 小数点以下の異なる桁数の数値が混在していても、小数 ...

続きを見る

Profile

執筆者/はま

おすすめ記事と広告

-Office2019 Excel2019

Copyright © 2001-2020 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2020 AllRights Reserved Powered by AFFINGER4.