今日はExcelのパワーピボット（クエリ）の弱点を補完する使い方について書きます。
パワークエリを使い始めてまもなくすると、
データソースの変更（フォルダの保存場所、保存名）に変更があった場合は、
いちいちデータソースの変更をしなければならないという特徴に気がつくはずです。
おそらく、パワークエリはこれらに変更がない環境での活用を想定していて、
データ活用の原則からいってもこれは正しいのですが、
私の環境のようにしょっちゅうデータソースが変わってしまって、
いちいちこの変更を行わなくてはならなくて困っている方もいると思います。
今回はそんな方のために、
セル上の値をパラメータにして、クエリの数式に埋め込む方法を書きます。
こうしておけば、データソースのアドレスや名前に変更が生じた時、
セル上のアドレスを修正するだけでデータソースの変更が完了するようになります。
本記事では、その設定方法を紹介します。
参考にしたのは海外のこちらの記事です。
（パラメータ以外にも、パワーピボットの使い方について詳しい内容が載っているので、興味のある方はフォローをおすすめします！）
パワーピボット（クエリ）とは、
Excelに搭載されている超絶優秀なデータ分析ツールであり、
簡単にいえば、定期的に同じデータを使って同じような作業をしているのであれば、それをほぼ自動化できるようなツールです。
詳細については、以前このブログで関連書籍を取り上げさせてもらったので、
そちらを見てください。
そもそも、なぜデータソースの変更が必要となるのか
これはパワークエリのステップをクリックして、数式バーを見てもらえば分かるのですが、数式バーに直接データソースの情報が記述されているからです。
クエリが動作するとき、いちいちデータソースの記述を元にしてアクセス
しているので、データソースのアドレスや名前が変わると、そのステップで
クエリにエラーが出ます。
今回は、この部分を「データの保存場所」のようなパラメータ名に変更します。
「データの保存場所」にはセル上の数値（例：C:●●\●●.csv）が入っていて、
セル上の数値を変更すれば「データの保存場所」に自動的に反映されるイメージです。
VBAの変数みたいなものですね。
セル上の値をパラメータにする方法
テーブルを作成する
まずはセル上にアドレスを記述します。
今回は「データの保存場所」というパラメータを作成しますので、
タイトルを「データの保存場所」、データの内容をデータのアドレスとします。
その後、データが記述されている部分を選択した状態で、「テーブルまたは範囲からデータを取り込む」を選択し、セル上にテーブルを作成します。
テーブルにする時は、「先頭行をテーブルの見出しとして使用する」に
チェックを入れます。
OKを押すとパワークエリエディターが開きますので、そのまま次の作業に入ります。
テーブルをパラメータ用のクエリに変換する
エディターが開いたら、アドレスが記述されている部分で右クリックし、
ドリルダウンを選択します。
ドリルダウンを選択すると、テーブル名のステップが追加されます。
プロパティの名前がテーブル名と一致していることを確認してから、
「ファイル」タブを選択し、データを「接続の作成のみ」で取り込みます。
「接続の作成のみ」を選択してOKをクリック
ここまでできれば、パラメータ用のクエリ「データの保存場所」が出来上がります。
クエリのステップにパラメータを記述する
ここまでできたら、本命のクエリにパラメータを書き込みます。
本命のクエリを開き、最初のステップである「ソース」を選択すると
データソースのアドレスが記述された部分があります。
（数式バーが表示されていない場合は、「表示」タブから数式バーに
チェックを入れてください）
データソースのアドレスを先程作成したパラメータである「データの保存場所」に
変更します。（「””」はいらないです）
これでパラメータをクエリに適用することができました。
プライバシーレベルの設定をはずす
パラメータの設定が完了した後、データを更新すると
「Formula.Firewall」から始まるエラーが出るかもしれません。
これは、意図しない情報漏えいを防ぐためにマイクロソフトが作った仕組みです。
詳細はマイクロソフト公式ヘルプを読んでほしいのですが、ざっくりいえば、
「複数のデータソースを連携していて、そのデータがWEB上に公開されているデータと、会社の経理データといったような機密性の異なるデータだった場合、データソース間で情報交換が行われると、機密性の高いデータが漏洩する可能性がある。
そこで、プライバシーレベルの異なるデータソース間ではデータ連携しないように制限がかかる。」
ということだそうです。いつものことながらよくわかりません(´・ω・｀)
詳細が気になる方は、下記リンクから読んでみてください。
いずれにしても、セル上の数値をパラメータとして使うには、このエラーを回避しなければなりません。
回避するために、この情報漏えい防止のシステムを無視するように設定します。
「異なるプライバシーレベルの間で制限がかかるなら、同じプライバシーレベルに設定すれば良いのでは？」と
思った方もいるはずです。
実は私も同じ事を考えました。
そこで、
ブック内で利用している、すべてのデータソースのプライバシーレベルを同じレベルにしてみたのですが、
このエラーが解消されることはありませんでした。
パラメータの場合は違うルールでも適用されるのでしょうか……。
いずれにしても、私の環境ではプライバシーレベルを無視するという方法しか
ありませんでした。
この点について知っている方がいれば教えていただきたいですm(_ _)m
まずはパワークエリエディターを開き、「ファイル」タブからオプションと設定を選択
→クエリのオプションを選択します。
次に、プライバシーの設定から「常にプライバシーレベルを無視します」を選択。
これでパワークエリエディターに戻り、プレビューの更新を行えば、
データの読み込みが無事に完了します。
おわりに
今回は、パワークエリのデータソースをセル上に値で変更する方法について
書きました。
1.パラメータ用のテーブルを作成して、読み込み専用で取り込む
2.クエリのステップを、パラメータに書き換える
3.プライバシーレベルの設定を変更する
実は、プライバシーレベルの変更については参考元のページや
他のWEBサイトにもあまり記述がなくて、自分で調べるのに苦労しました。
同じプライバシーレベルに設定してもエラーが出るのがまだモヤモヤします……。
ちなみにデータソースの変更ですが、セル上のアドレスをいちいち書き換えるのも
面倒なので、マクロを使ってセルをクリックした時にフォルダ選択ダイアログを
出してあげると捗ります。
下記コードを対象のシートに記述すればOKです。
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim openfilename
If Selection <> Range("データの保存場所").ListObject.DataBodyRange Then End
openfilename = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
Range("データの保存場所").ListObject.DataBodyRange(1).Value = openfilename
End Sub
フォルダを取得する場合はこちら↓
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection <> Range("データの保存場所").ListObject.DataBodyRange Then End
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
Range("データの保存場所").ListObject.DataBodyRange(1).Value = _
.SelectedItems(1)
End If
End With
End Sub
セルをクリックしたときのマクロ動作についてはForguncy（フォーガンシー）さんのこのページが参考になります。
セルをクリックした時に始まるVBAフォルダ選択ダイアログについてはオフィス田中さんのこのページが参考になります。
フォルダを選択するダイアログパラメータ作成は他にも応用例があって、
日付を入れてその日だけのデータを読み込んだり、
勘定科目を入力して入出金だけを表示する
といったことも出来ます。
応用が効くテクニックなので、ぜひご自分の環境で試してみてください！
ここまで読んでくれてありがとうございました！