Excel2000にて表と表を紐付けするときに、VLOOKUP関数以外に、「DAO」というミドルウェアを使って、紐付いた値を、１つのセルに表示する方法

● Excel2000にて表と表を紐付けするときに、VLOOKUP関数以外に、「DAO」というミドルウェアを使って、紐付いた値を、１つのセルに表示する方法
　　
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
　　
　　
あと、本記事は「32bitの」Excelだけでのお話です。ご注意ください。
ただ、OSが64bitであっても、インストールされているExcelが32bitなら大丈夫ではないかと思います。
現在のExcelがどのbitかは、「Excel bit 確認」などの語句でWeb検索してみてください。
　　
　　
目次
　★ はじめに
　★ 難易度等について
　★ VLOOKUP関数を使ったほうが早いケース
　★ 実際の操作とプログラムの実行
　★ ExcelのMicrosoft Query、Accessのクエリ、などの「管理画面」で許される「SQL文」の「形・書き方」（特に以降の(b)）
　★ VBAプログラムで「SQL文」を実行させる場合の、許される「形・書き方」
　★ (a)と(b)の図示を使っての説明
　★ SQL文を書き替えるだけで、色々データの表示結果を変える例
　★ 前述のプログラムを「関数化（部品化）」して、SQL文やファイルの場所を指定するだけで良いようにする。
　★ 前述のプログラムを書き換えて、SQLで作った仮想表の任意のセルの値をExcelのワークシートの1つのセルに転記する方法。

※Shift＋TABキー、もしくは、Homeキー、Homeキー＋TAB数回、を押すと、目次付近に戻れます。
　　
　　
★ はじめに

1つのセルに、VLOOKUP関数で、何らかの紐付いたデータ（値）を引っ張ってくることがあると思いますが、その代替というか、別のやり方をご紹介したいと思います。

「DAO」という「ミドルウェア」と呼ばれる機能を使って、VBAでやるものなんですが、POSレジプログラムのようなものを作ったりするときに、VLOOKUP関数が何らかの理由で使えない時に、その代替策となると思います。（「ミドルウェア」とは「中間に位置するソフト」というような意味です。）

まず、何かに紐付いたデータを1つのセルに表示させる方法、あるいは、複数の表を紐付けして「横に結合して」「1枚の表として」表示する方法としては・・・、

(01)VLOOKUP関数、あるいはINDEX関数とMATCH関数など、関数を使う
(02)「リレーションシップ」機能か「PowerPivotのダイアグラムビュー」を使う
　　（2016～2010の場合のみ。ただし2010ではPowerPivotアドインのインストが必要。
　　　マイクロソフトのリレーション作成動画）
(03)Microsoft Query のリレーション（＝SQL文）を使う
(04)DAOやADOなどのミドルウェア＋SQL文を使う

・・・の４つくらいがあると思いますが、本記事は、そのうちの(04)のことになります。（なお、「02」以外は、2000以降の全てのバージョンでやれます。）

これは閉じたxlsファイルの値も（そのxlsファイルを1つのデータベースとみたてて）引っ張ってこれますし、相手がExcelファイルでも、AccessのmdbファイルやMySQLなどでもODBCドライバさえあれば引っ張ってこれるのでので、それなりに便利な場面もあるかと思います。
基本、ExcelやAccess、SQLServerのODBCドライバは最初から入っているので、大抵は特に何もしなくても大丈夫です（あってもインストールCDやDVDを使っての機能追加のインストールとか）。また、MySQLやオラクル、PostgreのODBCドライバは別途にダウンロードしてインストールします。
もちろん、いずれかのソフトのバージョンがあまりにも古かったり新しかったりだとダメかもしれませんが、すべてのソフトのバージョンが同じ時期のものなら問題ないと思います。
　　
　　
※★参考：「表と表の紐付け」について

「表と表の紐付け」については、ぱっと思いつくだけで以下のような方法があります。

まず Excel限定なら（2000以降の全バージョンで）、
「VLOOKUP関数を使う」
「VLOOKUP関数の弱点を補った方法、例えば、INDEX関数、MATCH関数、COLMUN関数などを使う」
「MicrosoftQuery（2003以前は新しいデータベースクエリ）のドラッグを使う」
「MicrosoftQuery（2003以前は新しいデータベースクエリ）のSQLを使う」
「VBA＋QueryTableオブジェクトでSQLを使う」
「VBA＋ADOでSQLを使う」
「VBA＋DAOでSQLを使う」
（※ MicrosoftQuery、ADO、DAO、では、VLOOKUP関数同様、2枚の表だけでなく、3枚、4枚と、紐付けさせられます。また、AccessやMySQL、SQLServerなどのリレーショナルデータベースのテーブルを読みに行って、そのなかですでに紐付け完了後の表を読み込んだり、あるいは、複数の「単体の表」を読み込んで、それらを紐付けさせるということも可能です。）
『参考：VLOOKUP関数で、「第15課　複数の表をつなぎ合わせて使う」』

