Facebook質問回答してみた。実験データから考えよう！

質問内容）



案①

👉 「実験度にフォームをコピー」これは大変で、Spreadsheetの加工もその度にしなくてはいけない。

そう思って、フォームの質問欄に「実験名を選択してください。」を追加しました。

・修正後Googleフォーム
https://docs.google.com/forms/d/1gg1nAyytm2i5i8UYgw5FzLYX5SWzMRMubh5ZmbFURi0/edit

・Spreadsheet
JOB PULLEY 案）実験データから考えよう！（元版改） （回答）

コメントと類型は手入力になります。

lookerStudioで表示 生徒に見せる用

https://lookerstudio.google.com/reporting/c9a8a556-ad8d-444b-a71c-5e85e8cc3348

このようなイメージでしょうか？


案②
質問内容 詳細



作成イメージ


作成過程

1.集約用シートを作成
00_集約用シート

2.学生表示用シートを作成
00_学生表示用

3.フォームを必要分作成
https://drive.google.com/drive/folders/1VdOYBTkMiefRqWO8bjAA_WFVD_hbU4Gm


利用する関数：
1.Query関数
2.importrange関数
3.ARRAYFORMULA関数（おまけ）


フォームの回答を 00_集約用シート に集める方法

「既存のSpreadsheetを選択」から「選択」をクリック


00_集約用シート を選択


上記内容をフォームの数だけ繰り返します。

00_集約用シート に回答のデータがリンクすると初期は「フォームの回答１」「フォームの回答２」などとなるはずです。自分でわかりやすい名前に変更することをオススメします。
今回は、「実験①」「実験②」などとしました。


タブに「集約」のシートを作成しています。
集約シートに入力している関数はこちらです。
👇
=QUERY({'実験①'!A:G;'実験②'!A2:G;'実験③'!A2:G;'実験④'!A2:G;'実験⑤'!A2:G;'実験⑥'!A2:G},"select Col2,Col3,Col4,Col5,Col6,Col7 where Col6 is not null order by Col6 asc")

これで、複数のタブの情報を１つにまとめました。

詳細は動画を作成しますね。（いつになるかはわかりませんが…すみません）

00_集約用シート の作業はこれで終了です。



続いて学生表示用です。

00_学生表示用 は１つのシートでは完結出来ませんでしたので…２つのしシートになります。

まずは、「ロック)インポートレンジ」と名前をつけています。
これは、 00_集約用シート の「集約」から情報を持ってきているだけのシートになります。
関数はこちらです。

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1anPnW4XP9NMEo_JriovHXtw-9kOjjpjr2_0paIJQXg4/edit#gid=1757971452","集約!A2:F"),"select * where Col5 is not null order by Col5")

本当は１つのタブで作成したかったのですが、セル参照で「コメント類型」を絞ることが出来なかったので、２つにしています。

学生がいじらないように、権限を閲覧専用にするかタブを非表示にしておいてもよいと思います。



さて学生表示用ですが、「類型」で表示されるものを絞ることも可能にしました。
あとは「条件付き書式」ですね。

範囲は「A4:F11」に今回は設定していますが、実際は、「A4:F」の方が良いかと思います。件数が増えても対応可能なので…


「A2」セルは、データ入力規則にてA～Iまで選択する形にしています。


関数はこちら👇

=QUERY('ロック)インポートレンジ'!A:F,"select * where E like '%"&A2&"%'",1)

like '%"&A2&"%' ですが、

Like はあいまい検索
% % で挟んでいるのは前後がA2セルで始まるもの・終わるものを指していますので、何も選択さされなければ、データの全部が表示される仕様にしています。

回答になっていますでしょうか…？

おまけ
ARRAYFORMULA関数ですが、「類型」の「なのです」が必要かは、わかりませんでしたが「コメント類型」にA～Iの入力がなされると自動で「なのです」が追加される関数を組み合わせました。


関数はこちら
👇

=ARRAYFORMULA(If(Isblank(F2:F),"",F2:F&"なのです"))



実際は 00_集約用シート の各シートの「H2」に入れています。

「コメント」と「コメント類型」については手入力になると思いますが、少しでも作業が楽になると思い作成してみました。

ちなみに、「コメント類型」が入力されていないと表示されない仕様にしております。

参考になればと思います。

よろしければチャンネル登録・いいね👍 よろしくお願いいたします。

job pulley - YouTube