![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
認可外保育園での延長保育の保育料の計算に関しお力をお貸しください。エクセルで作成したいのですが、かなり複雑なため苦労しております。
わかる方がいらっしゃいましたら計算式の簡単な説明を教えてください。
基本保育時間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となっていますが、これは料金表に該当する時刻データがないからです。
もしも、このような可能性も起こり得るのであれば、
・エラー表示のままとして別計算として扱う
・表をもっと早い時刻まで延長して作成しておく
などが考えられます。
※ 後半延長に関しても要領は同じです。
また、予め式を入力しておくような場合には、時刻入力がないとエラー表示になりますので、「時刻入力がされている」ことも条件式として追加しておく方が利用しやすく、見た目もわかりやすくなることでしょう。
![「エクセルでの延長表計算式を教えてください」の回答画像5](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/1/587726_63a2ba0b26380/M.png)
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円に掛ければいい。
すると、添付した画像のようになる。
数式をまとめてしまってもいいのですが、何か間違えていたり、修正が発生したときに分かりにくいでしょう。
バラバラにした状態で計算することを勧めます。
![「エクセルでの延長表計算式を教えてください」の回答画像3](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/b/542206607_63a29b6173d53/M.png)
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で質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
新宿 託児所 一時保育できる...
-
預かり保育とは一日預かる感じ...
-
幼稚園は2年保育と3年保育と...
-
託児室ありませんか?
-
保育料がなぜ安くなったか分か...
-
派遣社員で働いています。 保育...
-
育児休暇中の疑問です
-
去年の6月から10時-17時までの...
-
4月から転園しましたが不信感が...
-
3歳になったばかりの息子 保育...
-
保育園でアザを作って帰ってき...
-
体調不良で休職している旨、保...
-
『自営業の手伝い』では認めら...
-
就労証明書の偽造について。 保...
-
保育園 源泉徴収票
-
就労証明書は偽造してもバレな...
-
4月入園辞退しても育児休業給付...
-
保育園で働いているものです。 ...
-
育休延長中の引越しについて 子...
-
現在育休中で 上の子が保育園2...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルでの延長表計算式を教...
-
福山市の保育料引き落とし日
-
保育者の意味を知りたい
-
新宿 託児所 一時保育できる...
-
過去の保育料の返還
-
0〜2歳児クラスの保育園児の親...
-
認可保育所の保育料の仕組みに...
-
保育料について
-
大学入試こAOが近々あります そ...
-
今後介護や保育の人材は どれく...
-
保育料がなぜ安くなったか分か...
-
育児休暇中の疑問です
-
派遣社員で働いています。 保育...
-
体調不良で休職している旨、保...
-
保育園で働いているものです。 ...
-
去年の6月から10時-17時までの...
-
就労証明書の偽造について。 保...
-
5歳の息子が毎朝の登園で泣くよ...
-
4月から転園しましたが不信感が...
-
育休延長中の引越しについて 子...
おすすめ情報
ご質問ありがとうございます。詳細が足りず申し訳ございませんでした。
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円となります。
よろしくお願いいたします。