Excel VBA質問箱 IV

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

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


7722 / 13644 ツリー ←次へ | 前へ→

【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】やはり1秒で処理できました。 カド 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 -

引用なし
パスワード
   私が一番活用しているマクロですが、処理数を増やしたら、すごく処理が遅くなりました。
原始的なコードを書くのが好き(というよりそれしか書けない)なのですが、これじゃあ実用に耐えません。

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

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


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 '検索元を100行実行
  
    Do Until Count = aaa 'データベース(検索先)を100行検索

      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 -

引用なし
パスワード
   >アクティブセルと同じ値のセルを、アクティブセルの3つ左の列から探し出し、
>探しだしたセルの右にある値をアクティブセルの右に書き出す
普通に 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 さん:
こんにちは。回答ありがとうございます。

PCが固まりはしませんでしたが、やはり5分程度の時間が掛かりました。
エクセルでやる限り限界なのでしょうか?
アクセスだともっと速いなんてことがあるのでしょうか?
それとも別の言語(例えばC言語)なんかの方が速いのかな?
と疑問ばかり沸いてきます。

【36755】Re:データを取得する便利なマクロですが...
お礼  カド  - 06/4/12(水) 15:41 -

引用なし
パスワード
   ▼Kein さん 回答ありがとうございます。

Findメソッドでもやはり5分程度は掛かってしまいました。
やはりエクセルの限界でしょうか。

【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行の「適当な」テスト環境を作るのは困難なので、全部数値を使って
やってみました。空白シートで、以下の2つのマクロを試してみて下さい。
(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

テスト環境を作っている部分のコードは、処理時間の計測から除外しています。
こちらの結果は、2つとも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 さん 何度もありがとうございます。

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

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

精神的には、いつ処理が終わるのか分からず、ちょっと不安と苦痛が伴うかなっといったところです。

【36763】超高速処理でしたよ。なんてね。
お礼  カド  - 06/4/12(水) 17:52 -

引用なし
パスワード
   ▼Statis さん 何度もありがとうございます。

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

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

こちらのマシンスペックは
Pentium4: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 さん こんにちは。
ダミーデータで試してみました。
他の方の結果と比較すると、計算上は約1秒程度で処理が完了しそうです。
読み込みも書き出しも配列を使っていっきにやることを基本に、コードを
書くくせを付けた方がよさそうですね。

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

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

いいです。

【36796】やはり1秒で処理できました。
お礼  カド  - 06/4/14(金) 8:40 -

引用なし
パスワード
   ▼Ned さん おはようございます。
会社にて、本物のデータを処理してみました。
予想通り、1.25秒で処理できました。

【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で2次元配列に格納。
  With rD.Offset(, -3)
    vA = Range(.Cells, .End(xlDown).Offset(, 1)).Value
  End With
  '比較元の値を2次元配列に格納。
  With Range(rD, rD.End(xlDown))
    vD = .Value
    '結果書き出し先の値を2次元配列に格納。
    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…重複していない時だけ、配列の2番目の次元(2列目と考えてください) _
        の値を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

7722 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free