カスタムエクセルXLOOKUP機能

投稿日: 2020年10月22日 2020年11月12日
タグ: lookup-formulas

XLOOKUP関数は、Office365のインサイダープログラム専用です。

LOOKUP関数には、VLOOKUPおよびHLOOKUP関数の多くの弱点を克服する多くの機能がありますが、残念ながら、現時点では使用できません。ただし、心配しないでください。次のXLOOKUP関数MSExcelとまったく同じように機能するXLOOKUP関数を作成できます。機能を1つずつ追加していきます。

XLOOKUP関数のVBAコード以下のUDFルックアップ関数は、多くの問題を解決します。それをコピーするか、以下のファイルの下にあるxlアドインをダウンロードしてください。

Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range)

XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0))

End Function

説明：

上記のコードは、VBAで使用される基本的なINDEX-MATCHです。これにより、新しいユーザーが直面する多くのことが簡単になります。 INDEX-MATCH関数の複雑さを解決し、3つの引数のみを使用する場合。これをExcelファイルにコピーするか、以下の.xlamファイルをダウンロードしてアドインExcelとしてインストールできます。アドインの作成方法と使用方法がわからない場合は、 `link：/ excel-macros-and-vba-add-in-in-vb [ここをクリックしてください]。

XLOOKUP Add-In]

Excelワークシートでどのように機能するか見てみましょう。

XLOOKUPの構文

=XLOOKUP(lookup_value, lookup_array, result_array)

lookup_value：これは

で検索する値です。 === lookup_array。

lookup_array：lookup_valueを検索する1次元の範囲です。 result_array：これも1次元の範囲です。これは、値を取得する範囲です。

このXLOOKUP関数の動作を見てみましょう。

XLOOKUPの例：

ここに、Excelのデータテーブルがあります。このデータテーブルを使用して、いくつかの機能を調べてみましょう。

機能1.ルックアップ値の左側と右側にあるExactLookup。 ExcelのVLOOKUP関数は、ルックアップ値の左側から値を取得できないことがわかっています。そのためには、複雑なINDEX-MATCHの組み合わせを使用する必要があります。もうそうじゃない。

いくつかのロール番号の表で利用可能なすべての情報を取得する必要があると仮定します。その場合、ロール番号列の左側にある領域も取得する必要があります。

この式、I2を書いてください：

=XLOOKUP(H2,$B$2:$B$14,$A$2:$A$14)

ロール番号112の結果が北になります。下のセルの数式をコピーまたは下にドラッグして、それぞれの領域で埋めます。

どのように機能しますか？

メカニズムは単純です。この関数は、lookup_arrayでlookup_valueを検索し、最初の完全一致のインデックスを返します。次に、そのインデックスを使用して、result_arrayから値を取得します。この関数は、名前付き範囲で完全に機能します。

同様に、この式を使用して、各列から値を取得します。

機能2.ルックアップ値の上下のExactHorizontalLookup。 XLOOKUPは、正確なHLOOKUP関数としても機能します。 HLOOKUP関数には、VLOOKUPと同じ制限があります。ルックアップ値の上から値をフェッチすることはできません。しかし、XLOOKUPはHLOOKUPとして機能するだけでなく、その弱点も克服します。方法を見てみましょう。

仮に、2つのレコードを比較したい場合。すでに持っているルックアップレコード。比較するレコードはlookup_rangeの上にあります。その場合は、この式を使用してください。

=XLOOKUP(H7,$A$9:$E$9,$A$2:$E$2)

数式を下にドラッグすると、行を比較した記録全体が得られます。

機能3.列番号とデフォルトの完全一致は必要ありません。

VLOOKUP関数を使用するときは、値をフェッチする列番号を指定する必要があります。そのためには、列を数えるか、いくつかのトリックを使用する必要があり、他の関数の助けを借ります。このUDFXLOOKUPを使用すると、それを行う必要はありません。

1つの列から値をフェッチするため、または列に値が存在するかどうかを確認するためにVLOOKUPを使用している場合、これが私にとって最善の解決策です。

機能4.INDEX-MATCH、VLOOKUP、HLOOKUP関数を置き換えます

単純なタスクの場合、XLOOKUP関数が上記の関数に置き換わります。

XLOOKUPの制限：

もう1つの制限は、テーブルから複数のランダムな列または行を検索する必要がある場合、この式を何度も作成する必要があるため、この関数は役に立たないことです。これは、 `link：/ excel-range-name-all-about-excel-named-ranges-excel-range-name [namedranges]`を使用することで解決できます。

今のところ、おおよその機能は追加されていないので、もちろん、おおよその一致を取得することはできません。すぐに追加します。

XLOOKUP関数がルックアップ値を見つけられなかった場合、＃N / Aではなく#VALUEエラーが返されます。

そうですね、これはXLOOKUPを使用してExcelテーブルの値を取得、検索、検証する方法です。このユーザー定義関数を使用して、ルックアップ値の左側または上部で手間のかからないルックアップを行うことができます。この機能、EXCEL 2010/2013/2016/2019/365、またはVBA関連のクエリに関連する疑問や特定の要件がまだある場合は、以下のコメントセクションで質問してください。きっと返事が来るでしょう。

link：/ custom-functions-in-vba-create-vba-function-to-return-array [Create VBA Function to Return Array] |ユーザー定義関数から配列を返すには、UDFに名前を付けるときに配列を宣言する必要があります。

link：/ excel-array-formulas-arrays-in-excel-formula [ExcelFormulの配列] |どの配列がExcelにあるかを学びます。

link：/ vba-user-defined-function [VBAを使用してユーザー定義関数を作成する方法] | Excelでユーザー定義関数を作成する方法を学ぶ `link：/ custom-functions-userdefined-functions-from-other-workbooks-using-vba-in-microsoft-excel [Using a User Defined Function（UDF）from another workbook MicrosoftExcelのVBA] `| Excelの別のワークブックでユーザー定義関数を使用する `link：/ custom-functions-return-error-values-from-user-defined-functions-using-vba-in-microsoft-excel [ユーザー定義からエラー値を返すMicrosoftExcelでVBAを使用する関数] `|ユーザー定義関数からエラー値を返す方法を学ぶ