はじめに
Microsoft Formsはアンケートや問い合わせなど、さまざまな用途のフォームを手軽に作成でき、情報の収集・分析も自動で行ってくれる便利なツールです。
回答されたデータはExcelでも確認することができ、テーブル形式でデータを保存してくれます。
もしセミナー参加者の希望人数の総数や選択項目別の回答数など、データの合計値を確認したい時は、Excel側で計算するなど別の手段が必要です。
ただしFormsの回答を数値制限にしても、文字列の先頭に '(アポストロフィ) がついてしまうという問題もあり、これが変換作業に手間をかける原因となっています。
せっかくExcelでデータを集計できる利点がFormsにあるので、今回は回答されたデータを効率的に集計できるよう、Forms(&Excel)とPowerAutomate、Teamsを組み合わせて、簡単に合計値を可視化するフローを作成してみたいと思います。
手順
-
Formsの準備
今回は予約フォームをサンプルとして用意しました。参加希望人数と選択肢を選ぶシンプルな申込フォームです。プラン別の参加希望人数の集計を目的とします。
大事なポイントとして、使用するフォームはFormsサイトから作成したものではなく、OneDriveもしくはSharePoint(Teams)で作成したフォームを用意してください。
Formsの応答画面で「Excelで開く」を選択した際にOnline上で開くことができればOKです。
-
Excelの準備&テーブル追加
次にFormsの応答画面にある「Excelで開く」を選択してExcel Onlineを開きます。Formsの回答が蓄積されるシートは「Form1」というシート名&テーブル形式でデータが保存されています。
今回のフォームだとデータが入る列はH列までですが、はじめに説明した通り、集計したい参加希望人数のG列に入るデータは '(アポストロフィ) がつくため、このままでは集計できません。
そのため今回は数値変換の列(画像のI列)をテーブルに追加して、Value関数で直すことにします。=VALUE(@[参加希望人数を入力してください。])
なお、Excel側で列を追加してもフォームの表示に影響はありません。また回答が新しく追加されてテーブルの行が増えても、I列は関数を自動的に適用してくれます。
次は集計用のテーブルを作成します。同じブック内にシート「集計」を追加し、クロス集計する見出し・要素を作成してテーブル化します。
参加人数列には、条件を指定して数値を合計するSUMIF関数(範囲, 検索条件, 合計範囲)を入力し、合計範囲はシート「Form1」で新しく追加した列を参照するようにします。
=SUMIF(Form1!$H:$H,[@プラン名],Form1!$I:$I)
次これでExcel側での準備が完了しました!
-
トリガーの設定
PowerAutomateでフローを作成していきます。Formsで新しい回答があった場合にフローを開始するようにしたいので、「自動化したクラウド フロー」で作成、トリガーは「新しい応答が送信されるとき」を選択します。
トリガー「新しい応答が送信されるとき」の『フォームID』は、OneDriveもしくはSharePoint(Teams)で作成したフォームの場合、プルダウンの選択肢内に表示されません。
そのためFormsの回答収集URLから一度フォームにアクセスし、URLの「?id=」の後ろにある文字列をコピーし、カスタム値をクリックしてIDを直接入力する必要があります。次に設定画面に移り、Generalの『分割』をOnにし、『配列』は右にある矢印を押してプルダウン内にある「@triggerOutputs()?['body/value']」を選びます。
※新しいデザイナーになってから分割が自動的にOffの状態で作成されるようになっているため。クラシックデザイナーでは既定でOnになっています。 -
アクション「表内に存在する行を一覧表示」を追加
次にExcelコネクタ「表内に存在する行を一覧表示」アクションを追加します。
FormsのExcelデータが保存されている場所、ドキュメントライブラリ、ファイル名を選択し、テーブルはExcelのシート名「集計」で作成したテーブル名を選択します。
※テーブル1はFormsで自動的に作成されるテーブル名になります。 -
アクション「HTML テーブルの作成」を追加
組み込みコネクタ「HTMLテーブルの作成」アクションを追加します。
Fromには前の手順で追加したExcelアクションの動的なコンテンツ「body/value」を追加します。
次に詳細パラメーターの「すべてを表示」を選択し、『Columns』はカスタム、『Header』には見出しを入力、『Value』にはExcelアクションの動的なコンテンツをそれぞれ追加します。 -
アクション「チャネル内のメッセージで応答します」を追加
回答ごとにスレッドが新しく乱立するのを避けたいので、特定のスレッド内で投稿が行われるように、Teamsコネクタのアクション「チャネル内のメッセージで応答します」を追加します
『投稿者』はBot(ボット)かユーザーかを選択できますが、プライベートチャネルの場合、Botを選択するとフローがエラーとなるため注意してください。
『Message ID』は回答を投稿するスレッドが、標準チャネルかプライベートチャネルかによって取得方法が異なります。
【標準チャネル】
スレッドにある三点リーダ「・・・」内の「リンクのコピー」を選択し、メモ帳に張り付けるなどしてparentMessageIDの数字13桁を抽出。
【プライべートチャネル】
スレッドにある三点リーダ「・・・」内の「Outlookで共有」を選択し、ポップアップ内にある「Teamsに移動」のリンクをコピーし、thread.tacvとtenantIdの間にある数字13桁を抽出。
『Team』、『Channel』は該当するチームやチャネルをそれぞれ選択します。
『Message』には「HTMLテーブルの作成」より動的なコンテンツ「Output」を追加します。 -
動作の確認
これでフローの設定は完了です!Formsで回答があった場合、項目と参加希望人数の合計数をTeamsで確認できるようになりました。
まとめ
Microsoftのツールを組み合わせて集計作業を効率化し、時間と労力を節約しましょう!