Claudeで燃料サーチャージの算定表を作る手順 — 軽油価格から加算額を検算

燃料サーチャージの算定表は、基準となる軽油価格、価格帯の刻み、車種ごとの燃費の3つを置けば、キロ程ごとの加算額を式で出せます。Claude for Excelにこの式を組ませ、電卓で出した数ケースと突き合わせれば、荷主への説明資料を短時間で整えられます。

運送会社で運賃の改定を担う経理・営業担当や、運送会社から改定の申し入れを受ける荷主の物流担当に向けた手順です。本稿の金額・価格・燃費はすべて説明用の仮の値で、実際の契約や相場ではありません。

算定表に置く4つの値と加算額の式

燃料サーチャージは、燃料価格の上昇・下落によるコストの増減分を、運賃とは別建てで設定する制度です。国土交通省の「トラック運送業における燃料サーチャージ緊急ガイドライン」(平成24年5月16日改訂)は、現状の燃料価格が基準より一定額以上上がったときに、上昇幅に応じて設定または増額し、下がれば減額、基準を下回れば廃止するとしています。

算定表に置く値は、ガイドラインの記号で次の4つです。

Cから基準価格Bを引いた値が、算出上の燃料価格上昇額Dです。式は距離制と時間制で次のとおりです。

• 距離制: 燃料サーチャージ額(円)＝走行距離÷燃費×D
• 時間制: 燃料サーチャージ額(円)＝平均走行距離÷燃費×D

継続契約の平均走行距離は、1日・1か月・半期などの期間実績で出します。

ここで押さえたいのは、式が出すのが円額で、料率(%)ではない点です。しかもDは価格帯ごとに変わるため、ガイドラインはD1〜D5ごとに車種×キロ程の表を作るよう求めています。算定表が1枚で済まず、シートの構造を先に決める必要があるのはこのためです。

算定表のブックを4枚のシートに分ける

Claudeに「燃料サーチャージの表を作って」と一言で頼むと、基準価格や燃費が式の中に直接埋まったブックになりやすく、価格帯を変えるたびに式を書き直すことになります。入力値と計算を分けた4枚構成にしておくと、変えるのは入力セルだけで済みます。

設定シートのB3に基準価格100円/L、B4にきざみ幅5円を置くと、価格帯シートは次の式で組めます。

価格帯!A2  =設定!$B$3          (最初の帯の下限は基準価格B)
価格帯!B2  =A2+設定!$B$4       (上限＝下限＋きざみ幅)
価格帯!A3  =B2                 (次の帯の下限は前の帯の上限)
価格帯!C2  =(A2+B2)/2          (算出上の価格C＝帯の平均値)
価格帯!D2  =C2-設定!$B$3       (上昇額D＝C－B)

A3以降のB・C・Dは、2行目の式を下へ複写します。仮の値で5段分を並べると、次の表になります。

基準価格100円/L未満は、サーチャージを廃止する帯です。表には行を作らず、後で月次の判定式の中で0円として扱います。

燃費シートは仮の値で、4t車が6.0km/L、10t車が4.0km/Lとします。算定表シートは、B1に適用するD、3行目に車種名、4行目に燃費、A5〜A8にキロ程の50・100・150・200を置きます。

算定表!B4  =INDEX(燃費!$B$2:$B$3,MATCH(B3,燃費!$A$2:$A$3,0))
算定表!B5  =ROUND($A5/B$4*$B$1,0)

B5の式は、キロ程÷燃費×Dの順に書いてあります。ガイドラインの式と同じ順にしておくと、後で人が式を読み比べるときに迷いません。

Claude for Excelに組ませる依頼文と、常設の指示

Claude for Excelは、開いているブックについて質問し、数式の関係を保ったまま値を変え、テンプレートに式を入れたり新しいモデルを作ったりできるアドインです。Pro・Max・Team・Enterpriseの各プランで使えます。回答にはセルの根拠が付き、クリックで該当セルへ移動できます。

