Windows 8 & Office 2013 Excel 2013

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[一意]を選択します。

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

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

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

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

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

条件付き書式の結果

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

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

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

[条件付き書式]の[新しいルール]

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

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

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

[一意の値または重複する値だけを書式設定]を選択

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

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

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

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

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

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

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

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

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

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

数式を使ってチェック

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

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

=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]キーを使うと便利です。連続して押すと参照が切り替わっていきます。

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

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

続きを見る

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

複数の条件に合うデータを抽出する方法(別シートへの書き出しも可能)

Excelのデータ抽出の方法はいろいろありますが、ここでは[データ]タブの[並べ替えとフィルター]グループにある[詳細設定]を使って、複数の ...

続きを見る

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

Power Queryエディターで複数のグループ化の結果を表示するには

Power Queryには、[グループ化]という機能があります。 COUNTIF関数やSUMIF関数、またはピボットテーブルで求める結果と同 ...

続きを見る

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(ミニマムイフエス) ...

続きを見る

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

UNIQUE関数で重複しない値をリスト化して表内の該当セルを塗りつぶす

Excel2021とMicrosoft 365のExcelでは、UNIQUE(ユニーク)関数を使用することができます。 UNIQUE関数を使 ...

続きを見る

検索

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

Access / Excel / PowerPoint / Word

Profile

執筆者/はま

-Windows 8 & Office 2013 Excel 2013

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

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