(Excel)重複データに色を付ける方法

 Excelで、値が他のセルと重複するセルに色を付けたり、複数列の値が重複する行に色を付ける方法についてです。
 条件付き書式の機能を使って実現できます。

値が重複するセルに色を付ける例

重複するセルにだけ色を付ける場合

 次の画像内のB3:D6の範囲に果物の名前が並んでます。
 互いに重複するものがいくつかありますので、その重複しているセルに色を付けるものとします。
 以下ではメニュー操作のみで色を付ける方法と数式を使う方法の2つの方法を紹介します。

 


 まず1つ目のメニュー操作による方法です。
 色付けの対象となる範囲であるB3:D6の範囲を選択し、その状態で「ホーム」タブから「条件付き書式」→「セルの強調表示ルール」→「重複する値」と選択します。

 


 するとダイアログ(小さいウインドウ)が現れるとともに、値が重複しているセル(もも、りんご、キウイ)に色が付きます。

 


 ダイアログの右側にあるプルダウンを操作すると配色を変更することができます。
 選択肢のうち最も下の「ユーザー設定の書式」を選択するとより細かい設定ができます。

 


 次は2つ目の方法です。ちょっと難しくなりますが、以降で紹介する応用例ではこのやり方が必要となります。
 色付けの対象となるB3:D6の範囲を選択し、その状態で「ホーム」タブから「条件付き書式」→「新しいルール」と選択します。

 


 最初の例とは異なる大きめのダイアログが現れます。
 ここで中央付近にある「数式を使用して、書式設定するセルを決定」を選択します。
 そしてその下の数式欄に「=COUNTIF($B$3:$D$6,B3)>1」という数式を入力(詳細は下記参照)し、右下の「書式」をクリックします。

 数式欄の数式

=COUNTIF($B$3:$D$6,B3)>1

 条件に該当する値がいくつあるかカウントするCOUNTIF関数を使った数式です。これによりB3:D6の範囲にそれぞれのセルの値がいくつ存在するかがカウントされ、1より大きい(2以上、つまり重複がある)場合には、以降で設定する書式が適用されます。
 第1引数(範囲)の指定に絶対参照($)を用いて固定することと、第2引数(条件)に最も左上のセルであるB3セルを指定するところがポイントです。



 上記の画面で「書式」をクリックしたところですが、新たなダイアログが出現します。
 ここで「塗りつぶし」タブを選択して塗りつぶし色を設定します(他のタブから太字や罫線などの設定も可能です)。
 そして(画像では見えませんが)下部にあるOKをクリックし、最初のダイアログに戻ってさらにOKをクリックすると設定が完了します。

 


 以上で値が重複しているセルに色が付きました。

他の列にまで色を付ける場合

 次の画像のデータでは果物名が縦1列に並んでいます(C列)。
 果物名が重複していたら、そのセルだけでなく左右の列にまで(つまりB~D列の3列)に色を付けるものとします。

 


 色付けの対象となるB3:D14の範囲を選択し、「ホーム」タブにある「条件付き書式」→「新しいルール」を選択します。

 


 ダイアログの中央部にある「数式を使用して、書式設定するセルを決定」を選択します。
 そしてその下の数式欄に「=COUNTIF($C$3:$C$14,$C3)>1」という数式を入力(詳細は下記参照)し、右下の「書式」をクリックします(以降の設定は上記の例と同様)。

 数式欄の数式

=COUNTIF($C$3:$C$14,$C3)>1

 この数式により、(B3:D14の範囲にある各セルからみて)同じ行のC列の値がC3:C14の範囲にいくつあるかカウントされます。そして結果が1より大きい(重複がある)場合に書式が適用されます。
 COUNTIF関数の第2引数を「$C3」としているところがポイントです。果物名が入っているセルのうち最も上のセルを指定するとともに「$」を使って列を固定しているのがポイントで、この「$」がないと(B3:D14の範囲にある)各セルが自身のセルの値をカウントしてしまうのでうまくいきません。



 設定を完了すると3つの列に色が付きました。

複数列の値が重複する行に色付けする方法

データが2列の場合

 次の画像ではB,Cの2つの列に産地名と果物名が並んでいます。
 2つの値がともに他の行と重複するか判定し、重複する行については2つの列に色を付けるものとします。

 


 B3:C14の範囲を選択し、「ホーム」タブにある「条件付き書式」→「新しいルール」を選択します。

 


 ダイアログが出現するので、中央部にある「数式を使用して、書式設定するセルを決定」を選択します。
 そしてその下の数式欄に「=COUNTIFS($B$3:$B$14,$B3,$C$3:$C$14,$C3)>1」という数式を入力(詳細は下記参照)し、右下の「書式」をクリックします(以降の設定は上記の例と同様)。

 数式欄の数式

=COUNTIFS($B$3:$B$14,$B3,$C$3:$C$14,$C3)>1

 複数条件に該当するセルや行、列をカウントするCOUNTIFS関数を使った数式です。
 これにより「同じ行のB列の値とB列の値が同じで、かつ、同じ行のC列の値とC列の値が同じである行の数」がカウントされます。結果が1より大きければ重複する行があることになり、書式が適用されます。
 ここでも第2引数及び第4引数(条件)を「$B3」「$C3」とし、列のみを固定するのがポイントです。



 設定を完了すると、産地と果物の両方が他と重複している行(「岡山県 もも」と「長野県 りんご」の2通り)の2つの列に色が付きました。

他の列にまで色を付ける場合

 次の画像ではC,Dの2つの列に産地名と果物名が並んでいます。
 2つの値がともに他の行と重複するか判定し、重複する行についてはB~Eの4つの列に色を付けるものとします。

 


 色付けの対象となるB3:E14の範囲を選択し、「ホーム」タブにある「条件付き書式」→「新しいルール」を選択します。 

 


 ダイアログの中央部の「数式を使用して、書式設定するセルを決定」を選択します。
 そしてその下の数式欄に「=COUNTIFS($C$3:$C$14,$C3,$D$3:$D$14,$D3)>1」という数式を入力(詳細は下記参照)し、右下の「書式」をクリックします(以降の設定は上記の例と同様)。

 数式欄の数式

=COUNTIFS($C$3:$C$14,$C3,$D$3:$D$14,$D3)>1

 ここでも1つ上の例と同じくCOUNTIFS関数を使っています。
 これにより「同じ行のC列の値とC列の値が同じで、かつ、同じ行のD列の値とD列の値が同じである行の数」がカウントされます。結果が1より大きければ重複する行があることになり、書式が適用されます。



 設定を完了すると4つの列に色が付きました。

 なお上記の式の内容は、実は1つ上の例と同じであり、産地と果物が記録されている列が1つ横に動いているぶん、参照する列も1つ横に動いているだけの違いしかありません。こちらのやり方で4つの列に色が付くのは、最初にB3:E14の範囲を選択しているからに過ぎません。これは複数条件の場合に限らず、条件が1列だけであっても同様です。