鉄道会社ごとの運賃比較表をExcelで作成する場合、単純に距離と運賃を比較するだけでは正確な比較が難しいことがあります。鉄道運賃は一定距離ごとの区間制になっているため、同じ距離でも会社によって設定されている運賃区間が異なるためです。この記事では、Excelの関数を使って区間の間の運賃を補間計算し、条件付き書式で安い運賃や高い運賃を分かりやすく表示する方法を解説します。
鉄道運賃の比較が単純な比較では難しい理由
多くの鉄道会社では、走行距離に対して1km単位で細かく運賃を設定しているわけではありません。例えば「10kmまで」「20kmまで」「30kmまで」のように区間ごとに運賃が決められています。
そのため、比較したい距離が運賃表に存在しない場合、単純に空白として扱うと正しい比較ができません。
例えば、A社が20kmで300円、30kmで450円という設定の場合、25km地点では運賃表に直接の値がありません。このような場合は、前後の運賃から中間値を推測する「線形補間」を利用すると比較しやすくなります。
Excelで運賃を補間計算する基本的な考え方
補間計算では、目的の距離の前後にある2つの運賃データを利用します。
計算式は以下のようになります。
予測運賃＝直前の運賃＋（直後の運賃－直前の運賃）×（目的距離－直前距離）÷（直後距離－直前距離）
例えば、20kmで300円、30kmで450円の場合、25kmの運賃は以下のように計算できます。
300＋（450－300）×（25－20）÷（30－20）＝375円
このように、実際の運賃設定がない距離でも比較用の参考値を作成できます。
Excel関数で前後の運賃を取得する方法
前後の距離に対応する運賃を取得するには、XLOOKUP関数やINDEX関数とMATCH関数の組み合わせが便利です。
例えば、A列に距離、B列に運賃を入力している場合、目的距離がD2セルにあるときは以下のような考え方になります。
直前の距離は「D2以下で最大の距離」、直後の距離は「D2以上で最小の距離」を取得します。
Excelの新しいバージョンでは、XLOOKUP関数を使って検索方向を指定することで、前後の値を比較的簡単に取得できます。
例：直前距離
XLOOKUP(D2,A:A,A:A,,,-1)
例：直後距離
XLOOKUP(D2,A:A,A:A,,1)
取得した距離と運賃を利用して補間計算用のセルを作成すると、自動的に比較表を更新できます。
補間した運賃を使って条件付き書式で色分けする方法
各鉄道会社の予測運賃を計算できたら、条件付き書式を利用して最安値や最高値を視覚的に表示できます。
例えば、比較対象の運賃がC2:F2に入力されている場合、最安値だけを色付けする条件は以下のような数式で設定できます。
=C2=MIN($C2:$F2)
この条件を範囲全体に適用すると、その距離で最も安い会社のセルだけに色を付けられます。
逆に最高値を表示したい場合は、MIN関数をMAX関数に変更します。
=C2=MAX($C2:$F2)
これにより、距離ごとの価格差を一覧表で簡単に確認できます。
運賃データが存在しない会社を除外する方法
質問のように「その距離で運賃設定がない会社には色を付けない」という処理も可能です。
補間値を作成したセルとは別に、実際の運賃設定が存在するかを確認する列を作る方法があります。
例えば、COUNTIF関数を利用して元の運賃表に対象距離が存在するか確認できます。
条件付き書式では、実際の設定値がある場合だけ色付けするように条件を追加します。
例：
=AND(COUNTIF(運賃表の距離範囲,比較距離)>0,C2=MIN($C2:$F2))
このように条件を複数組み合わせることで、補間値は比較対象に利用しつつ、表示上は実際の運賃設定がある場合だけ強調することもできます。
鉄道運賃比較表を作る場合のおすすめExcel構成
管理しやすい表にするには、距離データ、各社の運賃表、比較結果を分けて作成すると便利です。
例えば以下のような構成にすると、後から鉄道会社を追加する場合も修正しやすくなります。
| 項目 | 内容 |
|---|---|
| 距離一覧 | 比較したいkm数 |
| 運賃データ | 各鉄道会社の公式運賃表 |
| 補間計算 | 存在しない距離の推定運賃 |
| 比較結果 | 条件付き書式で色分け |
このように役割ごとに分けることで、運賃改定があった場合でもデータ部分だけ変更して対応できます。
まとめ｜Excelの補間計算と条件付き書式で複雑な運賃比較が可能
鉄道会社の運賃比較では、区間制による運賃設定の違いがあるため、単純な数値比較では正確な判断ができません。
ExcelのXLOOKUP関数やINDEX関数などで前後の運賃を取得し、線形補間によって比較用の値を作成することで、任意の距離で比較できます。
さらに条件付き書式を組み合わせれば、最安値や最高値を自動で色分けでき、複数の鉄道会社の運賃比較表を見やすく管理できます。
コメント