コピー機の保守料金を求める計算式

質問

会社でカラーと白黒のコピー機を使っていますが、毎月業者からの請求が手書きで来る為、社長から再度計算するように言われて手計算でやっています。エクセルで毎月の実枚数を入れるだけで自動計算したいのですが計算式を作るだけのスキルがありません。どなたか教えていただけないでしょうか。
カラーコピーの保守料金は、
（1）基本料金2,600円
（2）モノカラー（実枚数から２％控除した枚数で計算します）
　　　　1枚～200枚まで＠10円、
　　　　201枚～500枚まで＠8円、
　　　　501枚以上＠7円。
　　フルカラー（実枚数から３％控除した枚数で計算します）
　　　　1枚～1000枚まで＠40円、
　　　　1001枚～3000枚まで＠34円、
　　　　3001枚以上＠29円。
　　フルカラープリント（実枚数から３％控除した枚数で計算します）
　　　　1枚～1000枚まで＠34円、
　　　1001枚～3000枚まで＠29円、
　　　3001枚以上＠25円。
カラーコピーは、カウンターが3つあり、それぞれ毎月枚数が出ます。
（1）と（2）を比べてどちらか高い方が保守料金になります。

白黒コピーの保守料金は、
（実枚数から2％控除した枚数で計算します）　　
　　基本料金（500枚まで含）4750円、
　　501枚～2000枚まで＠7円、
　　2001枚以上＠6.5円。
白黒コピーはカウンターが1つで500枚以下の場合は基本料金が適用されます。
と、このように非常に複雑で困っています。
スペシャリストの方、是非よろしくお願いします

回答No.2

imogasi · ベストアンサー

ポイントは単価計算にあるのだと思うが、
ＶＬＯＯＫＵＰ（ＴＲＵＥ型）を使うが、３つの検索表をＩＮＤＩＲＥＣＴ関数で切り替えられるようにする。まあ定石的な手法です。
ーー
Ｈ１：Ｍ４　単価表を作成します。（区切りのＸＸ以上に当たる数値に注意）
モノ フル プリント
0 10 0 40 0 34
201 8 1001 34 1001 29
501 7 3001 29 3001 25
ーー
Ｈ１：Ｉ４－－＞「モノ」というなで名前を定義します。
Ｊ１：Ｋ４－－＞「フル」というなで名前を定義します。
Ｌ１：Ｍ４－－＞「プリント」というなで名前を定義します。
各々範囲指定しておいて、名前ボックスに名前を打ち込みます。
実際は下記Ａ２，Ａ３，Ａ４に打ち込む語句と合わせて、モノ、フル、プリントの語句を
決めてください。

ーーー
Ａ２に、モノ、フル、プリントのどれかを入れます。
Ｂ２に使用数量を入れます。
Ｃ２の式は単価を求めて　=VLOOKUP(B2,INDIRECT(A2),2,TRUE)
--
Ａ２ にモノの枚数
A３にフルの枚数
Ａ４にプリントの枚数
を入れている場合は、Ｃ２の式を下方向に複写。
ーー
結果　例
モノ 321 8
フル 1231 34
プリント 567 34
ーー
Ｃ列とＢ列をかけて足すと（ＳＵＭＰＲＯＤＵＣＴ）
=SUMPRODUCT(B2:B4,C2:C4)
結果
６３７００
ーー
基本料金を加える
2,600を加える。
ーー
割引枚数は、単価表を引く前に（単価計算前に）引くのか、計算後か
小生には正確にわからなかったので、良くわかっている質問者に任せますので、そちらのほうで修正してください。

お礼

検証に時間がかかりましたが、うまくできました。ありがとうございました。

回答No.1

hallo-2007

一度で式を作成するのでなくて
（2）モノカラー（実枚数から２％控除した枚数で計算します）
　　　　1枚～200枚まで＠10円、
　　　　201枚～500枚まで＠8円、
　　　　501枚以上＠7円。
ですが
　　A　　B
1
2　
3　0　　10
4　19　　7
5　499　　8　と表を作っておいて、仮にA1に実枚数入れて
A2セルに　=VLOOKUP(0.98*B2,B3:C6,2)*B2
でモノカラーの金額がでると思いますので、同様に右に表と式作って足し算できませんか？

お礼

検証に時間がかかりましたが、うまくできました。ありがとうございました。