スピルって速いの?スピルの速度について
Office365を導入する企業や個人で契約する人もかなり増えてきているようです。
自然とスピルや新関数を使う事も増えてくるでしょう。
スピルの登場によって、単純にセル範囲を指定（相対参照）するだけで、勝手に答えの範囲に値が出力されるようになりました。
とはいえ、使いこなすには配列の考え方も必要になりますし、スピルできる場合スピルできない場合等々、いろいろと覚えなければならないことも多くあります。
スピルについて
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事
後日追加訂正
上記ページで書いていますが、
当初このテストをした時点では、スピル数式を.Valueに入れていました。
しかし、その後の変更?で.Valueではインターセクション演算子が付いてしまってスピルしません。
そこで、Formula2へスピル数式をいれるように変更して再確認しました。
以下に掲載のVBAはFormula2に変更してあります。
また測定時間は、検証結果として下に追記しました。2022.9.19
当初このテストをした時点では、スピル数式を.Valueに入れていました。
しかし、その後の変更?で.Valueではインターセクション演算子が付いてしまってスピルしません。
そこで、Formula2へスピル数式をいれるように変更して再確認しました。
以下に掲載のVBAはFormula2に変更してあります。
また測定時間は、検証結果として下に追記しました。2022.9.19
速度検証に使う関数
数式の速度で気になるのは、大量データを処理する数式の場合になります。
10万行のデータから、検索値で行を特定して、該当行の他の列のデータを取得します。
そうです、VLOOKUP関数です。
VLOOKUP関数で一番困ってしまうのが、検索する列より左の列のデータを取得できないことです。
10万行のデータから、検索値で行を特定して、該当行の他の列のデータを取得します。
そうです、VLOOKUP関数です。
VLOOKUP関数で一番困ってしまうのが、検索する列より左の列のデータを取得できないことです。
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
・VLOOKUP関数 ・キー列より左側の列を取得したい ・MATCH関数 ・INDEX関数 ・OFFSET関数 ・MATCH関数とINDEX関数を使う ・MATCH関数とOFFSET関数を使う ・キー列より左側の列を取得のまとめ ・配列を使いVLOOKUPでキー列より左側の列を取得
鳴り物入りで登場したXLOOKUP関数は、VLOOKUPの不便だった点が大幅に改良されています。
=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)
「検索範囲」は、縦だけではなく横にも対応しています。
つまり、VLOOKUP関数とHLOOKUP関数の両方に対応しています。
検索範囲とは別に「戻り範囲」が指定できるため、検索列より左の列も取得できます。
さらに、不一致の場合のエラー値表示を変更できたり、
「一致モード」「検索モード」と至れり尽くせりの機能となっています。
XLOOKUP関数の詳細については、以下を参照してください。
つまり、VLOOKUP関数とHLOOKUP関数の両方に対応しています。
検索範囲とは別に「戻り範囲」が指定できるため、検索列より左の列も取得できます。
さらに、不一致の場合のエラー値表示を変更できたり、
「一致モード」「検索モード」と至れり尽くせりの機能となっています。
XLOOKUP関数の詳細については、以下を参照してください。
XLOOKUP関数｜エクセル入門
・XLOOKUP関数の書式 ・従来の関数の代わりとして ・XLOOKUP関数をスピルさせる ・見つからない場合 ・一致モードの使い方 ・検索モードの使い方 ・XLOOKUP関数をネストして戻り列を可変にする ・XLOOKUP関数の戻りセル範囲を別の関数で使う ・XLOOKUP関数が縦横に同時にスピルしないことについて ・スピルと新関数の練習 ・XLOOKUP関数のVBA使用例 ・スピルによって新しく追加された関数
以下の検証では、
・VLOOKUP
・XLOOKUP
・INDEX+MATCH
この3パターンの関数に対して、
・単一参照（検索値に単一セルを指定）
・共通参照（検索値にセル範囲を指定）
・スピル
この3パターンの指定をした場合、つまり3*3=9通りの数式を入れた時の速度比較になります。
具体的な数式については、以下のVBAをご覧ください。
・VLOOKUP
・XLOOKUP
・INDEX+MATCH
この3パターンの関数に対して、
・単一参照（検索値に単一セルを指定）
・共通参照（検索値にセル範囲を指定）
・スピル
この3パターンの指定をした場合、つまり3*3=9通りの数式を入れた時の速度比較になります。
具体的な数式については、以下のVBAをご覧ください。
速度検証に使うシート
F列の値でA列を検索し、D列の値をG列に表示します。
新規ブックの標準モジールに以下の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_スピル
VLOOKUP_共通参照
VLOOKUP_スピル
XLOOKUP_単一参照
XLOOKUP_共通参照
XLOOKUP_スピル
INDEX_MATCH_単一参照
INDEX_MATCH_共通参照
INDEX_MATCH_スピル
ClearAndWaitは、各Function間の影響を少しでも減らすために適当に待ちを入れたものです。
新規ブックの標準モジールにこのVBAを貼り付けて、
先頭のTestMainを実行すると、2番目のシートに結果が一覧で出力されます。
先頭の2シートは全セルをClearしているので、既存ブックで実行する場合は注意してください。
実行時間は、この下表の時間合計+Sleep時間があるので、かなりの時間（20分弱）が必要になります。
先頭のTestMainを実行すると、2番目のシートに結果が一覧で出力されます。
先頭の2シートは全セルをClearしているので、既存ブックで実行する場合は注意してください。
スピルの速度を検証した結果
| 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の後継関数として考えたら頻出の状況と言えます。
ただし、データ部分をスピル系の関数で作成した場合等では全く違った結果となることも確認できています。
ですが、この検証をしたPCが特殊かと言うとそんなこともないと思います。
Office2016→Office2019→Office365
と順にインストールして使っている点と、Office2010も別途入れている事くらいでしょうか。
特段珍しい事でもありませんし、少なくとも同様の状態のPCは相当数あるはずだと思います。
後日の再検証
最初に書いた通り、「2022.9.19」現在で再検証しました。
検証したPCは前回と同じPCです。
全体的に少し速くなっているようですが、その中でもスピルの速度が少し改善しているような傾向がみられました。
検証した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）でお知らせいただくと大変ありがたいですし、お話がいろいろとできると思います。
もちろん、「お問い合わせ」からメールでお知らせいただいても結構です。
仮にこの結果通りで仕方ないものとしても、それを上回る利便性は間違いなくあります。
この結果自体も、今後のアップデートやPCの状態によって変わってくるとも思います。
そういう情報をツイッター（@yamaoka_ss）でお知らせいただくと大変ありがたいですし、お話がいろいろとできると思います。
もちろん、「お問い合わせ」からメールでお知らせいただいても結構です。
・お問い合わせ入力フォーム ・お問い合わせについての注意事項 ・お返事について
同じテーマ「マクロVBA技術解説」の記事
エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
Withステートメントの実行速度と注意点
IfステートメントとIIF関数とMax関数の速度比較
スピルって速いの?スピルの速度について
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- スピルって速いの?スピルの速度について
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。