Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1379 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【74822】別ファイル間でのVLOOKUPの高速処理方法 びゅう 13/9/20(金) 20:22 質問[未読]
【74823】Re:別ファイル間でのVLOOKUPの高速処理方法 kanabun 13/9/20(金) 22:13 発言[未読]
【74825】Re:別ファイル間でのVLOOKUPの高速処理方法 びゅう 13/9/21(土) 8:20 お礼[未読]
【74824】Re:別ファイル間でのVLOOKUPの高速処理方法 kanabun 13/9/20(金) 23:05 発言[未読]
【74826】Re:別ファイル間でのVLOOKUPの高速処理方法 びゅう 13/9/21(土) 8:22 お礼[未読]
【74827】Re:別ファイル間でのVLOOKUPの高速処理方法 kanabun 13/9/21(土) 18:52 発言[未読]
【74829】Re:別ファイル間でのVLOOKUPの高速処理方法 びゅう 13/9/21(土) 21:51 お礼[未読]
【74828】Re:別ファイル間でのVLOOKUPの高速処理方法 γ 13/9/21(土) 19:03 発言[未読]
【74830】Re:別ファイル間でのVLOOKUPの高速処理方法 びゅう 13/9/21(土) 21:52 お礼[未読]
【74822】別ファイル間でのVLOOKUPの高速処理方法
質問  びゅう  - 13/9/20(金) 20:22 -
引用なし
パスワード
   エクセルマクロでVLOOKUPの使用方法で困っています。

A,BそれぞれのファイルのSheet1同志でVLOOKUPを使用してA列を検索してB列を転記するマクロを作成しましたが、A,Bそれぞれのファイルの行数が20000件近くあり
私が作成したマクロだと時間がかかりすぎて１０分近くかかります。

もっと、高速に検索する方法がありましたらどなたか教えて頂けませんか？

私の知識は記録マクロを少し修正して使える程度です。

私のマクロを下記に記します。

ファイルＡのSheet1をActivateしてから下記のマクロを実行します。

　　Sheets("Sheet1").Select
　　Line = 2
　　Do Until Cells(Line, 1).Value = ""

　　On Error Resume Next
　　Cells(Line, 1).Value = Application.WorksheetFunction.VLookup(Cells(Line, 1).Value, Workbooks("B").Worksheets("Sheet1").Range("A2:B60000"), 2, 0)
　　On Error GoTo 0
　　If Cells(Line, 1).Value = "" Then
　　Cells(Line, 1).Value = ""
　　End If
　　Line = Line + 1
　　Loop
【74823】Re:別ファイル間でのVLOOKUPの高速処理方...
発言  kanabun  - 13/9/20(金) 22:13 -
引用なし
パスワード
   ▼びゅう さん：

>私が作成したマクロだと時間がかかりすぎて１０分近くかかります。

ワークシート関数のVLOOKUPだと、２万行あれば ２万回同じ表を走査しないと
いけないですよね？
ためしてませんが、Dictionaryオブジェクトを使って、B Bookの表をメモリに
書き込んでおけば、２万回表をなめることはしなくて済みます。

Option Explicit

Sub dicLookup()
　　Dim i As Long
　　Dim v As Variant
　　Dim dic As Object
　　Set dic = CreateObject("Scripting.Dictionary")

　　With Workbooks("B.xls").Worksheets(1)
　　　　v = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp)). _
　　　　　　Resize(, 2).Value
　　End With
　　For i = 1 To UBound(v)
　　　　dic(v(i, 1)) = v(i, 2)
　　Next

　　With Workbooks("A.xls").Worksheets(1)
　　　With .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
　　　　v = .Value
　　　　For i = 1 To UBound(v)
　　　　　　If dic.Exists(v(i, 1)) Then
　　　　　　　　v(i, 1) = dic(v(i, 1))
　　　　　　Else
　　　　　　　　v(i, 1) = Empty
　　　　　　End If
　　　　Next
　　　　.Offset(, 1).Value = v　'結果をB列に書き込む
　　　End With
　　End With
End Sub
【74824】Re:別ファイル間でのVLOOKUPの高速処理方...
発言  kanabun  - 13/9/20(金) 23:05 -
引用なし
パスワード
   VLOOKUPを使うばあいは、
