総勘定元帳(元帳)をExcelマクロで作るアイデア

  • URLをコピーしました!

Excelのみで経理をやる場合、総勘定元帳(元帳)が問題となります。
元帳作成には、マクロが必要です。
私が実際にやっている事例で解説します。
Excel 総勘定元帳

 

法人も個人も法律上、元帳が必要

必ず作らなければいけない「元帳」とは

法人も個人(個人事業、フリーランス)も、経理において最終的に、元帳(総勘定元帳)といわれるものが必要です。
これが青色申告の要件になっており、税務調査(税務署が数年に1回、実際に資料や領収書を見に来る)のときには、提示しなければいけません。

提示するのは、
・元帳
・領収書、レシート、請求書、契約書等
です。

決算書や税務申告書は提出していますので、税務署側が持っていますが、これらのものは、提出しません。
そのため、実際に見に来るしかないのです。
誤解が多いのは、伝票。
伝票は必ずしも必要ではありません。
領収書、レシートから、PCへ直接入力して大丈夫なのです。

会計ソフトは私にとっても使いにくく感じます。
従来型のソフト(PCにインストールするもの。弥生会計が代表的)であれ、クラウド型のソフトであれ、決定打がありません。
それでいて、お金はかかります。

Excelで経理をやりたいという方も多いはずです。
しかし、使い慣れたExcelに入力して、集計することは比較的簡単ですが、元帳を作るのは簡単ではありません。

元帳の具体的な事例

元帳とは、こんなものです。
たとえば、「現金で会議費を払った」という取引があります。
会議費/現金 340という仕訳です。
これを、現金側から見た「現金」の元帳へ転記し、
スクリーンショット 2015 04 03 10 10 12

会議費の側から見た、「会議費」の元帳へ転記します。
スクリーンショット 2015 04 03 10 11 10

元々は、手書きでこういった経理をやっていましたので、その間違いを防ぐためにこういった転記をする仕組みなのです。
(元帳の形式で入力することもできます。これが現金出納帳や預金出納帳です)
今は、ソフトでやりますので、自動的にこの転記をやってくれます。

Excelなら、この転記を1つずつ、自分でやらなければいけないのです。

Excelで普通に元帳を作るなら

Excelで元帳を作るなら、かなりの作業になります。

わかりやすいように、同じシートへ元帳を作ってみましょう。

仕訳のデータから、まず「現金」の元帳へ、日付、科目(会議費)、金額、摘要をコピーして貼り付け、残高を計算し、
スクリーンショット 2015 04 03 10 18 42

次に相手科目の「会議費」の元帳へ、日付、科目(会議費)、金額、摘要をコピーして貼り付け、残高を計算します。

スクリーンショット 2015 04 03 10 19 29

相手科目が「交際費」なら、交際費の元帳へコピペし、これをすべての科目でやらなければいけません。

Excelで作るならマクロが必須です。

※現実的には、Excelに入力し、集計して、経営判断にも使い、最終的に会計ソフトへ取り込んで、元帳を作るというのがおすすめです。
会計ソフトは元帳作成ソフトとしてだけ使うのです。
そうすれば、入力も集計も楽で、自分なりに使いやすくできるExcelの良さが最大限に活かせます。

クラウド会計ソフトのメリットである、ネットバンク取り込みも、Excelでできますし、長年私はこれをやっています。
【関連記事】預金データを会計ソフトに取り込む方法(基礎編) | EX-IT
リンク

 

 

Excelで、仕訳→集計の流れを作っておくのが前提

Excelに入力した仕訳データから元帳を作ることもできますが、問題もあります。
1つは、期首の残高。
B/S科目は、期首の残高を元帳に反映しなければいけません。
もう1つは、元帳作成科目の準備。
どの科目の元帳を作るか、どの順番で作るかの指定をしなければいけません。

こういった問題を解決するなら、Excelで仕訳→集計の流れを作っておく方が、元帳作成上も便利です。
【関連記事】Excel、マクロで試算表を作る方法ーピボットテーブル、VLOOKUPを活用ー | EX-IT
リンク

今回は、シンプルに1ヶ月分のみ集計し、消費税は免税である場合の事例でご紹介します。

入力は仕訳で行い(現金、預金を元帳形式で入力、変換することもできます)、
スクリーンショット 2015 04 03 10 27 47

