Excelの手数料計算

質問

Excelで、株取引の損益計算をしており、手数料計算のことで質問致します。

現在、会社Aの手数料は（取引額：手数料）
～10万円：0円
～100万円：900円
以降100万円増加毎＋420円
となっています。（実際はもう少し細かい設定ですが割愛）

セルDに取引額合計を入れ、セルEにIF文で
IF(D:D<=100000,0,IF(D:D<=1000000,900,IF(D:D<=2000000,1320,IF(D:D<=3000000,1740,（・・・と続く）
のようにしていますが、あまりに長ったらしく、また上限があるようで、
想定額の半分ほどしか入りません。（mac版ver.xを使用しています）
現在はセルFにも同じ文で金額のより大きいものを作り対処していますが、
もう少し簡略し、すっきりした関数はないものかと思い、皆さまのお知恵を
拝借に伺いました。

なお、マクロは分からないので、関数のみでお願いできれば、と思います。
お手数を掛けますが、よろしくお願い申し上げます。

回答No.3

mshr1962 · ベストアンサー

通常なら
下限　　上限　　手数料
0 100000　0
100001　1000000　900
1000001 2000000　1320
2000001 3000000　1740
で
=VLOOKUP(D2,上記の表範囲,3,TRUE)

今回の場合(100万単位に等金額)なら
=IF(D1<=100000,0,INT(D1/1000000)*420+900)

お礼

分かりやすいご回答をありがとうございました。

補足に書いた質問は、ご回答No.3の方のやり方で解決致しました。
いろいろな関数があるのだなぁ、と、自分の勉強不足と、Excelの
奥深さを知りました。

お陰様で、思い通りのものができあがり、感激しました。
本当にありがとうございました。

補足

分かりやすいご回答、ありがとうございます。
思えば何年も前にVLOOKUPでやろうとして、上手く出来ず挫折したのでした・・・。

ところで100万円毎に等金額の部分なのですが、お教え頂いた、
=IF(D:D<=1000000,0,INT(D:D/1000000)*420+900)
でやってみました所、200万円,300万円等、切りのいい数字の時、
手数料が1段階高くなってしまいます。
（200万円ならば1320円になるところ、1740円になってしまう）
200万を越えて（200万1円より）1段階高くなるのですが、どのように手直しすればよろしいでしょうか・・・？

何度も幼稚な質問、大変すみません。
お時間ございましたら、お助け下さいますと幸いです。

回答No.4

mu2011

次の様な対応表から抽出する方法は如何でしょうか。
【手数料対応表】
A,B
0,0
100001,=(ROW()-2)*420+900
1000001,同上
1100001,同上
以降、省略

仮にC1を取引額、C2の手数料は以下の数式で表示します。
C2は、=IF(C1<>"",VLOOKUP(C1,$A$1:$B$4,2,TRUE),"")

お礼

分かりやすいご回答、ありがとうございました。
知らなかった関数を教えていただき、大変勉強になりました。

心より御礼申し上げます。

回答No.2

Dxak

=IF([対象セル]>100000,900,0)+ROUNDDOWN(([対象セル]-1)/1000000,0)*420

で、行けない？
しかし、実際、設定が違うなら、VLookUp関数で、表から引っ張るのが良いような気もするんですが・・・

お礼

分かりやすいご回答、ありがとうございます。

100万円以下をVLOOKUPで、それ以上をお教えの通りにすることで、
とてもすっきりした、思い通りの計算が出来るようになりました！！

思えば始めVLOOKUPでやりたくて、出来ずにIF文にしたのでした・・・。
何年も前に作ったため、忘れておりました。

これでしたら手数料の改訂があった場合にも、書き直しし易そうです。
本当に、ありがとうございました。

回答No.1

triumph405

1000000のところをPOWER関数を使用してはいかがでしょうか？

POWER関数とは累乗ができる関数です。

1,000,000は・・・
POWER(10,5)

3,000,000は・・・
(3*POWER(10,5))

このような形にすればだいぶすっきりするのでは。

お礼

早速のご回答、ありがとうございます。
恥ずかしながらPOWER関数の事を全く知らず、大変勉強になりました。
本当にありがとうございました。