MENU
長崎県諌早市で働く40代前半税理士による税金や効率化のこととか。
  1. ホーム
  2. Excel
  3. マクロ
  4. 消費税8%と10%が混ざったレシートの会計ソフトへのデータ入力を楽にしたくてExcelマクロ（ユーザーフォーム）を作ってみた件。

消費税8%と10%が混ざったレシートの会計ソフトへのデータ入力を楽にしたくてExcelマクロ（ユーザーフォーム）を作ってみた件。

Excel マクロ

最近 Excel の本でユーザーフォーム関連のものを読む機会がありましたので（まだ全部読んではいないんですが。）読んでみてインプットしたことを自分なりにアウトプットしてみようと思い、ユーザーフォームを一つ作ってみましたので備忘記録として記事に残しておこうと思います。

ニッチなユーザーフォームですが。。。

目次
  1. 消費税8%と10%が混ざったレシートをExcelマクロ（ユーザーフォーム）を使って仕訳データを作成する。
  2. 消費税8%と10%が混ざったレシートから仕訳データを作成するExcelマクロ（ユーザーフォーム）の内容説明
  3. ユーザーフォームを使用することのメリット
  4. ユーザーフォームで使用している機能についての説明
    1. リストボックス機能についての説明
    2. チェックボックス機能についての説明
    3. テキストボックス機能についての説明
    4. 仕訳登録ボタンが押された（クリックされた）時に動くマクロ
  5. まとめ

消費税8%と10%が混ざったレシートをExcelマクロ（ユーザーフォーム）を使って仕訳データを作成する。



スーパーマーケットなどで仕入れをしている事業者の方のレシートを拝見すると結構高い確率で軽減税率8%と標準税率10%が混ざったレシートに遭遇します。

消費税のルールによって食料品などについては消費税率が8%となっており、それ以外のものについては消費税が10%となっています。

そのため会計ソフトに仕訳を入力する際には消費税8%部分と10%部分を区分して入力する必要があります。

消費税の支払う必要がない【免税事業者】である事業者の場合にはこの取扱いは必要ありませんが、消費税を納める必要がある人【課税事業者】等については上記の通りの処理が必要になると思います。


レシートの数が数枚程度であれば特に入力の際に苦に感じることはないんですが、これが20枚30枚40枚、、、、となってきた時に毎回毎回8%部分と10%部分を分けてデータ入力するとなると、かなり苦痛に感じることになります。


なので、この部分について弥生会計へCSVインポートすることを前提としてエクセルでユーザーフォームを作成しました。こんなやつです↓




具体的にはレシートに記載された8%部分の税抜金額と10%部分の税抜金額をユーザーフォームに入力することで弥生会計のインポートに必要形式で仕訳データを作成するようにしてみました。


今回のユーザーフォームで作成する弥生会計のインポートデータの形式は仕訳日記帳形式によるもので作成しています。
振替伝票形式によるデータ作成をしたい場合には以前に記事にした弥生会計のインポートデータを振替伝票形式に変更するという記事を参考にしてもらえればと思います。



具体的にはこんな感じで動きます↓

消費税8%と10%が混ざったレシートから仕訳データを作成するExcelマクロ（ユーザーフォーム）の内容説明


今回作成したユーザーフォームですが使い方としてはフォームの右側で仕訳のベースとなる基本項目を設定します。この部分です↓


ここに入力した情報が弥生会計にインポートされるので、勘定科目に関しては弥生会計で実際に使用している勘定科目名を記載する必要があります。


エンターキー押すことで次の項目へ移動していくように設定していますので、各項目の入力が終わったらエンターキーを押して次の項目を入力していきます。


摘要まで入力が終われば、後はレシートを見ながら日付の入力とレシートに記載された8%の税抜金額と10%の税抜金額をユーザーフォームに入力します。


なお、8%の税抜金額を入力するとそれに対応する8%の消費税額が計算され、同時に税込金額がユーザーフォーム上に表示されます。


同様に10%の税抜金額を入力するとそれに対応する10%の消費税額が計算され、同時に税込金額が表示されます。


最終的に8%の税込金額と10%の税込金額の合計額がユーザーフォームに表示されるので、この表示された金額とレシートに記載された支払金額をチェックします。


この部分です↓



仕訳イメージに表示された仕訳データを確認し、問題なければエンターキーで仕訳登録へ進みます。


