Excelで複数のCSVファイルを、セル・行を問わずに比較する方法を教えてください

質問

Excelで、複数のファイルを比較し、差異を調べる方法を探しています。
使用しているExcelは2000です。

・最新データファイル.csv
・旧データファイル.csv
・リスト1.csv
・リスト2.csv
・リスト3.csv　
（……リストファイルは複数存在。今後も増える予定）

リストファイルをひとつにまとめたものが、旧データファイルです。
それをシステムに登録し、自動的に追加削除が加わり、必要な時にダウンロードしたものが最新データファイルとなります。

データファイルは一括されてしまいますので、新しく追加されたものだけ、削除されたリストだけという、データを指定してダウンロードすることはできません。

各ファイルの列はほぼ同じデータが並びますが、行に関しては、削除された場合自動的につまるため、シートとシートを行で比較して差異を調べるという方法では対応できませんでした。

追加・削除されたものを把握し、それがどのリストファイルに含まれていたかをできるだけ簡単に知る方法をご存知の方がいたら、教えてください。

また、これでは何をしたいのかわからないという場合は、必要な項目を教えてください。追記したいと思います。
よろしくお願いいたします。

回答No.3

pauNed · ベストアンサー

（#2コメントへのレスです）
>> A列の情報が追加・削除されたことを確認できれば、あとの列は
>　 特に比較しなくても問題ないと考えています。
なので、比較的簡単そうです。（データ総量を考えなければ）

>『ファイル名付の1つのcsvファイル』とは、具体的にどういうものですか？
についてはQNo.3168255のANo.1を実際に試してみられたら良かったのですが、
ついでの修正版を書いておきます。

1)まず、各csvファイルを１つのフォルダにコピーしておいてください。
2)新規Bookに、『最新データファイル.csv』の『A列のみ』をコピーしてください。

(2)の新規Bookのコピー後シートをActiveにして（選択して）、
　以下のコードを実行してみてください。
　フォルダ選択画面では、(1)のフォルダを選択してください。

Sub CSVまとめsample()
　　Dim obj As Object
　　Dim fd　As String
　　Dim fn　As String
　　Dim msg As String
　　Dim i　 As Long
　　Dim n　 As Long
　　Dim x　 As Long
　　Dim y　 As Long
　　Dim z　 As Long
　　Dim arg(1 To 256) As Long

　　'フォルダを選択する
　　Set obj = CreateObject("Shell.Application") _
　　　　.BrowseForFolder(0, "SelectFolder", 0)
　　'選択なければ処理を抜ける
　　If obj Is Nothing Then Exit Sub
　　On Error Resume Next
　　fd = obj.self.Path & "\"
　　If Err.Number <> 0 Then
　　　　fd = obj.Items.Item.Path
　　　　Err.Clear
　　End If
　　On Error GoTo errHndr
　　Set obj = Nothing
　　If Len(fd) = 0& Then MsgBox "failure": Exit Sub
　　Application.ScreenUpdating = False
　　arg(1) = 2
　　x = 1
　　'■ActiveSheetのD:E列を使用します。
　　With ActiveSheet
　　　　'Dirを使って指定フォルダ内csvファイルを順次処理
　　　　fn = Dir(fd & "*.csv")

　　　　Do Until Len(fn) = 0&
　　　　　　i = i + 1
　　　　　　'データ数によってセット先を変更
　　　　　　n = n + x
　　　　　　'外部データ取り込みを利用
　　　　　　With .QueryTables.Add(Connection:="TEXT;" & fd & fn, _
　　　　　　　　　　　　　　　　　Destination:=.Range("D" & n))
　　　　　　　　.AdjustColumnWidth = False
　　　　　　　　.TextFilePlatform = xlWindows
　　　　　　　　.TextFileStartRow = 1
　　　　　　　　.TextFileCommaDelimiter = True
　　　　　　　　.TextFileColumnDataTypes = arg
　　　　　　　　.Refresh BackgroundQuery:=False
　　　　　　　　x = .ResultRange.Rows.Count
　　　　　　　　.Parent.Names(.Name).Delete
　　　　　　　　.Delete
　　　　　　End With
　　　　　　'ファイル名をF列にセット
　　　　　　.Range("E" & n).Resize(x).Value = fn
　　　　　　'次のファイルへ
　　　　　　fn = Dir()
　　　　Loop

