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




みなさん、こんにちは。
埼玉県ふじみ野市でITサポートを行っているSORAの関口です。

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

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

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

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

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

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

1.目次

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

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

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

3.初回準備

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

3.1 スマホの準備

今回ご紹介する方法は、スマホの「NFC/おサイフケータイ機能」を利用してSuica等ICカードリーダーの交通費履歴を読み取ります。よって、お使いのスマホが「NFC/おサイフケータイ機能」が備わっていること、有効になっていることを確認してください。ここではGalaxyNote3(Andoroid)の画面で解説していきます。

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

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

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

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

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

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

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

3.2 Excelのマクロを記録

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

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

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

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

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

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

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

最初に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列に数値として貼り付けています。

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

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

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

4.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で履歴を吐き出しておくなど、マイルールを決めておくことで、月末の締め処理が楽になると思います。

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

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

複式簿記の場合、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

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

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

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

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

6.まとめ

ここまで、「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カード

7.今回のオススメアイテム

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
















 


ネットワークスペシャリストのITサポート
ITのことでお困りではないですか?

「ネットにつながらない」「メールが送信できない」などのパソコン・スマホトラブルから、ITの便利な使い方など、お客様のITに関するすべてのことをサポートします。リモートサポートにも対応していますので、お気軽にお問い合わせください。








KindlePaperwhiteの活用術
KindlePaperwhiteのすすめ

「Kindle Paperwhiteのすすめ」では、当ブログで紹介してきたPaperwhiteで自炊した本を読む方法をはじめ、Paperwhiteハイライト機能を活かした記憶に残る読書術を【100円】で解説しています。




コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

ABOUTこの記事をかいた人

ネットワークスペシャリストと心理カウンセラーの資格をもつ。

埼玉県ふじみ野市を中心としたITやパソコンの訪問サポート及び、心の変化を実感できる心理カウンセリングを行っております。

「複雑なものをシンプルにわかりやすく」をモットーに、お客様の目線でITと心のサポートをします。

「心理」についてのBlogも日々書いておりますので、こちらもご覧ください。
心理カウンセリング空