« 「複数のセルの組み合わせの重複」を省く。 | トップページ | 領収書発行について先に知りたいんです。 »

2つの表を比較して、片方にだけあるものを抽出する。

この記事は自分用の覚書です。

■ sheet1 の「イベント来場者」と sheet2 の「顧客一覧」を比較して、 "「イベント来場者」にはあって「顧客一覧」にはない人" を見つける。
(「イベント来場者」にだけあるデータ)
Windows8 Pro 64ビット + Excel2013

イベント来場者

顧客一覧

※表中の氏名はダミー

・前提
「イベント来場者」の列には同姓同名がある。

同じ会社には同じ氏名の人はいない。

どちらの表にも、氏名と社名の組み合わせで重複しているデータはない。

1. sheet1 の C2 から C23 に A列の氏名と B列の社名を結合したデータを作る。
・sheet1 の C2 に入れて下方へコピーする式
=CONCATENATE(A2,B2)

 sheet1 の A列の氏名と B列の社名を結合したデータを C2 から C23 に作る。

2. sheet2 の D2 から D26 に A列と C列を結合したデータを作る。
=CONCATENATE(A2,C2)

sheet2 の A列と C列を結合したデータを D2 から D26 に作る。

3. sheet1 の D列に sheet2との比較の結果を入れる。
=COUNTIF(Sheet2!$D$2:$D$26,Sheet1!C2)

sheet2 の D2 から D26 の範囲に、 sheet1 の C列にあるデータが何個あるかを調べる。

sheet2 の D2 から D26 の範囲に、 sheet1 の C列にあるデータが何個あるかを調べる。

4. sheet1 の D列でオートフィルターを実行して、セルの値が「0」のものを抜き出す。
「データ」タブ-「並び替えとフィルター」-「フィルタ」

D列のセルの値が「0」のものが、 "「イベント来場者」にはあって「顧客一覧」にはない人"。 (「イベント来場者」にだけあるデータ)

「イベント来場者」にだけあるデータ


人名と社名を結合して1つの文字列にすることで、同姓同名(字が同じの別人)の場合に違いがでるようにした。
同じ会社に同じ氏名はないのが前提。同じ会社に同じ氏名がある場合は、日本花子1 や日本花子2 としておくなど、区別がつく加工があらかじめ必要。

sheet1 の C列にある

=COUNTIF(Sheet2!$D$2:$D$26,Sheet1!C2)

の式は、 sheet2 の D2 から D26 の範囲のなかに、 sheet1 の C2 の値がいくつあるかを数えている。

式の結果が「1」ならば、その「氏名と社名の組み合わせ」は sheet2 の D2 から D26 の範囲のなかに1つある。
( sheet2 の「顧客一覧」に、その「氏名と社名の組み合わせ」がすでにある。)

式の結果が「0」ならば、それは sheet2 の D2 から D26 の範囲のなかにはない。

式の結果が「0」のものをまとめれば、

"「イベント来場者」にはあって「顧客一覧」にはない人"
( sheet1 の「イベント来場者」にだけあるデータ)

を見つけたことになる。

|

« 「複数のセルの組み合わせの重複」を省く。 | トップページ | 領収書発行について先に知りたいんです。 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック


この記事へのトラックバック一覧です: 2つの表を比較して、片方にだけあるものを抽出する。:

« 「複数のセルの組み合わせの重複」を省く。 | トップページ | 領収書発行について先に知りたいんです。 »