- ベストアンサー
表を参照して計算結果を求める
会社で営業インセンティブ（受注利益率によっての報酬金）の取り決めを行いました。毎月指定日に受注を締めて、各々営業担当のインセンティブを計算するのですが、現在はいちいち手計算で求めています。 しかし、エクセルで簡単に求めることが出来ないものか？と思いましてのご相談です。 （営業インセンティブの条件） 粗利益額/利益率 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％ ＝１７万１千円 と、いうようになるわけなのですが、これをエクセルで簡単に求める方法を伝授して欲しいのです。 よろしくお願い致します。
- black_cools
- お礼率26% (13/50)
- オフィス系ソフト
- 回答数2
- ありがとう数0
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
まずテーブルを作ります（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 以上で終わりです。
その他の回答 (1)
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)
