【Excel VBA】100万行以上あるCSVからユニーク数を集計する方法

CSVファイルの内容は、
ID、値1、値2、値3
の4項目だけですが、100万行以上あるため、エクセルのワークシートには取り込めません。
値3には重複している値が多数あって、ユニーク数は40万件ほどになります。
VBAでユニーク数を算出したいだけなのですが可能でしょうか？
重複削除したCSVを生成する必要はありません。
数を出力したいだけです。

ちなみにAccessでリンク取り込みして、値3をグループ化してカウントする方法で実現はしております。
しかし誰でも使えるようにしたいというのと、ExcelVBAでそこまでできるか知りたく質問しました。
あと件数が多いのでできるだけ処理が早く終わる手法だと助かります。
よろしくお願いいたします。

No.8 回答者： kmee 回答日時： 2019/08/18 00:39

そのCSVって、「元になる一つ」があって、それを各人ダウンロードしたりしているんですよね?

もしそうなら
・その「元になる一つ」が置いてあるPCで、CSVを作るときに同時に重複を数えるプログラムも実行して、結果を別ファイル(例えばcount.csv)に保存する。
→ 使う人は、Data.csvとcount.csv をダウンロードする

って方法にすれば、「数えるプログラム」は1つのPCで動作させればいいので、「誰でも」である必要はありません。


他にも、Data.csvに保存するのではなく、最初からデータベースサーバーに登録するようにすれば、「SQLの結果だけをExcelに取り込む」ようにVBAでも作れば、「誰でも動かせる」ようになります。


Data.csvを誰でも動かせるプログラムで、ということに固執するのではなく、いろんな方法を考えてみては?

No.7 回答者： bonaron 回答日時： 2019/08/17 08:36

ADO の例です。

Sub Sanple()
　Dim adoCON As Object
　Dim adoRS As Object
　Dim csvDir As String
　Dim strSQL As String

　Set adoCON = CreateObject("ADODB.Connection")
　Set adoRS = CreateObject("ADODB.Recordset")

　csvDir = ThisWorkbook.Path
　strSQL = "Select Count(*) As 件数 From (Select [値3] From Data.csv Group By [値3]) As tbl"

　adoCON.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
　　　　"Data Source=" & csvDir & ";" & _
　　　　"Extended Properties=""Text;" & _
　　　　"HDR=YES;" & _
　　　　"FMT=Delimited"""

　adoRS.Open strSQL, adoCON

　MsgBox adoRS(0) & "件です"

　adoRS.Close
　adoCON.Close
　Set adoRS = Nothing
　Set adoCON = Nothing
End Sub

Excel ファイルと同じフォルダに Data.csv がある場合です。
マシンスペックにもよるでしょうが、３,４秒位で可能です。

No.6 回答者： usa3usa 回答日時： 2019/08/17 07:11

＞あと件数が多いのでできるだけ処理が早く終わる手法だと助かります。

ならば、ExcelVBAでなく、CやC#で専用のプログラムを作成するのが良い気がします。

＞しかし誰でも使えるようにしたい
「当該のCSVファイルをドラッグするだけで答えが表示される」実行形式の専用プログラムを作成すれば良いだけかと・・

私が作るとしたら、
　４０万ｘ３程度のハッシュテーブルを用意して、
　未使用の印をつけ、
　CSVファイルを読み込み値３でハッシュテーブルに使用済みの印をつけ、
　読み終わったら、使用済みの印の数を数えて表示する
な感じのプログラムになるかな。メモリ管理が面倒そうなのでC#で作成。
処理内容からして、１，２秒で結果が表示できる程度かなと思います。

No.5 回答者： めぐみん_ 回答日時： 2019/08/17 06:19

No.3です。

『値３』の件数を知りたいだけなら、テキストボックス等に表示させるAccessのフォームを作成してボタンを押して実行の方が楽なのでは？
あとは頻度によりますがCSVファイルをAccessファイルにしてExcelから接続するようにするか。

Accessだと使用者が限られるってのは『Accessが使えない』のか『Accessがインストされてない』のかによりますし。

ネットワークでつながっているなら共有フォルダを作成しておき、変換したAccessファイルに接続するのも手では？
接続者数やネットワーク環境によって時間は変わるかもですけど、ただのテキスト形式の重いファイルで悩むよりかは多少マシな気もしますが。

この回答へのお礼

ありがとうございます。
集計はデイリーです。
あと Accessは全員のPCにはインストールされていません。

お礼日時：2019/08/17 08:20

No.4 回答者： tom04 回答日時： 2019/08/16 22:58

こんばんは！

A列～D列の重複データが何件存在するか？というコトで良いのですかね。
いずれにしてもExcelで100万行となるときついと思います。
無理やりやってみました。

元データはSheet1にあり1行目は項目行でデータは2行目以降にあり
Sheet2に表示するとします。

Sub Sample1()
　Dim myDic As Object
　Dim i As Long, lastRow As Long
　Dim wS As Worksheet, myStr As String
　Dim myKey, myItem, myR, myAry

