excel関数の計算について

質問

office2016

oracleのテーブル1からデータ取得し、データ加工してoracleのテーブル2にデータを登録という流れの中で、データ加工処理に時間がかかっています。

下記excel　VBAの内容は
sheet1のＣ列：シリアルデータ(8桁文字列　例ABCD0001）
sheet1のＡＥ列：日付データ（8桁文字列　例20200612）
sheet1のＣ列全体をsheet2のＡ列へコピー
sheet1のＡＥ列全体をsheet2のＢ列へコピー

sheet2のＦ列に開始時刻データ(14桁文字列）
sheet2のＧ列に終了時刻データ(14桁文字列）
を設定
という内容になっています。

Ｆ列は日付データの稼働日３日前で時刻が0845　例20200609084500
Ｇ列は日付データの時刻が1700　例20200612170000
という計算を実施します。
T_非稼働日シートには工場の非稼働日データ（2020/05/05みたいな休日等）をＡ列に設定しています。

excel上で計算方法の設定は”手動”にしておき700件くらいのデータに関して下記マクロ実行すると約30[s]程度かかります。
sub test()
Application.Calculation = xlCalculationManual
'sheet1 最終行の取得
Dim LASTROW As Long
LASTROW = Worksheets("sheet1").Cells(Rows.Count, 3).End(xlUp).ROW

'sheet1のシリアルデータ(Ｃ列）をsheet2 A列へコピー

Worksheets("sheet1").Range(Worksheets("sheet1").Cells(1, 3), Worksheets("sheet1").Cells(LASTROW, 3)).copy _
Destination:=Worksheets("sheet2").Cells(1, 1)

'sheet1の納期データ(ＡＥ列）をsheet2 B列へコピー

Worksheets("sheet1").Range(Worksheets("sheet1").Cells(1, 31), Worksheets("sheet1").Cells(LASTROW, 31)).copy _
Destination:=Worksheets("sheet2").Cells(1, 2)

'Ｆ列　日付データの稼働日３日前で時刻が084500
Worksheets("sheet2").Range("F1:F" & Range("A" & Rows.Count).End(xlUp).ROW).Value = "=TEXT(WORKDAY(DATE(MID(RC[-4],1,4), MID(RC[-4],5,2), MID(RC[-4],7,2)),-3,T_非稼働日!C[-5]),""yyyymmdd"")&""084500"""

'Ｇ列 日付データの時刻が170000　例20200612170000
Worksheets("sheet2").Range("G1:G" & Range("A" & Rows.Count).End(xlUp).ROW).Value = "=RC[-5]&""170000"""

Application.Calculation = xlCalculationAutomatic
end sub

Ａ列　　　Ｂ列
ABCD0001 20200612
ABCE0001 20200608
ABCE0002 20200610
ABDD0001 20200616
ABDD0002 20200623
…
約600行
これを
end sub

Ａ列　　　Ｂ列 　　　　Ｆ列　　　　　　Ｇ列
ABCD0001 20200612　20200609084500 20200612170000　
ABCE0001 20200608　20200603084500 20200608170000
ABCE0002 20200610　20200605084500 20200610170000
ABDD0001 20200616　20200611084500 20200616170000
ABDD0002 20200623　20200618084500 20200623170000
…
の様にするのに30[S]くらいの時間がかかっている。Ｆ列データ作成が遅い状況。
ところが、
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
の内容を削除し、計算方法の設定を”自動”で実施すると8[S]程度で終了します。
計算方法は手動の方が早いはずなのに自動の方が速いのは何故なのでしょう？
また600行の計算（Ｆ列データを作成する部分です）に関して、もう少し早く出来る構成がありましたら教えていただきたく。

回答No.2

HohoPapa · ベストアンサー

事象に興味を惹かれ、やってみました。
当方でも容易に30秒を超える事象を再現できます。

私もよくわかりませんが、
計算方法を手動に変更し、
セル群に（課題の）計算式を埋め、自動に変更すると
再計算の時間が長くなることから、
どのセルを再計算すべきかの判定が甘く
同じセルに対して複数回再計算が行われているんじゃないかと思います。

課題のマクロでは、
Ｆ列とＧ列にワークシート関数の計算式を埋め
その計算式によって期待の結果を求めているわけですが

計算式である必要がないのであれば
マクロ側で計算を行い、セル値を埋めるほうが
再計算といったことを意識しなくて済み
より速い動作で、よりシンプルなコードになるだろうと思います。

例えば、以下です。

Sub test3()

　Dim RowCounter
　Dim wsGet As Worksheet
　Dim wsPut As Worksheet
　Dim PutDt1 As String
　Dim WDay As Range
　Dim LASTROW As Long

　With ThisWorkbook

　　'作業範囲を定義、クリアー
　　Set wsGet = .Sheets("Sheet1")
　　Set wsPut = .Sheets("Sheet2")
　　With .Sheets("T_非稼働日")
　　　LASTROW = .Cells(Rows.Count, 1).End(xlUp).Row
　　　Set WDay = Range(.Cells(1, 1), .Cells(LASTROW, 1))
　　End With
　　wsPut.Cells.ClearContents

　　RowCounter = 1
　　Do
　　　If wsGet.Cells(RowCounter, 3).Value = "" Then Exit Do
　　　wsPut.Cells(RowCounter, 1).Value = _
　　　　wsGet.Cells(RowCounter, 3).Value
　　　wsPut.Cells(RowCounter, 2).Value = _
　　　　wsGet.Cells(RowCounter, 31).Value
　　　wsPut.Cells(RowCounter, 6).NumberFormatLocal = "@"
　　　wsPut.Cells(RowCounter, 7).NumberFormatLocal = "@"
　　　PutDt1 = Format(Application.WorksheetFunction.WorkDay(DateSerial( _
　　　 Mid(wsPut.Cells(RowCounter, 2).Value, 1, 4), _
　　　 Mid(wsPut.Cells(RowCounter, 2).Value, 5, 2), _
　　　 Mid(wsPut.Cells(RowCounter, 2).Value, 7, 2)), _
　　　　 -3, WDay), "yyyymmdd") & "084500"
　　　wsPut.Cells(RowCounter, 6).Value = PutDt1
　　　wsPut.Cells(RowCounter, 7).Value = _
　　　　wsPut.Cells(RowCounter, 2).Value & "170000"
　　　RowCounter = RowCounter + 1
　　Loop

　End With

End Sub

お礼

回答ありがとうございます。
やってみた所、30[s]くらいかかっていたのが0.4[s]くらいになりました。
大変助かりました。ありがとうございます。
”同じセルに対して複数回再計算”というのもなるほどと思いました。

回答No.1

kon555

手動と自動での速度差については分からないですね。
　ただ速度アップについてなら、コピーしてペーストするよりも、直接セルに値をする処理の方が迅速に実行できると思いますよ。
参考ページ

http://officetanaka.net/excel/vba/speed/s13.htm
https://officedic.com/excel-vba-copy-paste-highspeed/#toc3

　個人的にはfor　nextで上から処理してしまって、Ｆ列とG列のデータについてもその流れで捌いてしまえば、あんまり複雑なコードにもならず、処理も早く済むと思います。

お礼

回答ありがとうございます。
for　nextで上から処理やってみましたが処理時間短縮にはなりませんでした。
処理が遅いのは、コピーの部分ではなくて計算の部分なのです。