次に、Excelの2013以降限定なら（2010はアドイン？アドオン？PowerPivotで？）」
「 ” リレーションシップ機能 ” のダイアログを使う（PowerPivot、2010用）」
「 ” データモデルのダイアグラムビュー ” でのドラッグを使う（PowerPivot、2010用）」
「Power Query のマージ機能を使う」
（※ 多分すべて、VLOOKUP関数同様、2枚の表だけでなく、3枚、4枚と、紐付けさせられます。AccessやMySQL、SQLServerなどのリレーショナルデータベースのテーブルを読みにも行けます。ただ、PowerPivotでリレーションを作ると、ピボットテーブルの集計フィールドやグループ化が使えなくなるようです。これはこれでデメリットですね。。。ただ、まだ不勉強なので詳細がわかりません。すみません。間違いもあるかもです。基本、動作が遅いです。操作対象がクラウドデータだともっと遅くなるかもしれません。VBAでの自動化は「QueryTable＋Offset名前定義＋PivotTalbe」ほど簡単そうではなく、結構難しそうです・・・。SQLは使えないっぽいですが、ただ、クラウド対応にとても期待ができるそうです。また、グラフ連携などのビジュアル系での見える化にも期待が持てるかもしれません。また、データを多種多様な加工しながら表のマージができるので、SQLではできないこともできる側面があります。きっと便利な面も多いかと思います。）
マイクロソフトのリレーション作成動画）

プラス、それらとVBAや関数の合わせ技

などの方法があり、

最後に、Excel限定でなければ・・・
「AccessでSQLを使う」
「Accessのクエリでドラッグを使う」
「MySQLでSQLを使う」
「SQL Server やオラクルでSQLを使う」
「SQL Server やオラクルのビューでドラッグを使う」

といった方法があります。

　　
　　
★ 難易度等について

また、本記事の処理は、「プログラミングでやるかたち」なので、何やら面倒・難易度が高くに感じるかもしれませんが、でも、SQL文を多少でも使える方なら（あるいは勉強してもいいなら）、プログラムを１つ作ってしまえばあとはデータを見に行くxlsファイルのフルパスの指定を変えたり、SQL文を書き替えて色々対応すればいいだけなので（他は書き換えなくてもとりあえずは使えるので）、かえってラクになる、ということもあります。
（ほかの部分も書き換えをするにしても、どのセルに書き出すかを変えるだけのことが多いです。）

SQL文は横に長く書くと意味不明のちんぷんかんぷんですが、でも実は、カンマや空白スペースごとになら改行してもエラーにならないので、テーブル名と列名をメインに使って縦方向に構造的に書きかえることができます。で、それが結構（意外と）見やすくて、かつ、それほど難しくないものも少なくないです。そして「その ”それほど難しくないもの” であっても、結構なことが・意外とかなりなことが」できます。
（条件別の集計やリストのピックアップなどにおいて。）

※なお、「テーブル名や列名など」、文字でリレーションの設定や絞り込み条件の設定の処理ができるということは、「もともと命令を ”文章で自動化” しやすい」ということなので、クリックで設定したリする手間が省ける、ということもあります。

複数の表を結合する際も、横の結合だけでなく、縦の結合もできますし・・・。（これはDAOやADOでもできます。）

なお、今回の例では紐付けで引っ張ってきた値の表示を、シート3の「A2」セルを起点に横方向に自動的に行っていますので、プログラムのなかで「SELECT（SELECT句と呼びます）」のところで指定した列（＝呼び出した列）が左から順に自動的に並んで表示されます。
が、別の命令を使えば、どこか適当なバラバラな1セルずつ、に、引っ張ってきたデータを入力することもできます。
例えば、A1セルには引っ張ってきたデータ（例えば1レコード＝1行分）のうちの「姓」だけを表示したリ、C3セルには「顧客ID」、F10セルには「電話番号」を・・・、といった具合にバラバラに飛び飛びに値を表示させることも可能です。
もちろんどこかのセルに、「姓」と「名」をスペースで結合したものを入れたり、すべての列の値を全角スラッシュで結合したものを入れたり、ということもできます。

また、数式でセルに値を表示するわけではなく「生の実データが自動入力されます」ので、「数式を破壊されて他の関連セルにも影響が出る・・・」、といった不都合も起こりません。（消されたり直されたりしたらどうしようもないですけど・・・。でもまあ、そうされない限りは、不都合がおこりません。）

　　
　　
★ VLOOKUP関数を使ったほうが早いケース

