VBA技術解説
スピルって速いの?スピルの速度について

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2022-09-19

スピルって速いの?スピルの速度について


Office365を導入する企業や個人で契約する人もかなり増えてきているようです。
自然とスピルや新関数を使う事も増えてくるでしょう。


今まで範囲内に同じ数式を入れるときは、絶対参照・相対参照を組み合わせて入れなければならず、これを難しく感じていた人も多いでしょう。
スピルの登場によって、単純にセル範囲を指定(相対参照)するだけで、勝手に答えの範囲に値が出力されるようになりました。
とはいえ、使いこなすには配列の考え方も必要になりますし、スピルできる場合スピルできない場合等々、いろいろと覚えなければならないことも多くあります。

以下では、数式を入れる際にスピルを使った場合とスピルを使わない旧来の入れ方をした場合の速度差について検証します。

スピルの一般的な説明については以下を参照してください。
スピルについて
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事

後日追加訂正

上記ページで書いていますが、
当初このテストをした時点では、スピル数式を.Valueに入れていました。
しかし、その後の変更?で.Valueではインターセクション演算子が付いてしまってスピルしません。
そこで、Formula2へスピル数式をいれるように変更して再確認しました。
以下に掲載のVBAはFormula2に変更してあります。
また測定時間は、検証結果として下に追記しました。2022.9.19

このページでは、当初Value,Value2でもスピルするように書いていました。その後スピルしないことが確認できて修正しています。


速度検証に使う関数

数式の速度で気になるのは、大量データを処理する数式の場合になります。
10万行のデータから、検索値で行を特定して、該当行の他の列のデータを取得します。
そうです、VLOOKUP関数です。
VLOOKUP関数で一番困ってしまうのが、検索する列より左の列のデータを取得できないことです。
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
・VLOOKUP関数 ・キー列より左側の列を取得したい ・MATCH関数 ・INDEX関数 ・OFFSET関数 ・MATCH関数とINDEX関数を使う ・MATCH関数とOFFSET関数を使う ・キー列より左側の列を取得のまとめ ・配列を使いVLOOKUPでキー列より左側の列を取得
鳴り物入りで登場したXLOOKUP関数は、VLOOKUPの不便だった点が大幅に改良されています。
=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)

「検索範囲」は、縦だけではなく横にも対応しています。
つまり、VLOOKUP関数HLOOKUP関数の両方に対応しています。
検索範囲とは別に「戻り範囲」が指定できるため、検索列より左の列も取得できます。
さらに、不一致の場合のエラー値表示を変更できたり、
「一致モード」「検索モード」と至れり尽くせりの機能となっています。
XLOOKUP関数の詳細については、以下を参照してください。
XLOOKUP関数|エクセル入門
・XLOOKUP関数の書式 ・従来の関数の代わりとして ・XLOOKUP関数をスピルさせる ・見つからない場合 ・一致モードの使い方 ・検索モードの使い方 ・XLOOKUP関数をネストして戻り列を可変にする ・XLOOKUP関数の戻りセル範囲を別の関数で使う ・XLOOKUP関数が縦横に同時にスピルしないことについて ・スピルと新関数の練習 ・XLOOKUP関数のVBA使用例 ・スピルによって新しく追加された関数

以下の検証では、
・VLOOKUP
・XLOOKUP
・INDEX+MATCH
この3パターンの関数に対して、
・単一参照(検索値に単一セルを指定)
・共通参照(検索値にセル範囲を指定)
・スピル
この3パターンの指定をした場合、つまり3*3=9通りの数式を入れた時の速度比較になります。
具体的な数式については、以下のVBAをご覧ください。


速度検証に使うシート

VBA マクロ スピル XLOOKUP

10万行のデータになります。
F列の値でA列を検索し、D列の値をG列に表示します。

以下のVBAでは、このデータも作成しています。
新規ブックの標準モジールに以下のVBAを貼り付けるだけで実行できます。
※既存ブックでも良いですが、シート全体をクリアしているので注意してください。


