AWSのサポート費用を計算する
AWSのサポート費用は、AWSの利用料に対してその利用料に応じた料率をかけることで算出します。「どれくらいのAWS利用料でどのくらいのサポート費用になるのか知りたい！」という時にはこの料率を使って計算しますが、この料率は利用料によって段階的に変わるのでそのまま記述すると数式がとても煩雑になります。しかし、配列数式を使うことで比較的見やすい数式になります。
苦戦しつつも学びを得た感じでかなりExcel力が上がった気がします♪
ということで、備忘のためにまとめておきます。
例えば、AWSビジネスサポート料金の説明には次のように記載されています。
これをExcel上で計算したいとすると次のようにB1に利用料を入れるとB2にサポート費用が計算されるようにします。
この時のB2の計算式は次のように書きました。
=(B1<=1000)*100
+(B1>1000)*B1*B5
-SUMPRODUCT((B1>A6:A8)*(B1-A6:A8)*(B5:B7-B6:B8))
ここでのポイントは次の３点です。
1.全体の費用を10%で計算した後、量が多い際の割引を引く
2.論理式を使う
3.配列数式を使う
上記３点を見ていきます。
1.全体の費用を10%で計算した後、量が多い際の割引を引く
まず、最初に書いたのは次のような表でした。
各レンジ毎の費用を6行目で計算し、B2はその合計を表示しています。
例えばE6の数式は次の様になっています。
=IF($B$1>D$4,IF($B$1>E$4,(E$4-D$4),($B$1-D$4))*E5,0)
なんとなく力技的に書いたのですが、複数パターンを計算したい場合、
6行目をいくつも書かなければいけなくなり、ちょっと辛いなと思いました。
最近ちょっと頭が固くなっている今日このごろですが、ちょっと調べると、ドンピシャなこちらを見つけ、まさに答えはここにある、という感じでした。
http://www.excel.studio-kazu.jp/kw/20070325171314.html
最初はちょっとむずかしいなと思いましたが、ちゃんと読み解くとわかれば理解できました(^_^;
引き算のほうが範囲を考えずに増えた際に割引分を引けば良いだけなので、楽でした。。。
2.論理式を使う
(B1<=1000)*100 のような使い方です。
この場合、B1が1000以下であれば (B1<=1000)の部分は1になります。
逆にB1が1000を超えると0になります。
そのため、(B1<=1000)のような書き方でif関数を使わずに記述できます。
論理式で引き算にすると次のような計算式ができます。
=(B1<=1000)*100 →B1が1000以下なら100
+(B1>1000)*B1*B5 →B1が1000を超えたら利用料(B1)に料率10%(B5)をかける
-(B1>A6)*(B1-A6:)*(B5-B6)) →B1が10000を超えたら、超えた分の割引分(B5-B6)を引く
-(B1>A7)*(B1-A7:)*(B6-B7)) →B1が80000を超えたら、超えた分の割引分(B6-B7)を引く
-(B1>A8)*(B1-A8:)*(B7-B8)) →B1が250000を超えたら、超えた分の割引分(B7-B8)を引く
3.配列数式を使う
数字を並べて配列数式を使う場合には{1,2,3}のように記述します。
例えば、次のような計算式を考えます。
=sum(2*{1,2,3})
この結果は2に{1,2,3}の部分を順に掛け、掛けた数字を足しあわせ、次のようになります。
2*1+2*2+2*3=12
ということで計算式に数字をそのまま記載すると、B2の計算式は次のように記述できます。
=(B1<=1000)*100
+(B1>1000)*B1*B5
-SUM((B1>{10000,80000,250000})*(B1-{10000,80000,250000})*{0.03,0.02,0.02})
これを表の値を参照する形で書き換える場合は、SUM関数をSUMPRODUCTに変更し、次のような記述ができます。
=(B1<=1000)*100
+(B1>1000)*B1*B5
-SUMPRODUCT((B1>A6:A8)*(B1-A6:A8)*(B5:B7-B6:B8))
