★★★★★★★★★★★★★★★★Access2000VBA・Excel2000VBA独学~★超重要!!「■ Microsoft Query + SQL」、「■ ODBCデータソース or ADO/DAO + SQL + VBA +QueryTableオブジェクト」、「■ PowerQueryやPowerPivotやリレーションシップ機能+テーブル機能等々」の違い。本当に便利なのはどれなのか?~
  
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
  
目次
< ※Shift+TABキー、もしくは、Homeキー、Homeキー+TAB数回、を押すと、目次付近に戻れます。   
  

★ はじめに

Excelでは、ワークシート関数以外に、「表と表をリレーション」させたり、「リレーションさせながら・同時に絞り込みや計算・並べ替え等々も同時に行う」のには、3つくらいの方法があると思います。

SQLを直接使う方法として、以下の2つ。

(01)「Microsoft Query + SQL
   (クリック・ドラッグ中心だけど内部的にSQLを自動生成。手動記述も可能。VBAなし。)と、

(02)「ODBCデータソース or ADO/DAO + SQL + VBA +QueryTableオブジェクト」
  
  
SQLを直接は使わない方法として、以下の1つ。

(03)「PowerQueryやPowerPivotやリレーションシップ機能+テーブル機能等々」
  
  
そのほかにもあるかもしれませんが、ぱっと思いつくだけでも上記の3つがあります。

この中で便利のいいのは結局のところどれなのでしょうか?
  
  
★ 結論(僕個人の独断と偏見)

僕の場合、クラウドを使ったり特別なことはしないので、以下の優先順位となります。

(02)「ODBCデータソース or ADO/DAO + SQL + VBA +QueryTableオブジェクト」
(01)「Microsoft Query + SQL
(03)「PowerQueryやPowerPivotやリレーションシップ機能+テーブル機能等々」

(02)と(01)は多分多くのケースでこの順位だと思いますが、(03)については、「(03)でしかできないことがどうしても必要な人」ならば、(03)が一番上にくるかと思います。

僕の場合は、そんなことがいまだ無いので、(03)は面倒くさすぎて、使う気になれません。(SQLも使いにくいっぽいですし。)

パワーピボットのようなことも、QueryTableオブジェクト(MicrosoftQueryも含まれます)の結果の表に、Offset関数を使って名前定義すれば、バージョン2000(20年前のバージョン)でも同じことができますから。

ただ、ダッシュボード機能?みたいなのが無いので「絵や図での見える化」のときには劣るかもしれませんが。
でも、集計やリストアップだけならまったく同等・・・というか(01)(02)のほうが遥かに(03)よりも「格上」です。
また、「見える化」は「「絵や図での見える化」よりも、文字だけの見える化のほうが多いですから。

また、操作性のシンプルさやVBA連携・SQLでの効率化やコスパアップが可能かどうかで言えば、それも(03)はすごく劣ります。推測しますに、機能自体の構造が複雑すぎて、特にVBAでの「複数の集計やリストアップ」については効率が悪くて仕方がないと思います。
たとえば「(01)や(02)にて・SQLでやれは2分~10分で済むことを、なんで(03)では、こんなにウィザード作って複雑化しているのか?」という感じです。さらには、複数の(03)の処理を、VBAで連続自動処理することについてはまるでできないですし・・・。(できるのかもしれませんが多分アホみたいに難しく、(02)で実現するほうが10倍効率的でコスパもいいです。(03)でなければならない、というのはかなり特殊な事情ではないかとおもいます。)

その他の理由はこれ以降に示します。
  
  
★ その前に「重要な注意事項!!」

(01)、(02)、(03)、は、Excelで標準的に良く使われる機能ではないため「Web上にも文献が少ない」です。

そのため、『 https://www.shegolab.jp/entry/excel-tips-data-combination 』のようなWeb記事が多く存在します。

この記事は正しいです。
そして、記事を書いている方は、私の数十倍、SQLにもVBAにも詳しい人です。

でも、このような記事を信用「し過ぎ」ないでください。

たとえばこのWeb記事は、(01)や(02)が「将来は無くなるかもしれない」と役に立たないかのような記述をしていらしゃいますが、それは半分は確かにそのとおりです。「将来は無くなるかもしれない」です。でも、「役に立たないものではありません」。

例えば、(01)と(02)は、さらに例えば、「Windows10」が「最後のWindows」として今後も存在するうちは、VirtualBOXやVMWareなどの仮想マシン管理ソフトを使って、古いExcelを使い続ける方法もあります。

例えば「Excel2000(数千円)」なら「VMWare+XPorWin2000」などで今も使えますし、「Excel2016」などなら、もちろんそのままWin10でネイティブに使い続けられます。

