重量とサイズの組み合わせから送料を取得する(INDEX,MATCH)

荷物の送料のように荷物の重量とサイズの 2 つの変数の組み合わせによって料金が設定されている場合に、指定した重量とサイズの送料を自動で取得できると便利です。ここでは Excel の関数である INDEX 関数、 MATCH 関数を組み合わせて、重量と合計サイズの組み合わせから送料を取得する方法について解説します。

(Last modified: 2023年07月10日)

重量とサイズから送料を算出する

重量とサイズという 2 つの変数の組み合わせで送料が決まっているテーブルから送料を取得する方法です。送料のテーブルとて次のようなものを例として用意しました。

	0Kg～20Kg	20Kg～40Kg	40Kg～60Kg	60Kg～
0cm～50cm	1,000円	1,500円	2,000円	2,500円
50cm～100cm	1,800円	2,300円	2,800円	3,300円
100cm～	2,600円	3,100円	3,600円	4,100円

INDEX 関数および MATCH 関数を使って重量とサイズから送料を検索するために、上記の表を次のように Excel のシートに入力しました。

重量の区分は「0Kg～20Kg」「20Kg～40Kg」「40Kg～60Kg」「60Kg～」の 4 つの区分があり、シートにはそれぞれの区分の中の最小の数値を入力しておきます。またサイズの区分は「0cm～50cm」「50cm～100cm」「100cm～」の 3 つの区分があり、シートにはそれぞれの区分の中の最小の数値を入力しておきます。

入力した重量とサイズから該当する送料を取得するために、送料を表示する C10 セルに次のような数式を入力しました。

Enter キーを押すと、デフォルトで入力している重量とサイズから送料を取得して C10 セルに表示されました。

今度は重量として 50 、サイズとして 60 を指定すると送料の 2800 が C10 セルに表示されました。

このように入力した重量とサイズから送料を取得することができました。

解説

今回は MAX 関数を重量とサイズにそれぞれ使用してどの区分になるのかを取得しています。例えば重量で 50 Kg を指定した場合、 MATCH 関数の検索値として 50 、検索範囲として C2:F2 、照合の種類として 1 を指定して MATCH(C8,C2:F2,1) が実行され、結果として 50 以下の最大の値である 40 と一致したものとして 3 を取得します。

同じようにサイズで 60 cm を指定した場合、 MATCH 関数の検索値として 60 、検索範囲として B3:B5 、照合の種類として 1 を指定して MATCH(C9,B3:B5,1) が実行され、結果として 60 以下の最大の値である 50 と一致したものとして 2 を取得します。

最後に INDEX 関数を使って送料を取得します。 INDEX 関数の参照として C3:F5 、行番号として 2 、列番号として 3 を指定すると送料として 1500 を取得することができます。

今回使用した関数の解説は下記を参照されてください。

-- --

Excel の関数である INDEX 関数、 MATCH 関数を組み合わせて、重量と合計サイズの組み合わせから送料を取得する方法について解説しました。