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

Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。

A列には分析用データとして別表に貼り付け済みのIDがあって、B列にはシステムからダウンロードしたデータのIDがあるとします。

この時、ダウンロードしたデータのうち、まだインポートしていないIDのみを抽出したいということがあります。

セルごとの重複であれば条件付き書式で簡単にみつけられますが、2列がともに重複している行を見つけるには、条件付き書式でCOUNTIFS関数を使用します。

また、結果を反転させるには、NOT関数を使用します。

参考2つの表で重複値をチェックする方法は、以下の記事で解説しています。

条件付き書式から一意の値を値を選択

差分を求めるというより、範囲選択内の重複しない一意の値を求める方法です。

Excel2007以降は、条件付書式が充実していて、こういう場合、とても便利になりました。

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

まず、一意の値を求めたい範囲を選択します。

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

［重複］を［一意］に変更

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

重複ではなく一意の値を強調表示したいので、プルダウンボタンをクリックして、

［一意］を選択します。

このとき書式もプルダウンボタンから他の書式を選択することもできます。

ここでは、既定のまま［濃い赤の文字、明るい赤の背景］を選択して［OK］ボタンをクリックします。

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

条件付き書式の［新しいルール］から設定

［重複する値］のチェックは、［新しいルール］を表示して設定することもできます。

条件付き書式を設定した範囲を選択して、［条件付き書式］の［新しいルール］を選択します。

［新しい書式ルール］ダイアログボックスが表示されますので、［一意の値または重複する値だけを書式設定］を選択します。

以下のウィンドウになります。［一意］を選択して書式を指定します。

参考条件付き書式の場合、大文字と小文字の区別はされません。

条件付き書式の［重複する値］を先にセル範囲に設定しておくと、重複データが入力された時にセルに色を付けることができます。

条件付き書式を使って重複データをチェック

条件付き書式の［重複する値］を使用したくない場合は、関数でチェックすることもできます。

2つの列を比較して重複値を探すには

注意条件付き書式の［重複する値］は便利な機能ですが、16文字以内でなければ有効になりません。以下の記事が参考になります。

エクセルの条件付き書式の重複する値は１６文字まで見て判定してるっぽい - Qiita

Microsoft 365のExcelで［重複する値］を実行すると、半角スペースと全角スペースが区別されずに同じ値とみなされるようになっています。

Excel2021までのバージョンでは、半角スペースと全角スペースは異なる値となります。

Microsoft 365のExcelの「重複する値」では全角・半角スぺ―スが区別されず重複になる

数式を使ってチェック

関数を入力してチェックするのであれば、以下のようになります。

数式は以下のように入力しています。

A列もチェックする場合は以下のように設定します。この場合、該当するセルがないので、塗りつぶしのセルはありません。

条件付き書式の書式を残して条件付き書式を削除するには

条件付き書式を設定すると、値が変わると自動的に書式設定のセルも変わります。

それを防ぐために書式は残して、条件付き書式は削除したいという場合があります。特に共有で使用するシートの場合は気を付けたいですね。

その場合の方法です。ただし、データバーなどの書式には適用できません。

まず、条件付き書式を設定した範囲を選択して、コピーします。

クリップボードを開きます。クリップボードの値を貼り付けします。必ず、クリップボードから貼り付けします。

これで完成です。

条件付き書式を確認してみます。

［ホーム］タブの［スタイル］グループにある［条件付き書式］の［ルールの管理］をクリックすると確認できます。

［条件付き書式ルールの管理］ダイアログボックスが表示されます。［書式ルールの表示］から［このワークシート］を選択して確認します。

セルの書式設定は残っていますが、条件付き書式は消えています。

参考以下の記事でも解説しています。

条件付き書式の書式のみを残すにはクリップボードから貼り付け

Windows 10以降では、［Windows］+［V］でWindowsのクリップボードを表示できますが、Windowsのクリップボードから貼り付ける場合は、直前以外のアイテムであれば同じように書式のみにできます。

選択したセルの色でフィルター

もし、条件付書式で強調されたセルのみ抽出したいのであれば、色付きのセルで右クリックします。

ショートカットメニューから［フィルター］をポイントして［選択したセルの色でフィルター］をクリックします。

結果は以下のようになります。関数を使わなくても簡単に求めることができて便利です。

参考色フィルターについては、以下の記事で解説しています。

セルの色で並べ替えることもできます。

複数列で共に一致している重複行に色を付ける

以下のような表があって、B列とC列で共に一致している重複行をチェックするには、条件付き書式の［重複する値］では機能しません。

条件付き書式で数式を使用して、書式を設定するようにします。

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

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

［新しい書式ルール］ダイアログボックスから［数式を使用して、書式設定するセルを決定］をクリックします。

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

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

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

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

以下のように、B列とC列でともに同じ値の行に色が付きます。

参考COUNTIFS関数については、以下の記事でも解説しています。Excel2007以降で使用できます。

COUNTIFS関数（複数の検索条件に一致するセルの個数を求める）

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

=COUNTIFS (条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2],…)  最大127組指定可

特定の条件に一致するセルの個数を返します。

数式内の［$B2］や、［$C2］は、列固定の複合参照です。

［$］の入力は、［F4］キーを使うと便利です。連続して押すと参照が切り替わっていきます。

古くからある機能ですが、［フィルターオプションの設定］で重複を削除したリストを作成できます。また、複数列で共に一致している重複行を削除することもできます。

Power Queryエディターでも、複数列で一致している重複行を削除した結果を求めることができます。

B列とC列を結合して重複をチェック

B列とC列をフラッシュフィルや、数式で結合して、チェックしてもいいと思います。

セルの文字列を結合するための方法は、いくつかあります。

セル［D2］に以下の数式を入力して、数式をコピーします。

セル［A2］からセル［D13］を選択して、条件付き書式を設定します。数式には、以下のように入力します。

この場合は、COUNTIF関数でもOKです。

重複の2番目以降の行に色を付ける

もし、重複行の1番目を省いて、2番目以降に色を付けたい場合は、条件付き書式の数式を以下のように修正します。

NOT関数で重複していない行に色を付ける

重複行ではない行に書式を設定するには、条件付き書式の数式を以下のように修正します。

NOT関数を使って、反転させればいいですね。

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

数式が見づらい場合は、数式ボックスの右側にある［ダイアログボックス拡大/縮小］ボタンをクリックすると、少し見やすくなります。

以下のようになります。再度、［ダイアログボックス拡大/縮小］ボタンをクリックすると、元に戻ります。

参考重複を削除する機能もあります。

以下の記事も参考になると思います。

ピボットテーブルを使って重複をチェックすることもできます。

Excel2019では、複数の検索条件で最大値・最小値を求められる［MAXIFS（マックスイフエス）関数］と［MINIFS（ミニマムイフエス）関数］が使えるようになっています。

Microsoft 365のExcelとExcel2021では、UNIQUE（ユニーク）関数を使用して一意のリストを作成できます。