Excel

Excel ~動的配列数式 XLOOKUP~

更新日:

今回はXLOOKUP関数について紹介します。
XLOOKUPはOffice365の2020年1月30日から公開された新しい関数で、VLOOKUPの進化版の関数です。
まずはVLOOKUPから見ていきましょう。

例えば、このような表があります。企業ではよく使うマスタになる表ですね。
このマスタとは別で各店舗毎の売り上げ個数の表があったとします。

売上個数の表は残念ながら型番が載っていません。このような経験は数字を管理されている部署の方であれば良く経験されているかと思います。
さて、マスタの表から売上の一覧表へ型番を持ってきたいと思います。
このようなときに使うのがVLOOKUP関数でしたが、マスタの表では、商品名が右の方にあり、そのままではVLOOKUP関数が使えません。商品名のD列をA列にコピーしてからVLOOKUPを行うような作業を経験された方も多いと思います。

こんなイメージですね。キーとなる列を左側にもってきてからでないと利用できなかったVLOOKUP関数ですが、XLOOKUP関数の場合は【戻り範囲】として指定できるようになりました。

XLOOKUP関数は、1つの列で検索したキーワードの戻り列が右側、左側のどちらからでも検索結果を返すことができます。
つまり、下の表でC列の商品名をキーとして、A列の型番を返すことができます。


XLOOKUP関数:範囲または配列を検索し、最初に見つけた一致に対応する項目を返す


下の表はA~C列がマスタとなる表で、F~I列が各店舗の売上げとします。(分かりやすくするために同じシートに記載していますが、別シートでも問題ありません。)
J列に型番、K列に小売価格を持ってきたい場合、XLOOKUP関数で簡単に作ることができます。

 


XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])


となります。
少し複雑ですが、実際に上の表のJ2セルに入れた数式を見ながら紐解いていきましょう。

=XLOOKUP(F2,C:C,A:B,0,0)
<F2
セル(りんご)をC列から完全一致検索し、見つかったらA~B列を返す、見つからなかったら0を返す>

VLOOKUPとの違いが判りますか?
検索キーとした商品名のりんごはマスタの中ではC列にありますが、戻り値としてA~B列を指定できるのです。
また、戻り値をA~B列と複数行指定するとSPILLしてくれるので、K列に数式を入れる必要はありません。
今までVLOOKUPを使うために表を加工してから、作業していた方もXLOOKUPを使いこなすことができればかなり省力化できると思います。
XLOOKUP関数も過去に紹介しました、[FILTER関数][SORT関数][UNIQUE関数] と同じ動的配列数式です。

-Excel
-

Copyright© ゆるちゃり , 2024 All Rights Reserved Powered by STINGER.