【ExcelVBA】５万行以上のデータ比較の効率的な処理方法について

社内での重複チェックツールを作っています。

セル関数で対応していましたが、以下理由でマクロでないと厳しいため試行錯誤中です。
・行数は不定で、使うときに足りない分を関数を付け足す作業はしたくない
・関数を埋め込んだ場合、ファイルサイズが大きすぎて開かない＆再計算でフリーズ
・マクロにしたはいいが、結果が遅い（量が量だから仕方ない？）

以下処理ですが、
スピードが今一歩と感じています。
アドバイス頂ければ、幸いです。
データは現状５万ちょっとが最大です。
基本配列を使って比較すればいいのですが、デバッグしてると２０秒位かかり、
ハングアップしてるか不安になり、escすると止まるので動いてはいますが、
量が多いからこんなもんでしょうか？

やりたいことは１つずつ比較して、２つ以上ある箇所の隣に×をして更に隣のセルに該当データを出力させます。
そして、フィルターを掛けて抽出できるようにします。
これをボタンを押したら、ファイルを選ばせてチェックが始まるという流れです。

以下試しのコードです（比較箇所だけ）
sub test
Set targetRng = Range("A1:a50000")

For Each Rng In targetRng
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Rng = Cells(i, 1) Then
cnt1 = cnt1 + 1
End If
Next
If cnt1 > 1 Then
Rng.Offset(0, 1) = "×"
End If
cnt1 = 0
Next
end test

No.3 ベストアンサー 回答者： tatsumaru77 回答日時： 2024/10/06 22:52

あなたが提示されたマクロでは、約50000×25000回のセルの参照を行っています。

このようなケースでは、dictionary（連想配列）を使うと、劇的に早くなります。
dictionaryを使うとセルの参照は、５万回で済みます。
又、セルの更新（×の設定）に時間がかかるので、配列で処理し、配列の内容を一気にセルへ転送すると更にはやくなります。

提示されたマクロをみると、
１．データは1行目から開始している。
（通常は１行目は見出しで、データは２行から始まるが、そうではない）
２．チェックするのはA列で、A列に同じ内容のセルが２つ以上ある場合は、
そのセルの右隣のB列に×を設定する。

上記の仕様であってますか。
それであっているなら、その仕様に従って、重複の行に×を設定するマクロを提供することは可能です。

こちらの環境でデータ５万件で確認したとき
１．dictionaryを使用し、該当セルに×を個別に設定すると、約８秒で完了
２．dictionaryを使用し、配列にB列の内容を格納し、配列で処理した結果をB列に戻すと、約１秒で完了
となっています。

マクロの提供が必要であれば、その旨、補足してください。
（又、仕様が違っていれば、その旨補足してください。１行目が見出しのケース等）

この回答へのお礼

お返事ありがとうございます。
dictionaryでのコードを教えて頂けますか?

以下流れです。
重複チェックは全ての項目ではなく、フラグがあるものが対象です。

別シートにある実データを行終端までチェックする。
データの開始行はファイルによって変わるが、ファイルごとに専用のマクロを作るので、そこを意識する必要はない（終端はチェック必須）。
例えば14行目から6万行目までを各行で重複チェックする。
専用のシートがあり、チェック結果はそこに都度出力。
2つ以上見つかった場合は、チェックシートのその行と同じ行に数と対象データが何かを出す（フォーマットはデータ元と同じ）。
1つでも重複があれば、インデックス行（13行目）をオレンジに変える（条件付き書式は実証済み）
別シートのチェック結果は重複有無に関わらず、出力させる（重複がない場合は、全部1になる）。
次の対象フラグへ続く。

以上になります。

お礼日時：2024/10/07 15:29

No.7 回答者： tatsumaru77 回答日時： 2024/10/07 20:26

No3です。

補足ありがとうございました。
補足を読んだのですが、具体的なマクロにするためには、更に詳しい情報が必要になります。下記の件、お願いできますでしょうか。

１．別シートのレイアウトがわかりません。具体的なレイアウトを画像で提示していただけませんでしょうか。特に、フラグの列と重複チェックデータのある列が不明です。又、シート名も提示してください。

２．チェック結果を出力するシートのレイアウトを画像で提示していただけませんでしょうか。又、シート名も提示してください。

３．重複チェックは全ての項目ではなく、フラグがあるものが対象ということですが、具体的に例を挙げて説明していただけませんでしょうか。
又、「次の対象フラグへ続く。」ということの意味が分かりません。
それも、含めて、説明をお願いします。

以上、よろしくお願いいたします。

この回答へのお礼

結果的に解決しそうです。
アドバイスありがとうございます。

お礼日時：2024/10/08 15:28

No.6 回答者： いんちょ 回答日時： 2024/10/07 09:22

No.5でadd時にitemに行番号を入れて、existsの場合にitemの行とi行目にｘを入れれば、ループは１回で済むでしょう。

No.5 回答者： fujillin 回答日時： 2024/10/07 02:15

こんばんは