仕訳登録ボタンをエンターキーで押すと、Excelのシートに弥生会計インポート形式でデータが作成されます。


と同時に、ユーザーフォーム上の金額欄と日付のデータがクリアされますので、次のレシートの入力作業を行います。


あとはこの作業の繰り返しです。

ユーザーフォームを使用することのメリット



ユーザーフォームの便利な点としてはマウス操作を減らし、エンターキーやテンキー（数字入力キー）のみでデータを作成することができるという点ではないでしょうか。


会計ソフトを使って仕訳データを入力する場合、

  1. 日付
  2. 借方科目
  3. 貸方科目
  4. 金額
  5. 摘要


などを入力する必要がありますが、ユーザーフォームを利用することでこれらの一部分、ものによっては大部分を省略することができます。


今回の場合には一度、仕訳のベースとなる基本項目（借方科目、貸方科目、摘要）を入力してしまえばその後は日付と支払金額のみ変更すればどんどん仕訳データの作成が可能になります。

今回のユーザーフォームを使用する前には領収書を整理しておき、取引先別かつ月別にまとめておくと効果的なデータ作成が可能になります。

ユーザーフォームで使用している機能についての説明




今回ユーザーフォームを作成するにあたって使用した機能？としては

  1. リストボックス機能
  2. チェックボックス機能
  3. テキストボックス


こんな感じです。

リストボックス機能についての説明


リストボックス機能に関しては仕訳データを登録する直前、仕訳のイメージをリストボックス上で確認するためにこんな感じで作りました。　

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
Private Sub MyTotalAmount()
 
'■税込金額を計算して合計金額を表示する一連の作業
 
On Error GoTo myerror
 
Dim total As Long
Dim komi8 As Long
Dim komi10 As Long
Dim i As Long
Dim hiduke As String
 
 
'■日付を作成する
 
hiduke = DateSerial(meTextyear, meTextmonth, meTextDay)
 
 
'■８％の税込金額を計算して表示する
 
komi8 = Int(meText8 * 1.08)
 
 
meText8komi.Text = Format(komi8, "#,##0")
meLabel8tax.Caption = Val(komi8) - Val(meText8)
 
 
'■10％の税込金額を計算して表示する
 
komi10 = Int(meText10 * 1.1)
 
meText10komi.Text = Format(komi10, "#,##0")
meLabel10tax.Caption = Val(komi10) - Val(meText10)
 
 
 
On Error GoTo 0
 
 
 
'■合計金額を表示する
 
total = Val(komi8) + Val(komi10)
 
meTotalAmount.Caption = Format(total, "##,#0")
 
meCommandButton1.Enabled = True
 
 
 
'■仕訳イメージに作成した仕訳を表示する
 
 
With ListBox1
 
     
    '1行目の仕訳
         
    .AddItem hiduke
    .List(0, 1) = "諸口　/"
    .List(0, 2) = meTextkasikata
    .List(0, 3) = meTotalAmount.Caption
    .List(0, 4) = meTextTekiyou
     
     
    '2行目の仕訳
     
    .AddItem hiduke
    .List(1, 1) = meTextkarikata
    .List(1, 2) = "/　諸口"
    .List(1, 3) = meText8komi.Text
    .List(1, 4) = meTextTekiyou & "　8％軽減税率分"
     
     
    '３行目の仕訳
     
     .AddItem hiduke
    .List(2, 1) = meTextkarikata
    .List(2, 2) = "/　諸口"
    .List(2, 3) = meText10komi.Text
    .List(2, 4) = meTextTekiyou & "　10%分"
 
  
 
End With
 
 
myerror:
 
    '■エラーが発生した時（金額欄に何も入力されていない時）は何もしない。
 
End Sub


上のマクロに関しては下記の画像の8%軽減の税抜金額欄と10%税抜金額欄に数値が入力されたらマクロが実行されるように設定しています。


こんな感じです。「AfterUpdate」イベントを使用するとよいようです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Private Sub meText8_AfterUpdate()
 
'金額計算　8％
 
If IsNumeric(meText8) = False Then
 
    MsgBox "入力した値が不正です", vbExclamation
    meText8.Text = ""
    meText8.SetFocus
     
    Exit Sub
     
     
End If
 
 
Call MyTotalAmount
 
End Sub
 
