マクロや関数に詳しい方

質問

私は個人で店を経営しているものです。
私の店では、その滞在時間に応じて料金を加算していく（漫画喫茶のような）料金設定をとっています。
この清算をエクセルで計算したいと思っているのですが、
どのようにすればよいか教えて頂ければ幸いです。

詳細です↓
(1)30分以内で300円として、その後30分ごとに300円ずつ加算されていきます。
(2)3時間以上から5時間までは定額で1800円です。
(3)5時間からは、1800円＋30分経過ごとに300円の追加料金という形です。
(4)ちなみに、入室時間と退室時間を入力すれば料金が表示できるようにしたいです。
(5)滞在時間が50分や1時間45分などの中途半端な時間ならば、それぞれ60分・2時間扱いで計算します。

滞在時間は退室時間から入室時間を引いて出すことが出来るのですが、
その以降の計算が関数やマクロを使用しても、どうしても自分ではできません。
エクセルに詳しい方のお力を頂ければと思い質問させていただきました。

わかりにくい説明で大変申し訳ありませんが、どうかよろしくお願いいたします。

回答No.4

Flak45

＃３です。書き間違いがありました

＞Ａ５のセルに次の式を書きます

Ａ６のセルに

でした。

回答No.3

Flak45

ひとつの式に放り込んでみました（＾＾；

滞在時間＝Ｎ

Ｎ＋29分したものを30で割った商がＮ１とし
Ｎ１＜６であればＮ１＊３００
Ｎ１＞５かつＮ１＜１１であれば１８００
Ｎ１＞１０であれば（Ｎ１－１０）＊３００＋１８００
という式をひとつのセルに埋め込めば良いかと思います

Ａ１のセルに入店時間　
Ａ２のセルに出店時間
Ａ３のセルにその差　セル書式で[mm]としますと60分を超えた場合も分単位になります。
Ａ４にワンクッションおいて　＝Ａ３＊２４＊６０とします　セル書式は標準にしてください。
(ワンクッションおくのは時間が1日で1.00の数値だからです。24時間＊60分をかけて整数にします。)
Ａ５に　=INT((A4+29)/30)で、30分単位の数字が出ます。
Ａ３以外セル書式が[mm]にならないよう注意です。

Ａ５のセルに次の式を書きます
=IF(A5<6,A5*300,IF(AND(A5>5,A5<11),1800,(A5-10)*300+1800))
3時間未満ですと前部分で30分ごとに300円
3時間を超え5時間未満ですと中段の1800円定額
それ以外ですと(5時間超えですので)5時間までの1800円に5時間を超過した30分ごとに300円を追加した額になります。
尚、30分きざみで端数時間は1分でもオーバーすると繰り上がります。その辺調整するにはＡ４のセル計算式の29を任意にすればＯＫです。

まあ力ずくってことで、もっとスマートなやり方あると思いますので、参考程度で（＾＾；

お礼

わかりやすく式を入れて教えて頂きありがとうございます！！
試してみたところ表示されました！
非常に助かりました（*＾0＾）

回答No.2

imogasi

ＶＬＯＯＫＵＰ関数のＴＲＵＥ型をやってみましたか。
こういうタイプの問題は、まずＶＬＯＯＫＵＰ関数でできないか考えるものです。
これでできないほど複雑なルールかどうか、ご自分でやってみて、できなければ質問してください。
概略は本日の他の質問（点数の段階わけ）のに載せましたが。
＞マクロを使用しても、
自由度はあがりますが、難しくなるだけでしょう。

お礼

ＶＬＯＯＫＵＰ関数がありましたね！
まだ、試していませんでした。
教えてくださりありがとうございました。

回答No.1

shinkun0114

●場合分け
　こういう場合は、まず滞在時間により場合分けをしましょう。
IF関数を使うことになりますが、すべての条件を一つの式に
放り込んでしまうと、式が複雑になりすぎて、見た目に
わかりにくくないります。
　そこで、A列に滞在時間が入っている場合、
　　B列　3時間以下の式
　　C列　3時間～5時間の式
　　D列　5時間以上の式
のようにそれぞれ式を入れ、E列にIFを用いて
滞在時間に応じてどの式かを選ばせます。

●具体的な式
　A1に滞在時間が入っているとします。
　B1　=(FLOOR(A1-"0:01","0:30")*24*2+1)*300
　C1　=1800
　D1　=1800+(FLOOR(A2-"0:01","0:30")*24*2-9)*300
　E1　=IF(A1<VALUE("3:00"),B1,IF(A1<VALUE("5:00"),C1,D1))

なお、B～Eは表示形式がおかしくなる可能性があります。
時刻や日付表示になってしまったら、セルの書式設定で
数値や通貨などに変えてください。

お礼

アドバイスありがとうございます！！
具体的に式を入れて下さっているので、
早速試させて頂きます。