Excel VBA 大量のレコードからある列の重複数をカウントする方法？拡張編

ある列の重複数をカウントする作業があります。
こちらのNo.13の回答で解決しているのですが、
https://oshiete.goo.ne.jp/qa/13027572.html

行数が増えてきたため、1シート(104万行)に収まらなくなりそうです。
全く同じ処理を2シートにまたがって行う様に修正できないでしょうか？
シート名は「重複①」「重複②」と考えていますが、プログラムの都合に合わせられます。

(例えばシート名の文頭が「重複～」のをすべて対象とできるなら万が一3シートを超える事があっても対応できるのに・・・というのは独り言です。)

質問者からの補足コメント

• 

  俺した直後で恐縮ですが、このソースって、
  将来どちらのシートが増えた場合も、重複チェック対象列が増えた場合も、
  ソース内をいじるだけで対応できるって事ですよね？
  まさに完璧です。

  No.3の回答に寄せられた補足コメントです。 補足日時：2022/08/23 20:45

• 

  「俺した」→「お礼した」です。失礼しました。

    補足日時：2022/08/23 20:45

• 

  KenKen_SP さんにお礼をしようとしたら、「お礼出来なくなっています」と赤字が出ました。
  ブロックされてるんでしょうかね。残念です。

    補足日時：2022/08/26 10:53

No.7 回答者： tatsumaru77 回答日時： 2022/08/26 11:27

以下、余談です。

１００万行程度になっているとのことですが、
今後も増える可能性はあるのでしょうか。
もし、あるとしたら、最大どのくらいになるのでしょうか。
現状では、excelの限界に近づきつつあるような気がします。
もし、CSVファイルを直接読み込み、重複件数をCSVファイルへ出力する
ということであれば、python等のスクリプト言語で処理が可能となります。

ちなみに、100万件のCSVファイル（約170Mバイト）を処理すると
約２６秒程度でした。

最終結果がCSVファイルでよいなら、データ件数が増えてもexcelのようにシート別に分割する必要もないので多少は楽になるかもしれません。

この回答へのお礼

お気遣いありがとうございます。
業務の事情で詳細は伏せますが、
重複が多いと困る案件で、重複数が多い順にスタッフが手作業、目検でチェックし、ある対応をしています。
作業者はIT系ではないので、ごく普通の事務処理が出来るようにExcelで渡すことになってます。

実は恐らく現在200万行超えてます。
しかし「重複あり」「重複なし」に分けると、半数以上が重複なしなので、
Powerクエリで「重複を保持」という設定があったので、100万行以内にシートに読み込むことができてます。
しかしそれでも100万行に達してしまいました。
重複ありの中でも、2重複が半数を占めるので今度は2重複も除外する策を取っています。
これを繰り返すと非常に待ち時間が発生します。
何件あっても複数シートに分割すれば処理できる体制にしておくのが望ましいと思い、この質問をさせて頂きました。

お礼日時：2022/08/26 16:39

No.6 回答者： tatsumaru77 回答日時： 2022/08/24 10:39

No3です。

＞シート内に関数式は多用してますが、重くなるので手動計算にしてあります。

もし、自動計算の状態でしたら、マクロ内で一旦手動計算にして、
全ての処理が終わった後、自動計算に戻すという方法がありますが、
既に、手動計算にしてあるのでこの方法を採用しても無駄になります。
私の知る限りでは、これ以上の速度アップは難しいです。

この回答へのお礼

ありがとうございます。
100万行近くなってくると、マクロ実行する以外に通常の操作で結構支障きたすんです^^;
普通ならマクロ内で処理して、普段は自動計算にしておきたいのですが、
事情があってそうしています。

お礼日時：2022/08/26 10:55

No.5 回答者： KenKen_SP 回答日時： 2022/08/23 16:42

ちな、dictionary で下記プロパティを設定すると大文字と小文字を区別しないですよ。

dic.CompareMode = vbTextCompare

lcase とかでの比較をカットできるかも。

他には Option compare ステートメントを利用する方法も。
https://docs.microsoft.com/ja-jp/office/vba/lang …

SQL を使う方法をおすすめしたいとこですが、、、
ご参考までに。

No.4 回答者： tatsumaru77 回答日時： 2022/08/23 14:08

No3です。

重複①の①の文字ですが
①と
⓵の２つあります。（文字コードが違います）
マクロで使用している①と同じ文字コードの①をシート名でも使用するようにしてください。
異なる方の⓵をつかうと正しく動作しません。（②③・・も同様です）
上記のような間違いを防ぐ意味では、シート名に重複1,2,3のような半角の数字を使用したほうが良いかもしれません。

