参加登録データを分析しよう！①

先日までとあるイベントの運営を手伝っていました。

参加者は、グーグルフォームから申し込みをすることで、こちらが把握できる仕組みです。グーグルフォームに集まったデータはExcel形式でダウンロードできたり、アンケートならグラフも自動生成してくれるので便利なのですが、今回はExcelの関数機能を試しながらそのデータを分析してみました。

グーグルフォームのデータをダウンロード

グーグルフォームに集まった参加者データをExcel形式でダウンロードしてみました。入力してもらったのはB列から右の項目。A列のタイムスタンプは自動で入力されます。（グーグルフォームのことは余裕があったら書きます。）

さて、端から見てみましょう。まずはA列のタイムスタンプ。

日付と時刻で構成されています。ここには5件しかデータがありませんが、大量のデータがあったら、「申し込みの多い時間は何時か」とか気になりませんか？　ツイートした時間と申し込み人数の相関を見たい場合ありませんか？

ということで、時間帯別の登録者数を関数を使って調べてみたいと思います。1つ目のデータから見てみましょう。

4月19日12時3分で次の人は15時3分なので、12:00～12:59までの1時間で1人という結果が出せればいいわけです。

COUNTIFS（カウントイフズ）関数登場

関数を使う際は、これをコンピューターに条件式の形で入力してあげなければなりません。似た関数でCOUNTIF関数があるのですが、それとこれとの違いは複数形のSがあるかどうか。つまり、条件（IF）が複数指定できるかどうかにあります。　全体像はこちら。

＝coutifs(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2,...)

と続きます。範囲と条件のセットを足していくことで２つ、３つといろいろな条件に合致するデータの個数を求めることができます。個数を求める道具です。

例えば、点数のデータの中から90点以上の学生を探し出した上で、男子の人数、女子の人数を求める、なんて使い方ができます。

今回欲しいのは4月19日の12:00～12:59までのデータ。これをどういう式で表すか考えながら見ていくことにしましょう。

1つ目の条件

まずは4月19日のデータを探すことから始めましょう。

検索条件範囲に、どこから4月19日のデータを探すのかを指示します。日付はA列なのでA列を全部指定しちゃいましょう。→A:Aで表します。

次に検索条件。探す場所が決まったので、いよいよ条件を入れます。4月19日を探したいのでそのまま、2020/4/19でOK

次は時刻です。ここは少し頭を使うかもしれませんが、最後にはなるほど～と思えるはずです。

12時から13時までを探し出すために、まず、12時以降のデータを見つけてもらうことにしましょう。　先ほどの日付＋半角スペース＋時刻

>=2020/4/19 12:00。

不等号＞と＝を合わせることで12時ちょうども含めることができます。なるほど～と思いませんか？？　じゃあ「≧」の1文字でいいじゃないかと思う方。賢い！　でも、Excelの関数内ではその1文字を認識してもらえないのです。ということで、2文字に分けて入力します。

先ほどの条件では、4月19日の12時以降のデータの個数を求めることができます。今あるデータすべてが該当するので答えは５と返ってくるはず。

2つ目の条件

では、次に12:59までというフィルターをかけてみましょう。今回は分までしかないですが、秒まで書いてあると考えるのが大変ですよね。なので、不等号「＜」を生かして「13時より前」という表現で指定してみましょう。

<2020/4/19 13:00。

「＜」の左辺にはA列のセル1つ1つがあるイメージです。13時のほうが大きい。左辺に来た日付が13時より前ならカウント対象というわけです。

ではこれまでに作ったパーツを組み合わせてみましょう。ちなみに、条件は文字列として認識させるために"  "でくくるのを忘れずに！

＝coutifs(A:A,">=2020/4/19 12:00",A:A,"<2020/4/19 13:00")

こうなりました。検索するデータがある範囲が変わらないなら、同じ範囲をもう1回使うだけなので、A:Aが2回登場してます。

これ、パッと見ただけじゃ ただの数式で吐き気がしますよね。私もそうです。（よくもこんなのやろうとするね笑）

ということで、解読。

カウントイフ、A列が2020年4月19日12時以降のとき、A列が2020年4月19日13時前のもの。

訳し方はいろいろあると思うのですが、言いたいことはこういうことです。

この式を、データ数を表示したいセルに入力しましょう。

いざ、入力！

なんじゃこの表は。3分クッキング以上のクオリティやんけ。という突っ込みはおいといて・・・。

緑のカーソルのあるセル（P5セル）、１と出ましたね。

式の時間帯をずらしながら横移動すれば同日の時間帯別データができます。

1日分できたら、それをコピーして次の4月20日の行に貼り付け。

置換ツールで、数式を対象として、日付を4/19を4/20にすれば一発で翌日の数値が表示されることでしょう。（1行目の手入力さえ乗り切ればあとは楽です。）

お付き合いいただきありがとうございました。

次は学籍番号のアルファベットをもとに学部の人数を数えたいと思います。

つづく