駐車場の料金算出

数式 / 関数


2007/6/14追記
こちらで紹介している計算式に誤りが発見されました。考え方として記事は残しておきますが、計算式をそのまま使うことは止めてください。
駐車料金算出するにはこちら


「時間貸しの駐車場精算機から入庫時間、出庫時間のデータをエクセルに落とし、料金を算出したい」
というご依頼がありました。
駐車場の料金体系はこの様になっているそうです。
27-1.gif
時間帯によって料金が変わってしまうので、その場合の計算をどうすれば良いのか?という事ですね。
しかも時間のデータが「2007/6/1 13:00」の様に日付・時間データが一つのセルになっているとの事。こういう感じですか。
27-2.gif

時間の取り出し

まず、過去記事[エクセルで日付データの認識は
エクセルでは数値の1を1日とし、0.5だと12時間、1.5だと1日と12時間です。
「2007/6/1 10:00」というB2セルから時間だけを抽出したい・・・

=HOUR(B2)

でいいと思うんですが、これではただ数値の10しか返ってこず、これを時間データとして扱うと10日となってしまいます。
そこで時間データとして扱える様にするには

=TIME(HOUR(B2),MINUTE(B2),SECOND(B2))

という風にTIME関数を使います。
TIME関数は(時間,分,秒)を引数としています。
これで入庫時間・出庫時間が抽出します。
27-3.gif

差し引き時間

C列からB列を単純に引くとどうなるか?(=C2-B2)
No1の場合だと9時間です。これはもちろん時間データですのでNo5の場合1日と20時間57分となります。
G列に差し引きした時間を出してみました。
分かりやすい様に表示形式は「d”日” hh”時間”mm”分”」としています。
27-4.gif

昼間の駐車時間は・・・?

6:00~20:00を昼間、20:00~6:00を夜間と呼んでおきます。
先ほど算出した駐車時間の内、昼間に停めていたのは何時間なのか?夜間は何時間なのか?が解ればこの課題はクリアです。
では、どの様に昼間の駐車時間を出すか。以下の判断が必要になります。

  1. 入時間が昼間かどうか?
  2. 出時間が昼間かどうか?
  3. 入時間 < 出時間 か?(E列<F列)

この3点です。
入時間が昼間で、出時間が夜間ならば昼間の駐車時間は20時-入時間になりますし、入時間が夜間で出時間が昼間ならば出時間-6時が昼間の駐車時間になります。
分かりやすく表にしてみましょう。

入時間 昼間 入時間 夜間
出時間 昼間 出時間 - 入時間 出時間 - 6時
出時間 夜間 20時 - 入時間 なし

判断条件の3番目は、No2やNo5、No8のパターン用の判定です。13時に入って、翌朝8時に出た場合の昼間駐車時間は
20 - 13 + 8 - 6 = 9時間
な訳ですから、上の表とは別の計算を行わなければなりません。

実際の計算式

以上を踏まえまして昼間の計算式は・・・

=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,))

長いです・・・
27-5.gif
あとは夜間の駐車時間ですが、差時間から昼間駐車時間を引くだけでOKです。
そしてこの昼・夜の駐車時間というのは日数を除いたものですのでご注意を。12:00から翌日12:00ぴったりに出れば昼駐車時間、夜駐車時間ともに0となります。
丸一日止めた場合の料金は別で計算する為です。
そして、昼間の駐車時間×400円+夜間の駐車時間×70円で駐車料金が出ます。
27-6.gif
昼間の駐車料金 =HOUR(H2) * 400円
夜間の駐車料金 =HOUR(I2) * 70円
一日の駐車料金 =DAY(G2) * 6300円

但し・・・

この計算式だと時間以下切り捨てになってしまいます。上の画像では「分込み」という列を作りましたが、分計算もしてみました。

+MINUTE(H2)/60*$F$15+MINUTE(I2)/60*$F$16

この計算式を「分切り捨て」に足すと、1時間400円、30分200円の端数計算をします。1円単位までやっちゃいます。

但し・・・②

この計算には欠点があります
昼間と夜間の計算を別モノとしているので、「分切り捨て」で計算した場合、19:01入庫の20:59出庫だと無料です!
昼間の駐車時間59分、夜間の駐車時間59分を共に切り捨てるので・・・
「分込み」だと大丈夫ですけどね。

簡潔に計算式だけ!

うだうだと説明から入りましたが急ぎの場合、こちらの計算式をD列に入れると計算してくれます。


=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

・・・
恐ろしい程に長い・・・
やはり説明の流れどおりに作業用セルを作ったほうが良さそうです。
それかユーザー定義関数を作る方法が良いかもしれません。

コメントをどうぞ

メールアドレスが公開されることはありません。

封筒 STDEV TODAY 作業効率UP 条件付き書式 SUM フロー図 XLSTART ワイルドカード 図形 偏差値 標準報酬月額 テンプレート クイズ 目標 COLUMN VLOOKUP 配列数式 検索 NA() 履歴書 コピペ ネットプリント 無料 コンビニ COUNTIF チルダ SUMIF バーコード 印刷
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