エクセルの神髄<br />SQL入門：VBAでデータベースを使う
エクセルの神髄<br />SQL入門：VBAでデータベースを使う

エクセルの神髄SQL入門：VBAでデータベースを使う

Excelで扱うデータ量が増え、「動作が重い」「管理が複雑」といった限界を感じていませんか？ 本シリーズでは、その問題を解決するために、ExcelのVBAから外部データベース（DB）を操作する方法を解説します。軽量DBのSQLiteを例に、ADOを使った接続や、データの操作・管理を行うためのSQLの基本構文を、

社会的にパソコンで扱うデータ量は近年急激に増えています。 これに呼応してエクセルも2003までは65536行まででしたが、2007から飛躍的に増えて1048576行となっています。 しかしエクセルで100万行扱えるといっても、データ量としては列数もありますので、 実際には100万行はおろか数十万行でもエクセルが重くなって扱いづらくなってしまいます。 このような場合、ブックを分割する等である程度は対応可能な場合もありますが、 境目のデータ取得等の問題点もあり、なかなかスムーズには扱えなくなります。

エクセルにはテーブル機能があり、まさしくデータベースとして扱えるようになっていますが、あくまでエクセルのデータでしかありませんし、データ件数の制限があります。 ユーザーが直接触れる部分にデータが存在することで、簡単に扱えるメリットはありますが、それに伴うデメリットも多くあります。 また、Power Queryでは外部データベースからデータを取得できますが、取得するだけになります。

本シリーズでは、 エクセルのマクロVBAから外部データベースを扱う方法について解説していきます。 使用するDBはSQLiteを使いますが、SQLiteの説明は環境作成にとどめ、SQLの説明を中心に進めます。 SQLiteについては必要な部分のみの説明となりますので、詳細を知りたい場合は別途お調べください。

DBとはSQLとは DBとは

データベース(DataBase)の略になります。 データベースとは、複数のデータが集まっている、まさにデータの基地です。 そこに行けば、必要なデータが全て揃うように、関係するデータを一か所に集めたものになります。

コンピュータでデータベースを運用、管理するためのシステムを、 データベース管理システム（DataBase Management System、略してDBMS） と呼びます。 RDBMSとは

本シリーズでは、 現在主流となっているリレーショナルデータベース管理システム(Relational DataBase Management System、略してRDBMS)の中から、SQLiteを使います。

SQLiteは、データの保存に単一のファイルのみを使用することが特徴となっています。 そしてインストールも簡単ですし、非常にコンパクトなため扱いやすいDBです。

リレーショナルデータベースとは、 いろいろな説明がなされますが、単純にエクセルのテーブルを思い浮かべてもらえれば良いでしょう。 横に項目が並んでいて、縦に1行1件のデータとなっているものです。 そして複数のテーブルがありテーブルとテーブルの関係性は、 IDや主キーとなる項目によって、データ同士を関連付けることができるようになっているテーブルの集まりです。

SQLとは 「structured query language」ですが、正式にはこの略称ではないらしいです。 日本語としては、「構造化問い合わせ言語」になります。

SQLは、データベースの定義や表の操作を行う言語です。 データ定義言語：DDL(data description language) データ操作言語：DML(data manipulation language) データ制御言語：ＤＣＬ(Data Control Language) これらに分けられます。 さらに、トランザクション制御言語：TCL(Transaction Control Language)を区別する場合もあるようですが、コンシリーズでは特に区別せずに進めます。

DDLはデータベースの定義を行うためのSQLですので、当初のテーブル作成で扱います DMLはデータベースに対してデータの操作を行うためのSQLで、データの抽出や更新、追加、削除を行います。 DMLが本シリーズの主なテーマとなります。 ＤＣＬについては扱う予定はありません。

エクセルでは、テーブル等の表からデータを取得する場合、 SUMIF関数、COUNTIF関数、VLOOKUP関数・・・ このような関数を使っているはずですが、 SQLを使う事で、DBからデータ取得する時に、エクセルでこれらの関数を使う時と同様の考え方でデータを取得することができます。 したがって、もし上記関数について自信が無い場合は、これらの関数を先に習得してお区ことをお勧めします。

SQL入門の目次 SQLiteのインストール

・SQLiteのダウンロード ・SQLiteのインストール ・データベースの作成 ・GUIツールも欲しい ・VBAからADOで使えるようにODBCドライバーを入れる ・SQLiteのインストールの最後に

データベースに接続/切断

・ADOとは ・ADOを使う準備 ・SQLiteに接続/切断 ・データベースに接続できたかの確認 ・他のデータベースに接続する場合の指定 ・今後のためにクラス化しておく ・データベースに接続/切断の最後に

