Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
7723 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【36749】データを取得する便利なマクロですが、処理を速くするには？ カド 06/4/12(水) 14:43 質問[未読]
【36750】Re:データを取得する便利なマクロですが、... Statis 06/4/12(水) 14:53 発言[未読]
【36751】Re:データを取得する便利なマクロですが、... カド 06/4/12(水) 14:58 発言[未読]
【36753】Re:データを取得する便利なマクロですが、... Statis 06/4/12(水) 15:08 回答[未読]
【36754】Re:データを取得する便利なマクロですが、... カド 06/4/12(水) 15:37 お礼[未読]
【36756】Re:データを取得する便利なマクロですが、... Statis 06/4/12(水) 15:57 回答[未読]
【36757】Re:データを取得する便利なマクロですが、... カド 06/4/12(水) 16:37 お礼[未読]
【36760】Re:データを取得する便利なマクロですが、... Statis 06/4/12(水) 17:01 回答[未読]
【36763】超高速処理でしたよ。なんてね。 カド 06/4/12(水) 17:52 お礼[未読]
【36764】蛇足でしたらごめんなさい。 Ned 06/4/12(水) 20:13 発言[未読]
【36771】Re:蛇足でしたらごめんなさい。 カド 06/4/13(木) 6:13 お礼[未読]
【36772】ほんとに超高速でした。 カド 06/4/13(木) 6:53 お礼[未読]
【36796】やはり１秒で処理できました。 カド 06/4/14(金) 8:40 お礼[未読]
【37191】大きな問題があることに気づきました。 カド 06/4/25(火) 15:23 質問[未読]
【37197】Re:大きな問題があることに気づきました。 Ned 06/4/25(火) 16:18 発言[未読]
【37223】Re:大きな問題があることに気づきました。 Ned 06/4/26(水) 10:05 発言[未読]
【37247】Re:大きな問題があることに気づきました。 カド 06/4/26(水) 21:34 お礼[未読]
【37249】こちらこそ。勉強させて頂きました^ ^ Ned 06/4/26(水) 23:10 発言[未読]
【36752】Re:データを取得する便利なマクロですが、... Kein 06/4/12(水) 15:00 回答[未読]
【36755】Re:データを取得する便利なマクロですが、... カド 06/4/12(水) 15:41 お礼[未読]
【36758】Re:データを取得する便利なマクロですが、... Kein 06/4/12(水) 16:51 回答[未読]
【36762】Re:データを取得する便利なマクロですが、... カド 06/4/12(水) 17:22 お礼[未読]
【36749】データを取得する便利なマクロですが、処...
質問  カド  - 06/4/12(水) 14:43 -
引用なし
パスワード
   私が一番活用しているマクロですが、処理数を増やしたら、すごく処理が遅くなりました。
原始的なコードを書くのが好き（というよりそれしか書けない）なのですが、これじゃあ実用に耐えません。

１．処理速度を上げるには、どう変更したら良いのでしょうか？
２．処理数が50,000個だとＰＣが固まってしまいます。
　　遅いのは分かるのですが、固まるという現象はどうして起きるのでしょうか？

****************************
アクティブセルと同じ値のセルを、アクティブセルの３つ左の列から探し出し、探しだしたセルの右にある値をアクティブセルの右に書き出すマクロです。


Sub データ取得1個()
　　Dim Count, aaa As Long
　　
　　
　　Set ACELL = ActiveCell '検索元
　　Set BCELL = ActiveCell.Offset(0, -3) '検索先
　　
　　aaa = 50000　　’←処理数が2000ならまったく問題ない。
　　
　　Count = 0
　　Count1 = 0
　　
　　Do Until Count1 = aaa '検索元を１００行実行
　　
　　　　Do Until Count = aaa 'ﾃﾞｰﾀベース（検索先）を１００行検索

　　　　　　If ACELL = BCELL Then '見つかった場合
　　　　　　　　ACELL.Offset(0, 1) = BCELL.Offset(0, 1) 'ﾃﾞｰﾀの書き込み
　　　　　　　
　　　　　　　　Count = 0 '検索先のﾃﾞｰﾀ検索数
　　　　　　　　Exit Do
　　　　　　Else '見つからなかった場合
　　　　　　　　Set BCELL = BCELL.Offset(1, 0) '検索先を次に変更
　　　　　　　　Count = Count + 1 '検索先のﾃﾞｰﾀ検索数
　　　　　　　　
　　　　　　End If
　　　　Loop
　　　　
　　　 Set ACELL = ACELL.Offset(1, 0)
　　　 Set BCELL = ActiveCell.Offset(0, -3)
　　　
　　　Count1 = Count1 + 1 '検索元のﾃﾞｰﾀ検索数
　　　Count = 0
　　Loop