とはいうものの・・・基本・そういうことをするなら（飛び飛びの1セルに引っ張ってきたデータを入れたい場合は）、こんな「DAO」なんて使わずに普通にVLOOKUP関数を使うほうがいい場合が多いと思います。マイクロソフトさんは「VLOOKUP関数は古いやり方」とか愚かしいことを言っていますが、ぜんぜんそんなことはなく、セル単位で何らかの紐付きデータを引っ張ってくるなら、基本的には VLOOKUP関数のほうが断然便利です。セルが連続する大きな表でVLOOKUP関数を使うのでなけば、例えば「初心者の方がうっかりの操作ミスで数式を破壊する」というようなこともほとんど起こらないでしょうし・・・。

開いているxlsの中でやるなら、VLOOKUP関数のほうが良ければそちらを使えば全然早いことのほうが多いので、VLOOKUP関数でオッケーかと思います。

閉じたxlsのデータを自由自在に紐付けて引っ張ってきたいなら、本記事のようにDAOやADO、あるいはMicrosoft Queryを使うほうが良いとは思います。

★ 実際の操作とプログラムの実行
・・・というわけで・・・、では、以下、簡易的な説明です。Excel2000での説明です。
（細かいところまで書けてないのですみません！）

今回は、古いExcelでも使えるようにと思って、DAOのほうを使いました。
（ADOのほうはWebに沢山事例があると思います。「excel ado sql join」という検索語句で是非調べてみてください。）

以下のような流れになります。

(01)Dドライブのルートに、空の「DAOテスト02.xls」を作成します。
（ルート→フォルダを作らずに、Dドライブに直接・・・という意味です。）

(02)シート1を「氏名表」にリネームして、以下のようなデータを入力します。
（リネーム→名前の変更をするという意味です。）

(03)シートを1つ増やして、シート4を、「電話番号表」にリネームして、以下のようなデータを入力します。

(04)ツール→マクロ→Visual Basic Editor を選びます。

(05)VBEが開くので、ツール→参照設定を押します。
出てきたダイアログで、「Microsoft DAO 3.6 Object Library」を探してチェックを入れ、OKします。
これをやらないと、本記事のプログラムはエラーが出て動きません。

(06)「挿入→標準モジュール」で標準モジュールをひとつ、挿入します。

(07)そこに、以下のコードをまんまでコピペします。

'
'
Option Explicit

Private Sub ReadByDao__test02()
'参照設定でMicrosoft DAO 3.6 Object Library にチェックを入れておく。
'このファイルを「D:\DAOテスト02.xls」として配置
    Dim DB           As DAO.Database
    Dim RS           As DAO.Recordset
    Dim xlFName      As String
'    Dim SheetName    As String    '※本記事では使わないコードなのでコメントアウトしています。
    Dim StrData01    As String

    Dim StrSql_01    As String
    'データベースファイル名定義、システムテーブル定義
    xlFName = "D:\DAOテスト02.xls"
