Teams会議 参加者数推移の集計 By Excel & GoogleSpreadSheet

はじめに

　Teams会議の参加者リストのダウンロードしたcsvファイルをもとに、会議中の参加者数の推移を集計するExcel帳票を作成しました。
　私が検索した範囲ではちょっと見つけきれなかったので。良いのがあるよという場合、コメントに残していただけると幸いです。

　Teamsで主催となったイベントがあり「参加者数集計してね」と言われデータをダウンロードするが、ちょっと厄介な形（と思った）のデータ。なによりも参加と退出を繰り返す方、最後まで退出されない方などもあり、ううう。手作業は嫌だ。今回引き継いだ仕事なので、これからも時々こんな作業が発生するので、なんとかしないと。前任者に聞くと手作業でした。
　会議に一瞬でも参加された方の人数は簡単に出せますが、時系列での集計は手作業では面倒です。

　会議ではあまり関係ありませんが、イベントなどでは、どのタイミングで一番参加者が多く注目の集まるコンテンツだったのか、どのシーンで客が離れていったかがわかるかなぁと、そんな使い方もできると思い、作ってみました。

準備

　こちらのサイトを参考に、出席者リストを取得してください。なければ、適当に作りましょう。
　Teams オンライン会議の出席者リストを取得

1.制約事項（というか言い訳です^^;）

1.　会社ではExcelなんですが、自宅ではExcelでなくGoogleSpreadSheetを使っています。今回の開発はGoogleSpreadSheetで行いました。Excelへは、ダウンロードするときにExcel形式を選択してご活用ください。
2.　参加、退出を繰り返した人は名前の最後に数字をつけてユニークな名にしてください。重複があると正しく集計できません。
　こちらのサイトを参考にして重複しないようにしてください。エクセルで重複データに色を付ける方法（セルと行に色を付ける）
3.　出退リストは100件まで。数式の検索範囲を変更していただければ上限を変更できます。
4.　今回作成したのは10分単位です。時間間隔を変更いただければ、1分とかできます。

2.出席者リストの貼り付け

　準備の段階で取得したデータを開き、A2を起点に貼り付けてください。以降も説明で使う関数の関係上、図にある行と列の位置を固定して説明させていただきます。

3.元データの加工

　そのまま張り付けるだけでできることも多いですが、次のポイントでデータをクリーニングしてあげてください。
　・A列　氏名の重複がないこと
　　　参加と退出のセットであれば問題ありません。複数回、参加・退出を行っている方。なので、氏名が3回以上ある方が対象です。図の赤枠。
　・B列　ユーザーの操作で「参加」「退出」以外のキーワードを修正
　　　参加者リストをダウンロードできる主催者が会議に参加される前に、すでに参加されている方がおられると、「参加」でなく別の表現になってます（何だったか失念）。ここを「参加」と変更してください。

3.参加リストと退出リストへ振り分け

　ここから関数を使って自動的に加工していきます。
　参加者リストを張り付けたとき、参加と退出に振り分けます。これにより、次の4.において、それぞれの参加者が、いつ参加していつ退出したかの時間の検索が容易になります。

出来上がった時の図がこれです。

　では仕掛けです。参加リストのF3に仕組んだ関数を紹介します。

F3に埋め込んだ関数

=IFERROR(INDEX($A$3:$C$101,MATCH(LARGE(($B$3:$B$101="参加")*1/ROW($A$3:$A$101),ROWS($F$3:$F3)),1/ROW($A$3:$A$101),0),COLUMNS($A$2:A$2)),"")

　この関数の詳細説明は、参考にしたサイト①が、超詳しく説明されてますので割愛します。

　ここでの作業は
　1.　F2に「参加リスト」と入力。
　2.　F3に先ほどの関数を張り付ける。
　　 例の通りだと、田中と表示されればO.K.　
　　　　★Excelだと人作業が必要です。参考サイト①の七番目の作業です。
　　　　　ここの関数を選択して、
　　　　　数式を配列数式にするには、数式を入力し終わった後、「Shift」キーと「Ctrl」キーを押しながら「Enter」キーを押す必要があります。

　3.　F3の関数をG列、H列に反映
　　　　関数コピーの要領です。F3選択したときに、右下に点があります。これをマウスでー横移動。
　4.　同様に縦方向にも関数を反映させましょう。
　　　　ここでは100行ぐらい行ってみましょう。

　5.　同様に退出リストも作ります。
　　　　J3に埋め込んだ関数を掲載しておきます。
　　　　違いは、検出文字、氏名の検索列FからJへです。

J3に埋め込んだ関数

=IFERROR(INDEX($A$3:$C$101,MATCH(LARGE(($B$3:$B$101="退出")*1/ROW($A$3:$A$101),ROWS($J$3:$J3)),1/ROW($A$3:$A$101),0),COLUMNS($A$2:A$2)),"")

4.開始時刻と終了時刻の入力

　会議の時間帯を設定します。いつからいつまでを計測したいかです。

