ExcelのXLOOKUP関数で、範囲（～）から検索する方法。シリアルナンバー・所得税

Excelの  XLOOKUP関数を使えば、「100円超200円以下」のような範囲から、目的のものを探すことができます。

※XLOOKUP関数　by　Leica M10

XLOOKUP 関数でできること

XLOOKUP関数は、新しい機能であり、Excelのバージョンによっては使えないものもあります。
お使いのExcelで、= xlと入力して 、「XLOOKUP」が出てくれば使えるということです。

 

Excelの更新のしかたや、XLOOKUP関数の基礎は、こちらの記事を参考にしていただければ。

VLOOKUP関数を使ってはいけない。よりシンプルなXLOOKUP関数を使う。 | EX-IT

XLOOKUP関数とスピル。#N/A、#SPILL! エラーの解決方法。 | EX-IT

XLOOKUP 関数では、たとえばこのようにシリアルナンバーが一致したら、それに対応する製造年を表示するということができます。

セルB2（356523）を、G列から探して、一致したら、H列の数値（1937）を表示するというしくみです。

 

ただ、シリアルナンバー、1つ1つのリストをつくるのも大変ですし、通常は、「シリアルナンバーの345001から416500なら、1937年」というような、範囲でリストがつくられるはずです。
その場合、前述したXLOOKUP関数だと、エラーが出ます。
E列には、356523がないからです。

こういった場合でも対応する方法はあります。

XLOOKUP 関数でレンズのシリアルナンバーから製造年を探す

この事例は私が実際に使ったものです。

Leicaのレンズは1つ1つにシリアルナンバーが付いていて、そのシリアルナンバーから製造年がはがわかります。

リストでは、たとえばシリアルナンバーが156001から195000までだったら1933年製造です。

 

エルマー35 mmF 3.5というレンズであればシリアルナンバーは356523。

レンズにはこのようにシリアルナンバーがついています。
（写真は1937年製造のレンズ、ズマール50㎜F2。356523のレンズは、黒字で見づらいので、この写真に）

 

シリアルナンバー356523をリストから探してもいいのですが、手間がかかりますし、めんどくさく、さらには複数のものを探すのは時間がかかるものです。

こういった範囲から探す場合も XLOOKUP 関数は使えます。

= XL と入れてエックスルックアップが出てきたら

Tabキーを押し確定します。

まずは検索する値を指定しましょう。

セルB2のシリアルナンバーを指定し、「,（カンマ）」を入れて、次に入れるのは検索範囲です。

リストのうち、〇～△の範囲の〇が入っているE 列を指定しましょう。

「，（カンマ）」を入れ、次は戻り範囲、表示する値が入っている列を指定します。

製造年を表示したいので、G列を指定しましょう。

仮にここで確定すると356523と完全に一致するものを探すことになります。
［見つからない場合］［一致モード］［検索モード］は省略するということです。

範囲で指定するときには、さらに XLOOKUP関数で指定しなければいけません。

［見つからない場合］は、省略してもいいので、「，」を入れ、［一致モード］を入れます。

ここでは、省略すると［0　完全一致］を指定することになるのです。
範囲から探す場合は、［-1　完全一致または次に小さい項目］を選びましょう。

 

あとは省略できるので、Enterキーを押すと、範囲を正しく判定し、1937と表示されます。

試しに、［一致モード］を［1　完全一致または次に大きい項目］にすると、1938が表示されてしまうのです。
このように、わからなくなったら別のことを試して確認してみましょう。

1937年に製造されたレンズが今手元にあるというのは感慨深いものです。

他のレンズを調べるとこんな感じでした。
（2015年以降のリストは、今のところ見当たらず……）

自分（1972年生まれ）より年上のレンズも多く、古いレンズはオールドレンズと呼ばれ、いい味わいを出してくれます。
最新レンズよりも安く手に入るので（それでも安くはありませんが）、ちょっとずつ集めているところです。
（Leicaのオールドレンズは買っていいことにしています。他のレンズは買うの禁止です）。

シリアルナンバーは、こちらのサイトを参考に表をつくってみました。
Serial Number Links for Leica Cameras and Lenses – Leica Wiki (English)

XLOOKUP関数のサンプル、ご興味ある方は使っていただければ。

EX-ITサンプル　Leica serial number

 

こういった範囲から探す場合に、 XLOOKUP関数を使ってみましょう。
もう1つ事例を挙げてみます。
所得税の例です。

XLOOKUP 関数で所得税を計算する

所得税の計算は次の表により、所得をもとに計算します。

 

所得ですので収入ではなく給料なら給与所得控除という金額（給料500万円なら154万円差し引く）、事業であれば収入から経費を引き青色申告特別控除（最大65万円）を引いたものです。

所得が300万円だとしたら、
195万円超 330万円以下の範囲ですので、300万円×10%-97500円=202,5000円となります。

この場合も XLOOKUP 関数で計算できるのでやってみましょう。
所得税率と控除額を別々に計算し、それらから所得税を計算するようにしました。
（C列はB列に入っている数式を表示しています）

 

 

所得税率は、セルB1の所得を、E列から探して、範囲内に見つかったらG列を表示、控除額は、セルB1の所得を同じくE列から探して、範囲内に見つかったらH列を表示しています。

この場合も［完全一致または小さい値］で［-1］を指定しましょう。

これで、セル B1を変えるだけで所得税が計算できるわけです。

VLOOKUP 関数よりも、感覚的にわかりやすい XLOOKUP 関数。
従来の VLOOKUP 関数でも同じことができますが、わかりやすいXLOOKUP関数を使うためにExcelをバージョンアップする価値があります。

ぜひ使ってみていただければ。

所得税のサンプルはこちらです。
EX-ITサンプル　XLOOKUP関数 所得税計算.xlsx

 

 

---

■編集後記

昨日は、クロスバイクをひさしぶりにひっぱりだしました。
（トライアスロンバイクではなく、普段用）

ほこりかぶっていたのを掃除して空気を入れて。

事故後はシェアサイクル（電動）だけに乗っていたので、ちょっとしんどい一方、自分でこいでいる感のほうが楽しいです。
自転車を降りるときに、傷口あたりの筋肉が痛みますが。

 

■昨日の1日1新
※詳細は→「1日1新」

HGUC　ジオング
豊洲　ユニクロ、無印

 

■昨日の娘日記

ママのパンづくりを楽しそうに手伝っていました。
が、「パン、まだー？」と。
時間がかかるのが待てないようで。
かわりにベーグルを食べていました。