LOOKUP表をA列で昇順に並び替えておくと、
検索の型1 が使えるので、速くなりますよ
【74825】Re:別ファイル間でのVLOOKUPの高速処理方...
お礼  びゅう  - 13/9/21(土) 8:20 -
引用なし
パスワード
   ▼kanabun さん：

回答ありがとうございます。

>ワークシート関数のVLOOKUPだと、２万行あれば ２万回同じ表を走査しないと
>いけないですよね？
>ためしてませんが、Dictionaryオブジェクトを使って、B Bookの表をメモリに
>書き込んでおけば、２万回表をなめることはしなくて済みます。

教えて頂いた、プログラムの意味は正直分かりませんが、とりあえず
自分の条件にあてはめて試してみようと思います。

それから、使用されているワードの意味を調べて理解したいと思います。

分からない事がありましたらまた相談させて頂きます。
有難うございました。
【74826】Re:別ファイル間でのVLOOKUPの高速処理方...
お礼  びゅう  - 13/9/21(土) 8:22 -
引用なし
パスワード
   ▼kanabun さん：
>VLOOKUPを使うばあいは、
>LOOKUP表をA列で昇順に並び替えておくと、
>検索の型1 が使えるので、速くなりますよ

有難うございます。
ちょっと早くなりましたが、やはり時間がかかりました。
【74827】Re:別ファイル間でのVLOOKUPの高速処理方...
発言  kanabun  - 13/9/21(土) 18:52 -
引用なし
パスワード
   ▼びゅう さん：

>ちょっと早くなりましたが、やはり時間がかかりました。

それって、１行づつ WorksheetFunction関数使った結果を書き込んで
いってるでしょ？
範囲に一括数式を書き込んで、そのあと Valueに直したら、
もっと速くなるような気がします。
ま、あくまで VLOOKUP関数を利用していくなら、という条件での話
ですが。

Dictionaryのほうはどんな結果になりましたでしょうか？
【74828】Re:別ファイル間でのVLOOKUPの高速処理方...
発言  γ  - 13/9/21(土) 19:03 -
引用なし
パスワード
   横から失礼します。

Dictionaryが最速だと思いますが、
今のものでも10分はかかりすぎですね。
計算式が多量にあって、書き込みごとに再計算が動いているんじゃないでしょうか。
プロシージャの最初で
　　Application.ScreenUpdating = False
　　Application.Calculation = xlCalculationManual
最後で　　
　　Application.Calculation = xlCalculationAutomatic
　　Application.ScreenUpdating = True
を挿入してみてはどうでしょうか。
【74829】Re:別ファイル間でのVLOOKUPの高速処理方...
お礼  びゅう  - 13/9/21(土) 21:51 -
引用なし
パスワード
   ▼kanabun さん：

>Dictionaryのほうはどんな結果になりましたでしょうか？


kanabunさんの例をそのまま使用してテストしてみました。
数秒で完了しました！！

私は記録マクロをちょこちょこ直して使用したり、
for to やLoop 関数しか使用していなかった（他の方法を知らなかった）ので
今回の方法は驚きました。
このような方法もあるのですね。

やはり、一からマクロを勉強したほうが良いかもしれません。

有難うございました。
【74830】Re:別ファイル間でのVLOOKUPの高速処理方...
お礼  びゅう  - 13/9/21(土) 21:52 -
引用なし
パスワード
   ▼γ さん：
>横から失礼します。
>
>Dictionaryが最速だと思いますが、
>今のものでも10分はかかりすぎですね。
>計算式が多量にあって、書き込みごとに再計算が動いているんじゃないでしょうか。
>プロシージャの最初で
>　　Application.ScreenUpdating = False
>　　Application.Calculation = xlCalculationManual
>最後で　　
>　　Application.Calculation = xlCalculationAutomatic
>　　Application.ScreenUpdating = True
>を挿入してみてはどうでしょうか。

これも効果がありました。
時間が半分くらいになりました。
有難うございました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1379 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free