といいますか、(01)(02)は、「VMWare+XPorWin2000+Excelの古いバージョン」なんていう面倒くさい方法をとってでも、その利用価値が、「非常に」高いです。

そもそも、「VMWare+XPorWin2000」自体も、仮想マシンを一個作っておけば、他のマシンに「それをコピペするだけ」で使えます。そして仮想マシンはLANにも親PCとともに参加させることができます。そのなかにExcrelが入っていれば、もちろんExcel込みでコピペでき・仮想マシンのデータをLAN経由でも利用できる・・・ということになります。それを面倒と思うかラクと思うかは個人差があるかもしれませんが・・・。
前のマシンから仮想マシンを消してから作業すれば、ライセンス的にも問題なく「仮想マシンを新マシンにコピペ」するだけで、いつまででも「Excel2000」を使い続けられます。
そしてSQL Server、MySQL、オラクルのSQLの学習がわりにも使えます。
(VMWereやVirtualBOXのバージョンがWin10の大型バージョンアップに追い付かなくなったらそれもそのとき考えればOKです。)

もっと言うと、(01)(02)は、Win10や他のOSをネットから切り離して、大型アップデートを止めて「スタンドアロン環境・あるいは閉じたLAN」ででも、「使い続ける価値のある機能」です。

だって、OSのライセンスさえあれば、仮想マシン同士でもLANが組めますし、仮想マシンコピペするだけで、複数のパソコンで使えるし、手間がいらないんですもの。

事実、今の時点での最新バージョンの2019でも使えますし、SQL Server や MySQLにも接続できる・しているユーザーが居ることも考えますと、「まだしばらくは消えない」、と思います。
もちろん、いつかは消える可能性はありますが、前述したように「VirtualBOXやVMWareなどの仮想マシン管理ソフト」を使う方法も利用すれば、「あと20年はいけそう」な感じです。

そのあいだに、今のPoweQueryなどがどう進化するか、参考文献が増えるか、ですね。

逆に言いますと、(03)はそこまでする「魅力が無い」と、そういう機能でもある、と言えます。(その理由も詳しくは後述します。)

いずれにしても、(01)や(02)のような、
「あと20年は使えそうな機能」であり・かつ・「仮想マシンに入れてでも使い続ける価値のある機能」については、こういう内容の記事は、信用することはしてもいいですが、「し過ぎ」てはいけないと思います。

ちなみにですが、この記事の2ページ目の、『 複数リストから全ての組み合わせデータを作りたい(2/2) 』の記事の終わりのほうに書いてあるコードと同じ結果を得ることができる、VBAプログラムを2つ、ご紹介しておきます。
両方ともバージョン2010で作りました。

ただし こちらの『 複数リストから全ての組み合わせデータを作りたい(2/2) 』のコードは1枚のシート上に複数の表があっても選択して処理できるように高度になっています。
また、パワークエリを直接いじってクロス結合しているのではなく、ExcelVBAのRangeオブジェクトなどでループなどでゴリゴリにクロス結合している・・・・っぽいです。
なので長いのですが・・・。
また、「パワークエリを直接VBAで動かす」様子もわかりません。

ただ、一般的にはそこまでしなくても普通にMicrosoftQueryを使う方法をマクロ記録しただけでも同様のクロス結合が、自動化は可能です。どちらを使うかは利用シーン次第ですが・・・。(ただ、このサンプルのように範囲を選択すると大きな表の場合は「遅くなる」とは思います。)
そして、「このページの ” CrossJoin ” プロシージャの長さと内容のわかりやすさ・ループ処理の数」と、後述のコードのそれを見比べてみてください。基本、ループ処理の個数が増えればるほど、セルを参照すればするほど、プログラムの動作速度は遅くなります。

いずれにしましても、以降の2つのサンプルコードの いずれかのコードで、アクティブになっているシートに、前述の『 複数リストから全ての組み合わせデータを作りたい(2/2) 』の記事と同じ、「クロス結合」の結果が表示されます。
このサンプルでは「D:\1\5.xlsx」というExcelファイルに記録された、Sheet1~Sheet3(つまり、具、カレー、辛さの3つの表)を「クロス結合」するサンプルです。

