Excel3つ以上の列間での重複抽出方法

重複抽出について、質問です。

A列 電話番号(1)
B列 氏名(1)
C列 電話番号(2)
D列 氏名(2)

があります。

電話番号もしくは氏名での重複を探し、カウントしたいです。
尚、AC/BDどちらでも重複する場合は、1カウントとしたいです。

その場合、
①AC間での重複抽出
②BD間での重複抽出
③ ①②間での重複削除
④ 残りをカウント
するしかないでしょうか。

よりスマートな方法が有ればご教示いただきたいです。
宜しくお願いいたします。

質問者からの補足コメント

• AB列はアプローチ中の顧客、
  CD列はアプローチから顧客化できた方
  のため、分けて参照したく別の列で載せています。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/11/28 19:34

• no.6-7にていただいた質問について補足です。
  説明がわかりにくく、申し訳ありません。
  AB列については、アプローチ時に重複を除いています。
  つまり、同じ電話番号宛には営業していません。
  CD列についてはその後の獲得顧客をそのまま載せており、電話番号や氏名が重複している可能性があります。
  例）
  ■アプローチ対象
  A列　電話　123
  B列　氏名　山田花子
  ↓
  ■獲得顧客
  ①
  C列　電話　123（対象本人）
  D列　氏名　山田花子（対象本人）
  ②
  C列　電話　456（対象の家族の番号）
  D列　氏名　山田花子（対象本人）
  ③
  C列　電話　123 （対象本人）
  D列　氏名　山田太郎（対象の家族）
  上記のように顧客獲得できる可能性があります。
  ①②の山田花子さんについては同一人物のため1としてカウントしたいのですが、③についてはその家族も獲得できているため別カウントとしたいです。

    補足日時：2021/11/30 11:40

No.9 回答者： goomania 回答日時： 2021/12/01 00:08

No.8です。

失礼しました。添付画像に番号がもれていました。
修正したものを投稿します。
No.8の回答の添付画像を差替えしたものとしてご理解くだい。

No.8 回答者： goomania 回答日時： 2021/11/30 23:42

No.7です。

>①②の山田花子さんについては同一人物のため1としてカウントしたい
>のですが、③についてはその家族も獲得できているため別カウントと
>したいです。

ということは「電話番号が異なっても名前が同じなら同一人物とする」
という意味になります。

しかし、同姓同名の別人はリストには存在しないという保証がないとそのような方法はとても危険です。他人を同一人物にしてしまう可能性があるからです。

その危険性を無視してよいなら、対応は可能です。
添付画像①をご覧ください。E2セルに

=(SUMPRODUCT(($A$2:$A$16=C2)*1)+SUMPRODUCT(($B$2:$B$16=D2)*1)>0)/SUMPRODUCT(($D$2:$D$16=D2)*1)

という数式を記述し、下方向へコピーしています。

E17セルに
=SUM(E2:E16)
という数式を記述しています。

これにより、添付画像①のケースだと、アプローチリストから顧客化できた人数は８人だと計算されます。

では、その8人の顧客内訳はどうなるのでしょうか?
この状態で、添付画像①の薄緑の枠線範囲を指定して、ピボットテーブルを作成しすま。作成方法は添付画像③を参照してください。

ピボットテーブルが作成されたら、
(1)添付画像➁のB1セルに表示されている(すべて)のオートフィルタ(▼)をクリックし、「複数のアイテムを選択」にチェックを入れる
(2)「０」のチェックを外してOKを押す
(3)ピボットテーブルの範囲内のセルをクリックして、EXCELメニューの「デザイン」⇒「レポートのレイアウト」⇒「表形式で表示」を選択する
(4)「デザイン」⇒「総計」⇒「行と列の集計を行わない」を選択する
(5)「デザイン」⇒「小計」⇒「小計を表示しない」を選択する
これで、添付画像➁のような表ができます。

添付画像①では山田花子さんはD列に重複して含まれていますが、電話番号が異なります。
ご質問者のご指示のように、「電話番号が異なっても名前が同じなら同一人物とする」ということで、カウントは0.５ずつとし、あくまでも田中花子さんは１カウントになるようにしています。
さらに、同じ電話番号でD列に田中太郎さんが存在しますが、この方は別カウントとして１カウントとしています。

これで、ご質問者のご希望は満たされるのではないかと思います。

No.7 回答者： goomania 回答日時： 2021/11/29 22:45

このご質問を読んで以下の疑問がわきました。

