前の投稿で、複数のワークシートをまとめる、というお題を取り上げましたが、これ、「今、Excel で開いているブックにある、複数のワークシートにあるテーブルをまとめる」という処理でした。
Excel の処理として、扱うことのできるブックは「Excelで開いている」ことが鉄則です。
そのため、基本的には Excel でデータを扱いたいのであれば、まずは Excel でブックを開く、という処理が「必須」でした。 Excel で一度開けば、Excel という Application を頂点とした、「ブック - シート - セル」という階層構造で処理することができるからです。
VBAでも同じです。ただし、裏技的にブックを開かずにデータを取り出すことが技術的には可能でしたが、広くお勧めできるものではありませんでした。
[VBA] ブックを開かないで読む (Office TANAKA)
http://officetanaka.net/excel/vba/tips/tips28.htm
前回は、既に開いているブックのシートにあるテーブルから、それぞれクエリを作成しましたが、今回は、ブックをExcelで開くことなく、指定するだけで、そこにあるテーブルを「テーブル」という条件で絞り込み、リスト化して、一気に展開する、という流れです。
通常はここでワークシートやテーブルを選ぶ、または、[複数のアイテムの選択] オプションをチェックして、複数のワークシートやテーブルを選びます。
複数のワークシートやテーブルを選んだ場合は、その数ぶんの「クエリ」を自動的に作成します。それぞれのクエリが作成された後の、たとえば追加（ユニオン）の処理は、前の回で紹介した手順と同じです。
このテーブルやワークシートのオブジェクトを「選択する」条件を含めてクエリ化したいのです。
もう一度、ナビゲーターウィンドウをみると「フォルダー」のようなアイコンがあります。実は、これは Excel のブックそのものを表しているんです。
このフォルダの形をした「ブックアイコン」を選択して、[編集] ボタンを押すと、ブック内のオブジェクトとその属性がテーブル形式で編集可能になります。
ここで、オートフィルターの要領で、必要なオブジェクトの絞り込みが可能になります。
アニメーションGIFでは種類(Kind)でTableを指定して、テーブルオブジェクトだけに絞り込んでいます。
もちろん、名前で「～から始まる」や「～を含む｝で絞り込みをかけることも可能です。
そして、このブックだけしか選ばない方法の最大のポイントは、作成するクエリはたった1つしかない、ということです。よって、テーブルが何個あろうが、クエリは１つであり、以下で紹介するように、複数のテーブルを追加するのは、たった1回ボタンを押すだけでいいのです。
ただし、展開する前に不要な列を削除します。不要な列を削除、というよりは、「必要な列だけを残す」という作業が本質になります。
アニメーションGIFでは、展開する [Data] 列と、元のデータがどこかわかる [Name] 列を残して、他の列を削除しました。
[Data] 列の先頭行にある展開・集計するボタンで、展開します。
元データのブックが更新されたら、加工用に用意したクエリを更新することで、最新のデータを取り込みます。もちろん、名前の知らない新しいワークシートの新しいテーブルが追加されても、クエリの編集なしにデータを取り込むことができます。
そのため、列の順序、順番は関係なく、同じ「列名」を持つオブジェクトが「追加」されます。
[日付] [番号] [数値] という列順序のテーブルと、[数値] [日付] [番号] というテーブルは、問題なく追加可能だ、ということになります。
業務のほとんどは、まとめるべきワークシートの名前が決まっていることが多いですが、日付や期間、時期のようなワークシート名だと、ブックを開くたびにワークシートの数と、新たなワークシートが追加されている、というケースも少なくありません。
VBAを使えば解決していましたが、逆にいえば VBA を使わなくても、Power Query / 取得と変換の機能でここまでできることは、知っていて損はないと思います。
Excel でデータを扱うのなら、Excel でブックを開くのが鉄則
ただ、この手の処理は、同じブックで処理するよりも、元データと、加工・処理するブックは「違う」パターンが多いです。元データのブックは、別の部門だったり担当者が準備したり、更新したりしていて、そこから加工するのは自分、みたいな感じです。Excel の処理として、扱うことのできるブックは「Excelで開いている」ことが鉄則です。
そのため、基本的には Excel でデータを扱いたいのであれば、まずは Excel でブックを開く、という処理が「必須」でした。 Excel で一度開けば、Excel という Application を頂点とした、「ブック - シート - セル」という階層構造で処理することができるからです。
VBAでも同じです。ただし、裏技的にブックを開かずにデータを取り出すことが技術的には可能でしたが、広くお勧めできるものではありませんでした。
[VBA] ブックを開かないで読む (Office TANAKA)
http://officetanaka.net/excel/vba/tips/tips28.htm
Power Query でブックを開かずにデータを参照する
という前置きは置いておいて、Excel でブックを開かずに、別ブックから、そこに含まれている各ワークシートのテーブルを一気に取り込んでしまう処理は、アニメーションGIFで一気に紹介できるくらい「簡単」なんです。前回は、既に開いているブックのシートにあるテーブルから、それぞれクエリを作成しましたが、今回は、ブックをExcelで開くことなく、指定するだけで、そこにあるテーブルを「テーブル」という条件で絞り込み、リスト化して、一気に展開する、という流れです。
(1) ブックを選んで、テーブル選ばす
[新しいクエリ] - [ファイルから] - [ブックから] で、Excel ブックを指定できます。このとき、ブックに複数のワークシートがあれば、ナビゲーターウィンドウにそれぞれが表示されます。テーブルも同様です。
通常はここでワークシートやテーブルを選ぶ、または、[複数のアイテムの選択] オプションをチェックして、複数のワークシートやテーブルを選びます。
複数のワークシートやテーブルを選んだ場合は、その数ぶんの「クエリ」を自動的に作成します。それぞれのクエリが作成された後の、たとえば追加（ユニオン）の処理は、前の回で紹介した手順と同じです。
このテーブルやワークシートのオブジェクトを「選択する」条件を含めてクエリ化したいのです。
もう一度、ナビゲーターウィンドウをみると「フォルダー」のようなアイコンがあります。実は、これは Excel のブックそのものを表しているんです。
このフォルダの形をした「ブックアイコン」を選択して、[編集] ボタンを押すと、ブック内のオブジェクトとその属性がテーブル形式で編集可能になります。
ここで、オートフィルターの要領で、必要なオブジェクトの絞り込みが可能になります。
アニメーションGIFでは種類(Kind)でTableを指定して、テーブルオブジェクトだけに絞り込んでいます。
もちろん、名前で「～から始まる」や「～を含む｝で絞り込みをかけることも可能です。
そして、このブックだけしか選ばない方法の最大のポイントは、作成するクエリはたった1つしかない、ということです。よって、テーブルが何個あろうが、クエリは１つであり、以下で紹介するように、複数のテーブルを追加するのは、たった1回ボタンを押すだけでいいのです。
(2) 追加(Append = Union)は「同じ形式」であることだけ気を付け、展開する
絞り込んだ後、テーブルの中のデータを取りだす（展開する）のは、ボタン１つを押すだけでOKです。ただし、展開する前に不要な列を削除します。不要な列を削除、というよりは、「必要な列だけを残す」という作業が本質になります。
アニメーションGIFでは、展開する [Data] 列と、元のデータがどこかわかる [Name] 列を残して、他の列を削除しました。
[Data] 列の先頭行にある展開・集計するボタンで、展開します。
(3) 別のシートが増えても対応可能なクエリ
このクエリはクエリ内の適用済みステップで、ワークシートやテーブルを明示的に指定していません。 「指定したブックにある、テーブルオブジェクトをすべて集めて展開する」というクエリになっているからです。元データのブックが更新されたら、加工用に用意したクエリを更新することで、最新のデータを取り込みます。もちろん、名前の知らない新しいワークシートの新しいテーブルが追加されても、クエリの編集なしにデータを取り込むことができます。
(4) 列順序は関係なく、列名が重要
テーブルオブジェクト(ListObject)は「構造化参照」されますが、このクエリにおいても構造化参照されています。列名を使って、それぞれのテーブルの列の追加を行っているのです。そのため、列の順序、順番は関係なく、同じ「列名」を持つオブジェクトが「追加」されます。
[日付] [番号] [数値] という列順序のテーブルと、[数値] [日付] [番号] というテーブルは、問題なく追加可能だ、ということになります。
業務のほとんどは、まとめるべきワークシートの名前が決まっていることが多いですが、日付や期間、時期のようなワークシート名だと、ブックを開くたびにワークシートの数と、新たなワークシートが追加されている、というケースも少なくありません。
VBAを使えば解決していましたが、逆にいえば VBA を使わなくても、Power Query / 取得と変換の機能でここまでできることは、知っていて損はないと思います。
