質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

6回答

9112閲覧

データが多いと固まってしまうFor文を軽くしたい

lq_hm_165912

総合スコア18

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2019/02/26 01:25

編集2019/02/26 05:08

以下のコードが行数が多くなると固まってしまいます。
(10万行ほど)

構成シートのA列と、集計画面のJ列が一致したら、集計画面のT列に構成シートのC列を出力したいのです。
この構成シートのA列にあたる部分が固定されている他のマクロでは上手く動きましたが、今回は変動有のリストなので最終行まで見ようとしています。

※最初の変動がないリストに関しては無事動作しました

VBA

1Sub 構成反映2() 2 3 4 Dim Sh1, Sh2 As Worksheet 5 Dim rM, rH, rMy, rFirst, rU As Range 6 7 Set Sh1 = ThisWorkbook.Worksheets("集計画面") 8 Set Sh2 = ThisWorkbook.Worksheets("構成") 9 10 Application.ScreenUpdating = False 11 Application.Calculation = xlManual 12 13 '最終行の取得 14 Dim lastrow As Long 15 Dim lastrow2 As Long 16 lastrow = Sh1.Cells(Rows.Count, 10).End(xlUp).Row 17 lastrow2 = Sh2.Cells(Rows.Count, 1).End(xlUp).Row 18 19 '検索 20 For Each rM In Sh2.Range("A2:A" & lastrow2) 21 Set rH = Sh1.Range("J2:J" & lastrow) 22 Set rMy = rH.Find(What:=rM.Value) 23 24 If rMy Is Nothing Then 25 Exit For 26 Else 27 Set rFirst = rMy 28 rMy.Offset(, 10).Value = rM.Offset(, 2).Value 29 End If 30 Do 31 Set rMy = rH.FindNext(rMy) 32 If rMy.Address = rFirst.Address Then 33 Exit Do 34 Else 35 rMy.Offset(, 10).Value = rM.Offset(, 2).Value 36 End If 37 Loop 38 Next 39 40 41 Sh1.Range("T1").Value = "構成" 42 43 Application.Calculation = xlAutomatic 44 Application.ScreenUpdating = True 45 46End Sub

↓Esc押せば結果がなぜか出てくるコードはこちら↓

VBA

1 2Sub 構成反映() 3 Dim Sh1, Sh2 As Worksheet 4 Set Sh1 = ThisWorkbook.Worksheets("集計画面") 5 Set Sh2 = ThisWorkbook.Worksheets("材料構成") 6 '------最終行の取得 7 8 Dim lastrow As Long 9 lastrow = Sh2.Cells(Rows.Count, "A").End(xlUp).Row 10 11 Dim prefRng, cityRng As Range 12 Set prefRng = Range(Sh2.Cells(2, 1), Sh2.Cells(40000, 1)) 13 14 Dim workEndR, workTmpR As Long, tmpStr As String 15 workEndR = Sh1.Cells(Rows.Count, 1).End(xlUp).Row 16 17 Dim x As Long 18 Application.ScreenUpdating = False 19 20 For x = 0 To workEndR 21 For workTmpR = 2 To workEndR 22 tmpStr = Sh1.Cells(workTmpR, 10).Value 23 On Error Resume Next 24 Sh1.Cells(workTmpR, 20).Value = Sh2.Cells(Application.WorksheetFunction.Match(tmpStr, prefRng, 0) + 1, 3) 25 Next 26 Next 27 Sh1.Range("T1").Value = "構成" 28 29End Sub

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

coco_bauer

2019/02/26 05:54

"構成"ワークシートのA列に入っているデータの数が固定でない件については、"lastrow2 = Sh2.Cells(Rows.Count, 1).End(xlUp).Row"というコードで最後のデータの行を探すことで問題解決しているはずです。 それでもうまくいかないのは、"構成"ワークシートのA列の途中に空白のセルが存在している等、ワークシートのデータに問題があるためだと思われます。 プログラムを疑う前に、データを疑ってみてはどうでしょうか? "構成"ワークシートでフィルターをかけて、A列に空白セルが含まれていないかどうかを確認してみてください。
coco_bauer

2019/02/26 05:56

構成ワークシートのA列の値と、C列の値を辞書(Dictionary)にして、RangeのFindではなくDictionaryの検索を使うと、相当実行速度が上がると思います。
lq_hm_165912

2019/02/26 06:00

空白やエラー値はありません。教えていただいた方法でもう少し頑張ってみます。
coco_bauer

2019/02/26 06:12

「最初の変動がないリストに関しては無事動作しました」と書いてあるという事は、「変動有のリスト」では動作しない(無事には終わらない?)などの問題があるのですよね? 何が問題なのですか???
lq_hm_165912

2019/02/26 06:22

95列固定の場合だと値が入力されてましたが、提示しているマクロだと入力されずに終わってしまいます。
guest

回答6

0

VBA

