Excelのvlookup-どの式が最速ですか？

今日、私たちはいくつかの異なる式でExcelでvlookupを行い、計算速度を測定し、一貫性を評価するために、勝者を選択できます。

異なるテーブルで情報を見つけることは、Excelで最も一般的なタスクの1つです。残念ながら、古典的なvlookup機能は、パワーと柔軟性の処理に関しては有名です。長年にわたって、Excelユーザーがインデックスマッチフォーミュラなどの独自のソリューションを思いついたのも不思議ではありません。幸いなことに、MicrosoftはついにVlookupの弱点が多すぎることに気付き、より強力な後継者であるXlookup機能をリリースしました。それで、どれが使用するのに最適ですか？

テーブルに数十行しかない場合、おそらくどの方法も十分に速く、違いに気付くことはありません。しかし、行の数が数千で測定される場合、関数の正しい選択が重要です - パフォーマンスの違いは10倍以上になります！それでは、タイマーを始めて、誰が最速かを見てみましょう:)

• ソースデータ
• vlookup関数
• インデックスマッチ式
• オフセットマッチ式
• xlookup関数
• Excelテーブルのvlookup
• 動的配列を使用したvlookup
• パワークエリでマッチを引く
• 追加ボーナス：テーブルウィザードのマージ
• 要約と結論

ソースデータ

すべての例では、次のデータセットを使用しています。

• 500,000行を含むメインテーブル
• 500行を含むルックアップテーブル

私たちの目標は、アイテム名を一致させ、ルックアップテーブルからメインテーブルに価格を引き出すことです。

各方法について、C2の式を入力し、C500001を介してコピーし、50万セルを計算するのにかかる時間を測定します。

当然、結果はCPUのパフォーマンス、RAMサイズ、Excelバージョンなどの多くの要因に依存します。この実験では、重要なのは絶対的な数ではありません。比較して各式のパフォーマンス、その利点と欠点を理解することがより重要です。

私たちの例は、あなたが関数の基本的な知識を持っていることを前提としており、私たちはそれらの構文についてあまり留まらないでしょう。詳細なチュートリアルへのリンクは、便利なために含まれています。

すべてのテストは、Microsoft 365アプリをインストールするためのMicrosoft 365アプリを備えたDellラップトップ（Intel Core I5-8250U; RAM 16 GB）で実行されました。 Excel 32-Bit、バージョン2011、ビルド13415、ベータチャネル。同じテストもExcel 64ビットで私の同僚によって実行され、いくつかの結果は劇的に異なります！

vlookupフォーミュラ

Excelで一致するデータを検索して取得することになると、頭に浮かぶ最初の機能は古き良きvlookupです。実際、それはそれほど良くありませんが、少し後でこれを話します:)

vlookup（lookup_value、table_array、col_index_num、[range_lookup]））

ルックアップテーブル（E2：F501）からメインテーブル（A2：B500001）に価格を引き出すには、vlookup式の次の引数を定義します。

• lookup_value ：b2-ルックアップテーブルで検索する値。
• Table_array ：$ e $ 3：$ f 501- $ signで参照をロックして、式をコピーするときにシフトしないように注意してください。
• col_index_num ：2-データは、ルックアップテーブルの2 ^nd列から取得されます。
• range_lookup ：false-正確な一致を探しています。

完全な式は次のとおりです。

=VLOOKUP(B2, $E$3:$F$501, 2, FALSE)

上記のフォーミュラはC2に移動し、右下隅のプラス記号をダブルクリックして、列全体に式をコピーします。ダブルクリックの時点で、ストップウォッチを実行し、この計算には6.6秒かかることがわかります。

実際のワークシートでvlookupを行うとき、多くの人が列全体を提供し、将来の可能性のある追加に対応するために列全体を提供します。

=VLOOKUP(B2, E:F, 2, FALSE)

それはパフォーマンスに影響しますか？はい、そうです。列全体を計算するには14.2秒かかります。それは信じられなかったので、私は再確認しました。同じ結果 - 範囲の半分未満。

迅速であることとは別に、あなたのフォーミュラは堅牢で耐久性も必要ですよね？残念ながら、vlookupは信頼性と回復力を誇ることはできません。

vlookupの制限

すでに述べたように、Excel vlookupには多くの刺激的な制約があります。最も重要なものは次のとおりです。

• 左を見ることができません。 vlookup関数は、テーブル配列の左端列のみを見て、右から情報を返すことができます。
• カラムの挿入や削除に耐えられません。 return列はインデックス番号として指定されているため、新しい列がテーブル配列に追加または削除されるとすぐにvlookup式が動作しなくなります。
• ルックアップ値は255文字に制限されています。

