2つの表を比較して重複しているデータに塗りつぶしを設定する

同じような2つの表があり、2つの表から重複する値をチェックしたい場合の方法です。

条件付き書式から簡単にチェックすることができます。

また、1つの列の値だけでなく複数列でともに同じ値のデータをチェックしたい場合もあると思います。

その場合は、COUNTIFS関数を使用します。

2つの表で重複する値をチェックする

以下のような2つの表があります。

名前の列だけをチェックして、2回目の参加者の中に1回目の参加者がいないかを確認します。

条件付き書式の［重複する値］でチェック

まず、2つの表の名前のセル範囲を選択します。

セル［B4］からセル［B9］までを範囲選択して、

［Ctrl］キーを押したまま、セル［F4］からセル［F14］までを範囲選択します。

2つの表の名前のデータを選択したまま、［ホーム］タブの［スタイル］グループにある［条件付き書式］から［セルの強調表示ルール］-［重複する値］をクリックします。

［重複する値］ダイアログボックスが表示されます。

［重複］が選択されていることを確認して、［OK］ボタンをクリックします。

［書式］は、プルダウンから選択することができますが、ここでは既定のままにします。

結果、以下のようになります。

ただ、第2回講習会では参加者の中に同姓同名者がいます。

1回目に参加したのは、どちらなのかは名前も住所も一致するデータをチェックする必要があります。

COUNTIF関数で重複値をチェック

条件付き書式は使用せずに、COUTTIF関数を使用してチェックすることもできます。

COUNTIF関数は、指定した範囲の中から、検索条件に一致するデータの個数を返す関数です。

COUNTIF 関数 - Microsoft サポート（Microsoft）

まず、重複をチェックするための列を追加します。

セル［H4］を選択して、以下の数式を入力します。

COUNTIF関数の範囲にセル［B4］からセル［B9］を指定して、［検索条件］にセル［F4］を指定します。

関数の引数ダイアログボックスを表示すると、以下のようになります。

セル［H4］の数式をオートフィルで下へドラッグしてコピーします。

フィルハンドルをダブルクリックしてもOKです。

COUNTIF関数は、検索条件に一致するセルの個数が返されるので、［1］が返された場合は重複セルということです。

条件付き書式で指定範囲に塗りつぶしを設定

右の表に条件付き書式で、選択範囲に塗りつぶしを設定します。

条件付き書式を設定する場合は、以下の3つの要件が必要になります。

条件付き書式を設定したい範囲を選択します。ここでは、セル［E4］からセル［G14］を選択します。

そして、［ホーム］タブの［スタイル］グループにある［条件付き書式］から［新しいルール］をクリックします。

［新しい書式ルール］ダイアログボックスが表示されますので、［数式を使用して、書式設定するセルを決定］を選択します。

［次の数式を満たす場合に値を書式設定］のテキストボックスに、以下の数式を入力します。

どの列から見ても、H列の値を参照するようにセル番地のHを固定します。Hの前に［$］をつけ忘れないように気をつけてください。

数式が入力できたら、［書式］をクリックします。

［セルの書式設定］ダイアログボックスが表示されますので、［塗りつぶし］タブから任意の色を選択して［OK］ボタンをクリックします。

［新しい書式ルール］ダイアログボックスに戻りますので、［OK］ボタンをクリックして閉じます。

選択を解除すると、以下のようになります。

H列で右クリックしてショートカットメニューから［非表示］をクリックし、H列は非表示にしてもかまいません。

以下のようになります。

COUNTIF関数で列を追加せずに条件付き書式のみで設定するには

［重複］列を追加しないでも条件付き書式のみで設定することもできます。

その場合、条件付き書式の数式は以下のようにします。

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

2つのデータの2列を比較してともに重複する値を見つけるにはCOUNTIFS関数

名前と住所がともに一致するデータをチェックします。

2つのデータの2列を比較してともに重複する値を見つけるには、COUNTIFS関数を使用します。

COUNTIFS関数は、複数の範囲ごとに条件を設定して、すべての条件を満たすデータの個数を返します。

COUNTIFS 関数 - Microsoft サポート（Microsoft）

まず、重複をチェックするための［重複］列を追加します。

セル［H4］を追加して、以下の数式を入力します。

関数の引数ダイアログボックスを表示すると、以下のようになります。

セル［H4］の数式をオートフィルで下へドラッグしてコピーします。

フィルハンドルをダブルクリックしてもOKです。

COUNTIFS関数は、検索条件に一致するセルの個数が返されるので、［1］が返された場合は重複セルということです。

今度は、［名前］と［住所］が一致するデータをチェックしたので、［1］と表示された重複値が2個になっています。

条件付き書式で対象セルに塗りつぶしを設定

右の表に条件付き書式で、選択範囲に塗りつぶしを設定します。

条件付き書式を設定する場合は、以下の3つの要件が必要になります。

条件付き書式を設定したい範囲を選択します。ここでは、セル［E4］からセル［G14］を選択します。

そして、［ホーム］タブの［スタイル］グループにある［条件付き書式］から［新しいルール］をクリックします。

［新しい書式ルール］ダイアログボックスが表示されますので、［数式を使用して、書式設定するセルを決定］を選択します。

［次の数式を満たす場合に値を書式設定］のテキストボックスに、以下の数式を入力します。

数式が入力できたら、［書式］をクリックします。

どの列から見ても、H列の値を参照するようにセル番地のHを固定します。Hの前に［$］をつけ忘れないように気をつけてください。

［セルの書式設定］ダイアログボックスが表示されますので、［塗りつぶし］タブから任意の色を選択して［OK］ボタンをクリックします。

［新しい書式ルール］ダイアログボックスに戻りますので、［OK］ボタンをクリックして閉じます。

選択を解除すると、以下のようになります。

H列は非表示にしてもかまいません。

参考条件付き書式の数式を［=$H4=0］にすると、重複していない値に塗りつぶしが設定されます。

COUNTIFS関数で列を追加せずに条件付き書式のみで設定するには

［重複］列を追加しないでも条件付き書式のみで設定することもできます。

その場合、条件付き書式の数式は以下のようにします。

参考絶対参照と複合参照については、以下の記事で解説しています。［$］の入力は、［F4］キーを使うと便利です。

重複をチェックする方法については、以下の記事でも解説しています。1つのリストでチェックする方法です。

また、選択したセルの色でデータを抽出する方法も解説しています。

2つのシートの表を比較して、条件付き書式で変更されたセルに塗りつぶしを設定することもできます。