Excel 任意のセルの値を行 / 列数を指定して参照抽出する関数と処理速度

5671 ワード
Excel関数 Excel エクセル Excel テキストリンク

目的

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