Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。
A列には分析用データとして別表に貼り付け済みのIDがあって、B列にはシステムからダウンロードしたデータのIDがあるとします。
この時、ダウンロードしたデータのうち、まだインポートしていないIDのみを抽出したいということがあります。
セルごとの重複であれば条件付き書式で簡単にみつけられますが、2列がともに重複している行を見つけるには、条件付き書式でCOUNTIFS関数を使用します。
また、結果を反転させるには、NOT関数を使用します。
参考2つの表で重複値をチェックする方法は、以下の記事で解説しています。
-
2つの表を比較して重複しているデータに塗りつぶしを設定する
同じような2つの表があり、2つの表から重複する値をチェックしたい場合の方法です。 条件付き書式から簡単にチェックすることができます。 また、 ...
条件付き書式から一意の値を値を選択
差分を求めるというより、範囲選択内の重複しない一意の値を求める方法です。
Excel2007以降は、条件付書式が充実していて、こういう場合、とても便利になりました。
条件付き書式を設定する場合は、以下の3つの要件が必要になります。
- 書式を設定する範囲
- どういう条件の時に設定するのか
- 条件が合致した場合の書式
まず、一意の値を求めたい範囲を選択します。
範囲選択した状態で、［ホーム］タブの［スタイル］グループにある［条件付き書式］の［セルの強調表示ルール］から［重複する値］をクリックします。
［重複］を［一意］に変更
［重複する値］ダイアログボックスが表示されます。
重複ではなく一意の値を強調表示したいので、プルダウンボタンをクリックして、
［一意］を選択します。
このとき書式もプルダウンボタンから他の書式を選択することもできます。
ここでは、既定のまま［濃い赤の文字、明るい赤の背景］を選択して［OK］ボタンをクリックします。
結果は以下のようになります。
条件付き書式の［新しいルール］から設定
［重複する値］のチェックは、［新しいルール］を表示して設定することもできます。
条件付き書式を設定した範囲を選択して、［条件付き書式］の［新しいルール］を選択します。
［新しい書式ルール］ダイアログボックスが表示されますので、［一意の値または重複する値だけを書式設定］を選択します。
以下のウィンドウになります。［一意］を選択して書式を指定します。
参考条件付き書式の場合、大文字と小文字の区別はされません。
条件付き書式の［重複する値］を先にセル範囲に設定しておくと、重複データが入力された時にセルに色を付けることができます。
条件付き書式の［重複する値］を使用したくない場合は、関数でチェックすることもできます。
注意条件付き書式の［重複する値］は便利な機能ですが、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列でともに同じ値の行に色が付きます。
参考COUNTIFS関数については、以下の記事でも解説しています。Excel2007以降で使用できます。
COUNTIFS関数（複数の検索条件に一致するセルの個数を求める）
COUNTIFS 関数 - Microsoft サポート（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
セル［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
NOT関数で重複していない行に色を付ける
重複行ではない行に書式を設定するには、条件付き書式の数式を以下のように修正します。
NOT関数を使って、反転させればいいですね。
NOT 関数 - Microsoft サポート（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関数で重複しない値をリスト化して表内の該当セルを塗りつぶす
Excel 2021とMicrosoft 365のExcelでは、UNIQUE（ユニーク）関数を使用することができます。 UNIQUE関数を ...