MsgBox aaa & "個処理が終了しました。"
End Sub
【36750】Re:データを取得する便利なマクロですが...
発言  Statis  - 06/4/12(水) 14:53 -
引用なし
パスワード
   こんにちは
検索で実行して方が良いのでは？
【36751】Re:データを取得する便利なマクロですが...
発言  カド  - 06/4/12(水) 14:58 -
引用なし
パスワード
   ▼Statis さん　回答ありがとうございます。

>検索で実行して方が良いのでは？

すみません。おっしゃる意味が分からないので、
もう少し具体的に教えて頂きたいのですが。

検索関数を使ったらということでしょうか？
いずれにしてもコードで示していただけたらありがたいのですが。
【36752】Re:データを取得する便利なマクロですが...
回答  Kein  - 06/4/12(水) 15:00 -
引用なし
パスワード
   >アクティブセルと同じ値のセルを、アクティブセルの３つ左の列から探し出し、
>探しだしたセルの右にある値をアクティブセルの右に書き出す
普通に Findメソッドで探せばよいと思います。

Sub データ取得1個B()
　 Dim FR As Range

　 With ActiveCell
　　　Set FR = .Offset(, -3).EntireColumn _
　　　.Find(.Value, , xlValues, xlWhole)
　　　If Not FR Is Nothing Then
　　　　 .Offset(, 1).Value = FR.Offset(, 1).Value
　　　　 Set FR = Nothing
　　　Else
　　　　 MsgBox .Value & vbLf & "は見つかりません", 48
　　　End If
　 End With
End Sub
【36753】Re:データを取得する便利なマクロですが...
回答  Statis  - 06/4/12(水) 15:08 -
引用なし
パスワード
   こんにちは

こんな感じです。

Sub データ取得1個_1()
　　Dim ACELL As Range, C As Range, Fi As Range
　　Set ACELL = Range(ActiveCell, ActiveCell.End(xlDown))
　　For Each C In ACELL
　　　　Set Fi = C.Offset(, -3).EntireColumn.Find(C.Value, , xlValues, xlWhole)
　　　　If Not Fi Is Nothing Then
　　　　　 C.Offset(, 1).Value = Fi.Offset(, 1).Value
　　　　End If
　　　　Set Fi = Nothing
　　Next C
　　Set ACELL = Nothing
End Sub
【36754】Re:データを取得する便利なマクロですが...
お礼  カド  - 06/4/12(水) 15:37 -
引用なし
パスワード
   ▼Statis さん：
こんにちは。回答ありがとうございます。

ＰＣが固まりはしませんでしたが、やはり５分程度の時間が掛かりました。
エクセルでやる限り限界なのでしょうか？
アクセスだともっと速いなんてことがあるのでしょうか？
それとも別の言語（例えばＣ言語）なんかの方が速いのかな？
と疑問ばかり沸いてきます。
【36755】Re:データを取得する便利なマクロですが...
お礼  カド  - 06/4/12(水) 15:41 -
引用なし
パスワード
   ▼Kein さん　回答ありがとうございます。

Findメソッドでもやはり５分程度は掛かってしまいました。
やはりエクセルの限界でしょうか。
【36756】Re:データを取得する便利なマクロですが...
回答  Statis  - 06/4/12(水) 15:57 -
引用なし
パスワード
   こんにちは
あまり変わらないかもしれませんがお試しを。

Sub データ取得1個_2()
　　Dim ACELL As Range, C As Range, Ma As Variant
　　Set ACELL = Range(ActiveCell, ActiveCell.End(xlDown))
　　For Each C In ACELL
　　　　Ma = Application.Match(C.Value, C.Offset(, -3).EntireColumn)
　　　　If Not IsError(Ma) Then
　　　　　 C.Offset(, 1).Value = Cells(Ma, C.Column - 2).Value
　　　　End If
　　Next C
　　Set ACELL = Nothing
End Sub
【36757】Re:データを取得する便利なマクロですが...
お礼  カド  - 06/4/12(水) 16:37 -
引用なし
パスワード
   ▼Statis さん　こんにちは。
何度もすみません。

