目的
Excelで任意のセルの値を参照抽出したい場合の関数がいくつか用意されていますが、それぞれの処理速度を調べました。対象はINDIRECT関数、OFFSET関数、INDEX関数、参考にVLOOKUP関数。
例えば、Excelで生データを必要な順番に並び替えた上で解析したい場合などに、「Sheet1に生データを入力し、Sheet2で行数と列数を指定して抽出・解析する」ということを想定しています。
…まぁVBA使えばいいんですけどね。
評価方法
評価方法として、下のようにSheet1に被参照データ(100万個)、Sheet2に参照データ(100万個)を用意しました。
Sheet1には、1行目およびA列目に0~1000の数値が入力されており、B2セルより右下に行×列の数式が入力されています。
Sheet2には、A1セルに"Sheet1"の文字列が、1行目およびA列目に0~1000の数値が入力されており、B2セルより右下に各関数を用いた参照式が入力されています。
このExcelを再計算したときの処理時間の10回平均を3回求めました。
結果
結果をまとめると下記のようになりました。
INDIRECT関数だけがSheet名指定ができる一方で極端に遅く、INDEX関数は用途が限られますが非常に高速です。
バランスを考えるとOFFSET関数が、体感的な処理速度が十分高速で、記述が短くて済み、範囲参照もできるため、汎用的でいいように思います。
関数 | Sheet名指定 | 範囲参照 | 数式文字数 | 処理時間1 [sec] | 処理時間2 [sec] | 処理時間3 [sec] |
---|---|---|---|---|---|---|
INDIRECT関数 + ADDRESS関数 | ○ | ○ | 36 | 5.248193 | 5.297607 | 5.202295 |
OFFSET関数 + INDIRECT関数 | ○ | ○ | 43 | 5.235791 | 5.148193 | 5.283008 |
OFFSET関数 | × | ○ | 32 | 0.2808105 | 0.2843262 | 0.2863281 |
INDEX関数 + INDIRECT関数 | ○ | × | 45 | 5.406054 | 5.347656 | 5.247851 |
INDEX関数 | × | × | 34 | 0.0008300781 | 0.001513672 | 0.00078125 |
VLOOKUP関数 | × | × | 44 | 0 | 0.00078125 | 0 |
参考
B2セルの各関数の数式
INDIRECT + ADDRESS
=INDIRECT($A$1&"!"&ADDRESS($A2,B$1))
OFFSET + INDIRECT
=OFFSET(INDIRECT($A$1&"!$A$1"),$A2-1,B$1-1)
OFFSET
=OFFSET(Sheet1!$A$1,$A2-1,B$1-1)
INDEX + INDIRECT
=INDEX(INDIRECT($A$1&"!$1:$1048576"),$A2,B$1)
INDEX
=INDEX(Sheet1!$1:$1048576,$A2,B$1)
VLOOKUP
=VLOOKUP($A2-1,Sheet1!$1:$1048576,B$1,FALSE)
処理速度計算VBA
Sub Macro1()
Dim startTime, endTime, time As Single
time = 0
For i = 1 To 10
startTime = Timer
Calculate
endTime = Timer
time = time + endTime - startTime
Next
time = time / 10
MsgBox (time)
End Sub