　　　　If i = 0 Then
　　　　　　msg = "no file"
　　　　Else
　　　　　　msg = i & " files.done"
　　　　　　y = .Range("A65536").End(xlUp).Row
　　　　　　z = n + x - 1
　　　　　　'ここから---
　　　　　　With .Range("B1:B" & y)
　　　　　　　　.Formula = "=vlookup(A1,$D$1:$E$" & z & ",2,0)"
　　　　　　　　'.Value = .Value '数式を値に変えるには行頭の『'』を削除
　　　　　　End With
　　　　　　With .Range("F1:F" & z)
　　　　　　　　.Formula = "=match(D1,$A$1:$A$" & y & ",0)"
　　　　　　　　'.Value = .Value
　　　　　　End With
　　　　　　'---ここは関数埋め込みなので、手作業でも可。
　　　　End If
　　End With
　　Application.ScreenUpdating = True
　　MsgBox msg
　　Exit Sub
errHndr:
　　Debug.Print Err.Number, Err.Description
　　MsgBox Err.Number & vbTab & Err.Description
End Sub

データ総量を考えた場合、Excelでは厳しいかもしれません。
ただ、xl2007（1,048,576 行×16,384 列に拡張）への移行という手もあるかもしれませんし、
SQLを使った不一致抽出クエリをVBAから実行する手もあるかもしれません。

#でも業務に使う事と、今後のカスタマイズの事を考えればAccessなどの利用が現実的なのかなぁ。

お礼

ありがとうございます!!
おっしゃるとおりに進めたら、バッチリです!!

現状、元リストがごちゃごちゃになってしまっている部分があるので、リスト作りから整理しなければなりませんが、要求をばっちり満たしてくれていますので、この方法で現状はOKそうです。

ただ、おっしゃるように、将来的にどんどんデータ数が増えたら、処理しきれなくなりますね……。
そのときは改めて考えようかなと思います。
本当にありがとうございました!!!

回答No.2

pauNed

こんにちは。
Q1)
・最新データファイル.csv
は何行、何列くらいのデータですか？

Q2)
各
・リスト1.csv
・リスト2.csv
・リスト3.csv
は何行くらいのデータですか？
また、最大何ファイルになる事が予測されますか？

Q3)
『各ファイルの列はほぼ同じデータが並びます』
これは、各データ（行）の列については比較の必要がないという事でしょうか？

Q4)
例えば各データ（行）の各列（セル）は比較しないとして、
各行には照合のキーとなるユニークな列はありますか？（例えば『商品コード』など）

上記条件によっては、（ラクなのはキー列が存在している場合）
１）
・リスト1.csv
・リスト2.csv
・リスト3.csv
各csvファイルを、ファイル名付きの１つのcsvファイルにまとめる。
（内容によっては、キーとなる列だけでよいかも）

２）まとめたcsvファイルと『最新データファイル.csv』をMacth関数などで比較する。
　（データ量によってはピボットテーブルやVBAでの重複チェックも検討）
...のような流れが考えられるかと思います。

>どのリストファイルに含まれていたか
を調べるには、この『ファイル名付きの１つのcsvファイルにまとめる』ところが肝かと。
http://oshiete1.goo.ne.jp/qa3168255.html

各行の各列を比較する必要があるとなると、ちょっと面倒な気もします。
（それもデータ量によります）
Excelで、となるとシェアウェアなどを活用したほうが近道かもしれませんね。
http://hp.vector.co.jp/authors/VA033788/softmenu.html

補足

回答ありがとうございます!!
まず、質問について回答させていただきます。

Q1)
・最新データファイル.csv
は何行、何列くらいのデータですか？

>> 現在、行数は13,000程度。今後さらに増える予定です。

Q2)
各リストx.csv
は何行くらいのデータですか？
また、最大何ファイルになる事が予測されますか？

