・Suicaによる交通費の精算その2 (精算書の作成)

2010年2月5日

URLをコピーしました！

昨日に続き，交通費の精算についての記事です。
Suicaの利用履歴をExcelで加工し，簡単に交通費精算書を作ることができます。
交通費のデータの取得
交通費のデータは，Suicaのサイト（最大50履歴）やカードリーダ（最大20履歴）で取得することができます。
残っている履歴に限度があるので，ある程度の期間ごとに取得しなければいけません。
私の場合，毎月，2回（月初と15日過ぎ）取得しています。

モバイルSuicaの場合は，サイトで取得します。

このサイト上で，データを選択してコピーし，Excelに貼り付けます。
そのまま貼り付けを行うと，色がグレーになってしまいますので，
最終的にセルの色を変えれば，大丈夫です。

その都度貼り付けておいて，月ごとのシート（例えば2010年1月だったら「201001」）に再度コピーして同じ月のものをまとめていきます。
前回の記事で挙げたように通常のカードタイプのSuicaでもデータを取得することができますが，データの構成が少し異なります。
今回はモバイルSuicaを使った場合の事例をご紹介します。

データの構成
データの構成は，左から「月/日」，「種別」，「利用場所」　「種別」　「利用場所」　「残額」です。
「種別」には，次のようなものが表示されます。
　・電車に乗った駅→【入】
　・電車から降りた駅→【出】
　・定期券の範囲を経由した場合→【定】　
　　例）新宿から飯田橋の定期がある場合に新宿から秋葉原まで電車に乗ったとき
　　・・・【定】飯田橋　【出】秋葉原
　・モバイルSuicaでチャージした場合→【VIEW】
　　※私はViewカードでチャージしているため，この表示です。
　・Suicaで商品を購入した場合→【物販】
利用金額の計算
上記のデータにはSuicaの利用金額は表示されません。
あくまで表示されるのは「残額」ですので，利用金額を計算する必要があります。
Gの列に次のような式を入力し，G列のそれ以降のセルにコピーします。
この例では，G2のセルに計算式が入っています。
前日の残高　6,781から　当日の残高6,621を差し引いた160円が計算結果です。
銀座から乗って，末広町で降りた場合の交通費が160円ということになります。

同様にすべての行の交通費を計算してきます。
※「残額」は，例えば1万円なら，\10,000と表示されます。
　Excel2007以外（Excel2002,2003など）では，これを文字列として認識してしまいますので，計算ができません。
　「\」を置換で消すなどの処理が必要です。
　具体的には残額の入っている列を選択して，Ctrlキーを押しながら「H」を押します。（メニューバー→編集→置換でも可）
　すると，次のようなボックスが表示されますので，検索する文字列に「]，置換後の文字列を空欄にして，すべて置換をクリックすると，「\」が消え，「残額」が数字として扱われます。（計算できるようになります。）
　この記事を書きながら，念のためExcel2002，2003で試してみたところ判明しました。
「物販」の取り扱い
前日のデータ構成で出てきた種別の「物販」をどう取り扱うかは，2つの方法があります。
1　「物販」のデータも集計して，交通費精算書とともに，経費精算書も作成する方法
2　「物販」のデータは集計せずに交通費精算書のみを作成し，経費については，領収書で処理する方法
種別が「物販」となるものは，領収書が発行されています。通常の立替経費と同様に処理することが可能です。
当事務所は，この後者の方法を使っています。
経営者や社員が立て替えた経費は，現金で払おうが，Suicaで支払おうが，個人のクレジットカードで支払おうが，処理は同じです。
「VIEW」の取り扱い
種別の「VIEW」は，チャージした場合に表示されます。
このチャージの考え方も2つあります。（チャージ時点では経費にしません。）
1　チャージは会社のお金とする方法
　チャージしたSuicaは会社のお金と考えると，個人の携帯やカードにチャージしたSuicaを会社が支払う方法です。
　経費の仮払と同じ考えです。
2　チャージは個人が一時的に立替えたお金とする方法
　チャージした時点では，会社の経費として使うのか，個人的に使うのかが確定していません。
　会社の経費として使ったもののみを経費として，後日精算するという方法です。
当事務所は後者の方法です。
データの抽出
作成したデータから，会社の経費であるもののみを抽出します。
具体的には，不要なものを削除していきます。
削除の前にあらかじめ，計算した利用金額をコピーし，値のみを選択して貼り付けておく必要があります。
数式で参照しているため，その参照先が削除された場合，数式にエラーが出てしまいます。
削除にはオートフィルタが便利です。
※オートフィルタ
メニューバーのデータ→フィルタ→オートフィルタ
特定のデータを一括して抽出し，削除することができます。

　
又は，削除しないで経費のみを表示させたまま，合計値を出し，それを精算書にしても大丈夫です。
私は経費の行に印（1でも＊でも何でもよい）をつけて，それをオートフィルタで抽出したものを精算書にしています。
罫線も引かず，見た目はこだわりません。明細として分かれば十分です。
　
会社で決められた様式がある場合
交通費の精算書として，会社で決められた様式がある場合は，それに合わせてアレンジする必要があります。
行き先・路線名を入力しなければいけない場合もあるかと思います。
手間をかけない
移動交通費（電車・バス）の総額を考えると，経営者の場合は特に，交通費の精算に時間をかけていられません。
稼働日や遠方への移動が多い場合は，もう少し多くなると思いますが，1日500円としても20日間稼働して1万円ですしね・・・。
手間をかけない方法をそれぞれ実践していただければと思います。
私は，記憶を呼び戻したり，その都度メモしたりするのが嫌なので，このような方法を使っています。
このモバイルSuicaの恩恵を手放したくなくて，iPhone導入をあきらめました。
2つ持つようにすればいいのでしょうけど・・。
カードタイプのSuicaだと，いつでもチャージができないのが難点です。
オートチャージは，改札でしかできないようですし。
しばらくはモバイルSuicaを使っていく予定です。