Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 8.1 Pro : Excel 2007)
2つのブックの値の比較
投稿日時: 20/02/05 19:25:47
投稿者: FILETUBE

こんばんは。
1つ教えて下さい。
 
 
2つのブック(A.xlsxとB.xlsx)があります。
オープンしwksとwks2としてあります。
 
A.xlsxとB.xlsxの3列を比較して等しければ
AにBの値をセットします。
 
For cnt = 2 TO maxrow
 
  IF wks.cells(cnt,1) <> "" Then
   
     For cnt2 = 2 TO maxrow2
    
         IF wks.cells(cnt,1) = wks2.cells(cnt2,5) And
            wks.cells(cnt,2) = wks2.cells(cnt2,6) And
            wks.cells(cnt,3) = wks2.cells(cnt2,7) Then
            wks.cells(cnt,10) = wks2.cells(cnt2,20)
            Exit For
         End If
     Next
  End If
Next
 
のようにコーディングしましたがA,B両方5000件位あるので
かなり時間がかかります。
 
もっと良い方法はありますか。
分かる方おられましたら教えて頂けないでしょうか。
 
宜しくお願いします。

回答
投稿日時: 20/02/05 19:34:47
投稿者: takesi

 
http://officetanaka.net/excel/vba/speed/s11.htm
配列を使う
 
http://officetanaka.net/excel/vba/speed/s13.htm
値貼り付けは遅い
 
このようなページあります。

回答
投稿日時: 20/02/05 19:50:51
投稿者: takesi

マクロVBAの高速化・速度対策の具体的手順と検証
https://excel-ubara.com/excelvba4/EXCEL228.html
 
すごいですね、究極
Application.ScreenUpdating 、Application.Calculation
の切り替え時間すら邪魔なそうです。
 

回答
投稿日時: 20/02/05 20:24:11
投稿者: simple

配列やDictionaryを使うと早くなるでしょうね。
このあいだも、同じような話がありました。
https://www.moug.net/faq/viewtopic.php?t=79023
なんかが参考になるかも知れません。
 
ところで、確認です。
Bブックのデータですけど、
5列、6列、7列の組でみたとき、
ふっつ(以上の)行に、同じ組み合わせのデータが重複することはありますか?
もしあった場合、どの行の20列データを転記しますか?

投稿日時: 20/02/05 21:14:58
投稿者: FILETUBE

回答ありがとうございます。
なるほど、配列(Range)かDictionaryですね。
 
Bブックに5、6、7列に重複データはありません。
 
3列の比較になるとFindは使えないですね?
 
If文で比較するしかないでしょうか?
 

回答
投稿日時: 20/02/05 22:35:23
投稿者: simple

回答拝見。
>なるほど、配列(Range)かDictionaryですね。
配列とRangeは違いますよ。
また、配列かDictionary の二者択一ではなく、両方を使います。
参考スレッドをチラ見するとよいのでは?
  
Dictionaryについては、
複数セルの値をTab文字か何かを間にはさんだ連結文字列を作り、
それを Keyにし、
行頭行番号を Itemとするものを作成して使うとよいでしょうね。
 
Findの利用、ちょっと趣旨がよくわかりません。
ところで、5000行で、どのくらいの時間がかかるんですか?

投稿日時: 20/02/05 22:49:44
投稿者: FILETUBE

回答ありがとうございます。
両方5000件で20分くらいかかります。
 
Findは検索文字列が1つかと思い
質問しました。
 
配列は早速試してみたいと思います。

回答
投稿日時: 20/02/06 17:47:38
投稿者: simple

質問者さんがトライされている間、関連する話をしてみます。
 
(1) AND評価の非効率性

If wks.cells(cnt,1) = wks2.cells(cnt2,5) And  _
   wks.cells(cnt,2) = wks2.cells(cnt2,6) And  _
   wks.cells(cnt,3) = wks2.cells(cnt2,7) Then
という式は、
 