(1)A列、B列、C列、D列のそれぞれの列内には重複はないのでしょうか?今時、固定電話の顧客はいないのかもしれませんが、A列に同じ電話番号があって、B列に異なる氏名があることはないのでしょうか(例えばご夫婦)
※添付画像①のA･B列14、15行目にわざと同一電話番号の夫婦らしきデータを入れてあります。
同じことはC列、D列にも言えると思います。

B列、D列に電話番号は異なっているが氏名が同じ人、つまり同姓同名の人が存在する可能性はないのでしょうか
※添付画像①のC･D列４行目と16行目にわざと電話番号の異なる同姓同名者データを入れてあります。

(2)そもそも「A･B列はアプローチ中の顧客」、「C･D列はアプローチから顧客化できた方」ということは、C･D列のデータは必ずA･B列にあるということではないのでしょうか?
それともA･B列から顧客化できたらC･D列に移動させているのでしょうか?
あるいはC･D列にはアプローチなしで顧客化した方(例えば、顧客が直接貴社を指名した方)が含まれているということでしょうか?

上記(1)のように、A、B、C、Dの各列内に重複があるとmygoonicknameさんがお示しになった方法は使えない気がします。
もし、ご質問者のデータがこのような状況だとするとA列とB列をワンセット、C列とD列をワンセットとして考えて比較する方法をとらないといけません。

仮に、上記(2)のようにC･D列のデータが必ずA･B列に存在するならC･D列の件数そのものが重複件数です。

ご質問者のデータの内容が不明なため、具体的な回答が困難ですが、とりあえず、A列とB列をワンセット、C列とD列をワンセットとして考えて比較する方法を考えてみました。

添付画像をご覧ください。
E列でA･B列がC･D列にあるのかを判断します。E列の同じ行のA･B列のTEL・氏名のセットがC･D列のどこかにあるかをチェックし、あればその個数、なければ0を表示します。
E2セルに

=SUMPRODUCT(($C$2:$C$15&$C$2:$D$15=A2&B2)*1)

という数式を記述し、下方向へコピーしています。
次に、F列でC･D列がA･B列にあるのかを判断します。E列の同じ行のC･D列のTEL・氏名のセットがA･B列のどこかにあるかをチェックし、あればその個数、なければ0を表示します。
F2セルに

=SUMPRODUCT(($A$2:$A$16&$B$2:$B$16=C2&D2)*1)

という数式を記述し、下方向へコピーしています。

これで、「E列が0」でフィルターをかけて「A･B列を見ると」、A･B列にはあるが、C･D列にはないTEＬ・氏名が表示されます。
※添付画像➁がこの例です。

逆に、「E列が0でない」でフィルターをかけて「A･B列を見ると」、A･B列にもC･B列にもあるTEＬ・氏名が表示されます。

さらに、一旦E列のフィルターは解除して、
「F列が0」でフィルターをかけて「C･D列を見ると」、C･D列にはあるが、A･B列にはないTEＬ・氏名が表示されます。

逆に、「F列が0でない」でフィルターをかけて「C･D列を見ると」、A･B列にもC･B列にもあるTEＬ・氏名が表示されます。

勘違いしてはいけないのは、この方法では、あくまでもフィルターで表示させたとき、意味があるのはA･BまたはC･Dの片方の列セットであり、フィルターをかけて、同時に二つの列セットに意味を持たせて表示することはできません。

さらに、添付画像①で言えば
E17セルに
=SUM(E2:E16)
F17セルに
=SUM(F2:F16)
という数式を記述し、重複件数を数えています。

重複しているものはA･B列からC･D列を探しても、C･D列からA･B列を探しても同じ件数になるのは当たり前です。

この回答へのお礼

ご丁寧にありがとうございます！

ご指摘の通り、CD列には重複があります。
尚、ご指摘いただいた
>> (2)そもそも「A･B列はアプローチ中の顧客」、「C･D列はアプローチから顧客化できた方」ということは、…
については、誤りがありました。
× 「C･D列はアプローチから顧客化できた方」
○ 「C･D列は何らかの媒体より顧客化できた方」
よって、全獲得者の中から、今回のアプローチから顧客化できたと考えられる顧客だけ拾いたいです。

お礼日時：2021/11/30 11:48

No.6 回答者： fujillin 回答日時： 2021/11/29 17:28

こんにちは

ご質問の内容が、イマイチはっきりと理解できませんけれど・・・

AB列のリストと、CD列のリストは別物と考えて、その中で重複がいくつあるかをカウントしたいという事と解釈しました。
「重複の判断」としては、氏名、電話番号のどちらかが一致していれば、重複とみなすということですよね？

