Suicaの履歴をスマホで送信。3分で作る交通費精算書

みなさん、こんにちは。

埼玉県ふじみ野市でITサポートを行っているSORAの関口です。

今年の2月からプレミアムフライデーが導入されましたね。

月末の金曜日は15時に退社しよう！という夢のような取り組み。月末の金曜日に15時に退社したくても、月末の締め処理でそれどころではないというのが、ほとんどではないでしょうか。

私も会社勤めをしていから頃の月末は、日報のまとめや勤怠管理や交通費精算書の作成など、月末締の資料作成作業が多かったです。

そのなかでも、交通費精算書はいつも時間がかかっていました。

電車を使う度に日々交通費精算書に入力をすればいいのですが、ズボラな私は月末にまとめ交通費精算書を作るため、月末になると手帳を見ながらその月に行った訪問先を思い出し、乗換え案内サイトでひとつひとつ電車代を調べながら、エクセルで交通費精算書を作成をしていたので、かなり時間がかかっていました。

月末のルーティンの締め処理は簡略化したいところですね。

そこで、今日は「スマホ」と「エクセル」を連携させて、3分で交通費精算書を作成する方法をご紹介します。皆様の締め処理の効率化に役立てば幸いです。

3分で作る交通費精算書の完成イメージ

まずは、下記の動画をご覧ください。

今回のブログでは、スマホのNFC/おサイフケータイ機能と『ICカードリーダー』アプリ、Excelのマクロ機能を使用して、下のような交通費精算書を3分で作る方法をご照会します。

初回準備

まずはスマホ側の環境準備とExcelのマクロ作成方法をご照会します。

スマホの準備

今回ご紹介する方法は、スマホの「NFC/おサイフケータイ機能」を利用してSuica等ICカードリーダーの交通費履歴を読み取ります。

よって、お使いのスマホが「NFC/おサイフケータイ機能」が備わっていること、有効になっていることを確認してください。

ここではGalaxyNote3（Andoroid）の画面で解説していきます。

「NFC/おサイフケータイ機能」を有効にします。

「Playストア」から『ICカードリーダー　byマネーフォワード』をインストールします。

『ICカードリーダー　byマネーフォワード』を起動し、Suicaの履歴を読み取ります。

しばらくすると、現在のSuicano残高と、過去20件分の利用履歴を読み取ります。赤丸で囲った矢印をタップします。

CSV形式で保存をタップします

CSVファイルをパソコンに送ります。

今回はメールで自分宛に送信しましたが、Dropboxなどのオンラインストレージでも大丈夫です。

スマホ側の準備作業は終了です。

Excelのマクロを記録

スマホで送信したCSVファイル「例：2017-07-05.csv」をパソコンのデスクトップ等に保存します。

続いて新規セクセルファイル「例：交通費精算書マクロ」を作りシートを開く

デスクトップのCSVファイル「例：2017-07-05.csv」を開き、20件の履歴データのセルをコピーする

別ウィンドウで開いている「交通費精算書マクロ」シートにそのまま貼り付ける

ここからのExcel操作をマクロとして登録をします。Excelの「表示」タブの右側にある「マクロ」アイコンをクリック。続いて「マクロの記録」をクリック

下図が表示されるので、マクロ名を「交通費精算」マクロの保存先を「作業中のブック」にして「OK」をクリック。Excel操作のマクロの記録がはじまります。
[aside]

Excel操作に自信がない方は、一度以降の手順を確認してから、マクロ記録を行なってください。

[/aside]

最初にA列に新規列を挿入し、下図のように直近の利用に20を振り、降順で番号を振る

A列を選択し、「データ」タブの「昇順」をクリックし並べ変える

昇順で並べ終えた後、A列を削除

日付データを「月/日」表記にするため、日付列をクリックし右クリック。「セルの書式設定」から「表示形式タブ」、「ユーザー定義」を選択し種類欄を「mm/dd」と入力しOKをクリック

続いてB列（内容列）の横に、5列（Gまで）列を新規挿入

続いてB列を範囲選択し、「データ」→「区切り位置」を選択

区切り位置ウィザードが開きます。「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選択し「次へ」

区切り文字の「スペース」にチェックを入れて「次へ」をクリック

下図が表示されるので「完了」をクリック

B列が5分割されるので、続いてB列～G列の間で不必要なセルを削除する

私は発駅と着駅がわかればいいので2列のみを残し、後の列は削除しました

続いてD列（金額列）のマイナス表記プラスに変える為、E列に「D列＊－1」式を入力

上記の数式を全ての行に展開する

続いて、E列をコピーし、隣の列に「値オプション」を指定して貼り付ける

注）

E列は、D列の計算結果が表示されているため、E列を消すとD列がエラーになってしまいます。そのためE列の隣のF列に数値として貼り付けています。

 

後は、社内の交通費精算書のフォーマットに沿って表を整えていきます。私は以下のようにフォーマットを整えました。

