Excelで文字列を集計する方法

まずは下の図をみていただきたい．左のような表から，右の表のように集計したいのだ．

そもそもPythonやらRubyやら使って簡単なスクリプトで処理すれば一番簡単なのだが，自動化してプログラミングに馴染みのない人にも使ってもらいたいという事情がある．

ピボットテーブルで集計するアイデアも考えられるが，ピボットテーブルで数値以外を扱うのはいささか手の込んだ処理をしなければならない．最近のExcelにはPower Pivotという機能が備わっていて，ピボットテーブルで文字列を扱うのは比較的簡単にできるようになったらしいが，Mac版のExcelではそのやり方を発見できなかった（誰か教えてください）．

というわけで，いささか泥臭いやり方だが，次のような手順で実施した．

まずは対象とするデータである．この状態から始めよう．左に2列，作業用の列を挿入し，A列に「出現順」，B列に「ID」とラベルを付けておく（これは，メモ用なので，なんでもよい）．

セルA2に「=COUNTIF(E$2:E2, E2)」と入力する．

ここで，範囲指定に行絶対参照を指定しているところがミソだ．このセルA2から，下に向かってドラッグして連続コピーしていくと，A3には「=COUNTIF(E$2:E3,E3)」A4には「=COUNTIF(E$2:E4,E4)」というような式が入る．探索範囲の開始位置を最上部に固定して，同じ行まで自動的に広げている点が工夫した点で，そのなかでグループの記号を数えているので，その数は，上から数えたときのその記号の出現順位となるわけである．

下までコピーするとこんな感じになる．

これを使って，それぞれの行に一意のIDを付けよう．IDは，「グループ名＋その出現順」とする．すなわち，山田太郎君はA1，斎藤忍さんはA2，新山貴子さんはB1という具合だ．

これは簡単で，セルB2に「=E2&A2」と入れればよい．&は，セルの内容をそのままくっつける演算子である．

下までコピーするとこんな感じ．

準備が整ったので，新しい表を用意しよう．なお，出現順のカラムはもう使わないので「表示しない」設定にしておいた．

右側に，新しいフォーマットの表を用意する．A〜Eと1〜3が太字・右寄せになっているのは，趣味の問題なのであまり気にしないでよい．

VLOOKUP関数を使って，一気に拾い集めてしまえばOKである．セルG2に，次の式を入れる．

=VLOOKUP($F2&G$1, $B$2:$C$16, 2, FALSE)

列絶対参照，行絶対参照，絶対参照と，絶対参照各パターンがてんこ盛りである．新たな表の行ラベルと列ラベルを使い，A1〜E3までのIDを自動生成しているところ，それぞれがずれないように行と列の絶対参照をしているところなどが見どころといえよう．LOOKUP先の表を絶対参照で固定するのは，しばしば使うのでお馴染みかもしれない．

列方向，行方向にがさっと連続コピーすれば，できあがりである．

めでたし．めでたし．