スピルの速度を検証するVBAコード

Option Explicit

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub TestMain()
  Dim ws As Worksheet
  With ThisWorkbook
    If .Worksheets.Count = 1 Then
      .Worksheets.Add After:=ThisWorkbook.Worksheets(1)
    End If
    .Worksheets(1).Cells.Clear
    .Worksheets(2).Cells.Clear
    Set ws = .Worksheets(1)
  End With
  
  Dim i As Long
  Dim ary(1 To 9, 1 To 5) As Double
  For i = 1 To 100000
    ws.Cells(i, 1) = "a" & i
    ws.Cells(i, 2) = "b" & i
    ws.Cells(i, 3) = "c" & i
    ws.Cells(i, 4) = "d" & i
    ws.Cells(i, 6) = "a" & i
  Next

  For i = 1 To 5
    Call ClearAndWait(ws)
    ary(1, i) = VLOOKUP_単一参照(ws)
    Call ClearAndWait(ws)
    ary(2, i) = VLOOKUP_共通参照(ws)
    Call ClearAndWait(ws)
    ary(3, i) = VLOOKUP_スピル(ws)
    Call ClearAndWait(ws)
    ary(4, i) = XLOOKUP_単一参照(ws)
    Call ClearAndWait(ws)
    ary(5, i) = XLOOKUP_共通参照(ws)
    Call ClearAndWait(ws)
    ary(6, i) = XLOOKUP_スピル(ws)
    Call ClearAndWait(ws)
    ary(7, i) = INDEX_MATCH_単一参照(ws)
    Call ClearAndWait(ws)
    ary(8, i) = INDEX_MATCH_共通参照(ws)
    Call ClearAndWait(ws)
    ary(9, i) = INDEX_MATCH_スピル(ws)
    Call ClearAndWait(ws)
  Next
  
  With Worksheets(2)
    .Range("B1:F1") = WorksheetFunction.Transpose( _
             WorksheetFunction.Transpose( _
              Array("1回目", "2回目", "3回目", "4回目", "5回目")))
    .Range("A2:A10") = WorksheetFunction.Transpose( _
              Array("VLOOKUP_単一参照", _
                 "VLOOKUP_共通参照", _
                 "VLOOKUP_スピル", _
                 "XLOOKUP_単一参照", _
                 "XLOOKUP_共通参照", _
                 "XLOOKUP_スピル", _
                 "INDEX_MATCH_単一参照", _
                 "INDEX_MATCH_共通参照", _
                 "INDEX_MATCH_スピル"))
    .Range("B2:F10").Value = ary
    .Range("B2:F10").NumberFormat = "0.000"
  End With
  MsgBox "完了"
End Sub

Sub ClearAndWait(ws As Worksheet)
  Sleep 3000
  ws.Columns("G").Clear
  DoEvents
End Sub

Function VLOOKUP_単一参照(ws As Worksheet) As Double
  Dim st As Double: st = Timer
  ws.Range("G1:G100000").Value = "=VLOOKUP(F1,A$1:D$100000,4,0)"
  VLOOKUP_単一参照 = Timer - st
End Function

Function VLOOKUP_共通参照(ws As Worksheet) As Double
  Dim st As Double: st = Timer
  ws.Range("G1:G100000").Value = "=VLOOKUP(@F$1:F$100000,A$1:D$100000,4,0)"
  VLOOKUP_共通参照 = Timer - st
End Function

Function VLOOKUP_スピル(ws As Worksheet) As Double
  Dim st As Double: st = Timer
  ws.Range("G1").Formula2 = "=VLOOKUP(F1:F100000,A1:D100000,4,0)"
  VLOOKUP_スピル = Timer - st
End Function