VBA自体が決して速いとは言えませんし、速度はPCの性能やメモリにも影響されますので、一概には言えませんけれど・・・

VBAで、多少なりとも速度向上を目指すなら、
　・処理ロジックの効率化
　・シートへのアクセス回数の削減
等があげられます。
その他にも、検索すればよく見かける
　・スクリーン更新の停止
　・関数等の計算やイベント処理の一時停止
等もありますけれど。

既に指摘があるように、ご提示のコードの処理方法はあまり効率の良いものとは言えないように思われます。


＞デバッグしてると２０秒位かかり～
ご提示のコードで50000件を20秒で処理できるのなら、かなり高スペックの処理速度と言えると思います。
（当方の環境では、ご提示のコードでは50000件を30分以上かかっても処理できなかったため中断しました。）

一方で、以下のように処理方法を変えれば、それなりに速度向上が見込めると思います。
（多分、No3様の回答と類似した方法かと思います。）
ちなみに、こちらであれば、当方の環境でも0.34秒程度で50000件の処理が終わります。


ご説明文のうち
＞～箇所の隣に×をして更に隣のセルに該当データを出力させます。
「更に隣のセルに」とある処理が、ご提示のコードには見当たらないので、内容が不明なため無視しました。
以下は、重複のあるセルのB列に「×」を表示するだけのものです。
ご参考までに。

Sub test()
Dim d, v
Dim rmax As Long, i As Long

Set d = CreateObject("Scripting.Dictionary")
rmax = Cells(Rows.Count, 1).End(xlUp).Row
v = Cells(1, 1).Resize(rmax).Value

For i = 1 To rmax
If d.exists(v(i, 1)) Then d.Item(v(i, 1)) = 2 Else d.Add v(i, 1), 1
Next i
For i = 1 To rmax
If v(i, 1) <> "" Then
If d.Item(v(i, 1)) = 1 Then v(i, 1) = "" Else v(i, 1) = "×"
End If
Next i

Cells(1, 2).Resize(rmax).Value = v
End Sub

この回答へのお礼

＞デバッグしてると２０秒位かかり～、
すいません。これは嘘でした。
実際は１つの項目チェックに約１０分かかります。
dictionaryは使い方が分かってないのもありますが、今回のに応用するのはややこしくなりそうなので、納期もあるので処理時間が掛かってもいいから自分で追えるのを試そうかと試行錯誤中です。

お礼日時：2024/10/07 21:01

No.4 回答者： いんちょ 回答日時： 2024/10/06 23:25

「更に該当データを出力させます」のところが不明ですが、単に重複チェックならcountif関数入れればいいです。

「関数を付け足す作業はしたくない」ってことですが、計算式のコピーに手間は要りません。
https://media.yayoi-kk.co.jp/4340/
ファイルサイズが問題ならｘ付けた後で計算式を削除すればいいです。
もし、どこと重複しているかを隣のセルに書き出すとかするならVBAで二重ループになりますが、
For i = 1 To 最終行
　For j = i + 1 To 最終行
とすれば計算量は半分になります。j < i の場合の比較は実施済みなので。

この回答へのお礼

アドバイスありがとうございます。
行データが多すぎるので、一旦関数によるチェックは除外としました。
もう少し考えます。

お礼日時：2024/10/07 17:27

No.2 回答者： amerikaumare 回答日時： 2024/10/06 22:03

＞スピードが今一歩と感じています

こういう、どこまでやっているのかわからないときには
イミディエイトウィンドウに途中結果を表示する
がいいでしょう　適当なところに　Debug.Printを使います。
これにより、コードの実行中に変数や計算の途中結果を確認することができます。

If i Mod 5000 ＝ 0 then
　 Debug.Print "現在の値は " & i

のようにすれば　5000件ごとに　表示されます。
Visual Basic　でイミディエイトウィンドウを表示させてから　やってください。

この回答へのお礼

アドバイスありがとうございます。
もう少し試します。

お礼日時：2024/10/07 08:24

No.1 回答者： lv4u 回答日時： 2024/10/06 21:43

>>量が多いからこんなもんでしょうか？

以前、エクセルファイルで、マクロ等で処理していて終わるまで40分くらいかかっているものがありました。
あまりに時間がかかるのと、入力で間違っている個所について、人の目で再チェックする必要があり、間違ったデータが残ってしまうので、VBAでチェック処理を追加して、作り直したことがありました。
それで、処理時間が短くなったけど、それでも20分程度かかっていました。
この時は、「処理対象にすべきデータをうまく絞り込めば、もっと速く終わるのでは？」と考えて処理の流れを見直すことで、最終的には5分くらいで終わるようにできました。

ただ、処理内容によっては、そんな工夫ができないこともあると思います。
その場合、「エクセルＶＢＡでの処理はこんなものだ」と諦めるしかないのかもしれません。

ちなみに、もっと処理スピードを速くしたい場合、C#でプログラミングすると可能になると思いますけど、普通の方には無理でしょうね。

この回答へのお礼

早々のアドバイスありがとうございます。
もう少し考えます。

お礼日時：2024/10/07 08:21