試してみましたが、正しい検索結果が得られませんでした。
VLOOKUP関数であいまい検索をしたような感じ？？？？
【36758】Re:データを取得する便利なマクロですが...
回答  Kein  - 06/4/12(水) 16:51 -
引用なし
パスワード
   こちらで50000行の「適当な」テスト環境を作るのは困難なので、全部数値を使って
やってみました。空白シートで、以下の２つのマクロを試してみて下さい。
(RANDBETWEEN関数を使えるようにするには「ツール」「アドイン」で"分析ツール"
にチェックを付けて下さい)

Sub DataFind_Test()
　 Dim Sta As Single, En As Single
　
　 With Range("A1:D50000")
　　　.Formula = "=RANDBETWEEN(1,5000)"
　　　.Columns(3).ClearContents
　　　.Copy
　　　.PasteSpecial xlPasteValues
　 End With
　 Application.CutCopyMode = False
　 Sta = Timer
　 On Error Resume Next
　 With Range("F1:F50000")
　　　.Formula = "=VLOOKUP($D1,$A$1:$B$50000,2,FALSE)"
　　　.SpecialCells(3, 16).ClearContents
　　　.Copy
　　　.PasteSpecial xlPasteValues
　 End With
　 Range("E:E").Delete xlShiftToLeft
　 On Error GoTo 0
　 Application.CutCopyMode = False
　 En = Timer
　 MsgBox En - Sta
End Sub

Sub DataFind_Test2()
　 Dim Sta As Single, En As Single
　
　 With Range("A1:D50000")
　　　.Formula = "=RANDBETWEEN(1,5000)"
　　　.Columns(3).ClearContents
　　　.Copy
　　　.PasteSpecial xlPasteValues
　 End With
　 Application.CutCopyMode = False
　 Sta = Timer
　 On Error Resume Next
　 With Range("F1:F50000")
　　　.Formula = "=VLOOKUP($D1,$A$1:$B$50000,2,FALSE)"
　　　With .SpecialCells(3, 1)
　　　　 .Offset(, -1).Value = .Value
　　　End With
　　　.ClearContents
　 End With
　 On Error GoTo 0
　 En = Timer
　 MsgBox En - Sta
End Sub

テスト環境を作っている部分のコードは、処理時間の計測から除外しています。
こちらの結果は、２つとも40秒前後で出来ました。ちなみにマシンスペックは
AMD:Athlon1800+(1.53Ghz), 256Mb, ExcelのVersionは 2000 です。
【36760】Re:データを取得する便利なマクロですが...
回答  Statis  - 06/4/12(水) 17:01 -
引用なし
パスワード
   こんにちは

>Ma = Application.Match(C.Value, C.Offset(, -3).EntireColumn)
Ma = Application.Match(C.Value, C.Offset(, -3).EntireColumn, 0)
としてみて下さい。
【36762】Re:データを取得する便利なマクロですが...
お礼  カド  - 06/4/12(水) 17:22 -
引用なし
パスワード
   ▼Kein さん　何度もありがとうございます。

こちらのマシンスペックは
Pentium４:2.4GHz, 256Mb, ExcelのVersionは 2000 です。

実行結果ですが、
　・提示いただいたコードでは、両者とも２５秒程度
　・こちらのオリジナルのデータでは、両者２１０秒程度でした。

精神的には、いつ処理が終わるのか分からず、ちょっと不安と苦痛が伴うかなっといったところです。
【36763】超高速処理でしたよ。なんてね。
お礼  カド  - 06/4/12(水) 17:52 -
引用なし
パスワード
   ▼Statis さん　何度もありがとうございます。

超高速処理が実現出来ました。なんて、ちょっとオーバーかな。

下記スペックにて、約５５秒でした。

こちらのマシンスペックは
Pentium４:2.4GHz, 256Mb, ExcelのVersionは 2000 です。
【36764】蛇足でしたらごめんなさい。
発言  Ned  - 06/4/12(水) 20:13 -
引用なし
パスワード
   こんにちは。私の環境では速く処理できたので、参考まで^ ^

