ついに2019年も終わりが近づいてきました。決算月という会社も多いと思います。

月末になると営業さんは交通費の精算などに追われているという方も多いのではないでしょうか。

社内の交通費の精算はみなさんどのようなツールを使われていますか？何かしら有料のサービスを利用されている、という方もいらっしゃると思いますが、中には無料ということでエクセルやスプレッドシートで管理している会社は多いのではないでしょうか？

その場合に面倒なのが、乗車駅から降車駅までの運賃の計算です。スプレッドシート上にどこの駅からどこの駅まで乗ったかを記入して、そのあとに路線検索のサイトで運賃を検索して、、、

1日だけであれば大した工数もかかりませんが、営業さんだとこれを毎日行わないといけないとなると、なかなかの工数になりますよね。

私自身、営業交通費の精算だけで毎月そこそこの工数を取られており非常に苦労していたので、これをなんとか解決したい、ということで、スプレッドシート上で動くそれっぽいシステムを開発してみることにしました。

スプレッドシートの関数（多分エクセルでもほぼ同様でいけます）と路線検索のAPIを使いました。正直スプレッドシートの関数についてはそこまで詳しくないので完璧な作り方ではないかもしれませんが、ほぼコピペで使えると思うので、システム開発の経験がない方でも可能です。

前提

・スプレッドシート上で動くこと
・外部サービスのAPIを使用

開発方法

APIの申し込み

今回は「駅すぱあと」という路線検索サイトのAPIを使用しますので、下記URLより申し込みください。フリープランであれば無料で利用可能です。普通に交通費を出すだけであればフリープランで問題ありません。

https://ekiworld.net/free_provision/index.php

申し込みから1,2営業日以内にはAPIキー発行の連絡が届くと思います。

スプレッドシートへの記入

APIキーが発行されたら早速スプレッドシートに諸々関数を入力していきたいと思います。

セルの位置などについては、とりあえず今回作成した内容で書いていますが、皆様の都合に合わせて書き換えていただいて大丈夫です。

①まずメールにて届いた「APIキー」をB2セルに入力します。

②乗車駅の駅名をB4、降車駅の駅名をC4、乗継駅の駅名をD4にそれぞれ入力します。

③B5のセルに

と入力してください。（乗継駅がありの場合と無しの場合をIF文で分岐させています）

すると「http://api.ekispert.jp/v1/xml/search/course/light?key=*******&from=****&to=****」といった内容のURLが表示されるかと思います。

④B6のセルに

と入力してエンターキーを押すと、「https://roote.ekispert.net/ja/result?arr=…」といった内容のURLが表示されます。

こちらのURLにアクセスするとB4、C4に入力した駅間の運賃等が表示されたページが表示されますので、あとはこのページから運賃情報の箇所のみをスクレイピング（自動取得）するという形で作成していきます。

⑤B7のセルに

と入力してください。

そうするとセル内に「15:14 ⇒ 15:16（2分）」「 乗換:0回」「 片道:130円」といった内容（乗車駅・降車駅によって表示内容は変わります）が表示されるかと思いますので、このうちの「130円」という箇所のみ抜き出して表示する、という形にしたいと思います。

⑥B8のセルに

と入力してください。そうすると「130円」という文字だけが表示されます。「円」も消したい場合はB9のセルに

と入力すれば、「130」という数字だけが表示されるようになります。

 

完成

こちらで完了になります。実際のスプレッドシートのイメージを置いておきます。

あとは、乗車駅・降車駅のところを変えると自動で金額が変更されるようになります。こちらの内容を現在使われているスプレッドシートのセルの位置に合わせて調整すると運賃の自動計算ができて、交通費精算の工数が大幅に削減できます。