テーブルの作成/削除（CREATE TABLE,DROP TABLE）

・データベースで使う用語や記号について ・カラムのデータ型 ・テーブル作成：CREATE TABLE ・テーブル削除：DROP TABLE ・ADODB.ConnectionのExecuteメソッド ・クラスを拡張修正 ・VBAでADOを使う為のクラスの全VBA ・テーブルの作成/削除の最後に

テーブル名変更と列追加（ALTER TABLE）とテーブル自動作成

・テーブル名変更：ALTER TABLE RENAME TO ・カラム（列）追加：ALTER TABLE ADD COLUMN ・SQLの半角空白と改行とセミコロン ・テーブル自動作成 ・テーブル名変更と列追加とテーブル自動作成の最後に

データの挿入（INSERT）と全削除

・テスト用のテーブル作成 ・データ挿入：INSERT INTO ・テストデータをシートで作成 ・INSERT INTOのサンプルVBA ・より速くINSERT INTOを処理するために ・テーブルの全削除 ・データを挿入の最後に

VBAクラスの全コード：データの挿入 SQL入門の「データの挿入（INSERT）と全削除」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール：clsSQLite データの挿入：バルクインサート

・テスト用のテーブル作成 ・VBAの改善点の概要 ・バルクインサートのSQL解説 ・バルクインサートのVBAコードと動作検証 ・文字列操作を改善する方法とVBAコード ・標準モジュールでの使い方と、100万行の実行結果 ・バルクインサートの最後に

データの取得：条件指定（SELECT,WHERE）

・テスト用のテーブル作成 ・データの取得：SELECTの構文 ・クラスにデータ取得用のメソッドを追加 ・シートにある見出しを使ってカラム名リストを作成 ・標準モジュールの使用例 ・WHERE句のいろいろな条件指定方法 ・データの取得：条件指定の最後に

VBAクラスの全コード：データの取得 SQL入門の「データの取得：条件指定（SELECT,WHERE）」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール：clsSQLite データの取得：集約集計、並べ替え（DISTINCT,GROUP,HAVING,ORDER）

・データの取得：SELECTの構文 ・テスト用のテーブルとVBAコード ・DISTINCT句 ・GROUP BY句 ・HAVING句 ・ORDER BY句 ・VBAでのSQL使用例 ・列名（カラム名）の別名（エイリアス）をつけるAS句 ・データの取得：条件指定の最後に

SQL関数と演算子 ・集計/数値関数 ・文字列関数 ・日付時刻関数 ・その他の関数 ・演算子 ・CASE演算子 ・SQL関数と演算子の最後に データベースにおけるNULLの扱い方 ・NULLデータのINSERT ・NULLに関する演算子とSQL関数 ・文字列結合におけるNULLの挙動 ・集計関数におけるNULLの挙動 ・NULLの扱い方の最後に データベースの正規化とマスタの作成 ・データベース正規化とは ・マスタデータとトランザクションデータ ・正規化したテーブル定義 ・非正規化から正規化したテーブルを作成 ・データベース正規化の最後に 全テーブル定義とテーブル自動作成VBA ・テーブル：m_customer ・テーブル：m_item ・テーブル：t_sales ・テーブル自動作成 テーブルを結合して取得（INNER JOIN,OUTER JOIN）

・テーブルの結合方法 ・列名修飾とエイリアス ・内部結合：INNER JOIN ・左外部結合：LEFT OUTER JOIN ・右外部結合：RIGHT OUTER JOIN ・外部結合（OUTER JOIN）で結合条件に一致する行が無い場合 ・複数のJOINを組み合わせた使用例 ・JOINを使わない内部結合 ・テーブルを結合して取得の最後に

複数のSELECT結果を統合（UNION,UNION ALL） ・集合演算：データの統合方法 ・SQLの集合演算について ・和集合：UNION ・全体集合：UNION ALL ・複数のSELECT結果を統合の最後に データの更新（UPDATE） ・UPDATATEの構文 ・UPDATEの使用例 ・SELECTで取得→シートで編集→UPDATEで更新 ・データの更新（UPDATE）の最後に データの削除（DELETE） ・DELETEの構文 ・DELETEの使用例 ・SELECTで取得→シートで削除行を指定→DELETEで削除 ・データの削除（DELETE）の最後に 他のテーブルのデータで追加/更新/削除 ・新規テーブルを追加 ・他のテーブルを基にデータを追加 ・他のテーブルを基にデータを更新 ・他のテーブルを基にデータを削除 ・他のテーブルを基にデータを追加/更新/削除の最後に インデックスを作成して高速化（CREATE INDEX） ・インデックスとは ・インデックスを作成 ・UNIQUEインデックスを作成 ・インデックスを削除 ・インデックスを作製の最後に トランザクション処理 ・トランザクション処理とは ・ADOでのトランザクション処理 ・VBAクラスの全コード ・トランザクション処理の使用例 ・トランザクション処理の最後に VBAクラスの全コード：トランザクション処理

