EXCELでの質問

質問

ちょっと複雑な質問です。

今、会社で各契約に対する代理店に対しての手数料を計算できるようなテーブルを作成しています。
販売数や商品種類によって支払う手数料が変化するというものです。

手数料の決定要素は以下の3つです。
1.販売数
2.商品種類
3.地域

手数料は3年間支払われ、手数料率は変化します。
3つの決定要素別の手数料一覧が別シートにあり、以下の通りになっています。
行　販売数　種類　地域　1年　2年　3年
1　10　甲　東京　10%　5%　1%
2　10　甲　大阪　11%　6%　2%
3　10　乙　東京　12%　7%　1%
4　10　乙　大阪　13%　6%　2%
5　20　甲　東京　14%　5%　1%
6　20　甲　大阪　15%　4%　1%
7　20　乙　東京　16%　3%　2%
8　20　乙　大阪　17%　2%　1%

契約テーブルには、上記の3つの項目を入力する部分と3年間の手数料を表示する部分があるのですが、このテーブルに3つの項目を入力するとその行にある1～3年目の手数料が自動的に手数料一覧より選択され、表示されるというシートを作成したいのです。
（ex）
販売数：20、種類：乙、地域：東京を選択すると、1年目に16%、2年目に3%、3年目に2%が自動的に表示される。

関数などを1～3年目の手数料部分に挿入することになると思うのですが、どのような関数を使えばよいでしょうか？

わかりづらくてすいませんが、こういったものを作成したことがある方、是非教えてください。

回答No.1

papayuka · ベストアンサー

一覧表の先頭列に　=A2 & ":" & B2 & ":" &C2　のような感じで検索項目を作り値に置き換えます。

行　検索項目　　　販売数　種類　地域　1年　2年　3年
1　10：甲：東京　　10　　　甲　　東京　10%　5%　1%
　　　　　　　　・
　　　　　　　　・

契約シートの３項目を入力するセルに対しても何処かのセル（検索セル）に上記と同様の関数をいれておき、その検索セルを使って VLOOKUPを使えばOKでしょう。

=ISERROR(VLOOKUP(検索セル,リスト範囲,5,0),"",VLOOKUP(検索セル,リスト範囲,5,0))
=ISERROR(VLOOKUP(検索セル,リスト範囲,6,0),"",VLOOKUP(検索セル,リスト範囲,6,0))
=ISERROR(VLOOKUP(検索セル,リスト範囲,7,0),"",VLOOKUP(検索セル,リスト範囲,7,0))

お礼

なるほど。
その条件に該当する1つの値を作成すればいいわけですね。

ありがとうございました。

回答No.2

SAKURAMYLOVE

セルの結合とvlookupの組み合わせではいかがでしょうか。
sheet1に質問のデータが、sheet2に結果を抽出するものとします。
【手順】
(1)A列：販売数、B列：種類、C列地域、D列：1年・・として、sheet1の地域と一年目の間に列を挿入(よってE列：1年、F列：２年・・となる）し、作業用セルを用意します。
(2)セルD2に=A2&B2&C2とし、下の行にコピー貼り付けをする。
(3)SHEET2にA1=販売数、B1：種類、C1：地域、D1：１年、E1：２年、F1：３年と入力する。
(4)D2～F2に以下の関数を入力する。
D2=VLOOKUP($A2&$B2&$C2,Sheet1!$D$2:$G$9,2,0)
E2=VLOOKUP($A2&$B2&$C2,Sheet1!$D$2:$G$9,3,0)
F2=VLOOKUP($A2&$B2&$C2,Sheet1!$D$2:$G$9,4,0)

これで、SHEET2の販売数、種類、地域の下に必要な項目を入力すると1年、２年、３年の下にそれぞれの手数料が、表示されます。

お礼

なるほど。
その条件に該当する1つの値を作成すればいいわけですね。

ありがとうございました。