エクセルで件数を数える関数

質問

担当者と契約者のリストがあります。
担当者と同じ苗字の契約者の件数が知りたいのです。
たとえば、田中はなこさんの担当の契約者で、苗字が田中さんは何件か、という感じです。
件数が多くて困っています。

　　A　　　　　B　　　　　　C
　担当者　担当の名前　　契約者名
1　001　　田中はなこ　　田中はなこ
2　001　　田中はなこ　　田中じろう
3　001　　田中はなこ　　中田さぶろう
4　002　　中田たろう　　田中はなこ
5　003　　中田たろう　　中田さぶろう

よろしくお願いします。

回答No.2

abekkan · ベストアンサー

(1)Ｄ列に作業用列を作ります。
D1に　=IF(LEFT(B3,2)=LEFT(C3,2),LEFT(B3,2))と書いてD2から下（例えばD100まで）にコピーする。
→D列に同じ苗字の場合のみ苗字が書かれる。

(2)E列に担当者の苗字の一覧を書く。

(3)F1に、=COUNTIF($D$1:$D$100,E2)　と書いてF2以下にコピー。（$の有無に注意！）
→F列に担当者ごとの数が出ます。ただし苗字が２文字のみに対応です。

このやり方がわかりやすいかと思います。

お礼

ありがとうございました。
作業列をたくさん増やして、一段階ずつ関数を入れて解決しました。
何とか仕上がりました。
ほんとうに助かりました。

回答No.3

maruru01

No.1です。

>C行とE行は、実際にはLEFT関数を使って、
>「田中スペース」という状態になっています。
スペースが入っているとよくないので、CE列の数式を、

=TRIM(LEFT(省略))

のようにして、スペースを除いて下さい。
そうしたら、担当者と契約者の苗字が共に「田中」の件数は、

=SUMPRODUCT((C2:C6="田中")*(E2:E6="田中"))

になります。
また、担当者と契約者の苗字が同じ（田中とは限らない）データの件数は、

=SUMPRODUCT((C2:C6=E2:E6)*1)

になります。

お礼

いつもいつも、色々考えていただいて、ほんとうにありがとうございます。

関数の組み合わせがさっぱりわからないもので、いつも頼りっぱなしでごめんなさい。

また、お世話になると思います。
よろしくお願いします♪

回答No.1

maruru01

こんにちは。maruru01です。

特定の条件、例えば、
「田中はなこさんの担当の契約者で、苗字が田中さんは何件か」
ならば、

=SUMPRODUCT((B2:B6="田中はなこ")*(LEFT(C2:C6,2)="田中"))

という風な数式で出来ます。
ただし、これは、あくまで契約者名の先頭の2文字が"田中"という条件になります。
したがって、
「田中山ゆうこ」
という名前で「田中山」が苗字の人も該当してしまいます。
Excelには、「田中山」までが苗字かどうかは判断出来ません。
苗字と名前を分離して別のセルに入れるか、間にスペースを入れるかしなければ、Excelで苗字と名前の判断は出来ません。
したがって、正しいデータの集計をすることは出来ません。
ちなみに、苗字と名前の分離は、当然手作業で行わなければいけませんよ。

補足

maruru01さん、またまたお世話になります。

これならどうでしょう？
C行とE行は、実際にはLEFT関数を使って、「田中スペース」という状態になっています。
　　A　　　　　B　　　　　　C 　　　D　　　　　E
　担当者　担当の名前　担当の苗字　契約者名 　契約者の苗字
1　001　　田中　はなこ　　田中　田中　はなこ 　田中
2　001　　田中　はなこ　　田中　田中　じろう 　田中
3　001　　田中　はなこ　　田中　中田　さぶろう 　中田
4　002　　中田　たろう　　中田　田中　はなこ 　田中
5　003　　中田　たろう　　田中　中田　さぶろう 　中田