フォーマットを整えたら、マクロを「記録停止」します。

マクロ記録が無事に終わったら、ここ作成した表を一旦削除しファイルを上書き保存します。これで交通費精算書のマクロシートが完成です。

3分で交通費精算書を作成する

ここまで準備ができたら、後は3分で交通費精算書が作れるようになります。

スマホの「ICカードリーダー」で交通費履歴を読み取る。

履歴データをCSVファイル形式でパソコンに送信。（1分）

パソコンのデスクトップ等にCSVファイルを保存する。

続いて先ほど作成した「例：交通費精算書マクロ」Excelファイルを開き、CSVファイルのデータを貼り付ける。

「表示」タブの「マクロ」を選択。続いて「マクロの表示」を選択

交通費精算書マクロを選択し「実行」クリック

瞬時に20件分の履歴が表として作成されます。(2分）

「ICカードリーダー」は常に20件分のCSVが作成されますので、この後不要な行を削除していきます。

例えば6月分の交通費精算書を作成するのであれば、2行～11行までは不要ですので削除します。

訪問先等必要事項を入力した後、「6月度交通費精算書」として名前を付けて保存します。（3分）

注）

※「交通費精算書マクロ」ファイル名のまま上書き保存をすると、今月の表が残ってしまい来月が使いづらいので、必ず別名で「名前付けて保存」をしてください。

※「ICカードリーダー」は20件まで利用履歴がCSVでエクスポートされます。営業の方などは1週間で20件を越えてしまうと思うので、電車の利用頻度に応じて、この作業を繰り返し行なってください。とりあえず、3日1回CSVで履歴を吐き出しておくなど、マイルールを決めておくことで、月末の締め処理が楽になると思います。

 

個人事業主としての旅費精算費の効率化

私は個人事業主なので、交通費は旅費交通費として費用計上できます。

複式簿記の場合、Suica等ICカードリーダーの仕分けは下記のとおりになります。

■Suicaでオートチャージが発生したとき（クレジットで支払い）

借方科目	金額	貸方科目	金額
前払金	8,000	未払金	8,000

■Suicaで交通費を支払ったとき

借方科目	金額	貸方科目	金額
旅費交通費	500	前払金	500
旅費交通費	1,500	前払金	1,500

※前払金の残高が0円になると、Suicaでチャージが発生する。

■Suicaでチャージされた金額がクレジットで引き落とされたとき

借方科目	金額	貸方科目	金額
未払金	8,000	銀行口座	8,000

複式簿記を厳密に行なうと、旅費交通費の経費が発生する度に、経費入力登録をする必要があるのですが、電車を使う度に経費登録をするのは面倒ですね。

そこで、Suicaのチャージが発生した場合は、そのまま旅費交通費として一括計上します。

■Suicaでチャージが発生した場合（クレジット利用）

借方科目	金額	貸方科目	金額
旅費交通費	8,000	未払金	8,000

■Suicaでチャージされた金額がクレジットで引き落とされたとき

借方科目	金額	貸方科目	金額
未払金	8,000	銀行口座	8,000

旅費交通費の内訳・根拠として、「旅費精算書」を作っておけば問題ないようです。（※）

[aside]

※もし、私の認識が間違っていたら、コメント欄でご指摘いただけると嬉しいです。

[/aside]

私は日々の帳簿入力を『MFクラウド確定申告』で行なっていますので、Suicaのオートチャージが発生した場合の仕分けを下記のとおり「MFクラウド確定申告」に登録しておけば、あとば月末に今回の手順で旅費精算書を作成するだけです。

なお、旅費交通費のチャージをクレジット機能を利用して支払っている場合、そのクレジットカードでは「旅費交通費以外は使用しないという」マイルールがあると、月末の処理が楽になります。

まとめ

ここまで、「3分で作る交通費精算書」と題して説明をしてきましたがいかがでしたでしょうか。

Excelのマクロ作成作業に少し時間がかかりますが、マクロが完成後はスマホの「ICカードリーダー」でピッと履歴を読み取りパソコンに送信。CSVファイルから交通費精算書を直ぐに作成することができます。

今回は「Suica」を使用して説明をしましたが、「ICカードリーダー」は下記のICカードに対応しているので、同じ手順で利用履歴をCSVファイルでエクスポートできると思います。

ぜひ、月末の交通費精算書作成のときに使ってみてくださいね。

当ブログが、月末処理の効率化に少しでも役立てば幸いです。ここまでありがとうございました。]

【ICカードリーダー対応カード一覧】

• Suica
• PASMO
• ICOCA
• TOICA
• nimoca
• SUGOCA
• manaca
• PiTaPa
• Kitaca
• はやかけん
• SAPICA
• icsca
• PASPY
• IruCa
• NORUCA
• CI-CA
• NicoPa
• 嵐電
• hanica
• くまもんのICカード

今回のオススメアイテム

■個人事業主には絶対にオススメ！「MFクラウド」を使えば経理処理が楽になりますよ。