Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
重複チェック
投稿日時: 20/01/17 11:55:42
投稿者: vaioyuki

いつもお世話になっております。
今回もよろしくお願いします。
 
2列目にはお客様から送られてくるデータが約35,000ほど、
4列目にはこちらで管理しているデータが約42,000ほどあります。
お客様から送られてくるデータがこちらで管理しているデータにきちんと反映されているかを確認するため、
3列目にCountIfで計算しています。
 
データが大量のため7分ほどかかります。(Debug.Print Timeで確認)
 
ただの確認なのでもう少し早く処理できればと思っているのですが何かほかに高速化できる方法などはありませんでしょうか。
よろしくお願いします。
 
 

Public Sub HenKan()
Dim Lrows1 As Long '1列目
Dim Lrows2 As Long '4列目
Dim i As Long
Dim Tel As String
Dim t As Single

Lrows1 = Cells(Rows.Count, 1).End(xlUp).Row
Lrows2 = Cells(Rows.Count, 4).End(xlUp).Row

Debug.Print Time

Application.ScreenUpdating = False

For i = 3 To Lrows1
    Cells(i, 3) = WorksheetFunction.CountIf(Range("D3:D" & Lrows2), Cells(i, 2))
Next

Application.ScreenUpdating = True

Debug.Print Time

End Sub

回答
投稿日時: 20/01/17 12:48:56
投稿者: simple

回答ではありません。確認です。
 
B列の値が、D列のなかにあるかどうかが判明すればよいのですか?
つまり、個数は必要ですか?
有無のチェックだけなら、Dictionaryを使うことでスピードアップできます。
たぶん数秒で可能でしょう。

投稿日時: 20/01/17 13:13:49
投稿者: vaioyuki

ありがとうございます。
データの有無だけで個数は必要ありません。
 
Dictionary
 
検索して調べてみます。

回答
投稿日時: 20/01/17 14:20:18
投稿者: simple

書き方の一例です。

Sub test()
    Dim Lrows1 As Long
    Dim Lrows2 As Long
    Dim i As Long
    Dim t
    
    Dim v As Variant
    Dim dic As Object
    Dim mat() As Boolean
    
    t = Timer
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    Lrows1 = Cells(Rows.Count, "B").End(xlUp).Row
    Lrows2 = Cells(Rows.Count, "D").End(xlUp).Row

    ReDim mat(1 To Lrows1) As Boolean
    
    'D列データを辞書に保持
    v = Range("D1").Resize(Lrows2, 1).Value
    For i = 3 To Lrows2
        dic(v(i, 1)) = Empty
    Next

    '有無判定
    v = Range("B1").Resize(Lrows1, 1).Value
    For i = 3 To Lrows1
        mat(i) = dic.Exists(v(i, 1))    'あればTrue,なければFalse
    Next
    
    'シートに書き込み
    Range("F1").Resize(Lrows1, 1) = Application.Transpose(mat)

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

 
今のコードでもCountifのかわりに、
Application.Matchを使い、返り値がエラー値であるかどうか判定にすると改善するでしょう。
 
なお、上記のDictionary利用のコードは、検索が早い(ネイティブコードは超えられないだろうが)Dictionary利用の効果に加え、
・配列に読み込み、
・配列として一括して書き込むところ
が効果を上げているはずです。(特に、書き込み部分は段違いに速度が変わってきます。)
 
# 脱字を修正し、コメントを追加しました。

投稿日時: 20/01/17 15:41:38
投稿者: vaioyuki

ありがとうございます。
段違いどころか、1秒かからずに出来ました!!
 
今のデータが3行目から始まるものですので頑張って直します。
Application.Match でも勉強してやってみます。
 
ありがとうございました!!