エクセルでのデータ集計方法について

質問

エクセルで、【１．元データ】のようなデータがシート毎に５，０００社ほどあります。このデータを各市区町村ごとに集計を取りたいのです。
【１．元データ】
A社　┃　東京都品川区戸越
B社　┃　東京都新宿区下落合
C社　┃　東京都新宿区新宿
D社　┃　埼玉県川口市柳崎
E社　┃ 神奈川県川崎市中原区丸子通

そこで、ピボットテーブルを使って集計したかったのですが、同じセルの中に番地までデータが入っていて【２．集計後のイメージ】のような意図している集計が出来なかったです。

【２．集計後のイメージ】
品川区　┃　１
新宿区　┃　２
川口市　┃　１
川崎市　┃　１

これを解決するにはどういう集計の仕方をすればいいでしょうか？
現在はエクセル上で住所データで並べ替えをして数えて集計をしているのですが、時間がかかってしまうため、何かいい方法はありませんか？

回答No.3

don9don9

No.2の方と同じような方法を回答しようとしていましたが
先を越されてしまったので一つだけ補足を。

住所から特定文字（都、道、府、県、郡、市、区、町、村など）
を探してその前後で切る方法の場合、その特定文字が付く地名、
例えば「京都」「市川」「町田」「四日市」「別府」
のような地名は、変な切れ方をする可能性がありますので
ご注意下さい。

回答No.2

gyouda1114

C列・D列を作業列として使う方法です。
C2に　=MIN(IF(NOT(ISERROR(FIND({"府","県","道","都"},B2,1))),FIND({"府","県","道","都"},B2,1)))
D2に　=MIN(IF(NOT(ISERROR(FIND({"郡","区","市"},B2,C2))),FIND({"郡","区","市"},B2,C2)))
E2に　=MID(B2,C2+1,D2-C2)
H2に　=COUNTIF(E:E,G2)
何れも下方にオートフィル

画像を参考にお試しを

回答No.1

wisemac21

元のデータがA:B列にあるとすると、1列空けて、D列に
品川区
新宿区
川口市
川崎市
　・
　・

という表を作成し、E列に
E1=COUNTIF($B$1:$B$100,"*"&D1&"*")　と入力して
下へコピー
COUNTIFを使って検索条件の入力方法がキモです。