Excel関数

質問

Exce2003を使用しております。

下記のようにA列に約1万件の住所が入力されております。

A1　東京都世田谷区・・・
A2　東京都目黒区・・・
A3　千葉県船橋市・・・
A4　東京都世田谷区・・・

その住所から県毎、及び市区町村毎の集計を行いたいのですが、
その集計処理に適した関数をご教示頂きたくお願い致します。

運営の都合上、出来ればフィルタの使用は控えたいので
関数利用でのアドバイスを頂きたく宜しくお願い致します。

回答No.8

nattocurry · ベストアンサー

C1に
=COUNTIF(A:A,"*"&B1&"*")
と入力して、それを下にコピーすれば良いです。

お礼

二度もご回答頂き誠に有難うございます。

ご回答拝読致しました。大変参考になりました。

私の望んでいた的を射たご回答です。

貴重なお時間を割いてのご回答、感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.7

imogasi

関数でといっているが、易しく無い。
（１）関数の解説本の中には、漢字県名を切り出す関数は載っている。
そこまでは何とかできるでしょう。
（２）その県名を省いた住所を作る（ＳＵＢＳＴＩＴＵＴＥで県名を空白に置き換える）
（３）残りの住所に対し、「区」か「市」の漢字が現れるまでを切り出す。
これには「市川市」など都合の悪い例が有る。他市町村にも有るかもしれない。それは人間が見つけて修正する。
（４）以上の県＋市区の区分でピボットテーブルで集計する。
ーーーー
以上関数でやるのは完全とはいえないことがある。
ーー
こういうことで、ＶＢＡででもプログラムを組めて、完全といえないところを補うことを考える課題だと思う。
住所漢字でソートし、市が現れるまでの文字列を抜き出す。その際例外の市に配慮するてもある。
ーーー
参考
ＶＢＡの一種だが、
県名抜き出しのユーザー関数を作ってみた。
Ａ列　　　　　Ｂ列　　　　　　　　　Ｅ列
住所 県名（関数結果） 県名テーブル
青森県青森市 青森県 青森県
岩手県盛岡市 岩手県 岩手県
宮城県仙台市 宮城県 宮城県（以下４７県のリストを作る（出現頻度順がよいが）。
Ｂ２には　=fnd(A2,5)　と入れて下方向に式を複写。
５はＥ列に県名リストが有ることを教えるもの。
ＶＢＡコードは標準モジュールに
Function fnd(a, b)
For i = 1 To 47
x = WorksheetFunction.Substitute(a, Cells(i, b), "")
If Len(a) > Len(x) Then
fnd = Cells(i, b)
Exit Function
End If
Next i
fnd = "nf"
End Function県名を除いた住所は
県名除く
青森市
盛岡市
仙台市
式は=fnd2(A2,5)
コードは
Function fnd2(a, b)
For i = 1 To 47
x = WorksheetFunction.Substitute(a, Cells(i, b), "")
If Len(a) > Len(x) Then
fnd2 = x
Exit Function
End If
Next i
fnd2 = "nf"
End Function

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

補足

ご回答いただきました皆様、
質問の意図が伝わらず誠に申し訳ございませんでした。

再度質問させて頂きますと、以下のようにA列に住所が入力されています。

A1　 東京都世田谷区
A2　 東京都目黒区
A3　 東京都江東区
A4　 東京都世田谷区
A5　 東京都目黒区
A6　 東京都新宿区
A7　 東京都大田区
A8　 東京都世田谷区
A9　 東京都江戸川区
A10　東京都中央区
A11　・・・

B1セルに世田谷区と入力し、C1セルにA列に世田谷区と
入力されているセルが何件あるか抽出したいのです。
（上記の場合は3と表示）

同じようにB2セルには目黒区、C2セルには目黒区の入力件数。
（上記の場合は2）
D1には東京都と入力、E1に東京都の入力件数。
（上記の場合10）

拙い説明ですがご理解頂ければ幸いです。
宜しくお願い致します。

回答No.6

某HN クロメート(Chromate)（@CoalTar）

