「Microsoft Query」で私たち素人のエンドユーザーでもできることいろいろ

● 「Microsoft Query」で私たち素人のエンドユーザーでもできることいろいろ

『VBAでMicrosoft Queryの結果表を表示する方法（本当のタイトル→★★★ Access2000VBA・Excel2000VBA独学～Excelに、テキストファイルの内容を、「SQLを使いながら」・かつ・「絞り込みながら読み込む」方法（QueryTableオブジェクトにてODBC接続利用）～）』

(01)VLOOKUP関数やExcel2016のリレーション機能のように表と表を紐付けする。
　　（2表間で複数の紐付け可能・2枚以上の表も紐付け可能）
(02)フィルタオプションのようにセルに直接細かい条件値を入れてフィルタする。
(03)複雑でないならフィルタしながら集計機能やピボットのようにグループ化集計できる。
(04)ピボットテーブルのソースにでき、そのデータ範囲を（列や行が増えても減っても）
　　指定し直さずにいつでもピボット更新でき・結果反映できる。

・・・といった、４つのことを「すべて同時に」できる機能です。

20年前（Excel97の時代）からOfficeに実装されている機能です。（一応Excelからは独立しているプログラムですがExcelとの親和性がすごく良いです。）

なお、「Microsoft Query」の画面は、Microsoft Access のクエリと酷似しています。
「SQL」と呼ばれるものも扱えますので、その勉強にもなり、AccessやSQL Server、MySQL、といったデータベース製品に触れる前に、それらの製品の基本原理を学ぶことができます。

ここでは、その「Microsoft Query」で、私たち素人にも何ができるのかをお伝えさせて頂きたいと思います。

【超重要】関連記事（ZipサンプルやPDFあり）
ExcelでのGUIでの簡易SQL機能「Microsoft Query」の使い方（Excel2010でもほぼ同じ操作です）～ビジネスデータ管理（コスト減等含む）を2～100倍効率化するツールその1
　　
　　
★ 「経営者様がExcelで「本当にやりたかったこと」はこれではないですか？」、のようなビジネスの定型集計や定型リストアップの多くが、VBAを使わずにできます。
ピボットテーブルと組み合わせることで、ですが。（ピボットテーブルのソースを Microsoft Query の結果表、あるいは、ネストしたMicrosoft Queryの結果表にすることで実現できます。）

★ 基本的にはGUIでの操作です。
ER図のような「テーブルと列名が見える」GUIです。
「ER図」でGoogle検索してみてください。
「Microsoft Query 」で画像検索しても雰囲気が分かると思います。

★ 閉じたxlsファイルの表の場合は、それをインポートせずともリレーション等々ができます。
閉じたxlsファイルの表同士のリレーションができます。それを、現在開いているファイルに対象の表をインポートすることなくできます。現在開いているxlsに表示されるのは、リレーションさせたりグループ化集計させたり、条件抽出等々をしたあとの結果の表のみです。
もちろん、開いているxlsファイルの表も扱えます。

★ 扱える表はExcel、Access、テキストファイル、CSV、SQLServer、オラクル、MySQLなどです。
MicrosoftQueryでは閉じた・開いたxlsファイルのほかに、Accessのファイル、テキストファイル、CSV、SQLServer、オラクル、MySQL、PostgreSQL、などのデータ（表）も扱うことができます。それらの表同士をリレーションさせたりグループ化集計させたり、条件抽出させたり、等々ができます。

※注意事項
MySQL、PostgreSQL、などの外部データベースは、別途にODBCドライバをそれぞれのサイトからダウンロードしてインストールしないといけません。もしかしたらオラクルもそうかもです。
また、オラクルやMySQL、PostgreSQL、のバージョンが最新なのに、Excelのバージョンが2000とかの古いバージョンだとダメかもしれません。オラクルやMySQL、PostgreSQL、のバージョンに合わせて、Excelもその同じ時期に出たバージョン以上のものを使う必要があると思います。（ODBCドライバのバージョンも同じです。）
また、ODBCドライバを入れるだけでは扱えないかもしれないので、詳しくはそれぞれのサイトで調べるか、例えばMySQLの場合なら「Excel MySQL 接続」とか「Excel MySQL MicrosoftQuery」といったような検索語句で調べてみてください。
Excelファイル、Accessファイル、テキストファイル、CSVファイル、SQLServer、一部のオラクル？、などは最初からODBCドライバが入っているので、特に何もせずとも扱えます。

