Excelで料金表

質問

B7は入居者氏名、E7には使用量[立法米]が入っています。
入居者がいない＝空欄時には計算しないことを考慮し
こんな数式を作ってみました。
=IF(B7="","",IF(E7<16,1040,IF(E7<40,1040+85*(E7-16),IF(E7<60,3080+100*(E7-40),5080+120*(E7-60)))))

0～16まで基本料\1040円で以後、17～40、41～60、61～100、101～
と段階的に超過料金が発生します。
上記数式だと次回料金改定時にメンテナンスしにくく
思いますので皆さんのお知恵を拝借したく思います。
なお、式中1040、3080、5080となってますが、
数式で入れると式が長すぎると警告されたので仕方なく
計算していれた結果です。

（料金表シートを作り、lookupも検討してみましたが
　1ずつ増加のテーブル作るの大変ですし、これを楽して
　マウスでドラッグ操作で作成したとして40、60の境界
　で計算間違いしそうで、案としては考えましたが、
　没ネタにしてます。）

他に後で見てわかりやすく、かつ、メンテしやすい方法ありましたらご教示お願いします。

回答No.1

misatoanna · ベストアンサー

料金マスタと LOOKUP関数 を使われたらいかがでしょうか。

たとえば、F1:F5 に 0,16,40,60,100 の順に入力、
G1:G4 に 85,100,120 の順に入力、
H1:H2 に 0,1040 の順に入力します。
ここまでは、基本料金など変化する可能性のあるデータです。
H3 に =H2+(F3-F2)*G2 と入力して、H4:H5 にコピーします。
以上が料金マスタです。

ここで、A3 に 56 という使用量が入力されているとして、B3 に
=(A3-LOOKUP(A3,$F$1:$F$5))*LOOKUP(A3,$F$1:$G$5)+LOOKUP(A3,$F$1:$H$5)
と入力すれば、料金が表示されます。

回答No.2

misatoanna

#1 です。
間違っていました。

> H1:H2 に 0,1040 の順に入力します。

H1、H2 とも 1040 を入力します。

お礼

検算ありがとうございます。
ご回答G列に対してh列が一段ずれての入力なので
0,17,41・・・の列を前に追加し数式編集等応用させて
頂きました。
応用きかせている間に返答遅れてしまい恐縮です。
おかげさまで満足した結果を得ることができました。