　ここでの作業は図の赤枠部分を作成していきます。
　1.　N2　開始時刻。N3　終了時刻　と入力
　2.　O2,O3　図の形式で　2021/9/17 14:00:00　と秒まで入力
　　　　表示形式が違っている場合は、セルの表示形式を変更してください。
　
　おまけ作業（ここはやってもやらなくてもO.K.）
　3.　P2は、=O2　と入れてます。同様にP3は、=O3　です。
　　　　先ほど入力した日時を数字に変換させるとどうなるかを確認
　　　　2021/9/17 14:00:00　→　44,456.5833　になります。
　4.　P4は、=P3-P2 つまり、開始から終了まで二時間を数字で表すと　0.083333　となることを確認しているだけです。

5.時刻の前処理　10分っていくら？

　二時間の会議において、ここでは10分単位で参加者数を割り出してみようと思います。そのために、日時のままで演算できますが、理解をしやすく数値で考えてみます。

　ここでの作業は図の赤枠部分を作成していきます。
　1.　O5　に　2021/1/1 0:00:00　。O6　に　2021/1/1 1:00:00　と入力
　2.　N7　に　1時間　N8　に　10分 と入力
　3.　O7　に　=O6-O5　と一時間の差分を数字にしてみます。
　4.　O8　に　=O7/6　とすると、10分がどれだけの数値になるか確認できます。

　これで、「10分毎に」という基準値が算出できました。
　10分とは、0.006944444　だそうです。

6.出退リストの作成

　そもそも欲しかった出退リストを作ります。

　ここでの作業は
　1.　Q1　出退リスト
　2.　Q2　氏名、R2　参加時刻、S2　退出時刻　とそれぞれ入力
　3.　Q列とR列は簡単です。
　　　　Q3　には、=F3　　参加リストから持ってきてます。
　　　　R3　には、=H3　　参加リストの参加時刻になります。
　4.　Q列とR列　Q3とR3の式をずっと下までコピーしましょう。
　5.　退出時刻　S列は、ちょっと仕掛けが必要です。下図参照
　　　　VLOOKUPで氏名をもとに、退出リストから退出時刻を求めてます。

★図の式内の数値が間違ってます。　図の下にある「S3に埋め込んだ関数」が正しいです。

S3に埋め込んだ関数

=IFERROR(VLOOKUP(Q3,$J$3:$L$101,3,FALSE),O3)

　ここでは、参加と退出がセットの場合は問題ないのですが、IFERRORを使ったのは、最後まで退出せず退出記録がない方のデータでは、エラーとなるためです。なので、退出リストにデータがない場合は、会議の終了時刻(O3)を設定するようにしています。

7.時系列参加者数の算出

　とうとう、最後の部分。
　下図の一行目が各時刻での参加者数です。
　三行目からは、それぞれの参加者がどの時間帯に参加していたかを関数で導いてます。1がある時間は参加していたということを示しています。これをもとに折れ線グラフでも出せばテレビで言う視聴率推移が出せると思います。

　ここでの作業は
　1.　基準となる時刻を求めていきます。二行目です。
　　　　U2　は　=P2
　　　　V2　は　=if(U2="","",if(U2+$O$8<=$P$3,U2+$O$8,""))
　　　V2の関数を横に反映させていきます。必要な分だけ。

V2

=if(U2="","",if(U2+$O$8<=$P$3,U2+$O$8,""))

　　ここでも、ifを使っているのは、終了時刻以降を""として空白にするためです。直前の参考にするセルが空白なら、算出せず空白にします。
　　O8は先ほど求めた、10分です。

　2.　参加者がその時刻に参加していたかの判断部分を作成します。
　　　　U3部分の関数は　

U3

=if(U$2="","",if(and($R3<=U$2,U$2<=$S3),1,""))

　　2行目の時刻が入っていなければ、空白とします。時刻が入っている場合、その時刻が、参加時刻と退出時刻の間に入っているかをandで確認するようにしています。
　　この式を全体にコピー反映させてます。
　3.　最後に、2.で求めたものを合計すれば完成です。同様に横に反映させましょう。
　　　
★ここも訂正します。正確には100より少し多くするほうがいいかな。

U1

=sum(U3:U100)

8.その他

　一度作った後、再利用する際は 1.～2.の作業は別のシートで行ったほうが便利と思います。フィルタで名前をソートして重複を探したり加工するので。

あとがき

　つたない説明を最後までお読みいただき大変恐縮です。
　なんとなくアルゴリズムを考えるのに一時間。作成から二時間かかってしまいましたが、なんとかできました。週明けの仕事が楽しみ！
　こんな毎日なら素敵なんですが。なかなか。
　ちなみに、この投稿にまとめる時間のほうが長かったなぁ。。。
　最近、私は脱Excelと会社では言いつつも、試作検討するにはE、やっばりExcelが便利。メンバーに説明するにもExcelだと聞いてくれます。プログラムだとソフトはわからんと瞬殺されます（笑）。プログラム初心者でも関数ならなんとか理解できるし、改変もハードルが低いExcel。なかなか手放せません。
　Pythonなんかでサクッと作るべきなんでしょうが自分はまだまだ初学者。時機を見てトライします。
今回紹介した方法で、もっといい方法があるよとか、ここ間違ってるよ、説明が分かりにくいとかあると思います。学びのためにコメントいただけると嬉しいです。

参考にしたサイト

　本文中に取り上げなかったサイトを列挙いたします。
　①　条件に合うデータをエクセル関数で全て順に抽出できる数式の作り方
　②　VLOOKUP関数の使い方