Function XLOOKUP_単一参照(ws As Worksheet) As Double
  Dim st As Double: st = Timer
  ws.Range("G1:G100000").Value = "=XLOOKUP(F1,A$1:A$100000,D$1:D$100000)"
  XLOOKUP_単一参照 = Timer - st
End Function

Function XLOOKUP_共通参照(ws As Worksheet) As Double
  Dim st As Double: st = Timer
  ws.Range("G1:G100000").Value = "=XLOOKUP(@F$1:F$100000,A$1:A$100000,D$1:D$100000)"
  XLOOKUP_共通参照 = Timer - st
End Function

Function XLOOKUP_スピル(ws As Worksheet) As Double
  Columns("G").Clear
  Dim st As Double: st = Timer
  ws.Range("G1").Formula2 = "=XLOOKUP(F1:F100000,A1:A100000,D1:D100000)"
  XLOOKUP_スピル = Timer - st
End Function

Function INDEX_MATCH_単一参照(ws As Worksheet) As Double
  Dim st As Double: st = Timer
  ws.Range("G1:G100000").Value = "=INDEX(D$1:D$100000,MATCH(F1,$A$1:$A$100000,0))"
  INDEX_MATCH_単一参照 = Timer - st
End Function

Function INDEX_MATCH_共通参照(ws As Worksheet) As Double
  Dim st As Double: st = Timer
  ws.Range("G1:G100000").Value = "=INDEX(D$1:D$100000,MATCH(@F$1:F$100000,A$1:A$100000,0))"
  INDEX_MATCH_共通参照 = Timer - st
End Function

Function INDEX_MATCH_スピル(ws As Worksheet) As Double
  Dim st As Double: st = Timer
  ws.Range("G1").Formula2 = "=INDEX(D1:D100000,MATCH(F1:F100000,A1:A100000,0))"
  INDEX_MATCH_スピル = Timer - st
End Function

数式ごとのFunction
VLOOKUP_単一参照
VLOOKUP_共通参照
VLOOKUP_スピル
XLOOKUP_単一参照
XLOOKUP_共通参照
XLOOKUP_スピル
INDEX_MATCH_単一参照
INDEX_MATCH_共通参照
INDEX_MATCH_スピル

ClearAndWaitは、各Function間の影響を少しでも減らすために適当に待ちを入れたものです。

新規ブックの標準モジールにこのVBAを貼り付けて、
先頭のTestMainを実行すると、2番目のシートに結果が一覧で出力されます。
先頭の2シートは全セルをClearしているので、既存ブックで実行する場合は注意してください。

実行時間は、この下表の時間合計+Sleep時間があるので、かなりの時間(20分弱)が必要になります。


スピルの速度を検証した結果

1回目 2回目 3回目 4回目 5回目
VLOOKUP_単一参照 13.977 13.914 13.063 12.930 12.977
VLOOKUP_共通参照 14.141 13.797 13.914 13.773 13.844
VLOOKUP_スピル 29.344 29.133 29.211 29.305 29.180
XLOOKUP_単一参照 13.805 13.820 13.852 13.797 14.117
XLOOKUP_共通参照 13.836 13.836 13.836 13.945 13.930
XLOOKUP_スピル 30.227 29.844 30.125 30.633 29.945
INDEX_MATCH_単一参照 13.844 14.438 13.859 13.859 14.156
INDEX_MATCH_共通参照 14.008 14.047 13.898 13.883 13.867
INDEX_MATCH_スピル 29.531 29.156 29.211 29.141 29.188
※数は秒数

VLOOKUP、XLOOKUP、INDEX+MATCH、これらの時間差は測定誤差範囲でしょう。
この結果を見る限り、スピルが完全に遅くなっています。
もちろん、単純にスピルが遅いという結論を出すのは早計すぎます。
あくまでこのような使い方をした場合の、ある環境下での結果でしかないことは言っておきます。
ただし、このような使い方はごく普通の使い方ですし、VLOOKUPの後継関数として考えたら頻出の状況と言えます。

