Microsoft Access で2つのデータの一致・不一致を探す (VLOOKUP の代替手段)

Microsoft Access で2つのデータの一致・不一致を探す (VLOOKUP の代替手段)

以前に EXCEL で VLOOKUP 関数を使ってみる という記事で EXCEL の VLOOKUP 関数を使ってデータの一致・不一致を探す方法を紹介しました。

今回は Microsoft Access を使って EXCEL の VLOOKUP 関数と同じように2つのデータの一致・不一致を探してみましょう。ここで利用する Microsoft Access のバージョンは 2016 です。

この記事で必要となるものは、以下の2つです。

1. 新規 Microsoft Access データベース
2. 2つの EXCEL データあるいは CSV ファイル


Microsoft Access で新規データベースを作成

それでは早速 Access を起動しましょう。起動すると以下のような画面が表示されますので、下図のように赤枠で囲まれた「空のデータベース」をクリックします。

次に下図のようなデータデータファイル名をしている画面が表示されます。そのまま作成ボタンを押下してデータベースを作成します。

すると下図のように Access データベースが利用可能な状態となります。

EXCEL ファイルを準備して、Access にデータをインポートする

一致・不一致を探すためのテストデータが必要ですので、2つの EXCEL ファイルを準備します。Access_ImportData_1.xlsx というファイルと、Access_ImportData_2.xlsx という2つのファイルを以下のような内容で準備します。

Access_ImportData_1.xlsx

Access_ImportData_2.xlsx

これら2つの EXCEL ファイルを Access データベースにインポートしていきましょう。

Access で「外部データ」タブを開き、「新しいデータソース」メニューを選び「ファイルから ..... Excel(X)」を選択します。

次に下図のようにインポートする EXCEL ファイル名を指定します。ここでは Access_ImportData_1.xlsx ファイルを指定します。そして画面中段以下の部分では「現在のデータベースの新しいテーブルにソースデータをインポートする」を選択しましょう。これにより、Access データベースに新しいテーブルが作成されて、Access_ImportData_1.xlsx ファイルの内容がインポートされます。

「OK」ボタンを押すと次のような画面になります。「先頭行をフィールド名として使う」にチェックを入れたまま、「次へ」ボタンを押して進みます。

すると次のような画面が表示され、フィールド名やデータ型、検索を高速化するためのインデックスを作成するかどうかを指定します。ここでは何も変更せずに「次へ」ボタンを押して先に進みます。

次の画面に進むと「主キー」を設定する下記画面が表示されます。「主キー」とは Primary Key と呼ばれるもので、あるレコードを必ず一意(Unique)に特定できる値を持つ列を指定するものです。今回のデータで主キーとなる列は "No" 列です。"No" 列には1から5までの一意な値が設定されており、3を指定すれば必ず1レコードのみを特定できるので、主キー(Primary Key)となります。

下記画面では「次のフィールドに主キーを設定する」にチェックを入れ、その右側のドロップダウンリストで「No」を選択します。ここで主キーを設定しておくことにより、あとで間違って同じデータをインポートした時に、重複エラーが発生してデータをインポートできませんので事故を防ぐことができます。
インポートするデータによっては、値を一意に特定することが難しい場合もありますので、不明な場合には「主キーを設定しない」を選択して問題ありません。

上図で「次へ」ボタンを押すと次の画面が表示され、Access データベースにインポートしたデータを格納するテーブル名を入力する画面になります。ここではそのまま Sheet1 というテーブル名でインポートしましょう。何も変更せずに「完了」ボタンを押します。

完了ボタンを押すことにより、EXCEL ファイルのデータが Access にインポートされました。インポートされたデータは Sheet1 というテーブルに保存されています。そのまま「閉じる」ボタンを押してデータのインポートを終了します。

Access 画面に戻ると、下図のようにテーブル一覧に Sheet1 というテーブル名が表示されています。Sheet1 のテーブル名をダブルクリックするとインポートされたデータも表示されているのが分かります。

次にもう1つの EXCEL ファイル (Access_ImportData_2.xlsx) を Access にインポートしましょう。インポート手順は全く同じです。

