メールアドレスを分離したい

質問

エクセルで表を作っています.
いろんな人の会員データです.
名前・ふり仮名・住所・アドレス・生年月日・会員登録日がデータです。
そこで会員登録した人の属性などをまとめなければなりません.
１、登録日の推移を出したいのですが、いいやり方ないでしょうか。
２、生年月日はdatedifを使い年齢を表示させ
アベレージで平均年齢、最年少などを出しました.
３、住所による円グラフですが、これが難しい.
住所が羅列されているので「この住所に何人すんでる」というわけでなく文字列のグラフ化になやんでいます。
４、メールアドレスのドメイン別でグラフもしくは集計がしたいのです.というのも近くにケーブルテレビがありそこを使っている人が多い模様.その傾向を示し、今後の会員獲得にむけての施策を考えているからです.
メールアドレスは○○＠○○．ｃｏ．ｊｐと出ていてソートしづらいですし．
どなたか教えてください、早急に。
ちなみにエクセル２００２でウイン２０００です。

回答No.7

oresama · ベストアンサー

基本的な考え方として、
文字列データをカウントするためには、
数えやすいようにデータを抽出して、
それをテーブルの列に加えておいて、
COUNTIFで数えます。

たとえばA3に渋谷区、B3に目黒区の人数を
表示させたい場合、
A1に、
="*"&A2&"*"　（B1にはコピー）
A2、B2には'渋谷区（テキスト）、'目黒区（同）
A3に、
=COUNTIF(住所の列,A1)　（B3にはコピー）
で表になると思います。

抽出したい項目のソースは、
どこかでテキストデータを見つけてくるか、
手打ちになりますね。

手っ取り早くおおまかなベスト10を見つけたければ、
アドレスなら@以下4文字程度
住所なら、区の前2文字程度を抽出します。
抽出後のアドレス一部のデータがC列にあるとすると
=COUNTIF(C:C,C1)　以下、下までコピー
の後、
オートフィルで多い順に見ていけば、
おおまかな多数派が見えてくると思います。

回答No.6

mshr1962

#2のmshr1962です。
>5/6　43人　と登録状況を表にしたいのです。
集計は日ごとですか？年・月での集計かと思いました。
記載した式は日付から年と月を抽出するものです。

>この25歳がこのくらいいる、とか23歳がこのくらいいるみたいな分布図（円グラフはどうやればいいのでしょうか）
集計に関してはSUMIF,COUNTIF,SUMPRODUCT等を使う方法もありますが
ピボットテーブルを使われた方が簡単に集計できます。
そのままピボットグラフにするか？
ピボットテーブルのデータを使ってグラフを作ってください。

お礼

日別集計はピボットテーブルを使いました.
たまにエラーになるのは何でなんでしょうな.

補足

すいません。データ上は登録年月日が入ってます．
日ごとに集計したいのです．
そしてそれをグラフ（折れ線か縦棒）にするつもりです。

ピボットテーブル？
すいません、知識が中途半端なのです．

回答No.5

oresama

＃1、4です。

=COUNTIF(住所の列,"*渋谷区*")
で『渋谷区』を含む住所の人を数えられます。

補足

なるほど。
じゃあ、大丈夫そうですね．
ただ、ひとつ。。。。

その渋谷区とか目黒区とかありそうな住所をあらかじめ設定しておかないといけないわけですね．
見てから作るのでは大変そうです．

回答No.4

oresama

ぱっと見多そうな
特定のデータ
たとえば、
～～@bbcatv.com
みたいなアドレスだけ数えたければ、
=COUNTIF(アドレスの列,"*catv*")
で一発で数えられます。
=COUNTIF(住所の列,"東京都*")
で東京都の人数も数えられます。

回答No.3

misatoanna

セル A1 に入力されたメールアドレスの ＠より後ろを抜き出します。

=RIGHT(A1,LEN(A1)-SEARCH("@",A1))

補足

ありがとうございます。
アドレスは解決しそうです．
2番のかたのとどういう風に違うんでしょうか．

あ、あとアドレス抽出した後
そのあと目で各アドレスを確認してサムイフをやって数だしなんですかね．やっぱり。
なにかうまく表にできないでしょうか．

回答No.2

mshr1962

1.日付なら下記のようにすれば集計できませんか？
年=YEAR(日付)
月=MONTH(日付)

2.年齢の範囲を作るなら
=CEILING(年齢,5)で0～5=>5,6～10=>10のように5才おきになります。
CEILING関数は数値を基準値の倍数に切り上げします。

3.都道府県だけを抽出などされては如何ですか？
特定の都道府県なら市区町村の抽出する。
郵便番号の上3桁なんて方法もあります。

4.下記の数式で分離してください。
=MID(A1,FIND("@",A1,1)+1,LEN(A1))
これで@マークより後ろを抽出できます。

補足

ありがとうございます。
１、がわかりません。
山田太郎　渋谷区　太郎＠ヤフー　25歳　5月6日（登録）
というふうに入力されてます．
そこで
5/5　50人
5/6　43人　と登録状況を表にしたいのです。

２、この25歳がこのくらいいる、とか23歳がこのくらいいるみたいな分布図（円グラフはどうやればいいのでしょうか）

４、は完璧です．

回答No.1

oresama

3はLEFT関数またはMID関数で、
住所の一部を抽出して
SUMIF関数またはCOUNTIF関数で数えるのがいいと思います。
配列数式なる方法もあるようですがよく判りません。

4はSEARCH関数とMID関数の組み合わせで
＠以下を抽出して3と同様に数えるのがいいと思います。
=MID(アドレスのセル,SEARCH("@",アドレスのセル,1)+1,15)
みたいな感じです。

補足

やっぱりＳＵＭＩＦですか。
ところで住所は会員の任意であり、市町村は絶対なのですがその下が登録されてる方とされてないかたがいます．
ということは渋谷区渋谷と渋谷区がいるわけです。
渋谷区で引っ張ってくると渋谷区渋谷はもってこれないのでしょうか。
で、それで数を出してから円グラフですかね．