★ SQL用の画面もあって、SQLでの操作もできます。
Excel方言でのSQLですが、かなりの柔軟なデータ処理ができるみたいです。

★ 実表をもとに仮想表を作ることができます。
SQLというもの自体が、データ抽出できる機能を持ったものですが、それがベースとなっているMicrosft Query でも同様です。そして「実表をもとに仮想表を作る」というニュアンスもあるので、そのおかげで、ソースとなる実表を破壊せずに済み、何度でも再利用できます。

★ 仮想表はピボットテーブルのソースにでき・かつ・列や行の増減を再設定せずに済みます。
SQL（≒Microsft Query ）にて抽出・絞り込んだレコード（＝データ）・・・、つまり、SQLで作った「仮想表」は、ピボットテーブルのソースにできます。一般の表やテーブル機能での表のフィルタの結果はピボットテーブルのソースにできません。絞り込んでも全部のデータが対象にピボット集計されてしまいます。
逆に、SQL（≒Microsft Query ）で絞り込んだ場合は、絞り込んだ結果をそのままピボットのソースにできます。
SQL（≒Microsft Query ）側で絞り込みの条件を変えた場合は・・・、例えば「期間、各種ID、アイテム、担当者、等々」の条件を変えた場合は、その絞り込み結果の変化に伴い、ピボットテーブルの集計結果も変化します。

つまり、SQL（≒Microsft Query ）側での集計・リストアップ内容の変更は、そのままピボット側にも反映される、ということです。

フィルタをかけた結果を別のシートに貼り付けてからピボットテーブルをかける、といった手間が減りますし、ファイルを開くときやコマンドボタンで VBAプログラムにて、「SQL（≒Microsft Query ）→ピボット」の順番に「更新」を自動的にかけてけば、いつでも、すぐに結果の反映をすることができます。

なお、SQL（≒Microsft Query ）での結果の表をピボットソースのソースに使うときに、全バージョンでは、名前の定義づけにて「=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))」という式を使うことで、行も列もその増減を可変にすることができます。

つまり、例えば期間や顧客ランクなどの条件を変更したいとき、
(a)SQL（≒Microsft Query ）の画面を再度開いて条件を変え、結果の表に反映させる。
(b)ピボットの更新ボタンを押す。
の２ステップだけで済みます。

(a)と(b)のあいだに、「ピボットのソース表の範囲の指定のし直し」、をする必要がありません。

そして結果の表も、もし列構成が変わればピボットの更新でそれも反映されるので、必要に応じてピボットの列構成をドラッグで変えるだけです。

逆に列構成も変わらなければ、単純に値のみが集計しなおされるだけですので本当にラクチンです。

※参考
● ピボットテーブルのソースの表を、行も列も可変にして、再設定しなくても済むようにする方法２つ（「=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))」とテーブル機能）

★ 各種の条件を指定してのデータ抽出がGUI画面で私たち素人にも行えます。
「フィルタオプション（フィルタオプションの設定）」のような感じで、かつ、よりラクに条件指定ができます。
その際のSQL文も、GUIで操作・設定することにより自動生成されます。

★ Accessの「クエリ」の機能とほぼ同じ仕組み・操作です。Accessの学習にももってこいです。
GUIでの操作を覚えると、Accessの「クエリ」の機能とほぼ同じ仕組み・操作なので、Accessのクエリの勉強をしていることとほぼ同等です。SQLを貼り付けて設定再現できるところも同じです。（SQLの方言が少し違いますが）

なお、ER図的なものを使ってのリレーション設定などは、より高度・高機能な他のデータベースソフト、例えば SQL Server などでも似たような操作ですので、そういったものの操作にも少し役に立つと思います。

★ 件数が少なければ、Microsoft Query の画面のなかだけでも、条件設定・結果表示・結果チェックができます。