>> 上記最新データファイルを分割したものですので、
　 少ないものは50行くらいから、多いものは10,000以上です。
　 今後はさらに20,000以上となることも考えられます。
　 ファイル数においては、最大値は決まらないです。
　 ほぼ一定のペースで増え続けるであろうと考えています。
　（年単位で2～10ファイル程度）

Q3)
各データ（行）の列については比較の必要がないという事でしょうか？

>> A列の情報が追加・削除されたことを確認できれば、あとの列は
　 特に比較しなくても問題ないと考えています。

Q4)
例えば各データ（行）の各列（セル）は比較しないとして、
各行には照合のキーとなるユニークな列はありますか？（例えば『商品コード』など）

>> A列に、ユニークな文字情報が入っています。

---

アドバイスいただいた方法についてですが、

1）
『ファイル名付の1つのcsvファイル』とは、具体的にどういうものですか？　csvファイルということは、1枚のシートに統合していくわけですよね？　他のセルを使ってファイル名を記載しておくということですか？

2）
他の質問でみつけたVBAを利用したチェックは、同じ行ごとでしかチェックできず、対応できませんでした。
関数もマクロも詳しくないので、具体的ナ記述方法などを教えていただけると助かります。

やっぱりシェアウェアですかね。
今はまだ、方法さえわかれば手作業でこなせるボリュームですが、今後は確かに、膨大になる可能性が大いにあります。
教えていただいたサイトをチェックして、そちらも検討してみたいと思います。

回答No.1

imogasi

中央での処理があり、現場（？）のニーズに、中央が応え切れてないため、現場担当者が、何かを独自でやろうとしているように見える。
こういうのは本来システムの担当者（ぷろ？）がやるべき問題と思う。担当者にシステムの改良を、根気良く説得するのが筋です。
本部（中央）での作業中に、希望のデータは、顔を出していたはずですから
、本部なら、印刷やファイル化だけの問題だと思います。
ーー
担当者がやることを考えた場合
（１）VBAはできますか
（２）エクセル関数はどの程度で着ますか
これらが回答に影響します。具体的に質問をかけていないところから
（２）も心配です。
技量やプログラムなど好きな人、執着心のある方で無いと、なかなか進みません。
ーー
似たようなことはVLOOKUP関数で,一方をテー物として、やれますが、第3のシートは全セル関数でうまり、式は複雑になると思います。
どちらかというと、ファイル比較はアクセス向きの課題です。
量的な（おおよその行数）なども書いてありません。行数が多いと
関数も動かなくなるかも知れません。
作業サイクル（日時、月次）なども書いてありません。この質問の課題をエクセルで考えていること自体、対処量力に不安を感じます。質問の書き振りから、途は遠いのかなと思ってしまいます。
（A)追加・削除されたものを把握し
（B)それがどのリストファイルに含まれていたか
の両方をやりたいのはさらに（内容が違うので）することが多くなります。

補足

状況は、有料のシステムを利用して行っているものに、自分たちのニーズを取り入れたいというものです。
同じ社内での問題ではないため、即時対応してくれるはずもなく（社内だって無理ですけど……）、今あるシステムで与えられるものを有効に利用したいために試行錯誤しています。

ご質問についてですが、
VBAの知識はありません。関数も一般的に使用する程度で、難しいことはわかりません。
ただ、他の方がこの質問広場で質問していたExcel関係の質問で、具体的に関数などの書き方が掲載されていたものは、取り入れて試すことができたので、場合によっては自分でもできるかなと思い、今回質問してみた次第です。
執着心は結構ありますが（笑）、プログラムは学生時代に触れた程度です。

データの量に関しては、ANo.2のアドバイスに補足させていただきましたので、参考にしていただけると幸いです。

……と、一応答えてみましたが、私の力量では、また、エクセルでやること自体難しい、ということのようですね。
アクセスはろくに触ったことがないので自分ではできないだろうと思ったこともあり、知人にざっと話したところその程度ならエクセルでもできるのではないかな？といっていたので、その方法を模索してみました。

要望も大きく、そう簡単ではない問題なのですね。
改めて、プログラムに詳しい知人に相談してみようと思います。