現在は特別支援教育ー業務効率化ー専門性向上ー生成AI活用をつなげる研究活動に集中しています💻 最新のサービスを特別支援教育に応用する研究です✨ 世界には素晴らしいサービスがたくさんあると、日々ワクワクしています😊 本研究はJSPS科研費(課題番号23K02737)の助成を受けて実施しております。

出席データから、個人ごとの出席回数をカウントする

グーグルフォームと連動したスプレッドシートのデータ集計(UNIQUE、COUNTIF関数使用)

フォームで申請した出席簿を集計して、個々の出席数を自動でカウントしたい

きゃりん
きゃりん

きゃりんでーす。ねえねえざいちゃん。
もっくんから、スプレッドシートの集計についてヘルプきたのでお助けマンしてね。内容はこうよ。

もっくんです。
授業の出欠をグーグルフォームでおこなっているんですが、連携されたスプレッドシートのデータから、学生の出席回数を自動で集計してくれる方法知りませんか?
データは、期日や学籍番号、名前、感想などです。
出席した回数は、名前でフィルターかけて絞り込んで数えていますが、学生が多いので、一人ひとり集計していくのが大変です。
よく学生本人や教務から出席状況を緊急に求められる時があり、苦労します。

ざいちゃん
ざいちゃん

はい、ざいちゃんです。
出席状況の確認は、いちいち調べていたら大変ですね。
でも、スプレッドシートでデータを管理されているので関数を2つ使って別シートで集計することで自動化できますよ。
以下の説明にある関数をコピーしたり、サンプルファイルをダウンロードして、使ってみてください!

出席フォームを作成する

Googleフォームとスプレッドシートが連動していることが分かりやすいように、フォームから作っていきます。
「学籍番号」「名前」「メールアドレス」「感想」が入力できるフォームを作成します。
今回、サンプルを作るにあたり、疑似個人情報データ生成サービスというサイトで、名前を作成しました。このサイトでは、疑似メールアドレスや住所も作成できました。サンプル作るときはとても重宝します。
「学籍番号」「メールアドレス」「感想」は記述式で項目を作成しました。「名前」はプルダウンで選べるようにしてみました。フォーム作成でプルダウンを選択し、疑似個人情報サービスで作成した名前を一気にペーストします。これでフォームの完成です。

フォームと連動したスプレッドシートに集計シートを追加する

プレビューで試しに入力して、連携したスプレッドシートを確認します。入力したら、フォームの回答の、スプレッドシートにリンクをタップし、「新しいスプレッドシートを作成」を選びます。すると、試しに入力したデータが入っているのが分かります。
データが1件なので、集計した結果が分かるように増やします。疑似個人データで作った名前を、数十人分入れて、ランダムに抜いていきます。
そして集計シートを作成して準備完了です。

集計シートに関数を入れて、個別に出席数をカウントする

「使用する関数」
出席簿集計で使用する関数
UNIQUE関数「指定した範囲に含まれるデータから重複を除いたものを取り出す関数」
UNIQUE(‘参照シート’!重複しているデータ範囲)
UNIQUE(‘フォームの回答 1’!C2:C)

COUNTIF関数「条件に合う物の数を数える関数」
COUNTIF(‘参照シート’!数えたい名前の範囲,数えたい名前)
COUNTIF(‘フォームの回答 1’!$C$2:$C,A1)
※セル(C2)に$$を入れるとオートフィルしてもセル値が変わらない(絶対値)F4キーで入力可

COUNTIF(‘フォームの回答 1’!C2:C,A1) $を抜いた関数は、オートフィルでセル値が変動するので、範囲指定できない。

関数は、コピーして使ってください。

まずは、UNIQUE関数を使います。
集計シートの任意のセルに関数を入力します。
セルをクリックして、=を入れてフォームの回答1シートに移動し参照する範囲を選びます。C行の列すべて(C2:C)。すると集計シートにフォームの回答1シートの名簿から、単一の名前が絞り込まれ表示されます。下図参照

次に、UNIQUE関数で、抽出された名前の右行に、COUNTIF関数を入れていきます。
=COUNTIF()と入力し、フォームの回答1から参照する名前行(C2:C)を選択して、集計シートにもどり、数えたい名前のセル(A1)を入力します。この時に「$C$2:$C」のように、$を間に入れることで、A1以下にオートフィルで関数を貼り付けた時に、数えたい名前の範囲が変動せずに表示されます。※F4キーを押すことで一挙に入力できます。これで完成です!

運用時に注意すること

もっくん
もっくん

もっくんです。
早速使ってみます。これで、学生本人や教務から出席状況を緊急に求められた時も、すぐに答えることができます。

もっくん
もっくん

役に立ててよかったです。
今後注意することとして、学生さんたちがフォームで名前を入力するときに共通理解しておくことがあります。

もっくん
もっくん

UNIQUE関数では、名前を完全一致で抽出しますので、「ざいちゃん」「ざい ちゃん」のように、若干違う表記にすると別に分類されてしまい、正しくカウントされなくなります。

もっくん
もっくん

アドバイスありがとうございます。

もっくん
もっくん

他の方々からのヘルプもお引き受けしています。参考動画も見てくださいねー
では、ざいちゃんでした!

他にもDX関係の動画を配信しています。「ざいちゃんねる」参考にしていただけたら幸いです。
よければチャンネル登録お願いします。
www.youtube.com/@user-bs8ef8nn1z

コメント

タイトルとURLをコピーしました