Windows 8 & Office 2013 Excel 2013

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

投稿日:2017年1月9日 更新日:

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

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

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

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

B列とC列ともに同じ値の行に書式設定

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

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

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

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

条件付き書式を設定する範囲を選択

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

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

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

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

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

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

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

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

[重複]を[一意]に変更

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

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

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

[一意]を選択します。

[重複する値]ダイアログボックスで[一意]を選択

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

[重複する値]ダイアログボックスで書式選択

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

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

条件付き書式の結果

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

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

=ISERROR(VLOOKUP(B2,$A$2:$A$14,1,FALSE))

数式を入力してチェック

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

=ISERROR(VLOOKUP(A2,$B$2:$B$20,1,FALSE))

数式を入力してチェック

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

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

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

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

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

条件付き書式の範囲を選択してコピー

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

クリップボードから貼り付け

これで完成です。

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

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

[条件付き書式]の[ルールの管理]

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

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

[条件付き書式ルールの管理]ダイアログボックス

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

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

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

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

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

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

ショートカットメニューの[フィルター]-[選択したセルの色でフィルター]

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

色フィルターの結果

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

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

Excelで色分けした表を作成して、セルの色で絞り込む方法です。 Excel2007からの機能です。 便利なので、ぜひ活用してみてください。 ...

続きを見る

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

セルの色で並べ替え(条件を追加して複数キーによる並べ替えもできる)

Excel2007からセルの塗りつぶしの色で並べ替えや絞り込みができるようになりました。 ここでは、Excel2010でセルの塗りつぶしの色 ...

続きを見る

[並べ替え]の[レベルの追加]で複数の基準でデータを並べ替え

Excelのデータでセルに塗りつぶしを設定して色分けした表を作成している場合、セルの塗りつぶしの色で並べ替えることができます。 [並べ替え] ...

続きを見る

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

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

サンプル表

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

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

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

[条件付き書式]から[新しいルール]を選択

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

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

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

=COUNTIFS($B$2:$B$13,$B2,$C$2:$C$13,$C2)>1

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

[新しい書式ルール]に数式を入力

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

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

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

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

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

B列とC列でともに同じ値の行に色が付いた表

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

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

COUNTIFS 関数(Microsoft)

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

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

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

相対参照と絶対参照と複合参照は【 F4 】キーで切替

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

続きを見る

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

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

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

複数の文字列を結合してひとつの文字列にするには

Excelで1つのセルに他のセルの文字列を結合した文字列を表示する方法を紹介します。 5つの方法を紹介しますので、使用環境に合った方法で操作 ...

続きを見る

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

=B2&C2

B列とC列を結合

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

=COUNTIFS($D$2:$D$13,$D2)>1

条件付き書式の数式

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

=COUNTIF($D$2:$D$13,$D2)>1

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

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

=COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2)>1

2番目以降に色を付けたい場合の数式

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

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

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

NOT 関数(Microsoft)

=NOT(COUNTIFS($B$2:$B$13,$B2,$C$2:$C$13,$C2)>1)

重複行ではない行に書式を設定

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

[書式ルールの編集]の[ダイアログボックス拡大/縮小]ボタン

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

[ダイアログボックス拡大/縮小]ボタンを押して縮小したダイアログボックス

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

[重複の削除]は1番目の値が残るがデータの空白スペースには注意

Excel2007以降には、[データ]タブに[重複の削除]というコマンドがあります。 ポップヒントには、以下のように記されています。 重複の ...

続きを見る

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

2つのセルの文字列を比較して同じ文字列かどうかを確認する方法

Excelで2つのセルに入力された文字列が合っている(同じ)かどうかを確認する方法です。 下のようにB列とC列に文字列または数字が入力されて ...

続きを見る

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

ピボットテーブルで重複データをチェックして一意のリストを作成

Excelのリストで重複データが混在している場合、重複データをチェックして一意のリストを作成したい場合があります。 重複データをチェックする ...

続きを見る

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

複数の条件で最大値/最小値を求める[MAXIFS関数]と[MINIFS関数]

Excel2019では複数の検索条件で最大値・最小値を求められる[MAXIFS(マックスイフエス)関数]と[MINIFS(ミニマムイフエス) ...

続きを見る

検索

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

Profile

執筆者/はま

-Windows 8 & Office 2013 Excel 2013

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

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