'    SheetName = "Sheet1$"  '「Sheet1」をシステムテーブルとして読み込む。
'                           '「$」を付けるとシステムテーブルになる。※本記事では使わないコードなのでコメントアウトしています。
   
   
    'データベース(xlFName)ファイルを開く（ここではxlsファイル）
    'HDR=NO →「IMEX」の値を「=1」にしたときは、1行目を列名として読込む。（どうやら数値もテキストとして読み込まれるようです）
    'HDR=YES →「IMEX」の値を「=1」にしたときは、1行目を列名として読込まない。（どうやら数値は数値、文字列は文字列として読み込まれるようです）
    'IMEX=1→表示されているセル書式の値でデータを吸い込む。（今回のテストでは完全にはそうはなりませんでした。よくわかりません。）
    '        ただし、https://support.microsoft.com/ja-jp/help/194124/prb-excel-values-returned-as-null-using-dao-openrecordset
    '        や http://blog.sorceryforce.net/?p=154 を参照
    'Excel 8.0→「VBA Excel バージョン番号」でGoogle検索。
    '            8.0で2000でも2010でも動いた。
    
    '   Set DB = OpenDatabase(xlFName, False, False, "Excel 8.0;HDR=NO;IMEX=1") '←SQLを使う？からダメなのかDAOだからダメなのか何なのか分かりませんが、本操作では、「HDR=NO →1行目（列名）を読込む。」設定にするとエラーになります。
    Set DB = OpenDatabase(xlFName, False, False, "Excel 8.0;HDR=YES;IMEX=1")


    'SQL文の作成
    '氏名表$と電話番号表$を、「顧客ID」の列で紐付け（＝リレーション）したうえで、
    '氏名表$の顧客IDの列の値が、「1」のレコードを抽出し
    'その中の「電話番号」列の値だけを抜き出す・・・というSQL文 を作成。
    StrSql_01 = "Select 電話番号"                                    '「電話番号」の列だけを抜き出せ、という命令です。
    StrSql_01 = StrSql_01 & " FROM [氏名表$] INNER JOIN [電話番号表$]" 'どんな種類のリレーション（＝VLOOKUP関数のような紐付け）をするかの設定です。（少しあとの「※注01」をご参照ください）
    StrSql_01 = StrSql_01 & " ON 氏名表$.顧客ID=電話番号表$.顧客ID"     'リレーションを「氏名表」の「顧客ID」の列と、「電話番号表」の「顧客ID」の列で、それぞれ同じ値同士で紐付けしますよ・くっつけ合いますよ、という設定。
    StrSql_01 = StrSql_01 & " WHERE 氏名表$.顧客ID=1;"                'そのうえで、氏名表の顧客IDが「1」の行を抜き出す対象にする設定。この設定のうえで、「SELECT」に書かれた列名の列に絞り込まれます。
                                                                    'つまりこのSQL文では、WHEREという命令（WHERE句と呼びます）で1行に絞り込んでおいてから
                                                                    'SELECTという命令（SELECT句と呼びます）で1列に絞り込んでいるので、
                                                                    'その行と列とがクロスする ”1セル分” の値を取得していくことになります。

   '※注01 ↑ ちなみに「INNER JOIN 」は「内部結合」といって、VLOOKUPと同じような意味の命令です。
   '          小学か中学で習った「ベン図」の２つの円が交わるエリアを求めるようなイメージの命令です。
   '          （ラグビーボールを縦にしたような真ん中の交わったエリア）
   '          また、内部結合(INNER JOIN)のほかに、「外部結合（OUTER JOIN）」と言う命令を使って、
   '         「交わらない」左側だけの部分や右側だけの部分（それぞれ三日月みたいな形の部分）の、
   '          それぞれのエリアの部分も求めることもできます。
   '          　　　 例えば左の三日月の部分は、いったん「LEFT OUTER JOIN 」を使って出した結果から、
   '          　　　「INNER JOIN 」に相当するエリアを非可視化し、
   '          　　　 右の三日月の部分は、いったん「RIGHT OUTER JOIN 」を使って出した結果から、
   '          　　　「INNER JOIN 」に相当するエリアを非可視化する、
   '          　　　 という感じ・イメージで求めることができます。
   '         「どんな種類のリレーションか」ということの意味は、
   '         「INNER JOIN （内部結合）」にするのか
   '         「LEFT OUTER JOIN（左外部結合）」にするのか
   '         「RIGHT OUTER JOIN（右外部結合）」にするのか
   '          それとも、他の結合方法もあるので、それらにするのか
   '          どの種類の結合方法をとりますか？という意味になります。
    


    'レコードセットを定義（仮想表をメモリ上に作成・・・みたいな感じ。）
    '（ここではシート名で定義するのではなく、もう一つ別の方法として
    ' 　SQL文を使って定義しています。）
'    Set RS = DB.OpenRecordset(SheetName)  '←シート名で定義するときのコード ※本記事では使わないコードなのでコメントアウトしています。

    Set RS = DB.OpenRecordset(StrSql_01)   '前述のSQL文を用いて、レコードセット＝仮想表をメモリ上に作成
       

    'Sheet3 のA2セルに、定義されたレコードセットのすべてのデータ（レコード＝行）を貼り付け
    With Worksheets("Sheet3")
        .Range(.Range("A2"), .Range("A2").SpecialCells(xlLastCell)).ClearContents ’いったんシートをオールクリアでまっさらにする
        .Range("A2").CopyFromRecordset RS  '作成されたレコードセット（仮想表）をA2セルを起点にしてコピペ
    End With

   
    RS.Close
    DB.Close
    Set RS = Nothing
    Set DB = Nothing
   
End Sub
'
'

Option Explicit

Private Sub ReadByDao__test02()

'参照設定でMicrosoft DAO 3.6 Object Library にチェックを入れておく。

'このファイルを「D:\DAOテスト02.xls」として配置

Dim DB As DAO.Database

Dim RS As DAO.Recordset

Dim xlFName As String

' Dim SheetName As String '※本記事では使わないコードなのでコメントアウトしています。

Dim StrData01 As String

Dim StrSql_01 As String

'データベースファイル名定義、システムテーブル定義

xlFName = "D:\DAOテスト02.xls"

' SheetName = "Sheet1$" '「Sheet1」をシステムテーブルとして読み込む。

' '「$」を付けるとシステムテーブルになる。※本記事では使わないコードなのでコメントアウトしています。

'データベース(xlFName)ファイルを開く（ここではxlsファイル）

'HDR=NO →「IMEX」の値を「=1」にしたときは、1行目を列名として読込む。（どうやら数値もテキストとして読み込まれるようです）