1Sub 構成反映3() 2 Dim rngKey As Range 3 Dim rngList As Range 4 5 6 With ThisWorkbook.Worksheets("集計画面").Range("A1").CurrentRegion 7 Set rngKey = Intersect(.Cells, .Offset(1), .Columns("J")) 8 End With 9 10 With ThisWorkbook.Worksheets("構成") 11 Set rngList = .Range("A1").CurrentRegion 12 .Range("T1").Value = "構成" 13 End With 14 15 With rngKey.Offset(, 2) 16 .Formula = "=VLookup(A2," & rngList.Address(, , , True) & ",10)" 17 .Value = .Value 18 End With 19End Sub

セルの位置関係が分かってないけど、
なんとなくこんな感じで高速化できませんか?(動作確認はしていません。)

VBAで出来るだけループをしないように書くこと(=エクセル君が得意なことはエクセル君に任せる)が、
高速化につながると思います。

投稿2019/02/27 13:15

mattuwan

総合スコア2136

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

lq_hm_165912

2019/02/27 23:55

このまま使ってみたところ、T列に出力したいものがJ列(検索する列)の2つ横に#REF!となりましたので位置関係を整理して使ってみます。
lq_hm_165912

2019/02/28 01:39

Sub 構成反映3() Dim rngKey As Range Dim rngList As Range With ThisWorkbook.Worksheets("集計画面").Range("A1").CurrentRegion Set rngKey = Intersect(.Cells, .Offset(1), .Columns("J")) .Range("T1").Value = "構成" End With With ThisWorkbook.Worksheets("構成") Set rngList = .Range("A1").CurrentRegion End With With rngKey.Offset(, 10) .Formula = "=VLookup(J1," & rngList.Address(, , , True) & ",3)" .Value = .Value End With End Sub これでうまくいきました!ありがとうございます。
mattuwan

2019/02/28 10:26

>これでうまくいきました! ↑だけでなく、どの程度のデータ量(集計画面シートと構成シートで、それぞれ何列×何行)で、 改善前と後でどれくらい処理時間が短縮されたか、書いてもらえたらありがたいです。 他に見ている人の参考にもなりますし、過去ログを探る人も居るでしょう。僕も知りたいです。 せっかくの場所なので、皆で情報を共有しましょうよ^^
lq_hm_165912

2019/03/01 03:59

Sub 構成反映() Dim rngKey As Range Dim rngList As Range With ThisWorkbook.Worksheets("集計画面").Range("A2").CurrentRegion Set rngKey = Intersect(.Cells, .Offset(1), .Columns("J")) .Range("T1").Value = "構成" End With With ThisWorkbook.Worksheets("構成") Set rngList = .Range("A2").CurrentRegion End With With rngKey.Offset(, 10) .Formula = "=VLookup(J2," & rngList.Address(, , , True) & ",3)" .Value = .Value End With End Sub ↑しかも間違えていましたすみません。 データ量は変動するのですが、構成シート3列×30000行・集計画面シートは23列×5000~30000行です。 処理時間が短縮というか、改善前は処理が終わらずにEscで無理やり終わらせていた(でも出力はしていた)ので処理速度の比較は出来ませんが、集計画面シート3000行でしたら1秒もかからずに出力出来ました。
mattuwan

2019/03/01 09:25

了解です。普段、個人的に200行ぐらいしか使うことがないので、何万行になったときにどうなるか知りたかったです。 ありがとうございます。 何万行とか超えたら(細かい数字は失念しました^^;)、 Value = Value より、コピペの方が速いという噂もあります。 もし運用して、気になったら試してみる価値があるかも知れません。
guest

0

直接的ではないのですがパフォーマンスに影響することもある気になった箇所がありますので、余談と捉えていただいて結構です。

VBA

1 Dim Sh1, Sh2 As Worksheet 2 Dim rM, rH, rMy, rFirst, rU As Range

この型宣言部分ですが、このように書くと、

  • Sh2 は Worksheet型
  • rU は Range型
  • その他は Variant型

と宣言したことになります。

VBA

1 Dim Sh1 As Worksheet, Sh2 As Worksheet 2 Dim rM As Range, rH As Range, rMy As Range, rFirst As Range, rU As Range

とすることで各変数が意図したデータ型として宣言したことになります。


本題、構造的に2重ループになっている上に何度も検索をかけるような状態なので、検索対象行が増えれば増えるほどどんどん重くなる構造になっていますね。
質問文中、行と列が混乱しているように読めるためデータリストの構造が今一つ理解できていませんが検索をかける回数を減らすような構造にするとよいかと思います。

投稿2019/02/26 02:52

kaz.Suenaga

総合スコア2037

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

lq_hm_165912

2019/02/26 03:14

指摘いただき訂正しましたが動かないですね・・・ 他の方の参考URLを見ながら直したのですが。。 構成シートのA列と、集計画面のJ列が一致したら、集計画面のT列に構成シートのC列を出力したいのです。 この構成シートのA列にあたる部分が固定されている他のマクロでは上手く動きましたが、今回は変動有のリストなので最終行まで見ようとしています。
kaz.Suenaga

