TERAOKAのレジから売上データをダウンロードし、Excelで集計して、弥生会計への入力用データを作成する仕組みを作りました。
これまでの売上データの入力方法
顧問先の店舗では（株）寺岡外食ソリューションズ（以前の会社名は（株）アスターでした。）のレジを使用しています。
これまでは売上データを会計ソフトに入力するために、日別の売上のレポートを印刷したものを渡してもらって、それを見てExcelに入力していました。
売上の日付と現金売上の金額だけを入力していました。
クレジットカード支払いの売上については別途クレジット会社から送付されてくるクレジット手数料の明細書を見て入力していました。
いずれにしても入力する必要があるのは日付と金額の二種類だけでした。
軽減税率導入後の売上データの入力項目
軽減税率が導入されると、売上の金額を税率8%と税率10%に分けなければならなくなりました。
レポートには、8%と10%のそれぞれに内税の設定と外税の設定の区別があり、それらのそれぞれについて売上の税抜金額と消費税額が記載されています。
また、8%と10%の売上金額を合計した金額を、現金売上とクレジットカード売上に売り分ける形式で記載されています。
クレジット手数料の明細書には、税率8%と税率10%の区分は当然ながら記載されていなので、クレジットカード売上の金額もクレジット手数料の明細書ではなくレポートで把握しなければならなくなります。
まとめると、これまでは入力項目が
- 日付
- 売上金額
の2つだけだったのが、
- 日付
- 内税 本体金額(8%)
- 内税 税額(8%)
- 内税 本体金額(10%)
- 内税 税額(10%)
- 外税 本体金額(8%)
- 外税 税額(8%)
- 外税 本体金額(10%)
- 外税 税額(10%)
- 現金売上の金額
- クレジットカード売上の金額
の11個になりました。支払い手段にQR決済などが導入されると12個になります。
入力項目が約6倍になってしまいました。
恐らくこれでは入力にかかる時間が大幅に増えてしまうだろうということが予測されたため、顧問先にお願いしてレジの売上データを提供していただくことにしました。
売上データの形式
レジの環境が店舗によって異なると思いますので、以下の記述はあくまで今回の店舗のレジの場合です。
また、レジのデータの取り出し方については、（株）寺岡外食ソリューションズの営業担当者に確認してください。
レジにはWindowsがインストールされており、売上のデータは「取引別レポート.XLS」という名前のExcelのファイル（*.xlsの形式）でUSBに保存することができます。
このファイルの形式は、1つのExcelのファイルに1つのワークシートがあってそこに日々の売上データが1行ずつ上から順番に記載されている、といったものではありません。
日別にExcelのファイルが作成され、それぞれのファイルにその日の売上の内容を記載した表がある、というものです。以下のようなレイアウトです。
また、この表の形式は一定しているわけではなく、例えば内税の設定をしている商品の売上が無かった日の場合には「内税小計」「内税計」といった項目自体が無く、その分だけExcelの表の行が少なくなっています。
つまり、同じ項目でもExcelのワークシート上に記載されている位置が日によって異なっていることがあるということです。形式が揃っていないのです。
このデータの名前の先頭に日付を付けて、
- 「20191001_取引別レポート.XLS」
という名前でUSBに保存しました。このようなファイルが
- 「20191002_取引別レポート.XLS」
- 「20191003_取引別レポート.XLS」
というように営業日の分だけあることになります。
これらのデータから、1枚のワークシートに売上データを集計する方法を考えました。
ファイル名をまとめて変更する
レジにインストールされているExcelで出力されるファイルの拡張子は、「xlsx」ではなくて「xls」でした。まず、これらのファイル名を「*.xls」から「*.xlsx」に変更します。
これについては、井ノ上陽一さんのブログのこちらの記事を参考にさせていただきました。
→ フォルダ内すべてのExcelファイルをxlsからxlsxに変更するマクロ
下記で紹介するマクロを使ったときにExcelのファイル拡張子が「xls」だとうまく動作しなかったので、ファイルの拡張子「xls」を「xlsx」に変更することにしました。
複数のExcelのファイルから必要な項目を抜き出して1つのワークシートにまとめる
これについては、こちらの記事を参考にさせていただきました。
→ 複数のExcelブックから必要な項目をコピーして1つのシートにまとめるVBA
ファイルごとのExcelの表の形式が異なっていても、項目名が一致していて、項目名の右となりに項目名に対応するデータが入力されていればデータを抜き出すことができるマクロです。
Excelのファイルから抽出する項目
今回は、色を付けた11の項目の金額を抽出します。
Excelのファイル（xlsmファイル）のダウンロード
こちらです。 → teraoka_regi.xlsm
使用方法
「yyyy/mm/dd_取引別レポート.XLS」が保存されているフォルダと同じフォルダに上記のExcelのファイルを保存してください。
マクロ「fileformat_change」を実行すると、フォルダ内の「*.xls」ファイルを開いて「*.xlsx」の形式で保存します。
マクロ「extraction」を実行すると、(WS)data上に必要な項目が集計されます。
マクロ「calc_copy」を実行すると、(WS)dataに計算式が挿入されます。
- M列・・・ファイル名から日付を作成
- N列・・・軽減税率8%の税込金額
- O列・・・標準税率10%の税込金額
- P列・・・現金売上の金額（A列と同じ金額）
が計算されます。
入力用売上データの作成
マクロ「uriage_shukei」を実行すると、「uriage.csv」と言う名前で
- 8%、10%それぞれの日別の売上高を計上する
弥生会計の入力用データ（売上）が作成されます。
今回のような形式のデータだと、「現金売上の税率別の内訳」や「クレジット売上の税率別の内訳」といった「支払方法ごとの税率別の内訳」はわかりませんので、8%・10%の売上を計上する際には借方の勘定科目をいったん「仮払金」等にしておきます。
マクロ「furikae_shukei」を実行すると、「furikae.csv」と言う名前で
- 現金売上の金額については「仮払金」から「現金」へ振替
- クレジットカード売上の金額については「仮払金」から「売掛金」へ振替
を行うための弥生会計の入力用データ（振替）が作成されます。
あらかじめ(WS)dataのQ列に、現金売上の普通預金への入金日を入力しておいてからマクロ「nyukin_shukei」を実行すると、「nyukin.csv」と言う名前で
- 現金売上の金額について「現金」から「普通預金」に振替
を行うための弥生会計の入力用データ（入金）が作成されます。
これらの入力用データの作成については、弥生会計において売上をどのように仕分けして管理したいかによって変わってくると思いますので、あくまでも1例に過ぎません。
売上データのサンプル、弥生会計のサンプル
売上データのサンプルは用意することができなかったので、下記のファイルを元にして何日分か作成してみてください。
上記のマクロで作成された「uriage.csv」「furikae.csv」「nyukin.csv」のインポートのテスト用の弥生会計データベースです。
→ TERAOKA_レジデータ入力_テスト用(1期).KB20
お願い
期待した結果が得られない場合は お問い合わせフォーム よりお問い合わせ下さい。
改善に取り組みたいと思います。