'HDR=YES →「IMEX」の値を「=1」にしたときは、1行目を列名として読込まない。（どうやら数値は数値、文字列は文字列として読み込まれるようです）

'IMEX=1→表示されているセル書式の値でデータを吸い込む。（今回のテストでは完全にはそうはなりませんでした。よくわかりません。）

' ただし、https://support.microsoft.com/ja-jp/help/194124/prb-excel-values-returned-as-null-using-dao-openrecordset

' や http://blog.sorceryforce.net/?p=154 を参照

'Excel 8.0→「VBA Excel バージョン番号」でGoogle検索。

' 8.0で2000でも2010でも動いた。

' Set DB = OpenDatabase(xlFName, False, False, "Excel 8.0;HDR=NO;IMEX=1") '←SQLを使う？からダメなのかDAOだからダメなのか何なのか分かりませんが、本操作では、「HDR=NO →1行目（列名）を読込む。」設定にするとエラーになります。

Set DB = OpenDatabase(xlFName, False, False, "Excel 8.0;HDR=YES;IMEX=1")

'SQL文の作成

'氏名表$と電話番号表$を、「顧客ID」の列で紐付け（＝リレーション）したうえで、

'氏名表$の顧客IDの列の値が、「1」のレコードを抽出し

'その中の「電話番号」列の値だけを抜き出す・・・というSQL文を作成。

StrSql_01 = "Select 電話番号" '「電話番号」の列だけを抜き出せ、という命令です。

StrSql_01 = StrSql_01 & " FROM [氏名表$] INNER JOIN [電話番号表$]" 'どんな種類のリレーション（＝VLOOKUP関数のような紐付け）をするかの設定です。（少しあとの「※注01」をご参照ください）

StrSql_01 = StrSql_01 & " ON 氏名表$.顧客ID=電話番号表$.顧客ID" 'リレーションを「氏名表」の「顧客ID」の列と、「電話番号表」の「顧客ID」の列で、それぞれ同じ値同士で紐付けしますよ・くっつけ合いますよ、という設定。

StrSql_01 = StrSql_01 & " WHERE 氏名表$.顧客ID=1;" 'そのうえで、氏名表の顧客IDが「1」の行を抜き出す対象にする設定。この設定のうえで、「SELECT」に書かれた列名の列に絞り込まれます。

'つまりこのSQL文では、WHEREという命令（WHERE句と呼びます）で1行に絞り込んでおいてから

'SELECTという命令（SELECT句と呼びます）で1列に絞り込んでいるので、

'その行と列とがクロスする ”1セル分” の値を取得していくことになります。

'※注01 ↑ ちなみに「INNER JOIN 」は「内部結合」といって、VLOOKUPと同じような意味の命令です。

' 小学か中学で習った「ベン図」の２つの円が交わるエリアを求めるようなイメージの命令です。

' （ラグビーボールを縦にしたような真ん中の交わったエリア）

' また、内部結合(INNER JOIN)のほかに、「外部結合（OUTER JOIN）」と言う命令を使って、

' 「交わらない」左側だけの部分や右側だけの部分（それぞれ三日月みたいな形の部分）の、

' それぞれのエリアの部分も求めることもできます。

' 　　　例えば左の三日月の部分は、いったん「LEFT OUTER JOIN 」を使って出した結果から、

' 　　　「INNER JOIN 」に相当するエリアを非可視化し、

' 　　　右の三日月の部分は、いったん「RIGHT OUTER JOIN 」を使って出した結果から、

' 　　　「INNER JOIN 」に相当するエリアを非可視化する、

' 　　　という感じ・イメージで求めることができます。

' 「どんな種類のリレーションか」ということの意味は、

' 「INNER JOIN （内部結合）」にするのか

' 「LEFT OUTER JOIN（左外部結合）」にするのか

' 「RIGHT OUTER JOIN（右外部結合）」にするのか

' それとも、他の結合方法もあるので、それらにするのか

' どの種類の結合方法をとりますか？という意味になります。

'レコードセットを定義（仮想表をメモリ上に作成・・・みたいな感じ。）

'（ここではシート名で定義するのではなく、もう一つ別の方法として

' 　SQL文を使って定義しています。）

' Set RS = DB.OpenRecordset(SheetName) '←シート名で定義するときのコード ※本記事では使わないコードなのでコメントアウトしています。

Set RS = DB.OpenRecordset(StrSql_01) '前述のSQL文を用いて、レコードセット＝仮想表をメモリ上に作成

'Sheet3 のA2セルに、定義されたレコードセットのすべてのデータ（レコード＝行）を貼り付け

With Worksheets("Sheet3")

.Range(.Range("A2"), .Range("A2").SpecialCells(xlLastCell)).ClearContents ’いったんシートをオールクリアでまっさらにする

