Microsoft Access で２つのデータの一致・不一致を探す (VLOOKUP の代替手段)
以前に EXCEL で VLOOKUP 関数を使ってみる という記事で EXCEL の VLOOKUP 関数を使ってデータの一致・不一致を探す方法を紹介しました。今回は Microsoft Access を使って EXCEL の VLOOKUP 関数と同じように２つのデータの一致・不一致を探してみましょう。ここで利用する Microsoft Access のバージョンは 2016 です。
この記事で必要となるものは、以下の２つです。
1. 新規 Microsoft Access データベース
2. ２つの EXCEL データあるいは CSV ファイル
Microsoft Access で新規データベースを作成
それでは早速 Access を起動しましょう。起動すると以下のような画面が表示されますので、下図のように赤枠で囲まれた「空のデータベース」をクリックします。
次に下図のようなデータデータファイル名をしている画面が表示されます。そのまま作成ボタンを押下してデータベースを作成します。
すると下図のように Access データベースが利用可能な状態となります。
EXCEL ファイルを準備して、Access にデータをインポートする
一致・不一致を探すためのテストデータが必要ですので、２つの EXCEL ファイルを準備します。Access_ImportData_1.xlsx というファイルと、Access_ImportData_2.xlsx という２つのファイルを以下のような内容で準備します。
Access_ImportData_1.xlsx
Access_ImportData_2.xlsx
これら２つの EXCEL ファイルを Access データベースにインポートしていきましょう。
Access で「外部データ」タブを開き、「新しいデータソース」メニューを選び「ファイルから ..... Excel(X)」を選択します。
次に下図のようにインポートする EXCEL ファイル名を指定します。ここでは Access_ImportData_1.xlsx ファイルを指定します。そして画面中段以下の部分では「現在のデータベースの新しいテーブルにソースデータをインポートする」を選択しましょう。これにより、Access データベースに新しいテーブルが作成されて、Access_ImportData_1.xlsx ファイルの内容がインポートされます。
「OK」ボタンを押すと次のような画面になります。「先頭行をフィールド名として使う」にチェックを入れたまま、「次へ」ボタンを押して進みます。
すると次のような画面が表示され、フィールド名やデータ型、検索を高速化するためのインデックスを作成するかどうかを指定します。ここでは何も変更せずに「次へ」ボタンを押して先に進みます。
次の画面に進むと「主キー」を設定する下記画面が表示されます。「主キー」とは Primary Key と呼ばれるもので、あるレコードを必ず一意(Unique)に特定できる値を持つ列を指定するものです。今回のデータで主キーとなる列は "No" 列です。"No" 列には1から5までの一意な値が設定されており、3を指定すれば必ず１レコードのみを特定できるので、主キー(Primary Key)となります。
下記画面では「次のフィールドに主キーを設定する」にチェックを入れ、その右側のドロップダウンリストで「No」を選択します。ここで主キーを設定しておくことにより、あとで間違って同じデータをインポートした時に、重複エラーが発生してデータをインポートできませんので事故を防ぐことができます。
上図で「次へ」ボタンを押すと次の画面が表示され、Access データベースにインポートしたデータを格納するテーブル名を入力する画面になります。ここではそのまま Sheet1 というテーブル名でインポートしましょう。何も変更せずに「完了」ボタンを押します。
完了ボタンを押すことにより、EXCEL ファイルのデータが Access にインポートされました。インポートされたデータは Sheet1 というテーブルに保存されています。そのまま「閉じる」ボタンを押してデータのインポートを終了します。
Access 画面に戻ると、下図のようにテーブル一覧に Sheet1 というテーブル名が表示されています。Sheet1 のテーブル名をダブルクリックするとインポートされたデータも表示されているのが分かります。
次にもう１つの EXCEL ファイル (Access_ImportData_2.xlsx) を Access にインポートしましょう。インポート手順は全く同じです。
Access で「外部データ」タブを開き、「新しいデータソース」メニューを選び「ファイルから ..... Excel(X)」を選択します。そして下図のように Access_ImportData_2.xlsx ファイルを選択してインポートしましょう。
そしてデータのインポートは下記のように進めていきます。
下記画面では特に何も指定せずに次へボタンを押して進めます。
そして下図の主キーの設定画面では、先に述べた通り「次のフィールドに主キーを設定する」を選択して "No" 列を選び、次へボタンを押して先に進めます。
そして最後に EXCEL ファイルをインポートする Access のテーブル名を指定します。ここでは下図の赤枠の通り Sheet2 というテーブルに EXCEL ファイルをインポートしましょう。
下図のようにインポート完了画面が表示されます。インポートされたデータは Sheet2 というテーブルに保存されています。そのまま「閉じる」ボタンを押してデータのインポートを終了します。
Access 画面に戻ると、今度は Sheet2 というテーブルが画面に表示されていますので、ダブルクリックするとインポートされたデータが下図のように表示されます。
以上で２つの EXCEL ファイルを Access にインポートすることができました。
Access で２つのテーブル間のデータをクエリ・ウィザードで比較し、不一致レコードを検索する
Access には、最初から２つのテーブルを比較して不一致レコードを検索してくれる便利な機能が存在します。それが「不一致クエリ ウィザード」と呼ばれるものです。画面には「重複クエリ ウィザード」という機能もありますが、こちらは１つのテーブル内での重複データを調べる場合に使います(２つのテーブルを結合したクエリー(SQL で言えば、２つのテーブルを JOIN した結果を表示するビュー)を使えば、２つのテーブルで一致するレコードを表示することは可能です)。
これらは簡単な質問に答えていくだけで、２つのテーブル間での不一致レコードを検索できる機能です。まずはこれらを使ってみましょう。
それでは「不一致クエリ ウィザード」を使ってみましょう。下図のように「作成」タブを選択して「クエリウィザード」をクリックします。ウィザードとは、質問に答えていくだけで様々なタスクを実行してくれる便利な機能です。
すると下記のような画面が表示されますので、「不一致クエリ ウィザード」を選択して「OK」ボタンを押します。
次に比較する２つのテーブルを選択していきます。まず最初に下図の通り「テーブル: Sheet1」を選択して「次へ」ボタンを押して先に進みます。
次に下図のように、比較するテーブル名「テーブル: Sheet2」を選択して「次へ」ボタンを押して先に進みます。
今度は２つのテーブル間で比較したい列名を指定します。ここで比較したいのはくだものの名前ですので下図のように赤枠内の「全部のくだもの」と「食べたい物」を選択して「<=>」ボタンをクリックします。このボタンは選択された列を紐付けるためのものです。すると下図の緑枠内のように比較する列が関連づけられたことが分かります。そして「次へ」ボタンを押します。
今度は下図のように不一致レコードを検出した時に表示する列名(フィールド)を選択します。基本的には全て表示すべきですので、下図赤枠内の「>>」ボタンを押して全ての列名(フィールド)を右側に移します。
全ての列名を選択すると次のような画面になりますので、このようになっていることを確認した上で「次へ」ボタンを押します。
最後に下図のように比較結果が "クエリ" として保存されます。これにより比較結果が Access データベースに保存されますので、あとでいつでも不一致レコードを確認することが出来ます。このまま「完了」ボタンを押しましょう。
すると下図のように「Sheet1とSheet2との差分」という画面が表示され、２つのテーブル間での不一致レコードのみが表示されます。また不一致結果は画面左側の「Sheet1とSheet2との差分」というクエリに保存されています。
このようにして Access データベースのウィザード機能を利用して不一致レコードのみを検索することが可能です。
しかし一致するレコードのみ検索したい、もっと複雑な条件で比較したい場合には、次に述べる SQL を使って比較する必要があります。
Access で２つのテーブル間のデータを SQL で比較し、一致・不一致レコードを検索する
今度は Access データベースに備わっているウィザードを使わずに、SQL を使って柔軟に一致・不一致レコードを検索してみましょう。Access データベースで SQL を使うには、以下のような手順で SQL を作成する画面を表示することが出来ます。
まず最初に「作成」タブを選択して「クエリデザイン」という下図赤枠内のボタンをクリックします。
「クエリデザイン」ボタンをクリックすると次のような画面が表示されますので、何もせずにそのまま「閉じる」ボタンを押します。
すると以下のような画面になりますので、下図のように何もない部分でマウスを右クリックしてメニューを表示し、「SQL ビュー」を選択します(下図の赤枠内)。
すると次のような画面が表示されます。この画面には最初から "SELECT;" という SQL の一部が表示されていますが、これは消してしまいましょう。
一致するレコードのみを表示するために、以下のような SQL を書きます。
SELECT s1.全部のくだもの FROM Sheet1 s1 INNER JOIN Sheet2 s2 ON (s1.全部のくだもの = s2.食べたい物) ;
下図のように SQL を書いた状態で、下図青枠内の「実行」ボタンをクリックします。すると SQL が実行されます。なお Access では内部結合する場合には JOIN だけではエラーになりますので、INNER JOIN と書く必要があります。
注）本来はテーブル名や列名に日本語を使うべきではありませんが、ここでは説明のために日本語列名を使っています。
「実行」ボタンを押すことにより、以下のように Sheet1 テーブルと Sheet2 テーブルで一致するレコードのみが SQL で抽出されます。
最後にこの SQL を保存しておきましょう。下図のように閉じるボタン「x」を押すと、画面中央のようにクエリを保存するかどうか尋ねられますので「はい」をクリックして保存しましょう。
すると下図の通り保存するクエリに名前を付けるかどうか尋ねられますので、今回はそのまま「クエリ1」という名前で保存します。「OK」ボタンをクリックしましょう。これでクエリが Access データベースに保存されます。
次に２つのテーブル間の不一致レコードのみを抽出してみましょう。再度「クエリデザイン」から新しいクエリ作成して下記の SQL を書きましょう。
SELECT s1.全部のくだもの FROM Sheet1 s1 LEFT OUTER JOIN Sheet2 s2 ON (s1.全部のくだもの = s2.食べたい物) WHERE 1 = 1 AND s2.食べたい物 IS NULL ;
これでまた「実行」ボタンをクリックすることにより、以下のように不一致レコードのみが表示されます。
その他にも複雑な条件を組み合わせた SQL を書くことが出来ます。
まとめ
このようにして Access に備わっているウィザードを使って不一致データを簡単に探したり、Access に EXCEL データを取り込んで SQL を書くことにより、非常に柔軟にデータを集計・差分抽出などを行うことが出来ます。また扱うデータ量が多い場合、EXCEL よりもパフォーマンス面で圧倒的に高速ですので、Access を活用していきましょう。
