freeeはモバイルSuicaは直接連携していますが、カードタイプのSuicaは直接連携していません。交通費などの支払いをSuicaカードを使っている場合、freeeにSuica履歴を取り込んだほうが経理が楽になります。freeeへのインポート専用のエクセルテンプレートを作って、効率的に取り込みましょう。

Suicaカードの履歴をfreeeに取り込む手順【エクセルテンプレートの前に】

Suicaカードのfreeeへの取り込みは、3つの手順で進めます。Suica履歴ダウンロード、エクセルテンプレートへのコピペ、freeeでの取り込み作業、この3つです。今回はエクセルテンプレート作りを説明するため、一連の流れを確認したいときはこちらの記事をご覧ください。

Suicaカード履歴をfreeeに取り込むためのエクセルテンプレート作成方法

ここから、Suicaカード履歴をfreeeに取り込むときに使うエクセルテンプレートを作成していきます。

エクセルテンプレートは2シート

エクセルテンプレートは、Suica履歴を貼り付ける入力シート、freeeへのインポートに必要な情報を抽出した出力シートの2枚で構成されます。

↓入力シート

↓出力シート

今の段階では、エクセルテンプレートはシートが2枚ってことだけ分かれば大丈夫です。

入力シート

入力シートは、次の構成となっています。説明を分かりやすくするために色分けしました。

オレンジ・・・・貼り付けスペース
グリーン左半分・・・・摘要
グリーン右半分・・・Suicaの相手勘定科目
ブルー・・・種別と勘定科目のデータベース
グレー・・・勘定科目と消費税区分のデータベース

次から色別に見ていきましょう。

【オレンジ】Suicaデータを貼り付けるスペース

オレンジ部分には、Suica履歴を貼り付けます。Suicaインターネットサービスから引っ張ります。

↓Suicaインターネットサービスの履歴をドラッグ&コピー

↓オレンジ部分に貼り付け

今回、しっかりエクセルテンプレートを作り込んでおけば、次回からはこのコピペ作業のみで、freeeへインポートするデータが出力シートに表示されるようになります。

【グリーン左半分】摘要を組み合わせて表示する

Suica履歴だと、利用場所が2ヶ所に分かれています。摘要では乗車駅と降車駅を表示させたいので、関数を設定します。

↑例えば、C3セル「地 池袋」とE3セル「永田町」をハイフン(/)をつないでJ3セルに表示させる場合、「J3=C3&”/”&E3」と算式を設定します。

【ブルー】種別から勘定科目を結びつけるデータベース

Suica履歴の種別には、「物販」「入」「オート」「現金」のどれかです。これを使って勘定科目を決めるデータベースを作ります。ブルーの部分には関数の設定はせず、単純に文字情報を入力しているだけです。

【グリーン右半分】Suica履歴に応じて勘定科目を決める

オレンジのSuica履歴の種別に応じて、ブルーのデータベースに従って、グリーンの右半分に勘定科目を表示させます。

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

ブルーにない・・・未確定勘定にする
ブルーにある・・・データベースから探す

(注)「FALSE」や「$(絶対参照)」を使うとエクセルが重くなるという意見もあるので、アレンジしてもいいと思います。

【グレー】勘定科目に応じて消費税区分を決めるデータベース

消費税の納税義務があるなら、消費税区分も設定したほうがfreeeへのインポート後の修正が少なくて済みます。ブルーと同じように、文字情報を入力します。関数の設定はしません。

出力シート

freeeに取り込むには、「日付」「借方勘定科目」「借方金額」「貸方勘定科目」「貸方金額」が必須です。必須項目を指定しないとインポートできません。

また、freeeへのインポートができても、手作業での修正は面倒なので避けたいです。そのため、必須項目ではありませんが、消費税区分を借方と貸方で設定することをオススメします。

最終的には、出力シートは次のように表示させることを目指して作っていきます。

発生日

発生日は、オレンジ部分に貼り付けたSuica履歴の数字を引っ張っているだけです。細かい算式は不要です。入力シートとイコールでつなげばOKです。

借方科目

借方科目には、「Suica」又はグリーン右半分の「相手勘定科目」のどちらかが入ります。

オレンジ部分の「差額」がマイナスなら借方科目はSuica以外、プラスなら借方科目がSuica、これをIF関数で設定します。

B3=IF(入力シート!G2<0,入力シート!K2,"Suica")

借方税区分

税区分はグレー部分に作ったデータベースから呼び出します。

C3=IF(ISERROR(VLOOKUP(B3,入力シート!$P$3:$Q$6,2,FALSE)),”対象外”,VLOOKUP(B3,入力シート!$P$3:$Q$6,2,FALSE))

借方勘定科目の文字列(消耗品費)がグレー部分にあるときは、グレーブ部分の消耗品費の隣の税区分(課税仕入8%)を呼び出す、これが関数の意味です。

借方金額

金額はオレンジの「差額」から引っ張ります。

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

D3=IF(入力シート!G2<0,入力シート!G2*-1,入力シート!G2)

貸方勘定科目、貸方税区分、貸方金額

借方と同じやり方なので、省略します。

摘要

摘要は、グリーン部分からそのまま転記します。関数は「=」でつなぐだけです。

Suicaカードの履歴をfreeeに取り込むエクセルテンプレート

freeeとSuicaカードは直接連携していないので、インポートするにはエクセルテンプレートを準備してあげる必要があります。関数が出てきて難しく感じるかもしれませんが、一つ作ればずっと使えますし、弥生会計やJDL IBEX出納帳Majorなどの他のソフトへの取り込みにも応用できます。

Suicaカードをfreeeへインポートする一連の流れも確認してみてくださいね。