このような集計表です。
推移表を作ることを想定していますので、B/S、P/Lを縦に並べます。
ファイルの構成は、
・data
仕訳データ
・集計
集計表
です。
マクロでは、「集計」で科目名や期首残高を取得し、「data」よりデータを抽出して新しいブックへ元帳を作ります。
スクリーンショット 2015 04 03 10 28 00

 

 

Excelマクロで元帳を作る方法

こんなマクロです。
事例に合わせて作っていますので、加工しないと使えません。
考え方を理解していただければと思います。

カーソルを置いたセルでマクロを実行すると、そのセルがある科目の元帳を作るものです。
一括で作るなら、このマクロを加工して、すべての科目で作るマクロにしましょう。

Sub leger()

    
    Dim Wb As Workbook
    Set Wb = ThisWorkbook
    
'カーソルがあるセルの行を取得
    Dim r As Long
    r = ActiveCell.Row
    
    Dim kamoku As String
    Dim Kishu As Long
    Dim Frg As Long
    
'元帳を作る科目名、期首残高、フラグを取得
    kamoku = Worksheets("集計").Cells(r, 2).Value
    Kishu = Worksheets("集計").Cells(r, 3).Value
    Frg = Worksheets("集計").Cells(r, 1).Value
    
    
'元帳作成科目を判定
    If Frg = 0 Then
        MsgBox ("該当するデータはありません")
        Exit Sub
    End If

    
    
'■借方を元帳へ
    


'新規ブック作成
    Workbooks.Add
    
'シート名を科目に
    ActiveSheet.Name = kamoku
    
'シートを変数にセット
    Dim w As Worksheet
    Set w = Worksheets(kamoku)
          
'元帳のフォーマットを作成
    w.Range("a1").Value = "日付"
    w.Range("b1").Value = "相手科目"
    w.Range("c1").Value = "借方"
    w.Range("d1").Value = "貸方"
    w.Range("e1").Value = "残高"
    w.Range("f1").Value = "摘要"
        
'フィルターで科目を抽出
    Wb.Worksheets("data").Range("a1").AutoFilter Field:=2, Criteria1:=kamoku
        
    Dim D_count As Long
    D_count = Wb.Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
    
    
'抽出データがあるかどうか判定
    If WorksheetFunction.Subtotal(3, Wb.Worksheets("data").Range("a:a")) > 1 Then
    
        '日付
        Wb.Worksheets("data").Range("a2", "a" & D_count).Copy
        w.Range("a3").PasteSpecial xlPasteValues
        
        '相手科目
        Wb.Worksheets("data").Range("c2", "c" & D_count).Copy
        w.Range("b3").PasteSpecial xlPasteValues
        
        '借方金額
        Wb.Worksheets("data").Range("d2", "d" & D_count).Copy
        w.Range("c3").PasteSpecial xlPasteValues
        
        '摘要
        Wb.Worksheets("data").Range("e2", "e" & D_count).Copy
        w.Range("f3").PasteSpecial xlPasteValues
        
    
    End If
    
    Wb.Worksheets("data").Range("a1").AutoFilter

'貸方を元帳へ
    Wb.Worksheets("data").Range("a1").AutoFilter Field:=3, Criteria1:=kamoku
   
    D_count = Wb.Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
    
'抽出データがあるかどうか判定
    If WorksheetFunction.Subtotal(3, Wb.Worksheets("data").Range("a:a")) > 1 Then
'貼り付け位置を指定
        Dim Paste As Long
        If w.Range("a3").Value < > 0 Then
            Paste = w.Range("a" & Rows.Count).End(xlUp).Row + 1
        Else
            Paste = 3
        End If
        
        '日付
        Wb.Worksheets("data").Range("a2", "a" & D_count).Copy
        w.Range("a" & Paste).PasteSpecial xlPasteValues
        
        '相手科目
        Wb.Worksheets("data").Range("b2", "b" & D_count).Copy
        w.Range("b" & Paste).PasteSpecial xlPasteValues
        
        '貸方金額
        Wb.Worksheets("data").Range("d2", "d" & D_count).Copy
        w.Range("d" & Paste).PasteSpecial xlPasteValues
        
        '摘要
        Wb.Worksheets("data").Range("e2", "e" & D_count).Copy
        w.Range("f" & Paste).PasteSpecial xlPasteValues

    End If
    
    'dataのフィルター解除
    Wb.Worksheets("data").Range("a1").AutoFilter
     
     
    '元帳へ期首残高を入力
    w.Range("e2").Value = Kishu
    
    
    '元帳を日付順にソート
    D_count = w.Range("a" & Rows.Count).End(xlUp).Row
    
    Range("a3", "i" & D_count).Sort key1:=Range("a3"), order1:=xlAscending
    
    '残高を計算
    Dim i As Long
    For i = 3 To D_count
        If Frg = 1 Then
            Range("e" & i).FormulaR1C1 = "=-rc[-1]+r[-1]c+rc[-2]"
        Else
            Range("e" & i).FormulaR1C1 = "=rc[-1]+r[-1]c-rc[-2]"
        End If
        
    Next
    
    '元帳のフォーマットを調整
    Columns("a:f").AutoFit
    Columns("a:a").NumberFormatLocal = "yyyy/mm/dd"
    Columns("c:e").NumberFormatLocal = "#,###"
    Columns("a:f").Interior.ColorIndex = xlNone
    
    '元帳をテーブルへ変換
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("a1", "f" & D_count), , xlYes).Name = kamoku