この回答へのお礼

①に2種類あるのは初めて知りました。。。

実際には、_A、_B とか、_1、_2・・のように環境依存文字を使わないようにしようと思います。
ありがとうございます。

お礼日時：2022/08/23 20:41

No.3 回答者： tatsumaru77 回答日時： 2022/08/23 13:18

マクロを下記URLにアップしました。

標準モジュールに登録してください。
https://ideone.com/lJK7qg

Public Sub CSV_重複件数()がCSVシートの重複件数設定です。
Public Sub 重複_重複件数()が重複①、重複②シートの重複件数設定です。

もし、重複③を追加する場合は、
Public Sub 重複_重複件数()の
sh_arr = Array("重複①", "重複②")を
sh_arr = Array("重複①", "重複②","重複③")
のようにしてください。

列を追加する場合は
col_arr = Array("G|I")を
col_arr = Array("G|I","○|●")
のようにします。
〇が重複データのある列
●が重複件数を出力する列になります。


追伸：
処理時間ですが、こちらで、1列のデータ件数を約３０万件として、実行すると
CSVシートが８秒、重複①②シートが４秒かかっています。
（windows10 メモリ12Gです）
もし、CSVシート、重複①、重複②シートに関数式が埋め込まれているなら、
その旨補足してください。
さらに速くなる可能性があります。

>来週あたりから104万件を超過すると思われます。
>その時初めて2シートに分割するのですが、104万件を超えた分を②にするのと、
>重複①50万件、重複②55万件、のようにキリの良いところで分けてほぼ均等にするのとではどちらが望ましいとかありますか？

１つのシートで大量のデータを埋め込むと、そのデータ処理時のメモリ使用量がかなり増えます。
メモリ容量がかなり潤沢にあれば問題ないかもしれませんが、潤沢でない場合は、
１つのシートのデータ件数が少ないほうが軽快に動作しますのでその方が良いと考えます。
（複数のシートになるので、その数ぶんの時間はかかりますが、１シートに大量に詰め込みすぎで動かなくなる
ことは避けられます）
従って、重複①50万件、重複②55万件のようにするのが良いかと思います。
それでも、時間がかかるなら、重複①40万件、重複②40万件、重複③25万件のようにしてトライする価値はあります。

この回答へのお礼

ありがとうございます！さっと動作確認した感じ、完璧です！
シート内に関数式は多用してますが、重くなるので手動計算にしてあります。
メモリは16GB積んでるのでメモリ不足はないかなと思っております。
複数シートに分ける点も理解しました。50万ずつシートを増やすのが全体パフォーマンスが良いのかなと思いました。
ありがとうございました。

お礼日時：2022/08/23 20:40

No.2 回答者： tatsumaru77 回答日時： 2022/08/23 08:43

補足要求です。

重複数をカウントする列がA列でその結果を出力する列がX列とします。
そのような組み合わせが３組あるということなので
A列の重複数の結果をX列へ表示
B列の重複数の結果をY列へ表示
C列の重複数の結果をZ列へ表示
とします。
以下、その前提での確認です。
１．重複①のシートのA列に"ABC"の文字が２件、
重複②のシートのA列に"ABC"の文字が３件、あった場合、
A列の重複件数は、５件となるのでしょうか。
（重複①と重複②をまとめて処理する）
それとも、重複①のシートのA列は２件、重複②のシートのA列は３件
となるのでしょうか。
（重複①と重複②を別々に処理する）

２．重複①のシートのA列に"ABC"の文字が２件、
重複①のシートのB列に"ABC"の文字が３件、
重複①のシートのC列に"ABC"の文字が４件、
あった場合、（重複②のシートのA,B,C列は０件とする）
A,B,C列をまとめて処理し、重複件数は９件とするのでしょうか。
それとも、A,B,C列を別々に処理し、
A列の重複件数は２件、B列の重複件数は３件、C列の重複件数は４件、
とするのでしょうか。

３．参考までに実際の重複①のデータ件数と重複②のデータ件数を教えていただけませんでしょうか。

４．実際のレイアウトは、どのようになっていますか。
A列の重複数の結果をX列へ表示
B列の重複数の結果をY列へ表示
C列の重複数の結果をZ列へ表示
の箇所の実際のA,B,C列、実際のX,Y,Z列の提示をお願いします。

この回答へのお礼

前回の質問回答まで見て頂いてありがとうございます！

> １．A列の重複件数は、５件となるのでしょうか。

はい。まとめて処理したいです。Excelの制限さえなければ1シート内でやりたかった事だからです。


> ２．A,B,C列をまとめて処理し、重複件数は９件とするのでしょうか。

