EXCELの複数条件式で、途中から条件が変わる場合

質問

マンションの管理をしており、水道料金をどうにか条件式で簡単に算出できないか苦戦している者です。

水道の使用量が１～１０立方メートルが２１４０円、１１～２０立方メートルが２４５０円、その後は２１立方メートル＝２５６０円、２２立法メートル＝２６８０円・・・・などと８０立法メートルまで金額が変化しますが、一定の値で上がっていかないので、それぞれの対応値を入れようかと最初、IF関数の入れ子形式で計算できると思っていたら、項目が多すぎて８項目以降はエラーになってしまいます。

項目数は、６０程になりますが、このような場合の条件式をどう設定したら良いのかお分かりになる方、どうか教えてください。手計算していたのですが、部屋数が多く、間違えてばかりになってしまい、困っています。

どうぞお力を貸してください。

回答No.4

Cupper · ベストアンサー

ANo.1 p-applepieさんの方法で良いと思います。

VLOOKUP関数を使うのであれば、
使用量　料金
　0 　　2140
　10　　2140
　20　　2450
　21　　2560
　22　　2680
　:　　　：
　80　高額請求
などと実際の料金表を作るといいです。
使用量と料金の対応する範囲を「料金表」と範囲名を付けると、
　=VLOOKUP(A1,料金表,2)
などと簡単に記述できます。

また、多くの水道事業体は１立方メートル単位での請求になるので
小数点以下の数値は考える必要は無いと思います。

※VLOOKUP関数の詳細はExcelのヘルプを参照してください。
※範囲名は、範囲を選択して、右上の名前ボックスに名前を入れるだけです。
　 修正や編集は 「挿入」→「名前」→「定義」で行えます。

お礼

とても的確なアドバイスありがとうございます。

おっしゃっているように、水道料金は整数値で出てきます。とても参考になりました。エクセルのヘルプも参考にしながら試してみたいと思います。

ありがとうございました。

回答No.3

leap_day

23立方メートル以上の変化がわからないのですが・・・
20立方以降は110円,120円、・・・と増えているので

=IF(A1>20,INT(SUM(A1-19.1))^2*5+INT(SUM(A1-19.1))*105+2450,IF(AND(A1<=20,A1>10),2450,IF(A1>0,2140,"")))

とすれば出ますよ

もし０立方メートルの家もあるならば

=IF(A1>20,INT(SUM(A1-19.1))^2*5+INT(SUM(A1-19.1))*105+2450,IF(AND(A1<=20,A1>10),2450,IF(A1>0,2140,IF(A1=0,0,""))))

にしてください

これでA1に数値を入れていくと
無記入 or 0 => 0
0.1 => 2140
10 => 2140
10.1 => 2450
20 => 2450
20.1 => 2560
21 => 2560
21.1 => 2680
22 => 2680
23 => 2810
32 => 4430
61 => 15160
80 => 26750
という風に表示されます

お礼

早速のアドバイスありがとうございます。

なるほど、このような方法もあるのですね。とても参考になります、ありがとうございます。

回答No.2

noname#204879

「水道料金」とのことですが、お示しの料金体系「１～１０立方メートルが２１４０円、１１～２０立方メートルが２４５０円、その後は…」というのは本当ですか？
というのは、使用量が1立法メートルであっても10立法メートルであっても同一料金はありえないと思うから。

ちなみに、私が知る水道料金の体系は次のようなものです。(東京23区の場合)
0～ 5立法メートルは、0円
6～10立方メートルは、1立方メートルにつき22円
11～20立方メートルは、1立方メートルにつき128円
(以下省略)

参考URL：

http://www.waterworks.metro.tokyo.jp/life/r_keisan.htm

お礼

早速のアドバイスありがとうございます。

参考のページを拝見させていただきました。ただ、こちらでは個人でなく建物全体に水道料金が一括で請求されるシステムなので、それを管理人が部屋の水道料を料金表に従って個別に請求するようになっています。

その算出に手間がかかり、エクセルでどうにか簡単に計算できないものかとお伺いさせていただきました。

ご回答ありがとうございました。

回答No.1

p-applepie

いっそ　1～80までであれば、別シートにコード表を作成してVLOOKUPで
算出するのはいかがでしょう。

例）　シート名　料金
A　　　　B
使用量　料金
1　　　　2140
2　　　　2140
・　　　・
10　　　2140
11　　　2450
・　　　・
・　　　・
80　　　○○○○

としておいて、
A　　　　　B　　　　C
部屋番号　使用量　　料金
のC列に下の関数を入れます。

=VLOOKUP(ROUNDUP(B2,0)料金!$A$1:$B$81,2,FALSE)

10立方メートルの欄を2140としたので
10.1立方メートルは11とするためにROUNDUPを入れています。

お礼

早速のアドバイスありがとうございます。

少数以下は四捨五入のようで、ROUNDUPは必要ないのですが、この方法であればできそうに思えてきました。

ありがとうございます。