例えば、添付図のようなデータとして、（電話番号は適当に3桁の数値で代用してあります）
CD列で言えば、
電話番号の一致：C3、C5、C6
氏名の一致：D1、D2、D3、D5、D8
うち、電話番号、氏名の両方とも一致しているのはC3:D3とC5:D5
となっており、結果として重複は6として求められればよいものと解釈しました。

添付図ではF2セルに
=SUMPRODUCT((COUNTIF(C:C,A1:A100)+COUNTIF(D:D,B1:B100)>0)*1)
の式を入力して、この結果を求めています。

※ 上式はAB列のデータから見て、『CD列内に（AまたはB列が）一致するものが何個あるか』をカウントするものです。
※ このため、AB列内に重複データがある場合には、その分だけ重複してカウントされる可能性がありますので、ご注意ください。
※ また、同じ理由で、CD列内に重複があってもそちらは無視されます。

この回答へのお礼

ありがとうございます！

>> 「重複の判断」としては、氏名、電話番号のどちらかが一致していれば、重複とみなすということですよね？

その通りです。
ご教示いただいたものを試してみます。

お礼日時：2021/11/30 11:12

No.5 回答者： banzaiA 回答日時： 2021/11/29 12:29

UNIQUE関数は使えますか？

この回答へのお礼

コメントありがとうございます。

あまり詳しくなく、わかりかねるので調べてみます。

お礼日時：2021/11/30 11:13

No.4 回答者： mygoonickname 回答日時： 2021/11/29 10:22

No.1の者です。

＞no2,3どちらも試してみてからまたレスポンスさせていただきます。
→　結果が同じですので、試すのはNo.3だけで良いですよ。
No.2は入力が面倒ですので、手間が掛かる分、効率も悪いですし。

No.3 回答者： mygoonickname 回答日時： 2021/11/28 20:36

No.1の物です。

下記でも、結果は同じですね。　こちらの方が少しスマートですね。
A1～D10までがデータとして、E列から右側が空いているなら、

セルE1　=1/COUNTIF($A$1:$D$10,A1)　を入力
E1をコピーして、F1～H1へ貼り付け
E1～H1をコピーして、E2～H10まで貼り付け
E列とG列を合計すると、電話番号で、重複していない個数が出るかと。
同じくF列とH列を合計すると、氏名の重複していない個数が出るかと。
重複をカウントするなら、全データ数から上記の個数をそれぞれ引けば
結果出るかと思います。


Excel365でしたら、UNIQUE関数で重複するデータをまとめる関数もあり
ます。私の環境では使えないので参考までに、リンクを貼っておきます。

https://dekiru.net/article/19811/

No.2 回答者： mygoonickname 回答日時： 2021/11/28 20:06

No.1の物です。

スマートかどうか、正直微妙ですが、数式で下記の様に入力する。

A1～D10までがデータとして、E列から右側が空いているなら、
セルE1　=1/(COUNTIF($C$1:$C$10,A1)+COUNTIF($A$1:$A$10,A1))
セルF1　=1/(COUNTIF($D$1:$D$10,B1)+COUNTIF($B$1:$B$10,B1))
セルG1　=1/(COUNTIF($C$1:$C$10,C1)+COUNTIF($A$1:$A$10,C1))
セルH1　=1/(COUNTIF($D$1:$D$10,D1)+COUNTIF($B$1:$B$10,D1))
E1～H1をコピーして、E2～H10まで貼り付け
E列とG列を合計すると、電話番号で、重複していない個数が出るかと。
同じくF列とH列を合計すると、氏名の重複していない個数が出るかと。
重複をカウントするなら、全データ数から上記の個数をそれぞれ引けば
結果出るかと思います。　実用的ではないかも知れませんが。。。

この回答へのお礼

ご丁寧にありがとうございます！
次の出社が火曜日のため、no2,3どちらも試してみてからまたレスポンスさせていただきます。

お礼日時：2021/11/29 00:06

No.1 回答者： mygoonickname 回答日時： 2021/11/28 19:26

こんばんは。

下記の違いは何かあるのでしょうか？
・A列とC列の電話番号
・B列とD列の氏名

仮に重複した場合は、片方を消すという事でしょうか？

個数だけでしたら、A列のデータの下に、C列のデータを持ってくる。
同じ様にB列のデータの下に、D列のデータを持ってくる。
それぞれ、重複の削除をすれば、名前と電話番号の重複が削除されるかと。

この回答へのお礼

早速のご回答ありがとうございます。
違いを補足に載せました。

希望としては、ABとCDは分けて載せたいです。

お礼日時：2021/11/28 19:36