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

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

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


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


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

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

スピルの一般的な説明については以下を参照してください。
スピルについて
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。


速度検証に使う関数

数式の速度で気になるのは、大量データを処理する数式の場合になります。
10万行のデータから、検索値で行を特定して、該当行の他の列のデータを取得します。
そうです、VLOOKUP関数です。
VLOOKUP関数で一番困ってしまうのが、検索する列より左の列のデータを取得できないことです。
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
エクセルの数ある関数の中でも頻繁に使われるVLOOKUP関数は非常に便利な関数ですが、キー列(検索値を探す列)より左側にある列を取得できません。これは仕様で仕方ないのですが時に不便な場合があります。キー列より左側の列を取得したい場合には、VLOOKUP関数ではなく他の関数を使って実現します。
鳴り物入りで登場したXLOOKUP関数は、VLOOKUPの不便だった点が大幅に改良されています。
=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)

「検索範囲」は、縦だけではなく横にも対応しています。
つまり、VLOOKUP関数HLOOKUP関数の両方に対応しています。
検索範囲とは別に「戻り範囲」が指定できるため、検索列より左の列も取得できます。
さらに、不一致の場合のエラー値表示を変更できたり、
「一致モード」「検索モード」と至れり尽くせりの機能となっています。
具体的な使い方の例がMicrosoftの公式ページで案内されていますのでご覧ください。
以下の検証では、
・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").Value = "=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").Value = "=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").Value = "=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は相当数あるはずだと思います。

スピルの速度の最後に

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

テスト方法がおかしいとか、結果が全く違うとか、または同様の結果になったとか、
そういう情報をツイッター(@yamaoka_ss)でお知らせいただくと大変ありがたいですし、お話がいろいろとできると思います。
もちろん、「お問い合わせ」からメールでお知らせいただいても結構です。
各種ソフト、ホームページに関するへの質問、問合せを受付けます。



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

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


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

ユーザーに絶対に停止させたくない場合のVBA設定|VBA技術解説(4月1日)
CharactersプロパティとCharactersオブジェクト|VBA技術解説(3月31日)
指数近似/対数近似/累乗近似(掲載順位とCTR)|エクセル関数超技(3月31日)
練習問題32(連続数値部分を取り出し記号で連結)|VBA練習問題(3月24日)
連続数値部分を取り出し記号で連結|エクセル関数超技(3月24日)
数式バーの高さを数式の行数で自動設定|VBAサンプル集(3月21日)
LET関数(数式で変数を使う)|エクセル入門(3月21日)
スピルに対応したXSPLITユーザー定義関数(文字区切り)|VBAサンプル集(3月15日)
XMATCH関数(範囲から値を検索し一致する相対位置)|エクセル入門(3月14日)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)|エクセル入門(3月14日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.繰り返し処理(For Next)|VBA入門
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.セルに文字を入れるとは(Range,Value)|VBA入門




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


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



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