.Range("A2").CopyFromRecordset RS '作成されたレコードセット（仮想表）をA2セルを起点にしてコピペ

End With

RS.Close

DB.Close

Set RS = Nothing

Set DB = Nothing

End Sub

(08)実行してみて、シート3のA2セルに、顧客ID「１」の人の電話番号が表示されればOKです。

※実行するには、コピペしたプログラムの中のどこかの行をクリックしてアクティブにし、「実行→Sub/ユーザーフォームの実行」を押します。

※ プログラムの「StrSql_01 = StrSql_01 & " WHERE 氏名表$.顧客ID=1"」の行の「顧客ID=1」を「顧客ID=2」に変えて実行すると、探しに行く行が2行目（顧客IDが「2」の行）になるので佐藤さんの電話番号が出てきます。
　　
　　
ちなみに、プログラムの、「StrSql_01 = "Select 電話番号"」の行の「電話番号」を「*」に書き換えて実行すると、紐付けさせた行（ここでは顧客IDが「1」の行のみ）のすべての列のデータが出ます。A2セルを起点に、すべての列の値が表示されます。（「*」は「すべての列」という意味なので。）

この場合、「氏名表」と「電話番号表」の２つの表が、両方の中に在る「顧客ID」という列を経由して紐付けされますので、まずは、「両方の表を顧客ID列を介して横に合体（結合）させた表」ができあがります。そしてそこから、WHEREによって顧客IDが1番の「武田」さんのデータに絞り込まれ、それが表示されます。

さらに、「*」にしたまま、さらに WHERE 以降を全部とっぱらって実行すると、すべての行のすべての列のデータがA2セルを起点に表示されます。

この場合も前項と同じく、「氏名表」と「電話番号表」の２つの表が、両方の中に在る「顧客ID」という列を経由して紐付けされますので、まずは、「両方の表を顧客ID列を介して横に合体（結合）させた表」ができあがります。そしてそこから、3名全員のデータが表示されます。

「WHERE 以降を全部とっぱらう」ということは、「行を絞り込むための条件が消えて無くなる」ということですので、3名すべてのデータが」表示されます。

　　
　　
★ ExcelのMicrosoft Query、Accessのクエリ、などの「管理画面」で許される「SQL文」の「形・書き方」（特に以降の(b)）

ExcelのMicrosoft Query、Accessのクエリ、SQL Serverのビュー、などのSQLの「管理画面」で書く（許される）「SQL文の形・書き方」についてご説明します。本記事のSQL文を使ってご説明いたします。

このことは、Excelの「VBAの画面（VBEditorの画面）」での場合と似ていますが少し異なりますのでご注意ください。区別がつかないとエラーになってしまうことが多いので、ここではその意味で、あえて、ご説明させていただきます。

結論から先に言うと、各ソフトのSQLの管理画面では、以降の(a)のように全部の命令を半角スペースでつなげて1行にしてしまってもいいし、(b)のように、ちょっと見やすく、意味毎に改行しても大丈夫です。

(a)ぜんぶ 1行につなげてしまう書き方
見たまんまですね。すべての命令や式が、半角スペース区切りで1行につながっています。
（右端の隠れた部分は分をクリックすると下に左右のスクロールバーが出ますのでそれを右にずらしてみてみてください。）

'
'
Select 電話番号 FROM [氏名表$] INNER JOIN [電話番号表$] ON 氏名表$.顧客ID=電話番号表$.顧客ID WHERE 氏名表$.顧客ID=1;
'
'

Select 電話番号 FROM [氏名表$] INNER JOIN [電話番号表$] ON 氏名表$.顧客ID=電話番号表$.顧客ID WHERE 氏名表$.顧客ID=1;

(b)複数行に意味毎に分ける書き方
この場合は、
・「Select（＝表の中からどの列を「抜き出すか・表示させるか」を指定して！）」
・「FROM（＝列を抜き出す単一の表、あるいは複数の表を指定して！）」
・「WHERE（＝表の中からどの行を抜き出すかの、そのための条件を指定して！）」
といった大きな意味毎に、改行しています。
1行に全部をつなげてしまうよりは、少し、意味がわかりやすいと思います。

なお、「ON」のところで改行してあるのは、FROMのところが横に長くなってしまうので、「まあ区切りのいいところで適当に改行した・・・」ということで深い意味はありません。「INNER JOIN」のところで改行する場合も多いです。

'
'
Select 電話番号
 FROM [氏名表$] INNER JOIN [電話番号表$]
   ON 氏名表$.顧客ID=電話番号表$.顧客ID
 WHERE 氏名表$.顧客ID=1;
'
'

Select 電話番号

FROM [氏名表$] INNER JOIN [電話番号表$]

ON 氏名表$.顧客ID=電話番号表$.顧客ID

WHERE 氏名表$.顧客ID=1;

