こんにちは。情報処理部の時間です。
最近、私はExcelの作業にハマりつつある今日この頃です。……いや、むしろ職場の業務で無秩序なExcelファイルを相手にしてドツボにはまりつつある今日この頃です。
そこで気が向いたときに、自身の勉強とリマインド、読んでくださる方への共有を兼ねて、Excelのテクニックをご紹介したいと思います。
一応きららアニメにも絡めていって、趣味全開のつもりです。
この記事では、「１セル内の任意の文字数のカウント」を活用します。
目的と手段
目的
きららアニメ全37作品のタイトルに出現するひらがな（＆カタカナ）の数のカウント。
手段
- １セル内の任意の文字数をカウントし、Excel上の表へのリストアップ
- ↑具体的には、LEN関数とSUBSTITUTE関数の使用
集計表の作成
まずは、これから記入作業を行うための集計表の作成から。
こんなイメージの表で、列に作品タイトル、行に文字を設定しました。
文字の行ではひらがなの五十音に加えて、濁音と半濁音、記号（！、？、－）、拗音も用意してあります。
行は、あ行の「あ」(2行)～拗音の「ゎ」(85行)。
列は、「ひだまりスケッチ」(C列)～「まちカドまぞく」(AM列)。
各文字の合計（AN列）。
作品のリストは、良ければこちらを参考にご一読下さい（宣伝）
関数の使用
最終的には、
――けいおん！ → け×１、い×１、お×１、ん×１、！×１
――まちカドまぞく → ま×２、ち×１、ぞ×１、く×１
と、一望できる集計表の完成が理想です。
Excelの流儀に従い、オートフィルで楽々リストアップできるようなセル内計算式を記述します。
活躍するのは、LEN関数とSUBSTITUTE関数。
LEN関数
LEN関数は、指定した文字列の文字数を返してくれます。次のように書くだけ。
= LEN(文字列)
SUBSTITUTE関数
SUBSTITUTE関数は、対象のセルで文字列の置換を行うことができます。次のように書きます。
= SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
４つ目の引数である[置換対象]は、今回は使用しないため省略。
注目していただきたいのが、５行目の「お母さん先生」です。
置換文字列を「""」とすることで、「先生」を削除して「お母さん」に変えています。
LEN関数で文字数をカウントすると、ちゃんと４文字と出力されるはず。これ、あとで使います。
組み合わせた関数
２つの関数を使って、目的を達成します。と言うと大袈裟ですが、やることはとてもシンプルです。次の式をご覧ください。
任意の文字数 = 作品タイトルの文字数 – (作品タイトル - 任意の文字)の文字数
文字列のカウントと、特定の文字列の置換及び削除を学んだ後なら、次のようにExcelで表すことができます。
= LEN(作品タイトル) - LEN( SUBSTITUTE(作品タイトル, 任意の文字, "") )
けいおん列で、任意の文字数のカウントがちゃんとできていますね。
ただし、「！」と「？」に関しては半角・全角が混在しているため、ここだけはASC関数を新たに使います。ASC関数は、全角を半角に変換します。
= LEN(作品タイトル) - LEN( SUBSTITUTE(作品タイトル, 任意の文字, "") ) + LEN(作品タイトル) - LEN( SUBSTITUTE(作品タイトル, ASC(“！” or “？”), "") )
あとはこれを集計表全体に適用するわけですが、その前に。
＄マークは行か列の固定。これが意外と重要。
オートフィルで縦や横に引っ張ると、参照するセルまでずれてしまいます。
列を変えたくないときはアルファベットの前に（$B3）、行を変えたくないときは数字の前に（J$1）、＄マークを入れてください。
そして出来上がりがこちら！
……って、やろうとしましたけど、ただのでかい表なのでやめました笑
代わりに。
ウィンドウの固定で表を見渡しやすく。
右や下にスクロールしすぎると、何の行と列が交差しているのかわからなくなりますよね。
そこで、まずは、見出しの行のすぐ下かつ見出しの列のすぐ右のセルを選択してください（C2）。次に、上の表示タブ > ウィンドウ枠の固定を実行。
このように、スクロールしても見出しがついてきます。ゆゆ式の「ゆ」の数までよくわかります。他の結果も正しいことをご確認ください。
以上で、作品ごとの任意の文字数がわかりました。
合計列は、言わずもがなのSUM関数でお求めください！
おまけ
さっきのままだと文字の合計が一列で見づらいので、こんな風にまとめてみたり。
ここではVLOOKUP関数が活躍したのですが、その紹介はまた後日。
ちなみにカタカナやアルファベットでもカウントしたい場合は、集計表にその文字の行を足すだけです。
今日のまとめ
ひらがなの最多は「ま」で７文字。ただし、ひだまり全４期とまちカドまぞくで稼いでいます。
意外と少ない、ぱ行と拗音。
おわりに
以上となります。
いつもと雰囲気異なりますが、最後まで読んでいただけたなら幸いです。
ありがとうございました。