ところで、このコードは、実は、「マクロの記録」機能で作りました。
特に1つ目のサンプルコードはそれを、SQL文(SELECTのところの1行)だけを短く書き換えただけのものです。
「SELECT 具, カレー, 辛さ FROM ・・・・・・・・・・・・」と1行書くだけです。MicrosoftQueryやExcelのSQLの場合、複数の表を「あえて結合しない」と「クロス結合」になるようです。
なお、内部結合(VLOOKUP関数などと同じ)、外部結合、集計関数による集計、縦結合(マージ)なども、同じようなパターンでやれます(これらの場合は「結合」というものをして)。
以下のサンプルは、「テーブル機能」を併用するものと(1つめ)、しないもの(2つめ)が作ってあって、いずれも、アクティブシートに、「具、カレー、辛さ」の3つの表がクロス結合された結果が表示されます。
『 マクロの自動記録でも簡単にSQLを使ったコードが自動生成される 』、『しかも短くて作り変えがラク 』というところに注目してください。
※4流以下の講師・市販書籍著者・ライター・編集者は、ここをまったく説明しません・やりません。だから4流以下なのですが・・・。

なお、「Microsoft Query」は、実はExcelからは独立した機能です(Excelが開いてなくても単独使用が可能です)。が、SQL実行の結果の表は、「QeryTableオブジェクト」に返ってくるように、Excel自身が指示を出しているっぽいです。
なので、「Microsoft Query」と「QueryTableオブジェクト操作」は、操作は異なりますが、結果は「QueryTableオブジェクト」に出力される、「同等なもの」と理解して良さそうです。(バージョン2007以降は、ExcelのMicrosoft Queryは、テーブル機能の中にQueryTableオブジェクトを埋め込みます。=1つめのサンプル)

では、以下、2つのサンプルコードです。

  

※2つ目のサンプルの(自動書き込みされる)細かい設定とダイアログボックスの対応状況の図  (『Excelで使うMySQL データ分析編』のP272の図を引用しました。)

  
  
★ 20000での吸い込みプログラムを関数化したもの

基本、「SQL内容・SQL文」の部分のコードを書き換えるだけでも色々できます。

※2010でも動きました。自ファイルも吸い込めます。何段階でも。そして動作が速いです。
ただ、自ファイルを読み込んだ場合は、そのQueryTableオブジェクトは、MicrosoftQueryでは「認識しない」という感じのエラーになってSQLの書き換え等ができません。2000も2010も同じようです。
2000では、MicrosoftQueryでなら多段階にネストすると、エラーが消えることは消えます。
でも、結果の表示の速度が超遅いです。
なので、多段階にするときは(VBA以外は不可に近いため)、MicrosoftQueryやSQLに慣れるまでは1ファイル単位で多段階にしたほうがいいのかもしれません。

  
  
  
★ ↑ 上の20000での吸い込みプログラムを関数化したもの・より詳しく版
(こちらのほうが実用向きかも?です。)

既存のQueryTableオブジェクトと同じ名前(近い名前)のものがないかチェックしてから、無ければQueryTableオブジェクトを自動作成する版です。

★★★★★★Access2000VBA・Excel2000VBA独学~★★超重要!!!ODBCでQueryTableオブジェクトを作成するサンプルコード)。多分、2000以降の全バージョンで使えると思います。~

読みこみたいファイルの指定、吸い込みたいファイルの指定等々、SQL文、などをプログラムの上の方でいじれます。
なので、すぐに他のファイルの内容を、「SQLによってリレーションや集計関数などを駆使した複雑な条件での集計や絞込み・リストアップ」をしながら、吸い込めます。

少し作り変えて、アドインファイル(xlam)として登録すれば、どのファイルにでも、すぐに他のふファイルの内容が吸い込めるようになると思います。

(※別手法ですが、ODBCデータソースを作れば、テキストファイルの内容SQLを使いながら読み込めます。リンク先の記事に、さらにその件のリンクがありますのでそちらをご参照ください。)

  
  
  
★ (03) の「PowerQueryやPowerPivotやリレーションシップ機能+テーブル機能等々」が「僕にとっては」ダメな理由

もちろん、「PowerQueryやPowerPivotやリレーションシップ機能+テーブル機能等々」が「まったくダメ」、ということはありません。

生き生きと使える場面も必ずあるはずです。
もしかしたらクラウド対応とか、より、簡単に縦結合・横結合、集計、その他、ができる、ということもあるかもしれません。

そもそも「ワークシート関数やSQLを学ばなくても・VLOOKUP関数の持つ欠点のような事を気にせずとも・表の結合ができる」ということは、もうすでにそれだけでも、また、「それが2つの表のあいだだけ」だとしても、私たちド素人・末端のユーザーにとっては価値が高いです。

でも、「SQLの学習」は「Excelを集計ソフトとして使う以上は」、VBAの学習よりもある意味重要です。
そのような意味を考えますと、どうしても「PowerQueryやPowerPivotやリレーションシップ機能+テーブル機能等々」「だけ」だと弱いのです。なので、あえて、それらの機能は「ダメ」と書かせて頂いてしまいました。
私の思い上がり、どうかお許しください。