この結果はVBAで実行した場合だけではなく、手動で行ったときもほぼ同様の結果(手動なので感覚的な時間)となっていることも確認しています。
ただし、データ部分をスピル系の関数で作成した場合等では全く違った結果となることも確認できています。

ツイッターでやり取りした範囲では、他のPCでは違う結果になっているようでもあります。
ですが、この検証をしたPCが特殊かと言うとそんなこともないと思います。

検証日:2020/2/4

VBA マクロ スピル XLOOKUP

VBA マクロ スピル XLOOKUP

気になる点は、
Office2016→Office2019→Office365
と順にインストールして使っている点と、Office2010も別途入れている事くらいでしょうか。
特段珍しい事でもありませんし、少なくとも同様の状態のPCは相当数あるはずだと思います。


後日の再検証

最初に書いた通り、「2022.9.19」現在で再検証しました。
検証したPCは前回と同じPCです。

1回目 2回目 3回目 4回目 5回目
VLOOKUP_単一参照 8.212 9.699 10.551 9.813 9.769
VLOOKUP_共通参照 10.459 10.930 10.553 10.649 10.471
VLOOKUP_スピル 20.202 21.651 20.174 20.340 20.858
XLOOKUP_単一参照 11.050 11.656 11.017 10.896 10.897
XLOOKUP_共通参照 10.903 11.230 10.904 11.390 10.928
XLOOKUP_スピル 21.122 20.928 21.016 21.867 25.654
INDEX_MATCH_単一参照 10.651 10.698 10.998 10.694 11.288
INDEX_MATCH_共通参照 10.906 10.745 11.135 10.718 10.772
INDEX_MATCH_スピル 20.506 20.446 20.162 20.331 20.626

全体的に少し速くなっているようですが、その中でもスピルの速度が少し改善しているような傾向がみられました。


スピルの速度の最後に

スピルにしろXLOOKUP関数にしろ、非常に便利な機能・関数です。
仮にこの結果通りで仕方ないものとしても、それを上回る利便性は間違いなくあります。
この結果自体も、今後のアップデートやPCの状態によって変わってくるとも思います。

テスト方法がおかしいとか、結果が全く違うとか、または同様の結果になったとか、
そういう情報をツイッター(@yamaoka_ss)でお知らせいただくと大変ありがたいですし、お話がいろいろとできると思います。
もちろん、「お問い合わせ」からメールでお知らせいただいても結構です。
・お問い合わせ入力フォーム ・お問い合わせについての注意事項 ・お返事について




同じテーマ「マクロVBA技術解説」の記事

エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
大量データで処理時間がかかる関数の対処方法(SumIf)
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
Withステートメントの実行速度と注意点
IfステートメントとIIF関数とMax関数の速度比較
スピルって速いの?スピルの速度について


新着記事NEW ・・・新着記事一覧を見る

数字(1~50)を丸付き数字に変換するVBA|VBA技術解説(2022-11-15)
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)|エクセル入門(2022-11-14)
TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)|エクセル入門(2022-11-14)
TEXTSPLIT関数(列と行の区切り記号で文字列を分割)|エクセル入門(2022-11-12)
LAMBDA以降の新関数はVBAで使えるか|VBA技術解説(2022-11-11)
WRAPCOLS関数(1次元配列を指定数の列で折り返す)|エクセル入門(2022-11-08)
WRAPROWS関数(1次元配列を指定数の行で折り返す)|エクセル入門(2022-11-08)
EXPAND関数(配列を指定された行と列に拡張する)|エクセル入門(2022-11-07)
TAKE関数(配列の先頭/末尾から指定行/列数を取得)|エクセル入門(2022-11-06)
DROP関数(配列の先頭/末尾から指定行/列数を除外)|エクセル入門(2022-11-06)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.エクセルVBAでのシート指定方法|VBA技術解説
9.マクロって何?VBAって何?|VBA入門
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