Excel VBA 消費税率を日付と軽減税率対象商品で分岐させる自作関数

ExcelVBA-実用編

2019.09.30

VBAで消費税計算を組み込んでいる場合に、今までは期限で分岐させるだけでも良かったツールが、軽減税率の対象商品でも分岐させる必要が出てきましたので、自作でツールを作成してみました。

1989年4月～2019年9月までは日付のみで税率を分岐させればよいだけでしたが、2019年10月以降は商品によって8%や10%と変わるため、分岐条件が多くなりました。

それに対応するロジックと自作関数についてご説明します。

掲載する内容は細かい条件や、税法についてまで考慮された設計にはなっていませんので、イメージやロジックが伝わり、ご自身の環境でカスタマイズして使用してもらえるといいな程度のツールになっています。

１.消費税率の分岐ロジック
2.サンプルコード
3.計算結果

１.消費税率の分岐ロジック

まずは簡単にロジックの説明です。

消費税計算をするためのデータと軽減税率の対象となるリストを用意します。

今回は下記のような軽減税率の対象となる商品リスト(B列)と、日付と商品名、そして税抜きの売上がわかるデータを用意しました。

2019年10月以降の日付は5日までが軽減税率商品で、6日以降が通常商品となっています。

日付でざっくり分かれてしまっていますが、分岐自体は日付と商品で処理されます。

軽減税率対象商品のB列には無駄にたくさん商品が登録されていますが気になさらずにお願いします。

その1 軽減税率対象商品のリストを登録

まず、軽減税率の対象商品と通常の10％の商品を分岐するためのリストを作成して、VBAでリストを登録します。

その2 日付と軽減税率対象で分岐

次に1989年4月〜2019年9月までは日付で、2091年10月以降は日付と軽減税率対象のリストでそれぞれの税率をセットする関数を作ります。

その3 計算処理

最後に計算する部分を組み込みます。

こちらは使用する環境で、全く変わってきますので、今回はシンプルに税抜きの売り上げを税込にするだけにしています。

2.サンプルコード

サンプルコードです。

ロジックで説明した通りに、3つの工程に分けてます。

全て標準モジュールに記述しています。データは上記で用意したデータを使います。

コードの基本的な説明はコメントに記述していますので、コード内をご覧下さい。

基本的に上から順に同じ標準モジュールに記述します。

その1 軽減税率対象商品のリストを登録

「Dictionary」の使い方については「Dictionaryの使い方」をご覧ください。

Option Explicit

Dim ExcDic  As Object '同一モジュール内で使用したいため

Sub MakeDic()

Dim TaxVal  As String
Dim MaxRow  As Long
Dim n       As Long

Set ExcDic = CreateObject("Scripting.Dictionary") 'リスト作成用にDictionaryをセット

MaxRow = Cells(Rows.Count, 2).End(xlUp).Row 'リストに登録したい最終行を取得(B列)

For n = 3 To MaxRow 'リストに登録する行数分ループ
            
    TaxVal = Cells(n, 2) 'Dictionaryに登録する商品名を文字列で取得
    
    If Not ExcDic.Exists(TaxVal) Then '未登録判定をして、未登録ならKeyに登録(Itemは適当)
        ExcDic.Add TaxVal, 1
    End If
        
Next n

End Sub

その2 日付と軽減税率対象で分岐

Function Tax_Set(ByVal Tax_Date As Date, ByVal Tax_Val As String) As Double '小数点以下を扱うため「Double」
    
    '日付判定
    Select Case Tax_Date 'Select文で分岐
        
        '3%
        Case CDate("1989/4/1") To CDate("1994/10/31")
        
            Tax_Set = 1.03
        
        '4%
        Case CDate("1994/11/1") To CDate("1997/3/31")
            
            Tax_Set = 1.04
            
        '5%
        Case CDate("1997/4/1") To CDate("2014/3/31")
            
            Tax_Set = 1.05
        
        '8%
        Case CDate("2014/4/1") To CDate("2019/9/30")
        
            Tax_Set = 1.08
            
        '8%と10%
        Case Is >= CDate("2019/10/1")
        
            '商品判定(軽減税率対象商品除外)
            If ExcDic.Exists(Tax_Val) Then '慶全税率対象商品のリストに登録されているか判定
        
                Tax_Set = 1.08
                
            Else
            
                Tax_Set = 1.1
                
            End If
        
    End Select

End Function

その3 計算処理

消費税の税率をかけた結果の小数点の計算なども、細かく設定していませんのでご注意ください。

Sub Sample1()

Dim TaxVal  As String
Dim TaxDate As Date
Dim MaxRow  As Long
Dim n       As Long
Dim GetTax  As Double

Call MakeDic 'リスト作成のタイミングとりあえず計算直前

MaxRow = Cells(Rows.Count, 4).End(xlUp).Row '計算したいデータの最終行を取得

For n = 3 To MaxRow '計算したいデータの行数分ループ
    
    TaxDate = CDate(Cells(n, 4)) '日付を取得(念のため日付型に変換)
    TaxVal = Cells(n, 5).Value '軽減税率対象商品か判定するために商品名を取得
    
    GetTax = Tax_Set(TaxDate, TaxVal) 'Function Tax_Setに日付と商品名を渡して税率分岐
    
    Cells(n, 7) = Format(Cells(n, 6) * GetTax, "#,##0") '税抜の売上と取得した税率で税込計算

Next n


End Sub