計算時間：範囲-6.6秒;列全体-14.2秒。

詳細なチュートリアル：初心者向けのExcel vlookupの例

インデックスマッチ式

多くのユーザーにとって、Excelの高度な形式のルックアップは魔法のインデックスマッチ式です。一般的な形では、次のように見えます：

index（ return_column 、match（ lookup_value 、 lookup_column 、0）））

データセットの場合、式は次の形式を取得します。

=INDEX($F$2:$F$501, MATCH(B2, $E$2:$E$501, 0))

vlookupと同様に、ルックアップに絶対的な参照を使用して、式が下のセルに正しくコピーされるようにすることを忘れないでください。

両方のテーブルが同じシートにあるため、インデックスマッチはvlookupよりもはるかに遅いパフォーマンスを発揮しました（6.6秒に対して8.9）。

しかし、ルックアップテーブルを別のワークシートに移動すると、式ははるかに速く動作し始めます（〜5秒）。これはvlookupよりも優れています。

とにかく、インデックスマッチは、多くの重要な利点で時間を補います。

インデックスマッチの利点

• 右から左に見ることができます。 YEP、インデックスマッチフォーミュラは、vlookUpとは異なり、テーブル配列ではなくルックアップ範囲を明示的に定義するため、ルックアップ列がどこにあるかを気にしません。
• カラムの挿入と削除の免疫。インデックスマッチを使用すると、インデックス番号ではなくリターン範囲を指定するため、列を安全に追加および削除できます。
• ルックアップ値のサイズに制限はありません。 vlookupは255文字に制限されていますが、インデックスマッチは長い文字列の処理に問題はありません。
• 上記のリンクされた例に示すように、複数の基準でvlookupを実行できます。
• 2次元検索を行い、特定の行と列の交差点で値を返すことができます。

計算時間：範囲-8.9秒。列全体-17.7秒。別のシートから-5.2秒。

詳細なチュートリアル：Excelのインデックスマッチ式

オフセットマッチ式

Excelで垂直に調べるもう1つの式があります。これには、Vlookupの多くの制限がありません。

offset（ lookup_table 、match（ lookup_value 、offset（ lookup_table 、0、 n 、rows（ lookup_table ）、1）、0）-1、 m 、1、1））

どこ：

• n - は、テーブルの先頭からルックアップ列に移動する列の数を指定するルックアップ列のオフセットです。
• m-は、返される列に移動する列の数を決定する返品列オフセットです。

私たちの場合、最初の列で検索しているため、ルックアップ列のオフセット（n）は0であるため、シフトは必要ありません。一致が2番目の列にあるため、返品列のオフセット（m）は1です。1列を右に移動する必要があります。

=OFFSET($E$2:$F$501, MATCH(B2, OFFSET($E$2:$F$501, 0, 0, ROWS($E$2:$F$501), 1), 0) -1, 1, 1, 1)

以前のソリューションと比較して、フォーミュラは面倒すぎますよね？ただし、vlookupまたはインデックスマッチよりもはるかに高速です。 50万列の計算には3秒未満かかります！列全体では、オフセットはわずかに遅くなります-3.5秒。

=OFFSET(E:F, MATCH(B2, OFFSET(E:F, 0, 0, ROWS(E:F), 1), 0) -1, 1, 1, 1)

ただし、Excel 64ビットでは、結果はそれほど印象的ではありません - 約7.5秒です。なんで？ MicrosoftGuysへの良い質問:)

オフセットマッチの利点

速度は別として、この式には他にもいくつかのメリットがあります。

• 右から左へのvlookupと上位のhlookupを実行できます。
• 列と行の値に基づいて双方向の検索を行うことができます。
• ルックアップテーブルから列が挿入されたり削除されたりしても、壊れません。

オフセットマッチの欠点

複雑な構文。

計算時間：範囲-2.9秒。列全体-3.5秒。

詳細なチュートリアル：

• フォーミュラの例を備えたオフセット関数
• Excelで一致機能を使用する方法

Xlookup式

Microsoft 365の加入者には、ワークシートで情報を調べるための新しいより強力な機能が提供されます。

xlookup（lookup_value、lookup_array、return_array、[match_mode]、[search_mode]、[if_not_found]）

私たちの目的のために、最後の3つの引数のデフォルトは正常に機能するため、必要な最初の3つのパラメーターのみを指定します。議論の名前は直感的であり、追加の説明なしに式を理解できると思います。

=XLOOKUP(B2, $E$2:$E$501, $F$2:$F$501)

