経理に必要なExcel関数①（XLOOKUP関数）

私は今まで、何度か転職を経験し、上場企業およびベンチャー企業等の上場準備会社で20年以上経理財務業務を行ってきました。
昨今の経理業務は、仕訳を手入力することはあまりなくなり、ほとんどが他システムからの連携もしくはデータを加工して会計システムに取り込むことが多いと思います。
そのため、経理業務ではExcelを活用することが多いため、高度なExcelスキルが求められます。そこで、経理業務でよく使うExcel関数であるXLOOKUP関数の実務での活用例をご紹介したいと思います。
似た関数にVLOOKUP関数がありますが、VLOOKUP関数よりも使い勝手がよく、便利ですので、XLOOKUP関数を使用できるようになると、VLOOKUP関数を使うことはなくなります。

XLOOKUP関数の基本的な使い方
XLOOKUP関数の実務での活用例
最後に

XLOOKUP関数の基本的な使い方

計算式と第3引数までの使い方

XLOOKUP関数は条件に一致する値を引っ張ってくる関数です。

=XLOOKUP(検索値、検索範囲、戻り範囲、見つからない場合、一致モード、検索モード）

色付きの引数が必須の引数（省略不可）、黒字の引数が任意の引数（省略可）になります。Excel関数では任意の引数は実務上省略されることが多いですが、XLOOKUP関数では、この任意の引数は結構便利に使えます。特に第4引数の「見つからない場合」は実務ではかなり使います。
まず、必須の第3引数の基本的使い方についてお伝えします。

下記表では、A商品～D商品の価格一覧の表があり、その表から、A商品の価格をC9セルに引っ張ってきています。C9セルの計算式は次のとおりです。

=XLOOKUP(B9,$B$3:$B$6,$C$3:$C$6)

・第1引数　検索値
検索値はB9セルを指定し、「A商品」を検索キーワードとしています。
・第2引数　検索範囲
検索範囲はB3セル～B6セルの範囲（A商品～Ｄ商品）を検索範囲としています。
・第3引数　戻り範囲
価格の情報を引っ張ってきたいので、戻り範囲はC3セル～C6セルとしています。

以上の計算式により、A商品～D商品（検索範囲）から、A商品（検索値）を検索し、一致したA商品の価格のデータ（戻り値）を返しています。

第4引数（一致モード）の使い方

それでは、先ほどと同じ表から、今度はE商品の価格を引っ張ってくるとしましょう。
C9セルの計算式は次のとおりです。

=XLOOKUP(B9,$B$3:$B$6,$C$3:$C$6,”-“)

第4引数を「”-“」としています。
E商品は上段の表のA商品～D商品の中にないので、この第4引数を入れないと「#N/A」となります。ただ、第4引数に「”-“」を入れることで、一致する値がない場合でもエラーとならず指定した「-」が返ってきています。
実務でもこの様に、エラー表示させないように、第4引数を入れることが多いです。

第6引数（検索モード）の使い方

第6引数では、検索順を設定します。
「1」は先頭から末尾へ検索
「-1」は末尾から先頭へ検索
になります。
省略した場合は先頭から末尾への検索になります。

今度は参照する表を、A商品～E商品として、A商品の情報が重複しているとしましょう。そこから、A商品の価格情報を引っ張ってきます。ただし、A商品の価格情報は2つあるため、どちらかの情報を引っ張ってくる必要があります。
C11セルの計算式は次のとおりです。

=XLOOKUP(B11,$B$3:$B$8,$C$3:$C$8,,,-1)

この計算式では、第4引数と第5引数を省略しています。そして、第6引数（検索モード）に「-1」を入れています。
仮に、この第6引数を入れない場合は、B3～B8セルの中から、A商品と一致する値を上から検索します。したがって、一番上のあるA商品の価格情報である「5,000」を引っ張ってきます。
今回の様に、第6引数として「-1」を入れると、上からではなく、下から検索するようになるので、下から2番目にあるA商品の価格情報である「4,500」を引っ張ってくるようになります。
この様に、参照元のデータが一部重複している可能性があるときに、この第6引数を使うことがあります。

以上が、XLOOKUP関数の基本的な使い方です。

ちなみに、第5引数は検索の精度を設定する引数です。
「0」は完全一致
「-1」は完全一致または次に小さい項目
「1」は完全一致または次に大きい項目
「2」はワイルドカード文字との一致
省略した場合は完全一致になりますので、この第5引数は省略することが多いです。

XLOOKUP関数の実務での活用例

では、次に実務での活用例をお伝えします。

まず、Excelのワークシートとして、①「商品マスタ」シート、②「月別仕入実績」シート、③「買掛金」シート、があるとします。経理部として、当月の買掛金の増加（発生）額を算出することが目的です。同じシート内のデータを引っ張ってくるというよりも、この様に、別のシートのデータを引っ張ってくるケースの方が実務上は圧倒的に多いです。

①「商品マスタ」シート
商品別の仕入単価、売単価、原価率のデータを一覧にしたマスターシートがあるとします。
このマスターシートはテーブル化し、テーブル名を「商品マスタ」としています。
この様に、マスターデータは、基本的にテーブルにしておいた方がよいです。表をテーブルにするには、表を選択したうえで、「Ctrl」キーと「T」キーを同時に押し、「Enter」キーを押すとテーブル化されます。

②「月別仕入数量」シート
商品別の仕入数を入力した表になります。
この表は見込の個数を入力する表になりますので、マスターデータではなく、テーブル化はしていません。

③「買掛金」シート
このシートでは、商品別に当月の買掛金の増加額・減少額を算出しています。ここで、A商品の2024年5月の買掛金増加額を計算したJ6セル（赤枠部分）でXLOOKUP関数を使用しています。

J6セルの計算式は次の通りです。

=ROUNDDOWN(XLOOKUP($B6,月別仕入数量!$A$3:$A$6,月別仕入数量!H$3:H$6,0)× XLOOKUP($B6,商品マスタ[商品名],商品マスタ[仕入単価（税抜/円）]1.1,0),0)

「月別仕入数量」シートから、XLOOKUP関数A商品の2024年5月仕入数500個を引っ張ってきています。
また「商品マスタ」シートから、XLOOKUP関数で、A商品の仕入単価3,600円を引っ張ってきています。
2024年5月の買掛金増加額は以下の通りです。
A商品2024年5月仕入個数500個×A商品仕入単価3,600円×1.1（消費税込みの金額にするために1.1をかける※）＝1,980千円
※消費税の端数処理を切り捨てにするために、ROUNDDOWN関数で0円未満切り捨ての処理をしています。

上記計算式から、「月別仕入数量」シートから、XLOOKUP関数で、A商品の2024年5月の仕入個数500個を引っ張ってきている部分を抜き出して説明しますね。

=XLOOKUP($B6,月別仕入数量!$A$3:$A$6,月別仕入数量!H$3:H$6,0)

・第1引数　検索値
検索値はB6セルを指定し、「A商品」を検索キーワードとしています。
・第2引数　検索範囲
検索範囲は「月別仕入数量」シートのA3セル～A6セルの範囲（A商品～Ｄ商品）を検索範囲としています。
・第3引数　戻り範囲
戻り範囲は「月別仕入数量」シートのH3セル～H6セルとしています。
2024年5月のA商品～D商品の個数を戻り範囲とし、そのデータの中から、A商品のデータを引っ張ってきています。
・第4引数　見つからない場合
見つからない場合は「0」としています。この第4引数を入れておかないと、一致するデータがない場合エラーになり、計算値が算出されなくなります。そのため、この第4引数はほとんどの場合、設定します。上述の通り「””」を入れ、一致するデータがない場合を空欄とし表示をすっきりさせることもよくありますが、計算する場合は「””」だとやはりエラーになってしまいますので「0」を入れます。　