背景
それはすべて、私がいわゆる Double-TRUE VLOOKUPトリック（4回目のパラメーターでVLOOKUPとTRUEを2回使用するトリック）。 アルゴリズムの詳細な説明については、Charles Williamsの記事「なぜ2つのVLOOKUPSが1つのVLOOKUPよりも優れているのか」（記事の最後）を参照してください。
作業の原理を実現し、このアプローチが従来の線形検索（4番目のパラメーターがFALSEのVLOOKUP）よりも数千倍高速になる可能性があることを発見し、その機能を明らかにするためにオプションを検討し始めました。 実装中に、コンテキスト広告に適したいくつかのツールが判明しましたが、そのうちの1つは引き続き改善を続けており、既にHabréのプロジェクトにいくつかの記事を捧げています。 SEOスペシャリストとコンテキスト広告スペシャリストにお勧めします（すぐに予約します。記事のリンクはすでに古いバージョンです。最新バージョンは条件付きで6.0です。最新バージョンを含むすべてのバージョンをダウンロードするリンクはこの記事の最後にあります）。
「 大きなセマンティックカーネルの分析、または「ロボットの認識」
「 Excelの補題、または」認識ロボット3.0
そのため、これらのファイルの驚異的な速度（Excelにとっては信じられないほど）にもかかわらず、マクロのコンポーネントの1つと同じ信じられないほど長いメガ式を使用する必要がありました（上記の記事の最後に例を示します-3215文字の式）。 そして、すべての欠点は、関数の複雑な構文です。
あなたがそれを使用して手に入れれば、それは難しいように見えなくなりますが、このアプローチが意図されている経験の浅いユーザーはほとんどそれを理解したくないでしょう。
構文は次のようになります。
If（VLOOKUP（検索;配列; 1; TRUE）<検索済み; ""; VLOOKUP（検索;配列; n; TRUE））
nは、目的のキーの反対の値を返す列のシリアル番号です。
4番目のパラメーターの「TRUE」の代わりに、式の長さの名目上の短縮に「1」を使用できます。これにより、その本質は変わりません。
数式の進行状況を発表すると、次のようになります。
「配列の最初の列でのバイナリキー検索がキー自体よりも小さい値を返す場合、空の文字列を返します。 それ以外の場合は、オフセットnを使用してバイナリキー検索の結果を返します。
アプローチは、配列内でキーが見つからない場合に値を返さないために使用されます。 多くの場合、必要なものが見つからない場合、低い値は必要ありません 。 つまり、すべてまたは何もありません。 要するに、これは「トリック」の本質です。
3桁または4桁の数字で計算される速度の増加が問題であることを思い出させてください。 純粋に数学的にアプローチする場合-2 ^ 20行の配列では、通常のバイナリ検索で〜10回の計算が行われ、上記の式は約20になりますが、線形検索では〜500,000になります。 上記の式の成長は25,000倍です。 むき出しの数字が印象的でない場合、より雄弁な同等の比較は1秒対7時間です。
実際には、成長はそれほど重要ではありません（記事の最後に、異なる方法が比較された記事へのリンクがあります）。 これは主に、プログラムが実行する追加手順（たとえば、セルへの値の書き込み）に費やされたプロセッサー時間によるものです。 ただし、ゲインは非常に重要です（〜4000倍）。
しかし同時に、複雑で完全に使用できない構文があります。 すべてのモータルにCMDが与えられたわけではありません。これは、2 CPRとIFの組み合わせについて言えます。
VBAを使用して複雑な構文の問題を解決しました。UDF（ユーザー定義関数、ユーザー関数）を作成しました。
UDFコード：
Public Function (a, b As Range, c As Integer) As String If Application.VLookup(a, b.Columns(1), 1, True) = a Then = Application.VLookup(a, b, c, True) Else = "" End If End Function
Excelファイルで関数を使用するには、上記のコードを追加するモジュールを現在のワークブックに追加するか、既に実行されているサンプルファイルをダウンロードする必要があります。
この関数は3つのパラメーターを入力として受け入れます。構文は、4番目のパラメーターを除いて、通常のVLRに似ています。 必要ありません：（ 必須、配列、列番号） 。
そのため、出力では、通常の構文と使い慣れた動作を備えた関数を取得しましたが、速度は、配列の長さに応じて、通常のVLOOKUPよりも数十倍、数百倍、数千倍も高速です。 1つの制限がありますが、この関数は最小から最大にソートされた配列でのみ正しく機能します。 多くの場合、最後の瞬間は乗り越えられない障害ではありません。
使用、コメント。 アルゴリズムの改善と同様の実装アイデアに満足しています。
私はPythonで検索の最適化に取り組んでいますが、現時点では標準の辞書検索よりも速く見つかりませんでした。これについても喜んでコメントします。
参照資料
「 ダブルVLOOKUPのトリックに関する記事」なぜ2 VLOOKUPが1よりも優れているのか
「 「ダブルフォワードトリック」を含むさまざまな検索方法の比較」
» 最新バージョンの認識ロボットと 、この記事の主題を含む、 以前およびその他のすべてのコンテキスト広告ツールを1つのリンクで示します。