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

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
中目黒フレッシュネス

■スポンサードリンク




井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』