Googleフォーム▶Googleスプレッドシートで「やりたいを実現する」役立つ関数
SPLIT関数、TEXTJOIN関数 の超応用例シリーズの番外編ってことで、実務で活用できる Googleフォームの回答をスプレッドシート連携した際に 使える数式を お題形式で取り上げたいと思います。
さらに今回の noteは Googleフォームネタではあるんですが、後半お題で登場する数式
実は 最新ネタの Googleスプレッドシートの 複数選択プルダウンの集計でもそのまま使えます！
最後まで要チェックや！
なお、今回はTEXTJOIN関数の出番はなく、SPLIT関数とその一つ前の超応用例シリーズで登場した ARRAYFOMRULA関数がメインとなります。（あの最強関数も登場）
前回は今回の前提となる Googleフォームとスプレッドシートをリンクした際の挙動の理解と注意点について書きました。
Googleフォームの回答行に数式が入らない・書式が適用できない問題を解決する
前回の noteの最後に
3-5. スプレッドシートへ追加される回答は 真っ新な行の挿入である
こちらについて書きました。
この挙動で問題となるのが書式設定と表示形式、そして数式が追加された回答の行に入らない問題です。
フィルコピーで事前に数式を入れておく方法は ✖
たとえば、フォームの回答をリンクしたスプレッドシートで 1列追加して タイムスタンプ（A列）の日時データから日付だけを抽出したい場合
日時を日付にする式
=IF(A2="",,INT(A2))
タイムスタンプのA列が空白のケースを考慮してIFで分岐させた上でINT関数を使うことが多いかと思います。
ちなみに EDATE関数を使う方法もあります。
日時を日付にする式
=IF(A2="",,EDATE(A2,0))
INT関数は数値の小数点以下を切り捨て整数にする関数です。
ExcelやGoogleスプレッドシートにおいては、日時データは 1日（24時間）を 1とした シリアル値であり、1日に満たない 〇時〇分〇秒 の部分は 小数となっています。
たとえば
2024/08/24 10:30 は
▼
45528.4375 というシリアル値。
※ 2024/08/24 が 45528
10:30 は 分であらわすと 630
これを日に単位変換すると 630÷60÷24 = 0.4375
というわけで、INTで整数部分のみとすることで日付化できるわけです。
で、この式をB2セルに入れて下にフィルコピーしておく。これが旧Excel的な式の入れ方です。
でも、事前に10行目のB10セルに =IF(A10="",,INT(A10)) が入った状態で、
「フォームの回答 出てこいや！」
と待ち構えていても
フォーム回答は 真っ新な行の挿入となる為、10行目には式も書式設定もない回答行が生成されてしまいます。
同じようにたとえば 別シートで 回答シートを参照しようと
='フォームの回答 1'!C1 を別シートの A1セルに入れて下にフィルコピーしていた場合も
新しい回答が11行目に入った時は、11行目に回答行が挿入された形なので、別シートの参照式が
='フォームの回答 1'!C11 (11行目は回答挿入前）
▼
='フォームの回答 1'!C12（11行目へ回答挿入後）
と、元々参照してた フォームの回答のシートの 元11行目は 上に1行挿入されたことで 一つ下の12行目となり、連動して参照していた式もズレてしまい、別シートから新しい回答を参照することが出きません。
もちろん新しい回答が入らない回答完了済みのシートであれば、この方法でも問題ありません。
しかし、回答受付中のアクティブなフォームを連携したGoogleスプレッドシートでは、フィルコピーした1セルずつ数式で参照するような式は効果がないってことです。
Googleフォームの回答をリンクしたスプレッドシートでは ARRAYFORMULAを使おう
この解決策が ARRAYFORMULA を使った配列数式です。
先ほどのA列のタイムスタンプの時刻データを日付だけにする式を
=ARRAYFORMULA(IF(A2:A="",,INT(A2:A)))
このように ARRAYFORMULAを使った配列式にして、回答範囲の1つ目の行である2行目の B2セルにだけ入れる。
※B3より下のB列には式の結果が展開されるので何もいれない
これによって 新しい回答の追加行に対しても数式を適用することができます。
回答の追加行にも数式が適用されて、B12 に数式の結果 45528 （2024/08/24 のシリアル値）が入ってるのがわかりますね。
ただし数式は配列式で適用できても、表示形式やセルの塗りつぶしは適用されてないので、シリアル値のまま白いセルになってしまっています。
回答行の書式は 条件付き書式で制御できる
セルの塗りつぶしや文字の色、太字などの「書式」に関しては、条件付き書式を設定することで、新しいGoogleフォームの回答行にも適用させることが出来ます。
たとえば B列の書式をセットしたい場合は、条件付き書式で 範囲をB:Bとして（保存後に 自動で B1:B100など 最終行の数字に変換される）、カスタム数式で
=COLUMN(B1)=2
こんな感じで「列番号が2である」を条件とした式をセットすれば
このように新しい回答の行であっても セルの色や文字の色といった書式を適用させることが出来ます。
閲覧用であれば 回答を別シートで参照しよう
しかし、日付 yyyy/MM/dd などの「表示形式」は、残念ながら Googleスプレッドシートの条件付き書式では設定ができません。（Excelだと出来る）
これは非常に厄介で、回答シートをテーブル化して
表計算の呼吸「日付の型」
を設定していても、追加された回答の数式を通した日付は シリアル値になってしまう頑固っぷり。
フォームの回答行の表示形式をコントロールするにはGASを使うしかないんですが、回答の手作業による編集などはしない 「閲覧用」ってことであれば、事前に表示形式を設定した他のシートから回答シートを丸ごと参照するという方法が使えます。
先ほどのように1セル単位で別シートで参照していた場合は、回答の際の行挿入に対応できませんが、
={'フォームの回答 1'!A:C}
もしくは
=ARRAYFORMULA('フォームの回答 1'!A:C)
このように範囲をまるごと参照できるスピル式をA1に入れ、あらかじめ列単位で表示形式を設定しておくことで
新しい回答が入った場合も参照している別シートでは、表示形式を適用させた形とすることが可能です。
今後は Googleフォームの回答をスプレッドシートにリンクした際は自動でテーブル化されるとのことなので、構造化参照の式の書き方も覚えておくと良いかもしれません。
=ARRAYFORMULA(表_1[#ALL])
このように ARRAYFORMULA や 条件付き書式、最後の手段で 別シートへ丸ごと参照といった方法で、GASを使わずに Googleフォームの新しい回答の行追加に対応することが出来ます。
ただ、どうしても回答シートで新しい回答に希望する表示形式をセットしたい場合は GASを使いましょう。
この新しい回答に書式や表示形式を適用するGASについては、別の機会に書きたいと思います。
Googleフォーム回答シートの見出し行にARRAYFORMULA式を入れたい
回答シート内で使う式として ARRAYFORMULAを使った数式を2行目に入れる方法を紹介しましたが、これは最低1件は回答が入っている状態でなければ使えません。
手入力で適当なダミーデータと数式を2行目に入れておいても、それは無視されて 式を入れてる行の上、2行目に回答が挿入されてしまいます。
【POINT】
Googleフォームの回答は、リンクしているスプレッドシートで回答として挿入されたデータの最終行の下に挿入される。
つまり2行目に1件だけ回答が入ったシートで、その回答行を8行目にドラッグした場合は、次の新しい回答は9行目に入ります。
こんな仕様になっているわけです。
というわけで、2行目にARRAYFORMULA式を入れる場合は、ダミーで手入力ではなくフォームから回答を入れる必要があります。
この1件目のダミー回答がどうしても許されない場合は、見出し行に式をいれるしかありません。
ただ、
={"日付";ARRAYFORMULA(IF(A2:A="",,INT(A2:A)))}
こんな感じで
{ "見出し行に入れる文字列" ; 2行目からの式 } と縦連結させた式
にして、1行目の見出し行に2行目からのARRAYFORMULA式を入れたいところですが、これはNGです。
このように参照しているA2:Aの上に回答が1行挿入されるので、回答が入ると式の範囲が A3:Aに切り替わってしまいます。
というわけで 参照範囲を A:Aとして見出し行に入れる式を組み立てる必要があります。
たとえば、
=ARRAYFORMULA(IF(ROW(A:A)=1,"日付",IF(A:A="",,INT(A:A))))
このように行番号が1の時は 「日付」という文字列を返す IF分岐をネストするとか
=ARRAYFORMULA(IFS(A:A="",,A:A="タイムスタンプ","日付",true,INT(A:A)))
A:Aが A1の見出しの値（タイムスタンプ）だった時を IFSで分岐させる、といった方法があります。
これらのA:Aを範囲とする式であれば
回答が1件も入っていない状態で先に見出し行に式を入れておいて、1件目の回答が2行目に入った時に数式の結果を反映することができます。
Googleフォームのチェックボックスをスプレッドシートで集計する
このように Googleフォームの回答とリンクしたスプレッドシートにおける基本は ARRAYFORMULAを使った配列数式を使うことがわかりました。
しかし質問タイプによっては、他の関数も必須で押さえておく必要があります。
その代表的な組み合わせの一つが、チェックボックスと SPLIT関数です。
ここから SPLIT関数超応用例の番外編 いってみましょう！
チェックボックスの回答は 一つのセルに複数の値が入る仕様
Googleフォームの質問の種類の一つ「チェックボックス」は、選択肢の中から複数を選ぶことが出来ます。
この回答をスプレッドシートにリンクした時どのようになるのか？
美味しさ, 食感, 栄養バランス, アレンジの多様さ
こんな感じでカンマ区切りデータで、チェックを入れた複数の値が一つのセルに格納されます。
Googleフォーム側では 回答の概要でチェックボックスは 横棒グラフになるんですが、
縦棒グラフがいい、円グラフにしたい、表にして多い順にまとめたい、他の要素と合わせてピボット表（クロス表）にしたい・・・
といった場合、このスプレッドシートに出力されたデータから自分でグラフ化、または集計表としてまとめる必要があります。
しかし本来の表計算ソフトの原則である、1セル1データに反したデータであるため、このままでは集計が出来ません。
数式による一工夫が必要となります。
SPLIT関数で フォームのチェックボックス回答を分割する
カンマで区切られたデータを 個々に分割するわけですから、ここで活用するのは直近で noteで紹介した SPLIT関数 ってことはわかりますね？
ただ、注意点があってスプレッドシートにリンクして書き出されたGoogleフォームのチェックボックス質問の回答は 実は単なるカンマ区切りではなく
カンマ + 半角スペース
で区切られています。
これを知らずに単に ，（カンマ）でSPLITしてしまうと、分割後のそれぞれのセルの頭に半角スペースが残ってしまい、集計でハマることがあります。
というわけでチェックボックスの回答を分割する場合は、
=SPLIT(C2,", ",FALSE)
こんな式になります。
"," カンマ ではなく
", " カンマ+半角スペース で分割し
第3引数の 個々の単文字での分割をFALSE指定
これによって 「味 おいしさ」のような選択肢の文字列内に 半角スペースが含まれる場合に誤った分割となることを回避しています。
SPLIT関数の基本 （第3引数FALSEで 文字列で区切る）
チェックボックスの選択肢には ,を含むテキストを設定しないように注意しましょう。
Q1. Googleフォームのチェックボックス質問の回答を集計したい
チェックボックス質問の回答を SPLITする方法がわかったところで、超応用例のお題に挑戦しみましょう！
▼
別シートに1つだけ数式を入れて Googleフォームの回答とリンクした シート名 フォームの回答 1 の C列のチェックボックスの回答を 多い順に集計して画像のような表にしたい。
どんな式を入れれば良いでしょうか？
フォームの回答 1 のダミーデータは以下を利用ください。
タイムスタンプ 好きなフルーツの選択 好きな理由
2024/08/16 11:36:51 リンゴ 美味しさ, 食感, 栄養バランス, アレンジの多様さ
2024/08/16 11:51:16 バナナ 美味しさ, コスパ, 栄養バランス
2024/08/17 10:35:16 バナナ 食べやすさ, コスパ, 栄養バランス, アレンジの多様さ
2024/08/17 11:04:15 オレンジ 美味しさ, 香り, コスパ
2024/08/17 11:05:13 オレンジ 美味しさ, 香り
2024/08/17 13:34:04 バナナ 美味しさ
2024/08/17 13:35:58 リンゴ 美味しさ, 香り
2024/08/17 14:11:14 オレンジ コスパ
2024/08/17 15:37:21 リンゴ 美味しさ, 香り, 食感
2024/08/24 13:41:55 リンゴ 香り
2024/08/24 13:55:47 オレンジ 食べやすさ
2024/08/24 13:57:02 バナナ 美味しさ, 香り
2024/08/24 14:00:49 バナナ 食べやすさ, 栄養バランス回答が1件も入っていない状態でも使えるように参照範囲は
'フォームの回答 1'!C:C
とすることを条件とします。
考えてみましょう！
↓↓
ここから回答です。
↓↓
A1. Googleフォームのチェックボックス質問の回答を集計する
回答です。
=ARRAYFORMULA(QUERY(
TOCOL(SPLIT('フォームの回答 1'!C:C,", ",FALSE),3),
"select Col1,count(Col1) group by Col1 order by count(Col1) desc
label count(Col1) '回答数'",1))
Googleスプレッドシート最強集計関数の QUERY関数 を使います。
解説していきましょう。
まず、範囲を C:Cと列全体で指定しないといけないので、SPLITの式にARRAYFORMULAを組み合わせて
=ARRAYFORMULA(SPLIT('フォームの回答 1'!C:C,", ",FALSE))
こんな式で チェックで回答した項目を 1データ1セルに分割します。
ここで集計に際して気になるのが
見出しの「好きな理由」
下の方の空白をSPLITして返っている #VALUE! エラー
ですが、この段階でIF関数を使って対処する必要はありません。どちらも後工程で他の関数で「ついでに」処理できます。
このSPLITで分割して複数列にバラけた結果を 集計しやすい1列データにします。
ここで使うのがTOCOL関数です。
=ARRAYFORMULA(TOCOL(SPLIT('フォームの回答 1'!C:C,", ",FALSE),3))
ここのポイントは TOCOL関数の第2引数を 3 とすることで、空白とエラーを無視しつつ 1列データにしている点です。
先ほどあった #VALUE! エラー はこのTOCOLによる1列化のついでに消されています。
TOCOLがGoogleスプレッドシートに登場する前は、事前にIF関数でエラー処理した上で FLATTEN関数で1列化、空白は QUERY関数 の where句で対処していましたが、いやー楽になったもんです。
この1列データを最後に QUERY関数で集計します。
"select Col1,count(Col1) group by Col1"))
まず、select 句と group by で表の大枠を生成します。
count などの集計関数と group by はセットで使う必要があります。
"select Col1,count(Col1) group by Col1 order by count(Col1) desc"))
これを orde by で並び替えます。
並び替えのキーとする列は count(Col1) で、desc（降順）を指定します。
2列目のラベルが count だとわかりづらいので、ラベルを"回答数"に変更しましょう。
label を使って count(Col1) の見出しを '回答数' とすると記述します。
"select Col1,count(Col1) group by Col1 order by count(Col1) desc label count(Col1) '回答数'",1))
最後に本来見出しである「好きな理由」がデータとしてカウントされてしまっているので、見出しとしてそのまま使うように指定します。
QUERY関数の 第3引数を 1とすることで 1行目を見出し行指定。
これで完成です。
=ARRAYFORMULA(QUERY(
TOCOL(SPLIT('フォームの回答 1'!C:C,", ",FALSE),3),
"select Col1,count(Col1) group by Col1 order by count(Col1) desc
label count(Col1) '回答数'",1))
もちろん UNIQUE関数、COUNTIF関数、SORT関数、これらを組み合わせても対処出来ますし、QUERY関数はシート関数としては独特な記述である上に、句の順番や スペースのあるなし 大文字・小文字など多数のエラーポイントなど 苦手って人も多い関数です。
でも、こんな感じで一気に集計表を生成できるってのを体感しちゃうと、QUERY関数使わにゃ損損って感じですよね。
数式で作成した集計表からグラフを生成すれば、回答毎のリアルタイムの結果をグラフの変化で可視化することができます。
Q2. Googleフォームのチェックボックス質問の回答を 別の質問の回答と 合わせてクロス集計したい
次に もう一歩進めたこんな集計をしてみましょう。
フォームの回答 1のシートの Ｂ列のフルーツを縦軸、好きな理由を横軸としたクロス集計です。
こちらはどんな式を入れればよいでしょうか？もちろん式は1つだけです。
タイムスタンプ 好きなフルーツの選択 好きな理由
2024/08/16 11:36:51 リンゴ 美味しさ, 食感, 栄養バランス, アレンジの多様さ
2024/08/16 11:51:16 バナナ 美味しさ, コスパ, 栄養バランス
2024/08/17 10:35:16 バナナ 食べやすさ, コスパ, 栄養バランス, アレンジの多様さ
2024/08/17 11:04:15 オレンジ 美味しさ, 香り, コスパ
2024/08/17 11:05:13 オレンジ 美味しさ, 香り
2024/08/17 13:34:04 バナナ 美味しさ
2024/08/17 13:35:58 リンゴ 美味しさ, 香り
2024/08/17 14:11:14 オレンジ コスパ
2024/08/17 15:37:21 リンゴ 美味しさ, 香り, 食感
2024/08/24 13:41:55 リンゴ 香り
2024/08/24 13:55:47 オレンジ 食べやすさ
2024/08/24 13:57:02 バナナ 美味しさ, 香り
2024/08/24 14:00:49 バナナ 食べやすさ, 栄養バランス
2024/08/24 16:03:48 リンゴ 美味しさ, コスパ, 映え回答が1行増えてるので、再度元データを用意しました。
こちらが フォームの回答 1 というシートに入っているものとして、今回も同じく参照範囲は B:B、C:Cといった全体指定を条件とします。
考えてみましょう！
↓↓
ここから回答です。
↓↓
A2. Googleフォームのチェックボックス質問の回答を 別の質問の回答と 合わせてクロス集計する
回答です。
=ARRAYFORMULA(LET(
a,SPLIT('フォームの回答 1'!C:C,", ",FALSE),
b,TOCOL(IF(a="",,'フォームの回答 1'!B:B&"_"&a),3),
c,SPLIT(b,"_"),
QUERY(c,"select Col1,count(Col1) group by Col1 pivot Col2",1)
))
QUERY式自体は先ほどのものよりシンプルですが、事前準備がやや煩雑なのでLET関数で 第1形態を a、第2形態を b ・・・ としてわかりやすくしています。
順番に解説していきましょう。
まず第1形態の a は フォームの回答の C列の回答を SPLIT関数で分割したものです。ここは先ほどと同じなので大丈夫ですね。
今回も見出しとエラーがデータに含まれてますが、このまま進みます。
第2形態の b は、2つの処理が入ってます。
まずは IF関数で 先ほどの結果 a が 空白以外の時には
'フォームの回答 1'!B:B&"_"&a
として、各セルに分割された好きな理由を、対応する好きなフルーツと "_" アンダーバーを区切り文字として連結させます。
こんな感じのデータになります。
先ほどエラーだった箇所はエラーのまま残っています。
これをTOCOL関数で第3引数3として、縦1列データに変換しつつ 空白と エラーを除外します。
これで邪魔なエラーは消えました。
最後に 再びSPLITで先ほど B列とC列を連結した _ アンダーバーで区切ります。
これで好きなフルーツと好きな理由が紐づいた綺麗な集計しやすい2列データが作成できました。
あとは、これをQUERY関数で処理するだけですね！
QUERY(c,"select Col1,count(Col1) group by Col1 pivot Col2",1)
QUERY関数の pivot句を使うことで、縦横で集計する表が完成しました。
Q3. Googleフォームの回答シートでチェックボックスの回答を分割して、その分割数に合わせてナンバーを付けたタイトル行を生成したい
最後にオマケです。フォームの回答シートで チェックボックスの回答を SPLIT関数で分割する方法は理解できたかと思います。
その分割された回答の分割数に合わせてタイトル行を変動させたい。という要件を加えたお題です。
タイトル行は C1に入っている 「好きな理由」にナンバーを付けた
好きな理由1 好きな理由2 好きな理由3・・・
と D列から順に入ります。
D1にはどんな式を入れたら良いでしょうか？
範囲指定は C:C とします。
元データはQ2のものを利用し、データが追加された時の挙動の確認は C16セルに
美味しさ, 香り, 食感, コスパ, 栄養バランス, 映えこちらを貼り付けて検証ください。
考えてみましょう！
↓↓
ここから回答です。
↓↓
A3. Googleフォームの回答シートでチェックボックスの回答を分割して、その分割数に合わせてナンバーを付けたタイトル行を生成する
回答です。
=ARRAYFORMULA(LET(
data,IFERROR(SPLIT(C:C,", ",FALSE)),
IF(SEQUENCE(ROWS(data))=1,C1&SEQUENCE(1,COLUMNS(data)),data)))
今回もLET関数を使って2段階でまとめています。
まず1段階目は 普通にIFERRORでエラーを空白化した C列をSPLITする式です。
この結果を dataと置きます。
1行目の時だけ C1の値「好きな理由」に番号を付けたものにしたいので、 data という 配列から
行数（高さ） ・・・ ROWS(data)
列数（横幅） ・・・ COLUMNS(data)
でサイズを取得して SEQUENCE関数を使ってバーチャルな 行番号、列番号を付与します。
行番号 ・・・ SEQUNECE(ROWS(data))
列番号 ・・・ SEQUENCE(1,COLUMNS(data))
このバーチャルな行番号、列番号を使って
IF(SEQUENCE(ROWS(data))=1,C1&SEQUENCE(1,COLUMNS(data)),data))
↑ 行番号が1の時は C1に&で列番号を連結したものを返し、それ以外はdataをそのまま返すと いう式にすれば良いですね。
配列にバーチャルな行番号、列番号を付与する処理は過去の SEQUENCEの回の note やこれまでも何度か登場しています。
LET関数と組み合わせると記述も簡潔になり非常に効果的です。
Googleフォームのチェックボックス 回答をスプレッドシート側の数式で集計、処理する方法を お題形式で3つ紹介しました。
SPLIT関数の式はこのまま 新しい 複数選択プルダウンリストでも使える！
さて、今回紹介したGoogleフォームのチェックボックス質問の回答をスプレッドシート側で集計する数式は、実は 2024年8月下旬に実装（発表は 2024年7月下旬）された「複数選択可能なプルダウンリスト」で、そのまま使えます！
気になってた Googleスプレッドシートの複数選択プルダウンがきましたよ～。
— mir (@mir_for_note) August 23, 2024
しかしコレ、数式で参照すると中身は カンマ+半角スペースで区切られたテキスト。Googleフォームの チェックボックスと同じね。
スマートチップの データ抽出には対応してない。集計するのに厄介なデータってことか？ pic.twitter.com/zaXdM09nK3
たとえば、こんな形で複数選択されたフルーツの数を集計したい時
このプルダウンで選択された複数チップ、中身は Googleフォームのチェックボックスの回答と同じく , + 半角スペース で区切られた テキストデータとなっています。
だから 先ほどのお題2の回答の式が 対象範囲を変えるだけで、そのまま使えちゃいます！
=ARRAYFORMULA(QUERY(TOCOL(SPLIT(B:B,", ",FALSE),3),
"select Col1,count(Col1) group by Col1 order by count(Col1) desc label count(Col1) '回答数'",1))
コピペでも使えますが、是非中身を理解してアレンジできるようになりましょう！
今回はここまでとなります。次回もGoogleフォーム + スプレッドシート ネタ が続く予定！
いいなと思ったら応援しよう！
