VBAで以下のような処理を考えているのですが、処理を高速化できる方法を教えて頂きたいです。
1.CSVファイルのデータを読み込む(添付資料のような感じです)
--以下CSVファイルのCODE1-3を結合したコード順にループ
2.シートAにCSVファイルのCODE1-3を結合したコードが存在するかチェック
存在した場合は4-aに遷移
3.シートAにCSVのデータを1行分出力
4.シートBに3で出力したデータを出力
a.シートAに同一CODEが存在し、シートAのNAMEが異なっていた場合
区分を”変更”にし、CODEと読込前のシートAのNAMEと読込後のNAMEを出力
b.シートAに同一コードがなかった場合
区分を”新規”にし、CODEとNAMEを出力
という処理で、
1はFileSystemObjectオブジェクトを利用し、一旦仮のシートにCSVシートの全データを出力し
2と4-aにWorksheetFunction.Vlookupを利用
としたのですがその都度再計算が発生してしまうので
大量データだととても遅いです。(多分30分以上処理がかかっています)
そのため、上記処理で1万件前後のデータを処理した場合に
比較的処理が早く済む方法を教えて頂きたいです。
この場合、1の段階でdictionaryオブジェクトによる仮想配列を利用した方が以降のチェック処理が早くなるのでしょうか。
(ただ、その場合 KEY =CODE1-3,VALUE = CSVの他項目となると思うのですが
その後VALUEを呼び出した場合、各項目ごとに分けてシートAやBにデータを入れる方法のイメージがついていません…)
なお、読込CSVファイルのフォーマット変更は不可です。
こういう処理を入れこんだ方がいい等というアイデアを教えて頂けると助かります。
---追記
CSVファイルは計7項目あり、CODE3とFLAG3は空白の可能性があります。
FLAG3は空白であればシートに出力しないまたはそのまま空白を出力する形式になります。
なお、1つのCSVファイル内ではCODE1-3を結合した結果は一意になっており重複することはありません。
また、CODE1-3は結合した場合、(数値の並びとして)昇順になるよう出力されています。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答7件
0
dictionaryオブジェクトを使った方が圧倒的に速いです。
以下は、使い方のサンプルです。
キーは、CODE1,2,3の連結ですが、間に、絶対に使われない文字をデリミタとして入れます。
例では、|ですが、カンマとかアンダーバーとかでも構いません。
何故かというと、
CODE1=1,CODE2=12,CODE3=(空白文字)
の場合、キーは112ですが、
CODE1=1,CODE2=1,CODE3=2
の場合も、キーは112となり区別できないからです。
これは、デリミタを入れてキーをつくれば回避できます。
キー:1|12| と
キー:1|1|2 は区別できます。
valueに何を入れるかですが、項目をまるまる入れることはすすめません。
もし、いれるなら、デリミタをつけて、valueへ格納してください。
そうすると、valueをそのデリミタでsplitすれば、個々の項目に分割できます。
私がすすめるのは、シートの行番号を格納する方法です。
例ではシートBの行番号ですが、
(たぶんそれで間に合うと思いますが、シートAの行番号もほしいなら、新たにdict2のディクショナリを
作成し、そこれシートAの行番号を格納し、管理すれば良いです)
その為、セルにアクセスする場合は、
アクティブシートでのセルの指定ではなく、シート名を指定したセルの指定をするようにしてください。
VBA
1Public Sub test() 2 Dim dicT As Object 3 Dim elm As Variant 4 Dim welm As Variant 5 Dim str As String 6 Dim key As Variant 7 Dim row2 As Long 8 Dim wrow2 As Long 9 Dim i As Long 10 Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義 11 Line = "1,2,,山田,1,2,1" 12 'カンマで分割 13 elm = Split(Line, ",", -1) 14 'キーを作成 15 key = elm(0) & "|" & elm(1) & "|" & elm(2) 16 'キーが存在するかチェック 17 If dicT.exists(key) = False Then 18 'SheetBへ出力、出力した行はrow2に記録するとすると 19 dicT(key) = row2 'そのキーの行番号を記憶しておく 20 row2 = row2 + 1 21 Else 22 'SheetBにあるので、それが、どの行にあるかを取得する 23 wrow2 = dicT(key) 'row2は順番に1ずつアップするので使用しない 24 'その行が求まったので、その行を処理する 25 End If 26 'もし、keyの内容をばらして使いたいなら、以下のようにする 27 welm = Split(key, "|", -1) 28End Sub 29
投稿2019/01/19 04:25
編集2019/01/19 04:26総合スコア5438
0
頂いたコードを実行した後だと、ダブルクオーテーションが残っているので削除したいために入れました。
このコードで、自分の環境で試しに400件程度のデータで実行したところ1.5 分程度で実行になりました。
また、ElseIfの部分は、
シート1に既にCODEが存在していて、かつそのCODEの行にあるNAMEと読みこんだNAMEが一致しない場合に>Then以降の処理をできるようにしたいと思っています。
これを反映したソースを再提示します。
ダブルクートの削除はキーのみ行いますelm(0),elm(1),elm(2)
ファイルの読み込みのところはあなたの環境に修正して下さい。
VBA
1Option Explicit 2 3Public Sub CSV取込み() 4 Dim dicA As Object 5 Dim dicB As Object 6 Dim maxrow1 As Long 7 Dim maxrow2 As Long 8 Dim row1 As Long 9 Dim row2 As Long 10 Dim sh1 As Worksheet 11 Dim sh2 As Worksheet 12 Dim fileNo As Long 13 Dim fname As String 14 Dim text As String 15 Dim elm As Variant 16 Dim key As String 17 Dim t1, t2 18 t1 = Time 19 Set dicA = CreateObject("Scripting.Dictionary") ' 連想配列の定義 20 Set dicB = CreateObject("Scripting.Dictionary") ' 連想配列の定義 21 Set sh1 = Worksheets("SheetA") 22 Set sh2 = Worksheets("SheetB") 23 Application.ScreenUpdating = False 24 25 maxrow1 = sh1.Cells(Rows.Count, "B").End(xlUp).Row 'sheetの最大行取得 26 If maxrow1 < 3 Then maxrow1 = 3 27 maxrow2 = sh2.Cells(Rows.Count, "B").End(xlUp).Row 'sheetの最大行取得 28 If maxrow2 < 3 Then maxrow2 = 3 29 'dictionaryの事前作成 30 For row1 = 4 To maxrow1 31 key = sh1.Cells(row1, "B").Value 32 dicA(key) = row1 33 Next 34 For row2 = 4 To maxrow2 35 key = sh2.Cells(row2, "B").Value 36 dicB(key) = row2 37 Next 38 39 'ファイルオープン 40 fname = "D:\goo\excel\goo401\data3.csv" 41 fileNo = FreeFile '空き番号取得 42 Open fname For Input As #fileNo 43 'ファイル終端まで読み込む 44 Do Until EOF(fileNo) 45 Line Input #fileNo, text 46 'カンマで分割 47 elm = Split(text, ",", -1) 48 key = elm(0) & elm(1) & elm(2) 49 key = Replace(key, """", "") 50 If dicA.exists(key) = False Then 51 'SheetAに未登録ならSheetA,SheetBに登録 52 maxrow1 = maxrow1 + 1 53 sh1.Cells(maxrow1, "B").Value = key ' 54 sh1.Cells(maxrow1, "C").Value = elm(3) 'NAME 55 sh1.Cells(maxrow1, "D").Value = elm(4) 'FLAG1 56 sh1.Cells(maxrow1, "E").Value = elm(5) 'FLAG2 57 sh1.Cells(maxrow1, "F").Value = elm(6) 'FLAG3 58 dicA(key) = maxrow1 59 maxrow2 = maxrow2 + 1 60 sh2.Cells(maxrow2, "B").Value = key ' 61 sh2.Cells(maxrow2, "C").Value = "" 'OLDNAME 62 sh2.Cells(maxrow2, "D").Value = elm(3) 'NEWNAME 63 sh2.Cells(maxrow2, "E").Value = "新規" '区分 64 dicB(key) = maxrow2 65 Else 66 If sh1.Cells(row1, "C").Value <> elm(3) Then 67 'SheetAに登録済みならSheetBを更新 68 row1 = dicA(key) 69 row2 = dicB(key) 70 sh2.Cells(row2, "C").Value = sh1.Cells(row1, "C").Value 'OLDNAME 71 sh2.Cells(row2, "D").Value = elm(3) 'NEWNAME 72 sh2.Cells(row2, "E").Value = "更新" '区分 73 End If 74 End If 75 Loop 76 Close #fileNo 77 t2 = Time 78 Application.ScreenUpdating = True 79 MsgBox ("完了 所要時間=" & Format(t2 - t1, "n分s秒")) 80End Sub 81
投稿2019/01/21 12:25
総合スコア5438
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2019/01/21 13:54 編集
2019/01/21 13:59
0
ベストアンサー
サンプルソースです。
ファイルの読み込みは、ファイル名を固定にしているのでそこだけあなたの環境に合わせてください。
シートAは、"SheetA"
シートBは、"SheetB"
をしようしています。
レイアウトは提示された画像通りです。
先に出したkeyの型はVriantでしたが、Variantだと今回は(デリミタなしの為)キーに数値が入ってしますのでうまくいきません。
明示的にstring型にしました。(dictionaryは文字列がキーなので)
VBA
1Option Explicit 2 3Public Sub CSV取込み() 4 Dim dicA As Object 5 Dim dicB As Object 6 Dim maxrow1 As Long 7 Dim maxrow2 As Long 8 Dim row1 As Long 9 Dim row2 As Long 10 Dim sh1 As Worksheet 11 Dim sh2 As Worksheet 12 Dim fileNo As Long 13 Dim fname As String 14 Dim text As String 15 Dim elm As Variant 16 Dim key As String 17 Dim t1, t2 18 t1 = Time 19 Set dicA = CreateObject("Scripting.Dictionary") ' 連想配列の定義 20 Set dicB = CreateObject("Scripting.Dictionary") ' 連想配列の定義 21 Set sh1 = Worksheets("SheetA") 22 Set sh2 = Worksheets("SheetB") 23 Application.ScreenUpdating = False 24 25 maxrow1 = sh1.Cells(Rows.Count, "B").End(xlUp).Row 'sheetの最大行取得 26 If maxrow1 < 3 Then maxrow1 = 3 27 maxrow2 = sh2.Cells(Rows.Count, "B").End(xlUp).Row 'sheetの最大行取得 28 If maxrow2 < 3 Then maxrow2 = 3 29 'dictionaryの事前作成 30 For row1 = 4 To maxrow1 31 key = sh1.Cells(row1, "B").Value 32 dicA(key) = row1 33 Next 34 For row2 = 4 To maxrow2 35 key = sh2.Cells(row2, "B").Value 36 dicB(key) = row2 37 Next 38 39 'ファイルオープン 40 fname = "D:\goo\excel\data.csv" 41 fileNo = FreeFile '空き番号取得 42 Open fname For Input As #fileNo 43 'ファイル終端まで読み込む 44 Do Until EOF(fileNo) 45 Line Input #fileNo, text 46 'カンマで分割 47 elm = Split(text, ",", -1) 48 key = elm(0) & elm(1) & elm(2) 49 If dicA.exists(key) = False Then 50 'SheetAに未登録ならSheetA,SheetBに登録 51 maxrow1 = maxrow1 + 1 52 sh1.Cells(maxrow1, "B").Value = key ' 53 sh1.Cells(maxrow1, "C").Value = elm(3) 'NAME 54 sh1.Cells(maxrow1, "D").Value = elm(4) 'FLAG1 55 sh1.Cells(maxrow1, "E").Value = elm(5) 'FLAG2 56 sh1.Cells(maxrow1, "F").Value = elm(6) 'FLAG3 57 dicA(key) = maxrow1 58 maxrow2 = maxrow2 + 1 59 sh2.Cells(maxrow2, "B").Value = key ' 60 sh2.Cells(maxrow2, "C").Value = "" 'OLDNAME 61 sh2.Cells(maxrow2, "D").Value = elm(3) 'NEWNAME 62 sh2.Cells(maxrow2, "E").Value = "新規" '区分 63 dicB(key) = maxrow2 64 Else 65 'SheetAに登録済みならSheetBを更新 66 row1 = dicA(key) 67 row2 = dicB(key) 68 sh2.Cells(row2, "C").Value = sh1.Cells(row1, "C").Value 'OLDNAME 69 sh2.Cells(row2, "D").Value = elm(3) 'NEWNAME 70 sh2.Cells(row2, "E").Value = "更新" '区分 71 End If 72 Loop 73 Close #fileNo 74 t2 = Time 75 Application.ScreenUpdating = True 76 MsgBox ("完了 所要時間=" & Format(t2 - t1, "n分s秒")) 77End Sub 78
投稿2019/01/19 10:57
編集2019/01/19 11:11総合スコア5438
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2019/01/19 12:53
退会済みユーザー
2019/01/21 10:41
2019/01/21 11:37
退会済みユーザー
2019/01/21 12:05 編集
0
先にdictionaryオブジェクトを使う旨の回答をしたものです。
今回は、シートAもシートBも継続して存続し、CSVファイルを逐次読込、それを反映していくということなので、
dictionaryは2つ作成する形になります。又、マクロの起動時に、シートA、Bを最後の行まで読み込み
dictionaryを作成しておく必要があります。それを考慮すると、デリミタを付けないキーのほうが、今回は良いです。
あなたが望まれるなら、具体的なコードを提示することは可能ですが、
実際のレイアウトもあなたが提示された画像の通りで間違いないでしょうか。
提示したあと、実はレイアウトが違っていましたということになると、2度手間になりますので。
投稿2019/01/19 09:11
総合スコア5438
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2019/01/19 10:19
0
いろいろ不足してたり、あやふやな部分がありますが、
3つの画像から、推測を加えてまとめると、下記のような感じでしょうか。
①CSVファイルの仕様
CODE1, CODE2 は1桁の数字
CODE3 は空白か1桁以上の数字
これを結合してキーとすると、重複はなし。
②これを結合したキーで昇順にして取り込む。
このとき、並び順はテキスト順(数値としての昇順ではない)。
③シートA の仕様
CODEは、 CSV の CODE1 & CODE2 & CODE3 に相当する。
④シートA には、すでにデータがあり(以前に取り込んだもの?)、
読み込んだCSVのデータをその後に追加していく。
ただし、既にあるCODEのデータは追加しない。
(この推測であってますか。この辺があやふや)
⑤シートBは、
CSVから読み込んだデータのCODEとNAME(NEWNAME列に)を出力
シートAに同じCODEがあった場合は、そのNAMEをOLDNAME列に出力
TYPE列は、シートAに同じCODEがある場合は「更新」、ない場合は「新規」
以上であってますか。異なる部分があればご指摘ください。
追記
列を結合したり、それで並べ替えたりという操作があるので、
私なら、ADOを使って取り込みますね。
SQLで簡単にデータを加工して、それをシートに貼り付けることができますので。
下記を参考にしてください。
ADOを使って、CSVファイルを読み込む - VBA - TIL
下記のようなSQLを実行して、
vba
1 sSql = "SELECT CODE1 & CODE2 & CODE3 AS CODE, NAME, FLAG1, FLAG2, FLAG3 " & _ 2 "FROM [tera169663.csv] ORDER BY 1" 3
取得したレコードセットを CopyFromRecordset で作業用シート([TempSheet])に貼り付けます。
シート上のデータもADOでテーブルとして扱えますので、SQLで取得、加工できます。
Excelファイルに接続(ADO) | ExcelWork.info
シートA と 作業用シート(取り込んだCSVデータ) の不一致データをSQLで抽出して、それをシートAに追記します。
不一致データの取得は下記のようなSQLです(いわゆる不一致クエリというものです)。
vba
1 sSql = "SELECT T.CODE, T.NAME, T.FLAG1, T.FLAG2, T.FLAG3 " & _ 2 "FROM [TempSheet$] AS T LEFT JOIN [シートA$B3:F] AS A " & _ 3 "ON T.CODE = A.CODE " & _ 4 "WHERE A.CODE IS NULL;" 5
シートBに出力するデータは下記のSQL文になります。
vba
1 sSql = "SELECT T.CODE, A.NAME AS OLDNAME, T.NAME AS NEWNAME, " & _ 2 "IIf(A.CODE IS NULL,'新規','更新') AS TYPE " & _ 3 "FROM [TempSheet$] AS T LEFT JOIN [シートA$B3:F] AS A " & _ 4 "ON T.CODE = A.CODE;" 5
シート全体のデータを取り込む場合は、[TempSheet$] というように シート名$ とします。
範囲を指定する場合は、[シートA$B3:F] というように$のあとに範囲を指定します。
[シートA$B3:F] というのは、シートA の B列からF列の3行目からデータの最後の行まで読み込みます。
投稿2019/01/19 05:26
編集2019/01/19 09:58総合スコア33699
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2019/01/19 06:18 編集
2019/01/19 06:28 編集
退会済みユーザー
2019/01/19 07:32
2019/01/19 08:18
退会済みユーザー
2019/01/19 10:21
0
こういう処理を入れこんだ方がいい等というアイデアを教えて頂けると助かります。
コード番号を使いまわさず、
必ずユニークな番号を使うようにすれば、
このような作業は必要なくなりますが、
そういうのは、出来ないということですか?
あと、例えば、同じコードで、
名前が3回、4回と更新されることはありますか?
そのときのデータはどのように管理されますか?
で、チェックしたときは更新直前の名前を表示するのですか?
投稿2019/01/19 09:47
編集2019/01/19 09:48総合スコア2136
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2019/01/19 10:23
0
SheetAのレイアウトは添付図のようなものであってますか。
あなたが考えているSheetBのレイアウトがよくわかりません。
私が提示したような画像で提示していただけると助かります。
特にあなたが提示されている
「a.シートAに同一CODEが存在し、シートAのNAMEが異なっていた場合
区分を”変更”にし、CODEと読込前のシートAのNAMEと読込後のNAMEを出力」
の内容がよくわかりません。具体的に出力した内容を提示していただけると助かります。
上記の「同一CODEが存在し」とは、CODE1,CODE2,CODE3を結合したCODEと理解しての前提ですが
もし、
CODE1,CODDE2,CODE3,NAMEで
1,1,1,阿部
1,1,1,安藤
1,1,1,山中
のデータがあると、
区分:変更
NAME欄は、「阿部、安藤、山中」が出力されることを望まれているのでしょうか?
それとも最初と最後の結果、即ち「阿部、山中」を望まれているのでしょうか?
投稿2019/01/19 03:29
総合スコア5438
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2019/01/19 03:56
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。