依頼は一度に全部を投げず、シートごとに区切ります。

1回目の依頼文は、たとえば次のように書きます。

新しいシート「設定」「価格帯」を作ってください。
- 設定!B3 に基準価格 100、B4 にきざみ幅 5 を入力値として置く
- 価格帯シートは A列=下限、B列=上限、C列=下限と上限の平均、
  D列=C列から設定!B3 を引いた値。5行分
- 基準価格やきざみ幅を式の中に数値で直接書かず、必ず設定シートのセルを参照する
- 既存のセルは上書きしない

「数値を式に直接書かない」は、毎回の依頼に書くより、アドインの常設指示に入れたほうが漏れません。サイドバーの設定にあるInstructionsの欄は、会話をまたいで効く指示を置く場所で、Excelだけに適用されます。たとえば次のように置きます。

- 金額の端数は ROUND で四捨五入する。切り上げ・切り捨てを使うときは設定シートで方式を選ばせる
- 基準価格・燃費・きざみ幅など、変わる値は式に直接書かず入力セルを参照する
- 入力セルは青字、式のセルは黒字にする
- 新しい式を入れたら、検算用に手計算できる行を3つ添える

Claudeの出した式は、そのまま受け取りません。サポートページはCurrent limitationsで、人のレビューを経ない最終成果物と、検証なしの監査上重要な計算にはお勧めしないと書いています。料金の改定は荷主との協議の資料になるため、この制約がそのまま当てはまります。

月次の軽油価格から適用するDを引く

ガイドラインの例では、改定条件を「軽油ローリー価格(月平均調達価格)が基準時点から一定の幅で変動した時点で、翌月から改定」、廃止条件を「基準価格を下回った時点で、翌月から廃止」としています。これを表にすると、判定月・月平均価格・D・適用月の4列になります。

月次!C2  =IF(B2<設定!$B$3,0,
            INDEX(価格帯!$D$2:$D$6,MATCH(B2,価格帯!$A$2:$A$6,1)))
月次!D2  =EDATE(A2,1)

B2が月平均価格、C2が適用するD、D2が適用月です。基準価格を下回れば0円(廃止)、それ以外は価格帯の下限と照合してDを返します。仮の価格で動かすと次のようになります。

7月のように帯の下限ちょうどの価格は、上の帯に入ります。「以上〜未満」の向きがずれると、境界の月だけDが半段階違う算定表になります。

手計算6ケースで検算する

検算の基本は、Claudeの式を通らない値を別に用意して、差を取ることです。期待値は人が電卓で出し、数字として検算シートに打ち込みます。Claudeには、算定表との差を出す列だけを作らせます。

6件は役割を分けてあります。1〜4は価格帯と四捨五入の通常の動き、5は帯の下限ちょうどの価格と、端数がちょうど.5になる行を同時に突く件、6は廃止条件です。

5番は、端数処理の方式が結果を左右する行です。四捨五入なら313円、切り捨てなら312円になります。ガイドラインの設定例にも「四捨五入、または端数を切り上げ、切り下げ」と、方式を選ぶ注記があります。方式は設定シートで決めて、検算シートの期待値もそれに合わせます。

差が出た行は、式の誤りか、期待値側の電卓の打ち間違いのどちらかです。まず入力値(価格帯の境界、燃費、キロ程)を手元で見直してから、Claudeに該当セルの根拠を尋ねます。

加算額を料率(%)で示したいとき

ガイドラインの式は円額を出すもので、運賃に対する料率は出てきません。契約書や荷主への説明に「運賃の何%」と書きたい場合は、算定表の円額を運賃で割る列を派生として足します。

たとえば4t車・100kmの運賃を仮に30,000円とすると、D＝7.5の月の加算額125円は、運賃の約0.42%です。式は=加算額/運賃で、表示形式をパーセントにします。

