グーグルフォームでの「その他」記述のチェック方法

 珍しく、ネットtips的な記事。タイトルにあるよう、グーグルフォームでの回答のエクセルシート(スプレッドシート)から、その他の記述があるセルを見やすくする方法。

 

 グーグルフォームを、ご多分にもれず愛用しているわけですが、あれ単体で取っている分にはとても便利なんですよね。結果もちゃんとグラフにしてくれるし、それを貼り出せば、それなりに形になる。

 ただ、あれで困るのは、「複数選択可」で、しかも「その他」回答を含めた場合だと思うんですよ。……少なくとも私はそうです。

 

 というのも……たとえば、「好きな寿司ネタは?(複数選択可)」で

  • マグロ
  • サーモン
  • タイ
  • はまち
  • その他(自由記述)

のように選択肢を設けると……

マグロ, タイ
サーモン, タイ, はまち
マグロ, はまち, 玉子
マグロ, サーモン, タイ, はまち, あなごとかエビも好きです
タイ, ねぎトロとアジ

 

 こんな感じでセルには入るんですよね。

 これ、先ほど云ったようにグーグルフォーム単体では、マグロの数もタイの数もカウントしてくれるし、自由記述はそれぞれ1としてカウントしてくれる。でも、じゃあこれをエクセル(またはグーグルスプレッドシート)として抜き出したときに、どうやって処理するの?ということに孤軍奮闘した結果を残す。誰かに役に立つかもしれないので(&もっとスマートなやり方あるかもしれないとは思うんだけど記事は見つからなかったし……)。

 

 

 

選択肢のあるセルのカウントの仕方

 これはまあ、簡単です。ネット見たらすぐ分かる。私がやったのは以下の方法。

 上記のように、回答セルがズラズラとあります。まず、右側に一行空行を挿入。んで、回答セルの下に、選択肢をずらずらと並べます。このように(スクショあった方がいいかと思いスクショに変更)。

f:id:Librairie_ymk:20201121082045p:plain

 

 んで、マグロの隣のセルに、次の数式を記入。

=COUNTIF(B$3:B$7,"*"&B10&"*")

 

 一応、解説。

 countifは、「これがあるか数えてね」って数式で、括弧内に「どの範囲か&どれか」を記述します。 

 「B$3:B$7」は、B3:B7の範囲(かつ3-7の範囲は常に固定)という意味。これ、3-7を固定にしているのは、このセル以外にも通用する汎用性を担保するため。たとえば次に、Dの列に「嫌いな寿司ネタは?」としてまたズラズラと回答があるとしたら、このセルをそのままコピペすると、「=COUNTIF(D$3:D$7,"*"&D10&"*")」にしてくれるという寸法です。

 「"*"&B10&"*"」これは、B10の文字列を含むセル、という意味ですな。前にある「"*"&」は「前に文字があるかもね」、うしろにある「&"*"」は「うしろに文字があるかもね」と云うくらいの意味。これがないと、「マグロ」と完全一致する(=それしか書いてない)セルだけ数えることになって、「マグロ, タイ」は除外されてしまう。グーグルフォームのように、一つのセルの中にいくつもの文字列をぶっ込んでくる形式で結果を返してくれる場合は、こう書いておかないと意味がない。

 

 そしてあとは、そのセルを下にズラズラ増やしてやればよいだけです。セルの右下にカーソル合わせて、「+」記号になったらドラッグ、ですな。

 あ、グーグルフォームだと、最初の選択肢である「マグロ」は絶対最初に来るので、マグロ単体で考えたら「B10&"*"」でいけます。ただ、次のサーモンとかタイとかは、このように右下クリックでコピペ大作戦なので、汎用性を考えて「"*"&B10&"*"」にしています。

 そしてカウントされたのがこちらになります。

 

 

f:id:Librairie_ymk:20201121083438p:plain

 

 まあ、ここまでは、そう難しいことではない。

 

単体での回答をカウントする

 次、単体での回答をカウントするケースが必要になるかもしれない。つまり、「タイだけ好き、と答えた人の人数は?」と知りたいときの場合だ。

 ……まあ好きな寿司ネタくらいなら必要とされないかもしれないですが、たとえば通勤手段などを「自転車、バス、徒歩、車」などと尋ねていて、「自転車のみで通勤している人は?」が知りたい場合はあるかもしれない(つーか、私のやつではそれが必要になったのですよ)。

 

 ……まあ、これは簡単ですね。先ほどと一緒で、データ範囲をB3:B7、該当するデータがB10にあるとします。

 