Private Sub meText10_AfterUpdate()
 
'金額計算　10％
 
If IsNumeric(meText10) = False Then
 
    MsgBox "入力した値が不正です", vbExclamation
    meText10.Text = ""
    meText10.SetFocus
     
    Exit Sub
     
     
     
End If
 
 
Call MyTotalAmount
 
End Sub


チェックボックス機能についての説明


チェックボックス機能に関してはレシートの種類によっては8%の消費税率が最初に記載され次に10%の消費税率が記載されているタイプのものや、


先に10%の消費税率が記載されており、次に8%の消費税率が記載されるタイプのレシートもあるのでどちらのタイプのレシートにも対応することができるようにチェックボックスを搭載してエンターキーで入力する順番を変更できるようにしています。


こんな感じです。「Change」イベントを使用すると上手くいくようです。↓

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Private Sub CheckBox1_Change()
 
'■チェックボックスにチェックが入ったら入力順を変更する
 
If CheckBox1.Value = True Then
 
    meText8.TabIndex = 7
    meText10.TabIndex = 6
    MsgBox "入力の順番を10％、8％軽減の順に変更します"
 
 
Else
 
    meText8.TabIndex = 6
    meText10.TabIndex = 7
    MsgBox "入力の順番を8％軽減、10％の順に変更します"
 
End If
 
End Sub


テキストボックス機能についての説明


金額を入力するテキストボックス欄に関しては、数字以外のデータが入力された場合にはエラーとなるように制御をかけています。これによって、間違って文字や記号などが入力されても仕訳データが作成されないようにしています。


この部分です↓

仕訳登録ボタンが押された（クリックされた）時に動くマクロ


仕訳登録ボタンが押されたときには下記のマクロが実行されるように記載してみました↓

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
Private Sub meCommandButton1_Click()
 
'■登録ボタンが押された時の処理
 
Dim i As Long
Dim lastrow As Long
Dim hiduke As String
Dim yayoiadd3(2, 24) As Variant  '仕訳転記用の配列
 
 
lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
 
 
'■日付を作成する
 
hiduke = DateSerial(meTextyear, meTextmonth, meTextDay)
 
 
 
'■1行目の追加する仕訳の項目を設定して配列yayoiadd3に格納する。
 
 
yayoiadd3(0, 0) = 2000 '識別フラグ
yayoiadd3(0, 3) = hiduke '日付
yayoiadd3(0, 4) = "諸口" '借方科目
yayoiadd3(0, 7) = "対象外" '借方消費税区分
yayoiadd3(0, 8) = meTotalAmount '借方金額
yayoiadd3(0, 9) = ""  '借方消費税金額
yayoiadd3(0, 10) = meTextkasikata '貸方科目
yayoiadd3(0, 13) = "対象外" '貸方消費税区分
yayoiadd3(0, 14) = yayoiadd3(0, 8) '貸方金額
yayoiadd3(0, 15) = yayoiadd3(0, 9) '貸方消費税金額
yayoiadd3(0, 16) = meTextTekiyou '摘要欄
yayoiadd3(0, 19) = 0
yayoiadd3(0, 24) = "no"
 
 
'■2行目の追加する仕訳の項目を設定して配列yayoiadd3に格納する。
'使用しない項目はコメントアウトしておくこと。
 
yayoiadd3(1, 0) = 2000 '識別フラグ
yayoiadd3(1, 3) = hiduke '日付
yayoiadd3(1, 4) = meTextkarikata
yayoiadd3(1, 7) = "課対仕入込軽減8%"
yayoiadd3(1, 8) = meText8komi '借方金額
yayoiadd3(1, 9) = Val(meLabel8tax.Caption)  '借方消費税金額
yayoiadd3(1, 10) = "諸口"
yayoiadd3(1, 13) = "対象外"
yayoiadd3(1, 14) = yayoiadd3(1, 8)
yayoiadd3(1, 15) = yayoiadd3(1, 9) '貸方消費税金額
yayoiadd3(1, 16) = meTextTekiyou & "　8%軽減税率"
yayoiadd3(1, 19) = 0
yayoiadd3(1, 24) = "no"
 
 
'■3行目の追加する仕訳の項目を設定して配列yayoiadd3に格納する。
'使用しない項目はコメントアウトしておくこと。
 