★ Microsoft Query の画面で表示しきれない分は、すべてをExcelシートに表示することができます。
2003以降は調べてませんのですみませんが、例えばバージョン2000のMicrosoft Query では、Microsoft Query の画面の中には4096件くらいだったか、それぐらいしかデータが表示できません。
そのような場合は、「Excelに結果を返す」というコマンドで（既定動作ではMicrosoft Query の画面を閉じるだけでいい）、数万件の結果表示ができます。（各バージョンでの扱える行数まで。2003までなら約63000行まで。それ以降は100万行まで。ただし、10万件超えたあたりから遅くはなるとは思いますが…。）

★ Microsoft Query の結果のネスト（結果の結果の結果の結果という入れ子操作）ができ、複雑な条件の集計やデータ抽出がしやすいです。
OSやExcelのバージョンによっては少し操作が要りますが、Microsoft Query の結果にさらに、Microsoft Query をかけ、その結果にまたさらに Microsoft Query をかけて・・・といったことができます。
1つのBookの中で、集計や抽出を完結したい場合に便利です。
また、複雑な条件の集計やデータ抽出が可能となります。
OSがWin2000までなら（Excelもそれに見合ったバージョンなら）、無条件にそれができ、VBAでファイルを開くたびに（もしくはコマンドボタン押下で）ラクに、ネストした全てのMicrosoft Query の結果の自動更新ができます。
XPの無印やSP1だとWindowsUpdateの状況次第ですが、WindousUpdateをあててない状態なら大丈夫だと思います。
それ以降のOSは、最初のもととなるMicrosoft Query の結果だけ、いったん、Microsoft Query の編集画面を開いて閉じることだけをすれば（あとは何もしなくても）、それ以降のMicrosoft Query を更新・反映することができます。
なので、最初のMicrosoft Query の編集画面を開いて閉じることだけをマクロの自動記録機能なのどで自動化すれば、ネスト結果の全更新の自動化も恐らくできると思います。

★ ピボットテーブルの結果に対して、Microsoft Query をかけることができます。
あまり意味が無いかもしれませんが、一応できるみたいです。やってみたらできました。

★ GUIでいろんな条件を指定してのデータの抽出（リストアップ）ができます。
条件別の「レコード（行）のリストアップができます。
単一条件はもちろん、複数の条件での抽出ができます。
Excelの「フィルタオプション（フィルタオプションの設定）」のようなことができます。

★ GUIでいろんな条件を指定しての四則演算ができます。
ワークシートでの数式指定と似た感じで数式を簡単に入力して四則演算ができます。
（新たな合計列を作成して）
四則演算した結果の列は新しい列名を指定できます。

★ GUIで演算の中で、平均、合計、最大値、最小値、などの算出もできます。

★ SQL文で複数のテーブルの縦結合ができます。
SQL文を発行することで、テーブルの縦結合ができます。

★ VLOOKUP関数と同義の「リレーションシップの設定」がドラッグ一発のみでできます。
GUIの画面でも、SQLの画面でもできます。

★ GUIで複数のテーブルの横結合ができます。（リレーションシップでの結合）
テーブルをキーとなる列で複数、横方向に結合することができます。
２つ、３つ、と結合できます。

★ 主キーだけでなく顧客名などでのリレーションが設定できます。
商品IDや顧客IDといった「IDの列（主キーの列）」が無い表の結合のときに、顧客名だけでとか、電話番号だけでとか、でもリレーションを組むことができます。（すべての表にそれがあれば）
「IDの列（主キーの列）」が無い表同士で、電話番号でどちらの表にどのデータが無いか・あるか、などをチェックしたいときに使えます。

★ 複数本のリレーション設定ができます。（表と表のあいだで）
リレーションシップを1本だけでなく、2本、3本と複数、設定ができます。
商品IDや顧客IDといった「IDの列（主キーの列）」が無い表の結合のときに、顧客名と電話番号でリレーションを組む、といったことができます。

★ GUIで並べ替えがかなり柔軟にできます
単一列での並べ替えはもちろん、複数列での並べ替えもできます。
各列ではすべて、昇順・降順を指定した並べ替えができます。
複数列で並べ替えをする場合は、基準にする列の順番を入れ替えることもできます。
（少し面倒ですが）