=COUNTIF(B$3:B$7,B10)

 

 先ほど、「"*"」を省くと完全一致するものしか引っかけないよ、といったことの逆、ですね。完全一致するものを探したければ、それを取ればいいのです。

 

その他の記述があるセルの数をカウントする

 本題来ました。グーグルフォームで、まずは、「その他」に解答があったものをどうやって識別するか。

 これ、もっとスマートなやり方はある可能性があるのですが、私がやったのは次の方法。

f:id:Librairie_ymk:20201121103657p:plain

 

=COUNTIFS(B$3:B$7,"<>"&"*"&B10,B$3:B$7,"<>"&"*"&B11,B$3:B$7,"<>"&"*"&B12,B$3:B$7,"<>"&"*"&B13)

 

 解説。

 countifsは、countifの複数形。つまり、次に当てはまる条件のものがいくつかあるか、という数式です。

 これ、見にくいので、色分けしますが、

 =COUNTIFS(B$3:B$7,"<>"&"*"&B10,B$3:B$7,"<>"&"*"&B11,B$3:B$7,"<>"&"*"&B12,B$3:B$7,"<>"&"*"&B13)

 この四つの条件から構成されています。であり、であり、であり、オレンジであるセルをカウントしてね、と。

 んで、B$3:B$7,"<>"&"*"&B10の部分です。最初のB$3:B$7は、ただの範囲指定ですね(上で見たとおり)。

 で、ややこしいのが次。"<>"&"*"&B10は、"<>"&"*"&B10で構成されています。最初の"<>"は、「以外のもの」を表す。最後のB10は文字列ですな。この場合は、マグロ。なので"*"&B10は、「なんか前に文字があっても最後がマグロで終わる」を意味する。つーことで、B$3:B$7,"<>"&"*"&B10は、「マグロで終わる文字列でないやつ」になるわけですよ。

 よって、上記のcountifsは、「最後の文字が、マグロでもサーモンでもタイでもはまちでもないセル」=「自由記述に何か文字が書かれているセル」or「何も書かれていないセル」の数を数えていることになります。*1

 

その他があるセルを色分けする

 私はこの式を、条件付き書式にも使いました。セルの数をカウントするだけではなく、「その他」の記述があるセルだけ色分けすると、分かりよいな、と。

 このあたりは説明省略も出来そうですが、まず範囲指定。この場合はB3:B7の範囲を指定しておいてから、

 

f:id:Librairie_ymk:20201121111132p:plain

 

 

 このように、条件付き書式で、「その他のルール」を選ぶ。

 

f:id:Librairie_ymk:20201121111252p:plain

 

 そして、「数式を指定して書式設定するセルを決定」。

 すると、下の欄に数式が書けるので、以下の数式を入力。

 

=COUNTIFS(B3,"<>"&"*"&B$10,B3,"<>"&"*"&B$11,B3,"<>"&"*"&B$12,B3,"<>"&"*"&B$13)

 

 先のやつとほぼ一緒ですが、範囲指定するセルの任意の一箇所でいいらしいので、範囲は一番上のB3にしています。んで条件をB10でなく「B$10」にしてるのは、範囲指定しているので、10は固定にしないとズレていくからですね(そしてBまで固定しちゃうと、他の列にコピペしにくいから)。

 ここ、めんどくさいんですが、うっかりカーソルキーを触ると、隣のセルとか入力してくれちゃうんですよね……。ここは本当に何回もムキー!ってなったとことです。

 

 これが完成すると、

f:id:Librairie_ymk:20201121111654p:plain

 

 このように、「その他」記述があったセルのみ、強調表示してくれます。便利。

 

おわりに

 なんか、もっと便利なやり方あるかもしれないのですが、私が取ったのはこの方法。

 一つに、文字列を数字に置き換えて(置換して)、選択肢はすべて数字で処理するやり方も考えたんですけどね……。それはそれでミスが出てしまうかもなあ、と思い、文字列を残したまま、「その他」記述が浮かび上がる方式を考えました。

 

 なんかね、もっとスマートなやり方があるのではないか、との懸念は拭えませんが、グーグルフォームで取ったデータの処理の仕方が、あまりネット上に落ちていない気がしたので、汚いものでもないよりマシか、と思い載せておきます。

*1:ここで、「何も書かれていないセルは除く」こともできるとは思いますが、その作業は必要がなかったので私は条件に入れていません。多分これの応用で、なんとかなると思います。