yayoiadd3(2, 0) = 2000 '識別フラグ
yayoiadd3(2, 3) = hiduke '日付
yayoiadd3(2, 4) = meTextkarikata
yayoiadd3(2, 7) = "課対仕入込10%"
yayoiadd3(2, 8) = meText10komi '借方金額
yayoiadd3(2, 9) = Val(meLabel10tax.Caption)  '借方消費税金額
yayoiadd3(2, 10) = "諸口"
yayoiadd3(2, 13) = "対象外"
yayoiadd3(2, 14) = yayoiadd3(2, 8)
yayoiadd3(2, 15) = yayoiadd3(2, 9) '貸方消費税金額
yayoiadd3(2, 16) = meTextTekiyou & "　10%税率"
yayoiadd3(2, 19) = 0
yayoiadd3(2, 24) = "no"
 
 
Range(Range("a" & lastrow), Range("y" & lastrow).Offset(2, 0)).Value = yayoiadd3
 
'チェックボックスにチェックがある場合には仕訳の並び順を変更する
 
If CheckBox1.Value = True Then
 
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
 
    Rows(lastrow - 1).Cut
    Rows(lastrow + 1).Insert Shift:=xlDown
 
End If
 
'■金額欄をクリアする
 
meTextDay.Text = ""
meText8.Text = ""
meText10 = ""
meTexttyousei.Text = ""
meLabel8tax.Caption = ""
meLabel10tax.Caption = ""
meText10komi.Text = ""
meText8komi.Text = ""
meTotalAmount.Caption = ""
 
 
'■登録ボタンを選択不可に変更する
 
meCommandButton1.Enabled = False
 
'■仕訳イメージの表示をクリアする
 
ListBox1.Clear
 
 
'■日付欄にフォーカスを移動する
 
meTextDay.SetFocus
 
 
End Sub



まとめ


読んだ書籍で学んだ知識をアウトプットするために仕事で使いそうなユーザーフォームを作ってみました。

今回のユーザーフォームを使えば「毎回現金支払いをしているような業種」で、その都度8%と10%の消費税が混在した支払いを行うような事業者を担当している税理士事務所の職員の方の作業を多少楽にすることができるものかもしれません。


なお細かなバグやエラーについては完全に対応し切れていません。（自分のスキルでは無理。）
そこはご愛嬌ということで。


最後に今回参考にした書籍を紹介して筆をおかせていただきます。
気になる方はチェックしてください。

アプリ作成で学ぶ Excel VBAプログラミング ユーザーフォーム＆コントロール 2019/2016対応 [ 横山　達大 ]
posted with カエレバ
楽天市場で購入
Amazonで購入



【本日の近況報告】

サッポロ一番塩ラーメンをトマトジュースで作ってみました。煮込む際に水を半分にして残り半分をトマトジュースで作るだけなんですが、最後にバジルと粉チーズを入れるといい感じに仕上がります。

日清のカップヌードルの「チリトマト味」が好きな人は気に入るのではないでしょうか。

【本日の１曲】

The Offspring / Let The Bad Times Roll 

【輸入盤】レット・ザ・バッド・タイムス・ロール [ OFFSPRING ]
posted with カエレバ
楽天市場で購入
Amazonで購入

オフスプリングの9年ぶりのニューアルバムからのタイトルトラック。

ラジオでちょくちょく流れてるのを聴いてるんですが、ブレないというか、安定感抜群というか、これぞオフスプリングって感じです。いいですね。

今回で10枚目のアルバムらしいです。そういえば高校生ぐらいのときからちょこちょこ聴いてきてますがアルバムとかレコードはそんなにもっていない印象が。

Excel マクロ
よかったらシェアしてね！
  • URLをコピーしました！
  • URLをコピーしました！

関連記事

目次
  1. 消費税8%と10%が混ざったレシートをExcelマクロ（ユーザーフォーム）を使って仕訳データを作成する。
  2. 消費税8%と10%が混ざったレシートから仕訳データを作成するExcelマクロ（ユーザーフォーム）の内容説明
  3. ユーザーフォームを使用することのメリット
  4. ユーザーフォームで使用している機能についての説明
    1. リストボックス機能についての説明
    2. チェックボックス機能についての説明
    3. テキストボックス機能についての説明
    4. 仕訳登録ボタンが押された（クリックされた）時に動くマクロ
  5. まとめ