★Access2000VBA・Excel2000VBA独学～2つのテーブルの間に何らかの差異があるどうかを調べる方法・いろいろ～

★Access2000VBA・Excel2000VBA独学～2つのテーブルの間に何らかの差異があるどうかを調べる方法・いろいろ～
　　
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
　　
★ 方法01：ピボットテーブルを使う方法

Excel2013以降？の場合は、2つの表を縦に結合し、「concat」関数を使って、新しい列に、すべての列の値を文字列として一括で結合したものを表示させます。
それをピボットで数え、2個なら同一。1個なら差異がある、ということになりますので、差異のあるものだけをどこが違うかを調べます。

とりあえず、「1行でも、1セルでも異なる＝ダメ」とだけわかればいい・・・という場合などに使えます。

結合前に、２つの表に、連番やフラグを振っておいて、それも縦の結合に含めると、いろいろと調べやすいと思います。

2013以前は、「concat」関数が無いので、以下のサイトを参考に、「concat」関数の代替関数を自作して、対応します。
concat関数の使い方とExcel2013以前の古いエクセルで使う方法
　　
　　

★ 方法02：EXACT関数を使う方法

２つのまったく同じ構成のシートを比べて、セルの値だけが違っていて、そのセルがどこかを知りたい場合に使えます。

すごく便利っぽいです。
ただ、これが使えるのはバージョン2002以降からで、2000では大変残念ながら使えないもようです。

【Excel】手元のデータがすべてサーバー上の名簿と一致しているか調べたい！エクセルで2つの表データを比較するテク

◎参考：
Excel2000にはEXACT関数が無いため、似たような関数を自作してもOKです。
セルの値の型のことを調べるのが面倒だったので、とりあえず引数の変数の型をVariant型でテキトーにやってしまいましたが、これだと、Valueプロパティも使ってないし、取得できた型が何型に変換されるかも考えてないし、正確に比較できない可能性があったり、多分思わぬエラーや比較し間違いが出るので、「引数その他の型」は、ちゃんと色々とためして、Varant型以外のものにできるならそうしたほうがいいと思います。
（あるいはもっと分岐を増やすとか）

バージョン2002以降はEXACT関数があるはずなので、以下のような関数は必要ありません。

ただ、以下の自作関数は・・・
▼ 比較して等しかったらあえて空白にせずに「--」を表示し、「比較用のシートのどこまで数式が入っているか」を見やすくしました。
▼ 差異があったら、比較したそれぞれの値を表示する。
・・・ということをしていますので、より判別しやすくはなると思います。

また、3枚同時に、４枚同時に、と比較することはできないので、そのような場合も、自作関数を作ったほうが早いかもしれません。少なくともそのような場合は、僕は「もとからある関数を組み合わせて使う」ということを考える根性がまるでないので、速度が遅くても我慢できるレベルならVBAに頼ってしまうと思います。

'
'
Function Difchk01(v_data01 As Variant, v_data02 As Variant) As Variant

    If v_data01 = v_data02 Then
        Difchk01 = "--"
    Else
        Difchk01 = v_data01 & "---" & v_data02
    End If

End Function
'
'

Function Difchk01(v_data01 As Variant, v_data02 As Variant) As Variant

If v_data01 = v_data02 Then

Difchk01 = "--"

Else

Difchk01 = v_data01 & "---" & v_data02

End If

End Function

ローカルウィンドウで調べたら、引数にセルを指定すると、、「単一セル丸ごと」が、「Range型オブジェクト」として代入されていましたので、If文の分岐の比較の式にはValueプロパティを使うなどしたほうがよいかもしれません。
（それでも、数値型と文字列型を比較してしまう・・・、というあまり意味のないことをしてしまう場合もありますが・・・。少数なんかの比較も特に危険、だと思います。日付や時刻のシリアル値とか、ヤバそうです。）

'
'
Function Difchk01(v_data01 As Variant, v_data02 As Variant) As Variant

    If v_data01.Value = v_data02.Value Then
        Difchk01 = "--"
    Else
        Difchk01 = v_data01.Value & "---" & v_data02.Value
    End If

End Function
'
'

Function Difchk01(v_data01 As Variant, v_data02 As Variant) As Variant

