背景
たくさんのグループについて、そのメンバーを調べる作業がありました。グループごとのメンバー数は全くバラバラで、多くのグループによく出現するメンバーとその他のメンバーがたくさん、という感じでした。
Office しか入っていないような PC しか作業には使えない状況でした。
当初：主なメンバーのみの集計
主なメンバーの出現回数だけの集計でよいので、その他メンバーは適当に済まします。
Excel で 1 行に 1 グループずつ記録していきます。A 列にはグループ名を、続いて主なメンバー名ごとの列を作って「o」「x」を、その他メンバーは次の列に名前を列挙しました。
できあがった表はこんな感じ。
| A | B | C | D | E |
|---|---|---|---|---|
| グループ | メンバー1 | メンバー2 | メンバー3 | その他メンバー |
| AAA | ○ | x | ○ | m1, m2, m3 |
| BBB | x | ○ | ○ | m3, m4 |
| CCC | ○ | ○ | ○ | |
| DDD | ○ | ○ | x | m1, m4, m5 |
| EEE | x | ○ | x | m2, m3, m5, m6, m8 |
メンバー 1 の出現回数は、Excel関数 =COUNTIF(B3:B100,"○") で数えられます。
すべての主なメンバーの出現回数は、それらの sum() です。
予定変更：全てのメンバーを集計
ところが、その他のメンバーの全数も必要になりました。
幸い、その他メンバーを個別に集計する必要はありませんでした。
ですが、セル内に列挙してあるメンバーの出現回数を数えなければいけません。
その他メンバーを 1 個ずつセルに分けてあれば COUNTA(範囲) で数えられますが、そのようにはなっていません。「データ」→「区切り位置」コマンドを使えばメンバーごとにセルをばらせるようですが、このときは知りませんでした。
VBA なら Split() と UBound() で調べられますが、マクロを実行したときにしか数えられません。
Excel 関数で実現できないかと思い、文字列操作関数を調べたのですが、`Split()' に相当する関数はありません。
なんとかひねり出したのが次の式でした。
=LEN(E3)-LEN(SUBSTITUTE(E3,",",""))+1
, を全て削除した後の文字列の長さを元の文字列の長さから引けば、, の個数が数えられます。メンバーの数は , より1個多いので '+1` します。これで列挙したメンバーの数が数えられます。
その他メンバーが全くいないグループもあるので、式を少し直します。
=IF(E3="",0,LEN(E3)-LEN(SUBSTITUTE(E3,",",""))+1)
これを sum() で足し算すれば、その他メンバーの全数がわかります。
おわりに
人間が見やすいように表を作ると、機械的な集計はしづらくなる、という話でした。