エクセルでの延長表計算式を教えてください

認可外保育園での延長保育の保育料の計算に関しお力をお貸しください。エクセルで作成したいのですが、かなり複雑なため苦労しております。
わかる方がいらっしゃいましたら計算式の簡単な説明を教えてください。

基本保育時間9:20~13:50 ←換算せず

延長保育時間13:50～
13:50～18:00 550円/30分
18:00以降 550円/15分
9:00～9:20 550円/30分
8:00～9:00 550円/15分

登園時間と降園時間の入力だけで、その日はいくらの延長保育料が発生した、と分かるように作れると助かります。

また、その式の簡単な説明を付けていただけければ応用して展開していこうと思いますのでよろしくお願いいたします。

質問者からの補足コメント

• ご質問ありがとうございます。詳細が足りず申し訳ございませんでした。
  1分～30分単位で550円が加算されます。
  14:00までの場合は550円
  
  17:50までだと
  13:50～17:50　550円×8
  合計4400円の延長料金
  
  18:00の場合は上記プラス1550円となり合計が4950円となります。
  
  9:00～9:20 550円/30分は、30分ありませんが550円。朝の時間だけは保育園の開始時間が中途半端な時間となりますので、9:20から逆に計算しています。例えば8:55～9:20だと550円。（9時前の時間は5分が9時以降の10分と同じ料金となるため）
  
  8:10に登園した場合は
  8:55～9:20　550円
  8:40～8:55　550円
  8:25～8:40　550円
  8:10～8:25　550円
  合計2200円となります。
  
  よろしくお願いいたします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/12/21 13:05

No.5 回答者： fujillin 回答日時： 2022/12/21 16:49

No2です。

料金の計算方法が不明のままなので、No2の後半で紹介しておいた「計算方法が整理できない」場合でも使える方法の例を以下に簡単に示しておきます。
こちらの方法であれば、料金体系を説明できなくても、質問者様が結果を求めることができればどのような体系であろうとも対応が可能です。


以下は、要領の例示なので、早い時間帯の延長のみの計算にしてあります。
遅い方の延長も同様に計算して加算すれば、全体の延長料金を求めることができるでしょう。

添付図では、
・B列に登園時刻、C列に退園時刻が入力されているとします
・D列に延長料金を求めるものとします。
　（添付図では、基本時間前の部分だけを計算してあります）
・事前準備として、G,H列のような料金表を作成しておきます
　（添付図では同じシートにしてありますが、別シートに作成しておく方が良いかも）

D2セルには、=VLOOKUP(B2,G$2:H$8,2,1) の式を入力し下方にフィルコピーしてあります。
同様に午後の延長分を求める表と式を作成し
　=VLOOKUP(~~) + VLOOKUP(~~)
という式にしておくことで、全体の延長料金が求められることはお分かりと思います。


※ 料金表の作成方法
例えば、G8、H8の「8:35　1100」は、「登園時刻が8:35以降で（次の行の）8:50より前であれば1100円」という意味です。
添付図は、仮として15分おきに作成してありますが、実態に合わせて「料金が変わる時刻とその料金」を示す表にしておけば充分です。
この方式であれば、料金体系がイレギュラーであろうとも、実際の登園時刻で表を検索すればそのまま料金を求められるので、仕組みとしては簡単になると思います。

※ 添付図7行目の「7:50登園」のデータでは、D列が#N/Aとなっていますが、これは料金表に該当する時刻データがないからです。
もしも、このような可能性も起こり得るのであれば、
・エラー表示のままとして別計算として扱う
・表をもっと早い時刻まで延長して作成しておく
などが考えられます。

※ 後半延長に関しても要領は同じです。
また、予め式を入力しておくような場合には、時刻入力がないとエラー表示になりますので、「時刻入力がされている」ことも条件式として追加しておく方が利用しやすく、見た目もわかりやすくなることでしょう。

この回答へのお礼

丁寧にご説明いただきありがとうございました。
勉強になりました。

お礼日時：2022/12/21 23:15

No.4 回答者： chonami 回答日時： 2022/12/21 15:00

降園時の延長については例えば項目名をA1に「登園」・B1に「降園」、E1に基準時間として「13:50」・F1に「18:00」・G1に「18時超」と入力し2行目からA2とB2に時刻を入れるとそれぞれの時間帯の延長料金が出る式として

F2=CEILING(IF($B2>=$F$1,$F$1-$E$1,$B2-$E$1),"0:30")/"0:30"*550
G2=CEILING(IF($B2<=$F$1,0,$B2-$F$1),"0:15")/"0:15"*550

で計算できるかと思います。（ちょっと冗長ですが）
ただ、朝の超過料金については既回答にもありますが

>9:00～9:20 550円/30分
>8:00～9:00 550円/15分

