電子帳簿保存法の検索要件をエクセルマクロで乗り切る方法
※おそらく検索要件を満たせるんじゃないかと思っておりますが、実際のところはわかりません。
あくまでも素人知識という事を前提にお読みください。
電子帳簿保存法が2024年01月01日から始まりましたね！
始まる前は領収書をもらったらタイムスタンプ付与業者にお金払って依頼して…って感じでホントにこんなことやんのか？！って感じでしたが、
事務処理規程を作れば回避できるようになって割と自由が利くようになりましたね。
国税庁のサイトのパンフレットが分かりやすいですね。
■国税庁の電子取引関係のページ
https://www.nta.go.jp/law/joho-zeikaishaku/sonota/jirei/tokusetsu/01.htm
■パンフレットへの直リンク
https://www.nta.go.jp/law/joho-zeikaishaku/sonota/jirei/tokusetsu/pdf/0023006-085_01.pdf
パンフレットによるとやることは3つで、
- 改ざん防止のための措置 をとる必要があります。
- 「日付・金額・取引先」で検索 できる必要があります。
- ディスプレイやプリンタ等を備え付ける必要があります。
だそうですね。
3個目のディスプレイ、プリンタ云々は普段使ってるPCでOKなので自動的にクリアできますね。
1個目の改ざん防止のための措置はパンフレット2ページ目に
> 「改ざん防止のための事務処理規程を定めて守る」といったシステム費⽤等をかけずに導入できる方法もあります。
とあり、国税庁のページから事務処理規定のサンプルファイルがダウンロードできるので、
これを落としてきてちょこっと書き換えれば完了ですね。(関係者への周知も忘れずに！！)
■国税庁の事務処理規定のサンプルが置いてあるページ
https://www.nta.go.jp/law/joho-zeikaishaku/sonota/jirei/0021006-031.htm
■事務処理規定(法人用)のサンプルへの直リンク
https://www.nta.go.jp/law/joho-zeikaishaku/sonota/jirei/word/0021006-031_d.docx
残るは2番目の
> 「日付・金額・取引先」で検索 できる必要があります。
という検索に関しての部分ですね。
パンフレットの2ページ目に説明が有ります。
----------------------------------------------------
① 表計算ソフト等で索引簿を作成する方法
表計算ソフト等で索引簿を作成、表計算ソフト等の機能を使って検索する方法です。
② 規則的なファイル名を付す方法
データのファイル名に規則性をもって「日付・金額・取引先」を入力し、特定のフォルダに集約しておくことで、
フォルダの検索機能が活⽤できるようにする方法です。
----------------------------------------------------
①で行けるんなら普段会計ソフトに入れてるデータがあれば良いんでは？って思うところですが、
どうもこの索引簿から実ファイルを特定するために索引簿・ファイル共に同じ連番を入れて、
索引簿のデータとファイルを一致させるようにしないといけないようですね。
もしくは索引簿に「ファイル名」の列を追加してファイル名を書く方法でも良いかもしれませんね。
ただ、連番を忘れずに毎回振るのは面倒ですし、
索引簿にファイル名を書いたとしてもファイル名を変えたくなった時に実ファイル&索引簿の両方を一致させつつ変更しないといけないので、少々面倒ですね。
②のファイル名に「日付・金額・取引先」を入れるのはメンテも簡単そうなので、これで良ければこれで行きたいところですが、
実は②はそれだけじゃないんですよね。
パンフレットの3ページ目のフローチャートを見ると
-----------------------------------------
・日付または金額について、範囲を指定した検索ができる
・「日付・金額・取引先」 のうち２つ以上の任意項目を組み合わせて検索できる
-----------------------------------------
という感じで、範囲指定での検索、さらに複数項目での検索ができないといけないんですよね！
一応できない場合は税務職員からの「電子取引データのダウンロードの求め」というのに応じてデータを渡せばうまいこと処理するよってことなようですが、
領収書等のファイルを全部渡すのか、ファイル名のリストを出せばいいだけなのか、
索引簿的なものをcsv等にまとめて提出する事になるのか、よくわからないですね。
税務調査が入った時に「結局最初から索引簿作らなきゃダメだったんじゃん…。二度手間やん…。」みたいなことになるのも面倒なので、
エクセルのマクロで索引簿を作って準備しちゃおう！というのが今回やることです。
電子取引データの索引簿作成エクセルマクロファイル
■ダウンロードリンク
電子取引データの索引簿作成マクロ_v1.0.0.xlsm
使い方
まずは「日付・金額・取引先」を含んだ状態でファイルを保存しておきます。
パンフレットに書いてあったファイル名と同じような感じで、
「{日付}_{金額}_{取引先名}」というフォーマットでファイルを保存しておきます。
以下のような感じです。
※日付部分は「2024-01-01」でも「20240101」でも大丈夫です。
※金額部分にカンマは無くても大丈夫です。
※取引先名の後ろに品目も付けてますが、必須ではありません。
普段このような感じのファイル名でファイルを保存しておきます。
アンダーバー「 _ 」区切りで日付、金額、取引先の順で書いておきます。
そしていざって時になったらエクセルマクロファイルの出番です。
起動するとこのような画面が出てきます。
そして左上の「リスト作成」ボタンを押して、ファイルが置いてあるフォルダを選択します。
すると以下の様にリストが出来上がります。
追加でリストを作りたいフォルダがあればもう一度「リスト作成」ボタンを押して選択していきます。
するとリストに追加されます。
これで索引簿が出来上がったので、後は税務署員の人の求めに従ってエクセルのオートフィルターを使って検索をしていきましょう。
例えば
・日付が2023年12月1日～2023年12月31日
・金額が1,000円～10,000円の範囲
というリクエストをもらったとします。
日付のフィルターのボタンを押して、範囲指定をしましょう。
「2023年12月1日～2023年12月31日」の期間を指定します。
次に金額のフィルターボタンを押して範囲指定をしましょう。
「1,000円～10,000円」の範囲です。
これにてバッチリ複数項目&範囲指定で検索できました！
これなら税務署員の人も満足してくれるんじゃないでしょうか？
どうでしょうか？
どうなんでしょうね！？
探り探りですね！
一応一番右の列にファイルパスを出力するようにしたので、実体のファイルとの対応もバッチリですね。
これなら索引簿と実ファイル名の2重管理をする必要は無く、実ファイルの名前にだけ気を付けておけばOKですね。
リストを消してやり直したい場合は6行目以降の部分を丸っと消してください。
ソース
コードのハイライトができないので見づらいですが、内容はマクロファイルを開いてAlt + F12キーを押して見れるものと一緒です。
やってることは単純で、選択されたフォルダ配下のファイルの名前を1個ずつ取得して、
ファイル名をアンダーバーで分割してエクセル上に記入していってるだけですね。
日付のフォーマットを吸収する処理が入ってたり、ファイル名のフォーマットが想定と違う場合の処理が入ってたりしますね。
' v1.0.0
Dim 前回選択したリスト化フォルダパス As String
Sub リスト作成()
' ～～～～～～ リスト化したいフォルダを選択する処理 ～～～～～
Dim リスト化フォルダパス As String
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = 前回選択したリスト化フォルダパス
If .Show = True Then
リスト化フォルダパス = .SelectedItems(1)
前回選択したリスト化フォルダパス = リスト化フォルダパス & "\"
End If
End With
If Len(リスト化フォルダパス) = 0 Then
MsgBox "リスト化したいフォルダを選んでください。"
Exit Sub
End If
' ～～～～～～ フォルダ内のファイルをリスト化する処理 ～～～～～
' B5セルにタイトル行があるので、その次の行から記入していく。
Dim 記入基点セル As Range
Set 記入基点セル = Range("B5")
Dim ターゲット行カウンター As Long
ターゲット行カウンター = 1
' 値が入ってる所の次の行まで記入位置を進める。
Do
If Len(記入基点セル.Offset(ターゲット行カウンター, 0).Value) = 0 And _
Len(記入基点セル.Offset(ターゲット行カウンター, 1).Value) = 0 And _
Len(記入基点セル.Offset(ターゲット行カウンター, 2).Value) = 0 Then
Exit Do
End If
If Rows.Count = 記入基点セル.Offset(ターゲット行カウンター, 0).Row Then
MsgBox "これ以上データを作れません。(MAX:" & Rows.Count & "行)"
Exit Sub
End If
ターゲット行カウンター = ターゲット行カウンター + 1
Loop
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim ターゲットフォルダ As Object
Set ターゲットフォルダ = fso.GetFolder(リスト化フォルダパス)
Dim ファイル As Object
Dim 日付 As String
Dim 金額 As String
Dim 取引先 As String
For Each ファイル In ターゲットフォルダ.Files
Dim 拡張子無しのファイル名 As String
拡張子無しのファイル名 = fso.GetBaseName(ファイル.Name)
拡張子無しのファイル名パーツ = Split(拡張子無しのファイル名, "_")
' ファイル名から各項目を特定する。
日付 = ""
金額 = ""
取引先 = ""
If UBound(拡張子無しのファイル名パーツ) < 2 Then
' アンダーバー区切りのデータが3つ見つからない場合はファイル名がおかしいので、取引先列に入れちゃう。
取引先 = "【ファイル名NG】" & Join(拡張子無しのファイル名パーツ, "_")
Else
日付 = 拡張子無しのファイル名パーツ(0)
' 年月日の区切りが無い場合はフォーマット変化を試みる。
If Not IsDate(日付) Then
日付 = Format(日付, "####/##/##")
End If
日付 = CDate(日付)
金額 = 拡張子無しのファイル名パーツ(1)
' 取引先は金額より後ろの部分をまとめて入れる。
' 取引先の後に品名等入れてる場合は以下のコードでOK
' ---------------------------
' 取引先 = 拡張子無しのファイル名パーツ(2)
' ---------------------------
Dim arr() As String
ReDim arr(UBound(拡張子無しのファイル名パーツ) - 2)
For i = 2 To UBound(拡張子無しのファイル名パーツ)
arr(i - 2) = 拡張子無しのファイル名パーツ(i)
Next i
取引先 = Join(arr, "_")
End If
' 日付
With 記入基点セル.Offset(ターゲット行カウンター, 0)
.Value = 日付
.NumberFormatLocal = "yyyy""年""mm""月""dd""日"""
.Borders.LineStyle = xlContinuous
End With
' 金額
With 記入基点セル.Offset(ターゲット行カウンター, 1)
.Value = 金額
.NumberFormatLocal = "#,##0;-#,##0"
.Borders.LineStyle = xlContinuous
End With
' 取引先
With 記入基点セル.Offset(ターゲット行カウンター, 2)
.Value = 取引先
.Borders.LineStyle = xlContinuous
End With
' ファイルパス
With 記入基点セル.Offset(ターゲット行カウンター, 3)
.Value = ファイル.Path
.Borders.LineStyle = xlContinuous
End With
If Rows.Count = 記入基点セル.Offset(ターゲット行カウンター, 0).Row Then
MsgBox "これ以上データを作れません。(MAX:" & Rows.Count & "行)"
Exit Sub
End If
ターゲット行カウンター = ターゲット行カウンター + 1
Next
MsgBox "リスト化完了"
End Sub