ExcelのTips

　仕事中に、アンケートの分析をすることになった。今回使ったアンケートシステムでは、複数選択可能な設問については回答者が選択した番号がカンマ区切りで記録される（画像の矢印左側）。単純集計するのであればこのデータを全部書き出して、カンマでばらせば、Excelで集計可能となる。
　でも、ほかの設問とのクロスを取ろうと思うと、回答者ごとに選んだ選択肢にフラッグを立てていく（画像の矢印右側）方が扱いやすい。これをどうやって実現するか。
　同僚に聞いてみたりしたところ、例えば、E2セルに入れる数式をこうしてはどうかというアドバイスがあった。

=iferror(if(find(E$1,$B2),1),0)

　確かに、これを右の表いっぱいにコピーするときちんとばらせることができた。
　この数式の意味は、
　１）FIND関数:B2（1,2,4）にE1の内容（つまり1）があれば、その文字列中に現れる場所を返すが、なければエラーになる。
　２）IF関数:上記のFIND関数によって値が得られれば1を返す
　３）ERRORIF関数:上記のIF関数が正しければそのまま1を返し、エラーであれば0を返す
という意味だ。IFが絡めてあるのは、FINDは文字列が初出する場所を返すので任意の自然数が値となり、０／１にはならないからだ。IFを使わないようにしたければ、０／１になるようにFINDを2回使って、

=iferror(find(E$1,$B2)/find(E$1,$B2),0)

とすれば良い。どっちがエレガントなのかは個人のセンス。

　さて、今回のアンケートは選択肢が10個以上ある。そうすると、最初のFIND関数で不具合が起きることになる。つまり、「10,12」とある回答をばらしたときに、選択肢1の欄にもフラッグ1が立ってしまう。選択肢1の欄のFIND関数が数字の1が最初に出てくる場所を探すからだ。同様に選択肢2にもフラッグが立つ。
　どうしようか。

　いろいろ調べていくうちに、ワイルドカード検索を使えば良いことがわかった。つまり、選択肢2なら、回答のパターンは、「２」「1,2」「2,（3以降の任意の選択肢）」「1,2,（3以降の任意の選択肢）」のいずれかであることから、回答の文字列に”2””*,2””2,*”*,2,*”のいずれかが含まれているかどうかを検索し、あるものに1を立てるようにすれば良い。さて、これをどう実現するか。文字列の検索はFIND。しかし、これはワイルドカードが使えない。IFも同様。ワイルドカードが使える関数は、SEARCH、MATCH、COUNTIFなどとなる。
　COUNTIFは検索結果があれば1としてカウントするものだ。そこで、これを使って、

=countif($B2,E$1)+countif($B2,”*,”&E$1)+countif($B2,E$1&”,*”)+countif($B2,”*,”&E$1&”,*”)

　としてみた。各項は０か１を返すが、同時に1が返ることはないので、足し算で十分。これでどうだ。
　うまくいった。なお、SEARCHだと最初に現れる文字の場所を返すので０／１にはならない。

　ふぅ、久しぶりに勉強した。