Script事業案内エクセル以外ホームマクロVBAマクロVBA技術解説スピルって速いの?スピルの速度について最終更新日:2020-02-04スピルって速いの?スピルの速度についてOffice365を導入する企業や個人で契約する人もかなり増えてきているようです。自然とスピルや新関数を使う事も増えてくるでしょう。組み合わせて入れなければならず、これを難しく感じていた人も多いでしょう。スピルの登場によって、単純にセル範囲を指定(相対参照)するだけで、勝手に答えの範囲に値が出力されるようになりました。いえ、使いこなすには配列の考え方も必要になりますし、スピルできる場合スピルできない場合等々、いろいろと覚えなければならないことも多くあります。以下では、数式を入れる際にスピルを使った場合とスピルを使わない旧来の入れ方をした場合の速度差について検証します。スピルの一般的な説明については以下を参照してください。スピルについて2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。スピルとは何か、その概要について説明します。以下で、「従来」または「旧」と呼んでいるのはスピルしないエクセルを指しています。数式の速度で気になるのは、大量データを処理する数式の場合になります。10万行のデータから、検索値で行を特定して、該当行の他の列のデータを取得します。VLOOKUP関数です。VLOOKUP関数で一番困ってしまうのが、検索する列より左の列のデータを取得できないことです。左側の列を取得(MATCH,INDEX,OFFSET)エクセルの数ある関数の中でも頻繁に使われるVLOOKUP関数は非常に便利な関数ですが、キー列(検索値を探す列)より左側にある列を取得できません。これは仕様で仕方ないのですが時に不便な場合があります。キー列より左側の列を取得したい場合には、VLOOKUP関数ではなく他の関数を使って実現します。鳴り物入りで登場したXLOOKUP関数は、VLOOKUPの不便だった点が大幅に改良されています。=XLOOKUP(検索値,検索範囲,戻り範囲,見つからない場合,一致モード,検索モード)「検索範囲」は、縦だけではなく横にも対応しています。VLOOKUP関数とHLOOKUP関数の両方に対応しています。検索範囲とは別に「戻り範囲」が指定できるため、検索列より左の列も取得できます。「一致モード」「検索モード」と至れり尽くせりの機能となっています。具体的な使い方の例がMicrosoftの公式ページで案内されていますのでご覧ください。・単一参照(検索値に単一セルを指定)・共通参照(検索値にセル範囲を指定)・スピルこの3パターンの指定をした場合、つまり3*3=9通りの数式を入れた時の速度比較になります。具体的な数式については、以下のVBAをご覧ください。速度検証に使うシート10万行のデータになります。F列の値でA列を検索し、D列の値をG列に表示します。以下のVBAでは、このデータも作成しています。新規ブックの標準モジールに以下のVBAを貼り付けるだけで実行できます。※既存ブックでも良いですが、シート全体をクリアしているので注意してください。Function数式ごとのFunctionVLOOKUP_単一参照VLOOKUP_共通参照VLOOKUP_スピルXLOOKUP_単一参照XLOOKUP_共通参照XLOOKUP_スピルINDEX_MATCH_単一参照INDEX_MATCH_共通参照INDEX_MATCH_スピルClearAndWaitは、各Function間の影響を少しでも減らすために適当に待ちを入れたものです。先頭のTestMainを実行すると、2番目のシートに結果が一覧で出力されます。先頭の2シートは全セルをClearしているので、既存ブックで実行する場合は注意してください。実行時間は、この下表の時間合計+Sleep時間があるので、かなりの時間(20分弱)が必要になります。29.188※数は秒数VLOOKUP、XLOOKUP、INDEX+MATCH、これらの時間差は測定誤差範囲でしょう。この結果を見る限り、スピルが完全に遅くなっています。単純にスピルが遅いという結論を出すのは早計すぎます。このような使い方をした場合の、ある環境下での結果でしかないことは言っておきます。このような使い方はごく普通の使い方ですし、VLOOKUPの後継関数として考えたら頻出の状況と言えます。この結果はVBAで実行した場合だけではなく、手動で行ったときもほぼ同様の結果(手動なので感覚的な時間)となっていることも確認しています。データ部分をスピル系の関数で作成した場合等では全く違った結果となることも確認できています。ツイッターでやり取りした範囲では、他のPCでは違う結果になっているようでもあります。この検証をしたPCが特殊かと言うとそんなこともないと思います。検証日:2020/2/4気になる点は、Office2016→Office2019→Office365と順にインストールして使っている点と、Office2010も別途入れている事くらいでしょうか。特段珍しい事でもありませんし、少なくとも同様の状態のPCは相当数あるはずだと思います。スピルの速度の最後にスピルにしろXLOOKUP関数にしろ、非常に便利な機能・関数です。仮にこの結果通りで仕方ないものとしても、それを上回る利便性は間違いなくあります。この結果自体も、今後のアップデートやPCの状態によって変わってくるとも思います。テスト方法がおかしいとか、結果が全く違うとか、または同様の結果になったとか、そういう情報をツイッター(@yamaoka_ss)でお知らせいただくと大変ありがたいですし、お話がいろいろとできると思います。「お問い合わせ」からメールでお知らせいただいても結構です。Next)|VBA入門8.セルに文字を入れるとは(Range,Value)|VBA入門9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説10.ひらがな⇔カタカナの変換|エクセル基本操作ホームマクロVBAマクロVBA技術解説スピルって速いの?スピルの速度についてこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。GASスプレッドシート入門スプレッドシート応用GAS入門GAS応用ホームマクロVBAマクロVBA技術解説スピルって速いの?スピルの速度についてこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。