フリーランス、ひとり社長の場合、事業のお金もプライベートのお金も合わせてすべて自分のお金と考えることができますが、両方の支出について経理することが重要です。

モバイルSuica履歴から、事業とプライベートの交通費をまとめて集計する方法をご紹介します。

目次

モバイルSuica履歴をExcelに落とす

まずは、モバイルSuicaにログインします。

JR東日本：モバイルSuica＞ログイン

該当のSuicaを選択して、「次へ」をクリックし、「Suicaご利用状況照会」の「SF（電子マネー）利用履歴」をクリックします。

そうすると、このように履歴が出ますので、マウスでドラッグしてコピー（control+C（Macは⌘+C））します。 

これをExcelに貼り付けます。

control+Cで貼り付けたあと、Ctrl+Mで貼り付け先の書式に合わせると見やすくなります。

ちなみに、2020年分を2021年になってから集計する、という具合に年をまたぐ場合、Excelでは実際に入力した年が勝手に付与されてしまいます。

そんなときは、列全体を選択してから、Ctrl+Hと押し、「検索する文字列」に「2021/」、「置換後の文字列」に「2020/」と入力し、「すべて置換」をクリックします。

こうすれば、実際に支出した年度のデータに変換することができます。（置換したら、ボックスは閉じてしまって大丈夫です。）

プライベートの交通費・事業の交通費・その他の支出にわける

B〜E列を見ながら、プライベートの交通費、事業の交通費、その他の支出に分類していきます。

記号は何でも良いのですが、例えば、

• プライベート→p
• 事業→j
• その他（交通費以外）→空欄

とし、H列に入力していきます。

ちなみに、確定申告で医療費控除の予定があれば、医療費としての交通費の集計もしておいた方が後々楽です。その場合は、「i」などの記号を付しておきます。

（分類は適当です）

ピボットテーブルで集計

あとはピボットテーブルで集計するだけです。

Windowsならalt+N+Vと順番に押して、エンターキーを押します。

「ピボットテーブルのフィールド」で年月日・金額・記号にチェックを入れ、例えばこのように配置します。

このままではだいぶイケてない表ですので、日付のどこかのセル（例えば、A4）を右クリックし、「グループ化」をクリックします。

「開始日」「最終日」を入力して、単位で「月」を選べば、スッキリします。（数字のセルを選択して、ctrl+shift+1を押すと桁区切りになります。）

これで集計自体は終わりです。

この方法を使えば、Suica履歴に限らず通帳やクレジットカード明細など何でもプライベート分や事業分をまとめて集計・分析できます。

余談ですが、この集計は1ヶ月に1度を目処にやった方が良いです。1週間に一度ですと多すぎですし、1ヶ月超放置してしまうと、「あれ、これは何しに行ったときだっけ？」と、内容を忘れてしまい、分類が面倒になってしまいます。

+α　モバイルSuica履歴を会計ソフトに取り込む

分析をするだけなら上記のステップで終わりなのですが、会計ソフトにモバイルSuica履歴を取り込むことも考えてみます。

この場合、B〜E列に分かれてしまっている摘要をひとつにまとめる必要があります。

「Sheet1」に戻り、たとえばK列に「=CONCATENATE(B1,” “,C1,D1,” “,E1)」と入力します。（半角スペースは見やすさのために入れただけですので、あってもなくても大丈夫です。）

あとは、A、G、H列をそれぞれ連動させておき、データの数だけ算式を下にコピーします。

このデータを事業用分のみソートし、会計ソフト取り込み用のシートにコピペするなり、算式で連動させておけば、会計ソフトへ取り込むことができます。

マネーフォーワードや弥生会計への取り込みは税務コンサルティングでご提供しています。

Suicaをプライベート用と事業用をわけていれば経理上は楽なのですが、2枚持つといちいち財布やカードケースから取り出す必要があり、iPhoneなどに入れておサイフケータイとして使う場合には2枚持ちは現実的ではないかと思います。

プライベート・事業共用のSuicaを使っている場合は、会計ソフトに連携させるよりも、一度Excelで集計して事業用分だけ会計ソフトに取り込む方が、プライベート・事業ともに集計できて便利です。

実際に、僕もこの方法で経理しています。