注意したいのは、運賃が距離に比例しない区間です。運賃表の段が飛ぶ地点で料率が一定のままだと、加算額は燃料消費量(距離に比例)とずれていきます。料率で運用するなら、代表的な距離の行で円額との差を一覧にして、荷主と合意した範囲に収まるかを見ます。

運賃表の持ち方や、請求書との突合は運賃請求書を運賃表と突合する手順に載せています。燃料サーチャージは運賃表とは別項目なので、請求側では区分列を分けて照合します。

燃費と走行距離は実績から取る

燃費Eは、ガイドラインが荷主別・車種別に把握するのが望ましいとしています。同じ車種・同じ運行でも、貨物量やエコドライブの度合いで変わるためです。メーカーの公表値を入れてもよいのですが、実績に基づく値のほうが、荷主への説明が通りやすくなります。

実績から燃費を出すなら、給油量と走行距離の月次集計がもとになります。配送実績のCSVから車種別に集計する流れは、トンキロ法のCO2排出量を月次集計する手順の燃料法・燃費法の節と共通です。キロ程そのものを運行計画から出す場合は、配送ルート最適化の手順の距離行列が材料になります。

届出・協議・国交省の算出シートとの付き合わせ

算定表ができた後の手続きも、ガイドラインに載っています。

• 燃料サーチャージを新たに設定または変更したときは、トラック事業者の報告規則(第2条の2)に基づき、30日以内に運賃料金設定(変更)届出書を地方運輸支局等の窓口へ提出します
• 導入や改定の内容は荷主等と協議します
• 個々の事業者が導入やその内容を個別に決めること自体は、独占禁止法上の問題にならないとされています。事業者間や事業者団体で合意・決定すると問題になるとされているため、基準価格や価格帯を同業他社と揃える相談はしません
• 疑問点や適正取引の相談は、全国の運輸支局等の輸送部門が受け付けています

国土交通省のページには、燃料サーチャージ算出用のExcelファイルが2種類あります。貸切トラック用と、特別積合せ貨物運送を行う事業者だけが使える特積みトラック用です。複数のシートに分かれ、運賃料金設定(変更)届出書の記載例も入っています。ダウンロードしてから使う前提で、推奨環境は「WindowsXP以上のOSでMicrosoft Excel2002以上」と書かれています。

自作の算定表の検算に、この算出シートを使う方法があります。同じ仮の入力値を入れ、結果の円額が自作の算定表と一致するかを見比べます。ただし、ダウンロードしたファイルをClaude for Excelを動かしているブックに貼り込むのは避けます。サポートページは、外部由来のテンプレートやベンダーのファイルには、アドインを操作して情報を抜き出させたり記録を書き換えさせたりする隠し指示が含まれうると警告し、信頼できるスプレッドシートでだけ使うよう求めています。算出シートは別のブックで開き、結果の数値だけを手で見比べます。

Claude for Excelで使えない環境と、任せない判断

サポートページが挙げる対象外は次のとおりです。

• Excel 2016・2019の永続ライセンス版とボリュームライセンス版、iPad版、Android版
• Microsoft 365版でも、共有ランタイムの条件を満たさない古いビルド
• データテーブルと、マクロ・VBAの操作

対応する環境は、Excel on the web、Microsoft 365のWindows版(ビルド16.0.13127.20296以降)、Mac版(バージョン16.46以降)です。

人が決める部分は、算定表の外にあります。基準価格をどの時点の価格にするか、価格帯の幅をいくらにするか、端数をどう扱うかは、荷主との協議と契約で決まる内容です。Claudeに任せられるのは、決まった前提を式にして全行を計算し、期待値との差を並べるところまでです。

まとめ

算定表の山場は、式そのものより、入力値を式から切り離す設計と、端数が.5になる行の検算にあります。基準価格・きざみ幅・燃費・端数処理を入力セルに集め、手計算6ケースと差を取ってから荷主に出せば、価格帯が変わっても作り直しは入力セルの更新で済みます。