表を参照して計算結果を求める

質問

会社で営業インセンティブ（受注利益率によっての報酬金）の取り決めを行いました。毎月指定日に受注を締めて、各々営業担当のインセンティブを計算するのですが、現在はいちいち手計算で求めています。
しかし、エクセルで簡単に求めることが出来ないものか？と思いましてのご相談です。

（営業インセンティブの条件）

粗利益額/利益率　　　　22％以上　　22%未満　　20%未満
　　　　　　　　　　　　　　　　　 20%以上　　18%以上

850万以上　　　　　　　　　8％　　　7.5％　　　6.5％
650万以上850万未満　　　 7.5％　　　7.0％　　　6.0％
550万以上750万未満　　　 7.0％　　　6.5％　　　5.5％
450万以上550万未満　　　 6.5％　　　6.0％　　　5.0％
350万以上450万未満　　　 6.0％　　　5.5％　　　4.5％
250万以上350万未満　　　 5.5％　　　5.0％　　　4.0％
250万未満　　　　　　　 4.5％　　　4.0％　　　3.0％

例えば、当月の契約高が２，０００万で利益が３８０万円、利益率が19％だったとします。その際の営業インセンティブは上表から下記のように求めるのです。
・利益率が19％なので、上表から「20％未満、18％以上」の列を参照し、利益が３８０万円なので「350万以上450万円未満」の行を見て、その交差する「4.5％」がインセンティブ％となり、下記の計算で報酬額を求めます。

　　３８０万円　×　4.5％　＝１７万１千円

　と、いうようになるわけなのですが、これをエクセルで簡単に求める方法を伝授して欲しいのです。

　よろしくお願い致します。

回答No.2

deecyan · ベストアンサー

まずテーブルを作ります（Sheet2)と仮にします

（A)　　（B)　　（C)　（D)　　（E)　（F)
受注額　率１　　率2　　率3　　率4　　利益率
0　　　　　4.5　　　4　　　3　　　0　　　0
250　　　　5.5　　　5　　　4　　　0　　　18
350　　　　6　　　　5.5　　4.5　　0　　　20
450　　　　6.5　　　6　　　5　　　0　　　22
550　　　　7　　　　6.5　　5.5　　0　
650　　　　7.5　　　7　　　6　　　0　
850　　　　8　　　　7.5　　6.5　　0　

A列には受注金額をいれます（A2からA8まで）
B列には　22％以上のレートをいれていき
E列は18%未満のレートを設定します　すべて0を入れておきます

F列は利益率のテーブルです

これでテーブルは終わりです
後はSheet1にデータを入れます

A列は名前で　A2から入れていきます
B列は受注金額をいれます　B2から入れていきます
C列は利益をいれます　　　C2から入れていきます
D列は利益率として　　D2に式　=B2/C2*100　を入れて　D3以降にコピーします
E列はインセンティブとして　E2に式　
=B2*(INDEX(Sheet2!$B$2:$E$8,MATCH(B2,Sheet2!$A$2:$A$8,1),5-MATCH(D2,Sheet2!$F$2:$F$5,1))/100)
を入れて　E3以降コピーします

例
名前　売り上げ　　利益　　粗利　　インセンティブ
Aさん　300　　　　　30　　　10　　　0
Bさん　300　　　　　60　　　20　　　15

以上で終わりです。

回答No.1

noname#204879

A　　　　　　 B　　　 C　　　　 D　　　　　 E　　 F　　 G
1　契約高　　　　　20,000,000　　 粗利益額＼利益率　 18%　 20%　 22%
2　粗利益額　　　　 3,800,000　　　　　　　　　　0　3.0%　4.0%　4.5%
3　利益率　　　　　　　　 19%　　　　　　2,500,000　4.0%　5.0%　5.5%
4　インセンティブ　　 171,000　　　　　　3,500,000　4.5%　5.5%　6.0%
5　　　　　　　　　　　　　　　　　　　　4,500,000　5.0%　6.0%　6.5%
6　　　　　　　　　　　　　　　　　　　　5,500,000　5.5%　6.5%　7.0%
7　　　　　　　　　　　　　　　　　　　　6,500,000　6.0%　7.0%　7.5%
8　　　　　　　　　　　　　　　　　　　　8,500,000　6.5%　7.5%　8.0%

B3: =B2/B1
B4: =B2*VLOOKUP(B2,D2:G8,MATCH(B3,E1:G1)+1)