0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

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

Posted at

目的

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回求めました。

Sheet1
image.png

結果

結果をまとめると下記のようになりました。
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
0
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?