Access で「外部データ」タブを開き、「新しいデータソース」メニューを選び「ファイルから ..... Excel(X)」を選択します。そして下図のように Access_ImportData_2.xlsx ファイルを選択してインポートしましょう。

そしてデータのインポートは下記のように進めていきます。

下記画面では特に何も指定せずに次へボタンを押して進めます。

そして下図の主キーの設定画面では、先に述べた通り「次のフィールドに主キーを設定する」を選択して "No" 列を選び、次へボタンを押して先に進めます。

そして最後に EXCEL ファイルをインポートする Access のテーブル名を指定します。ここでは下図の赤枠の通り Sheet2 というテーブルに EXCEL ファイルをインポートしましょう。

下図のようにインポート完了画面が表示されます。インポートされたデータは Sheet2 というテーブルに保存されています。そのまま「閉じる」ボタンを押してデータのインポートを終了します。

Access 画面に戻ると、今度は Sheet2 というテーブルが画面に表示されていますので、ダブルクリックするとインポートされたデータが下図のように表示されます。

以上で2つの EXCEL ファイルを Access にインポートすることができました。

Access で2つのテーブル間のデータをクエリ・ウィザードで比較し、不一致レコードを検索する

Access には、最初から2つのテーブルを比較して不一致レコードを検索してくれる便利な機能が存在します。それが「不一致クエリ ウィザード」と呼ばれるものです。

画面には「重複クエリ ウィザード」という機能もありますが、こちらは1つのテーブル内での重複データを調べる場合に使います(2つのテーブルを結合したクエリー(SQL で言えば、2つのテーブルを JOIN した結果を表示するビュー)を使えば、2つのテーブルで一致するレコードを表示することは可能です)。

これらは簡単な質問に答えていくだけで、2つのテーブル間での不一致レコードを検索できる機能です。まずはこれらを使ってみましょう。

それでは「不一致クエリ ウィザード」を使ってみましょう。下図のように「作成」タブを選択して「クエリウィザード」をクリックします。ウィザードとは、質問に答えていくだけで様々なタスクを実行してくれる便利な機能です。

すると下記のような画面が表示されますので、「不一致クエリ ウィザード」を選択して「OK」ボタンを押します。

次に比較する2つのテーブルを選択していきます。まず最初に下図の通り「テーブル: Sheet1」を選択して「次へ」ボタンを押して先に進みます。

次に下図のように、比較するテーブル名「テーブル: Sheet2」を選択して「次へ」ボタンを押して先に進みます。

今度は2つのテーブル間で比較したい列名を指定します。ここで比較したいのはくだものの名前ですので下図のように赤枠内の「全部のくだもの」と「食べたい物」を選択して「<=>」ボタンをクリックします。このボタンは選択された列を紐付けるためのものです。すると下図の緑枠内のように比較する列が関連づけられたことが分かります。そして「次へ」ボタンを押します。

今度は下図のように不一致レコードを検出した時に表示する列名(フィールド)を選択します。基本的には全て表示すべきですので、下図赤枠内の「>>」ボタンを押して全ての列名(フィールド)を右側に移します。

全ての列名を選択すると次のような画面になりますので、このようになっていることを確認した上で「次へ」ボタンを押します。

最後に下図のように比較結果が "クエリ" として保存されます。これにより比較結果が Access データベースに保存されますので、あとでいつでも不一致レコードを確認することが出来ます。このまま「完了」ボタンを押しましょう。

すると下図のように「Sheet1とSheet2との差分」という画面が表示され、2つのテーブル間での不一致レコードのみが表示されます。また不一致結果は画面左側の「Sheet1とSheet2との差分」というクエリに保存されています。

このようにして Access データベースのウィザード機能を利用して不一致レコードのみを検索することが可能です。

しかし一致するレコードのみ検索したい、もっと複雑な条件で比較したい場合には、次に述べる SQL を使って比較する必要があります。

Access で2つのテーブル間のデータを 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 データベースに保存されます。


次に2つのテーブル間の不一致レコードのみを抽出してみましょう。再度「クエリデザイン」から新しいクエリ作成して下記の 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 を活用していきましょう。