If wks.cells(cnt,1) = wks2.cells(cnt2,5) Then
   If wks.cells(cnt,2) = wks2.cells(cnt2,6) Then  
      If wks.cells(cnt,3) = wks2.cells(cnt2,7) Then
と書いても同じ効果が得られ、後者のほうが速度的には早くなります。
1/3くらいに短縮されます。
 
というのは、
前者(And方式)では、最初の条件がFalseでも、残りの評価が行われるので、
それが無駄になるわけです。
後者は、そうした無駄がありません。
 
言語によっては、AND論理演算子の評価にあたって、
最初の条件がFalseなら、残りは評価せずに、飛ばす仕様のものもあります。
こうした評価は、短絡評価(最小評価)と呼ばれ、多くの言語でこれが採用されています。
この場合は、余り無駄が生じません。
 
しかし、VBA(VB6)のANDは非短絡評価であり、すべての条件がもれなく評価されて
しまう方式が採用されています。言語仕様としては、やや珍しい部類に入るかもしれません。
https://ja.wikipedia.org/wiki/%E7%9F%AD%E7%B5%A1%E8%A9%95%E4%BE%A1 参照
 
上記の特徴を考えると、
今回のような速度を求める場合は、3つのIfを重ねたほうが有利になります。
 
勿論、速度が求められない場合は、意図が分かり易いこともあって、ANDが使われることが
あります。場面によって、使い分けたほうがよいと思います。
 
(2)
作業領域として、3つの列を連結したものを作っておけば、VLOOKUPなりMatchなりで、
事は済みます。
このとき、通常は使われない文字を間に挟むことが行われます。
これは、"abc" ,"de" と "ab","cde"を同一のものと誤認してしまわないための仕掛けです。
 
ワークシート関数は高速ですから、5000件でも、照合、表引きに要する時間は
1秒かかりません。

回答
投稿日時: 20/02/06 22:35:08
投稿者: simple

参考にしてください。
前のコードを殆ど同じです。たぶん、瞬時に終わると思われます。
 

Option Explicit

Sub test()
    Dim dic     As Object
    Dim ws1     As Worksheet
    Dim ws2     As Worksheet
    Dim lstRow1 As Long
    Dim lstRow2 As Long
    Dim mat1    As Variant
    Dim mat2    As Variant
    Dim v       As Variant
    Dim k       As Long
    Dim s       As String
    
    Dim t
    t = Timer

    Set ws1 = Workbooks("A.xlsx").Worksheets(1)  '■適切に修正してください。
    Set ws2 = Workbooks("B.xlsx").Worksheets(1)  '■適切に修正してください。
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    lstRow1 = ws1.Cells(Rows.count, "A").End(xlUp).Row
    lstRow2 = ws2.Cells(Rows.count, "E").End(xlUp).Row

    mat1 = ws1.Cells(1, 10).Resize(lstRow1, 1).Value
    mat2 = ws2.Cells(1, 20).Resize(lstRow2, 1).Value
    
    '【ws2の5〜7列の連結データ → その行番号】の対応関係をdictionaryに保持
    v = ws2.Range("E1").Resize(lstRow2, 3).Value
    For k = 2 To lstRow2
        s = v(k, 1) & vbTab & v(k, 2) & vbTab & v(k, 3)
        dic(s) = k
    Next

    '比較判定
    v = ws1.Range("A1").Resize(lstRow1, 3).Value
    For k = 2 To lstRow1
        s = v(k, 1) & vbTab & v(k, 2) & vbTab & v(k, 3)
        If dic.Exists(s) Then
            mat1(k, 1) = mat2(dic(s), 1)
        End If
    Next
    
    'シートに書き込み
    ws1.Cells(1, 10).Resize(lstRow1, 1) = mat1

    Debug.Print Timer - t    'かかった秒数を表示
End Sub

回答
投稿日時: 20/02/09 22:19:54
投稿者: simple

突発事象が発生したのであればともかく、
コメントがあってから、3日も何のコメントもなく放置するのはいかがなものですか?
ちょっと失礼ではないかと思う。簡単な反応くらいすべきでは?

トピックに返信