営業コミッションの計算をClaude for Excelで検算する手順 — 段階料率と返金調整
達成率で料率が上がる営業報酬は、境界の扱いと返金の取り戻しで計算が割れます。受注台帳と報酬テーブルを同じブックに置き、Claude for Excelで式を検算する手順です。
営業のコミッションは、達成率で料率が上がる計画だと、計算が人によって食い違いやすくなります。割れ目は2つあります。ひとつは、料率の段が上がる境界で、新しい料率を超過分だけに掛けるのか、全額に掛けるのか。もうひとつは、受注のあとに返金や解約が出たとき、すでに払った分をどう取り戻すかです。
この記事では、受注台帳と報酬テーブルを同じブックに置き、1人ずつの報酬額を式で出して、Claude for Excelに検算させる手順を書きます。数字は説明用に作った仮の計画で、自社の報酬規程の数値ではありません。
計算が割れる場所は境界と返金の2つ
達成率に応じて料率が上がる計画では、次の2通りの読み方があります。
| 読み方 | 式 | 境界での動き |
|---|---|---|
| 段階ごと(超過分のみ) | 式各段の幅に、その段の料率を掛けて足す | 境界での動き境界の前後で報酬が連続する |
| 全額に適用 | 式到達した段の料率を、受注額の全体に掛ける | 境界での動き境界をまたぐと報酬が跳ぶ |
報酬規程が「100%以上は5%」とだけ書いていると、どちらにも読めます。営業が全額適用で理解し、経理が段階ごとで計算していれば、境界を越えた人だけ数万円から数十万円ずれます。
返金のほうは、取り戻す額の出し方が分かれます。返金額に料率を掛けて引く方法と、返金を引いた純受注で報酬を計算し直し、支払済みとの差を取り戻す方法です。段をまたいで達成率が下がると、この2つの答えが一致しません。
検算で先に疑う3つの箇所
境界の読み方
達成率ちょうど100%、ちょうど120%の人が、計画書の意図どおりの段に入っているかを見ます。
返金の取り戻し
返金の対象期間に入る行だけを引いているか、段が下がった分まで反映しているかを見ます。
達成率の丸め
割り算の端数で、境界の人がわずかに下側へ落ちていないかを見ます。
台帳と報酬テーブルを同じブックに置く
検算の土台は、入力の置き場所を分けることです。シートは3枚にします。
| シート | 中身 | 種別 |
|---|---|---|
| 受注台帳 | 中身受注ID・担当・受注日・受注額・返金額・返金日 | 種別入力 |
| 報酬テーブル | 中身達成率の下限・料率・差分料率 | 種別入力(規程どおり) |
| 集計 | 中身担当ごとの目標・受注額・達成率・報酬 | 種別式 |
報酬テーブルは、下限を小さい順に並べます。差分料率は「その段の料率から一つ下の段の料率を引いた値」で、段階ごとの式で使います。
| 下限達成率(A列) | 料率(B列) | 差分料率(C列) |
|---|---|---|
| 0% | 料率(B列)3% | 差分料率(C列)3% |
| 100% | 料率(B列)5% | 差分料率(C列)2% |
| 120% | 料率(B列)8% | 差分料率(C列)3% |
C列は =B2 、 =B3-B2 、 =B4-B3 と置きます。料率や下限を変えても、式を触らずに済みます。
受注台帳には、返金が取り戻しの対象になるかを判定する列を足します。計画に「受注から90日以内の返金だけ取り戻す」とあるなら、判定日数をセルに置いて参照します。J1に90と入れておく例です。
G2: =IF(AND(E2>0,F2-C2<=$J$1),E2,0)E列が返金額、F列が返金日、C列が受注日です。返金がない行はE列が空でも0になります。
集計シートの式を組む
集計シートは1行を1人にします。A列が担当、B列が目標、C列が受注額、D列が対象返金、E列が純受注、F列が達成率、G列が段階ごとの報酬、H列が全額適用の報酬(検算用)、I列が支払済みの報酬(入力)、J列が差引です。
C2: =SUMIFS(受注台帳!$D$2:$D$500,受注台帳!$B$2:$B$500,A2)
D2: =SUMIFS(受注台帳!$G$2:$G$500,受注台帳!$B$2:$B$500,A2)
E2: =C2-D2
F2: =ROUND(E2/B2,4)
G2: =ROUNDDOWN(SUMPRODUCT((E2>B2*報酬テーブル!$A$2:$A$4)
*(E2-B2*報酬テーブル!$A$2:$A$4)*報酬テーブル!$C$2:$C$4),0)
H2: =ROUNDDOWN(LOOKUP(F2,報酬テーブル!$A$2:$A$4,
報酬テーブル!$B$2:$B$4)*E2,0)
J2: =G2-I2G列は、各段の下限金額(目標×下限達成率)を超えた部分に差分料率を掛けて足す形です。到達していない段は、先頭の判定が0になって消えます。
H列のLOOKUPは、達成率に対して「以下で最大」の下限を探し、その行の料率を返します。検索する側の下限は昇順に並べておく必要があり、並びが違うと正しい値が返りません。達成率が表の最小値より小さいと#N/Aになるため、下限0%の行を置いておきます。
集計の範囲は、列全体ではなく500行目までのように区切ります。列全体の参照はExcelが遅くなる原因として、Microsoftのサポートページが挙げているものです。目標が0の担当があるとF列が#DIV/0!になるので、目標が未入力の行は先に洗います。
F列でROUNDを入れているのは、割り算の端数対策です。境界の人の達成率が、計算の誤差で境界のわずかに下になる場合があります。小数4桁に丸めておくと、H列の段の判定が安定します。ただし丸めの桁数も計画の定義にあたるので、規程が「達成率は小数第1位まで」と定めているなら、そちらに合わせます。
数字を入れた例
仮の計画で3人を計算します。目標はAとCが1,000万円、Bが800万円です。
| 担当 | 受注額 | 対象返金 | 純受注 | 達成率 | 段階ごと(G) | 全額適用(H) |
|---|---|---|---|---|---|---|
| A | 受注額12,000,000 | 対象返金0 | 純受注12,000,000 | 達成率120% | 段階ごと(G)400,000 | 全額適用(H)960,000 |
| B | 受注額7,000,000 | 対象返金0 | 純受注7,000,000 | 達成率87.5% | 段階ごと(G)210,000 | 全額適用(H)210,000 |
| C | 受注額11,000,000 | 対象返金2,000,000 | 純受注9,000,000 | 達成率90% | 段階ごと(G)270,000 | 全額適用(H)270,000 |
Aさんは達成率がちょうど120%です。段階ごとなら、1,200万円全体に3%(36万円)、100%を超えた200万円に2%(4万円)を足して40万円。全額適用の読み方なら8%を全体に掛けて96万円で、56万円の差が出ます。GとHの食い違う行は、計画の読み方を人に確認する印になります。
Cさんは返金が出た例です。返金前の受注額1,100万円(達成率110%)では、1,100万円全体に3%で33万円、100%を超えた100万円に2%で2万円となり、35万円を払い済みでした。返金後は純受注が900万円、達成率90%に下がり、報酬は27万円です。差引は27万円−35万円でマイナス8万円、これが取り戻す額になります。
落とし穴
返金額に料率を掛けると取り戻しが多すぎる
Cさんの返金200万円に、払ったときの上の段の料率5%を掛けると10万円になり、正しい8万円より2万円多く取り戻してしまいます。段をまたいで達成率が下がる行では、返金を引いた純受注で報酬を組み直し、支払済みとの差を取り戻す形のほうが、台帳の数字と整合します。もちろん、どちらの方式が正しいかは報酬規程と契約の定めで決まります。
Claude for Excelに検算を頼む
台帳と式が組めたら、Claude for Excelに点検を頼みます。Claude for Excelは、開いているブックについての質問に、セル単位の根拠を付けて答えるExcelのアドインです。数式の関係を保ったまま値を変えることや、エラーの原因をたどることもできます。Pro、Max、Team、Enterpriseの各プランで使えます。
入力の列と式の列を名指しして、見てほしい点を絞ります。
集計シートのG列は段階ごとの報酬、H列は全額適用の報酬です。
次の3点を点検してください。
1. G列が、報酬テーブルの差分料率(C列)を使っていますか。
料率(B列)をそのまま掛けている行はありませんか。
2. G列とH列の値が食い違う行を一覧にしてください。
3. D列(対象返金)が、受注台帳のG列の合計と一致していますか。1つ目は、差分料率と料率の取り違えを拾う点検です。料率をそのまま超過分に掛ける式は、段が増えるほど報酬が多く出ます。2つ目で出た行は、前の節のAさんのように、計画の読み方を人が決める対象です。3つ目は、返金の判定列が集計に正しく流れているかの整合チェックです。
境界の挙動は、Claudeに仮の数字を入れさせて確かめることもできます。「Aの受注額を1,199万円と1,200万円と1,201万円に変えたときの報酬を並べてください」と頼めば、境界の前後で段階ごとの報酬が連続するか、全額適用が跳ぶかを表で確認できます。Claude for Excelは、数式の関係を保ったまま値を変えて、下流の計算を更新する使い方に対応しています。試す前に、ブックのコピーを取っておきます。
台帳が長いときの扱い
Claude for Excelが1回の読み取りで受け取れるのは、データのあるセルで2,000セルまでです。それを超える大きなシートは、複数の手順に分けて読み込まれます。受注台帳が数千行に及ぶなら、期間や担当で絞った台帳に分けて検算するほうが、依頼の意図が伝わりやすくなります。
手計算との突き合わせは人が行う
Claude for Excelのページは、監査上重要な計算を検証なしで任せないこと、顧客向けの最終成果物を人のレビューなしで出さないことを、推奨しない使い方として挙げています。報酬の支払いは本人の収入に直結するため、この線引きに当たります。
突き合わせは単純です。境界ちょうどの人、返金で段が下がった人、返金が対象期間外だった人の3種類から1人ずつ選び、電卓で報酬を出して、集計の値と照合します。マクロやVBA、データテーブルはClaude for Excelでは扱えないため、報酬の計算を別のブックのマクロで組んでいる場合は、その結果を値で貼って比べます。
営業の台帳は、CRMから書き出したファイルを使うことが多いと思います。外部から届いたファイルには隠れた指示が混ざっている可能性があり、Claude for Excelのページは、信頼できるスプレッドシートにだけ使うよう警告しています。社内システムから自分で書き出したコピーで作業し、他社から届いたブックは使い回さないほうが安全です。
ずれやすい入力の4か所
式が正しくても、入力側でずれが出ます。点検の最初にこの4つを洗います。
- 受注の計上日と期間の切り方: 受注日が期をまたぐ行が、どちらの期の目標に入るかを規程で確認します
- 返金の判定日: 取り戻しの期間を受注日から数えるのか、入金日から数えるのかで、対象の行が変わります
- 分割払いや一部返金: 返金額が受注額の一部のとき、台帳が1行で足りているかを見ます
- 担当の途中交代: 案件の担当が変わった場合、受注額の按分を台帳にどう置くかを決めます
営業の月次の見込みをExcelで組む手順はClaudeで営業の売上予測をExcelで作る手順にあります。報酬を賞与として年度末に引き当てる側の確認は賞与の引当を年度末に検算する手順で、歩合の形で払う報酬があるとき、残業の割増賃金の単価に歩合が関わる点は歩合給の割増賃金の検算手順で扱っています。
差が出たときの動き
GとHが食い違った行、差引がマイナスになった行は、次の順で扱います。
- 入力の確認: 受注額、返金額、返金日が、受注管理と請求の記録に合っているかを見る
- 規程の確認: 段階ごとか全額適用か、取り戻しの方式はどちらかを、報酬規程と契約の文言で確かめる
- 精算: 差額と取り戻しの額は、人事・給与の担当者が確定し、本人に説明できる形にして処理する
報酬の取り戻しは、本人の給与や報酬から差し引く扱いになる場合があり、規程や契約の定め方で可否が変わります。ここはClaude for Excelに判断させる領域ではなく、人事や法務に確認する領域です。Claude for Excelに任せられるのは、式の点検、整合チェック、食い違う行の抽出までです。
検算結果の残し方
結果は、対象の期間と作業した日を付けて、集計シートを値だけで別のシートに貼り付けて保存します。報酬テーブルの数値、判定日数(J1)、手計算で突き合わせた担当のIDを欄外に書いておくと、後で金額を問われたときに再現できます。
Claude for Excelのチャット履歴は、ブラウザー内のIndexedDBにローカル保存され、端末間では同期されません。点検の根拠は、会話ではなくブックの側に残します。累計部数で率が上がる出版の印税も、同じ段階計算の構造です。印税の再計算と部数報告書の突き合わせも参考になります。