If v_data01.Value = v_data02.Value Then

Difchk01 = "--"

Else

Difchk01 = v_data01.Value & "---" & v_data02.Value

End If

End Function

★ 方法03：SQLを使う方法

「Microsoft Query」や「QueryTableオブジェクト＋VBA」、「ADO／DAO＋VBA」、などで使えます。

複数の列での重複データを調べ、２つの表を比較したいときに便利です。
もちろん、1つだけの列で重複データを調べ、比較することも可能です。
（いずれも完全一致のみ？ですが。）

各列のセルのデータ型がちゃんと統一されていることが前提です。
ちゃんと統一されないとSQLの実行時にエラーになるかもしれません。

(01)A表とB表があって、A表にあってB表にないレコード（行）を抽出する場合。
（Accessでいう不一致クエリ）

'
' 
SELECT A表.* 
FROM A表 LEFT JOIN B表 
         ON A表.ID = B表.ID 
WHERE (B表.ID Is Null); 
'
'

SELECT A表.*

FROM A表 LEFT JOIN B表

ON A表.ID = B表.ID

WHERE (B表.ID Is Null);

　　
　　
(02)同じく、B表にあってA表にないレコード（行）を抽出する場合。
　下記SQL文の「連番」は「ID」などとなることが多いです。
　「列01」～「列04」は、必要に応じて減らしたり増やしたりします。
　重複を調べたい列の数だけ「AND」でつなげて書きます。
　これは、(01)の場合も同様です。

（「FROM」句のカッコの中の副問い合わせの部分は、
　INNER JION でやるのが本当な気がします・・・。
　自分でもなんで、Left Join を使ってしまったのかわかりません・・・。
　もっと言うなら、副問い合わせ自体が要らず、不一致クエリみたいに
　単なるLEFT JOIN で良かった・・・気がします。）

'
' 
SELECT B表.*
 FROM (SELECT B表.連番
       FROM A表 LEFT JOIN B表
       ON      (A表.列01 = B表.列01)
           AND (A表.列02 = B表.列02)
           AND (A表.列03 = B表.列03)
           AND (A表.列04 = B表.列04)
       WHERE B表.連番 Is Not Null) AS C表
    RIGHT JOIN B表
    ON C表.連番 = B表.連番
 WHERE C表.連番 Is Null;
'
'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
'
SELECT B表.*
 FROM (SELECT B表.連番
       FROM A表 INNER JOIN B表
       ON      (A表.列01 = B表.列01)
           AND (A表.列02 = B表.列02)
           AND (A表.列03 = B表.列03)
           AND (A表.列04 = B表.列04)) AS C表
    RIGHT JOIN B表
    ON C表.連番 = B表.連番
 WHERE C表.連番 Is Null;
'
'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
'
'多分、これが一番正しいです。基本、(01)のA表とB表が逆になっただけです。
'（AND の条件が増えてはいますが。）
'
SELECT B表.* 
FROM B表 LEFT JOIN A表 
       ON      (B表.列01 = A表.列01)
           AND (B表.列02 = A表.列02)
           AND (B表.列03 = A表.列03)
           AND (B表.列04 = A表.列04)
WHERE A表.連番 IS NUll; 
'
'

SELECT B表.*

FROM (SELECT B表.連番

FROM A表 LEFT JOIN B表

ON (A表.列01 = B表.列01)

AND (A表.列02 = B表.列02)

AND (A表.列03 = B表.列03)

AND (A表.列04 = B表.列04)

WHERE B表.連番 Is Not Null) AS C表

RIGHT JOIN B表

ON C表.連番 = B表.連番

WHERE C表.連番 Is Null;

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

SELECT B表.*

FROM (SELECT B表.連番

FROM A表 INNER JOIN B表

ON (A表.列01 = B表.列01)

AND (A表.列02 = B表.列02)

AND (A表.列03 = B表.列03)

AND (A表.列04 = B表.列04)) AS C表

RIGHT JOIN B表

ON C表.連番 = B表.連番

WHERE C表.連番 Is Null;

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'多分、これが一番正しいです。基本、(01)のA表とB表が逆になっただけです。

'（AND の条件が増えてはいますが。）

SELECT B表.*

FROM B表 LEFT JOIN A表