Sub sample()
　　Dim a, d, di, x
　　Dim r As Range
　　Dim Dic As Object
　　Dim i As Long, j As Long
　　Const n As Long = 50000
　　Set r = ActiveCell
　　If r.Column > 3 And r.Row + n < 65537 Then
　　　　a = r.Offset(, -3).Resize(n, 2).Value
　　　　d = r.Resize(n).Value
　　　　ReDim x(1 To n, 1 To 1)
　　　　Set Dic = CreateObject("Scripting.Dictionary")
　　　　For i = 1 To n
　　　　　　Dic(a(i, 1)) = a(i, 2)
　　　　Next i
　　　　For Each di In d
　　　　　　j = j + 1
　　　　　　If Dic.exists(di) Then x(j, 1) = Dic.Item(di)
　　　　Next di
　　　　r.Offset(, 1).Resize(n).Value = x
　　　　Set Dic = Nothing
　　End If
　　Set r = Nothing
End Sub

Dictionaryは勉強中なので、変な使い方をしてるかもしれません。
それと、ActiveCellより上の行は検索対象にはいらないですけど、
そういう仕様でいいのですよね？
【36771】Re:蛇足でしたらごめんなさい。
お礼  カド  - 06/4/13(木) 6:13 -
引用なし
パスワード
   ▼Ned さん　こんにちは。回答ありがとうございます。

まだ試せていません。私も早くやってみたいのですが、出張のため明日になりそうです。
【36772】ほんとに超高速でした。
お礼  カド  - 06/4/13(木) 6:53 -
引用なし
パスワード
   ▼Ned さん　こんにちは。
ダミーデータで試してみました。
他の方の結果と比較すると、計算上は約１秒程度で処理が完了しそうです。
読み込みも書き出しも配列を使っていっきにやることを基本に、コードを
書くくせを付けた方がよさそうですね。

本当のデータの結果はまた報告しますね。

>Dictionaryは勉強中なので、変な使い方をしてるかもしれません。
>それと、ActiveCellより上の行は検索対象にはいらないですけど、
>そういう仕様でいいのですよね？

いいです。
【36796】やはり１秒で処理できました。
お礼  カド  - 06/4/14(金) 8:40 -
引用なし
パスワード
   ▼Ned さん　おはようございます。
会社にて、本物のデータを処理してみました。
予想通り、１．２５秒で処理できました。
【37191】大きな問題があることに気づきました。
質問  カド  - 06/4/25(火) 15:23 -
引用なし
パスワード
   ▼Ned さん　こんにちは。
教えていただいたコードは、大変高速で愛用しておりますが、
私の使い方に対して、大きな問題があることに気づきました。
お願いばかりで恐縮ですが、よろしくお願いします。

問題点
今の仕様は、たとえばD列と同じ値をA列から探してきて、見つけたA列の値の
隣のB列の値をE列に書き込むものですが、

すでにE列に値が書き込まれている状況にて、
B列から何も取得する値が無い場合は、E列をNULLで上書きしてしまいますよね。

やりたいのは、B列から取得する値がある場合のみ、E列に書き込んでいくこと
です。（私が最初に示したコードのとおりです。）
【37197】Re:大きな問題があることに気づきました。
発言  Ned  - 06/4/25(火) 16:18 -
引用なし
パスワード
   こんにちは。
>やりたいのは、B列から取得する値がある場合のみ、E列に書き込んでいくこと
>です。（私が最初に示したコードのとおりです。）
なるほど、既に値があったのですね。
であれば、最初にxに値を取り込んであげればいいですけど。

B列から取得する値があって、E列にも値がある場合は上書きでいいのですか？
上書きしないなら And IsEmpty(x(j, 1)) の判断を入れればいいですけど、
とりあえず上書きなら

Sub sample()
　　Dim a, d, di, x
　　Dim r As Range
　　Dim Dic As Object
　　Dim i As Long, j As Long
　　Const c As Long = -3
　　Set r = Range("d1") 'ActiveCell
　　With r.Offset(, c)
　　　　a = Range(.Cells, .End(xlDown).Offset(, 1)).Value
　　End With
　　With Range(r, r.End(xlDown))
　　　　d = .Value
　　　　With .Offset(, 1)
　　　　　　x = .Value
　　　　　　Set Dic = CreateObject("Scripting.Dictionary")
　　　　　　For i = 1 To UBound(d)
　　　　　　　　If Not Dic.exists(a(i, 1)) Then Dic(a(i, 1)) = a(i, 2)
　　　　　　Next i
　　　　　　For Each di In d
　　　　　　　　j = j + 1
　　　　　　　　If Dic.exists(di) Then x(j, 1) = Dic.Item(di)
　　　　　　Next di
　　　　　　.ClearContents
　　　　　　.Value = x
　　　　End With
　　End With
　　Set Dic = Nothing
　　Set r = Nothing
End Sub
【37223】Re:大きな問題があることに気づきました。
発言  Ned  - 06/4/26(水) 10:05 -
引用なし
パスワード
   こんにちは。すみません。間違えてました。
