エクセルで出現文字列のランキング

質問

各行の文字列の中で、頻出する単語の出現回数ランキングを出したいです。
例えば、okwave、okwave.com、コミュニティokwave、コミュニティサイト、と4行ある場合、okwaveが3つ、コミュニティが2つあるので、okwave、コミュニティ、とデータが並ぶような仕組みを作りたいです。
ご教示よろしくお願いします。

回答No.1

dogs_cats · ベストアンサー

下記方法で可能ですが、コミュニティという文字は文字列から文字を削除して始めて検索出来ますので、そのような文字列が多くなれば、関数よりVBAの方が良いかもしれません。

A１~A4にデータがあるものとする。
B1式
=IF(A1="","",IF(COUNTIF($A$1:$A$4,"*"&A1&"*")>1,COUNTIF($A$1:$A$4,"*"&A1&"*"),""))

C1式（カウント数が同数でもデータ抽出する為の数式）
=IF(B1="","",B1+1/ROW()/100)

B1~C1下方へオートフィル

A6式
=IF(COUNT($B$1:$B$4)=0,"",SUBSTITUTE(A1,INDEX($A$1:$A$4,(MATCH(0,INDEX(0/($A$1:$A$4<>""),),0))),""))
A6式をA8までコピー

A1１式
=IF(COUNT($B$6:$B$9)=0,"",SUBSTITUTE(A6,INDEX($A$6:$A$9,(MATCH(0,INDEX(0/($B$6:$B$9<>""),),0))),""))
A11式をA14までコピー

E1式
=IFERROR(INDEX(A:A,MATCH(LARGE($C$1:$C$14,ROW(A1)),$C$1:$C$14,0)),"")
F1式
=IFERROR(INDEX(B:B,MATCH(LARGE($C$1:$C$14,ROW(A1)),$C$1:$C$14,0)),"")
E1~F1下方へオートフィル。

A11~A14式は特に必要ありませんが、データによっては繰り返し処理が必要である。
この方法はA1~A4のカウントで１以上が２つあった場合は最初の出現した文字列でA6~A9の文字列置換をしますので注意下さい。

もっとスマートな方法が他の方から提示されたら、そちらを採用下さい。

お礼

とても丁寧な回答ありがとうございます！実際の画像まで付けて頂いて分かりやすいです。なるほど、置換して消していく方法ですね。確かにこの方法であれば先頭に単語が来ていなくてもランキング対象に出来ますね。良い気づきになりました。ありがとうございます。