保険代理店の手数料明細を入金と突合する手順 — Claude for Excelで会社別の様式を揃える
保険会社ごとに違う手数料明細を1枚の統一明細に寄せ、会社別の入金額と突合する手順です。戻入や保留による差の仕分けと、契約管理台帳との照合、Claude for Excelへの依頼文まで示します。
保険代理店の手数料明細と入金が合わないときは、先に明細の様式を揃えます。保険会社ごとに列名も、戻入の表し方も違う明細を、同じ形の1枚に寄せてから会社別に合計すれば、差は「戻入」「保留」「台帳にない契約」のどれかに分けられます。Claude for Excelなら、この寄せ作業と照合の式づくりを、開いているブックの中で頼めます。
ここでは、複数の保険会社と取引する代理店の経理担当が、月次の入金確認を目視から切り離す手順を示します。統一明細の列の決め方、3社のサンプルによる差の仕分け、台帳との照合、依頼文、使えない場面の順に進みます。
手数料明細の突合でClaude for Excelに任せる範囲
Claude for Excelは、開いているExcelのブックについて質問し、セル単位の根拠つきで答えるアドインです。数式の関係を保ったまま値を変えたり、エラーの原因をたどったり、テンプレートに数式を入れたりできます。Pro・Max・Team・Enterpriseの各プランで使えます。並べ替え、フィルター、ピボットテーブルの編集も、そのまま頼めます。
手数料明細の突合に当てはめると、役割は次のように分かれます。
| 作業 | Claudeに任せる | 人が持つ |
|---|---|---|
| 各社の明細を統一明細に寄せる | Claudeに任せる列の対応づけと貼り付け | 人が持つどの列が何を表すかの最終確認 |
| 会社別の合計と入金の差を出す | Claudeに任せる式の作成と全行の計算 | 人が持つ差の原因の判断 |
| 台帳にない契約・台帳にあるのに明細にない契約を拾う | Claudeに任せる照合式と抽出 | 人が持つ契約内容の確認と保険会社への問い合わせ |
| 戻入や保留の仕分け | Claudeに任せる分類案 | 人が持つ保険会社の説明との照合 |
手数料の額そのものは、人が決めた前提でなく保険会社の計算で決まります。損害保険の代理店手数料の算出方法は、金融庁の監督指針で、代理店委託契約に基づく損害保険会社と代理店の協議・合意により決まると説明されています。明細の読み方に迷ったときの最終的な拠り所は、自社が結んでいる委託契約と各社の手数料の説明資料です。
銀行口座の入金を請求書に結びつける消込は別の作業です。そちらは入金消込をClaude Codeで自動化する手順にあります。同じ「入金の差を分解する」型の例として、キャッシュレスの入金と売上の突合も参考になります。保険の準備金ワークブックを検証する場合は、Claude for Excelによる保険の準備金の検証が近い題材です。この記事は、保険会社が出した明細の合計が、実際に振り込まれた額と合う理由を検算する手順です。
統一明細の列を先に決める
様式の違いは、列名より先に「何を1行と数えるか」で出ます。ある会社は契約ごとに1行、別の会社は計上区分ごとに1行ということがあります。そこで、Claudeに寄せ方を任せる前に、受け皿になる統一明細の列を人が決めます。
| 列 | 中身 | 決め方 |
|---|---|---|
| A保険会社 | 中身会社名 | 決め方シートごとに固定 |
| B契約キー | 中身証券番号など | 決め方全角・半角・ハイフンを揃えた文字列 |
| C区分 | 中身新規・継続・戻入・その他 | 決め方各社の区分名を自社の4分類に対応づける |
| D金額 | 中身支払はプラス、戻入はマイナス | 決め方符号の流儀を1つに固定 |
| E元の行 | 中身元シートの行番号 | 決め方追跡用 |
説明用に、3社の明細が次のように違う場合を考えます。社名と列名は架空の例です。
| 会社 | 契約の列名 | 金額の持ち方 | 戻入の表し方 |
|---|---|---|---|
| A損保 | 契約の列名証券番号 | 金額の持ち方手数料額と戻入額で別の列 | 戻入の表し方戻入額の列に正の数 |
| B生保 | 契約の列名契約番号 | 金額の持ち方支払額の1列 | 戻入の表し方マイナスの数 |
| C生保 | 契約の列名証券No | 金額の持ち方計上額の1列 | 戻入の表し方区分が「戻入」の行 |
同じ「戻入」でも、A損保は別の列、B生保は負の数、C生保は区分名です。統一明細のD列に符号を揃えておかないと、会社をまたいだ合計で戻入が足されてしまいます。
契約キーの揃え方は、先にルールを置きます。Excelの ASC 関数は全角の英数字を半角にします。
B2: =SUBSTITUTE(SUBSTITUTE(ASC(TRIM(元!A2)),"-",""),"－","")先頭のゼロが消えるのを避けるため、元データの契約番号は文字列として読み込みます。台帳側のキーにも同じ式を掛けておくと、照合で表記の揺れに引っかかりません。
保険会社ごとの明細を統一明細へ寄せる
寄せ作業は、1社ずつ小さく進めます。全社を一度に頼むと、どの対応づけが誤っていたかをたどりにくくなります。
1社分の明細を統一明細へ寄せる
- 1
明細のコピーを作る
保険会社から届いたファイルは、そのまま開かず、作業用のコピーをブックに貼ります。
- 2
列の対応づけを言葉で確認する
Claudeに、元の列を統一明細のどの列へ入れるつもりかを、先に日本語で説明させます。
- 3
符号と区分を揃える
戻入が別の列にある会社は、マイナスの金額の行として統一明細へ入れます。
- 4
合計を元の明細と比べる
統一明細に入れた会社別の合計と、元の明細の合計行が一致するかを確かめます。
最後の確認が、寄せ作業の取りこぼしを見つける確認です。元の明細に合計行がある会社は、その値と統一明細の SUMIFS が一致するまで次へ進みません。
会社別に明細の合計と入金の差を出す
入金シートには、保険会社名・入金日・入金額を1行ずつ置きます。会社別の差は、統一明細の合計と入金額を並べるだけで出ます。
明細合計: =SUMIFS(統一明細!$D:$D,統一明細!$A:$A,A2)
差額: =C2-D2C列は入金額、D列は明細合計です。差額は、実際の入金から明細の合計を引いた値にそろえます。この向きは、永続的な指示として置いておくと依頼のたびに繰り返さずに済みます。アドインの設定画面にはInstructionsの欄があり、Excelでの会話すべてに効く指示を書けます。たとえば「差は入金額から明細合計を引いた値とする」「金額は円の整数で表示する」と置きます。この指示はExcelだけに効き、PowerPointやWordの指示とは別です。
差額が0の会社は、ここで終わりです。0でない会社だけを、次の仕分けに回します。
3社のサンプルで差を仕分ける
ある月の3社を使います。数値は説明用の例で、実在の代理店の数字ではありません。
| 会社 | 手数料(支払) | 戻入 | 明細の差引合計 | 入金額 | 差額 |
|---|---|---|---|---|---|
| A損保 | 手数料(支払)182,400円 | 戻入−12,600円 | 明細の差引合計169,800円 | 入金額169,800円 | 差額0円 |
| B生保 | 手数料(支払)96,500円 | 戻入0円 | 明細の差引合計96,500円 | 入金額94,700円 | 差額−1,800円 |
| C生保 | 手数料(支払)58,200円 | 戻入0円 | 明細の差引合計58,200円 | 入金額58,200円 | 差額0円 |
| 合計 | 手数料(支払)337,100円 | 戻入−12,600円 | 明細の差引合計324,500円 | 入金額322,700円 | 差額−1,800円 |
A損保は戻入を引いた後の169,800円で、入金と一致しています。戻入を別の列から符号つきで寄せていなければ、この会社の明細合計は182,400円と読まれ、12,600円の差が出ていたはずです。差の原因が入金の側でなく、寄せ方にあった典型です。
差が残るのはB生保の1,800円だけです。統一明細でB生保の行を絞ると、契約B-0042の1,800円が、区分に「保留」と書かれた行に入っていました。その行を除いた明細の合計は94,700円で、入金額と一致します。つまり、この月の入金は保険会社が明細に載せた全額ではなく、保留の行を除いた額でした。
このように、差の原因が1つに絞れたら、保険会社に聞く内容も決まります。保留の理由と、支払予定月です。翌月の明細で同じ契約B-0042の行が支払として現れるかを、次の月の突合で確かめます。
契約管理台帳との照合で、明細の漏れと余りを拾う
会社別の合計が合っていても、明細の中身が自社の台帳と合っているとは限りません。契約管理台帳との照合で拾える問題は2種類あります。
- 明細にあるのに台帳にない契約: 台帳の登録漏れ、または他の代理店からの異動
- 台帳にあって更新月なのに明細にない契約: 手数料の計上漏れ、解約、保留
統一明細のF列に、台帳に同じ契約キーがあるかの判定を置きます。
F2: =IF(COUNTIFS(台帳!$A:$A,B2)=0,"台帳なし","台帳あり")逆向きの照合は、台帳側で同じ式を書きます。
台帳!H2: =IF(COUNTIFS(統一明細!$B:$B,A2)=0,"明細なし","明細あり")継続の手数料が出るはずの月を、台帳のどの列で判断するかは、自社の台帳の持ち方で決まります。Claudeには「更新月が今月の契約のうち、H列が『明細なし』の行を抜き出してください」のように、列を名指しして頼みます。
台帳の項目を手数料の水準で並べ替えて、提案の優先順位に使うことは、突合の目的の外です。金融庁の監督指針は、二以上の保険会社の商品を比較して推奨する代理店について、手数料水準の高い商品へ誘導するための絞り込みや推奨を行わないよう求めています。集計の結果は、入金と台帳の整合を取るために使います。
Claude for Excelへの依頼文を3段階に分ける
一度に全部を頼まず、寄せる、検算する、仕分けるの3段階にします。
- 寄せる。「A損保のシートの証券番号をB列、手数料額を支払としてD列、戻入額をマイナスにしてD列に、統一明細へ寄せてください。対応づけは先に日本語で説明してください」
- 検算する。「統一明細の会社別の合計を、元のシートの合計行と並べて、一致しない会社を示してください。根拠のセルも示してください」
- 仕分ける。「入金シートの差額が0でない会社について、区分が『保留』『戻入』の行を抜き出し、差額との関係を説明してください。判断がつかない部分は『要確認』にしてください」
Claude for Excelの回答には、クリックで該当セルへ移れるセル単位の根拠が付きます。2番目の依頼で、どの行を寄せ損ねたかをたどれます。また、既存のデータを上書きする前には警告が出ます。
外部ファイルを開くときの注意と、使えない場面
保険会社から受け取る明細は、外部から来るファイルです。Claude for Excelの公式ページは、ダウンロードしたテンプレートや取引先のファイル、データの取り込みに、AIを誘導する隠れた指示が含まれうると説明しています。信頼できるスプレッドシートでだけ使うよう求めています。
落とし穴
受け取った明細を、そのままClaudeに読ませない
保険会社から届いた明細を直接開いて操作を頼むと、ファイル内の文字列が指示として働く危険があります。作業用のコピーを作り、必要な列だけを統一明細に貼ってから依頼します。危険な操作に確認が出たら、内容を読んでから承認します。
ほかにも、使う前に知っておくことがあります。
- マクロとVBAの操作、データテーブルは未対応です。明細の取り込みをマクロで組んでいる場合、その部分はClaudeに任せられません
- Excel 2016と2019の永続・ボリュームライセンス版、iPad版、Android版では動きません
- 公式ページは、監査に直結する計算を検証なしで任せること、人の確認なしに最終成果物とすることを勧めていません。入金確認の結果は、担当者が明細と通帳で確かめます
- 会話の履歴はブラウザーのIndexedDBにローカル保存されます。入力と出力は、受領または生成から30日以内にバックエンドから削除されます。契約者名などの個人情報を含む明細を扱うときは、自社の情報管理規程と照らして、貼る列を絞ります
突合の結果をどう使うか
最初に効くのは、差額が0の会社を機械的に閉じることです。月次の確認時間は、差が出た会社の仕分けに集中できます。次に、保留や戻入が出た契約を、台帳に備考として残しておくと、翌月の明細で自動的に追えます。
Claudeが組んだ式や分類は下書きです。寄せ方の対応づけと、保険会社に問い合わせる内容は、担当者が最終決定します。