駐車場の料金算出
｜
｜
数式 / 関数 ｜
２００７／６／１４追記
こちらで紹介している計算式に誤りが発見されました。考え方として記事は残しておきますが、計算式をそのまま使うことは止めてください。
駐車料金算出するにはこちら。
「時間貸しの駐車場精算機から入庫時間、出庫時間のデータをエクセルに落とし、料金を算出したい」
というご依頼がありました。
駐車場の料金体系はこの様になっているそうです。
時間帯によって料金が変わってしまうので、その場合の計算をどうすれば良いのか？という事ですね。
しかも時間のデータが「2007/6/1 13:00」の様に日付・時間データが一つのセルになっているとの事。こういう感じですか。
時間の取り出し
まず、過去記事［エクセルで日付データの認識は］
エクセルでは数値の１を１日とし、0.5だと12時間、1.5だと1日と12時間です。
「2007/6/1 10:00」というＢ２セルから時間だけを抽出したい・・・
| =HOUR(B2) |
でいいと思うんですが、これではただ数値の１０しか返ってこず、これを時間データとして扱うと１０日となってしまいます。
そこで時間データとして扱える様にするには
| =TIME(HOUR(B2),MINUTE(B2),SECOND(B2)) |
という風にＴＩＭＥ関数を使います。
ＴＩＭＥ関数は（時間，分，秒）を引数としています。
これで入庫時間・出庫時間が抽出します。
差し引き時間
C列からB列を単純に引くとどうなるか？（=C2-B2）
No1の場合だと9時間です。これはもちろん時間データですのでNo5の場合1日と20時間57分となります。
G列に差し引きした時間を出してみました。
分かりやすい様に表示形式は「d”日” hh”時間”mm”分”」としています。
昼間の駐車時間は・・・？
6:00～20:00を昼間、20:00～6:00を夜間と呼んでおきます。
先ほど算出した駐車時間の内、昼間に停めていたのは何時間なのか？夜間は何時間なのか？が解ればこの課題はクリアです。
では、どの様に昼間の駐車時間を出すか。以下の判断が必要になります。
- 入時間が昼間かどうか？
- 出時間が昼間かどうか？
- 入時間 ＜ 出時間 か？（E列＜F列）
この３点です。
入時間が昼間で、出時間が夜間ならば昼間の駐車時間は２０時－入時間になりますし、入時間が夜間で出時間が昼間ならば出時間－６時が昼間の駐車時間になります。
分かりやすく表にしてみましょう。
| 入時間 昼間 | 入時間 夜間 | |
| 出時間 昼間 | 出時間 － 入時間 | 出時間 － ６時 |
| 出時間 夜間 | ２０時 － 入時間 | なし |
判断条件の３番目は、No2やNo5、No8のパターン用の判定です。１３時に入って、翌朝８時に出た場合の昼間駐車時間は
２０ － １３ ＋ ８ － ６ ＝ ９時間
な訳ですから、上の表とは別の計算を行わなければなりません。
実際の計算式
以上を踏まえまして昼間の計算式は・・・
| =IF(AND(E2>=$C$15,E2<=$E$15),IF(AND(F2>=$C$15,F2<=$E$15),
IF(F2>=E2,G2,$E$15-E2+F2-$C$15),$E$15-E2), IF(AND(F2>=$C$15,F2<=$E$15),F2-$C$15,)) |
長いです・・・
あとは夜間の駐車時間ですが、差時間から昼間駐車時間を引くだけでＯＫです。
そしてこの昼・夜の駐車時間というのは日数を除いたものですのでご注意を。12:00から翌日12:00ぴったりに出れば昼駐車時間、夜駐車時間ともに０となります。
丸一日止めた場合の料金は別で計算する為です。
そして、昼間の駐車時間×４００円＋夜間の駐車時間×７０円で駐車料金が出ます。
昼間の駐車料金 =HOUR(H2) * 400円
夜間の駐車料金 =HOUR(I2) * 70円
一日の駐車料金 =DAY(G2) * 6300円
但し・・・
この計算式だと時間以下切り捨てになってしまいます。上の画像では「分込み」という列を作りましたが、分計算もしてみました。
| +MINUTE(H2)/60*$F$15+MINUTE(I2)/60*$F$16 |
この計算式を「分切り捨て」に足すと、１時間４００円、３０分２００円の端数計算をします。１円単位までやっちゃいます。
但し・・・②
この計算には欠点があります
昼間と夜間の計算を別モノとしているので、「分切り捨て」で計算した場合、１９：０１入庫の２０：５９出庫だと無料です！
昼間の駐車時間５９分、夜間の駐車時間５９分を共に切り捨てるので・・・
「分込み」だと大丈夫ですけどね。
簡潔に計算式だけ！
うだうだと説明から入りましたが急ぎの場合、こちらの計算式をＤ列に入れると計算してくれます。
=HOUR(IF(AND(TIME(HOUR(B2),MINUTE(B2),)>=TIME(6,,), TIME(HOUR(B2),MINUTE(B2),)<=TIME(20,,)), IF(AND(TIME(HOUR(C2),MINUTE(C2),)>=TIME(6,,), TIME(HOUR(C2),MINUTE(C2),)<=TIME(20,,)), IF(TIME(HOUR(C2),MINUTE(C2),)>=TIME(HOUR(B2),MINUTE(B2),), C2-B2,TIME(20,,)-TIME(HOUR(B2),MINUTE(B2),)+TIME(HOUR(C2),MINUTE(C2),)-TIME(6,,)), TIME(20,,)-TIME(HOUR(B2),MINUTE(B2),)), IF(AND(TIME(HOUR(C2),MINUTE(C2),)>=TIME(6,,), TIME(HOUR(C2),MINUTE(C2),)<=TIME(20,,)), TIME(HOUR(C2),MINUTE(C2),)-TIME(6,,),)))*400+ HOUR((C2-B2-IF(AND(TIME(HOUR(B2),MINUTE(B2),)>=TIME(6,,), TIME(HOUR(B2),MINUTE(B2),)<=TIME(20,,)), IF(AND(TIME(HOUR(C2),MINUTE(C2),)>=TIME(6,,), TIME(HOUR(C2),MINUTE(C2),)<=TIME(20,,)), IF(TIME(HOUR(C2),MINUTE(C2),)>=TIME(HOUR(B2), MINUTE(B2),),C2-B2,TIME(20,,)-TIME(HOUR(B2), MINUTE(B2),)+TIME(HOUR(C2), MINUTE(C2),)-TIME(6,,)),TIME(20,,)-TIME(HOUR(B2),MINUTE(B2),)), IF(AND(TIME(HOUR(C2),MINUTE(C2),)>=TIME(6,,), TIME(HOUR(C2),MINUTE(C2),)<=TIME(20,,)), TIME(HOUR(C2),MINUTE(C2),)-TIME(6,,),))))*70+ DAY(C2-B2)*6300 |
・・・
恐ろしい程に長い・・・
やはり説明の流れどおりに作業用セルを作ったほうが良さそうです。
それかユーザー定義関数を作る方法が良いかもしれません。
関連記事
- 前の記事
- 配列数式とは
- 次の記事
- 駐車場の料金算出 ～ユーザー定義関数～
