締切済み

駐車場の料金計算で　悩んでおります。

2018/12/30 07:25

エクセル2003を使用しています。駐車場の料金計算で　悩んでおります。　アドバイス　お願いいたします。 ■条件１ 15分　100円 30分　200円 45分　300円 1時間　400円 2時間　800円 3時間45分　～12時間　　1500円 ■条件２ 12時間を過ぎたら 1500円　＋　　条件１最大で　48時間　　　12時間×４　　　　　　　　　　1500円×４　6000円を上限とする、 ■条件３時間に　マイナス10分とする。 25分経過していても　15分計算で　料金を算出。　　入庫時間　　出庫時間　経過時間　駐車料金　　　F2　　　　　G3　　　　H3　　　　I3 　　　↓　　　　　↓　　　　↓　　　　↓ よろしくおねがいします。

reo9546
お礼率61% (430/702)

オフィス系ソフト
回答数9
ありがとう数0

みんなの回答 （9）
専門家の回答

みんなの回答

HohoPapa
ベストアンサー率65% (454/690)

2019/01/01 15:27 回答No.9

No7です。絶対参照するのを漏らしていました。訂正します。 J3=MAX(ROUND(((H3-料金テーブル!$E$3)*24*60*60),1),0) K3=INT(J3/料金テーブル!$F$6) L3=MOD(J3,料金テーブル!$F$6) I3=MIN(料金テーブル!$G$6*K3+VLOOKUP(L3,料金テーブル!$B$3:$C$10,2),料金テーブル!$F$8)

bunjii
ベストアンサー率43% (3589/8248)

2018/12/30 20:08 回答No.8

回答No.5の追加です。 3時間45分まで15分毎に100円とし、12時間まで上限を1500円とする場合は規則性のある計算ができます。何故、12時間を超えるときに再入庫のようなリセットになるか不思議なのですがこれも規則性を持たせることができます。 F2セルに入庫日時がシリアル値で入力されているものとします。 G3セルには出庫日時が同様に入力されているものとします。条件3は出庫日時から入庫日時を引いた時間から10分を差し引いて駐車時間と定義しているものと推測しますが入庫から出庫までの経過時間が10分未満のときでも最低料金の100円を徴収するものとします。 I3セルには次の数式を設定します。 =MIN(SUM(MIN(ROUNDUP(((MOD((G3-F2)*2,1)/2-"0:10:00")*96),0),15)*100,MIN(INT((G3-F2)*2),4)*1500),6000) 入庫日時：2018/12/30 5:06 出庫日時：2018/12/30 18:39 経過時間：13時間33分駐車時間：13時間23分駐車料金：2,100円入庫日時：2018/12/30 17:50 出庫日時：2018/12/30 19:53 経過時間：2時間03分駐車時間：1時間53分駐車料金：800円入庫日時：2018/12/28 10:20 出庫日時：2018/12/30 19:58 経過時間：57時間38分駐車時間：57時間28分駐車料金：6,000円

HohoPapa
ベストアンサー率65% (454/690)

2018/12/30 15:58 回答No.7

条件１は、１０分控除後の時間が０分超　、１５分まで　　　　100円１５分超　３０分まで　　　　200円３０分超　４５分まで　　　　300円４５分超　１時間まで　　　　400円１時間超　２時間まで　　　　800円２時間超　３時間４５分まで　1000円？３時間４５分超　　　　　　　1600円測定時間は秒まで。ということでいいでしょうか？添付画像のように料金テーブルを設け、更に作業列を使う解はいかがでしょうか？ J3=MAX(ROUND(((H3-料金テーブル!E3)*24*60*60),1),0) K3=INT(J3/料金テーブル!F6) L3=MOD(J3,料金テーブル!F6) I3=MIN(料金テーブル!G6*K3+VLOOKUP(L3,料金テーブル!B3:C10,2),料金テーブル!F8)

imogasi
ベストアンサー率27% (4737/17068)

2018/12/30 15:15 回答No.6

