延長保育料の請求を突合する手順 — Claude for Excelで降園時刻から再計算
登降園時刻のCSVから延長保育の区分と日額を計算し直し、月額上限まで反映して請求書の金額と並べる手順です。30分ちょうどの境界と上限の掛け忘れを、行ごとに洗い出します。
延長保育料の突合は、降園時刻のCSVから延長した分数を出し、区分に切り上げて日額を計算し、月額上限をかけた合計を請求書の金額と並べる作業です。Claude for Excelなら、料金の条件を専用シートに切り出したうえで再計算の式を組ませ、金額がずれた園児だけを取り出せます。園の事務担当や運営法人の経理が、保育管理システムから出力したCSVを使って月次の請求を点検する場面を想定しています。
ここでは、ずれが生まれる3つの箇所、条件シートの作り方、時刻から金額までの式、サンプルで見る差異、Claudeへの依頼文、使えない場面を順に示します。同じ型の段階料率の検算は営業コミッションの検算に、日数の数え方が焦点になる請求はデマレージ請求の突合にあります。
金額がずれるのは、境界・上限・時刻の記録の3か所
延長保育料の差異は、掛け算の間違いよりも、区切り方の違いから生まれます。請求書には金額しか載らないので、どの区分に入れたのかは金額から逆算するしかありません。
差異が出やすい3つの箇所
区分の境界
18:30ちょうどの降園を1区分に入れるか、2区分に入れるか。「30分以内を1区分」なのか「30分に達したら次の区分」なのかで、境界の時刻の扱いが変わります。
上限の適用
日額の上限と月額の上限は、別々に決められていることがあります。日額上限を掛けたあとの合計に、さらに月額上限を掛ける順番になっているかが論点です。
時刻の記録
タブレットの打刻、職員の手入力、保護者アプリの記録のどれを正とするかで、同じ降園でも数分ずれます。区分の境界に近い時刻ほど、数分で金額が変わります。
料金体系は、園や自治体ごとに異なります。基本の保育時間、延長の開始時刻、区分の刻み、単価、上限の有無は、重要事項説明書や利用案内など、園が保護者に示している書面の記載が基準です。以降の数値はすべて、手順を示すための仮の条件です。
料金の条件を専用シートに切り出す
最初に、料率と区切り方を請求明細から分離します。条件がセルにあれば、境界の扱いを変えたときの結果をその場で比べられます。
| セル | 項目 | 仮の値 |
|---|---|---|
| B2 | 項目延長の開始時刻 | 仮の値18:00 |
| B3 | 項目1区分の長さ(分) | 仮の値30 |
| B4 | 項目1区分の料金(円) | 仮の値200 |
| B5 | 項目日額の上限(円) | 仮の値800 |
| B6 | 項目月額の上限(円) | 仮の値6,000 |
シート名は「条件」「請求」「月次」の3つにそろえます。依頼文でシート名を指すだけで済み、指示が短くなります。
条件シートの値は、書面から人が埋めます。Claudeに利用案内の文面を貼って項目を抜き出させる場合も、出てきた値は書面の該当箇所と1つずつ照らし合わせます。ここを読み違えると、以降の再計算がすべて同じ向きにずれます。
利用時間CSVを「請求」シートに取り込む
保育管理システムから出したCSVは、列を絞って貼ります。園児の氏名は取り込まず、園児IDだけにします。突合に必要なのは誰かを区別することであって、名前ではないからです。
| 列 | 内容 |
|---|---|
| A | 内容園児ID |
| B | 内容日付 |
| C | 内容降園時刻 |
| D | 内容請求書の延長料(日額、円) |
| E | 内容延長した分数(再計算) |
| F | 内容区分数(再計算) |
| G | 内容再計算した日額 |
| H | 内容差額(D − G) |
A〜D列は値だけを貼ります。取り込んだ時刻が左寄せの文字列になっているときは、数値の時刻に変えてから式を入れます。文字列のままでは、引き算の結果がエラーになるためです。変換もClaudeに「C列を時刻の値に直して」と頼めます。
時刻から金額までの再計算式
E2: =MAX(0, ROUND((C2-条件!$B$2)*1440, 0))
F2: =ROUNDUP(E2/条件!$B$3, 0)
G2: =MIN(F2*条件!$B$4, 条件!$B$5)
H2: =D2-G2E列は、降園時刻から開始時刻を引いた値に1440(1日の分数)を掛けて分に直します。ROUNDで整数にしているのは、時刻の引き算が浮動小数点の誤差を含むためです。ROUNDがないと、18:30ちょうどの引き算が30.0000000001分になり、ROUNDUPで2区分に入ることがあります。境界の降園で金額がずれる原因を、式の側で作らないための処理です。
F列は、分数を区分の長さで割って切り上げます。1分でも延長すれば1区分、30分ちょうどなら1区分、31分なら2区分です。「30分に達したら次の区分に入る」条件の園(30分ちょうどが2区分)なら、ROUNDUPの代わりに、分数を区分の長さで割って切り捨て、1を足す形に差し替えます。0分のときは0区分のままにします。
F2: =IF(E2=0, 0, INT(E2/条件!$B$3)+1)この式だと30分は2区分、29分は1区分、60分は3区分です。どちらの判定を使うかを条件シートに1行足して、式から参照させる方法もあります。
G列は、区分数に単価を掛け、日額の上限で頭打ちにします。
月額上限は、「月次」シートで園児ごとに合計してから掛けます。
B2: =MIN(SUMIFS(請求!G:G, 請求!A:A, A2), 条件!$B$6)
C2: 請求書の月額(貼り付け)
D2: =C2-B2順序は、日額ごとに上限をかけたものを足し、最後に月額上限をかける形です。日額上限の前の金額を足してしまうと、上限の効果が二重に抜けます。
サンプルで差異の出方を見る
仮の条件(18:00開始、30分刻み、200円、日額上限800円、月額上限6,000円)で、園児Aの6日分を再計算します。
| 日付と降園時刻 | 延長(分)と区分 | 請求額 | 再計算額 | 差額 |
|---|---|---|---|---|
| 9/1 18:00 | 延長(分)と区分0分・0区分 | 請求額0 | 再計算額0 | 差額0 |
| 9/2 18:07 | 延長(分)と区分7分・1区分 | 請求額200 | 再計算額200 | 差額0 |
| 9/3 18:30 | 延長(分)と区分30分・1区分 | 請求額400 | 再計算額200 | 差額+200 |
| 9/4 18:31 | 延長(分)と区分31分・2区分 | 請求額400 | 再計算額400 | 差額0 |
| 9/5 19:45 | 延長(分)と区分105分・4区分 | 請求額800 | 再計算額800 | 差額0 |
| 9/8 20:10 | 延長(分)と区分130分・5区分 | 請求額1,000 | 再計算額800 | 差額+200 |
差異は2行です。9/3は、30分ちょうどを2区分に入れた形で、境界の扱いの違いです。9/8は、5区分の1,000円がそのまま請求されていて、日額上限800円が掛かっていません。合計は請求2,800円に対し再計算2,400円で、400円の過大です。
2行は同じ「差異あり」でも、園内で確かめることがまったく違います。9/3には、請求システムの区分の判定条件を。9/8には、日額上限の設定が有効になっているかを見ます。金額差の大きさでなく、差額が区分単価の整数倍かどうかと、その日の延長分数から、原因の見当を付けます。
月額上限で別の差が出る
月次シートでは、園児ごとの合計を比べます。
| 園児 | 日額の合計(再計算) | 月額上限適用後 | 請求額 | 差額 |
|---|---|---|---|---|
| B | 日額の合計(再計算)7,400 | 月額上限適用後6,000 | 請求額7,400 | 差額+1,400 |
| C | 日額の合計(再計算)4,200 | 月額上限適用後4,200 | 請求額4,200 | 差額0 |
園児Bは、日額ごとの計算が正しくても、月額上限を超えた分がそのまま請求されています。日別の突合だけでは見つからず、月次の合計で初めて出る差です。
Claude for Excelへの依頼文を段階に分ける
「全部突合して」と一度に頼むより、段階を分けたほうが検算しやすくなります。Claude for Excelは、回答にセル単位の根拠を付け、クリックで該当セルへ移動できます。
- 式を組ませる: 「『条件』シートの値を使って、『請求』シートのE〜H列に、延長分数・区分数・再計算した日額・差額の式を入れてください。時刻の引き算は分に直したあと整数に丸めてください」
- 式を検算させる: 「9/3の行について、G列の金額がどう導かれたか、根拠のセルを示してください」
- 月次を組ませる: 「『月次』シートに、園児ごとの日額の合計に月額上限を掛けた金額と、請求書の月額との差を出してください」
- 差異を取り出させる: 「H列か月次の差額が0でない行を抽出して、日額の差と月額の差に分けてください」
4の抽出は、フィルタや条件付き書式といった標準のExcel操作として頼めます。条件を変えて試すこともできます。「B3を31に変えたら、差異のある行の件数はどう変わるか」と頼めば、数式の関係を保ったまま値だけが更新され、下流のセルが再計算されます。請求システムの区分の数え方が、手元の条件に近いのかを探る手がかりになります。
毎回の書式指定は追加の指示に置く
アドインのサイドバーの設定にある指示欄に書いた内容は、Excelでの全会話に適用されます。次のように置いておくと、依頼のたびに書式を書く手間が減ります。
金額は円で桁区切りを付け、小数点以下は表示しない。
時刻はhh:mm形式で表示する。
「条件」シートのセルは書き換えない。最後の1行は、条件シートの値を断りなく変えて結果を合わせてしまう事態を避ける保険です。上書きの前には警告が出ますが、指示でも明示しておくと意図しない編集が減ります。
差異を原因別に仕分けて、請求システムの設定を見直す
差異の出た行は、金額の大きさでなく原因で分けます。
| 原因の候補 | 見分け方 | 確かめること |
|---|---|---|
| 区分の境界 | 見分け方境界の時刻(30分・60分ちょうど)の行だけ区分単価ぶん多い | 確かめること判定が「30分以内は1区分」か「30分に達したら次の区分」か |
| 日額上限の未適用 | 見分け方延長が長い日だけ、上限を超えた額が請求されている | 確かめること日額上限の設定の有無 |
| 月額上限の未適用 | 見分け方日別は合い、月合計だけ上限を超える | 確かめること月額上限を掛ける順序 |
| 時刻の記録の違い | 見分け方数分違いで区分が1つずれる | 確かめること打刻と手入力のどちらを正とするか |
この仕分けの案はClaudeに作らせられますが、「候補」は推測です。確定には、請求システムの設定画面と、その日の打刻の原本を見る必要があります。
同じ保育の制度まわりでは、入園前の準備を扱う入所指数の試算や、年度末の書類を扱う保育要録の下書きがあります。
使えない場面と注意点
- 個人情報は最小限にする: 登降園の時刻は、園児の生活パターンが分かる情報です。氏名は園児IDに置き換え、突合に不要な列は貼りません。Claude for Excelの会話履歴は、ブラウザ内のIndexedDBにローカル保存され、端末ごとに残ります。共用PCでは、設定から履歴を消せます
- 外部のファイルをそのまま開かない: 保護者や外部業者から届いたファイルには、隠された指示が入っている可能性があります。自園の保育管理システムが出力したCSVでも、信頼できる自分のブックに値だけを貼り付ける運用が安全です
- VBAとマクロは扱えない: データテーブルとマクロ・VBAの操作は未対応です。月次の集計をマクロで自動化している園では、その部分を人が行います
- 最終判断は人が行う: 監査で根拠になる計算や、人の確認を経ない最終的な成果物には、推奨されない用途として挙げられています。保護者への過大請求の返金や、請求内容の訂正を通知する前に、規約との照合と数件の電卓検算を済ませます
- 動作環境: Excelは、ウェブ版、Microsoft 365のWindows版(ビルド16.0.13127.20296以降)、Mac版(16.46以降)で動きます。Excel 2016・2019の買い切り版、iPad、Androidでは動きません。利用はPro、Max、Team、Enterpriseプランが対象です
- 編集前にコピーを取る: 広い範囲を編集させる前に、元ブックのコピーを取ります
ブックを開かず、CSVをチャットに添付して集計させる方法もあります。ファイル作成機能は、CSVやTSVを読み込んで分析し、Excelファイルとして書き出せます。ただし、式の根拠をセルにたどる使い方は、Excelのアドインのほうが向いています。
まとめ
延長保育料の突合は、開始時刻・区分・単価・上限を条件シートに切り出し、降園時刻から分数、区分、日額、月額の順に式を並べると、請求書との差が行ごとに出ます。ROUNDで分数を整数にしておくことが、境界の誤差を防ぐ要点です。Claudeには式の作成、セル根拠つきの検算、条件を振ったときの件数比較を任せ、条件シートに入れる値は人が園の書面から決めます。差異は大きさでなく原因で分けると、請求システムの設定の見直しが1回で済みます。