Excelマクロ超入門 PayPay取引の経理を効率化するマクロ

Excelマクロ入門

経理は既にあるデータを使うことで効率化できます。

ただ、預金やクレジットカードのようにデータ連携ができる取引ばかりでもなく。データ連携ができない場合は既にあるデータを使って、インポートするのがおすすめです。

今あるデータを使えないか?

PayPayは私もよく使っています。

コンビニでモノを買う、わりかんする、飲食店で決済するときなど利用する場面は多いです。

このPayPayサービスは、自分が買う側だけでなく、売る側としても利用できるものです。

「PayPay for Business」を利用することで、店頭にQRコードを置いてお客様に決済していただくこともできますし、金額を指定してQRコードを生成することもできます。

PayPay決済は入金までが短期です。

補足

  • 入金口座がジャパンネット銀行なら翌日
  • 上記以外の入金口座でも売上10,000円ごとに翌々営業日
  • 売上10,000円超えない場合には月末締めで翌日または翌々日入金

現在は決済手数料も振込手数料も無料です。

補足
2021年9月30日まで決済手数料は無料。ジャパンネット銀行以外の銀行の登録をしている場合、2020年7月1日以降は入金手数料が発生する予定

私はそれほど積極的には使っていませんが、使ってみないとわからないですし、お客様も登録しているので登録しています。

ただ、経理を考えた場合、ちょっと手間になります。

現状、クラウド会計でもデータ連携ができないサービスになっています。

ただ、取引データはダウンロードすることができます。

そこで今回は、お客様の事例をもとにPayPayの取引データを使って、経理を効率化する方法を考えてみました。

PayPay取引の仕訳データ作成

PayPay決済をした取引データは、「PayPay for Business」に記録されます。

このデータを使って、仕訳データをつくります。

データのダウンロード

取引タブをクリックし、表示されたデータの右上にあるダウンロードのボタンをクリック。

スクリーンショット 2019 11 26 18 06 16

ここですぐにダウンロードできればいいのですが、このあとメールが届き、そのリンクをクリックしてまたこの画面に戻って、ようやくダウンロードできるというなかなかめんどくさい処理が必要です。

さらにダウンロードしたデータは圧縮ファイルですから、2度手間感がハンパないです。

データをExcelフォーマットに貼り付け

仕訳インポート用のファイルを準備します。

PayPayというシートをつくり、

スクリーンショット 2019 11 26 17 23 07

A1セルにダウンロードした取引データを貼り付けると、

スクリーンショット 2019 11 26 17 21 44

O列から右に仕訳データが表示される流れです。

スクリーンショット 2019 11 26 18 16 39

O列は伝票番号のイメージで、マネーフォワードクラウドの場合、ブランクのままだと、たとえ取引日付が違っていても1番上のデータの日付で1つの伝票になります。日付を反映させたい場合には適当に連番にしておく必要があります。

今回は連番になるように数式を入力しています。

仕訳は「売掛金(PayPay)/売上」としています。

補足
PayPayの場合は2021年9月30日まで手数料が無料なので、手数料の仕訳は省略しています。

日付のデータを変換

日付のデータが日時表示になっていますが、会計処理では日付の情報があれば足ります。

スクリーンショット 2019 11 26 17 21 44

TRUNCを使うと、日付情報だけを抽出できます。

=IF($I$2>0,+TRUNC(H2),””)

商品名が表示されない問題

PayPayの取引データには、何を買ったかが表示されません。

そこで、F列の端末番号(商品コードのようなもの)をキーに関数XLOOKUPを使ってU列(摘要欄)に表示されるようにしています。(VLOOKUPでも大丈夫です。)

スクリーンショット 2019 11 26 17 21 51

端末番号のマスタのシートを用意しておき、

スクリーンショット 2019 11 26 17 23 41

PayPayシートのL列に次の式を入力しています。

=XLOOKUP(F2,コード!$A:$A,コード!$B:$B)

スクリーンショット 2019 11 26 17 21 51

これで商品の情報がわかるようになりました。

これを新規ファイルにコピペして、CSVファイルで保存してもいいのですが、これを自動でやる流れをExcelマクロを使ってつくることができます。

仕訳インポートファイルをマクロで変換

この一連の処理からインポートファイルを生成するところまでをマクロでつくってみました。

スクリーンショット 2019 11 26 19 14 49

「CSV」ボタンをクリックすると、次のような処理をします。ボタン作成については下記の記事を参考にしていただければ。

Excelマクロ超入門 マクロを使ってもらうときにやっておきたいこと | GO for IT 

マクロで処理する流れ
  1. PayPayデータの数を数える
  2. 前回の履歴を削除(処理の都度、データ数が違うことが想定されるため)
  3. O列2行目の数式を3行目以下にコピー
  4. dataシートに仕訳データをコピペ
  5. 上記④をもとにCSVファイル作成
スクリーンショット 2019 11 26 19 02 40

次のように記述しました。

Sub paypay()
'1 シート[paypay」のデータをカウントし、コピー
    Dim Max_row_paypay As Long
    Max_row_paypay = Worksheets("paypay").Range("a" & Rows.Count).End(xlUp).Row
'2 前回の履歴を削除して新規コピー
    Worksheets("paypay").Range("o3", "u" & Rows.Count).ClearContents
    Worksheets("paypay").Range("o2", "u2").Copy Worksheets("paypay").Range("o3", "u" & Max_row_paypay)
'3 シート「data」のデータをクリア
    Worksheets("data").Cells.ClearContents
'4 売上データをコピーして、シート「data」の1行目に貼り付け
    Worksheets("paypay").Range("o1").CurrentRegion.Copy
    Worksheets("data").Range("a1").PasteSpecial Paste:=xlPasteValues
    Worksheets("data").Rows(1).Delete
    ActiveWorkbook.Save
'5 CSVデータを保存
    Worksheets("data").Activate
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="import.csv", FileFormat:=xlCSV, Local:=True
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

上手くいけば、CSVファイルとdataシートではこういうデータができているはずです。

スクリーンショット 2019 11 26 17 24 59

あとは、できたCSVファイルをfreeeでインポート(レポート→仕訳帳→他社会計ソフトインポート)をしたり、

スクリーンショット 2019 11 26 19 19 00

マネーフォワードクラウドでインポートすれば、完成です。

スクリーンショット 2019 11 26 17 26 24

既に「PayPay for Business」にあるデータを使えば、イチから入力する必要はありません。ネット上にあるデータを使っていきましょう。

一例ですが参考にしていただければ。


【編集後記】
昨日はお客様訪問。ランチをご一緒したあと、決算の報告を中心に。夜は長女(10)の宿題を見たり、長男(4)の戦いの相手をしたりという1日でした。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
アジアンレストラン コピラ