(a)と(b)のいずれも、たとえば、ExcelのMicrosoft Queryや、Accessのクエリなど、それぞれの管理画面で「実行ボタン」を押すと、このSQL文の内容が実行され、仮想表が表示されます。
たしか、SQL Server のビューやオラクル、MySQLなど、その他のデータベースシステムの管理画面でも同様だったかと思います。

ただ（ここがちょっと大事なところなんですが）特に(b)ですが・・・、ExcelのMicrosoft Queryや、Accessのクエリ、SQL Server のビューなど、それぞれの「管理画面」では、(b)のように改行されていても、上手いことに「改行コードが存在している」とは判断していません。「改行されていても、改行コードが無いものとして、自動的に1行に全部つなげ直してくれて、SQL文を実行している」というイメージです。（逆に、VBAプログラムでSQLを扱う場合は、1行につなげるか・分けるなら大抵配列を使わないといけないんですけど・・・。各種機能の「管理画面」のほうでは、改行してもスペースが多くてもオッケーなのです。）
1行に全部つなげ直してSQL文を実行している感じなので、各行の先頭か末尾に半角のスペースが必要です。もし改行ごとに先頭か末尾に半角のスペースが無い場合は、列の名前と「FROMやWHEREなど」のSQLコマンドがくっついてしまうことになるので、列の名前やSQLコマンドとしての判別ができなくなってしまいます。なのでエラーになります。ここはご注意ください。

【(a)の場合の補足説明】
「Select 」や「 FROM 」、「INNER JOIN」などの「句」とか「コマンド」とか呼ばれる「命令語句」ごとに「半角スペース」が存在しています。まあ、存在するというよりは、「必要」ということなんですけれども・・・

「すべての命令語句と式（=で結ばれている個所など）が、半角スペースで結合されている・・・」という感じです。

※注
決められた命令語句のうち、何が「句」で、何が「コマンド」かの区別は僕自身よく分かっていません。正直私たち素人はあまり高度なことはしないのでどっちでも大丈夫です。
実際には「Select 」や「 FROM 」は確実に「句」と呼ばれているのですが、「INNER JOIN」や「ON」などは「句」と呼ぶサイトもあれば「コマンド、SQLコマンド」などと呼ぶサイトもあります。（「句」とか「コマンド」とかで称さないサイトもいっぱいあります。）
なので本記事では、「Select 」や「 FROM 」などの大きなくくりに位置する命令語句を「句」と呼び、よくわからないもう少し細かいことをする命令たちを「コマンド」と呼ぶことにします。

【(b)の場合の補足説明】
(b)の場合、大きな意味毎に改行されています。『「句」ごとに改行されている』ともいえます。
ここでも、各「句」や「コマンド」ごとに半角スペース（複数個可）が存在します。
「句」や「コマンド」ごとに半角スペースが要るので、当然、改行ごとにも半角スペースがどこかに要ります。半角スペース（複数個可）が存在すればオッケーです。少なくとも改行の最後か最初のいずれかに必要です。複数個の半角スペースがあっても大丈夫なので改行の最初と最後の両方にあっても別にオッケーです。
ただし、全角スペースだとエラーになりますので注意が必要です。
空白行を作ってしまった場合もエラーになるかもしれません（未確認です。）

(b)の場合は以下のように書いてもいいんですが、「FROM」句が横に長くなるので、「ON」コマンドのところでも改行してしましました。

'
'
Select 電話番号
 FROM [氏名表$] INNER JOIN [電話番号表$]     ON 氏名表$.顧客ID=電話番号表$.顧客ID
 WHERE 氏名表$.顧客ID=1;
'
'

Select 電話番号

FROM [氏名表$] INNER JOIN [電話番号表$] ON 氏名表$.顧客ID=電話番号表$.顧客ID

WHERE 氏名表$.顧客ID=1;

どこで改行するかや半角スペースを何個入れるかは、基本、自分の好き勝手です。あるいは、社内のきまりに従います。

ちなみにですが、(b)のケースは、VBAプログラムではエラーになりますので書き方を変えないといけません。
そのことについては、後述します。
　　
　　
★ VBAプログラムで「SQL文」を実行させる場合の、許される「形・書き方」

※2019/04/29 間違っていたので修正

オブジェクトによってや、Accessのバージョン、Excelのバージョン、使用するプロパティなどによって、変わります。

例えばExcelの場合、SQLが（ユーザーの意思にかかわらず）自動的に使われるのは、
・Accessなどのデータベースのテーブルやクエリをピボットテーブルのソースとして覗きに言った場合、
・Microsoft Queryを使った場合
などです。

