簡単な例ですが、例えばB列にあるデータの平均値を求めるときに以下のようにしています。
sub 平均計算()
Dim X(1 To 1000, 1 To 1) As Variant, i as Integer
For i = 10 To 1000
X(i, 1) = WorksheetFunction.Average _
(Range(Cells(i - 9, 2), Cells(i, 2)))
Next
Range(Cells(1, 1), Cells(1000, 1)) = X
End Sub
ここで処理速度改善のため、B列のデータを別の配列変数Yに格納してから平均値を求めるというようなことをしたいのですが、そんなことは可能でしょうか?イメージとしてはこんな感じです。
Y = Range(Cells(1, 2), Cells(1000, 2))
for i = 1 to 1000
X(i, 1) = WorksheetFunction.Average _
(Range(Y(i - 9, 2), Y(i, 2)))
Next
当然これはエラーになってしまいますが、このようなことを可能にする方法があれば、どなたか教えてください!よろしくお願いします。
A 回答 (7件)
- 最新から表示
- 回答順に表示
No.7
- 回答日時:
こんにちは。
配列を使えば必ず高速化できるわけではないです。ご質問文の
例だと下記の方が早いと思いますよ。
しかし、コンマ何秒とか数秒の高速化が必要な状況なのですか?
私なら体感できないほどの高速化なら、ソースの可読性を優先
させますが。。
Sub TestProc()
With Range("A10:A1000")
.FormulaR1C1 = "=AVERAGE(R[-9]C[1]:RC[1])"
.Value = .Value
End With
End Sub
No.6
- 回答日時:
セル範囲は配列ではないVariant型に代入することが出来ます。
ちなみ処理速度は、セル参照型と変数参照型で約2倍の差が出ました。
私の環境でっていう条件ですが・・・・
Option Explicit
Sub Test()
Dim X(1 To 1000, 1 To 1) As Variant
Dim Y As Variant
Dim i As Integer
Dim j As Integer
Dim STime As Single
'処理開始時間の処理
STime = Timer
'連続したセル範囲の値を変数に代入
Y = Range("B1:B1000").Value
'処理時間が早すぎて比較しづらいため、同じ処理を100回繰り返す
For j = 0 To 100
For i = 10 To 1000
'変数に格納した値を参照(参考値 0.34375 秒)
X(i, 1) = WorksheetFunction.Average(Y(i, 1), Y(i - 1, 1), Y(i - 2, 1), Y(i - 3, 1), Y(i - 4, 1), Y(i - 5, 1), Y(i - 6, 1), Y(i - 7, 1), Y(i - 8, 1), Y(i - 9, 1))
'セルの値を参照(参考値 0.7192383 秒)
'X(i, 1) = WorksheetFunction.Average(Range(Cells(i - 9, 2), Cells(i, 2)))
Next i
Next j
Sheet1.Range(Cells(1, 1), Cells(1000, 1)) = X
Debug.Print Timer - STime
End Sub
<蛇足>
処理速度は速いですが、コーディングの観点から言えば、褒められません。
視認性が著しく低下し、何をしているのかがわかりにくくなります。
コードを作成者しか見ない場合は問題ないでしょうが、
そうでない場合はコメントでなぜこんな書き方をしているのか
わかりやすく理由を記述しておくとよいと思います
No.5
- 回答日時:
>hige_082さん
>前者であれば、配列や関数を使用するより、シンプルなものの方が・・・
ぉおー。なるほど、確かに速いですね。
Sub try5()
Dim v
Dim w
Dim x(10 To n, 1 To 1)
Dim i As Long
Dim t As Single
t = Timer
v = Range("A1").Resize(n).Value
For i = 1 To 10
w = w + v(i, 1)
Next
x(10, 1) = w / 10
For i = 11 To n
w = w + v(i, 1) - v(i - 10, 1)
x(i, 1) = w / 10
Next
Range("G10").Resize(n - 9).Value = x
Debug.Print "try5", Timer - t
End Sub
ありがとうございます。勉強になりましたm(_ _)m
#また修行のタビに出なくちゃ...
No.4
- 回答日時:
処理速度改善?それとも配列の練習?
前者であれば、配列や関数を使用するより、シンプルなものの方が・・・
Sub test()
Dim i, ii As Integer
Dim x As Integer
ii = 0
x = 0
For i = 1 To 1000
x = x + Cells(i, 2)
ii = ii + 1
Next
MsgBox x / ii
End Sub
速いかなと思っただけです
忘れてください お邪魔しました
確かにシンプルで速いですね。参考にさせてもらいます。皆さんに回答して頂く度にいろいろなやり方があることに気付かされます。ありがとうございます!
No.3
- 回答日時:
ぁー…すみません、ムダなLoopしてました。
'-------------------------------------------------
Sub try3改()
Dim v
Dim w(0 To 9)
Dim x(10 To n, 1 To 1)
Dim i As Long
Dim j As Long
Dim t As Single
t = Timer
v = Range("A1").Resize(n).Value
With WorksheetFunction
For i = 0 To 9
w(i) = v(i + 1, 1)
Next
x(10, 1) = .Average(w)
For i = 11 To n - 9 Step 10
For j = 0 To 9
w(j) = v(i + j, 1)
x(i + j, 1) = .Average(w)
Next
Next
End With
Range("F10").Resize(n - 9).Value = x
Debug.Print "try3改", Timer - t
End Sub
'-------------------------------------------------
多くの例を示して頂いてありがとうございます。いろいろとやり方があるんですね。これを参考にして検証させてもらいます。どうもありがとうございました!
No.2
- 回答日時:
別配列に取得した後に、もうひとつ別サイズの配列にLoopで格納し計算させる事はできますが......
いろいろ試してみるのもいいかもしれませんね。
Option Explicit
Const n = 60000 'テストデータ行数
'-------------------------------------------------
Sub pre() 'テストシート作成
With Sheets.Add.Range("A1").Resize(n)
.Formula = "=rand()*" & n
.Value = .Value
End With
End Sub
'-------------------------------------------------
Sub try1()
Dim x(10 To n, 1 To 1) As Variant
Dim i As Long
Dim t As Single
t = Timer
For i = 10 To n
x(i, 1) = WorksheetFunction.Average _
(Range(Cells(i - 9, 1), Cells(i, 1)))
Next
Range("B10").Resize(n - 9).Value = x
Debug.Print "try1", Timer - t
End Sub
'-------------------------------------------------
Sub try2()
Dim r As Range
Dim x(0 To n - 10, 1 To 1)
Dim i As Long
Dim t As Single
t = Timer
Set r = Range("A1:A10")
With WorksheetFunction
For i = 0 To UBound(x)
x(i, 1) = .Average(r.Offset(i))
Next
End With
Range("C10").Resize(n - 9).Value = x
Set r = Nothing
Debug.Print "try2", Timer - t
End Sub
'-------------------------------------------------
Sub try3()
Dim v
Dim w(0 To 9)
Dim x(1 To n - 9, 1 To 1)
Dim i As Long
Dim j As Long
Dim t As Single
t = Timer
v = Range("A1").Resize(n).Value
With WorksheetFunction
For i = 1 To n - 9
For j = 0 To 9
w(j) = v(i + j, 1)
Next
x(i, 1) = .Average(w)
Next
End With
Range("D10").Resize(n - 9).Value = x
Debug.Print "try3", Timer - t
End Sub
'-------------------------------------------------
Sub try4()
Dim t As Single
t = Timer
With Range("E10").Resize(n - 9)
.Formula = "=average(A1:A10)"
.Value = .Value
End With
Debug.Print "try4", Timer - t
End Sub
No.1
- 回答日時:
>Range(Y(i - 9, 2), Y(i, 2))
配列Yがマイナス(1-9=-8)になるのは変ですし、Yには値が入りますので、
Range型にもならないのでは?
10行を1行ずつずらした場合、1~1000行なら回数は991回だと思うのですが・・・
Sub test()
Dim r As Range
Dim i As Integer
Dim v(1 To 991, 1 To 2) As Variant
Set r = Range("B1:B10")
For i = 1 To 991
v(i, 1) = WorksheetFunction.Average(r)
v(i, 2) = r.Address
Set r = r.Offset(1)
Next
Range("C1:D991").Value = v
End Sub
違ってたらスル~して下さい。
この回答への補足
おっしゃる通りWorksheetFunction.Average(Range(Y(i - 9, 2), Y(i, 2)))
という表記は実行するとエラーになります。
このようなことが出来ればいいな、ということで記載しました。
教えていただいた方法を参考にして検証してみます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
- Visual Basic(VBA) VBA横データを縦にしたいです 2 2023/08/08 19:38
- Visual Basic(VBA) Sheet1のA列にコードB列にメアド、Sheet2のB列にコード一覧とD列にメアド一覧があり、Sh 3 2022/10/19 11:57
- Visual Basic(VBA) ExcelVBAで、index、match関数を使用して、指定範囲に出力したい 3 2022/10/18 21:53
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
- Visual Basic(VBA) VBA 請求書自動作成 3 2022/04/24 01:58
- Excel(エクセル) Excelマクロ 同列内で複数の数式を繰り返す方法 5 2022/05/22 13:58
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) エラーコード1004 6 2022/06/09 14:12
- Excel(エクセル) エクセルで同じ数字同士を自動で線で結ぶVBAを教えてください 6 2022/04/26 23:13
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
array関数で格納した配列の型を...
-
ExcelのINDEXとMATCH関数でスピ...
-
エクセルで、絶対値の平均を算...
-
Excelのセルの色指定をVBAから...
-
VBA 配列に格納した値の平均の...
-
【VBA】配列に数式を仕込む方法...
-
数字をコンマで区切った文字列...
-
エクセルでエラーを無視して一...
-
表にフィルターをかけ、絞った...
-
VBA listBoxについて
-
[エクセル]連続する指定範囲か...
-
【VBA】配列に格納したデータを...
-
配列がとびとびである場合の書き方
-
エクセル 条件を指定した標準...
-
ExcelVBAの配列変数で一括でセ...
-
[VBA]改行入りのセルの値を配列...
-
VB6.0 ファイルの一括読込み
-
Split関数でLong配列に格納する...
-
スプレットシートのGetTextにつ...
-
読み込みで一行おきに配列に格納
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelのセルの色指定をVBAから...
-
ExcelのINDEXとMATCH関数でスピ...
-
array関数で格納した配列の型を...
-
[エクセル]連続する指定範囲か...
-
表にフィルターをかけ、絞った...
-
VBA listBoxについて
-
エクセルで、絶対値の平均を算...
-
配列がとびとびである場合の書き方
-
DataSetから、DataTableを取得...
-
[VBA]改行入りのセルの値を配列...
-
VBA 配列に格納した値の平均の...
-
配列のSession格納、及び取得方...
-
【VBA】ユーザーフォーム リス...
-
エクセルでエラーを無視して一...
-
SUMPRODUCT関数を用いた最小値
-
Excel VBA 配列の分割について
-
Excelのオートフィルタで抽出し...
-
VB6.0 ファイルの一括読込み
-
Excel オートフィルタのリスト...
-
VBAで指定期間の範囲を抽出し、...
おすすめ情報