エクセルで文字列操作やってみたらできた

「なかのひと」のあしあとデータを加工するのにエクセルを使ってみた。数ヶ月後には社畜になる身ですから「文字列操作なんてシェルスクリプトで十分だぜ！」なんて言ってられなくなる気がする。時代はオフィスですよ。ってことで、今回やりたい作業はこんなかんじ。



立命館大学5　 日本電気　 東海大学　 三井化学　 富士通[fujitsu.com]　 東京理科大学13

左はアクセス元、右の数字がアクセス数。ちなみにアクセススが5以下だと記載なし。これをアクセス数でソートしたいわけです。イメージはこんな感じ。Aに下の文字列、Bに数値を取り除いた文字列、Cに数値。

これをエクセルで実現するには次のような操作をします。

最初はデータ例のように一行の文字列になっています。これを、要素ごとに挟まれている「　」で区切ることにより分割します。エクセルで操作する場合には

「データ」→「区切り位置」→「スペースによって右または〜」

で分割されます。このままでは扱いずらいので、この長い一行の行列をコピーし、貼り付ける時に形式を

「行列を入れ替える」

とします。ちなみに、正規表現での置換が可能なエディタ（秀丸など）で改行の正規表現「\n」を使って置換しても問題ありません。この時点でデータは次のように整形されるはずです。

ここからエクセルの関数を使います。A列に「なかのひと」からとってきたデータがあるとしたら、その隣のB列に次のような関数を指定します。

=VALUE(MID(A1,MIN(FIND({"１","２","３","４","５","６","７","８","９","０"},JIS(A1)&"１２３４５６７８９０")),256))

意味は…まあ面倒なので省略。これを入れれば基本的に成功です。ただし「日本電気」のようにアクセス数が記載されていないものについてはB列に「#VALUE!」と表示されているでしょう。面倒なのでC列に次のような関数を指定してください。

=IF(COUNTIF(B1,"#VALUE!"),"",B1)

こうすることで「#VALUE!」を空白に置き換えることができます。以上の操作により、データは次のような形になっているでしょう。

これは簡単です。C列に数値が抜き出してあるわけですから、これを利用してD列に次の関数を指定します。

=SUBSTITUTE(A1,C1,"")

これで文字列のみを抜き出すことができます。

最後にソートです。ソートしたい列を指定して

「データ」→「並べ替え」の「降順」

これで完成です。ただ、今回のデータ形式でB列を指定したソートを行うと「#VALUE!」が上の方に来てしまい（C列だと空白が上にきますので）気に入りません。仕方がないので更にE列に関数を指定します。

=IF(COUNTIF(B1,"#VALUE!"),1,B1)

これでアクセススが記載されいないデータを全て1とすることができます。結果、E列を基準にソートすることで最終的に次のようなデータが完成します。あとはD列とE列を使って好きに使ってみてください。

もっと良いやり方がありそうだけど、とりあえずできた。そのうちエクセルの関数についても学んでみようかな。

他のアクセス解析併用すると「なかのひと」で拾いきれてない企業が沢山あった。大学はac.jpだから全部抽出できてるっぽかったけど。この辺を少し検証しながら恒例のアクセスランキングを作成するとしようかな。