　　Set myDic = CreateObject("Scripting.Dictionary")
　　Set wS = Worksheets("Sheet2")
　　　wS.Range("A:E").ClearContents
　　　　With Worksheets("Sheet1")
　　　　　wS.Range("A1:D1").Value = .Range("A1:D1").Value
　　　　　wS.Range("E1") = "件数"
　　　　　　lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
　　　　　　　myR = Range(.Cells(2, "A"), .Cells(lastRow, "D"))
　　　　　　　　For i = 1 To UBound(myR, 1)
　　　　　　　　　myStr = myR(i, 1) & "_" & myR(i, 2) & "_" & myR(i, 3) & "_" & myR(i, 4)
　　　　　　　　　If Not myDic.exists(myStr) Then
　　　　　　　　　　myDic.Add myStr, 1
　　　　　　　　　Else
　　　　　　　　　　myDic(myStr) = myDic(myStr) + 1
　　　　　　　　　End If
　　　　　　　　Next i
　　　　End With
　　　myKey = myDic.keys
　　　myItem = myDic.items
　　　　myR = Range(wS.Cells(2, "A"), wS.Cells(UBound(myKey) + 2, "E"))
　　　　　For i = 0 To UBound(myKey)
　　　　　　myAry = Split(myKey(i), "_")
　　　　　　　myR(i + 1, 1) = myAry(0)
　　　　　　　myR(i + 1, 2) = myAry(1)
　　　　　　　myR(i + 1, 3) = myAry(2)
　　　　　　　myR(i + 1, 4) = myAry(3)
　　　　　　　myR(i + 1, 5) = myItem(i)
　　　　Next i
　　　　Range(wS.Cells(2, "A"), wS.Cells(UBound(myKey) + 2, "E")) = myR
　　　　　Set myDic = Nothing
　　　　　wS.Activate
　　　　　MsgBox "完了"
End Sub

※　こちらで100万行程度のサンプルでマクロを実行すると
約3分かかってしまいました。m(_ _)m

この回答へのお礼

回答ありがとうございます。
移動中のスマホなので誤解なら申し訳ありませんが、
その内容で言うなら、D列の重複削除後の数が知りたいだけです。A〜C列は無視です。
更に100万行程度じゃないです。
1000万行でも対応可能なロジックじゃないと意味ありません。
すみません。

お礼日時：2019/08/17 01:03

No.3 回答者： めぐみん_ 回答日時： 2019/08/16 19:28

ADOでの件ならExcelからCSVファイルに接続する方法になるので、Excel側の行数は書き出す４０万件ほどを気にするだけかと思いますよ。

ただ誰にでも使えるようにって所が何を指しているのかちょっと不明かな？

この回答へのお礼

分かりづらくて済みません。
現場が分からないと伝わらないですね。

Accessを使う方法だと、利用者が限られるのです。
Excel上でボタンをクリックするだけだと環境的にもスキル的にも(こちらの社内では)誰でも集計できるようになるんです。

お礼日時：2019/08/16 22:11

No.2 回答者： tatsu99 回答日時： 2019/08/16 19:19

値３の重複しない件数がわかれば良いのですね。

そうであれば、Scripting.Dictionaryを使用するのが簡単かと。
以下の例は、使用例です。
例１
"A", "B", "C", "D", "E", "A", "B", "C"の重複しない件数を出力する。
結果：6
例２
１～１００００００の数字の重複しない件数を出力する。
結果：１００００００

実際の手順としては
１．CSVファイルを１行読み込み、値3を取得する。
２．値３をキーとしてDictionaryへ登録する。値はtrue固定
３．これを全行分（１００万行）、繰り返す。
４．DictionaryのCountプロパティに件数が格納されているので、それを出力する。
ことになるかと。

追伸：例２を実行すると、さすがに重くなりますが、当マシンでは、約３０秒で完了しました。
（windows7 メモリ１２GB　core5 ）
10GB以上のメモリを積んでいれば、大丈夫かと思います。
-------------------------------------------
Public Sub sample()
Dim dict As Object
Dim i As Long
Dim val As Variant
Dim key As Variant
val = Array("A", "B", "C", "D", "E", "A", "B", "C")
Set dict = CreateObject("Scripting.Dictionary")
For i = 0 To UBound(val)
key = val(i)
dict(key) = True
Next
Debug.Print dict.Count
dict.RemoveAll '一旦、全てクリアする
For i = 1 To 1000000
dict(i) = True
Next
Debug.Print dict.Count
End Sub

No.1 回答者： ShowMeHow 回答日時： 2019/08/16 14:30

ADOなどで、エクセルに接続して、SQLでselect distinct...　がはやいんじゃないかな？

group byもためしてみてもよいけど、、、

https://qiita.com/EmikoKishi/items/7c5e4154cf044 …

この回答へのお礼

VBAから直接CSVを操作しないといけないと思うのですが、
Excelに接続するという事は104万件の壁があるから駄目ではあませんか？
理解が追いついてなかったらすみません。

お礼日時：2019/08/16 14:34