毎日つけた方が楽?Excel Vlookup関数による交通費の楽々記録

交通費の精算はめんどくさく、つい後回しにしがちです。
私は毎日記録しています。
活用しているのはやはりExcelです。

 

 

モバイルSuicaがあれば･･････

昔、こういう記事が書きました。

• ・Suicaによる交通費の精算　その1　（カードの読み取り）
• ・Suicaによる交通費の精算　その2　（精算書の作成）

モバイルSuicaがあれば、ネットで履歴を見ることができるので楽です。

ところが、iPhone1本にしてからこの方法が使えなくなりました。
駅で履歴を印刷しても加工はできません。
印刷した履歴に書き込むのもめんどくさく、そもそも、駅に印刷しに行くのがめんどくさいです。
カードを読み込む機器をPCにつけて履歴を見ることもできますが、接続する手間もかかります。

モバイルSuicaも含めて、これらの方法は表示できる履歴数が限られているのもネックです。
月に2回くらい履歴を取り込む必要があります。

専用のサービスやソフトも追加コストがかかります。

Suicaのチャージ金額を全額経費に落とす方法もあるのでしょうが、Suicaで食材を買ったり本を買ったりもするので、私の場合は使えません。

iPhone1本にしてから最初の年は、カレンダーを見ながら履歴を作りました(^_^;)

 

 

交通費は、毎日つけた方が楽

カレンダーを見ながらはさすがにめんどくさいので、会計データと同様に毎日記録するようにしました。
やはりこちらの方が楽です。

人間にとって（私にとって？）、めんどくさくつらい業務は「思い出す」ことです。
何かを思い出しながら、作業をするのはストレスがたまります。
それなら、記憶の新しいうちにやってしまった方が楽でしょう。

ただし、毎日やる業務は徹底して効率化して手間がかからないようにしたいものです。

こういうときにやはり役立つのはExcelです。

 

 

Excel Vlookup関数での交通費記録方法

私がやっている方法は次のとおりです。
毎朝のルーティンとして必ずやります。

同様の例は、本にも書いています(^_^)

 

次のようなフォームで、［日付］、［区分］、［行き先］、［片道・往復の別］を入力します。

※［区分］は、経費にするかしないかを入力しています。
経費にするものは空欄、プライベートのものには「1」を入力し、集計時に使います。
私は家計もビジネスもExcelで記録をつけているのでこういう使い方をしています。

入力した［行き先］をキーとして、［交通機関］、［発］、［着］、［金額］が連動されるしくみです。
（Vloopup関数を使っています。）
［行き先］は駅名でもいいですし、具体的な訪問先、会社名でもかまいません。
［交通機関］、［発］、［着］は必要なければ、なくても大丈夫です。

 

①［行き先］の入力

まず［行き先］を入力します。
例として、新宿にオフィスがあり、そのオフィスから水道橋まで行ったとします。

この時点では、VLOOKUP関数を入れてあるセルにエラーが出ます。
右側のデータベースに何も入っていないからです。
こういった方法を使わずに表にそのままデータを入力し、コピーして使う手もありますが、毎回めんどくさくミスが起きる可能性も高くなります。
そのため、このような方法を使っているのです。

 

 

②データベースにデータを入力

右側に次のデータを入力します。
［行き先］水道橋　［交通機関］JR　［発］新宿　［着］水道橋　［金額］　160

［金額］には片道の金額を入れます。
［発］と［着］は入れ替わってもかまいません。どこからどこまでというのが分かれば十分でしょう。

データを入力すると、VLOOKUP関数により表へデータが連動されます。
表の［金額］は、D列が空欄だと往復の金額（データの［金額］（N列）×2）、D列に「片道」と入力すると、片道の金額（データの［金額］（N列））が表示されます。

次の行も同様に入力してみます。

 

③データベースにある［行き先］の場合

次の行の［行き先］に水道橋と入力すると、自動的に［交通機関］、［発］、［着］、［金額］が表示されます。
すでにデータベースに入力されているからです。
一度データベースに入力しておけばいいので、使えば使うほど楽になっていきます。
人にもよるでしょうが、行く場所のパターンはだいたい決まっているはずです。

 

④応用

例えば、プライベートで新宿→渋谷→表参道→渋谷と移動したとします。
この場合、次の3つを入力します。
渋谷　、渋谷→表参道、表参道
いずれもD列［片道・往復］に「片道」と入力します。
［行き先］は、新宿を起点とする場合、「新宿→渋谷」とせずに省略して「渋谷」としています。
新宿を起点としない場合は「渋谷→表参道」というように入力します。

 

⑤集計・会計ソフトへの入力

月に1回、又は年に1回、決算時にピボットテーブルで集計して、その結果を入力します。
私は、会計ソフトに取り込む仕組みを作っています。
毎月の金額がそれほど大きくないのであれば、年1回でもかまわないでしょう。

この表だと3月に使った交通費は、プライベートのものが620円、ビジネスのものが940円となります。
プライベートのものは記録するしないはもちろん自由です。

サンプルをダウンロードできるようにしました。
入力しやすいしくみ（入力規則）や数式が自動的にコピーされるしくみ（テーブル）、そしてピボットテーブルを取り入れてますので、VLOOKUP関数とともに研究してみてください。
http://bit.ly/w6IVaq

※2003以前でも開くことはできますが、一部の機能が制限されます。

 

 

---

---

---

---

【編集後記】
昨日は、午前中にExcelのマンツーマンレッスン、午後に単発の税務相談でした。
なんだかんだと1日しゃべりづくしでしたね･･････。
お役に立てたようで何よりです(^_^)