ON (B表.列01 = A表.列01)

AND (B表.列02 = A表.列02)

AND (B表.列03 = A表.列03)

AND (B表.列04 = A表.列04)

WHERE A表.連番 IS NUll;

Excelのシートで処理する場合は以下のように、テーブル名に　$　と　[　]　がつきます。
[　]　については、　[　も　] 　も、それらの代わりに「`」（バッククォート）を使って、それでテーブル名を囲んでもOKです。

'
'
SELECT [B表$].* 
FROM [B表$] LEFT JOIN [A表$] 
       ON      ([B表$].列01 = [A表$].列01)
           AND ([B表$].列02 = [A表$].列02)
           AND ([B表$].列03 = [A表$].列03)
           AND ([B表$].列04 = [A表$].列04)
WHERE [A表$].連番 IS NUll;  
'
'＝＝＝＝＝＝＝＝＝＝＝＝
'
'VBAで変数にSQLを格納する例
'
    s_SQL01 = s_SQL01 & "SELECT [t99_社員マスタ002$].*"
    s_SQL01 = s_SQL01 & " FROM [t99_社員マスタ002$] LEFT JOIN [T99_社員マスタ$]"
    s_SQL02 = s_SQL02 & "       ON ([t99_社員マスタ002$].社員番号 = [T99_社員マスタ$].社員番号)"
    s_SQL02 = s_SQL02 & "       AND ([t99_社員マスタ002$].社員名 = [T99_社員マスタ$].社員名)"
    s_SQL03 = s_SQL03 & "       AND ([t99_社員マスタ002$].退社日付 = [T99_社員マスタ$].退社日付)"
    s_SQL04 = s_SQL04 & "       AND ([t99_社員マスタ002$].入室ID = [T99_社員マスタ$].入室ID)"
    s_SQL05 = s_SQL05 & " WHERE [t99_社員マスタ$].連番 Is Null;"
'
'テーブル名が長ったらしいと見づらいので、下記の例のように、
'テーブル名に「tA」とか「tB」といった「別名」をつけて、
'すっきりさせる方法も取れます。
'下記のコードと、上記のコードは、まったく同じ意味です。
'※「AS」が「～として・・・」という意味で、別名を付けるための
'　命令語句のようなものです。
'「FROM句でASを使うとテーブル名に別名を付けられる・・・」ということになります。
'それを「SELECT」句や他の句でも使えるようになります。
'（※ちなみにSELECT句でASを使うと、列名に別名を付けられます。）
'
    s_SQL01 = s_SQL01 & "SELECT tB.*"
    s_SQL01 = s_SQL01 & " FROM [t99_社員マスタ002$] AS tB LEFT JOIN [T99_社員マスタ$] AS tA"
    s_SQL02 = s_SQL02 & "       ON    tB.社員番号 = tA.社員番号"
    s_SQL02 = s_SQL02 & "         AND tB.社員名   = tA.社員名"
    s_SQL03 = s_SQL03 & "         AND tB.退社日付 = tA.退社日付"
    s_SQL04 = s_SQL04 & "         AND tB.入室ID   = tA.入室ID"
    s_SQL05 = s_SQL05 & " WHERE tA.連番 Is Null;"
'
'このように、少ないコードで・かつ・ループも使わずに、
'「ヒットしたデータ ” 以外 ” のデータも 一発で出せる」ので、
' 一括処理の場合は、SQLのほうが、VBAのループでやるよりも
' はるかに便利だし、書き換えもラクです。
' また、処理速度も、VBAのループ処理よりも速いことも少なくないですし。
'（数倍はもちろん、ケースによっては 数十倍以上も速くなることもあるそうです。）
'
'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
'
'以下、多分、僕が初心者すぎて冗長になりすぎてしまった良くない例。一応動くけど・・・。
'
SELECT [B表$].*
 FROM (SELECT [B表$].連番
       FROM [A表$] INNER JOIN [B表$]
       ON ([A表$].列01 = [B表$].列01)
       AND ([A表$].列02 = [B表$].列02)
       AND ([A表$].列03 = [B表$].列03)
       AND ([A表$].列04 = [B表$].列04)) AS [C表$]
    RIGHT JOIN [B表$]
    ON [C表$].連番 = [B表$].連番
 WHERE [C表$].連番 Is Null; 
'
'

SELECT [B表$].*

FROM [B表$] LEFT JOIN [A表$]

ON ([B表$].列01 = [A表$].列01)

AND ([B表$].列02 = [A表$].列02)

AND ([B表$].列03 = [A表$].列03)

AND ([B表$].列04 = [A表$].列04)

WHERE [A表$].連番 IS NUll;

'＝＝＝＝＝＝＝＝＝＝＝＝

'VBAで変数にSQLを格納する例

s_SQL01 = s_SQL01 & "SELECT [t99_社員マスタ002$].*"

s_SQL01 = s_SQL01 & " FROM [t99_社員マスタ002$] LEFT JOIN [T99_社員マスタ$]"

s_SQL02 = s_SQL02 & " ON ([t99_社員マスタ002$].社員番号 = [T99_社員マスタ$].社員番号)"

s_SQL02 = s_SQL02 & " AND ([t99_社員マスタ002$].社員名 = [T99_社員マスタ$].社員名)"

s_SQL03 = s_SQL03 & " AND ([t99_社員マスタ002$].退社日付 = [T99_社員マスタ$].退社日付)"

s_SQL04 = s_SQL04 & " AND ([t99_社員マスタ002$].入室ID = [T99_社員マスタ$].入室ID)"

s_SQL05 = s_SQL05 & " WHERE [t99_社員マスタ$].連番 Is Null;"

'テーブル名が長ったらしいと見づらいので、下記の例のように、

'テーブル名に「tA」とか「tB」といった「別名」をつけて、

'すっきりさせる方法も取れます。

'下記のコードと、上記のコードは、まったく同じ意味です。

'※「AS」が「～として・・・」という意味で、別名を付けるための

'　命令語句のようなものです。

'「FROM句でASを使うとテーブル名に別名を付けられる・・・」ということになります。

'それを「SELECT」句や他の句でも使えるようになります。

'（※ちなみにSELECT句でASを使うと、列名に別名を付けられます。）

s_SQL01 = s_SQL01 & "SELECT tB.*"

s_SQL01 = s_SQL01 & " FROM [t99_社員マスタ002$] AS tB LEFT JOIN [T99_社員マスタ$] AS tA"

s_SQL02 = s_SQL02 & " ON tB.社員番号 = tA.社員番号"

s_SQL02 = s_SQL02 & " AND tB.社員名 = tA.社員名"

s_SQL03 = s_SQL03 & " AND tB.退社日付 = tA.退社日付"

s_SQL04 = s_SQL04 & " AND tB.入室ID = tA.入室ID"

s_SQL05 = s_SQL05 & " WHERE tA.連番 Is Null;"

'このように、少ないコードで・かつ・ループも使わずに、

'「ヒットしたデータ ” 以外 ” のデータも一発で出せる」ので、

' 一括処理の場合は、SQLのほうが、VBAのループでやるよりも

' はるかに便利だし、書き換えもラクです。

' また、処理速度も、VBAのループ処理よりも速いことも少なくないですし。

'（数倍はもちろん、ケースによっては数十倍以上も速くなることもあるそうです。）

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'以下、多分、僕が初心者すぎて冗長になりすぎてしまった良くない例。一応動くけど・・・。

SELECT [B表$].*

FROM (SELECT [B表$].連番

FROM [A表$] INNER JOIN [B表$]

ON ([A表$].列01 = [B表$].列01)

AND ([A表$].列02 = [B表$].列02)

AND ([A表$].列03 = [B表$].列03)

AND ([A表$].列04 = [B表$].列04)) AS [C表$]

RIGHT JOIN [B表$]

ON [C表$].連番 = [B表$].連番

WHERE [C表$].連番 Is Null;

投稿タグ: 「本物」に近づくために, AccessVBA, Accessの独学, ADO／DAO, ExcelVBA, Excelの独学, Excel操作の基礎, Excel連携VBA

MENU

関連記事

最近の投稿

カテゴリー

MENU

★Access2000VBA・Excel2000VBA独学～2つのテーブルの間に何らかの差異があるどうかを調べる方法・いろいろ～

関連記事

最近の投稿

カテゴリー

タグ