Xlookupの利点

従来のvlookupと比較して、Xlookup関数には次のような多くの改善があります。

• 簡素化され、より意味のある構文
• 任意の方向で垂直および水平方向に検索する能力：右、左、底部、または上。
• ソートされたデータの場合、通常の検索よりもはるかに高速な特別なバイナリ検索モードがあります。
• 最後の発生を取得するには、逆順に検索します。
• 複数の値を返す機能。
• 複数の基準でExcel Xlookupで説明されているように、複数の条件を処理します。
• エラー機能の場合に組み込まれます。

Xlookupの欠点

XlookupはExcel 365および2021でのみ利用可能です。Excel2019、Excel 2016および以前のバージョンでは、サポートされていません。

そして今、この新しい機能がどれほど迅速であるか見てみましょう。 11.2秒 - 非常に残念です:(

範囲の代わりに列参照を使用した場合はどうなりますか？ =XLOOKUP(B2, E:E, F:F)

24.5秒。言葉はありません... vlookupのほぼ2倍です。

計算時間：範囲-11.2秒;列全体-24.1秒。

包括的なチュートリアル：例を備えたExcel Xlookup機能

Excelテーブルのvlookup

おそらくご存知のように、Excelテーブルのデータは、セルアドレスの代わりにテーブル名と列名を使用することにより、特別な方法で参照できます。これは構造化された参照と呼ばれ、計算速度に影響があるのではないかと思います。

これを確認するには、範囲をテーブルに変換し、式を再テストしましょう。

便利なため、私たちのテーブルはmain_table （a1：c500001）とlookup_table （e1：f5001）という名前です。

テーブルリファレンスを作成するには、最初のセル（C2）の式の入力を開始し、参照するセルと範囲を選択すると、Excelが構造化された参照を自動的に挿入します。

たとえば、vlookupの式がどのように見えるかは次のとおりです。

=VLOOKUP([@Item], Lookup_table, 2, FALSE)

Excelテーブルの優れた特徴は、1つのセルのみに式を入力するとすぐに、同じ列の他のすべてのセルにすぐに入力されることです。その上、テーブルは本質的に動的であり、自動的に展開して、テーブルの横に入力する新しいデータを含めます。

私たちのテーブルでは、2.3秒で計算されたvlookup式、インデックスは2.6秒で一致し、オフセットと2.7秒で一致し、Xlookupは3.3秒でXlookUpです。ご覧のとおり、計算速度は範囲と比較して大幅に増加します。

式は、参照のために以下にリストされています。

=INDEX(Lookup_table[Price], MATCH([@Item], Lookup_table[Item], 0))

=OFFSET(Lookup_table, MATCH([@Item], OFFSET(Lookup_table, 0, 0, ROWS(Lookup_table), 1), 0) -1, 1, 1, 1)

=XLOOKUP([@Item], Lookup_table[Item], Lookup_table[Price])

興味深いことに、Excelテーブルは、定期的な参照があっても非常に高速です。つまり、最初の範囲（A1：C500001）のみをテーブルに変換し、通常のvlookup式を使用してルックアップ範囲からデータを引き出すと、メインテーブルの列全体が約2.5秒で計算されます。

計算速度：式に応じて2.3〜3.3秒。

エンドツーエンドのチュートリアル：

• 例付きのテーブルをExcel
• Excelテーブルの構造的な参照

動的配列を使用したvlookup

2020年1月に発生したExcel 365計算エンジンの画期的な変更により、いわゆる動的配列がサポートされています。要するに、これらは自動的に計算され、1つのセルに入力された式に基づいて複数のセルに値を返すことができる復活可能な配列です。

動的配列の最良の点の1つは、ほとんどすべての従来のExcel関数で使用できることです。私たちのvlookupフォーミュラの場合、それは次のようになります：

=VLOOKUP(B2:B500001, E2:F500001, 2, FALSE)

古典的なvlookup関数との違いは、単一のルックアップ値ではなく、最初の引数にルックアップ配列全体を提供することです。式は1つのセルに入力されるため、絶対的な参照で範囲をロックすることを心配する必要はありません。

パフォーマンスに関しては、Dynamic ArrayがExcelテーブルよりもさらに速く動作します！ 50万個のセルがほぼすぐに結果に満たされています：1.8秒 - 非常に印象的です！

その他の結果を以下に示します。

インデックスマッチ-4.4秒

=INDEX(F2:F501, MATCH(B2:B500001, E2:E501, 0))

Xlookup -7.3秒

=XLOOKUP(B2:B500001, E2:E501, F2:F501)

うーん…設計による動的であると思われるXlookupは、古い関数よりも悪いパフォーマンスを発揮します。奇妙な！

計算速度：式に応じて1.8〜7.3秒。

詳細なチュートリアル：Excelの動的配列、関数、および式

パワークエリでマッチを引く

完全性のために、タスクのもう1つの可能なソリューションであるパワークエリをテストしましょう。もちろん、式の計算をクエリの更新と比較することはまったく正しくありませんが、私はより速いです:)