End Sub

ポイントを解説します。

期首の残高、科目、フラグ、フラグの取得

'カーソルがあるセルの行を取得
    Dim r As Long
    r = ActiveCell.Row
    
    Dim kamoku As String
    Dim Kishu As Long
    Dim Frg As Long
    
'元帳を作る科目名、期首残高、フラグを取得
    kamoku = Worksheets("集計").Cells(r, 2).Value
    Kishu = Worksheets("集計").Cells(r, 3).Value
    Frg = Worksheets("集計").Cells(r, 1).Value

セルがある位置で、科目、期首残高を取得します。
フラグとは、元帳で残高を計算するときに使うものです。
たとえば、現金なら、残高+借方金額ー貸方金額=残高と計算しますが、売上高は、残高+貸方金額ー借方金額=残高と計算します。
これを判断するフラグ(数値)です。

元帳のフォーマット作成

'新規ブック作成
    Workbooks.Add
    
'シート名を科目に
    ActiveSheet.Name = kamoku
    
'シートを変数にセット
    Dim w As Worksheet
    Set w = Worksheets(kamoku)
          
'元帳のフォーマットを作成
    w.Range("a1").Value = "日付"
    w.Range("b1").Value = "相手科目"
    w.Range("c1").Value = "借方"
    w.Range("d1").Value = "貸方"
    w.Range("e1").Value = "残高"
    w.Range("f1").Value = "摘要"

新規のブックを開き、シート名を科目名に変えて、元帳のフォーマットを作ります。

オートフィルターで、科目名を抽出

'フィルターで科目を抽出
    Wb.Worksheets("data").Range("a1").AutoFilter Field:=2, Criteria1:=kamoku
        
    Dim D_count As Long
    D_count = Wb.Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row

オートフィルターで、科目名を抽出し、そのデータ数を数えます。

スクリーンショット 2015 04 03 10 36 42

「現金」の場合は、こうなります。

コピーして元帳へ貼り付け

   
'抽出データがあるかどうか判定
    If WorksheetFunction.Subtotal(3, Wb.Worksheets("data").Range("a:a")) > 1 Then
    
        '日付
        Wb.Worksheets("data").Range("a2", "a" & D_count).Copy
        w.Range("a3").PasteSpecial xlPasteValues
        
        '相手科目
        Wb.Worksheets("data").Range("c2", "c" & D_count).Copy
        w.Range("b3").PasteSpecial xlPasteValues
        
        '借方金額
        Wb.Worksheets("data").Range("d2", "d" & D_count).Copy
        w.Range("c3").PasteSpecial xlPasteValues
        
        '摘要
        Wb.Worksheets("data").Range("e2", "e" & D_count).Copy
        w.Range("f3").PasteSpecial xlPasteValues
        
    
    End If
    
    Wb.Worksheets("data").Range("a1").AutoFilter

抽出したデータがある場合は、それをコピーして貼り付けます。
貼り付けは値のみ(書式なし)です。
このIF文を入れておかないと、データがない場合(現金入金がない場合)、元帳を正しく作れません。

貸方を貼り付け

'貸方を元帳へ
    Wb.Worksheets("data").Range("a1").AutoFilter Field:=3, Criteria1:=kamoku
   
    D_count = Wb.Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
    
'抽出データがあるかどうか判定
    If WorksheetFunction.Subtotal(3, Wb.Worksheets("data").Range("a:a")) > 1 Then
