【Excel】クエリで外部ファイルのテーブルを集計する

2020年11月27日 | IT全般

Excelでクエリを作成

【問題発生①】
Synologyを使って社内のデータを共有しているのですが、データの同期にタイムラグがあるため複数の部署で１つのExcelファイルを更新すると上書き保存で不具合がでることが分かりました。
誰かがファイルを開いて内容を編集していても、気づかずに開いて保存することができてしまうため、自分で編集したファイルが後から保存された人のデータで上書きされてしまいます。
これはSynologyによる同期に10分近いタイムラグがあることも状況を悪くしています。

※Dropboxだとファイルの競合を知らせてくれます。
以前使っていたファイルサーバーでは、Excelのファイルを開くときに編集中であることを知らせてくれて、後から開いた人には閲覧専用モードで開くということがありました。

【対策案】
Excelのシートの構成は、各部署の入力シートとそれらを集計するシートが１つのファイルになっていました。
部署同士の競合を防ぐため、入力用シートを部署ごとに別ファイルにして、集計用のページを１つ作ることにしました。
その集計の対応にクエリを初めて使ってみることになりました。

※以下は４つの支店がある会社のサンプルデータを使って内容を解説しています。

【クエリの作成】
入力用シートをテーブル化して、クエリ（Power Query）の機能で集計ページに読み込んで、一つのテーブルとしてまとめます。

なぜ計算式（関数）でやらなかったのかというと、入力用シートに入力されるデータの件数が分からないため、計算式を使うことができないからです。

※クエリというのはAccessで使われているデータベースの機能でもあります。データ受け渡しのルールという感じでしょうか。

①入力用シートの表をテーブル化
　各支店のシートを個別のファイルに切り分けて保存します。
　各入力用シートの表をテーブル化します。
　クエリで参照するためテーブルの名前は重要です。

②集計用ファイルにテーブルを読込
　メニュー：データ＞データの取得＞ファイルから＞ブックから
　テーブルの入ったブックを指定すると、テーブル名が表示されますので、①で設定したテーブルを選択し、「データを変換」すると、テーブルが新しいシートに作成され、クエリが作成されます。

　集計に必要なテーブルを順次読み込んで、集計に必要なクエリを作成します。

【支店のクエリの詳細】

let
ソース = Excel.Workbook(File.Contents("C:\Users\user\ドキュメント\売上集計.xlsm"), null, true),
売上東京_Table = ソース{[Item="売上東京",Kind="Table"]}[Data],
変更された型 = Table.TransformColumnTypes(売上東京_Table,{{"支店", type text}, {"取引先", type text}, {"1月", Int64.Type}, {"2月", Int64.Type}, {"3月", Int64.Type}, {"4月", Int64.Type}, {"5月", Int64.Type}, {"6月", Int64.Type}, {"7月", Int64.Type}, {"8月", Int64.Type}, {"9月", Int64.Type}, {"10月", Int64.Type}, {"11月", Int64.Type}, {"12月", Int64.Type}, {"合計", Int64.Type}})
in
変更された型

上記の例では、東京支店の売上を売上集計から読み込んでいます。
クエリの詳細は、メニュー＞クエリ＞編集＞詳細エディター、で見ることができます。
①ソースはファイルのパスを絶対指定しています。
②ソース（ファイル）からテーブルを指定してデータフィールドを読み込みます。
③読み込んだ後、表示するためにデータを加工することができます。

※今回は４つの支店があるため、４つのシートを４つの対応するクエリで読み込みます。
この時点でクエリで読み込んだテーブルを表示するシートが４つ作成されます。

【クエリの結合】

全社の集計をする方法として、各支店のテーブルとは別に集計テーブルを作ることも考えられますが、今回はクエリから１つのテーブルにまとめてみたいと思います。
１つのテーブルにまとめた後、ピボットテーブル（Power Pivot）で抽出・分析することができるからです。

複数のテーブルを読み込んで、複数のクエリが作成されたら、それらを１つに束ねます。
メニュー：データ＞データの取得＞クエリの結合＞追加
３つ以上のテーブルを選択して追加すると、新しいテーブルが新しいシートに作成されます。
複数のテーブル（クエリ）を束ねる新しいクエリが追加されます。

※結合するクエリができあがったら、各支店のシートは削除してもかまいません。各支店のクエリは必要ですので、消さないでください。

【集計用クエリの詳細】

let
ソース = Table.Combine({売上東京, 売上大阪, 売上札幌, 売上博多})
in
ソース

【問題発生②】

ここまで作業してクエリに大きな欠点があることに気づきました。
１つは読み込むファイルを絶対パスで指定しなければならない点です。そのため、読込元のファイルの保存先を変更するとクエリの読込設定を変更しなければいけません。
クエリを作成した時点のパス名は「C:\Users\user\ドキュメント\売上集計.xlsm」となっています。

この問題は、Synologyの同期ではさらに問題が重なります。
ファイルを共有フォルダに移動し、ファイルパスも次のように変更したとします。「C:\Users\user\共有フォルダ\売上集計.xlsm」
データを共有フォルダに同期した後、別のPCで集計ファイルを開いてもクエリが正しく動作をしません。
実際のWindowsのパス名は、「user」の部分に利用者のアカウント名が入ります。そのため、利用者の異なるPCでは、Synologyが同期をすると共有フォルダまでのパス名が異なることになります。

絶対指定のパス名ではこのように混乱が生じます。

※クエリは一度読み込んだあと、パスが不正でも読み込んだデータはそのまま保持されます。
クエリは自動更新しないため、手動で更新します。（マクロで自動更新することは可能）

【クエリの読込ファイルを相対パスで指定する】

ファイルパスを絶対指定から相対指定に変更することで、諸々の問題をクリアしようと思って、以下のサイトを参考に修正をしました。

データの取得を相対パスに変えてみた話【PowerQuery】｜とある会計士のひとりごと。 (sakatakablog.com)

ファイルパスをシート内で取得しておきます。
「=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1))-1)」
その後、クエリにファイルパスを読み込ませます。

注意点：
取得したパス名がURL形式になる場合、クエリの読込エラーが発生します。
OneDriveの設定を変更すると直るらしいという情報があったので対処したのですが、設定内容を忘れてしまいました。
（OneDriveでバックアップの対象になっていないフォルダ内では問題なく動作します）

【修正後のクエリの詳細】

let
filepath = Excel.CurrentWorkbook(){[Name="ファイルパス"]}[Content]{0}[パス名],
ソース = Excel.Workbook(File.Contents(filepath & "売上集計.xlsm"), null, true),
売上東京_Table = ソース{[Item="売上東京",Kind="Table"]}[Data],
変更された型 = Table.TransformColumnTypes(売上東京_Table,{{"支店", type text}, {"取引先", type text}, {"1月", Int64.Type}, {"2月", Int64.Type}, {"3月", Int64.Type}, {"4月", Int64.Type}, {"5月", Int64.Type}, {"6月", Int64.Type}, {"7月", Int64.Type}, {"8月", Int64.Type}, {"9月", Int64.Type}, {"10月", Int64.Type}, {"11月", Int64.Type}, {"12月", Int64.Type}, {"合計", Int64.Type}})
in
変更された型

①ソースを読み込む前にファイルパスを変数filepathに入れています。
②ソースでファイルパスを指定する部分でパス名（変数filepath）とファイル名を合わせています。
以下、変更なし