※間違ってたらすみません。※メモ書きなので、自分でも意味不明な箇所も多いです。あと、本記事は「32bitの」Excelだけでのお話です。OSが64bitであっても、インストールされているExcelが32bitなら大丈夫ではないかと思います。確認」などの語句でWeb検索してみてください。前述のプログラムを「関数化(部品化)」して、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アドインのインストが必要。リレーション(=SQL文)を使う(04)DAOやADOなどのミドルウェア+SQL文を使う・・・の4つくらいがあると思いますが、本記事は、そのうちの(04)のことになります。以外は、2000以降の全てのバージョンでやれます。)これは閉じたxlsファイルの値も(そのxlsファイルを1つのデータベースとみたてて)引っ張ってこれますし、相手がExcelファイルでも、AccessのmdbファイルやMySQLなどでもODBCドライバさえあれば引っ張ってこれるのでので、それなりに便利な場面もあるかと思います。基本、ExcelやAccess、SQLServerのODBCドライバは最初から入っているので、大抵は特に何もしなくても大丈夫です(あってもインストールCDやDVDを使っての機能追加のインストールとか)。MySQLやオラクル、PostgreのODBCドライバは別途にダウンロードしてインストールします。いずれかのソフトのバージョンがあまりにも古かったり新しかったりだとダメかもしれませんが、すべてのソフトのバージョンが同じ時期のものなら問題ないと思います。※★参考:「表と表の紐付け」について「表と表の紐付け」については、ぱっと思いつくだけで以下のような方法があります。MicrosoftQuery、ADO、DAO、では、VLOOKUP関数同様、2枚の表だけでなく、3枚、4枚と、紐付けさせられます。AccessやMySQL、SQLServerなどのリレーショナルデータベースのテーブルを読みに行って、そのなかですでに紐付け完了後の表を読み込んだり、あるいは、複数の「単体の表」を読み込んで、それらを紐付けさせるということも可能です。すべて、VLOOKUP関数同様、2枚の表だけでなく、3枚、4枚と、紐付けさせられます。AccessやMySQL、SQLServerなどのリレーショナルデータベースのテーブルを読みにも行けます。PowerPivotでリレーションを作ると、ピボットテーブルの集計フィールドやグループ化が使えなくなるようです。これはこれでデメリットですね。不勉強なので詳細がわかりません。間違いもあるかもです。基本、動作が遅いです。操作対象がクラウドデータだともっと遅くなるかもしれません。VBAでの自動化は「QueryTable+Offset名前定義+PivotTalbe」ほど簡単そうではなく、結構難しそうです・・・。SQLは使えないっぽいですが、ただ、クラウド対応にとても期待ができるそうです。グラフ連携などのビジュアル系での見える化にも期待が持てるかもしれません。データを多種多様な加工しながら表のマージができるので、SQLではできないこともできる側面があります。便利な面も多いかと思います。オラクルのビューでドラッグを使う」といった方法があります。難易度等についてまた、本記事の処理は、「プログラミングでやるかたち」なので、何やら面倒・難易度が高くに感じるかもしれませんが、でも、SQL文を多少でも使える方なら(あるいは勉強してもいいなら)、プログラムを1つ作ってしまえばあとはデータを見に行くxlsファイルのフルパスの指定を変えたり、SQL文を書き替えて色々対応すればいいだけなので(他は書き換えなくてもとりあえずは使えるので)、かえってラクになる、ということもあります。(ほかの部分も書き換えをするにしても、どのセルに書き出すかを変えるだけのことが多いです。)SQL文は横に長く書くと意味不明のちんぷんかんぷんですが、でも実は、カンマや空白スペースごとになら改行してもエラーにならないので、テーブル名と列名をメインに使って縦方向に構造的に書きかえることができます。それが結構(意外と)見やすくて、かつ、それほど難しくないものも少なくないです。あっても、結構なことが・意外とかなりなことが」できます。(条件別の集計やリストのピックアップなどにおいて。しやすい」ということなので、クリックで設定したリする手間が省ける、ということもあります。複数の表を結合する際も、横の結合だけでなく、縦の結合もできますし・・・。(これはDAOやADOでもできます。)なお、今回の例では紐付けで引っ張ってきた値の表示を、シート3の「A2」セルを起点に横方向に自動的に行っていますので、プログラムのなかで「SELECT(SELECT句と呼びます)」のところで指定した列(=呼び出した列)が左から順に自動的に並んで表示されます。別の命令を使えば、どこか適当なバラバラな1セルずつ、に、引っ張ってきたデータを入力することもできます。A1セルには引っ張ってきたデータ(例えば1レコード=1行分)のうちの「姓」だけを表示したリ、C3セルには「顧客ID」、F10セルには「電話番号」を・・・、といった具合にバラバラに飛び飛びに値を表示させることも可能です。どこかのセルに、「姓」と「名」をスペースで結合したものを入れたり、すべての列の値を全角スラッシュで結合したものを入れたり、ということもできます。数式でセルに値を表示するわけではなく「生の実データが自動入力されます」ので、「数式を破壊されて他の関連セルにも影響が出る・・・」、といった不都合も起こりません。(消されたり直されたりしたらどうしようもないですけど・・・。されない限りは、不都合がおこりません。VLOOKUP関数を使ったほうが早いケースとはいうものの・・・基本・そういうことをするなら(飛び飛びの1セルに引っ張ってきたデータを入れたい場合は)、こんな「DAO」なんて使わずに普通にVLOOKUP関数を使うほうがいい場合が多いと思います。VLOOKUP関数のほうが断然便利です。セルが連続する大きな表でVLOOKUP関数を使うのでなけば、例えば「初心者の方がうっかりの操作ミスで数式を破壊する」というようなこともほとんど起こらないでしょうし・・・。開いているxlsの中でやるなら、VLOOKUP関数のほうが良ければそちらを使えば全然早いことのほうが多いので、VLOOKUP関数でオッケーかと思います。Queryを使うほうが良いとは思います。・・・というわけで・・・、では、以下、簡易的な説明です。Excel2000での説明です。(細かいところまで書けてないのですみません!)今回は、古いExcelでも使えるようにと思って、DAOのほうを使いました。(ADOのほうはWebに沢山事例があると思います。join」という検索語句で是非調べてみてください。)以下のような流れになります。(01)Dドライブのルートに、空の「DAOテスト02.xls」を作成します。(ルート→フォルダを作らずに、Dドライブに直接・・・という意味です。)(02)シート1を「氏名表」にリネームして、以下のようなデータを入力します。(リネーム→名前の変更をするという意味です。)(03)シートを1つ増やして、シート4を、「電話番号表」にリネームして、以下のようなデータを入力します。(05)VBEが開くので、ツール→参照設定を押します。Library」を探してチェックを入れ、OKします。これをやらないと、本記事のプログラムはエラーが出て動きません。(06)「挿入→標準モジュール」で標準モジュールをひとつ、挿入します。(07)そこに、以下のコードをまんまでコピペします。チェックを入れておく。'※本記事では使わないコードなのでコメントアウトしています。'「Sheet1」をシステムテーブルとして読み込む。'「$」を付けるとシステムテーブルになる。※本記事では使わないコードなのでコメントアウトしています。→「IMEX」の値を「=1」にしたときは、1行目を列名として読込む。→「IMEX」の値を「=1」にしたときは、1行目を列名として読込まない。'IMEX=1→表示されているセル書式の値でデータを吸い込む。(今回のテストでは完全にはそうはなりませんでした。バージョン番号」でGoogle検索。8.0で2000でも2010でも動いた。→1行目(列名)を読込む。」設定にするとエラーになります。'「電話番号」の列だけを抜き出せ、という命令です。'どんな種類のリレーション(=VLOOKUP関数のような紐付け)をするかの設定です。'リレーションを「氏名表」の「顧客ID」の列と、「電話番号表」の「顧客ID」の列で、それぞれ同じ値同士で紐付けしますよ・くっつけ合いますよ、という設定。'そのうえで、氏名表の顧客IDが「1」の行を抜き出す対象にする設定。この設定のうえで、「SELECT」に書かれた列名の列に絞り込まれます。値を取得していくことになります。」は「内部結合」といって、VLOOKUPと同じような意味の命令です。小学か中学で習った「ベン図」の2つの円が交わるエリアを求めるようなイメージの命令です。それぞれのエリアの部分も求めることもできます。いう感じ・イメージで求めることができます。どの種類の結合方法をとりますか?という意味になります。'レコードセットを定義(仮想表をメモリ上に作成・・・みたいな感じ。SQL文を使って定義しています。※本記事では使わないコードなのでコメントアウトしています。チェックを入れておく。'※本記事では使わないコードなのでコメントアウトしています。'「Sheet1」をシステムテーブルとして読み込む。'「$」を付けるとシステムテーブルになる。※本記事では使わないコードなのでコメントアウトしています。→「IMEX」の値を「=1」にしたときは、1行目を列名として読込む。→「IMEX」の値を「=1」にしたときは、1行目を列名として読込まない。(どうやら数値は数値、文字列は文字列として読み込まれるようです)'IMEX=1→表示されているセル書式の値でデータを吸い込む。(今回のテストでは完全にはそうはなりませんでした。バージョン番号」でGoogle検索。8.0で2000でも2010でも動いた。→1行目(列名)を読込む。」設定にするとエラーになります。'「電話番号」の列だけを抜き出せ、という命令です。'どんな種類のリレーション(=VLOOKUP関数のような紐付け)をするかの設定です。'リレーションを「氏名表」の「顧客ID」の列と、「電話番号表」の「顧客ID」の列で、それぞれ同じ値同士で紐付けしますよ・くっつけ合いますよ、という設定。'そのうえで、氏名表の顧客IDが「1」の行を抜き出す対象にする設定。この設定のうえで、「SELECT」に書かれた列名の列に絞り込まれます。値を取得していくことになります。」は「内部結合」といって、VLOOKUPと同じような意味の命令です。小学か中学で習った「ベン図」の2つの円が交わるエリアを求めるようなイメージの命令です。それぞれのエリアの部分も求めることもできます。いう感じ・イメージで求めることができます。どの種類の結合方法をとりますか?という意味になります。'レコードセットを定義(仮想表をメモリ上に作成・・・みたいな感じ。SQL文を使って定義しています。※本記事では使わないコードなのでコメントアウトしています。Sub''(08)実行してみて、シート3のA2セルに、顧客ID「1」の人の電話番号が表示されればOKです。※実行するには、コピペしたプログラムの中のどこかの行をクリックしてアクティブにし、「実行→Sub/ユーザーフォームの実行」を押します。氏名表$.顧客ID=1"」の行の「顧客ID=1」を「顧客ID=2」に変えて実行すると、探しに行く行が2行目(顧客IDが「2」の行)になるので佐藤さんの電話番号が出てきます。電話番号"」の行の「電話番号」を「*」に書き換えて実行すると、紐付けさせた行(ここでは顧客IDが「1」の行のみ)のすべての列のデータが出ます。A2セルを起点に、すべての列の値が表示されます。(「*」は「すべての列」という意味なので。)この場合、「氏名表」と「電話番号表」の2つの表が、両方の中に在る「顧客ID」という列を経由して紐付けされますので、まずは、「両方の表を顧客ID列を介して横に合体(結合)させた表」ができあがります。そこから、WHEREによって顧客IDが1番の「武田」さんのデータに絞り込まれ、それが表示されます。以降を全部とっぱらって実行すると、すべての行のすべての列のデータがA2セルを起点に表示されます。この場合も前項と同じく、「氏名表」と「電話番号表」の2つの表が、両方の中に在る「顧客ID」という列を経由して紐付けされますので、まずは、「両方の表を顧客ID列を介して横に合体(結合)させた表」ができあがります。そこから、3名全員のデータが表示されます。全部とっぱらう」ということは、「行を絞り込むための条件が消えて無くなる」ということですので、3名すべてのデータが」表示されます。Serverのビュー、などのSQLの「管理画面」で書く(許される)「SQL文の形・書き方」についてご説明します。本記事のSQL文を使ってご説明いたします。このことは、Excelの「VBAの画面(VBEditorの画面)」での場合と似ていますが少し異なりますのでご注意ください。区別がつかないとエラーになってしまうことが多いので、ここではその意味で、あえて、ご説明させていただきます。結論から先に言うと、各ソフトのSQLの管理画面では、以降の(a)のように全部の命令を半角スペースでつなげて1行にしてしまってもいいし、(b)のように、ちょっと見やすく、意味毎に改行しても大丈夫です。すべての命令や式が、半角スペース区切りで1行につながっています。(右端の隠れた部分は分をクリックすると下に左右のスクロールバーが出ますのでそれを右にずらしてみてみてください。・「WHERE(=表の中からどの行を抜き出すかの、そのための条件を指定して!)」といった大きな意味毎に、改行しています。1行に全部をつなげてしまうよりは、少し、意味がわかりやすいと思います。「ON」のところで改行してあるのは、FROMのところが横に長くなってしまうので、「まあ区切りのいいところで適当に改行した・・・」ということで深い意味はありません。JOIN」のところで改行する場合も多いです。Queryや、Accessのクエリなど、それぞれの管理画面で「実行ボタン」を押すと、このSQL文の内容が実行され、仮想表が表示されます。ビューやオラクル、MySQLなど、その他のデータベースシステムの管理画面でも同様だったかと思います。ビューなど、それぞれの「管理画面」では、(b)のように改行されていても、上手いことに「改行コードが存在している」とは判断していません。「改行されていても、改行コードが無いものとして、自動的に1行に全部つなげ直してくれて、SQL文を実行している」というイメージです。(逆に、VBAプログラムでSQLを扱う場合は、1行につなげるか・分けるなら大抵配列を使わないといけないんですけど・・・。各種機能の「管理画面」のほうでは、改行してもスペースが多くてもオッケーなのです。)1行に全部つなげ直してSQL文を実行している感じなので、各行の先頭か末尾に半角のスペースが必要です。改行ごとに先頭か末尾に半角のスペースが無い場合は、列の名前と「FROMやWHEREなど」のSQLコマンドがくっついてしまうことになるので、列の名前やSQLコマンドとしての判別ができなくなってしまいます。ここはご注意ください。JOIN」などの「句」とか「コマンド」とか呼ばれる「命令語句」ごとに「半角スペース」が存在しています。「すべての命令語句と式(=で結ばれている個所など)が、半角スペースで結合されている・・・」という感じです。決められた命令語句のうち、何が「句」で、何が「コマンド」かの区別は僕自身よく分かっていません。私たち素人はあまり高度なことはしないのでどっちでも大丈夫です。JOIN」や「ON」などは「句」と呼ぶサイトもあれば「コマンド、SQLコマンド」などと呼ぶサイトもあります。(「句」とか「コマンド」とかで称さないサイトもいっぱいあります。」などの大きなくくりに位置する命令語句を「句」と呼び、よくわからないもう少し細かいことをする命令たちを「コマンド」と呼ぶことにします。【(b)の場合の補足説明】(b)の場合、大きな意味毎に改行されています。『「句」ごとに改行されている』ともいえます。ここでも、各「句」や「コマンド」ごとに半角スペース(複数個可)が存在します。「句」や「コマンド」ごとに半角スペースが要るので、当然、改行ごとにも半角スペースがどこかに要ります。半角スペース(複数個可)が存在すればオッケーです。少なくとも改行の最後か最初のいずれかに必要です。複数個の半角スペースがあっても大丈夫なので改行の最初と最後の両方にあっても別にオッケーです。全角スペースだとエラーになりますので注意が必要です。空白行を作ってしまった場合もエラーになるかもしれません(未確認です。)(b)の場合は以下のように書いてもいいんですが、「FROM」句が横に長くなるので、「ON」コマンドのところでも改行してしましました。氏名表$.顧客ID=1;''どこで改行するかや半角スペースを何個入れるかは、基本、自分の好き勝手です。社内のきまりに従います。ちなみにですが、(b)のケースは、VBAプログラムではエラーになりますので書き方を変えないといけません。そのことについては、後述します。間違っていたので修正オブジェクトによってや、Accessのバージョン、Excelのバージョン、使用するプロパティなどによって、変わります。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.CommandTextExcel2003以前は以下のような感じです。Variant型の配列」だと思ってよいと思います。SQL文の指定も、複数行のコマンドとしてVariant型の配列にArrey関数などを使っておこなえます。(配列の1要素あたり数百文字までのようです。要素数の上限は試してませんが結構大丈夫みたいです。)逆に、「SELECT」「FROM」「WHERE」が複数の行ではなく、1行で出てきたら、「1行構成のString型で指定すればOK」と考えてよいと思います。Accessの場合やExcelでも、何らかのケースの際はそのように出てくるかもしれません。覗きに行った表のソフトの種類・ファイル形式・オブジェクト形式、などによっても変わってきます。いろんな要素で変わるので、常に最初にチェックが必要です。テスト的に、Variant型の変数に代入してステップ実行+ローカルウィンドウで調べても何かわかるかもしれません。そのような判別で「1行構成のString型」とわかれば、前項の(a)の形・書き方、のみが有効です。1行に全部つなげられたものでないといけません。ないとエラーでSQLの実行ができません。エラーの場合は、たいがい、Variant型の配列として、Arrey関数を使えば大丈夫なことが多いです。)みたいな感じでSQL文を設定します。(a)と(b)の図示を使っての説明(a)と(b)のことについて、文章だけだと少しわかりづらいので、図示というか、矢印テキストなどを使っても描いてみました。何かご参考になれば幸いです。前述のプログラムを「関数化(部品化)」して、SQL文やファイルの場所を指定するだけで良いようにする。前述のプログラムを書き換えて、SQLで作った仮想表の任意のセルの値をExcelのワークシートの1つのセルに転記する方法。「安全」な、Youtube動画のダウンロード方法6つ。挫折するオブジェクト式やプログラム(プロシージャ)の書き方、挫折しない書き方、の違い。表と表を紐付けするときに、Excel2013以降のリレーションシップの機能、MicrosoftQueryのリレーション、VLOOKUP関数、DAOやADO、どれを使うといいか?マイクロソフトさんは愚かなので、「VLOOKUP関数は古いやり方」と言っています。「安全」な、Youtube動画のダウンロード方法6つ。挫折するオブジェクト式やプログラム(プロシージャ)の書き方、挫折しない書き方、の違い。送信メールが受信トレイに入ってしまうトラブルの回避、など。ODBCでQueryTableオブジェクトを自動作成するサンプルコード)。2000以降の全バージョンで使えると思います。~ついでに、セルの値で結果表示を自動で切り替える「パラメータクエリ」のVBAでの自動作成も。technology.。