私は仕事で、金額や重さによって段階的に値を決めるときに、XLOOKUPの近似一致を使っています。以前は、手形の印紙代を求めるのに使っていました。今は、宅配便の運賃表から、重さと都道府県で請求する運賃を出すのに、職場の人に使ってもらっています。
「○○以上ならA、△△以上ならB」のような判定は、IFやIFSでも書けます。ただ、基準を表にしてXLOOKUPで探すと、式が短くなり、基準を変えるときも表を書き換えるだけで済みます。
この記事では、テストの点数を例に、IFS・XLOOKUP・VLOOKUPの書き方を比べます。あわせて、領収書の印紙税のように「○○円以下」で区切られた表の探し方も紹介します。
例：テストの点数で評価を付ける
B列の点数に、次の基準で評価を付けます。
- 90点以上：S
- 80点以上：A
- 65点以上：B
- 65点未満：C
IFSで書く場合
=IFS(B2>=90,"S",B2>=80,"A",B2>=65,"B",TRUE,"C")この書き方では、基準の点数が式の中に入っています。基準を変えるときは、式を書き直して、下の行までコピーし直す必要があります。段階が増えると、式も長くなります。
基準を表にする
まず、基準を表にします。点数の列には、「この点数以上」の値を入れます。
| 点数（以上） | 評価 |
|---|---|
| 0 | C |
| 65 | B |
| 80 | A |
| 90 | S |
この表がE2:F5にあるとして、式を書いていきます。
XLOOKUPで書く場合
=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,"",-1)5つ目の引数「-1」は、「一致する値がなければ、次に小さい値で探す」という指定です。
例えば72点なら、72以下で一番大きい「65」が見つかり、評価は「B」になります。65点ちょうどは「B」、64点は「C」、90点は「S」です。
基準を変えたいときは、表の数字を書き換えるだけで済みます。式はそのままです。
VLOOKUPで書く場合
同じことは、VLOOKUPの近似一致（TRUE）でもできます。
=VLOOKUP(B2,$E$2:$F$5,2,TRUE)ただし、VLOOKUPの近似一致は、表を点数の小さい順（昇順）に並べておく必要があります。並んでいないと、エラーにならずに間違った評価を返すことがあります。
XLOOKUPの「-1」は、表が並んでいなくても正しく探せます。
点数が空欄のときは「C」になる
点数が空欄のセルは0として扱われるので、XLOOKUPでもIFSでも「C」になります。未受験の人がいる表では、受けていない人に「C」が付いてしまいます。
この場合は、IFで先に空欄かどうかを判定します。
=IF(B2="","",XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,"",-1))B2が空欄なら何も表示せず、点数が入っているときだけ評価を探します。
「○○以下」で区切る表は「1」を使う
領収書の印紙税のように、「100万円以下は200円」と上限で区切られている表もあります。売上代金の受取書の税額は、次のとおりです（1,000万円以下の部分を抜粋）。
| 記載金額 | 税額 |
|---|---|
| 5万円未満 | 非課税 |
| 5万円以上100万円以下 | 200円 |
| 100万円を超え200万円以下 | 400円 |
| 200万円を超え300万円以下 | 600円 |
| 300万円を超え500万円以下 | 1,000円 |
| 500万円を超え1,000万円以下 | 2,000円 |
出典：国税庁「No.7105 金銭又は有価証券の受取書、領収書」。営業に関しない受取書は非課税になるなど、細かい条件は国税庁のページで確認してください。
この表をExcelで使うときは、金額の列に「この金額以下」の上限を入れます。
| 金額（以下） | 印紙税 |
|---|---|
| 49,999 | 非課税 |
| 1,000,000 | 200 |
| 2,000,000 | 400 |
| 3,000,000 | 600 |
| 5,000,000 | 1,000 |
| 10,000,000 | 2,000 |
「5万円未満」を「49,999」にしているのは、金額が円単位の整数なので「49,999円以下」と同じだからです。
この表がH2:I7にあるとして、B2の金額から印紙税を探します。
=XLOOKUP(B2,$H$2:$H$7,$I$2:$I$7,"表の範囲外",1)5つ目の引数「1」は、「一致する値がなければ、次に大きい値で探す」という指定です。
例えば50,000円なら、次に大きい「1,000,000」が見つかり、印紙税は200円です。100万円ちょうどは200円、100万1円は400円です。1,000万円を超える金額は表にないので、「表の範囲外」と表示されます。
VLOOKUPの近似一致は「次に小さい値」しか探せないので、上限で区切った表はそのままでは使えません。下限（0、50,000、1,000,001…）で表を作り直せば使えますが、「100万円を超え」を「1,000,001」に置き換える必要があり、国税庁の表と見比べにくくなります。XLOOKUPなら、元の表の区切り方に合わせて「-1」と「1」を使い分けられます。
まとめ
- 段階の判定は、基準を表にしてXLOOKUPで探すと、式が短くなり、基準の変更も表だけで済む
- 「○○以上」で区切る表は「-1」、「○○以下」で区切る表は「1」を使う
- VLOOKUPの近似一致は、表を昇順に並べておく必要がある
- 空欄は0として扱われるので、必要ならIFで先に判定する
重さと都道府県から運賃を出す宅配便の運賃表は、この近似一致と、XLOOKUPを2回使うクロス表の検索を組み合わせて作れます。運賃表は、別の記事で紹介する予定です。
クロス表の検索は、こちらの記事で紹介しています。
XLOOKUPに乗り換えて便利だったことは、こちらの記事で紹介しています。