つまり、ピボットテーブルやMicrosoftQueryなどを使ったときに、SQLが自動生成されるわけなんですが、そのとき、それらのオブジェクトの「CommandTextプロパティ」か「SQLプロパティ」のいずれかでSQL文の内容を書き換えられます。
どのオブジェクトでどちらのプロパティが使えるか、両方使える、などは、Excelのバージョンによっても違うかもしれないので、どちらが使えるか、実際に、そのプロパティを使ってみてください。（ヘルプには書いてない場合・見つけにくい場合もあるためそのほうが早いこともありますので。）

で、「CommandTextプロパティ」か「SQLプロパティ」のいずれに対しても、常に、「1行構成（非配列）」か、「複数行構成（配列）」かどうか、をチェックする必要があります。

確認方法として簡単なのは、何らかのファイルの表をMicrosoftQueryで覗きに行って、好きなシートに結果表示します。
その、MicrosoftQueryの結果表はVBAではQueryTableオブジェクトとして扱えるようになっています。（2007以降はテーブル機能に埋め込まれたQueryTableオブジェクトとして。）
で、VBAにて、そのQueryTableオブジェクトの、「CommandTextプロパティ」や「SQLプロパティ」の値を調べます。

Excel2007以降？の場合は、例えば以下のような感じでイミディエイトウィンドウで調べてみます。
? WorkSheets("sheet1").ListObjects(1).QueryTable.SQL
? WorkSheets("sheet1").ListObjects(1).QueryTable.CommandText

Excel200③以前は以下のような感じです。（.ListObjects(1) を取ります。）
? WorkSheets("sheet1").QueryTable.SQL
? WorkSheets("sheet1").QueryTable.CommandText

で、もし出てきた結果が・・・

SELECT Sheet1$.連番, Sheet1$.顧客ID, Sheet1$.数量, Sheet1$.単価, Sheet1$.氏名
FROM D:\MSQテスト.xls.Sheet1$ Sheet1$
WHERE (Sheet1$.氏名 Like ?)

みたいな感じで、「SELECT」「FROM」「WHERE」などの句ごとに複数行で表示されたらそのプロパティは「複数行構成の Variant型の配列」だと思ってよいと思います。
なので、SQL文の指定も、複数行のコマンドとしてVariant型の配列にArrey関数などを使っておこなえます。（配列の1要素あたり数百文字までのようです。要素数の上限は試してませんが結構大丈夫みたいです。）

逆に、「SELECT」「FROM」「WHERE」が複数の行ではなく、1行で出てきたら、「１行構成のString型で指定すればOK」と考えてよいと思います。

Accessの場合やExcelでも、何らかのケースの際はそのように出てくるかもしれません。覗きに行った表のソフトの種類・ファイル形式・オブジェクト形式、などによっても変わってきます。いろんな要素で変わるので、常に最初にチェックが必要です。

テスト的に、Variant型の変数に代入してステップ実行＋ローカルウィンドウで調べても何かわかるかもしれません。

なお、たとえばそのような判別で「１行構成のString型」とわかれば、前項の(a)の形・書き方、のみが有効です。

その「文字列型の変数」に代入するSQL文が、「SELECT」「FROM」「WHERE」など、すべての句が１行に全部つなげられたものでないといけません。
でないとエラーでSQLの実行ができません。エラーの場合は、たいがい、Variant型の配列として、Arrey関数を使えば大丈夫なことが多いです。

例えば、

S_Sql_01 = "Select 電話番号"
S_Sql_02 = " FROM [氏名表$] INNER JOIN [電話番号表$]"
S_Sql_03 = " ON 氏名表$.顧客ID=電話番号表$.顧客ID"
S_Sql_04 = " WHERE 氏名表$.顧客ID=1;"

s_ALL_SQL = Arrey( S_Sql_01 ,S_Sql_02 ,S_Sql_03 ,S_Sql_04 )

みたいな感じでSQL文を設定します。

★ (a)と(b)の図示を使っての説明
(a)と(b)のことについて、文章だけだと少しわかりづらいので、図示というか、矢印テキストなどを使っても描いてみました。何かご参考になれば幸いです。

★ SQL文を書き替えるだけで、色々データの表示結果を変える例
　　
　　

★ 前述のプログラムを「関数化（部品化）」して、SQL文やファイルの場所を指定するだけで良いようにする。
　　
　　

★ 前述のプログラムを書き換えて、SQLで作った仮想表の任意のセルの値をExcelのワークシートの1つのセルに転記する方法。
　　
　　

投稿タグ: Excelの独学, Excel操作の基礎, Excel連携VBA, パソコンでの自動化, ビジネスパソコンの基礎, ピボットテーブル関連

MENU

関連記事

最近の投稿

カテゴリー

MENU

Excel2000にて表と表を紐付けするときに、VLOOKUP関数以外に、「DAO」というミドルウェアを使って、紐付いた値を、１つのセルに表示する方法

関連記事

最近の投稿

カテゴリー

タグ