Access Excel

2つのデータの不一致・追加されたデータを探す方法

2021-02-05

2つのデータを比較し、追加されたデータ(不一致データ)を探す方法です。

エクセルとAccessだとAccessの方が簡単です。ただしAccessの場合はエクセルデータによっては修正しないとインポートできない場合があります。

エクセルのセル結合の問題点

データの不一致例

例:左が最初のデータ(シート名「1」)で、右が追加後のデータ(シート名「2」)です。

シート名

最初のデータにないものを探すにはどうするか。

2つのデータの比較

この例ではわかりやすくするために、同じ順番に並べています。不一致データは、6行目と7行目です。データが大量にあり、データの順番がバラバラの場合は、不一致データを探すのが大変です。

エクセルの関数で不一致データを探す

簡単な方法は、C列にVLOOKUP関数を使って探します。

不一致データを探す

(1)セルC2に以下の関数を入力します。

=VLOOKUP(A2,'1'!A:A,1,FALSE)

VLOOKUP関数をもう少し詳しくみる → VLOOKUP関数の基本

(2)C列の最終行まで式をコピーします。

関数入力後です。「#NA」と表示されているデータが不一致です。

関数設定後

追加されたデータだけを表示する場合は、フィルターで「#NA」を抽出します。

フィルター

抽出後の画面です。

不一致をフィルターで抽出

これより簡単なのはAccessを使う方法です。

Accessのクエリで不一致データを探す

Accessでのデータ比較は、まず比較するエクセルデータを取り込みます。その後、クエリを使って不一致を探します。

エクセルデータのインポート

(1)Accessを起動し、空のデータベースをクリックし、名前を付けて保存します。自動で開くテーブルは「×」で閉じます。

(2)[ 外部データ ] タブ > [ 新しいデータソース ] > [ ファイルから ] > [ Excel ] をクリックします。

インポート

(3)インポートするエクセルデータを選択します。

インポートウィザード

(4)ワークシート「1」を選択し、次へをクリックします。

スプレッドシートインポートウィザード・ワークシート選択

(4)「先頭行をフィールド名として使う」にチェックを入れ、「次へ」を2回クリックします。

スプレッドシートインポートウィザード・列見出し

(5)主キーの設定で、「次のフィールドに主キーを設定する」より「ID」を選択し、「次へ」をクリックします。

スプレッドシートインポートウィザード・主キー

(6)インポート先のテーブル名を入力して「完了」をクリックします。ここではシート名と同じままにしています。

スプレッドシートインポートウィザード・テーブル名

(7)次の画面表示のインポート操作の保存は何もせず、「閉じる」をクリックします。

(8)ワークシート「2」を「1」と同様にインポートします。

インポートウィザードの最初の画面は、既にテーブルが存在しているので画面が少し異なります。

スプレッドシートインポートウィザード

2つのエクセルデータ取り込み後の画面です。

テーブル

クエリで不一致を探す

(1)[ 作成 ] タブ > [ クエリ ] グループ > [ クエリデザイン ] をクリックします。

(2)テーブル「1」と「2」を選択し、「選択したテーブルを追加」をクリックします。

テーブルの追加

テーブル追加後の画面です。

テーブル追加後

(3)「1」の「名前」を選択し「2」の「名前」へドラッグします。

(4)結合の線をダブルクリックし、結合プロパティを開きます。

(5)「2の全データと1の同じ結合フィールドのレコードだけを含める」を選択し、「OK」をクリックします。

結合プロパティ

(6)両方のテーブルのフィールドを追加します。ここではわかりやすくするためすべてのフィールドを追加しました。

フィールド追加

(7)テーブル「1」の抽出条件に、「null」と入力しエンターキーを押します。

抽出条件

「Is Null」と設定されます。

抽出条件

(8)リボンより実行「!」をクリックします。

結果

大量のデータがある場合、この方法だとすぐに不一致データ(追加されたデータ)を抽出することができます。問題点は、最初に述べた通り、インポートデータが結合加工等されていると、データを修正する必要があります。

-Access, Excel

© 2021 オフィスのQ&A