[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複雑な延長料金の計算』（おび）
いつもお世話になってます。
職場の利用者様の延長利用料金を自動化させたいと挑戦していますが、うまくいきません。よければアドバイスお願いいたします。
当施設は延長利用の月額料金が時間によって区切られており、以下の通りです。
⑴18:01〜18:30 1,000円
⑵18:31〜19:00 2,000円
⑶19:01〜19:30 3,000円
⑷19:31〜20:00 4,000円
上記に登録されておらず、延長利用時間開始の18:00を過ぎると、単発利用として30分毎に200円の徴収となります。≪例20:00まで利用すると200×4枠=800円≫
5回分利用すると、⑴の料金に到達する為、自動的に⑴の延長登録に切り替わり、6回目からの⑴の枠の単発の延長料金がかからなくなります。
挑戦したみた計算式です。
?@時間をセルに入力する
18:30 18:50 19:00
?A入力した時間から延長区間を割り出す
=IF(L4>=TIME(19,31,0),4,IF(L4>=TIME(19,1,0),3,IF(L4>=TIME(18,31,0),2,IF(L4>=TIME(18,1,0),1,0))))
?B上記を利用し、延長料金を算出する。
=IF(AD4+X4*200>1000,【(1000+((V4+W4+X4-5)*200))】,IF(AD4+X4*200>2000,(2000+((V4+W4+X4-10)*200)),IF(AD4+X4*200>3000,(3000+((V4+W4+X4-15)*200)),IF(AD4+X4*200>4000,4000,AD4+X4*200))))
上手くいったと思っていたら、?Bの計算で全て【】の範囲が適応されてしまっています。
詳しい方、教えてくださると幸いです。
どうぞ、よろしくお願いいたします。
＜ 使用 Excel：Excel2019、使用 OS：Windows11 ＞
こんばんは…延長料金の算出一例です。 A列に延長時間を入力したとする 月額料金については、意味が理解できないので参考になればです。
|[A] |[B]
[1] |延長時間|加算金額
[2] |18:30 | 200
[3] |18:31 | 400
[4] |18:50 | 400
[5] |19:00 | 400
[6] |19:01 | 600
[7] |19:29 | 600
[8] |19:30 | 600
[9] |19:45 | 800
[10]|20:00 | 800
[11]|20:01 | 1000
B2の式=(A2>"18:00"+0)*200+(A2>"18:30"+0)*200+(A2>"19:00"+0)*200+(A2>"19:30"+0)*200+(A2>"20:00"+0)*200 A列の下までコピーする
式を貰うより、レイアウトを頂いた方が回答者は理解しやすいと思いますので 出来たらあるといいでしょう。
もう今日は遅いので、提示された式をゆっくり閲覧する 時間はありませんのですいません。
明日も時間が取れないので...他の回答者にフォローしてもらってください。
（あみな） 2023/03/16(木) 22:03:45
=200*IF(延長月額登録の有無,5,MIN(5,COUNTIF(範囲,">"&時刻))
これを18:00,18:30,19:00,19:30の4つ分つくるのはどうでしょう
（火災報知器） 2023/03/17(金) 01:58:59
よくわかってないけど、単票形式で人毎に表が作ってあるのかな？
|[K] |[L] |[M] |[N] |[O] |[P] |[Q]
[1] |Aさん利用履歴|上限金額|1,000|2,000| 3000| 4000|
[2] | |延長時間|18:01|18:31|19:01|19:31|
[3] |日付 |退出時間|延長1|延長2|延長3|延長4|
[4] |3月1日 |18:00 | 0| 0| 0| 0| 0
[5] |3月2日 |18:01 | 200| 0| 0| 0| 200
[6] |3月3日 |18:30 | 200| 0| 0| 0| 400
[7] |3月4日 |18:31 | 200| 200| 0| 0| 800
[8] |3月5日 |19:00 | 200| 200| 0| 0|1,200
[9] |3月6日 |19:01 | 0| 200| 200| 0|1,600
[10]|3月7日 |19:30 | 0| 200| 200| 0|2,000
[11]|3月8日 |19:31 | 0| 200| 200| 200|2,600
[12]|3月9日 |20:00 | 0| 200| 200| 200|3,200
M4=IF(Q3>=M$1,0,IF($L4>=M$2,1,0)*200) 下と右にコピー Q4=SUM(M4:P4,Q3) 下にコピー 手で計算したのと、Q12の合計があっていれば計算できてるか？
（稲葉） 2023/03/17(金) 09:35:01
IF(1000以上か？,1000以上の場合,IF(2000以上か？,...
という構文になっているので2000以上でも3000以上でも「1000以上」なので【】内の処理が働いてしまいます。
=IF(AD4+X4*200>4000,4000,IF(AD4+X4*200>3000,3000+(V4+W4+X4-15)*200,IF(AD4+X4*200>2000,2000+(V4+W4+X4-10)*200,IF(AD4+X4*200>1000,1000+(V4+W4+X4-5)*200,AD4+X4*200))))
元の式を生かした形ではこのように順番を変更するとうまくいくかもしれません。
（ふなば） 2023/03/17(金) 09:37:40
=MIN(20,AD4*0.005+X4,SUM(V4:X4))*200
根拠は弱いですがこんな風に書き換えられるかも。
（ふなば） 2023/03/17(金) 19:46:27
A:延長登録をする場合
退室時間が18:01を超えると延長料金が発生します。
その為、事前に下記の⑴〜⑷の申請を行う必要があります。
⑴18:01〜18:30 1,000円
⑵18:31〜19:00 2,000円
⑶19:01〜19:30 3,000円
⑷19:31〜20:00 4,000円
⑴に登録して頂くと、18:30分まで追加料金無しで1カ月利用する事が出来ます。
⑵に登録して頂くと、19:00分まで追加料金無しで1カ月利用する事が出来ます。
⑶に登録して頂くと、19:30分まで追加料金無しで1カ月利用する事が出来ます。
⑷に登録して頂くと、20:00分まで追加料金無しで1カ月利用する事が出来ます。
B:延長登録をしていない場合
延長登録をしていない場合、枠ごとに200円の追加徴収を行います。
⑴、⑵、⑶、⑷をそれぞれ1枠と考えます。
≪例20:00まで利用すると200×4枠=800円≫
C:延長登録をしている方が、その区間を超えた場合
その枠に応じた追加徴収が行われます。
たとえば、⑴で登録されている方が18:45のお迎えに来られる場合、18:00〜18:30までの登録はされている為、18:30〜19:00の1枠分200円の料金が発生する事となります。
D:追加徴収が⑴〜⑷の月額料金に達した場合、それぞれの枠に応じて自動的に月額延長登録に切り替わります。
≪例≫延長登録されていない場合で⑴6枠使用➡6枠×200円=1200円 ➡5回目で上限となる為、1000円
その日時の追加徴収の合計金額によって、自動的に延長登録に切り替わる為、非常にややこしい事となっています。
4月1日 18:01（1枠）200円
4月2日 18:31（2枠）400円 計600円
4月3日 18:31（2枠）400円 計1000円
4月4日 18:01（1枠）200円 計1000円（3日に⑴に自動的に切り替わった為）
4月5日 18:31（2枠）400円 計1200円（⑴の枠が月額に切り替わっている為）
4月6日 19:31（4枠）800円 計1800円（⑴の枠が月額に切り替わっている為）
4月7日 19:31（4枠）800円 計2400円（⑴の枠が月額に切り替わっている為）
4月8日 19:31（4枠）800円 計2800円（⑴⑵の枠が月額に切り替わっている為）
.....4000円が最大上限です。
⑴各利用者様の退室時間をB列〜D列と順番に打ち込み、延長登録の有無を入力します。
[A] | [B] | [C] | [D] |...| [O] | [P]
【氏名】| 【 延 長 退 室 時 間 】 |...|【延長有】|【延長料金】
[1]A様 | 18:01 | 18:31 | 19:31 |...| ⑴ | 計算式A
[2]B様 | 18:01 | 18:31 | 19:31 |...| ⑵ | 計算式B
[3]C様 | 18:01 | 18:31 | 19:31 |...| ⑶ | 計算式C
⑵B列〜D列とO列、追加徴収からの月額に切り替わりを時系列に考えて、Ｐに結果を出します。
大変複雑ですが、お力を貸して頂けると幸いです。
よろしくお願いいたします。
（おび） 2023/03/18(土) 09:02:45
これを4つの時間で計算し合計
それが先日の回答内容です
=200*SUM(IF(延長月額登録の有無,5,MIN(5,COUNTIF($B1:$N1,">18:00"),IF(延長月額登録の有無,5,MIN(5,COUNTIF($B1:$N1,">18:30"),IF(延長月額登録の有無,5,MIN(5,COUNTIF($B1:$N1,">19:00"),IF(延長月額登録の有無,5,MIN(5,COUNTIF($B1:$N1,">19:30"))
表内の延長月額登録の表現内容がわかればもっと具体的にできます。
（火災報知器） 2023/03/18(土) 09:41:29
ありがとうございます。
さっそく頂いた計算式で試してみたのですが、延長の時間が入っていれば固定で1000円が出力されてしまいます。
初歩的な質問なのですが、【MIN(5,COUNTIF($B1:$N1,">18:00")】のダブルクォーテーションマーク内に＞が入っていますが、【>18:00】という文字列をカウントするという意味にはならないのですか？
延長月額登録の参考になれば嬉しいのですが、、、、
⑴Aさん 月額登録無し"18:01 18:01 18:31"3日間延長の場合
200+200+400=800円
⑵Bさん 月額登録無し"18:01 18:01 18:31 18:01 18:01 18:01 18:01"7日間延長の場合
200+200+400+200+200+200+200=1,600 ➡ 1000円
➡4日目に上限1,000円に達していますので、18:00〜18:30月額延長に切り替わる為、後の3日間に追加料金はかかりません。
⑶Cさん 月額登録無し"18:01 18:31 18:01 18:31"4日間延長の場合
200+400+200+400=1,200
4日目の18:00~18:30枠で上限1,000円に達して、自動的に月額延長⑴に切り替わっていますが、18:30~19:00の枠も利用されている為、超えた差分の200円も上乗せされます。
⑷Dさん 月額登録あり（18:00〜18:30）"18:01 18:31 18:01 18:31 19:01"5日間延長の場合
1,000+(0+200+0+200+400)=1,800円
⑸Eさん 月額登録あり（18:00〜18:30）"18:01 18:31 18:31 19:31 19:01"5日間延長の場合
1,000+(0+200+200+600)+(200)=2,200円
18:00~18:30の延長登録を頂いて上で、更に追加徴収が1,000円を超え、4日目の時点で18:30~19:00の延長に切り替わりました。5日目に19:00~19:30の枠を利用されていますので、2,000円＋200円 計2,200円となります。
（おび） 2023/03/21(火) 23:20:48
＞Cさん 月額登録無し"18:01 18:31 18:01 18:31"4日間延長の場合 ＞200+400+200+400=1,200 ＞4日目の18:00~18:30枠で上限1,000円に達して、自動的に月額延長?に切り替わっていますが、 ＞18:30~19:00の枠も利用されている為、超えた差分の200円も上乗せされます。
ちょっと呑み込めないのですが、以下だとどうなりますか？
１．Cさん 月額登録無し "19:31 19:31" 2日間延長 ２．Cさん 月額(1)登録 "18:01 18:31 18:01 18:31" 4日間延長
（半平太） 2023/03/22(水) 08:58:35
月額〜1830〜1900〜1930 合計 月額登録なし........ 0 + 800 + 600 + 200 = 1600 月額登録18:30まで...1000 + 0 + 600 + 200 = 1800 月額登録19:00まで...2000 + 0 + 0 + 200 = 2200
これで合ってますか
（火災報知器） 2023/03/22(水) 15:21:11
4回延長あり 無登録 月額1000 月額2000
18:01……… 200(+200) 1000(+0) 2000(+0) 18:31……… 600(+400) 1200(+200) 2000(+0) 19:01……… 1200(+600) 1600(+400) 2200(+200) 18:31……… 1400(+200)※1 1800(+200) 2200(+0)
※1 前日に1000以上になったため4日目以降は18:30まで無料
前日までの累計額で翌日の加算額が変わるというのは少々厄介ですね
（火災報知器） 2023/03/22(水) 16:08:09
（おび）さんの 2023/03/21(火) 23:20:48 発言を基に下記の表に 落としました。
確認ですが...⑸Eさん 月額登録あり（18:00〜18:30）"18:01 18:31 18:31 19:31 19:01"5日間延長の場合の 計2,200円は、間違いではないですか？ 計2,400円 になりませんか？
⑸Eさん 月額登録ありの場合においてですが
＞18:00~18:30の延長登録を頂いて上で、更に追加徴収が1,000円を超え、4日目の時点で18:30~19:00の延長に切り替わりました。
上記の、4日目の時点で18:30~19:00の延長に切り替わりました。 は、2000円上限に追加徴収金額がなったら切り替わるのではないですか？
⑶Cさんの例で、 4日目の18:00~18:30枠で上限1,000円に達して、自動的に月額延長⑴に切り替わっていますが の表現が正なら、Eさんの切り替わり上限額は、2,000円に追加徴収金額がなったらになりませんか？
|[A] |[B] |[C] |[D] |[E] |[F] |[G]|[H] |[I] |[J]|[K]|[L]|[M]|[N]|[O] |[P]
[1] |2023年3月度 | | 1| 2| 3| 4| 5| 6| 7| 8| 9| 10| 11| 12|延長枠|
[2] |No. |氏名|水 |木 |金 |土 |日 |月 |火 |水 |木 |金 |土 |日 |登録 |請求金額
[3] | 1|A様 |18:01|18:01|18:31|休 |休 | | | | | |休 |休 |無し | 800
[4] | 2|B様 |18:01|18:01|18:31|18:01|休 |18:01|18:01| | | |休 |休 |無し | 1,000
[5] | 3|C様 |18:01|18:31|18:01|18:31|休 | | | | | |休 |休 |無し | 1,200
[6] | 4|D様 |18:01|18:31|18:01|休 |休 |18:31|19:01| | | |休 |休 |(1枠) | 1,800
[7] | 5|E様 |18:01|18:31|18:31|19:31|休 |19:01| | | | |休 |休 |(1枠) | 2,400
■追加徴収日別金額
[10]|2023年3月度 | | 1| 2| 3| 4| 5| 6| 7| 8| 9| 10| 11| 12|延長枠| [11]|No. |氏名|水 |木 |金 |土 |日 |月 |火 |水 |木 |金 |土 |日 |登録 |追加徴収 [12]| 1|A様 | 200| 200| 400|休 |休 | | | | | |休 |休 |無し | 800 [13]| 2|B様 | 200| 200| 400| 200|休 | 0| 0| | | |休 |休 |無し | 1,000 [14]| 3|C様 | 200| 400| 200| 400|休 | | | | | |休 |休 |無し | 1,200 [15]| 4|D様 | 400| 200| 200|休 |休 | 400| 200| | | |休 |休 |(1枠) | 800 [16]| 5|E様 | 0| 200| 200| 600|休 | 400| | | | |休 |休 |(1枠) | 1,400
※Cさんの登録は、上記の表に記載してませんが...無しから(1枠)に切り替わる
（あみな） 2023/03/22(水) 23:57:15
半平太さん
１．Cさん 月額登録無し "19:31 19:31" 2日間延長 ➡800+800で1600円となります。
２．Cさん 月額(1)登録 "18:01 18:31 18:01 18:31" 4日間延長 ➡200+400+200+400で1200円となります。
利用日毎の発生料金に応じて、上限に達します。
その為、１はもし中日に200円の利用があれば、1000円に達し、3日目の800の1枠が月額延長に切り替わる為、600円となります。
その認識で間違いありません。
厄介です。。。
あみなさん
ありがとうございます。
●確認ですが...⑸Eさん 月額登録あり（18:00〜18:30）"18:01 18:31 18:31 19:31 19:01"5日間延長の場合の計2,200円は、間違いではないですか？ 計2,400円 になりませんか？
➡その通りです。失礼しました。
● ⑸Eさん 月額登録ありの場合においてですが
＞18:00~18:30の延長登録を頂いて上で、更に追加徴収が1,000円を超え、4日目の時点で18:30~19:00の延長に切り替わりました。上記の、4日目の時点で18:30~19:00の延長に切り替わりました。 は、2000円上限に追加徴収金額がなったら切り替わるのではないですか？ ➡その通りです。料金に達した場合、自動的に切り替わり、次回からの徴収が免除となります。 今まで800円徴収していた場合、次回200円の延長があれば、1000円に達し、【延長有】に切り替わります。
●⑶Cさんの例で、 4日目の18:00~18:30枠で上限1,000円に達して、自動的に月額延長⑴に切り替わっていますが
の表現が正なら、Eさんの切り替わり上限額は、2,000円に追加徴収金額がなったらになりませんか？ ➡おそらくその認識で間違いないのですが、月額延長有（1枠）の場合、先に1,000円が発生している為、更に追加徴収が1,000円発生し、月額延長分と合わせ、合計2,000円となった場合、自動的に月額延長（2枠）に切り替わります。 （おび） 2023/03/24(金) 15:56:39
4日目の時点で2,000円になっているので5日目以降は19:00まで追加料金なしになる、という仕様ではなかったですか？
その場合5日目の19:01は200円加算で合計2,200円になるのでは。
18:01 18:31 18:31 19:31 19:01
+0 +0 +0 +0 +0 18:01〜
+0 +200 +200 +200 +0 18:31〜
+0 +0 +0 +200 +200 19:01〜
+0 +0 +0 +200 +0 19:31〜
1000 1200 1400 2000 2200 計
※1 1日目から18:01枠加算なし（月額登録） ※2 5日目から18:31枠加算なし（4日目で2000円到達）
（火災報知器） 2023/03/24(金) 18:04:16
＞月額延長有（1枠）の場合、先に1,000円が発生している為、更に追加徴収が1,000円発生し、 ＞月額延長分と合わせ、合計2,000円となった場合、自動的に月額延長（2枠）に切り替わります。 ＞（おび） 2023/03/24(金) 15:56:39 より
合計2,200円ですね。 （あみな） 2023/03/24(金) 18:34:59
Function Sample(Range1 As Range, Str1 As String) As Long
Dim buf As Long
Dim t As Double
Dim i As Long
Select Case Str1
Case "⑴": buf = 1
Case "⑵": buf = 2
Case "⑶": buf = 3
Case "⑷": buf = 4
End Select
buf = buf * 5
For i = 1 To Range1.Cells.Count
t = Range1(i).Value
buf = buf + (t > TimeValue("18:00")) * (buf < 5) _
+ (t > TimeValue("18:30")) * (buf < 10) _
+ (t > TimeValue("19:00")) * (buf < 15) _
+ (t > TimeValue("19:30")) * True
If buf > 19 Then: buf = 20: Exit For
Next
Sample = buf
End Function
ユーザー定義関数でお茶濁し
（火災報知器） 2023/03/24(金) 18:43:42
火災報知器さんと同じ考えに行き着いたんですけど、さらに懸念があって
18:01 18:01 18:31 18:31 の場合と 18:31 18:31 18:01 18:01 の場合とで、料金変わりません？ 前者が 1 + 1 + 2 + 2 = 1,200円 後者が 2 + 2 + 1 + 0 = 1,000円 なんか不公平な気が・・・
数式だとlambda関数でできそうですが、私には難しいのでVBAに逃げ・・・
|[A] |[B] |[C] |[D] |[E] |[F]|[G] |[H] |[I]|[J]|[K]|[L]|[M]|[N]|[O] |[P]
[1]|【氏名】|【延長退室時間】| | | | | | | | | | | | |【延長有】|【延長料金】
[2]|A様 |18:01 |18:01|18:31| | | | | | | | | | | | 800
[3]|B様 |18:01 |18:01|18:31|18:01| |18:01|18:01| | | | | | | | 1,000
[4]|C様 |18:01 |18:31|18:01|18:31| | | | | | | | | | | 1,200
[5]|D様 |18:01 |18:31|18:01| | |18:31|19:01| | | | | | | 1| 1,800
[6]|E様 |18:01 |18:31|18:31|19:31| |19:01| | | | | | | | 1| 2,200
[7]|F様 |18:01 |18:01|18:31|18:31| | | | | | | | | | | 1,200
[8]|G様 |18:31 |18:31|18:01|18:01| | | | | | | | | | | 1,000
Function uf月額料金(rng As Range, c As Long) As Long
Dim s As Variant
Dim t As Long, tm As Date
Dim g(1 To 4) As Long, i1&, i2&, i3&, i4&
Dim tmp As Long
Dim ans As Long
tm = #6:00:00 PM#
'c ＝ 延長区分
'1 = 18:01を無視、2 = 18:31まで無視・・・
For i1 = 1 To c
g(i1) = 5
Next
'左から右に向かって順番に処理
For Each s In Application.Index(rng, 1, 0).Value
If s <> "" Then
'
'30分単位に切上後、30分単位の整数に置き換え
'18:01 → 18:30 → 37単位
t = WorksheetFunction.Ceiling(s, #12:30:00 AM#) * 48
'
'18:00分引く 37-(18:00*48) → 37-36 → 1単位
t = t - tm * 48
'
'すでに5カウントした時間分、差し引く
For i2 = 1 To 4
t = t + (g(i2) = 5) * 1
Next
'
'それでも単位が残ったら、次の区分に加算する
If t > 0 Then
For i3 = 1 To 4
If g(i3) < 5 Then
tmp = 5 - g(i3)
If t < tmp Then
g(i3) = g(i3) + t
t = 0
Else
g(i3) = 5
t = t - tmp
End If
End If
If t <= 0 Then Exit For
Next
End If
End If
Next s
For i4 = 1 To 4
ans = ans + g(i4)
Next i4
uf月額料金 = ans * 200
End Function
（稲葉） 2023/03/24(金) 20:00:39
＞数式だとlambda関数でできそうですが 新関数ですな...365版でしか使用できないようですね。
＞なんか不公平な気が・・・ 確かに変わりますね...う〜ん...。
関数では、回答がなさそうなので…私も、VBAのお勉強を兼ねて ※レイアウトは、オリジナルとさせていただいておりますのでご了承ください。
■準備 C1へ、西暦を入力 ( 2023 )と数値を E1へ、月を入力 今月なら( 3 )と数値を ※月を変更すればカレンダーの曜日を自動で変更させます。
C6=DATE($C$1,$E$1,COLUMN()-2) C7=DATE($C$1,$E$1,COLUMN()-2) 31日分右へコピー
・書式設定
6行目の日付を、ユーザー定義で 「d」とする 7行目の曜日を、ユーザー定義で 「aaa」とする 時間の範囲は、ユーザー定義で「h:mm」とする ※「」は不要
|[A] |[B] |[C] |[D] |[E] |[F] |[G]
[1] |延長料金算出表| | 2023|年 | 3|月度 |
[2] | | |月初日| |2023年3月1日 | |
[3] | | |月末日| |2023年3月31日| |
[4]
[5]
[6] |令和5年3月 | | 1| 2| 3| 4| 5
[7] |No. |氏名|水 |木 |金 |土 |日
[8] | 1|A様 |18:01 |18:01|18:31 |休 |休
上の行数を取ったのは、下記を入れる為です。飾りですけど…
|[AB] |[AC] |[AD]|[AE] |[AF]|[AG]
[1]|(1枠)|1,000円| |18:01|〜 |18:30
[2]|(2枠)|2,000円| |18:31|〜 |19:00
[3]|(3枠)|3,000円| |19:01|〜 |19:30
[4]|(4枠)|4,000円| |19:31|〜 |20:00
・B列に名前を空白行無しで入力 ・AL列に、枠を入力します、()付きで指定のみ有効
|[AI] |[AJ] |[AK]|[AL] |[AM]
[6] |延長枠| | |月額延長登録|
[7] |切替 |追加徴収| |月初 |請求金額
[8] | | | |(0枠) |
※丸括弧(まるかっこ)の種類に注意 （1枠）←これNG (1枠)←これOK
準備終了
■注意
※20人用に設定してあります。使用する場合は、人数に合わせて変更方 ※月額延長登録に切り替わると、フォントは(赤色)と、背景色(黄系色)に装飾されます マクロは、Stop で一時停止するので状態を確認方...後で取っ払って使用する
■完成レイアウト
|[A] |[B] |[C] |[D] |[E] |[F] |[G]|[H] |[I] |[J] |[K] |[L] |[M]|[N]|[O]|[P]|[Q]|[R]|[S]|[T]|[U]|[V]|[W]|[X]|[Y]|[Z]|[AA]|[AB] |[AC] |[AD]|[AE] |[AF]|[AG] |[AH]|[AI] |[AJ] |[AK]|[AL] |[AM]
[1] |延長料金算出表| | 2023|年 | 3|月度 | | | | | | | | | | | | | | | | | | | | | |(1枠)|1,000円| |18:01|〜 |18:30|
[2] | | |月初日| |2023年3月1日 | | | | | | | | | | | | | | | | | | | | | | |(2枠)|2,000円| |18:31|〜 |19:00|
[3] | | |月末日| |2023年3月31日| | | | | | | | | | | | | | | | | | | | | | |(3枠)|3,000円| |19:01|〜 |19:30|
[4] | | | | | | | | | | | | | | | | | | | | | | | | | | | |(4枠)|4,000円| |19:31|〜 |20:00|
[5]
[6] |令和5年3月 | | 1| 2| 3| 4| 5| 6| 7| 8| 9| 10| 11| 12| 13| 14| 15| 16| 17| 18| 19| 20| 21| 22| 23| 24| 25| 26| 27| 28| 29| 30| 31| |延長枠| | |月額延長登録|
[7] |No. |氏名|水 |木 |金 |土 |日 |月 |火 |水 |木 |金 |土 |日 |月 |火 |水 |木 |金 |土 |日 |月 |火 |水 |木 |金 |土 |日 |月 |火 |水 |木 |金 | |切替 |追加徴収| |月初 |請求金額
[8] | 1|A様 |18:01 |18:01|18:31 |休 |休 | | | | | |休 |休 | | | | | | | | | | | | | | | | | | | | |(0枠) |800円 | |(0枠) |800円
[9] | 2|B様 |18:01 |18:01|18:31 |18:01|休 |18:01|18:01| | | |休 |休 | | | | | | | | | | | | | | | | | | | | |(1枠) |1,000円 | |(0枠) |1,000円
[10]| 3|C様 |18:01 |18:31|18:01 |18:31|休 | | | | | |休 |休 | | | | | | | | | | | | | | | | | | | | |(1枠) |1,200円 | |(0枠) |1,200円
[11]| 4|D様 |18:01 |18:31|18:01 |休 |休 |18:31|19:01| | | |休 |休 | | | | | | | | | | | | | | | | | | | | |(1枠) |800円 | |(1枠) |1,800円
[12]| 5|E様 |18:01 |18:31|18:31 |19:31|休 |19:01| | | | |休 |休 | | | | | | | | | | | | | | | | | | | | |(2枠) |1,200円 | |(1枠) |2,200円
[13]| 6|F様 |18:01 |18:31|18:31 |19:31|休 |18:31|18:31|19:31|19:15|19:31|休 |休 | | | | | | | | | | | | | | | | | | | | |(3枠) |1,200円 | |(2枠) |3,200円
[14]| 7|G様 |19:38 |19:15|19:45 |17:55|休 |19:15|19:50|19:21|19:31| |休 |休 | | | | | | | | | | | | | | | | | | | | |(3枠) |800円 | |(3枠) |3,800円
Option Explicit Sub AdditionalCharge_Calculation()
Dim ws As Worksheet, r As Range
Dim TargetDate As String, sDate As String, OutDate As String, div As String
Dim i&, q&, x&, n&, k&, v&, EndOf_Month As Long, additional_charge As Long
Dim total(1 To 4) As Long, price(1 To 4) As Long
Set ws = Sheets(1)
With ws
x = Application.CountIf(.Range("B8", "B27"), "<>")
TargetDate = .Range("C1") & "/" & .Range("E1")
sDate = TargetDate & "/01"
OutDate = DateSerial(Year(sDate), Month(sDate) + 1, Day(sDate))
EndOf_Month = Day(DateSerial(Year(OutDate), Month(OutDate), Day(OutDate) - 1))
Union(.Range("AJ8:AJ" & x + 7), .Range("AM8:AM" & x + 7)).ClearContents
For Each r In .Range("AI8:AI32")
If r.Interior.Color = rgbLemonChiffon Then _
r.ClearContents: r.Interior.Color = xlNone
Next
.UsedRange.Font.Color = vbBlack
.Range("AI8:AI32").Value = .Range("AL8:AL32").Value
Stop '' 一時停止
Application.ScreenUpdating = False
For i = 0 To x - 1
total(1) = 0: total(2) = 0: total(3) = 0: total(4) = 0
For q = 3 To 3 + EndOf_Month
div = .Cells(8, "AI").Offset(i)
If IsDate(Format(.[C8].Offset(i, q - 3), "h:mm")) = True Then
If Format(.[C8].Offset(i, q - 3), "h:nn") >= "18:01" And _
Format(.[C8].Offset(i, q - 3), "h:nn") <= "18:30" Then
price(1) = additional_collection(div)
total(1) = total(1) + price(1)
ElseIf Format(.[C8].Offset(i, q - 3), "hh:nn") >= "18:31" And _
Format(.[C8].Offset(i, q - 3), "h:nn") <= "19:00" Then
price(2) = additional_collection(div & "i")
total(2) = total(2) + price(2)
ElseIf Format(.[C8].Offset(i, q - 3), "h:nn") >= "19:01" And _
Format(.[C8].Offset(i, q - 3), "h:nn") <= "19:30" Then
price(3) = additional_collection(div & "ii")
total(3) = total(3) + price(3)
ElseIf Format(.[C8].Offset(i, q - 3), "h:nn") >= "19:31" And _
Format(.[C8].Offset(i, q - 3), "h:nn") <= "20:00" Then
price(4) = additional_collection(div & "iii")
total(4) = total(4) + price(4)
End If
End If
additional_charge = total(1) + total(2) + total(3) + total(4)
With .Cells(8, "AI").Offset(i)
.HorizontalAlignment = xlCenter
Select Case True
Case additional_charge >= 10 And ws.[AL8].Offset(i) = "(0枠)"
.Value = "(1枠)": .Font.Color = rgbRed: .Interior.Color = rgbLemonChiffon
Case additional_charge >= 10 And ws.[AL8].Offset(i) = "(1枠)"
.Value = "(2枠)": .Font.Color = rgbRed: .Interior.Color = rgbLemonChiffon
Case additional_charge >= 10 And ws.[AL8].Offset(i) = "(2枠)"
.Value = "(3枠)": .Font.Color = rgbRed: .Interior.Color = rgbLemonChiffon
Case additional_charge >= 10 And ws.[AL8].Offset(i) = "(3枠)"
.Value = "(4枠)": .Font.Color = rgbRed: .Interior.Color = rgbLemonChiffon
End Select
Select Case True
Case additional_charge >= 20
.Value = "(2枠)": .Font.Color = rgbRed: .Interior.Color = rgbLemonChiffon
Case additional_charge >= 30
.Value = "(3枠)": .Font.Color = rgbRed: .Interior.Color = rgbLemonChiffon
Case additional_charge >= 40
.Value = "(4枠)": .Font.Color = rgbRed: .Interior.Color = rgbLemonChiffon
End Select
End With
With .Cells(8, "AJ").Offset(i)
.Value = additional_charge * 100
.NumberFormatLocal = "#,###円"
End With
n = monthly_fee(ws.Cells(8, "AL").Offset(i))
With .Cells(8, "AM").Offset(i)
.Value = additional_charge * 100 + n
.NumberFormatLocal = "#,###円"
End With
Next q
Next i
End With
Application.ScreenUpdating = True
End Sub
Function monthly_fee(charge As String) '' 月額料金
monthly_fee = Switch( _
charge = "(0枠)", 0, charge = "(1枠)", 1000, charge = "(2枠)", "2000", _
charge = "(3枠)", 3000, charge = "(4枠)", "4000")
End Function
Function additional_collection(p As String) '' 追徴料金
additional_collection = Switch( _
p = "(0枠)", 2, p = "(0枠)i", 4, p = "(0枠)ii", 6, p = "(0枠)iii", 8, _
p = "(1枠)", 0, p = "(1枠)i", 2, p = "(1枠)ii", 4, p = "(1枠)iii", 6, _
p = "(2枠)", 0, p = "(2枠)i", 0, p = "(2枠)ii", 2, p = "(2枠)iii", 4, _
p = "(3枠)", 0, p = "(3枠)i", 0, p = "(3枠)ii", 0, p = "(3枠)iii", 2, _
p = "(4枠)", 0, p = "(4枠)i", 0, p = "(4枠)ii", 0, p = "(4枠)iii", 0)
End Function
※ちゃんと動作しなかったら、ごめんなさい *_ _))ﾍﾟｺ
（あみな） 2023/03/27(月) 00:28:11
月額料金の契約有無を反映させると、もっと不公平かも・・・ HさんとIさんは同じだけ使ってるのに、月額契約しているIさんのほうが支払い金額が多い JさんとKさんも同様。
そう考えると、その枠を使った回数をカウントしたほうがどちらを契約しようがしまいが関係なくなるので 不公平ではなくなると思う。 月額を優先したいなら、月額料金を下げるか、延長料金を上げるか。 R2=IF($O2>=COLUMNS($R$1:R$1),5,MIN(5,COUNTIF($B2:$N2,">="&R$1))) →と↓にコピー Q2=SUM(R2:U2)*200
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U]
[1] |【氏名】|【延長退室時間】| | | | | | | | | | | | |【延長有】|指定ルール|カウントのみ|18:01|18:31|19:01|19:31
[2] |A様 |18:01 |18:01|18:31| | | | | | | | | | | 1| 1,200| 1,200| 5| 1| 0| 0
[3] |B様 |18:01 |18:01|18:31|18:01| |18:01|18:01| | | | | | | | 1,000| 1,200| 5| 1| 0| 0
[4] |C様 |18:01 |18:31|18:01|18:31| | | | | | | | | | | 1,200| 1,200| 4| 2| 0| 0
[5] |D様 |18:01 |18:31|18:01| | |18:31|19:01| | | | | | | 1| 1,800| 1,800| 5| 3| 1| 0
[6] |E様 |18:01 |18:31|18:31|19:31| |19:01| | | | | | | | 1| 2,200| 2,400| 5| 4| 2| 1
[7] |F様 |18:01 |18:01|18:31|18:31| | | | | | | | | | | 1,200| 1,200| 4| 2| 0| 0
[8] |G様 |18:31 |18:31|18:01|18:01| | | | | | | | | | | 1,000| 1,200| 4| 2| 0| 0
[9] |H様 |18:31 |18:31|18:01|18:01|18:01|18:01|18:01|18:01|18:01|18:01|18:01|18:01|18:01| | 1,000| 1,400| 5| 2| 0| 0
[10]|I様 |18:31 |18:31|18:01|18:01|18:01|18:01|18:01|18:01|18:01|18:01|18:01|18:01|18:01| 1| 1,400| 1,400| 5| 2| 0| 0
[11]|J様 |19:31 |19:31|18:31|18:31|18:31|18:31|18:31|18:31| | | | | | | 2,000| 2,800| 5| 5| 2| 2
[12]|K様 |19:31 |19:31|18:31|18:31|18:31|18:31|18:31|18:31| | | | | | 2| 2,800| 2,800| 5| 5| 2| 2
（稲葉） 2023/03/27(月) 08:48:56
下記のマクロを一部修正しました。 ＞（あみな） 2023/03/27(月) 00:28:11
で…修正したのは置いといて、なになに月額登録した方が 高くなるだって…なるほど、あっ本当ですね。
|[AH]|[AI] |[AJ] |[AK]|[AL] |[AM]
[6] | |延長枠| | |月額延長登録|
[7] |氏名|切替 |追加徴収| |月初 |請求金額
[8] |H様 |(1枠) |1,000円 | |(0枠) |1,000円
[9] |I様 |(1枠) |400円 | |(1枠) |1,400円
[10]|J様 |(2枠) |2,000円 | |(0枠) |2,000円
[11]|K様 |(2枠) |800円 | |(2枠) |2,800円
※稲葉さんと同じ時間条件でしてみました。
う〜ん、これは、既に運営をしているとしても 利用者で気が付いている方はいないかもですね。
（あみな） 2023/03/27(月) 16:27:24
確かに不公平は生まれてしまいますね・・
基本的に、延長される場合は月額登録が大前提となります。
極端に5回、6回と単発での延長をされる方に関しては、今後、月額登録への変更を案内させて頂いております。
登録されていないが、渋滞や緊急の事態で、お迎えが遅くなったご家庭への措置としてのシステムとなっております。
実はマクロは触ったことが無いので、皆様から頂いた知恵を参考に、この機会に触ってみようと思います。
ありがとうございました。
（おび） 2023/03/29(水) 23:26:16
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.