Microsoft365の機能の一つであるFormsのアンケートフォームを使って情報を収集し、その結果をデスクトップアプリのExcelへ読み込むことで、データをVBA等で使いやすい状態にする方法を、先日Zoomで説明させて頂きました。
資料を準備せずに開催したので、本日より手順を文章化していきます。
Formsを使う意味
クラウドサービスが無い頃は、ExcelやWordでアンケートフォームを作成し、メールで社員に拡散してメールで受け取ったファイルをVBAで集計するという作業が数多く見られました。（VBAで集計しているだけマシかもしれませんが）
昨今はクラウドサービス上でアンケートフォームを作成して自動的に集計するところまで簡単に出来るようになりました。
Microsoft365を契約している企業であれば、新たに契約しなくてもFormsという機能を使ってアンケートフォームの作成と集計が可能です。
従来のExcel VBAに頼った方法では、アンケートの項目やレイアウトに応じてVBAの修正が必要でしたが、Formsを活用すればそのような手間がかかりません。
アンケートの作成も、ポチポチとクリックするだけで簡単にできるようになっています。
アンケート収集をFormsへ置き換えるために必要なこと
Formsを使って回答を収集しデータベース形式のExcelに集めるのは簡単にできます。
しかしながら、回答結果を従来の帳票に変換したり、社内のファイルサーバーに何かしたい場合は、データをVBAで扱える状態にしなければなりません。
VBAだけでMicrosoft365上のデータを安定して読み込むのは非常に難しく、手動コピペに頼るかMicrosoft365の他の機能やPowerQueryを組み合わせる必要があります。
私が知っている方法を、以下の3つのステップで説明していきます。
Formsの回答をデスクトップアプリ版Excelへ取り込む方法
後日、各項目に対して、詳しい手順を説明した記事のリンクを増やしていきます。
1. Formsを作成 → Excelで保存 → 手動でコピペ
この方法は、Formsの基本的な操作とExcelの手動操作で非常に簡単に運用できる方法です。多く手作業が含まれますが、集計が数回限りのフォームではオススメです。
また、個人の（無償版の）Microsoftアカウントでも、使うことができる方法です。
弱点は、集計の度にフォームの回答ページを開いて新しいエクセルブックがダウンロードする必要がある上に、ダウンロードフォルダにファイルが増殖していき、手動でファイルを置き換えたり内容を集計ブックにコピペしたりする必要があります。
- Microsoft365 Formsでアンケートフォームを作成する方法
2. Excelを作成 → Formsを作成 → Excelと同期 → デスクトップExcelのPowerQueryで取り込み
この方法は、企業版のMicrosoft365（Basic以上の全てのプラン）で使用可能な方法です。
個人の保存場所（OneDrive for Business上）のExcelファイルからフォームを挿入するだけで、超簡単にExcel内に作成されたテーブルへ回答が追記されていくツールを作ることができます。
この方法の弱点は、組織の保存場所（SharePoint上）で運用できないことと、Excelブックが次項のListsより安定性に欠ける可能性があることです。
- Microsoft365 Excelと同期するフォームを作成する方法
- OneDrive上のExcelファイルの内容をPowerQueryで取り込む方法（後日執筆）
3. Formsを作成 → PowerAutomateでSharePoint Listsへ蓄積 → デスクトップExcelのPowerQueryで取り込み
この方法は、回答データを組織の保存場所（SharePoint上）の中に作成したLists（リスト）に保存します。Listsはデータベースのような概念の形式であり、列毎にデータ型を決めることができ、情報の管理単位がレコード毎（回答1件毎）に追加・更新・削除の履歴を管理することができます。
文字/数値情報だけではなく、プルダウン選択や、画像等のメタデータ、別リストのアイテムの参照、ユーザー等のオブジェクト情報も保存できます。
デメリットは、PowerAutomate、SharePoint、LIstsと言った、Microsoft365の幅広い知識が必要となることです。 （使い方を覚えてしまえば10分程度で組めるし、Excel管理より柔軟な要件に対応することができます）
- SharePoint サイトにListsを作成する方法（後日執筆）
- Formsに投稿された内容をPowerAutomateでListsへ転記する方法（後日執筆）
- SharePoint Listsの内容をPowerQueryで取り込む方法（後日執筆）
補足記事
以下の内容についても記事を書くかも知れない
- VBAで安定して別ブックのPowerQueryの更新をする方法
- VBAでSharePoint Listsの内容を直接読み書きする方法(ADODBを使用）
- AccessでSharePoint Listsの内容を一括修正する方法
最後に
難易度は下に行くほど難しくなりますが、フォームの運用期間、集計頻度、展開規模が大きくなるほど後者を使うことを推奨します。
また、FormsはEUCに近いレベルで運用することを想定されています。本当に大規模・重要な場面では、業務フロー全体を考慮して開発された専用システムへ任せることも忘れないでください。
ここで説明した内容以外にもフォームを読み込む方法があるかもしれません。 もし詳しい人がいたらTwitter等で教えて頂けると幸いです。
以上
登壇を聞いて下さった方で私を支援したいという方は、以下の通りウェルカムですので、よろしければどうぞ。
それではまた明日♪ちゅんちゅん(・8・)