[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ループのやり方がわからない』（紫電改）
下記のマクロは、集計というCSVファイル（7万行程度）から、抽出に必要な事柄 だけにして、フィルタで不要なものを取り、デイリーというファイルに取り込ん でいます。 '*****から'******までの間の今のやり方を、ループに変えてやってみたいのですが、 行き詰まってしまいました。 ループでやるとのと、今のやり方とではどちらが処理が速いですか？ また、毎日の仕事なのでより良い方法をと思っています。 全体を見渡してのご感想、ご指摘をよろしくお願いします。
Sub test1()
Sheets("buf").Cells.ClearContents
ChDir "C:\Users\PC\Desktop\data"
Workbooks.Open Filename:="C:\Users\PC\Desktop\data\集計.CSV"
Range("A1").AutoFilter Field:=3, Criteria1:="="
Range("A1").AutoFilter Field:=4, Criteria1:="=????????", Operator:=xlAnd
Range("A1").AutoFilter Field:=8, Criteria1:="<>"
Range("A1").AutoFilter Field:=15, Criteria1:="="
Range("A1").AutoFilter Field:=33, Criteria1:="="
Range("A1").AutoFilter Field:=31, Criteria1:="<>"
Set shtA = Workbooks("集計.CSV").Worksheets("集計")
Set shtB = Workbooks("デイリー.xlsm").Worksheets("buf")
With shtA
n = .UsedRange.Cells(.UsedRange.Cells.Count).Row
.Range("M1").Resize(n).Copy shtB.Range("A1")
.Range("D1").Resize(n).Copy shtB.Range("B1")
.Range("G1").Resize(n).Copy shtB.Range("C1")
.Range("K1").Resize(n).Copy shtB.Range("D1")
.Range("J1").Resize(n).Copy shtB.Range("E1")
.Range("H1").Resize(n).Copy shtB.Range("F1")
.Range("BN1").Resize(n).Copy shtB.Range("G1")
.Range("CD1").Resize(n).Copy shtB.Range("H1")
.Range("P1").Resize(n).Copy shtB.Range("I1")
.Range("AN1").Resize(n).Copy shtB.Range("J1")
.Range("Q1").Resize(n).Copy shtB.Range("K1")
.Range("F1").Resize(n).Copy shtB.Range("L1")
.Range("CC1").Resize(n).Copy shtB.Range("M1")
.Range("F1").Resize(n).Copy shtB.Range("N1")
shtA.Parent.Close savechanges:=False
End With
shtB.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
FieldInfo:=Array(1, 5)
Columns("A:A").NumberFormatLocal = "m/d""(""aaa"")"""
shtB.Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
FieldInfo:=Array(1, 5)
Columns("F:F").NumberFormatLocal = "m/d""(""aaa"")"""
shtB.Columns("I:I").TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
FieldInfo:=Array(1, 5)
Columns("I:I").NumberFormatLocal = "m/d""(""aaa"")"""
Sheets("buf").Select
With ActiveSheet.UsedRange
.Value = Application.Trim(.Value)
End With
b = shtB.Range("A" & Rows.Count).End(xlUp).Row
c = c + 15
Range(Cells(2, c), Cells(b, c)) = "=IF(I2="""","""",""★"")"
Range(Cells(2, c), Cells(b, c)).Value = Range(Cells(2, c), Cells(b, c)).Value
Cells(1, c) = "ABC"
c = c + 1
Range(Cells(2, c), Cells(b, c)) = "=O2&J2&K2"
Range(Cells(2, c), Cells(b, c)).Value = Range(Cells(2, c), Cells(b, c)).Value
Cells(1, c) = "DEF"
c = c + 1
Range(Cells(2, c), Cells(b, c)) = "=IF(ISERROR(VLOOKUP(O2,型番DB!M:N,2,FALSE)),"""",(VLOOKUP(O2,型番DB!M:N,2,FALSE)))"
Range(Cells(2, c), Cells(b, c)).Value = Range(Cells(2, c), Cells(b, c)).Value
Cells(1, c) = "GHI"
c = c + 1
Range(Cells(2, c), Cells(b, c)) = "=IF(ISERROR(VLOOKUP(L2,型番DB!A:F,6,FALSE)),"""",(VLOOKUP(L2,型番DB!A:F,6,FALSE)))"
Range(Cells(2, c), Cells(b, c)).Value = Range(Cells(2, c), Cells(b, c)).Value
Cells(1, c) = "JKL"
With shtB
n = .UsedRange.Cells(.UsedRange.Cells.Count).Row
.Range("A1").Resize(n).Copy shtC.Range("A1")
.Range("Q1").Resize(n).Copy shtC.Range("B1")
.Range("B1").Resize(n).Copy shtC.Range("C1")
.Range("N1").Resize(n).Copy shtC.Range("D1")
.Range("C1").Resize(n).Copy shtC.Range("E1")
.Range("D1").Resize(n).Copy shtC.Range("F1")
.Range("E1").Resize(n).Copy shtC.Range("G1")
.Range("F1").Resize(n).Copy shtC.Range("H1")
.Range("H1").Resize(n).Copy shtC.Range("I1")
.Range("G1").Resize(n).Copy shtC.Range("J1")
End With
End Sub
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
が、7万件もあるならば、DBでインポートして、任意列だけ抽出するSQL（MS-Accessならクエリで十分）1行が良いかと…。
（???） 2015/06/10(水) 15:18
???さんありがとうございます。 ＞DBでインポートして、任意列だけ抽出するSQL（MS-Accessならクエリで十分）
お答えいただいた事柄が、私のレベルでは理解できませんでした。勉強不足ですみません。
下記の部分だけでもループのご指導いただけないでしょうか？
b = shtB.Range("A" & Rows.Count).End(xlUp).Row
c = c + 1
Range(Cells(2, c), Cells(b, c)) = "=IF(ISERROR(VLOOKUP(O2,型番DB!M:N,2,FALSE)),"""",
(VLOOKUP (O2,型番DB!M:N,2,FALSE)))"
Range(Cells(2, c), Cells(b, c)).Value = Range(Cells(2, c), Cells(b, c)).Value
Cells(1, c) = "GHI"
（紫電改） 2015/06/10(水) 20:50
CSVファイルをどう扱うかは、ちょっと横において、 ・どのようなレイアウトのシートから、何を（どんな条件のものを） ・どのようなシートのどこに転記したいか。
それを、コードではなく言葉で、要件として整理して説明されてはいかがですか？
（β） 2015/06/10(水) 21:36
ですが、考え方だけ伝えます。1列ずつまとめて一括コピーはかなり速そうですが、今後更にデータが
増えたときでも同じように実行できるかは疑問です。この方法ならば、行コピーでもして、
もっと多いデータでの動作検証をしておきたいところですね。
それと、計算式を埋めていますが、コピー後のデータを書き換える可能性はありますか？
書き換えないならば、計算はVBAで行ってしまい、値だけセットするほうが、シートが軽い事でしょう。
また、マクロの前後で、表示更新と自動計算をFalse/Trueして、セット中に余分なイベントが発生しないようにしましょう。
（???） 2015/06/11(木) 08:56
>>下記の部分だけでもループのご指導いただけないでしょうか？
探求心旺盛なのは敬服ですが、7万行のセルに対して行ごとに、式を埋め込むと書き込み回数は7万回。 一方、7万行の領域に式をいれると、書き込み回数は1回。もちろん、1回あたりの書きこむデータ量は増えますが エクセル上では、1回あたりの書き込み行為自体が大きな負荷を持ちます。ここはループでやるべきではありません。
それとは別に、式を入れ、その結果を値にするわけですよね。しかも、採用している式は、マッチするとすれば ２回、VLOOKUP が行われます。お気づきでしょうか？ 10行程度ならなんてことはないでしょうが、7万行あれば、（すべてマッチすれば） 14万回の計算をエクセルにさせることになります。
処理効率を考えるなら、
・マッチングすべきリストを、Dictinaryに格納しておき ・O列の値とのマッチング結果を配列にセットし ・最後に配列から、B列に一括して落とし込む
こういった方法を検討すべきだと思います。
（β） 2015/06/11(木) 10:13
βさん、???さん数々のご意見ありがとうございます。
＞CSVファイルをどう扱うかは、ちょっと横において、 ・どのようなレイアウトのシートから、何を（どんな条件のものを） ・どのようなシートのどこに転記したいか。
それを、コードではなく言葉で、要件として整理して説明されてはいかがですか？
そのとおりですね。
・マッチングすべきリストを、Dictinaryに格納しておき ・O列の値とのマッチング結果を配列にセットし ・最後に配列から、B列に一括して落とし込む
こういった方法を今習得している最中で、是非とも自分のものにしたいと考えています。
もう一度、整理して仕切り直ししたいと思います。
（紫電改） 2015/06/13(土) 08:05
Sub test()
Dim dic As Object
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim v2, v1
Dim i As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set dic = CreateObject("scripting.dictionary")
v2 = ws2.Range("a1").CurrentRegion.Resize(, 2).Value
For i = 1 To UBound(v2, 1)
dic(v2(i, 1)) = v2(i, 2)
Next
With ws1.Range("a1").CurrentRegion
.Columns(2).ClearContents
v1 = .Columns(1).Value
End With
For i = 1 To UBound(v1, 1)
v1(i, 1) = dic(v1(i, 1)) '★
Next
ws1.Range("b1").Resize(UBound(v1, 1)).Value = v1
End Sub
またオートフィルタを使用していますが、
フィルタオプションのほうが楽かもかも知れません。
（マナ） 2015/06/14(日) 10:52 ★修正11:15
↑ Dictionaryの使い方とは関係ありませんが 前スレでは全角半角の件がありますので 動作確認には、★の行を
v1(i, 1) = dic(StrConv(v1(i, 1), vbNarrow))
で試して下さい。
（マナ） 2015/06/14(日) 14:21
マナさん、参考になります。ありがとうございました。
（紫電改） 2015/06/15(月) 09:51
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.