'貼り付け位置を指定
        Dim Paste As Long
        If w.Range("a3").Value < > 0 Then
            Paste = w.Range("a" & Rows.Count).End(xlUp).Row + 1
        Else
            Paste = 3
        End If
        
        '日付
        Wb.Worksheets("data").Range("a2", "a" & D_count).Copy
        w.Range("a" & Paste).PasteSpecial xlPasteValues
        
        '相手科目
        Wb.Worksheets("data").Range("b2", "b" & D_count).Copy
        w.Range("b" & Paste).PasteSpecial xlPasteValues
        
        '貸方金額
        Wb.Worksheets("data").Range("d2", "d" & D_count).Copy
        w.Range("d" & Paste).PasteSpecial xlPasteValues
        
        '摘要
        Wb.Worksheets("data").Range("e2", "e" & D_count).Copy
        w.Range("f" & Paste).PasteSpecial xlPasteValues

    End If
    
    'dataのフィルター解除
    Wb.Worksheets("data").Range("a1").AutoFilter

同様に、貸方でフィルターをかけて、データがあったらコピーして貼り付けます。
スクリーンショット 2015 04 03 10 36 56

この場合、貼り付け位置に注意です。
借方の場合、貼り付け位置は必ずセルA3になります。
貸方の場合は、借方の貼り付けがないときは、セルA3、貼り付けがあるときは、データの最終行+1の行に貼り付けなければいけません。

その処理がこの部分です。

'貼り付け位置を指定
        Dim Paste As Long
        If w.Range("a3").Value < > 0 Then
            Paste = w.Range("a" & Rows.Count).End(xlUp).Row + 1
        Else
            Paste = 3
        End If

フォーマットを整える

貼り付けたあと(値のみ貼り付けているのでこういう表示になります)、期首の残高を入れて、
スクリーンショット 2015 04 03 10 37 40

日付順にソートして、残高を計算します。
計算式は、前述のとおり、フラグで場合分けをします。

    '残高を計算
    Dim i As Long
    For i = 3 To D_count
        If Frg = 1 Then
            Range("e" & i).FormulaR1C1 = "=-rc[-1]+r[-1]c+rc[-2]"
        Else
            Range("e" & i).FormulaR1C1 = "=rc[-1]+r[-1]c-rc[-2]"
        End If
        
    Next

スクリーンショット 2015 04 03 10 38 02

最後に元帳のフォーマットを調整して、テーブルにして完成です。
(ここはお好みでやる部分です。)
実際は、横幅を1枚におさめるようにし、PDFにして保存するマクロも入れています。

    '元帳のフォーマットを調整
    Columns("a:f").AutoFit
    Columns("a:a").NumberFormatLocal = "yyyy/mm/dd"
    Columns("c:e").NumberFormatLocal = "#,###"
    Columns("a:f").Interior.ColorIndex = xlNone
    
    '元帳をテーブルへ変換
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("a1", "f" & D_count), , xlYes).Name = kamoku

スクリーンショット 2015 04 03 10 38 20

マクロをショートカットキーに設定して、選択した科目で元帳を出せるようにしておけば、残高のチェックにも使えます。
ただし、その元帳を修正しても、元のデータには反映されません。
もともとの仕訳データを修正する必要があります。

 

 

まとめ

一部のお客様を除き、Excelのこの仕組みを使っています。
自分の経理もこれです。
(会社分、フリーランス分、家計を仕訳で入力し、それぞれで集計しています。預金はネットバンクを加工しています)
消費税があると、さらに大変になりますが、しくみを作っておけば、自分好みの会計ソフトができあがります。

なお、科目の設定、カスタマイズの問題があるので、一般的に提供する予定は今のところありません。
一般的にかちっと作ってしまうと使いにくくなり、市販会計ソフトと同じになってしまうからです。
(それでもなんとかしようとは思っていますが)
税務顧問のお客様又は、Excelコンサルのお客様のみの提供とさせていただいております。

サンプルはこちらです。
EX-ITサンプル 元帳作成マクロ





【編集後記】
昨日は、午後にExcelコンサル2件。
仕事で使っている事例をお持ちいただいて、どう勧説すればいいか、Excelをどう考えればいいかについてお伝えしました。
セミナーでは、一般的な事例で基礎作りをし、コンサルでは、マンツーマンの個別対応で学べるようにしています。

【昨日の1日1新】
※詳細は→「1日1新」

ゼノブレイド 3DS
中目黒フレッシュネス

  • URLをコピーしました!