SuicaやモバイルSuicaで電車やバスなどの交通費を払っている、弥生会計で会計処理をしている、この2つを満たすなら、Suicaの履歴を弥生会計に取り込んでしまいましょう。エクセルのテンプレートを最初に1回作り込んでおけば、あとは毎回コピペするだけで仕訳が完成します。

SuicaとモバイルSuicaを弥生会計に取り込む4つの手順


↑弥生会計にSuica履歴を取り込んだ画面

SuicaやモバイルSuicaの履歴を弥生会計へ取り込むには4つの手順で進めます。Suicaインターネットサービスで履歴をダウンロード、Suica専用のエクセルテンプレートに貼り付け、CSVファイルに保存、弥生会計に取り込み、この4つです。本記事では専用のエクセルテンプレート作りについて説明します。4つの流れについては、別の記事をご覧になってください。

→モバイルSuicaは作成中

SuicaとモバイルSuicaを弥生会計に取り込むためのエクセルテンプレート作成方法

SuicaやモバイルSuicaを弥生会計への取り込みをスムーズに行うため、エクセルテンプレートを作っていきます。まっさらなエクセルから、Suica履歴のコピペで弥生会計インポート形式が出来上がるところまで作り上げるので、がんばってやってみましょう。

Suica専用のエクセルテンプレートの全体像

まず、完成版を先に確認しましょう。エクセルシートは2枚で、一つがSuica履歴コピペ用、もう一つが仕訳出力用です。

↓Suica履歴コピペ用

↓仕訳出力用

オレンジ色の部分にSuica履歴を貼り付けることで、日付や金額や移動期間はもちろん、勘定科目や消費税区分なども自動で設定されます。ここまで作り込んでおけば、基本的に弥生会計へ取り込みしたあとに仕訳の修正はありません。

かなり楽できると思いませんか?

Suica履歴コピペ用シートの作成手順

まずは、Suica履歴をコピペするエクセルシートを作っていきます。さきほどはオレンジのことだけ書きましたが、4色で説明します。

オレンジ・・・Suica履歴を貼り付けるスペース
グリーン・・・履歴から摘要と勘定科目を自動出力するスペース
ブルー・・・勘定科目用のデータベース
グレー・・・消費税区分用のデータベース

だいぶやることが多いと感じると思いますが、一度完成させれば後はコピペの繰り返しです。Suicaでうまくいけば、預貯金口座やクレジットカードなどでも流用できますので、試してみましょう。

オレンジ部分

ここは、Suica履歴を貼り付けるスペースです。とりあえず5つくらい履歴を貼り付けておきましょう。

なお、SuicaやモバイルSuicaの履歴は、Suicaインターネットサービスでダウンロードできます。

グリーン部分

ここは、履歴から摘要と勘定科目を自動出力するスペースです。

まずJ列の摘要から設定します。SuicaカードやモバイルSuicaの履歴は、次のようなルールで表示されています。(オレンジ参照)

月/日・・・SuicaやモバイルSuicaの利用日
種別・・・「入」は乗車、「オート」はオートチャージ、「物販」は買い物
利用場所・・・乗車駅、降車駅、オートチャージした駅。「地」は地下鉄

摘要には乗車区間を表示させたいので、C列とE列の利用場所をつなげて表示させてみます。


J2=C2&”/”&”E2
J3=C3&”/”&”E3



そうすると、グリーンの摘要が表示されます。乗車した場合は乗車区間が分かります。Suica履歴に表示されない物販は、「/」で弥生会計にインポートしておいて修正すればいいです。

今回、「Suicaチャージ」という勘定科目を作りました。こんな科目は普通はありません。でも、勘定科目と摘要を見て「地下鉄池袋駅でSuicaチャージ」という内容が分かりますよね?経理の手間を省くにはアリだと思っています。

なお、K列の相手勘定科目は、ブルー部分のデータベース作成時に設定します。

ブルー部分

ブルーで表示されている部分は、勘定科目用のデータベースです。

Suica履歴の「種別」は次のように区分されます。

「物販」・・・買い物
「入」・・・電車やバスへの乗車
「オート」・・・オートチャージ
「現金」・・・現金チャージ(履歴にはない)

この4種類に対して、勘定科目を設定していきます。

「物販」・・・買い物→消耗品費
「入」・・・電車やバスへの乗車→旅費交通費
「オート」・・・オートチャージ→Suicaチャージ
「現金」・・・現金チャージ→事業主借

今回はこのようにしましたが、設定はアレンジしてくださいね。「物販」を例に考えてみてもいろいろ考えられます。

・打ち合わせメイン・・・会議費
・私的な買い物メイン・・・事業主貸
・決められない・・・仮払金

種別と勘定科目の組み合わせは、ブルーのM3セルからN6セルに手入力します。ここまでで、次の流れが作れます。

オレンジ部分にコピペする

ブルー部分のデータベースから勘定科目を探す

グリーン部分K列の「相手勘定科目」に出力する

あとは、グリーン部分K列に関数を設定します。K3セルの関数は次のとおりです。

