- ベストアンサー
エクセルのファイル内データ比較
データがエクセルファイルなのでここで質問いたします. エクセルファイルが300個ぐらいあります. この中で基本になるファイルは50個ぐらいあります. その50個のファイルの1つと残り250個ぐらいのファイル内データを比較できる方法はないでしょうか? 当然一気に比較する方法ではなくてもかまいません. 現在1つ1つ開いて見て比較する途方もない作業で死にそうです. ファイル内データはマクロもなければ計算式すらありません. 数字データがA1~A90,B1~B90まで入っています. データ数は全ファイル共通となっています. どなたか良い知恵を授けてください.お願いします.
- eijikun
- お礼率93% (137/147)
- オフィス系ソフト
- 回答数3
- ありがとう数2
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
とりあえず、ファイル名固定と考えて、VBAを使わずに行うのなら、リンク貼り付けを利用すればいいですね。 1:ファイルを新規作成する。(以下、[比較1]) 2:基本となるファイル(50個ほどあるやつ)を一つ開く(以下、[基準1]) 3:比較対象のファイル(250個の方)をとりあえず一つ開く(以下、[対象1] 4:[基準1]のA1:B90 の範囲をコピーし、[比較1]のA2:B91にでもリンク貼り付けします。(A2を選択して、[編集]-[形式を指定して貼り付け]-[リンク貼り付け]) A1のセルには、適当にキャプションを付ける。 5:[対象1]のA1:B90をコピー、[比較1]のG2:H91にでもリンク貼り付けする。 6:次の比較対象のファイル[対象2]を開き、A1:B90を[比較1]のI2:J91にリンク貼り付けする。 以下同様に他の比較対象ファイルも処理する。 列が足りなくなったら、Sheet2にでも貼り付けする。この際、Sheet1と同じレイアウトにしておくと、何かと楽。 ****ここまでは準備。 ここから比較作業**** 7:G列を選択し、書式-条件付書式で、「数式が」「=(G1<>$A1)」を指定し、目立つような書式(例えば背景が赤とか)を設定する。 8:同様に、H列も「数式が」「=(H1<>$B1)」にして、条件付書式を設定。 9:G:Hを選択し、コピー、I:J列を選択し、「形式を指定して貼り付け」で書式のみを貼り付ける。同様にG:Jの書式をK:Nに貼り付けて・・以下同様 以上の操作で、基準データと異なるものだけがハイライト表示されるはずです。 それとか、C2のセルに =(A2<>G2)+(A2<>I2)+(A2<>K2)+・・・ ってすると、A2と異なるデータの個数が求められますね。 10:このファイルを複製して、他の基準データに対応したものも作成する。[比較2],[比較3](A列、B列のデータだけ変えればいいです。) これで解決できますか?何かあったら、補足してください。
その他の回答 (2)
- nishi6
- ベストアンサー率67% (869/1280)
前の回答と同様の前提です。 横に50余りのBook、縦に250余りのBookのSheet1のセル内容を取り込みます。AB列それぞれで比較しています。 各シートの差の絶対値を計算・表示し、シート単位のA・B列の絶対値計を計算し、ゼロでなければ青く色が付きます。 実際、300余りのBookでどの位時間がかかるか不明です。300Bookを一括処理が可能かも不明です。 基本3Book、比較対象6Bookで数秒です。(こちらはPentium120、32MB (;o;)とかなり遅い! ) 数Bookでテストして見て下さい。けっこうおもしろい問題でした。 標準モジュールに貼り付けます。 Public Sub SheetsHikakuCell() Dim MainFolder As String, MainExcel As String, MainBookNum As Integer ' 50シートのフォルダ,ファイル名,数 Dim Sub_Folder As String, Sub_Excel As String, Sub_BookNum As Integer '250シートのフォルダ,ファイル名,数 ' Dim myBookName As String '集計用ブック名 myBookName = ThisWorkbook.Name Dim sht1 As Worksheet '集計用シート Dim rg As Range '集計用シートのA1 Set sht1 = Worksheets("Sheet1") Set rg = sht1.Range("A1") MainFolder = "C:\benkyo\Main" '<=== 変更して下さい Sub_Folder = "C:\benkyo\Sub" '<=== 変更して下さい Application.ScreenUpdating = False ' 50シートの方を読む。 MainExcel = Dir(MainFolder & "\" & "*.xls") While MainExcel <> "" Workbooks.Open MainFolder & "\" & MainExcel MainBookNum = MainBookNum + 1 Workbooks(myBookName).Activate With Worksheets("Sheet1").Range("A1") .Offset(0, MainBookNum * 4) = MainExcel Workbooks(MainExcel).Worksheets("Sheet1").Range("A1:B90").Copy _ Destination:=.Offset(0, MainBookNum * 4 + 1) End With Workbooks(MainExcel).Close MainExcel = Dir Wend '250シートの方を読む。 Sub_Excel = Dir(Sub_Folder & "\" & "*.xls") While Sub_Excel <> "" Workbooks.Open Sub_Folder & "\" & Sub_Excel Sub_BookNum = Sub_BookNum + 1 Workbooks(myBookName).Activate With Worksheets("Sheet1").Range("A1") .Offset(Sub_BookNum * 100, 0) = Sub_Excel Workbooks(Sub_Excel).Worksheets("Sheet1").Range("A1:B90").Copy _ Destination:=.Offset(Sub_BookNum * 100, 1) End With Workbooks(Sub_Excel).Saved = True Workbooks(Sub_Excel).Close Sub_Excel = Dir Wend Dim rwCot As Integer With Worksheets("Sheet1").Range("A1") 'ファイル名のコピー .Offset(100, 4).Formula = "=E1" 'A列の差額計算式 For rwCot = 1 To 90 .Offset(99 + rwCot, 5).Formula = "=Abs(F$" & rwCot & "-$B" & (100 + rwCot) & ")" Next 'B列の差額計算式 For rwCot = 1 To 90 .Offset(99 + rwCot, 6).Formula = "=Abs(G$" & rwCot & "-$C" & (100 + rwCot) & ")" Next 'A列差の絶対値計 .Offset(102, 4) = "A列差の絶対値計" .Offset(103, 4).Formula = "=Sum(F101:F190)" .Offset(103, 4).FormatConditions.Delete '条件付き書式を設定 .Offset(103, 4).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0" .Offset(103, 4).FormatConditions(1).Interior.ColorIndex = 8 'B列差の絶対値計 .Offset(105, 4) = "B列差の絶対値計" .Offset(106, 4).Formula = "=Sum(G101:G190)" .Offset(106, 4).FormatConditions.Delete '条件付き書式を設定 .Offset(106, 4).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0" .Offset(106, 4).FormatConditions(1).Interior.ColorIndex = 8 End With '差額の式をコピー Dim mn, sb As Integer 'Bookカウンタ Range("E101:G190").Copy For mn = 1 To MainBookNum For sb = 1 To Sub_BookNum Range("E101").Offset((sb - 1) * 100, (mn - 1) * 4).Select ActiveSheet.Paste Next Next Application.ScreenUpdating = True End Sub
お礼
ありがとうございます. プログラムも書いて頂き 大変恐縮です. うまく行きそうです.
- nishi6
- ベストアンサー率67% (869/1280)
>ファイル内データを比較できる方法はないでしょうか? この意味はセル単位で比較し、セル単位に差額を表示したりすることでしょうか。それも一応作りましたが・・・まだ未完成。 下記は、シート50余りと、250余りのシートのAB列の内容を比較し、シート単位で一致しているか判定を表示します。質問の意味と異なっていれば補足してください。それから、比較結果の出力方法も補足してもらえればと思います。 前提 50余りのシートを1つのフォルダに集めます。 MainFolder = "・・・・・・" をそのドライブ、フォルダにします。 250余りのシートを1つのフォルダに集めます。(50余りとは別フォルダ) Sub_Folder = "・・・・・・" をそのドライブ、フォルダにします。 比較対象のブック以外は入れないで下さい。 各データはSheet1(シート名)に入っていると仮定しています。 出力 横に50余りのシート名、縦に250余りのシート名を表示してA,B列毎に 一致していれば『○』、異なっていれば『×』を表示します。 なにせシート数が多いので数シートずつにしてテストして見て下さい。 標準モジュールに貼り付けて下さい。 Public Sub SheetsHikaku() Dim MNdt() As Double ' 50シートの方の値 Dim SBdt(1, 90) As Double '250シートの方の値 Dim Sagaku(2) As Double 'A,B列の差 Dim rw, cl, bk As Integer '行,列,ブックのカウンタ Dim MainFolder As String, MainExcel As String, MainBookNum As Integer ' 50シートのフォルダ,ファイル名,数 Dim Sub_Folder As String, Sub_Excel As String, Sub_BookNum As Integer '250シートのフォルダ,ファイル名,数 ' Dim myBookName As String '集計用ブック名 Dim sht1 As Worksheet '集計用シート Dim rg As Range '集計用シートのA1 Set sht1 = Worksheets("Sheet1") Set rg = sht1.Range("A1") MainFolder = "C:\benkyo\Main" ' <=== 変更して下さい Sub_Folder = "C:\benkyo\Sub" ' <=== 変更して下さい myBookName = ThisWorkbook.Name Application.ScreenUpdating = False ' 50シートの方を読む。MNdt()に格納 MainExcel = Dir(MainFolder & "\" & "*.xls") While MainExcel <> "" Workbooks.Open MainFolder & "\" & MainExcel MainBookNum = MainBookNum + 1 rg.Offset(0, (MainBookNum - 1) * 2 + 1) = MainExcel '表題を書く rg.Offset(1, (MainBookNum - 1) * 2 + 1) = "A列" rg.Offset(1, (MainBookNum - 1) * 2 + 2) = "B列" ReDim Preserve MNdt(1, 90, MainBookNum) With Workbooks(MainExcel).Worksheets("Sheet1").Range("A1") For cl = 1 To 2 For rw = 1 To 90 MNdt(cl - 1, rw, MainBookNum) = .Offset(rw - 1, cl - 1) Next Next End With Workbooks(MainExcel).Close '次のブック MainExcel = Dir Wend '250シートの方を読む。SBdt()に格納 Sub_Excel = Dir(Sub_Folder & "\" & "*.xls") While Sub_Excel <> "" Workbooks.Open Sub_Folder & "\" & Sub_Excel Sub_BookNum = Sub_BookNum + 1 rg.Offset(Sub_BookNum + 1, 0) = Sub_Excel '表題を書く With Workbooks(Sub_Excel).Worksheets("Sheet1").Range("A1") For cl = 1 To 2 For rw = 1 To 90 SBdt(cl - 1, rw) = .Offset(rw - 1, cl - 1) Next Next End With Workbooks(Sub_Excel).Close 'データの比較 For bk = 1 To MainBookNum For cl = 1 To 2 Sagaku(cl) = 0 For rw = 1 To 90 Sagaku(cl) = Sagaku(cl) + Abs(MNdt(cl - 1, rw, bk) - SBdt(cl - 1, rw)) Next If Sagaku(cl) > 0 Then '比較結果を書く rg.Offset(Sub_BookNum + 1, (bk - 1) * 2 + cl) = "×" Else rg.Offset(Sub_BookNum + 1, (bk - 1) * 2 + cl) = "○" End If Next Next '次のブック Sub_Excel = Dir Wend Application.ScreenUpdating = True End Sub
関連するQ&A
- エクセルでの列と列の比較、重複データ個数抽出
すいません。どなたか教えてください。 今、エクセル2003を使用しているのですが、ある条件でデータの個数が抽出出来ません。 お知恵をお貸しください。 A列 B列 1 あ あ 2 い う 3 う い 4 え 5 お か A列とB列を比べると、「あ、い、う」の3つのデータが重複しています。 そこで、一つのセルで3個とい値を返したいです。 条件として、 ・計算用のシートや別セルを使わずに、例えば、C1などの1つのセルの中で関数を使用して個数を出したいです。 ・できたらマクロも使用しないでやりたいです。 ・数字ではなく、文字列の比較で、空白行もあります。 やはり、列同士の比較は一つのセルの中でやることは不可能なのでしょうか? どなたかよろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- メインのデータと比較してないものを抽出(エクセル)
電話番号データの比較を行っています。 現在は手作業なので、エクセルで一括でできないかと 思い、質問させていただきます。 メインデータ(A)と 比較データ(B)があります。 これらを照らし合わせて メインデータ(A)に存在して、比較データ(B)に 存在しない数字を抜き出す、か、 もしくは隣接するセルに存在しないなら「1」… といった判定をするにはどのようにすれば よいでしょうか? メインデータには比較データの数字が必ず存在。 比較データに存在してメインデータに存在することはない。 お知恵をお貸しください
- ベストアンサー
- オフィス系ソフト
- 二つのファイルデータを比較し一致したものを並べる。
こんにちは。VB初心者です。二つ別のファイル(A とB)のデータを比較して、その中で一致したものを並べたいのですが、とっかかりがつかめません。 例えばですが、商店名から下のデータ(一つのエクセルファイルAのシート1)に一致するものを、 EXCEL FILE A Sheet1 担当者 価格 地域 在庫 商品名 商店名 Bさん 埼玉 卵 佐藤屋 Cさん 250円 神奈川 有り 牛乳 高橋屋 もう一つのエクセルのファイル (下のデータ、エクセルファイルBシート1)の中から探して、商店名から比較したデータの結果をもう一つのシートに書き込みたいのですが、どうしたら情報を行ごとにマッチングできるのかわかりません(行ごとに情報が違うので)。。簡単にVLOOKUPでデータを拾うのではなく、マクロで比較したいのですが、よろしくお願いします。 EXCEL FILE B Sheet1 商店名 商品名 価格 地域 担当者 在庫 山田屋 みかん 100円 東京 Aさん 有り 佐藤屋 卵 200円 埼玉 Bさん 有り 高橋屋 牛乳 250円 神奈川 Cさん 有り あと、情報をマッチングの後、情報が抜けているところ(佐藤屋の価格、在庫)を色(赤)に塗ることができますか?こちらのほうもよろしくお願いします。
- ベストアンサー
- Visual Basic
- エクセルマクロでデータの比較
色々調べてみたのですが、いまいち自分の思ってる感じにぴったりなものがないので、質問させてください。 【やりたい事】 同じフォルダまたはその直下のフォルダに それぞれエクセルファイルが1つずつあり、 フォーマットは違うが意味的には同じデータが入っています。 (『フォルダA』に『エクセルファイル(Ae.xls)』と『フォルダB』があり、『フォルダB』の中に『エクセルファイル(Be.xls)』がある状況です。) この2つの対応する項目同士を比較して、差異があった場合メッセージを出力する 他のエクセルファイルの値を取得する方法を教えてください。 他のファイルを開いている状態であれば値を取得するのは可能なのですが、 閉じている場合それができません。 インデックスが範囲外です と出てしまいます。 どなたか知恵をおかしください。
- ベストアンサー
- その他(プログラミング・開発)
- Excelでの比較、データチェックを行うには?
Excel2000を使用しています。 全て数字で No、数量、金額、コード・・・・ といった感じにデータを入力しており、 量が多いので、同じものをもう一回入力して 比較し、違っている部分に色をつけることはできないか 調べています。入力自体はマクロを使ったファイルにて 入力しており、CSVで保存してあります。比較はExcelにて そのcsvのファイルをシート1、シート2にそれぞれ開いて 行おうとしています。 違っている部分に色をつけることはできそうなのですが その部分だけでなく、その行の一番左にあるNoにも色を つけたいと思うのですが、そのようなことは可能でしょうか?また、もっとよい方法があればどなたか教えてください。
- ベストアンサー
- オフィス系ソフト
- 別々のEXCELファイルを比較し、片方だけにある情報をもう片方に書き込
別々のEXCELファイルを比較し、片方だけにある情報をもう片方に書き込むやり方。 別々のEXCELファイルを比較し、片方にある情報を元に、もう片方のEXCELに表示するやり方を探しています。 Aファイル 営業担当者 お店の名前を管理する数字 お店の名前 電話番号 Bファイル お店の名前 お店の名前を管理する数字 Bのファイルに、お店の名前(お店の名前を管理する数字)が一致するAのファイルの営業担当者の情報を表示したいのですが、いい方法はあるでしょうか? よろしくおねがいします。
- ベストアンサー
- その他MS Office製品
- Excelでデータの比較をしたい
類似した2つのデータを比較し、相違箇所だけ発見したいと 思っています。 (元のテーブル) A 1 2 (取り込み後テーブル) B 1 2 上の場合は、1、2は取り込まれているものの、値AがBに なってしまっているため、そこだけセルの色が変わるとか そういったことが出来ないかと考えています。 このようなことをExcelで行う方法を教えていただけ ないでしょうか? よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルのファイル操作について
マクロが組み込まれていないエクセルファイル「ファイルA」 マクロが組み込まれているエクセルファイル「ファイルB」 があったとします。 ファイルAを開いたとき、ファイルBのマクロをファイルAに 自動的にとりこむ方法ってありますか?
- ベストアンサー
- Visual Basic
- excelからwordのファイル名を検索してリンクを貼りたいのです。
excelからwordのファイル名を検索してリンクを貼りたいのです。 1つのexcelファイルの中に数千行のデータが入っています。 そこから、excel1行に対して1つずつ、wordのファイル名にリンクを貼りたいのですが、 excelのデータとwordのファイル名で共通しているのは、excelにあるデータ(ABCDE)に対して、wordのファイル名は(CDEFG)と、全部が共通しているのではなく、3つだけ共通の文字列があるだけです。 (※各A,B,C,D,E,F,Gは文字列。A=日付、B=名前 など。) 関数でできるのか、マクロを組まなきゃいけないのか、それすらもわかりません。 こんな説明でわかりますか? マクロはほとんど初心者なので、詳しく教えて頂けると助かります。
- ベストアンサー
- Visual Basic
- エクセルファイルの一括変換・処理方法について
エクセルファイルの一括変換・処理方法についてお聞きしたいことがあります。100行3列の数値データを持つエクセルファイルがあるとします。4列目に計算式例えば、D1=A1*B1をD列にフィルする作業を自動的に行い、かつ複数の、同様なデータを持つエクセルファイルにも一括して同一作業を行う方法を、どなたかご教授くださいませんでしょうか?おそらくマクロを使うこととなるかもしれませんが、もしそうでしたらそのマクロについてもお教えいただけるとありがたいです。よろしくお願いいたします
- ベストアンサー
- オフィス系ソフト
お礼
ありがとうございます. すごいです. DOSレベルで簡単に考えてましたが エクセルのファイル比較はこのようにやるのですか. 参考させていただきます.