では、以降に、その「ダメ」な理由を書きます。
(はじめてパワーピボットやパワークエリを触ったときの感想メモ書きがベースになっているので、言葉遣いが悪いところがあります。どうかお許しください。ただ、当時、そう感じてしまったのも事実です。そのことも少し、イメージしてみてください。肯定しなくてもいいので、イメージだけでもしてみてください。)

(a)
SQL」でできることをGUIでやろうとしているくせに、それを隠そうとしており、使い勝手が悪すぎます。同じように「SQLでできることなら(01)か(02)のほうがやりやすいですし、おまけに、「Access」や「SQLそのもの」自体の学習もできます。つまり(多少の方言はあるものの)、SQL Server、オラクル、MySQLなどの学習を「意識せずとも自然・かつ・自動的におこなっている」ことにもなります。SQLは「Googleスプレッドシート」でも使えますし。
なので、(01)や(02)のほうが(03)よりも断然・利用価値が高いです。
(03)は「他のソフト(特にデータベースソフト)にはまったく使えず、 ” Excelでしか ” 利用できない」ので、「仕事として使う・今後のステップアップも考えて使う」にはすごく効率が悪いです。

(b)
そもそもですが、(01)と(02)は多分(03)と同様にSQL Server やMySQLなどにも接続できたと思います。(古いバージョンではできていました。多分今のバージョンでもできると思います。)
今さら(03)は別に要らないかな・・・という面もあります。
特に僕はExcelを高度に使ってはいないので・・・。

(c)
操作性も悪いです。難解です。
少なくとも(01)か(02)の2・3倍は面倒くさいです。
(01)と(02)なら1ドラッグ&2クリック、あるいは1文・2文で終わることをダラダラと多くの操作をさせられます。

(d)
例えば 「Power Query」という機能ですと3枚以上の表を一度にリレーションできません。
できるのかもしれませんが、デフォルトではできないもようです。
アホすぎてびっくりです。
(01)や(02)なら何枚の表でも「一発で」リレーションできます。

(e)
(01)(02)はAccessのクエリの画面とほぼ同じで使いやすいです。SQL Serverなどのビューの画面にも似ていると思います。
(03)は似てないので「なんとなく使う」ということができません。当然、SQL Serverなどの練習にもなりません。

(f)
複数の表を扱いたい時、個々の表がいちいち「シートとして」読み込まれてしまって動きが「遅い」し面倒くさいです。(結果の表1枚・1発だけで終われないのです。)

(g)
Excelのバージョンごとに使える使えないがあるので、機能を統一しづらいです。
でも、(01)(02)なら、2000~2019までの全バージョンで使えます。特に(02)がバージョン間の差異もほぼ無くて便利です。
(01)は2007以降はMicrosoftQueryの結果がテーブル機能の中に埋め込まれてしまいますが(そしてそのせいなのか、何重かにネスト的に使えなかったり自ファイルがエラーで覗けませんが)、しかし、(02)はそれがありません。(バージョンも関係なく。)

(02)は全バージョンで、どれだけでもネスト的に使えますし、自ファイルを覗きに行ってもエラーになりません。
そして高速に動きます。

でも、かといって(01)が使えないかというとそうでもなく、便利に使える場面も少なくないです。
(02)をやるときのSQL文の生成に便利ですし。
ちょっとしたことならいちいち(02)を使うまでもなく、ドラッグとクリック中心に(01)で完結します。
リレーションを切り替えての操作をすばやく、数種類、切り替えたいときも(01)のほうが便利です。

(03)はそのどれもが、「できません。」

(h)
SQL」でできることをGUIでやろうとしているくせに、SQLが、基本、使えない。使える方法が残されているようだけど、それを使ってしまうと元々の機能が制限されるらしい。詳しくはわからないけど、そんな面倒くさいなら、最初から(01)か(02)を使った方がはるかにラク。

(i)
そして何といっても「遅い」。(気がします。)

(j)
また、VBAでの自動化ができない(というかできるとは思うけど文献がない。また、QueryTableオブジェクトのほうが遥かに簡単でしかも動作が速い。結果でできた表のネスト的なことやすべての表の自動更新もラク。)

(k)
覗きに行っているファイルの「データの書き換え」が「できない」。
閉じたままはもちろん、開いたままでも。
逆に、(01)と(02)は、つまりはMicrosoft Queryの画面からも、VBAコードからも、「SQL」にて、相手ファイルが開いたままはもちろん、閉じたままでもデータの書き換えができます。