=IF(ISERROR(VLOOKUP(B3,$M$3:$N$6,2,FALSE)),”未確定勘定”,VLOOKUP(B3,$M$3:$N$6,2,FALSE))

すごく長いですが、なんとなく意味が分かればOKです。「データベースから勘定科目を探し、見つからないときは未確定勘定にする」、こんな指示を出しています。

B3セルが「入」となっている

ブルーでは「入」は「旅費交通費」となっている

K3セルに「旅費交通費」と表示させる

グレー部分

ここは、消費税区分用のデータベースです。Suicaで消耗品を買ったなら消費税の課税取引、オートチャージしただけなら消費税対象外、これをデータベースにします。ブルー部分と考え方は同じです。

エクセルのP3セルからQ6セルに、勘定科目と消費税区分を入力します。

消費税の税区分の記載は弥生会計インポート形式に合わせています。簡易課税を選択していたり、税率が10%になったりすると、表示が変わるので注意が必要です。

この消費税区分は、とりあえずデータベース作成だけで終了です。出力用シートで関数の設定をします。

出力用シートの作成手順

Suica履歴コピペ用とは別に出力用シートを作ります。

シートの追加と大雑把に形式を整える

まず、エクセルの下部でシートを追加します。

今回は弥生会計を使いますので、弥生会計インポート形式に合わせてシートを作っていきます。なお、説明が分かりやすいように、出力用シート1~2行目にタイトルと番号を入れています。

SuicaやモバイルSuicaの履歴の内容に関係なく、確定している部分は先に埋めてしまいます。

1.識別フラグ・・・2000
20.タイプ・・・0
25.調整・・・no

それぞれ意味がありますが、気にしなくていいです。どうしても気になるときは弥生会計公式サイトでご確認ください。

次から関数を設定していきます。

4.取引日付

取引日付はSuica履歴から関数で引っ張ります。「=」で繋げばOKです。

5.借方勘定科目

借方勘定科目に入るのは、大きく分けて2つです。

A.Suica
B.Suica以外・・・消耗品費、旅費交通費、オートチャージ、未確定勘定

Bについては、Suica履歴コピペ用シートで関数を指定しましたので、ここではAかBかを関数で指定します。

E3=IF(Suica履歴コピペ用!G2<0,Suica履歴コピペ用!K2,"Suica")

ちょっと難しく見えますね。これはオレンジ部分のSuica残高の増減に応じて勘定科目を指定しています。

〜がマイナス・・・Suica残高が減った→Bが入る
〜がプラス・・・Suica残高が増えた→Aが入る

物販や乗車・・・旅費交通費や消耗品費
チャージ・・・Suica

関数の意味が100%分からなくても、勘定科目が正しく引っ張れればOKです。

11.貸方勘定科目

弥生会計インポート形式の順番通りではありませんが、説明の便宜上、貸方勘定科目を先に確認しましょう。

K3=IF(Suica履歴コピペ用!G2>0,Suica履歴コピペ用!K2,”Suica”)

借方勘定科目で設定した関数の「<」を「>」に変えただけです。

8.借方税区分

借方勘定科目に応じて、消費税の対象かどうかを判断してあげます。Suica履歴コピペ用シートで作ったデータベースを利用します。

H3=IF(ISERROR(VLOOKUP(E3,Suica履歴コピペ用!$P$3:$Q$6,2,FALSE)),”対象外”,VLOOKUP(E3,Suica履歴コピペ用!$P$3:$Q$6,2,FALSE))

E3セルが「旅費交通費」となっている

グレー部分では「旅費交通費」は「課税仕入込8%」となっている

H3セルに「課税仕入込8%」と表示させる

14の貸方税区分もいっしょです。

9.借方金額

金額は、Suica履歴(オレンジ)から直接引っ張ります。

「差額」がプラス・・・そのまま使う
「差額」がマイナス・・・〜にマイナス1を乗じてプラスにする

I3=IF(Suica履歴コピペ用!G2<0,Suica履歴コピペ用!G2*-1,Suica履歴コピペ用!G2*-1)

「差額」がプラスでもマイナスでも借方金額を引っ張ることができます。

なお、15の貸方金額も同じ金額なので、「=」でつなぎましょう。

17.摘要

摘要は、Suica履歴コピペ用シートで加工しましたので、これも「=」でつなぐだけです。

確認作業

オレンジ色の貼り付けたデータから、しっかり仕訳が出来上がっているか確認しましょう。オートチャージ、現金チャージ、乗車、物販、全てのパターンを確認しておけば完璧です。

Suicaカード&モバイルSuica、弥生会計インポート用のテンプレート作成方法、まとめ

SuicaカードやモバイルSuicaの履歴を弥生会計にインポートするためのテンプレート作りを説明しました。ひとり社長や個人事業主などは、交通費のほとんどがSuicaなんてこともあります。わざわざ弥生会計に手入力するなら、テンプレートを作ってインポートしたほうが楽できます。

ご質問やご相談はこちらのフォームからお願いします。
無料なので、気軽にお声がけくださいね。

>>コンセプト&コンタクトページはこちらです。