パワークエリを使用する詳細な手順については、以下の別のチュートリアルで説明します。ここでは、結果を評価します。

マージされたテーブルは、Powerクエリエディターから8.5秒でExcelにロードされました。式とは異なり、クエリは自動的に更新されません。ソースデータの各変更後、[データまたはクエリ]タブのいずれかの更新ボタンをクリックして、結果のテーブルを手動で更新する必要があります。 500,000行は約7秒で更新されます。悪くはありませんが、Excelフォーミュラはより良いことができます。クエリの設定はワンクリックプロセスではないことを考えると、これはおそらく私が使用する最後の方法であり、他に何も機能しない場合にのみです。

パフォーマンス：8.5秒に拡張するために読み込みます。リフレッシュ7.6秒

詳細なチュートリアル：

• テーブルとExcelパワークエリを組み合わせる方法
• Excelで電源クエリの使用方法 - 実用的な例
• 初心者向けのExcelチュートリアルのパワークエリ

追加ボーナス：テーブルウィザードのマージ

Ultimate Suiteのユーザーには、Excelツールボックスにもう1つのツールがあり、共通の列に基づいて2つのテーブルをマージします。それがExcelのものとどのように比較されるか見てみましょう。

マージテーブルウィザードを実行するには、 [Ablebitsデータ]タブの[2つのテーブル]ボタンをクリックします。そして、魔法使いの階段をたどるだけで、プロセスを進めます。

さて、完了するのに約3秒かかりました。 50万の記録にはそれほど悪くはありません！

上記のメッセージを詳しく見ると、すべての一致が見つかったわけではないことに気付くかもしれません。ただし、ツールに欠陥があるという意味ではありません。一部のアイテム（ルックアップ値）がルックアップテーブルに存在しないことを知らせることができます。 vlookup関数は、この場合に＃n/aエラーを返しますが、マージテーブルのウィザードはセルを空白のままにします。

パフォーマンス：3.2秒

詳細：Excelの2つのテーブルをマージします

要約と結論

すべての例を慎重に読むと、おそらくあなたはすでにあなた自身の結論を描いているでしょう。詳細をスキップした場合は、この比較表に簡単な要約を見つけることができます。

関数	秒単位の計算速度
	範囲	列全体	テーブル	動的配列
vlookup	6.6	14.2	2.3	1.8
インデックスマッチ	8.9	17.7	2.6	4.4
オフセットマッチ	2.9	3.5	2.7	-
xlookup	11.2	24.1	3.3	7.3
電源クエリ	8.5
テーブルをマージします	3.2

以下に、テスト結果に基づいて作成したいくつかの観察結果があります。おそらく、彼らはあなたにとっても役に立つでしょう。

• すべての制限と欠点にもかかわらず、vlookupは、特に動的な配列で非常にうまく機能します。
• インデックスマッチは、予想ほど速くありません。私には、vlookupのようなテーブル配列ではなく、個々の列を処理するため、これは非常に奇妙に思えます。
• Xlookupには多くの驚くべき機能がありますが、巨大なデータセットでのVlookUpやインデックスマッチよりも遅いです。うまくいけば、Microsoftが将来のバージョンでパフォーマンスを向上させることを願っています。
• オフセットマッチは、Excel 32ビットで最速です。しかし、その複雑な構文のために、間違いを犯す大きなチャンスがあります。また、ダイナミックアレイでは動作しません。少なくとも私はそれを強制することができませんでした。
• 絶対に必要な場合を除き、列全体を計算する意味はありません。これにより、式は2倍以上遅くなります。
• エクセルテーブルロック！ Excelを最大限に活用するには、可能な限り使用してください。
• 動的配列は未来です。

これらの観察結果は、Dynamic Excel 365の私のテストに基づいていることに留意してください。他のバージョンでテストする機会がありませんでした。もしそうなら、あなたのコメントは大歓迎であり、大歓迎です！

ダウンロード用のワークブックを練習します

Excelの最速Vlookup式（.xlsxファイル、 74MB ）

以上がExcelのvlookup-どの式が最速ですか？の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。