>For i = 1 To UBound(d)
For i = 1 To UBound(a)
【37247】Re:大きな問題があることに気づきました。
お礼  カド  - 06/4/26(水) 21:34 -
引用なし
パスワード
   ▼Ned さん　本当に何度もお世話になりました。
Nedさんのコードがあまりにも高速なので他のコードが使えなくなりました。
でも、残念ながら記述の仕方が今ひとつ分からず、質問ばかりになってしまいます。
今後ともよろしくお願いいたします。
【37249】こちらこそ。勉強させて頂きました^ ^
発言  Ned  - 06/4/26(水) 23:10 -
引用なし
パスワード
   こんにちは。
私のコードが速いのではなくて、"Scripting.Dictionary"が速いのだと思います^ ^;
調べて、使いこなせるようになれば、いろんなところで役に立ちますヨ^ ^

でも、前に書きましたが、私も勉強中なので、詳しいわけではありません。
Scripting.Dictionary　でNET検索してみてください。
http://www.interq.or.jp/student/exeal/dss/ref/wsh/scripting/dictionary.html
など、いろいろHITすると思います。

ついでに。自分のコードにコメントつけるのは苦手なのですが、何かの参考になれば。

Sub sample0()
　　Dim vA As Variant '比較（検索）先範囲を格納するために準備
　　Dim vD As Variant '比較元となる範囲を格納するために準備
　　Dim v As Variant '配列vDの個別要素Loop用
　　Dim vX As Variant '結果書き出し先の値を格納するために準備
　　Dim rD As Range '起点セル用
　　Dim myDic As Object '"Scripting.Dictionary"事後バインディング用
　　Dim i As Long, ii As Long 'Loopカウント用
　　
　　'比較起点セルを変数にセット。（どちらかというと、記述を簡単にしたいから）
　　Set rD = Range("D1") 'ActiveCell
　　'比較対象範囲の値を=Range().Valueで２次元配列に格納。
　　With rD.Offset(, -3)
　　　　vA = Range(.Cells, .End(xlDown).Offset(, 1)).Value
　　End With
　　'比較元の値を２次元配列に格納。
　　With Range(rD, rD.End(xlDown))
　　　　vD = .Value
　　　　'結果書き出し先の値を２次元配列に格納。
　　　　With .Offset(, 1)
　　　　　　vX = .Value
　　　　　　'オブジェクトmyDicとして"Scripting.Dictionary"を作成。
　　　　　　Set myDic = CreateObject("Scripting.Dictionary")
　　　　　　'比較対象をdicに格納。この時、セル範囲から一旦配列に格納したものをLoopして高速化。
　　　　　　For i = 1 To UBound(vA)
　　　　　　　　'myDic.Exists(キー値)…キーが既にmyDicにあれば（重複していれば）Trueを返す。 _
　　　　　　　　なのでNot…重複していない時だけ、配列の２番目の次元（２列目と考えてください） _
　　　　　　　　の値をDictionaryのアイテムとして格納する。正式な書き方は↓？
　　　　　　　　'If Not myDic.exists(vA(i, 1)) Then myDic.Add Key:=vA(i, 1), Item:=vA(i, 2)
　　　　　　　　If Not myDic.exists(vA(i, 1)) Then myDic(vA(i, 1)) = vA(i, 2)
　　　　　　Next i
　　　　　　'比較元を格納した配列をLoopする。
　　　　　　For Each v In vD
　　　　　　　　'For...Each使用のため、配列要素数をカウントしながら。
　　　　　　　　ii = ii + 1
　　　　　　　　'Dictionary.Exists(キー値)=Trueで存在確認して、書き出し用配列にItemをセット。
　　　　　　　　If myDic.exists(v) Then vX(ii, 1) = myDic.Item(v)
　　　　　　Next v
　　　　　　'書き出し先セルをクリアする。（配列から書き出す時、一旦クリアしないと遅い）
　　　　　　.ClearContents
　　　　　　'配列から一括書き込み。
　　　　　　.Value = vX
　　　　End With
　　End With
　　Set myDic = Nothing
　　Set rD = Nothing
End Sub
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
7723 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free