SQL入門の「トランザクション処理」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール：clsSQLite SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説。

サブクエリ（副問合せ）

・サブクエリ（副問合せ）とは ・FROM句でサブクエリを使う ・WHERE句でサブクエリを使う ・SELECTのカラムにサブクエリを使う ・UPDATAEでサブクエリを使う ・DELETEでサブクエリを使う ・サブクエリ（副問合せ）の最後に

サブクエリのネストとSQLコメント&整形 ・サブクエリのネスト ・サブクエリのネストの組み方 ・SQLのコメントについて ・SQLの整形 ・サブクエリのネストとSQLコメント&整形の最後に WITH句（共通テーブル式）

・WITH句の構文 ・WITH句の最も簡単な使用例 ・WITH句に複数のサブクエリを使用する例 ・WITH句を使った再帰SQL ・CASE演算子を使ったマトリックス作成のSQL ・WIYH句（共通テーブル式）の最後に

取得行数を限定するLIMIT句とOFFSET句 ・LIMIT句の構文 ・LIMIT句の使用例 ・LIMIT句、OFFSET句の最後に 分析関数（OVER句,WINDOW句）

・分析関数とは ・OVER句 ・WINDOW句 ・FILTER句 ・分析関数の使用例 ・分析関数の使用例：グループの最大/最小/平均を明細に出力 ・分析関数の使用例：FILTER句で条件指定 ・分析関数の使用例：明細にレコード番号（連番）を振る ・分析関数の使用例：前回販売日を取得 ・RANK関数,DENSE_RANK関数 ・LAG関数,LEAD関数 ・分析関数の最後に

SQL基礎問題の目次 「ADO + VBA」でSQLを実行するときのVBAサンプル ・ADOとユーザー定義関数について ・ADO + Excel ・ADO + SQLite3 SQL基礎問題1：最大在庫数を持つ製品の在庫金額 SQL基礎問題2：文字列「-nn-」のnnが偶数のみ抽出 SQL基礎問題3：文字列の一部をキーにして集計 SQL基礎問題4：2つのテーブルの不一致を抽出 SQL基礎問題5：複数のマスタテーブルの結合 SQL基礎問題6：成績表から教科ごとの点数ベスト3を抽出

成績表テーブルから各教科ごとの点数ベスト3を抽出。SQLの練習用になるべく単純なSQLで済む問題を出します。SQLの練習として取り組んで見てください。問題 ※画像はExcelです。【SQL問題】※難易度は🤔 TBL成績：画像参照 ・各教科ごとに、点数順位が3位以内のデータを抽出する。

SQL基礎問題7：成績表から各教科の最高点と最低点を抽出 SQL基礎問題8：バスケット分析・ペア商品の出現回数 SQL基礎問題9：特定商品購入者の平均購入金額 SQL基礎問題10：非正規化（カンマ区切り）の結合と集計 SQL基礎問題11：連続期間の開始月と終了月を抽出 SQLを使った既存サンプル VBAでのSQLの基礎（SQL:Structured Query Language） ・SQL文 ・SELECT文 ・SQLの学習について ・実践例 ADO(ActiveX Data Objects)の使い方の要点

・データベースの種類 ・SQL（SQL:Structured Query Language） ・ADOを使う準備 ・ADOでのDB接続方法 ・ADODB.Recordsetの取得方法 ・ADODBのレコードセットの扱い方 ・ADODBのトランザクション処理 ・ADODB.Commandの使い方 ・VBA100本ノックでの実践例 ・最後に注意点等

ADOでマスタ付加と集計（SQL） ADOでマスタ更新（SQL） ADOでCSV読み込み（SQL）

・CSVテストデータ ・ADOでCSV読込のVBA ・ADO使用時の注意点 ・ADOレコードセットをCSV出力 ・ADOでTSVの読み込み ・ADOでCSVの読み込みについて ・本サイトにあるCSV関連記事一覧

新着記事 NEW ・・・新着記事一覧を見る アクセスランキング ・・・ ランキング一覧を見る このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。 掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。 本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。 This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.