はじめに
業務で、複数人の人にExcelへ記入してもらうテンプレートを作成することになりました。
ただこのテンプレート、「複数のデータマスタが必要」で、「ドロップダウンの項目が随時増えるかもしれない」し「項目が変わったら全テンプレートに反映が必要」なのです。
そこで、PowerQueryを利用してマスタファイルから、利用者が使用時に更新してデータを再取得することで、いちいちテンプレート差替え・再配布しなくてよくできるようにしたいと思います。
ちなみに、テンプレートファイルにマスタデータを取り込む際は、テンプレート側には何個もテンプレートの項目シート用意したくないので、マスタデータを1シートに纏めちゃおうと考えています。
（見た目は微妙だけど、項目のシートは非表示にしちゃうので。。）
イメージ図はこんな感じ。
使用環境
使用環境は、Windows10 で Office365 の Excel です。
PowerQueryとは
（これはこちらの参考サイトの受け売り）
PowerQueryとは、2016年に公開されたMicrosoft製のデータ分析用Excelアドインであり、Excel2010以降に対応しています。2010、2013はアドインのインストールをすることで利用可能です。
ファイル・DB・Webサービス等からデータを読み込み、変換・加工してExcelのシートに出力できます。VBAのマクロの記録と同じように、画面上で操作するとクエリ（読み込み手順の定義）が生成されます。複雑な加工をしない限りプログラミングは必要ありません。
Excel内に全ての情報を格納するため、一つのファイルで完結し他のツールは必要ありません。
中間処理は全てメモリ上で行われ、処理が非常に高速であることが特徴です。
ファイルに出力されるのは最終の出力結果のみであるため、ファイルサイズも少なく済みます。
まずはマスタファイル作成
マスタファイルを準備して、所定のフォルダに保存します。
今回は商品マスタと、
社員マスタの2つを用意しました。
テンプレートを作成
テンプレートは、とりあえず入力させたいカラムを準備。
PowerQueryエディタを起動→データの取得
いよいよPowerQueryを使っていきます。
Excelの「データ」タブ→「データの取得」→「PowerQueryエディターの起動」を押下します。
すると空っぽのPowerQueryエディターが開きます。
ここで、「新しいソース」→「ファイル」→「Excelブック」を選択します。
ファイル選択のポップアップが開くので、先ほど作成したマスタファイルを選択して「開く」を押下。
するとマスタファイルの各シートが表示されました。
ここでは、2つのマスタデータを取り込みたいので、「複数のアイテムの選択」にチェックを入れて、
「社員マスタ」と「商品マスタ」を選択。「OK」を押下します。
データが取り込めました！まずは第一関門突破。
クエリの結合
マスタを結合する前に、どのレコードはどのマスタなのか、という分類が必要そうなので、それぞれのマスタクエリに1列追加します。
（本当は自クエリの名称を列で追加出来たらよかったのですが、上手く見つからず。。諦めて力技です。笑）
「列の追加」タブ→「例からの列」を押下します。
画面右に、新規追加したい列が出てくるので、サンプルでいくつか入力してみます。
ここではマスタ名として「社員」と2行分入れてみたところ、予測で全行に「社員」が入りました。
ただのリテラル列になっちゃったけど、仕方ない。。
はい、マスタの列が追加できました。
商品マスタ側でも同様に列の追加をしておきます。
では、クエリを結合していきましょう。
画面左のクエリリストのあたりで「右クリック」→「結合」→「クエリを新規クエリとして追加」を押下します。
行結合する対象のクエリを選択するポップアップが出るので、「2つのテーブル」を選んで、「社員マスタ」と「商品マスタ」を選択します。
「追加1」というクエリが完成！
いらないヘッダーが混ざっているのはフィルターではじきます。
列名はダブルクリックで任意の名前に変更します。
クエリ名もダブルクリックで任意の名前に変更します。
これでOK！「閉じて読み込む」ボタンを押下します。
マスタ情報を結合したシートが完成
これでマスタデータの取り込み設定が完成です。
テーブルになって表示されてますね。
いらないシートは消しちゃいます。
テンプレートシート側にドロップダウンリストを設定
最後の仕上げとして、ドロップダウンリストを作成します。
テンプレートでは、「部署名」を選択したらその部署に応じた「社員名」が表示されてほしいのですが、今のままの項目情報だと単純な範囲選択では重複データが表示されてしまいます。
そのため、テンプレートシートの右端に重複を削除したレコードリストを作成します。
参考にしたのはこちらのサイトです。
関数はUNIQUE関数とFILTER関数を利用するようです。
例として、部署名のリスト表示から作成してみます。
書き方はこんな感じです。
=UNIQUE(FILTER(項目情報_2[[#すべて],[小分類]],項目情報_2[[#すべて],[大分類]]="社員"))
PowerQueryで取得したクエリはテーブルとして定義されるので、列を指定する際もPowerQUeryで自動定義された範囲で指定できるんですね。
（ドラッグして範囲指定すると自動でこういった表示になります）
Enterを押下すると、部署のリスト（スピルというらしい）ができました。
これをA列「部署名」のドロップダウンリストに設定します。
対象範囲を選択して、「データ」タブ→「データツール」の真ん中一番下のアイコン→「データの入力規則」を押下します。
「データの入力規則」のポップアップが表示されます。
「設定」タブの「入力値の種類」で「リスト」を選択し、「元の値」に部署リストの最上位セルを選択して末尾に「#」を付けます。
（#によってスピルを指定可能な模様）
これで部署名のドロップダウンリストができました。
次は選択した部署の社員名をドロップダウンリストにします。
これはこちらのサイトを参考にしました。
OFFSET関数とMUCH関数を利用するようですね。
直接社員名列を選択して、同様にデータの入力規則を設定します。
分かりづらいですが、指定した関数はこちらです。
=OFFSET(項目情報!$B$1, MATCH($A2,項目情報!$B:$B,0)-1, 1, COUNTIF(項目情報!$B:$B,$A2),1)
OKを押下することで、無事隣のセルの入力値によってドロップダウンリストが変わるようになりました。
商品側でも同様に行います。
マスタデータに変更が入ったらちゃんと更新されるのか
これで基本構造は完成です！
最後に、マスタに社員や商品が追加された場合にちゃんと反映してくれるかを確認します。
社員に「松本」さんを、
商品に「金平糖」を追加してみます。
そういえば今回データのPowerQueryでソートを設定するのを忘れてしまったので、データは和菓子群の一番下に挿入します。
次に作るときはちゃんとソートも指定しますね。
（たぶんデータ位置が分類間を飛んでしまうと、ドロップダウンリストで上手く拾えないんじゃないかな…）
マスタファイルを保存したら、再度テンプレートファイルを開きます。
「データ」タブ→「すべて更新」を押下すると…
おお！ちゃんとデータが増えてますね！
「テンプレート」シートでもドロップダウンリストが増えています！
おわりに
はじめてPowerQueryを使ってみましたが、かなり早いですね！
データの更新速度も、ネットワーク速度によるとは思いますが全然ストレスなく更新されました。
PowerQueryエディターはちょっと癖がありますが、おおむね直観的操作でできたように思います。
マスターデータのの結合にはデータ項目数を合わせる必要があったりで、ちょっとコアな記事になってしまいました。
どこかに需要があるといいなあ…。