この部分の区切りがイマイチわかりません。
9:10登園、8:50登園だとそれぞれ料金はどうなるのでしょうか？

No.3 回答者： 銀鱗 回答日時： 2022/12/21 14:36

この手のものは、決まった時間の中に何時間あったのかを別々に求め、それを足すという形で良いでしょう。

例えば、
　8:10～19:00　
なら、
　　8:00～　9:00　50分　　 4単位（550円×4＝2200円）
　　9:00～　9:20　20分　　 1単位（550円×1＝550円）
　　9:20～ 13:20　240分　　定額
　 13:20～ 18:00　280分　10単位（550円×10＝5500円）
　 18:00～ 22:00　60分 　　4単位（550円×4＝2200円）
こうなるのが一般的なんですが、

＞例えば8:55～9:20だと550円。（9時前の時間は5分が9時以降の10分と同じ料金となるため）

となると、
　　8:00～　9:00　50分×2(100分)
　　9:00～　9:20　20分　　
　　9:20～ 13:20　
　 13:20～ 18:00　280分
　 18:00～ 22:00　60分×2(120分)
として、
　(100分+20分+280分+120分)÷30分 × 550円＝9900円
のような計算になります。（30分で割ってるところは端数切り上げになる）
こんな考え方でよろしいでしょうか？

・・・

　登園時間をA1セル
　退園時間をB1セル
に入力したとすると、
8:00～9:00は、
　=MIN(B1,"9:00")-MIN(A1,"9:00")
が、基本の計算式になります。
さらに時間が8:00以前ではないことを考慮して
　=MIN(MAX(B1,"8:00"),"9:00")-MIN(MAX(A1,"8:00"),"9:00")
のようになります。
9:00～9:20の場合は、
　=MIN(MAX(B1,"9:00"),"9:20")-MIN(MAX(A1,"9:00"),"9:20")
のようになります。
これを求めたい時間帯ごとに作る。
でもって、その合計を求める。
それを1440倍（24時間は1440分）してシリアル値を分単位の数値に置き換える。
（TEXT関数で ”分” 表示にしてからVALUE関数で数値に置き換えても良い）
さらに30で割って小数点以下の値を切り上げる。
30分で1単位という数値が求まるので、あとは単価の550円に掛ければいい。

すると、添付した画像のようになる。
数式をまとめてしまってもいいのですが、何か間違えていたり、修正が発生したときに分かりにくいでしょう。
バラバラにした状態で計算することを勧めます。

この回答へのお礼

丁寧にご説明いただきありがとうございました。
勉強になりました。

お礼日時：2022/12/21 23:16

No.2 回答者： fujillin 回答日時： 2022/12/21 14:33

こんにちは

＞9:00～9:20 550円/30分
＞8:00～9:00 550円/15分
時間帯によって単位時間が変わっていますが、例えば8:00～9:00であれば、1～15分であれば繰り上げて1単位と見做し550円と換算するものと解釈してよいのなら、計算で換算することは可能と思います。

一方で、ご例示の
＞8:10に登園した場合は
　・・・・
＞合計2200円となります

を上記の方法で計算すると、
8:00～9:00の滞在時間50分
　→ 50分/15分＝3.33 → 4単位×550＝2200円
9:00～9:20の滞在時間20分
　→ 20分/30分＝0.66 → 1単位×550＝550円
合計 2750円
とするのであればわかりますが、ご例示では8:55~9:00までを無視して計算しているようです。

8:50登園の場合は、8:50～9:00までは無視して550円ということでしょうか？
（もしそうなら、9:00～9:20の時間区切りは8:50～9:20なのでは？）
このあたりの計算方法をきちんと整理できるのであれば可能と思いますが、どういう理屈になっているのか私には理解できないので、わかりかねます。


仮に計算方法を整理できない場合であっても、想像するところ9:20~13:50を外した利用（＝時間外のみの利用）は考えなくてもよさそうなので、登園時刻8:00～9:20迄、退園時刻13:50～23:59迄の15分刻みでの延長保育料一覧表を作成しておけば、これをLookup系の関数で検索することで計算できるようになるものと思います。
（実際には、15分刻みでなくても、料金の変わる時刻で作成しておけば足りますが・・）

この回答へのお礼

丁寧にご説明いただきありがとうございました。
勉強になりました。

お礼日時：2022/12/21 23:16

No.1 回答者： chonami 回答日時： 2022/12/21 12:35

ちょっとわからないところがあるので、できればこの時間までならこういう計算でいくらになるとかの具体例をいくつか出してもらいたいですね。

30分550円などは例えば14:00まででも550円かかるのですか？
17:50までと18:00まででは金額が変わるのですか？
9:00～9:20 550円/30分は、30分ないですけど550円なんですか？
8:10に登園したらどのような計算でいくらになるんですか？