2019/02/26 04:40

構成シートのの行数と集計画面シートの行数は一致するんでしょうか。
kaz.Suenaga

2019/02/26 05:22

構成シートのA列と一致する集計画面のJ列は1行しかないんでしょうか。
kaz.Suenaga

2019/02/26 05:29

vlookup関数で済むような動作に見えるんですが、違いますか?
lq_hm_165912

2019/02/26 06:49

10万行ほどあるand他はマクロで動かせているのですべてマクロで行いたいと思っています。
kaz.Suenaga

2019/02/26 07:14

分量的にも処理的にも、Excel というよりデータベースでやるような処理っぽいですね。 Excelのバージョンにもよりますが、最新のExcelではvlookupなどのデータの扱いが強化されているらしいので、マクロ内でデータを取得するのではなく、計算式を入れるマクロにする方法もありそうです。 https://claccico.com/excel-2019/ また、Excel VBA から、シートのデータをSQL(のJOIN)を使って扱う方法があるのでそういう方法を考えたほうがいいようにも思います。 例) https://lil.la/technology/post-2133
guest

0

固まるのを防止したい目的(速さは二の次)であるのなら、ループ内にDoEventsを入れてください。

投稿2019/02/26 02:40

ttyp03

総合スコア16996

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

自己解決

VBA

1Sub 構成反映() 2 3 Dim Sh1 As Worksheet, Sh2 As Worksheet 4 Dim tate As Long 5 Dim rH As Range 6 Dim st() As String 7 8 Application.ScreenUpdating = False 9 Application.Calculation = xlManual 10 11 Set Sh1 = ThisWorkbook.Worksheets("集計画面") 12 Set Sh2 = ThisWorkbook.Worksheets("構成") 13 14 '最終行の取得 15 Dim lastrow As Long 16 Dim lastrow2 As Long 17 lastrow = Sh1.Cells(Rows.Count, 4).End(xlUp).Row 18 lastrow2 = Sh2.Cells(Rows.Count, 1).End(xlUp).Row 19 20 '検索 21 For tate = 0 To lastrow2 22 ReDim Preserve st(tate) 23 st(tate) = Sh2.Range("A2").Offset(tate).Value 24 Next 25 26 For tate = 0 To UBound(st) 27 For Each rH In Sh1.Range("J2:J" & lastrow) 28 If rH.Value = st(tate) Then 29 rH.Offset(, 10).Value = Sh2.Range("C2").Offset(tate).Value 30 End If 31 Next 32 Next 33 34 Sh1.Range("T1").Value = "構成" 35 36 Application.Calculation = xlAutomatic 37 Application.ScreenUpdating = True 38 39End Sub

重いのでおそらくまだまだの出来だとは思いますが動きましたので解決にします。
参考URLなど勉強になりました。ありがとうございます。

投稿2019/02/27 07:17

lq_hm_165912

総合スコア18

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

VBA高速化テクニック 配列を使う
http://officetanaka.net/excel/vba/speed/s11.htm

20万個のセルから検索する方法について
・セル1つ1つ参照する場合
・配列に突っ込んでけんさする場合
・Findを使う場合
で計測すると、配列に突っ込んだのが一番早いそうです
(試したら確かに同じ結果でした)

また、本当に遅い処理はセルへの代入とのことで、まとめられるなら一括で行うことで劇的に早くなるとのこと

以上ご参考まで

投稿2019/02/26 03:34

takito

総合スコア3111

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

lq_hm_165912

2019/02/26 04:09

Worksheet系はいつもエラーになってしまい今回もだめみたいです。エラー値はないと思うのですが・・・参考にします。
coco_bauer

2019/02/26 06:47

「Worksheet系はいつもエラーになってしまい」って、何のエラーが出ているのですか??? 普通、途中でエラーが出たら、正しい値を得て終わり、にならないんですが。
guest

0

下記、ご参考ください。

つまり、forが一番時間がかかるというわけですね。
検査する列が決まっているならFindとかSearchとかの方が速いと思います。

投稿2019/02/26 01:30

m.ts10806

総合スコア80765

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

lq_hm_165912

2019/02/26 02:17

例にあげた分は上手くできましたが、リストもlastrowで検索するときに上手く動かないので頑張ってみます
m.ts10806

2019/02/26 02:20

場合によってはデータの置き方も「集計しやすいように」工夫する必要があるかもしれませんね。
lq_hm_165912

2019/02/26 02:24

リスト自体が、重複を削除したものとしているので変動ありなのです。 今のところFor文だと上手く動きます(固まりかけます)が、揉んでみます。参考になりました。
coco_bauer

2019/02/26 06:45

上手く動かない時には何が起きているのですか? 問題が何なのかが不明だと、問題解決は不可能ですよ。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問