Windows 11 & Office 2021 Excel 2021

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

投稿日:2024年7月1日 更新日:

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

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

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

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

条件付き書式でCOUNTIFS関数を使って複数列でともに重複しているデータに塗りつぶしを設定

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

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

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

2つの表

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

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

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

左の表で名前のセル範囲を選択

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

キーボード[Ctrl]キー

右の表で名前のセル範囲を選択

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

[ホーム]タブの[スタイル]グループにある[条件付き書式]の[セルの強調表示ルール]-[重複する値]

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

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

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

[重複する値]ダイアログボックス

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

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

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

条件付き書式で[重複する値]を実行した結果

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

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

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

COUNTIF 関数 - Microsoft サポート(Microsoft)

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

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

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

=COUNTIF($B$4:$B$9,F4)

セル[H4]を選択してCOUNTIF関数を入力

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

関数の引数ダイアログボックス

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

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

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

COUNTIF関数を入力した結果

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

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

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

  • 書式を設定する範囲
  • どういう条件の時に設定するのか
  • 条件が合致した場合の書式

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

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

[ホーム]タブの[スタイル]グループにある[条件付き書式]-[新しいルール]

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

[新しい書式ルール]ダイアログボックスの[数式を使用して、書式設定するセルを決定]

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

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

=$H4=1

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

[新しい書式ルール]ダイアログボックスの[次の数式を満たす場合に値を書式設定]のテキストボックスに数式を入力

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

[セルの書式設定]ダイアログボックスの[塗りつぶし]タブ

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

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

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

条件付き書式を設定した結果

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

H列で右クリックした時のショートカットメニュー[非表示]

以下のようになります。

H列を非表示にした表

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

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

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

=COUNTIF($B$4:$B$9,$F4)

[重複]列を追加しないで条件付き書式のみで設定した時の数式COUNTIF関数

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

相対参照と絶対参照と複合参照は[ F4 ]キーで切り替え

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

続きを見る

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

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

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

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

COUNTIFS 関数 - Microsoft サポート(Microsoft)

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

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

=COUNTIFS($B$4:$B$9,F4,$C$4:$C$9,G4)

COUNTIFS関数で2つの表の2つの列で重複をチェック

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

関数の引数ダイアログボックス

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

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

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

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

COUNTIFS関数を入力した結果

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

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

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

  • 書式を設定する範囲
  • どういう条件の時に設定するのか
  • 条件が合致した場合の書式

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

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

[ホーム]タブの[スタイル]グループにある[条件付き書式]-[新しいルール]

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

[新しい書式ルール]ダイアログボックスの[数式を使用して、書式設定するセルを決定]

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

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

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

=$H4=1

[新しい書式ルール]ダイアログボックスの[次の数式を満たす場合に値を書式設定]のテキストボックスに数式を入力

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

[セルの書式設定]ダイアログボックスの[塗りつぶし]タブ

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

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

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

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

条件付き書式で設定した結果

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

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

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

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

=COUNTIFS($B$4:$B$9,$F4,$C$4:$C$9,$G4)

[重複]列を追加しないで条件付き書式のみで設定した時の数式COUNTIFS関数

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

相対参照と絶対参照と複合参照は[ F4 ]キーで切り替え

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

続きを見る

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

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

重複しない一意の値を求める方法と複数列で重複をチェックする方法

Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。 A列には分析用データとして別表に貼り付け済みのI ...

続きを見る

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

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

Excelでセルの値が修正されたことが一目で分かるようにしておくと、便利なことはよくあります。 たとえば、各部署に既存の住所一覧を配布して、 ...

続きを見る

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

2つのファイルを比較できるINQUIRE(ワークシートやセルの相関関係も)

Excel2013以降では、INQUIREというアドインを使用して、2つのファイルを比較して違いを表示することができます。 また、ワークシー ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

関連記事30件

Profile

-Windows 11 & Office 2021 Excel 2021

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

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