いいえ。別々です。
例えばですが、A列は商品型番、B列は店舗、C列は担当者の様にそれぞれ異なった内容なので重なる事もないのですが、別々での重複数をカウントしたいです。
※例えばの例なので実際は違う項目名です。具体的な業務内容が漏れないようにしてます。


> ３．参考までに実際の重複①のデータ件数と重複②のデータ件数を教えていただけませんでしょうか。

先週のデータ数は約88万件、今週のデータ数は約95万件という所で、まだ1シートに収まってますが、
来週あたりから104万件を超過すると思われます。
その時初めて2シートに分割するのですが、104万件を超えた分を②にするのと、
重複①50万件、重複②55万件、のようにキリの良いところで分けてほぼ均等にするのとではどちらが望ましいとかありますか？


> ４．実際のレイアウトは、どのようになっていますか。

深く聞いてくださって恐縮です。ありがとうございます。
本当は、解決の手掛かりを掴めたら、その後の実際のカスタマイズ位は自分ですべきと思って、
分かりやすくABCの例で質問してましたが、せっかく聞いてくださったので実際の情報をお伝えします。
シートが2つあり、以下の通りです。
シート名「CSV」※システムからDLしたRAWデータ
　・AK列の重複数をAL列に表示
　・AN列の重複数をAO列に表示
　・AQ列の重複数をAR列に表示
シート名「重複」※RAWデータを加工して行数が3倍位に増加
　・G列の重複数をI列に表示

ここの「重複」シートがあふれそうなので重複①、重複②に分割せねばと思ってます。
「CSV」シートの方は増え方が少なく大丈夫と思ってましたが現在80万行位で、
10月までのこの案件が延長されると足りなくなるかも？と今書いていて思いました^^;
その時は「CSV」シートの方も同様の処理をせねば、と考えています。

お礼日時：2022/08/23 11:56

No.1 回答者： fujillin 回答日時： 2022/08/22 15:42

こんにちは

複数のシートをまとめて全体で同様の処理をなさりたいということでしょうか？

現状がどうなっているのか不明ですけれど、前半のDictionaryの作成を各シートで順に行い、その後、出力を各シートで順に行うようにすれば、考え方としては可能と思います。
（単に、データが分散しているだけと考えれば良いでしょう）

ただし、お断りしたように「速度を優先してメモリ使用が多い」方法になっています。
当方の環境では約1秒のところが、質問者様の環境では1分弱とのことでしたので、上記の考え方のままで実際に実行できるのかどうかは不明です。
（メモリオーバーなどが起きる可能性があるのではと危惧します）
多少なりともメモリを節約するなら、データを全シート分一気に読み込まずに、1シート分ずつ読んで処理をして解放するような方法をとることで、ごくわずかに処理時間は増加しますが、メモリ使用量はさほど増大しないものと想像します。

また、前回のご質問では複数列に対して同様の処理を繰り返している（？）ようですが、一つのシリーズ（＝Dictionary)について処理が終了してから、次の処理を行うようにする必要があると思われます。
（同時に必要な数だけのDictionaryを作成しておけば、並列で処理することは可能ですが、メモリ使用量は更に増大することになります。）


＞シート名の文頭が「重複～」のをすべて対象とできるなら
＞万が一3シートを超える事があっても対応できるのに・・・
上述のシートのループを、「全シートをループして対象のシートだけ処理する」ようにすれば可能でしょう。

とは言うものの、100万行を超えるようなデータをエクセルで扱おうとすること自体が、すでに限界を超えているように思われます。（前回は30万行とのことでしたので・・）
データベースなどを利用するほうが、シートの行数制約なども気にする必要がなくなるのではないでしょうか？

この回答へのお礼

ありがとうございます。
先に「Dictionary」に関して理解してないまま手法だけ拝借していて恐縮です。。。

> 「当方の環境では約1秒のところが、質問者様の環境では1分弱」
実際にはこちらは実情に合わせてカスタマイズした上での時間なので、
単純には比較できないと認識してます。
当初30万行で悩んでいたのがだんだん増えてきて100万行超に至ります。

ちなみにCPU：Core i7 1165G7、メモリ：16GBのノートを本作業専用に用意しています。
マクロ実行後、完了する迄放置できるので、メモリ節約は不要で速度優先です。現在約1時間で完了してるので根気強く待機してます。

> データベースなどを利用するほうが、シートの行数制約なども気にする必要がなくなるのではないでしょうか？
それは例えばAccessで実現できるしょうか？色々テストする時間が無いので困ってます。

お礼日時：2022/08/22 21:19