1. 住所から郵便番号に変換する
2. 郵便番号から住所に変換する
3. フィルタオプションの設定で重複するレコードをなくす
4. COUNTIFで計算する
3. ピボットテーブルでカウントする
Excel アドイン: 郵便番号変換ウィザード (2000-2007 ユーザー用)
http://www.microsoft.com/downloads/details.aspx?FamilyID=6F6AF8EF-B9DD-4E21-9E63-AF4A0FF4E7CE&displaylang=ja
Office 更新プログラム: 郵便番号辞書 (2008 年 11 月版)
http://www.microsoft.com/downloads/details.aspx?familyid=45192822-FBA7-49E6-B220-09BB32250EB3&displaylang=ja
参考まで

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.5

hallo-2007

他の方がおっしゃるとおりの事例です。
郵便番号の情報はないでしょうか、そちらで区分する方法を考えては如何でしょうか。
取合えず　都道府県名を抽出する方法ですが、
B列に　=IF(MID(A1,4,1)="県",LEFT(A1,4),LEFT(A1,3))　でシテへコピィ
（式を入れたセルの右下にマウス移動して　＋　の表示になったらダブルクリック）
あとは、熱のシートに　県名の一覧表準備して　COUNTIF関数で個数がでます。
>運営の都合上、出来ればフィルタの使用は控えたいので
>関数利用でのアドバイスを頂きたく宜しくお願い致します。
結果として、県名ごとの一覧表が必要なのか、データの件数が必要なのどちらでしょうか。
目的などあれば、別のアドバイスがつくかと思います。

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.4

web2525

集計に適した関数のみの提示でいいなら
集計のための関数
SUMPRODUCT関数
COUNTIF関数
など
都道府県や市町村を判定するための関数
FIND関数
SEARCH関数
MATCH関数
など
そのほか配列計算時に
INDEX関数
など

どんな表からどんな集計を行いたいのか
どんな計算式を立てたらどんな不具合が生じたのか
などの情報が無いと、これ以上は答えようがない。

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.3

grumpy_the_dwarf

まずは都道府県や市町村を住所全体から分離する必要があります
ね。この時に文字列検索で「都」や「府」や「町」を探したくなり
ますが、これは絶対にウマくいきません。甲府市や都城市や町田市
など困った例が山盛りです。

「地方公共団体コード」の一覧を入手して下さい。日本国内のすべ
ての市町村の名前が網羅されています。この表を市町村名の文字
コード順で並べ替えておき、vlookup関数やsearch関数の検索範囲に
使えば、ウマく市町村を分離できます。あとはcountifでも使って数
えればいいですね。
http://www.lasdec.nippon-net.ne.jp/cms/1,0,14.html

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.2

Cupper

なんか依頼っぽい投稿ですので、ヒントだけにします
（仕事として請け負ってくれるところを探して依頼すべき内容です）

住所から都道府県と市区町村を別々に抽出しちゃいましょう
一度に集計を行おうと考えるから難解になるんですよ

作業用の列を作ってそこに都道府県・市区町村を入れるようにしましょう
都道府県くらいなら関数で何とかできますが、市区町村はちょっと無理
かなり大掛かり（？）な VBAで処理する必要がありますしこれだけで有料のアドオンになり得ます
一つずつコピーする必要はありませんから半角スペースで都道府県と市区町村、市区町村とそれに続く住所を切り分けてみてください
あとは データ → 区切り位置 で半角スペースを区切りとしてセルを分割します
　※「置換」で都道府県別に半角スペースを入力すれば、少しは横着できますよ
　例：検索文字列「東京都」、置換文字列「東京都 」
　　　　検索文字列「千代田区」、置換文字列「千代田区 」

まあたかが１万件程度のデータですから１日くらい時間を掛ければ何とかなりますよ
あとは…後のことを考えずに集計用のデータを集めた人に向かって愚痴りましょう
その人を巻き込んでデータを修正するのもよいアイディアです

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.1

nattocurry

集計結果として、どのようなものが欲しいのか、具体的な例を挙げてもらえますか？