複数条件で重複連番・重複合計を計算する

顧客リストで複数条件で重複を検出して連番・合計を計算します。

条件は姓 名・郵便番号を使って組み合わせ、同姓同名でも郵便番号(住所)違いの人を検出します
使用・Excel2016

重複を検出して連番・合計を計算する例は、検索すると出てきますが、複数の条件で連番・合計を計算するページはなかった。

重複連番　D2
=COUNTIFS($D$2:D2,D2,$D$2:D2,D2)
=COUNTIFS([姓 名の行・1行目固定],[姓 名],[郵便番号・1行目固定],[郵便番号])

オートフィルで計算したい行までコピーする
1行目を固定しているので、下記のように1行ごとに範囲が増える。すると１行目から、そのセルまでが範囲になるので、数える対象になる。

=COUNTIFS($D$2:D3,D3,$D$2:D3,D3)

=COUNTIFS($D$2:D4,D4,$D$2:D4,D4)

重複合計　E2
=COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2)
オートフィルで計算したい行までコピー

下記、F2/G2は＆で計算結果を連結したもの。計算セルを使ってよいならそのほうがわかりやすい。

連番/合計　F2

=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)&"/"&COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2)

連番/重複/氏名/郵便　G2

=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)&"/"&COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2)&"_"&B2&"_"&TEXT(C2,"[<=999]000;[<=9999]000-00;000-0000")

*顧客リストで、その行が数字のみだと個人を判別できないので連結した。

★1、☆10は、同姓同名だが郵便番号が違うことで、個人を検出できている。

★1	平田 裕子	510-0063	1	3	1/3	1/3_平田 裕子_510-0063
☆10	平田 裕子	013-0426	1	3	1/3	1/3_平田 裕子_013-0426

A

B

C

D

E

F

G

番号	姓 名	郵便番号	重複連番	重複合計	連番/合計	連番/重複/氏名/郵便
★1	平田 裕子	510-0063	1	3	1/3	1/3_平田 裕子_510-0063
2	竹中 幸信	921-8812	1	3	1/3	1/3_竹中 幸信_921-8812
3	平田 武士	670-0944	1	3	1/3	1/3_平田 武士_670-0944
4	平田 裕子	510-0063	2	3	2/3	2/3_平田 裕子_510-0063
5	竹中 幸信	921-8812	2	3	2/3	2/3_竹中 幸信_921-8812
6	平田 武士	670-0944	2	3	2/3	2/3_平田 武士_670-0944
7	平田 裕子	510-0063	3	3	3/3	3/3_平田 裕子_510-0063
8	竹中 幸信	921-8812	3	3	3/3	3/3_竹中 幸信_921-8812
9	平田 武士	670-0944	3	3	3/3	3/3_平田 武士_670-0944
☆10	平田 裕子	013-0426	1	3	1/3	1/3_平田 裕子_013-0426
11	竹中 幸信	366-0826	1	2	1/2	1/2_竹中 幸信_366-0826
12	平田 武士	030-0944	1	2	1/2	1/2_平田 武士_030-0944
13	竹田 武裕	034-0013	1	1	1/1	1/1_竹田 武裕_034-0013
14	浜野 彰	381-3205	1	1	1/1	1/1_浜野 彰_381-3205
15	吉野 妃奈	880-0877	1	1	1/1	1/1_吉野 妃奈_880-0877
10	平田 裕子	013-0426	2	3	2/3	2/3_平田 裕子_013-0426
11	竹中 幸信	366-0826	2	2	2/2	2/2_竹中 幸信_366-0826
12	平田 武士	030-0944	2	2	2/2	2/2_平田 武士_030-0944
10	平田 裕子	013-0426	3	3	3/3	3/3_平田 裕子_013-0426

連絡があった人の合計数を数える

D列　重複連番
=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)

の計算結果をもとに
1の数を数える
=COUNTIF(D:D,1)

2回以上連絡があった人の合計数を数える

D列　重複連番

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

の計算結果をもとに
2の数を数える
=COUNTIF(D:D,2)
以降 3回以上,4回以上,5回以上...を数える

=COUNTIF(D:D,[3-5])

ここで、=COUNTIF(D:D,">=2")とするのは間違い。
なぜなら、3,4,5 も数えると、同一人物の3,4,5を数えることになる。

参考・協力
http://www.excel.studio-kazu.jp/kw/20180304164301.html