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

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

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

ソースデータ

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

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

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

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

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

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

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

vlookupフォーミュラ

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

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

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

• 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に移動し、右下隅のプラス記號をダブルクリックして、列全體に式をコピーします。ダブルクリックの時點で、ストップウォッチを?qū)g行し、この計算には6.6秒かかることがわかります。

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

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

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

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

vlookupの制限

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

• 左を見ることができません。 vlookup関數(shù)は、テーブル配列の左端列のみを見て、右から情報を返すことができます。
• カラムの挿入や削除に耐えられません。 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よりも優(yōu)れています。

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

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

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

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

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

オフセットマッチ式

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

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

どこ：

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

私たちの場合、最初の列で検索しているため、ルックアップ列のオフセット（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ビットでは、結(jié)果はそれほど印象的ではありません - 約7.5秒です。なんで？ MicrosoftGuysへの良い質(zhì)問:)

オフセットマッチの利點

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

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

オフセットマッチの欠點

複雑な構(gòu)文。

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

詳細なチュートリアル：

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

Xlookup式

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

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

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

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

Xlookupの利點

従來のvlookupと比較して、Xlookup関數(shù)には次のような多くの改善があります。

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

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テーブルのデータは、セルアドレスの代わりにテーブル名と列名を使用することにより、特別な方法で參照できます。これは構(gòu)造化された參照と呼ばれ、計算速度に影響があるのではないかと思います。

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

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

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

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

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

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

私たちのテーブルでは、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テーブルの構(gòu)造的な參照

動的配列を使用したvlookup

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

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

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

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

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

その他の結(jié)果を以下に示します。

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

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

Xlookup -7.3秒

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

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

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

詳細なチュートリアル：Excelの動的配列、関數(shù)、および式

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

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

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

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

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

詳細なチュートリアル：

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

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

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

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

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

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

パフォーマンス：3.2秒

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

要約と結(jié)論

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

関數(shù)	秒?yún)g位の計算速度
	範囲	列全體	テーブル	動的配列
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

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

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

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

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

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

以上がExcelのvlookup-どの式が最速ですか？の詳細內(nèi)容です。詳細については、PHP 中國語 Web サイトの他の関連記事を參照してください。