Excelハック 未分類

[Excel]パワークエリのデータソース変更をセル上の値の変更で済ませる方法

Excelハック
この記事は約8分で読めます。

今日はExcelのパワーピボット(クエリ)の弱点を補完する使い方について書きます。

パワークエリを使い始めてまもなくすると、
データソースの変更(フォルダの保存場所、保存名)に変更があった場合は、
いちいちデータソースの変更をしなければならないという特徴に気がつくはずです。

おそらく、パワークエリはこれらに変更がない環境での活用を想定していて、
データ活用の原則からいってもこれは正しいのですが、
私の環境のようにしょっちゅうデータソースが変わってしまって、
いちいちこの変更を行わなくてはならなくて困っている方もいると思います。

今回はそんな方のために、
セル上の値をパラメータにして、クエリの数式に埋め込む方法を書きます。

こうしておけば、データソースのアドレスや名前に変更が生じた時、
セル上のアドレスを修正するだけでデータソースの変更が完了するようになります。

本記事では、その設定方法を紹介します。

参考にしたのは海外のこちらの記事です。
(パラメータ以外にも、パワーピボットの使い方について詳しい内容が載っているので、興味のある方はフォローをおすすめします!)

Power Query – Using Parameters – Excel Off The Grid
そもそもパワーピボット(クエリ)って何よ?って方へ

パワーピボット(クエリ)とは、
Excelに搭載されている超絶優秀なデータ分析ツールであり、
簡単にいえば、定期的に同じデータを使って同じような作業をしているのであれば、それをほぼ自動化できるようなツールです。

詳細については、以前このブログで関連書籍を取り上げさせてもらったので、
そちらを見てください。

そもそも、なぜデータソースの変更が必要となるのか

これはパワークエリのステップをクリックして、数式バーを見てもらえば分かるのですが、数式バーに直接データソースの情報が記述されているからです。

クエリが動作するとき、いちいちデータソースの記述を元にしてアクセス
しているので、データソースのアドレスや名前が変わると、そのステップで
クエリにエラーが出ます。

今回は、この部分を「データの保存場所」のようなパラメータ名に変更します。
「データの保存場所」にはセル上の数値(例:C:●●\●●.csv)が入っていて、
セル上の数値を変更すれば「データの保存場所」に自動的に反映されるイメージです。

VBAの変数みたいなものですね。

セル上の値をパラメータにする方法

テーブルを作成する

まずはセル上にアドレスを記述します。
今回は「データの保存場所」というパラメータを作成しますので、
タイトルを「データの保存場所」、データの内容をデータのアドレスとします。

その後、データが記述されている部分を選択した状態で、「テーブルまたは範囲からデータを取り込む」を選択し、セル上にテーブルを作成します。

記述後、データタブから「テーブルまたは範囲から」を選択して取り込み

テーブルにする時は、「先頭行をテーブルの見出しとして使用する」に
チェック
を入れます。

OKを押すとパワークエリエディターが開きますので、そのまま次の作業に入ります。

テーブルをパラメータ用のクエリに変換する

エディターが開いたら、アドレスが記述されている部分で右クリックし、
ドリルダウンを選択します。

プロパティの名前がテーブル名と一致していることを確認する

ドリルダウンを選択すると、テーブル名のステップが追加されます。

プロパティの名前がテーブル名と一致していることを確認してから、
「ファイル」タブを選択し、データを「接続の作成のみ」で取り込みます。

ファイルタブを選択して、閉じて次に読み込むを選択

「接続の作成のみ」を選択してOKをクリック

ここまでできれば、パラメータ用のクエリ「データの保存場所」が出来上がります。

クエリのステップにパラメータを記述する

ここまでできたら、本命のクエリにパラメータを書き込みます。

本命のクエリを開き、最初のステップである「ソース」を選択すると
データソースのアドレスが記述された部分があります。
(数式バーが表示されていない場合は、「表示」タブから数式バーに
チェックを入れてください)

データソースが記述された部分を確認する

データソースのアドレスを先程作成したパラメータである「データの保存場所」に
変更します。(「””」はいらないです)

これでパラメータをクエリに適用することができました。

プライバシーレベルの設定をはずす

パラメータの設定が完了した後、データを更新すると
「Formula.Firewall」から始まるエラーが出るかもしれません。

「Formula.Firewall」から始まるエラー

これは、意図しない情報漏えいを防ぐためにマイクロソフトが作った仕組みです。

詳細はマイクロソフト公式ヘルプを読んでほしいのですが、ざっくりいえば、

「複数のデータソースを連携していて、そのデータがWEB上に公開されているデータと、会社の経理データといったような機密性の異なるデータだった場合、データソース間で情報交換が行われると、機密性の高いデータが漏洩する可能性がある。

そこで、プライバシーレベルの異なるデータソース間ではデータ連携しないように制限がかかる。」

ということだそうです。
いつものことながらよくわかりません(´・ω・`)

詳細が気になる方は、下記リンクから読んでみてください。

プライバシー レベル (Power Query)
プライバシー レベル (Power Query)

いずれにしても、セル上の数値をパラメータとして使うには、このエラーを回避しなければなりません。
回避するために、この情報漏えい防止のシステムを無視するように設定します。

この部分の設定について

「異なるプライバシーレベルの間で制限がかかるなら、同じプライバシーレベルに設定すれば良いのでは?」
思った方もいるはずです。

実は私も同じ事を考えました。
そこで、
ブック内で利用している、すべてのデータソースのプライバシーレベルを同じレベルにしてみたのですが、
このエラーが解消されることはありませんでした。

パラメータの場合は違うルールでも適用されるのでしょうか……。
いずれにしても、私の環境ではプライバシーレベルを無視するという方法しか
ありませんでした。

この点について知っている方がいれば教えていただきたいです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

フォルダ選択ダイアログについてはオフィス田中さんのこのページが参考になります。

フォルダを選択するダイアログ

パラメータ作成は他にも応用例があって、
日付を入れてその日だけのデータを読み込んだり、
勘定科目を入力して入出金だけを表示する

といったことも出来ます。

応用が効くテクニックなので、ぜひご自分の環境で試してみてください!

おぎの
おぎの

ここまで読んでくれてありがとうございました!

タイトルとURLをコピーしました