・エクセルの関数でやりたいのか。まず、それぐらい質問に書いておくこと。・中核の部分は、ＶＬＯＯＫＵＰ関数の（第４）引数が、ＴＲＵＥ型の応用でできると思う。エクセルを多少勉強し出して、関数の解説書を読めば、ＶＬＯＯＫＵＰ関数のことは、ごく有名で、それだけの単行本もあるぐらいなものだ。その関数名さえも、出さずに質問をしているのは、相当初心者だと思われてしまう。また、日付シリアル値のことも、はっきり知らないかもしれない。しかし重要なことです。そういうレベルでは、この課題をやるのは早すぎるU危険性も多い）。・個人的アドバイス（１）データは、エクセルの日付（時間）シリアル値でシートに入力すること。（２）商売にからむなら、検証テストをしっかりやること。　　特に区分が変わる時点の１分の差で、どちらに含まれるか、十分注意のこと．未満か以上かといったことに似ている問題のこと。　　本件では、１分間隔の単位のデータで、別シートを使い、最低VLOOUP関数の結果はテストをしておくこと。（３）全体に、VBAで時間帯を区分けして、大枠を分けて記述しておくことを勧める。　　VBAでもVLOOKUP関数（のTRUE型）は、同じ様に式を書いて、使える。本件で、１２時間を過ぎた部分は（条件２の部分は）VBAで大枠区分しておくことを勧める。 VBAでコードを書いておいた方が、複数行のコードが書けたり、CASE文というのが使えて、区分して書きやすく、後の人の理解や可視性や変更が明確になりやすい。エクセル関数は１行に多くの条件を盛り込む感じになるので、複雑な条件では、わかりにくい。ーー VLOOKUP関数のテストの参考事項 A-C列 A　FROM B　To B－A 2018/12/30 12:10 2018/12/30 12:21 0.0076388889 2018/12/30 12:10 2018/12/30 12:31 0.0145833333 2018/12/30 12:10 2018/12/30 12:51 0.0284722222 2018/12/30 12:10 2018/12/30 13:11 0.0423611111 2018/12/30 12:10 2018/12/30 15:31 0.1395834491 2018/12/30 12:10 2018/12/30 16:31 0.1812501736 2018/12/30 12:10 2018/12/30 12:31 0.0145832755 2018/12/30 12:10 2018/12/30 12:51 0.0284722222 2018/12/30 12:10 2018/12/30 13:11 0.0423610532 2018/12/30 12:10 2018/12/30 13:31 0.0562499421 2018/12/30 12:10 2018/12/30 13:51 0.0701388310 2018/12/30 12:10 2018/12/30 14:11 0.0840277199 2018/12/30 12:10 2018/12/30 14:31 0.0979166088 2018/12/30 12:10 2018/12/30 14:51 0.1118054977 2018/12/30 12:10 2018/12/30 15:11 0.1256943287 2018/12/30 12:10 2018/12/30 15:31 0.1395832176 2018/12/30 12:10 2018/12/30 15:51 0.1534721065 2018/12/30 12:10 2018/12/30 16:11 0.1673609954 2018/12/30 12:10 2018/12/30 16:31 0.1812498843 2018/12/30 12:10 2018/12/30 16:51 0.1951387731 2018/12/30 12:10 2018/12/30 17:11 0.2090276620 2018/12/30 12:10 2018/12/30 17:31 0.2229165509 2018/12/30 12:10 2018/12/30 17:51 0.2368054398 2018/12/30 12:10 2018/12/30 18:11 0.2506943287 2018/12/30 12:10 2018/12/30 18:31 0.2645832176 2018/12/30 12:10 2018/12/30 18:51 0.2784721065 D、E、F、G列 11 11 11 100 21 21 21 200 41 41 20 300 61 61 20 800 201 201 140 1000 261 261 60 1500 21 21 -240 200 41 41 20 300 61 61 20 800 81 81 20 800 101 101 20 800 121 121 20 1000 141 141 20 1000 161 161 20 1000 181 181 20 1000 201 201 20 1000 221 221 20 1000 241 241 20 1500 261 261 20 1500 281 281 20 1500 301 301 20 1500 321 321 20 1500 341 341 20 1500 361 361 20 1500 381 381 20 1500 401 401 20 1500 D2の式 =DAY(C2)*24*60+HOUR(C2)*60+MINUTE(C2) E2の式 =TEXT(B2-A2,"[mm]")*1 D列とE列は違う式で同じ結果を求めて検証しただけ。D列の方がポピュラーか。 F2の式　データの隣接分時間間隔です。確認用でテストデータ作成の時の参考。 =E2-E1 G2の式 =VLOOKUP(E2,$I$3:$J$11,2,TRUE) ＝＝＝このほかに前もって、 J\I,J列に（空いた列に） 0 100 15 200 30 300 45 400 60 800 120 1000 225 1500 720 のような参照表（対応表）データが必要。この作り方が、本問のすべてなので、十分チェックしてください。ーーずいぶん時間がかかるのと、十分質問者の場合（駐車場の料金の仕組）を小生は理解してないのと、どうしても他人事なので、不完全かも。回答者の方の看做しや補足も取り入れてやった（こういうのが読者から出るのは、質問者の思考や表現が精緻でない印だ）。　マイナス１０分ルールは、E列（かF列）ででも、１０を引けばよい。上記ではこの条件を込めてない。　　

bunjii
ベストアンサー率43% (3589/8248)

2018/12/30 13:11 回答No.5

駐車料金の定義が不明確なので的確な数式を提示できません。一般的な料金の考え方は次のようになります。基本料金=15分まで毎に100円長時間料金=12時間まで毎に1500円最長駐車時間=48時間まで（48時間を超えるとレッカー移動します）他の考え方として時間帯で駐車料金を変える。 9時から19時まで15分まで毎に100円 19時から翌朝9時まで2時間まで毎に100円質問の料金体系を分かり易く説明してください。