★ 複数列一括での昇順・降順並べ替えが簡単です。
複数列一括で、昇順・降順の並べ替えがしたいときは、目的の複数列を全部隣り合わせにまとめて、一括選択のうえ、昇順・降順ボタンを押すだけです。グループ化したいときの前段階チェックとしてや、どんな値があるかのチェックのとりあえずのチェックの時に便利です。

★ リレーション組み、並べ替えと複数の条件設定（パラメータ設定）、四則演算や集合関数演算が全部「同時に」できます。
これがオートフィルタやフィルタオプションとの違いとして、一番わかりやすい部分かもしれません。
例えば「リレーションを組んで表示した結果（仮想表）のなかで、期間別に、グループ化しながら、平均、最大値、最小値、等々を出しながら、何らかの列を基準にして並べ替えて表示」、といったことが１つのGUI画面で設定できます。そして、結果をExcelシートに表示できます。

★ GUIでグループ化することでピボットテーブルのような集計ができます。

★ 列名に別名を付けることができます。
別名を付けることで、同じ列名があったときに見分けやすくしたり、意味のわかりにくい列名を分かりやすい列名に変更表示したりできます。

★ 複数の列の値を連結して別名を付けて表示することができます。
Excelで、数式にて「=A1+B1+C1」とか、「=A1&B1&C1」と書いて、一番下までオートフィルしたのと同じことが簡単にオートフィルミス（や数式ミス）することなくできます。その列に別名を付けることができます。

★ SQL文の自動生成
ER図のGUI画面でドラッグ操作等をすると自動的にSQL文も生成されます。
なので、私たち素人にはSQLを知らなくても操作ができます。
また、SQLの勉強もできます。

★ SQL文によるGUI設定の再現ができます。
自動生成されたSQL文をどこかにコピペメモしておき、それをSQL文用の画面にペーストすることで、GUI画面側の設定が再現されますのでとても便利です。SQLのことがわからなくてもバックアップができ、全くの無の状態からは、SQLのわかる人に再現してもらえます。

★ より高度な処理はSQL文でできることがあります。
GUI画面ででできない処理はSQL文で処理できることがあります。

★ パラメータクエリの作成
クエリの実行時にパラメーターを使って、条件値の入力を求めるような「パラメータクエリ」を作成できます。
実行時に条件値を入力するダイアログボックスが出てきます。文言も指定できます。
指定したパラメータの分だけ、ダイアログボックスも出てきます。

★ パラメータクエリのパラメータの値の入力はダイアログだけではなくセルでもできます。
見た目はまさに「フィルタオプションの設定」のように、Excelシート上で指定したセルに条件値を入力することができます。セルに条件値を入力すると、「更新」しなくても即座に表の抽出結果が変化します。

★ 条件式の作成にExcelVBAの側の関数を使う（含める）ことができます。
IIF、Left、Right、といった関数です。
（すべては使えません。ヘルプによるとODBCドライバがサポートできる関数だけだそうです。）
自作関数、ワークシート関数は基本、使えません。（ワークシート関数は使えるものもあるのかも？？）
自作DLLを関数として認識させることができるかどうかは試したことがありません。
Declareで呼び出すようなDLLならできるのかもしれません。ちょっと不明です。

★ 条件式の作成に集合関数を使う（含める）ことができます。
集合関数を指定するのにもGUIで操作でき、集合関数のことを知らなくても使ったのごとくの処理ができます。
慣れれば（あるいはリファレンスさえあれば）私たち素人にもできます。

投稿タグ: 「本物」に近づくために, Accessの独学, ExcelVBA, Excelの独学, Excel操作の基礎, Excel連携VBA, MicrosoftQuery, WordVBA, クエリ, クエリSQL, クエリVBA, パソコンでの自動化, ビジネスパソコンの基礎, ピボットテーブル関連, マクロ, 独学, 用語, 簿記

MENU

関連記事

最近の投稿

カテゴリー

MENU

「Microsoft Query」 で私たち素人のエンドユーザーでもできること いろいろ

関連記事

最近の投稿

カテゴリー

タグ

「Microsoft Query」で私たち素人のエンドユーザーでもできることいろいろ