
認可外保育園での延長保育の保育料の計算に関しお力をお貸しください。エクセルで作成したいのですが、かなり複雑なため苦労しております。
わかる方がいらっしゃいましたら計算式の簡単な説明を教えてください。
基本保育時間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分
登園時間と降園時間の入力だけで、その日はいくらの延長保育料が発生した、と分かるように作れると助かります。
また、その式の簡単な説明を付けていただけければ応用して展開していこうと思いますのでよろしくお願いいたします。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
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となっていますが、これは料金表に該当する時刻データがないからです。
もしも、このような可能性も起こり得るのであれば、
・エラー表示のままとして別計算として扱う
・表をもっと早い時刻まで延長して作成しておく
などが考えられます。
※ 後半延長に関しても要領は同じです。
また、予め式を入力しておくような場合には、時刻入力がないとエラー表示になりますので、「時刻入力がされている」ことも条件式として追加しておく方が利用しやすく、見た目もわかりやすくなることでしょう。

No.4
- 回答日時:
降園時の延長については例えば項目名を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
- 回答日時:
この手のものは、決まった時間の中に何時間あったのかを別々に求め、それを足すという形で良いでしょう。
例えば、
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円に掛ければいい。
すると、添付した画像のようになる。
数式をまとめてしまってもいいのですが、何か間違えていたり、修正が発生したときに分かりにくいでしょう。
バラバラにした状態で計算することを勧めます。

No.2
- 回答日時:
こんにちは
>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分刻みでなくても、料金の変わる時刻で作成しておけば足りますが・・)
No.1
- 回答日時:
ちょっとわからないところがあるので、できればこの時間までならこういう計算でいくらになるとかの具体例をいくつか出してもらいたいですね。
30分550円などは例えば14:00まででも550円かかるのですか?
17:50までと18:00まででは金額が変わるのですか?
9:00~9:20 550円/30分は、30分ないですけど550円なんですか?
8:10に登園したらどのような計算でいくらになるんですか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 幼稚園・保育所・保育園 認可保育園ではならし保育期間中に、一時保育やファミリーサポートを利用して仕事開始することは可能ですか 2 2022/04/04 17:46
- その他(社会・学校・職場) 時短勤務に制限があるのは普通ですか? 1 2023/02/11 12:03
- その他(妊娠・出産・子育て) 産休育休の職場復帰について 現在産休中正社員です。9月出産予定です。職場復帰の時期に悩んでいます。 1 2023/08/17 09:51
- 幼稚園・保育所・保育園 育休延長中の引越しについて 子供が1歳の時点で保育園に入園できず育休延長中です。(今は11ヶ月)今回 2 2022/09/27 15:54
- アルバイト・パート 22年10月から社会保険適用拡大について、詳しい方いらっしゃいましたら教えてください 2 2022/09/01 20:40
- その他(家族・家庭) 共働きの家事分担についてお聞きしたいです。 家族構成 私、妻、7歳長女、1歳長男 共働きで収入は同じ 6 2022/05/08 10:35
- 会社・職場 転職について 1 2023/06/13 20:36
- Excel(エクセル) エクセルの早退時間を計算したいです。計算はうまくいっているようですが… 1 2022/12/26 16:22
- 会社・職場 ベビシッターをしています。 私の感覚は合ってますよね? いつも定期で16:00-20:00週4対応し 4 2023/06/10 03:45
- 幼稚園・保育所・保育園 保育園料についてわかる方いますか? 実家の両親と同居して1人息子を育てるシングルマザーです。 来年度 3 2023/06/25 14:21
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの延長表計算式を教...
-
保育園の入園の手続きで記入を...
-
派遣社員で働いています。 保育...
-
転園したばかりだけど、また元...
-
保育園で3年間も担任が同じは...
-
保育園の先生に聞きたい ネット...
-
保育士さんたちに差し入れをす...
-
保育園の年少~年長、連絡帳あ...
-
年少の娘の担任が男の先生
-
祖父母と同居ですが、保育園入...
-
体調不良で休職している旨、保...
-
母子家庭で私立の保育園につい...
-
保育園のクラス懇談会って必ず...
-
3歳で保育園を転園。慣れるまで...
-
4歳児にお昼寝時に机に突っ伏...
-
『自営業の手伝い』では認めら...
-
保育園で働いているものです。 ...
-
両親が仕事のり理由で、ならし...
-
先生と保護者が友人。
-
保育園の先生に子供が目を合わ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルでの延長表計算式を教...
-
あなたの職業は何ですか?
-
福山市の保育料引き落とし日
-
シングルマザーの保育料
-
0〜2歳児クラスの保育園児の親...
-
保育園の先生たちへのプレゼン...
-
派遣社員で働いています。 保育...
-
保育士さんたちに差し入れをす...
-
保育園で3年間も担任が同じは...
-
保育園の入園の手続きで記入を...
-
保育園に通っている年長の息子...
-
保育園の先生に、一言言ってし...
-
体調不良で休職している旨、保...
-
就労証明書は偽造してもバレな...
-
保育園の先生に聞きたい ネット...
-
保育園へのお礼
-
4月から転園しましたが不信感が...
-
去年の6月から10時-17時までの...
-
保育園からの電話は職場が規則?
-
育休延長中の引越しについて 子...
おすすめ情報
ご質問ありがとうございます。詳細が足りず申し訳ございませんでした。
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円となります。
よろしくお願いいたします。