私は仕事で、宅配便の運賃を出すExcelを作り、社内で使ってもらっています。見積もりのときに、商品と届け先の都道府県を選ぶと、請求する運賃が出る仕組みです。
この記事は、これまで紹介してきたXLOOKUPの使い方を組み合わせた応用編です。
- 複数の列をまとめて取り出す
- 近似一致で「○○以下」の区分を探す
- XLOOKUPを2回使ってクロス表から値を取り出す
なお、この記事の表や式は説明のために作ったもので、実際に使っているExcelとは作りが違います。運賃の数字も架空です。
例として使う表
運賃表
「運賃表」シートです。縦にサイズ、横に届け先のエリアが並んだクロス表です。サイズの数字は、箱の3辺合計の上限（○○cm以下）を表しています。実際の運賃表は発送する地域ごとに分かれていることが多いので、ここでは関東から発送する場合の表とします。
| サイズ | 関東 | 信越 | 東海 | 関西 | 九州 |
|---|---|---|---|---|---|
| 60 | 800 | 850 | 900 | 950 | 1,200 |
| 80 | 1,000 | 1,050 | 1,100 | 1,150 | 1,400 |
| 100 | 1,200 | 1,250 | 1,300 | 1,350 | 1,600 |
| 140 | 1,600 | 1,650 | 1,700 | 1,750 | 2,000 |
| 160 | 1,800 | 1,850 | 1,900 | 1,950 | 2,200 |
サイズはA2:A6、エリアはB1:F1、運賃はB2:F6に入っています。
エリア表
「エリア表」シートです。都道府県がどのエリアに入るかを並べます。実際には47都道府県すべてを入れますが、ここでは一部だけ載せます。
| 都道府県 | エリア |
|---|---|
| 東京都 | 関東 |
| 神奈川県 | 関東 |
| 新潟県 | 信越 |
| 愛知県 | 東海 |
| 大阪府 | 関西 |
| 福岡県 | 九州 |
商品マスター
「商品マスター」シートです。商品ごとに、梱包した箱の縦・横・高さ（cm）を入れています。商品そのものではなく、箱の外側の大きさを入れます。
| 商品 | 縦 | 横 | 高さ |
|---|---|---|---|
| 商品A | 30 | 20 | 8 |
| 商品B | 35 | 25 | 15 |
| 商品C | 40 | 30 | 20 |
| 商品D | 50 | 40 | 30 |
商品名はA2:A5、縦・横・高さはB2:D5に入っています。
商品と都道府県から運賃を出す
「見積」シートのA2に商品、B2に届け先の都道府県を入れます。都道府県はプルダウンから選ぶようにしておくと、入力ミスを防げます。
都道府県からエリアを出す
C2に、エリア表からエリアを探す式を入れます。
=XLOOKUP(B2,エリア表!$A$2:$A$7,エリア表!$B$2:$B$7)商品マスターから3辺を取り出して合計する
D2に、箱の3辺合計を出す式を入れます。
=SUM(XLOOKUP(A2,商品マスター!$A$2:$A$5,商品マスター!$B$2:$D$5))XLOOKUPの戻り範囲をB2:D5の3列にしているので、縦・横・高さの3つがまとめて返ってきます。それをSUMで合計すると、3辺合計になります。例えば商品Bなら、35・25・15が返ってきて、合計は75です。
複数の列をまとめて取り出す方法は、こちらの記事でも紹介しています。
3辺合計とエリアから運賃を出す
E2に、運賃表から運賃を探す式を入れます。
=XLOOKUP(D2,運賃表!$A$2:$A$6,XLOOKUP(C2,運賃表!$B$1:$F$1,運賃表!$B$2:$F$6),"規格外",1)- 内側のXLOOKUP：エリアの行（B1:F1）からC2のエリアを探し、そのエリアの列を丸ごと返す
- 外側のXLOOKUP：サイズの列（A2:A6）からD2の3辺合計を探し、内側が返した列から運賃を取り出す
外側のXLOOKUPは、5つ目の引数を「1」（一致する値がなければ、次に大きい値で探す）にしています。運賃表のサイズは「○○cm以下」なので、3辺合計が75cmなら、次に大きい「80」が見つかります。160cmを超えると表にないので、「規格外」と表示されます。
例えば、商品Bを大阪府へ送る場合は、エリアが「関西」、3辺合計が「75」で、80サイズの関西の運賃「1,150」が出ます。
重さについて
実際の運賃は、重さがサイズごとの上限を超えると、上のサイズになることがあります。例えば、60サイズの箱でも重さが5kgあれば、80サイズになるといった具合です。この記事では、説明を分かりやすくするために、3辺合計だけでサイズを決めています。実際に使うときは、使っている運送会社の基準に合わせて、重さも考えてください。
まとめ
- XLOOKUPの戻り範囲を複数列にすると、縦・横・高さをまとめて取り出せる。SUMで囲むと3辺合計になる
- 運賃表のサイズは「○○cm以下」なので、近似一致の「1」で探す
- 運賃表はクロス表なので、XLOOKUPを2回使う
それぞれの使い方は、こちらの記事で紹介しています。
