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

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

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

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

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

条件付き書式から重複する値を選択

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

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

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

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

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

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

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

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

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

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

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

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

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

［一意］を選択します。

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

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

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

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

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

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

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

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

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

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

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

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

これで完成です。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

COUNTIFS 関数（Microsoft）

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

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

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

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

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

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

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

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

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

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

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

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

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

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

NOT 関数（Microsoft）

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

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

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

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

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

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