LibreOffice Calcのマクロで仕訳帳から総勘定元帳を作成する

投稿日 2022年10月11日 5:28 PM by 浅野直樹

やりたいことはタイトルの通りです。凝りだしていろいろな機能を追加したら記事が長くなってしまいました。

結論だけが欲しい方は以下のファイルをダウンロードしてご利用ください。

journal2ledger.ods

できるだけわかりやすく記述したつもりですから、このファイル内のマクロだけを読んでもある程度理解できると思います。

デザインには頓着せず、ロジックだけを記載しています。

以下で理屈を説明しますので、改良やデザインの調整などご自由にしてください。

１．はじめに

個人事業や小さい法人では、専用の会計ソフトを使わずにエクセル等のスプレッドシートで会計管理をすることが多いと思います。

青色申告をするためには複式簿記により仕訳帳を日々つけることになります。そしてその仕訳帳をもとにして、総勘定元帳に転記し、試算表にまとめて、損益計算書と貸借対照表を作成するという流れになります。

仕訳帳から総勘定元帳への転記をLibreOffice Calcのマクロで自動化しようというのがこの記事の趣旨です。

エクセルだと会計ソフト不要!?エクセルを使って総勘定元帳（帳簿）を作るやコピペ可！Excelマクロによる総勘定元帳作成 | 公務員×不動産投資の記事がヒットしましたが、私が調べた限りLibreOffice Calcでは見つからなかったので、自分で作ったという次第です。

２．試算表

上記のエクセルを使う場合の記事にも書かれていますように、試算表を作るほうが簡単です。マクロを使わずに関数だけで実現できます。ここでも先に試算表の説明をします。

下図のような仕訳帳があるとします。

そこから以下ような試算表を作りたいということです。

（１）借方合計と貸方合計

借方合計と貸方合計の列に入力する関数を、D3セルについて考えます。

A列に入力されている勘定科目に一致する金額を合計するので、sumif関数を使います。大ざっぱには以下のような感じです。

=SUMIF(仕訳帳.D2:D10000, A3, 仕訳帳.E2:E10000)

仕訳帳に入力されているデータを全部集計するだけならこれでもよいです。私は開始行と終了行を指定して範囲を区切って集計したかったので、sumif関数の範囲指定にoffset関数を使いました。

「仕訳帳.D2:D10000」の部分を「OFFSET(仕訳帳.D1, K1-1, 0, K2-K1+1, 1)」と書き換えました。仕訳帳シートのD1セルを基準にして、開始行−1だけ下がったセルを範囲の開始にして、終了行−開始行＋1で範囲として選択すべき行数を算出し、1列を範囲にするという式です。「仕訳帳.E2:E10000」の部分も同様に書き換えます。

=SUMIF(OFFSET(仕訳帳.D1, K1-1, 0, K2-K1+1, 1), A3, OFFSET(仕訳帳.E1, K1-1, 0, K2-K1+1, 1))

行方向にドラッグできるように必要な部分を$マークで固定します（仕訳帳の列は2行おきになっているので列方向にはドラッグできません）。

=SUMIF(OFFSET(仕訳帳.D$1, K$1-1, 0, K$2-K$1+1, 1), A3, OFFSET(仕訳帳.E$1, K$1-1, 0, K$2-K$1+1, 1))

これを行方向にドラッグすれば借方合計のD列は完成です。

貸方合計のE列はE3セルに次のように入力して、行方向にドラッグします。

=SUMIF(OFFSET(仕訳帳.F$1, K$1-1, 0, K$2-K$1+1, 1), A3, OFFSET(仕訳帳.G$1, K$1-1, 0, K$2-K$1+1, 1))

（２）期首残高

期首残高は、仕訳帳の最初のデータ行を開始行とみなし、指定した開始行の1つ前の行を終了行と考えて、借方合計から貸方合計を引くことにより求めます。C3セルについて考えます。

その借方合計は次の式で求めることができます。

=SUMIF(OFFSET(仕訳帳.D$1, 1, 0, K$1-2, 1), A3, OFFSET(仕訳帳.E$1, 1, 0, K$1-2, 1))

貸方合計も同じように考えて、引き算をするのは次の式になります。

=SUMIF(OFFSET(仕訳帳.D$1, 1, 0, K$1-2, 1), A3, OFFSET(仕訳帳.E$1, 1, 0, K$1-2, 1))-SUMIF(OFFSET(仕訳帳.F$1, 1, 0, K$1-2, 1), A3, OFFSET(仕訳帳.G$1, 1, 0, K$1-2, 1))

これでほぼ完成なのですが、開始行として仕訳帳の最初のデータ行を指定するとエラーになります。そこでその判定をif関数でします。

=IF(K$1=2, 0, SUMIF(OFFSET(仕訳帳.D$1, 1, 0, K$1-2, 1), A3, OFFSET(仕訳帳.E$1, 1, 0, K$1-2, 1))-SUMIF(OFFSET(仕訳帳.F$1, 1, 0, K$1-2, 1), A3, OFFSET(仕訳帳.G$1, 1, 0, K$1-2, 1)))

行方向にドラッグすればC列の期首残高は完成です。

（３）調整後残高と期末残高

まずはF列の調整前残高を単純に借方合計から貸方合計を引いて計算します。

=D3-E3

これだと貸方の金額のほうが大きくなると残高がマイナスになるので、通例貸方に登場する勘定科目はB列に-1を入力して調整します。通例借方に登場する勘定科目は1を入力します。

G列の調整後残高は調整前残高にB列の借方貸方符号をかけます。

=F3*B3

H列の期末残高は、期首残高に調整前残高を加えます。

=C3+F3

F列からH列はそのまま行方向にドラッグすればよいです。

（４）合計確認

1行目に合計を用意して数字が合っているかを確認することをおすすめします。

=SUM(C3:C500)

例えばC1セルに上のように入力して列方向にドラッグします。

C列の期首残高とF列の調整前残高とH列の期末残高の合計はそれぞれ0になるはずですし、D列の借方合計とE列の貸方合計は同じ数字になるはずです。

 

３．仕訳帳で用いられている全勘定科目の書き出し

試算表のA列には仕訳帳で用いる全勘定科目を入力します。手作業で入力してもよいのですが、せっかくなので仕訳帳で用いられている全勘定科目を自動で取得するマクロを作りました。

そのためのsubプロシージャの本体はこれです。

'subプロシージャをまたいで使う変数の宣言
Dim TmpSheet As Object

'仕訳帳で用いられている全勘定科目の一覧を「試算表」シートのL列に書き出す
Sub WriteAllAccounts
    '「勘定科目一覧」一時シートを作成し、TmpSheet変数に格納する…（１）
    TmpSheet = CreateTmpSheet

    '仕訳シートの借方と貸方の勘定科目を全部「勘定科目一覧」一時シートのA列にコピーする…（２）
    CopyAllAccounts

    '「勘定科目一覧」一時シートにフィルターを適用して重複を非表示にする…（３）
    FilterAccounts

    '重複が非表示になっている「勘定科目一覧」一時シートのA列を「試算表」シートのL列にコピーする…（４）
    CopyAllDistinctAccounts

    '「勘定科目一覧」一時シートを削除
    If ThisComponent.Sheets.hasByName("勘定科目一覧") Then
        ThisComponent.Sheets.removeByName("勘定科目一覧")
    Endif
End Sub

TmpSheetはsubプロシージャをまたいで使われます。（１）〜（４）は独自に作成した関数やプロシージャです。順番に説明します。

（１）「勘定科目一覧」一時シートを作成し、TmpSheet変数に格納する

'「勘定科目一覧」一時シートを作成して返す
Function CreateTmpSheet() As Object
    With ThisComponent.Sheets
        'すでに「勘定科目一覧」シートがあれば削除する
        If .hasByName("勘定科目一覧") Then
            .removeByName("勘定科目一覧")
        Endif
        .insertNewByName("勘定科目一覧", 3)
        CreateTmpSheet = .getByName("勘定科目一覧")
    End With
End Function

何度も繰り返してThisComponent.Sheetsと書くのは面倒なので、Withブロックで囲んでいます。

まっさらな「勘定科目一覧」シートを作りたいので、すでに「勘定科目一覧」シートがあれば削除します。

シートの扱い方はMacros/Basic/Calc/Sheets – The Document Foundation Wikiにまとまっています。

（２）仕訳シートの借方と貸方の勘定科目を全部「勘定科目一覧」一時シートのA列にコピーする

下図のような状態がここでの目標です。

'仕訳シートの借方と貸方の勘定科目を全部「勘定科目一覧」一時シートのA列にコピーする
Sub CopyAllAccounts
    '変数の設定
    Dim LastRow As Long
    Dim DebitAccounts As Object
    Dim CreditAccounts As Object

    '仕訳シートの借方と貸方の勘定科目を全部「勘定科目一覧」一時シートのA列にコピーする
    JournalSheet = ThisComponent.Sheets.getByName("仕訳帳")
    LastRow = GetLastRow(JournalSheet)
    DebitAccounts = JournalSheet.getCellRangeByPosition(3, 1, 3, LastRow-1)
    CreditAccounts = JournalSheet.getCellRangeByPosition(5, 1, 5, LastRow-1)
    TmpSheet.copyRange(TmpSheet.getCellByPosition(0, 0).CellAddress, DebitAccounts.RangeAddress)
    TmpSheet.copyRange(TmpSheet.getCellByPosition(0, LastRow-1).CellAddress, CreditAccounts.RangeAddress)
End Sub

そのシートでデータが入力されている最終行を取得するという操作は頻繁につかうので、GetLastRowという関数を作りました。

'そのシートでデータが入力されている最後の行を返す
Function GetLastRow(Sheet As Object) As Long
    '変数の宣言
    Dim Cell As Object
    Dim Cursor As Object

    '最終行の取得（https://wiki.documentfoundation.org/Macros/Basic/Calc/Ranges#Cursors）
    Cell = Sheet.getCellRangeByName("A1")
    Cursor = Sheet.createCursorByRange(Cell)
    Cursor.gotoEnd()
    GetLastRow = cursor.RangeAddress.EndRow + 1
End Function

対象シートのA1セルにカーソルを置き、カーソルを下に進めて最終行を算出しています。Macros/Basic/Calc/Ranges – The Document Foundation Wikiを参考にしました。

CopyAllAccountsプロシージャに戻って説明を続けますと、仕訳帳の借方科目と貸方科目の範囲をそれぞれコピーし、それを全部「勘定科目一覧」一時シートのA列にコピーしています。セル範囲の操作はMacros/Basic/Calc/Ranges – The Document Foundation Wikiにまとまっています。

（３）「勘定科目一覧」一時シートにフィルターを適用して重複を非表示にする

先ほどの状態から空白でない値で重複を無視するという標準フィルターを適用します。そうすると下図のようになります。

'「勘定科目一覧」一時シートにフィルターを適用して重複を非表示にする（https://wiki.documentfoundation.org/Macros/Calc/ba026）
Sub FilterAccounts
    '変数の設定
    Dim FilterDesc As Object
    Dim Fields(0) As New com.sun.star.sheet.TableFilterField

    FilterDesc = TmpSheet.createFilterDescriptor(True) 
    With Fields(0)
        .Field = 0
        .Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
    End With

    FilterDesc.setFilterFields(Fields())
    FilterDesc.SkipDuplicates = True
    TmpSheet.filter(FilterDesc)
End Sub

ほぼCreate Standard Filter – The Document Foundation Wikiそのままです。LibreOffice Calcでuniq(重複行を削除)する | Days of speedのように標準フィルターを適用するという作業をマクロ化しています。

（４）重複が非表示になっている「勘定科目一覧」一時シートのA列を「試算表」シートのL列にコピーする

'重複が非表示になっている「勘定科目一覧」一時シートのA列を「試算表」シートのL列にコピーする
Sub CopyAllDistinctAccounts
    '変数の設定
    Dim TrialBalanceSheet As Object
    Dim LastRow As Long
    Dim AllDistinctAccounts As Object

    '重複が非表示になっている「勘定科目一覧」一時シートのA列を「試算表」シートのL列にコピーする
    LastRow = GetLastRow(TmpSheet)
    AllDistinctAccounts = TmpSheet.getCellRangeByPosition(0, 0, 0, LastRow-1)
    TrialBalanceSheet = ThisComponent.Sheets.getByName("試算表")
    TrialBalanceSheet.getColumns.removeByIndex(11,1)
    TrialBalanceSheet.copyRange(TrialBalanceSheet.getCellRangeByName("L3").CellAddress, AllDistinctAccounts.RangeAddress)
End Sub

TrialBalanceSheet.getColumns.removeByIndex(11,1)でL列を削除してから、TrialBalanceSheet.copyRange(TrialBalanceSheet.getCellRangeByName(“L3”).CellAddress, AllDistinctAccounts.RangeAddress)でL3セルを起点にして貼り付けています。

４．総勘定元帳の作成

いよいよここからが本番です。総勘定元帳を作成する本体となるsubプロシージャです。

'subプロシージャをまたいで使う変数の宣言
Dim JournalSheet As Object

'総勘定元帳を作成する
Sub CreateGeneralLedger
    '変数の宣言
    Dim StartRow As Long
    Dim EndRow As Long

    '変数の設定
    JournalSheet = ThisComponent.Sheets.getByName("仕訳帳")
    StartRow = JournalSheet.getCellRangeByName("K1").value
    EndRow = JournalSheet.getCellRangeByName("K2").value

    '不要なシートを削除する…（１）
    DeleteUnnecessarySheets

    '仕訳シートの各行を総勘定元帳に転記する…（２）
    WriteLedgers(StartRow, EndRow)

    '総勘定元帳シートを試算表の並び順にソートする…（３）
    SortSheets
End Sub

JournalSheet変数はsubプロシージャをまたいで使われます。仕訳帳シートのK1セルとK2セルに記入された開始行と終了行を読み取り、（１）〜（３）を実行します。

（１）不要なシートを削除する

すでに総勘定元帳シートなどがあればそれを削除します。必要なシートは「仕訳帳」、「試算表」、「元帳ブランク」の3つだけです。

'不要なシート（仕訳・試算表・元帳ブランク以外のシート）を削除する
Sub DeleteUnnecessarySheets
    Dim i As Long
    Dim SheetName As String
    For i = ThisComponent.Sheets.Count - 1 To 0 Step -1
        SheetName = ThisComponent.Sheets.getByIndex(i).Name
        If SheetName <> "仕訳帳" And SheetName <> "試算表" And SheetName <> "元帳ブランク" Then
            ThisComponent.Sheets.removeByName(SheetName)
        End If
    Next
End Sub

インデックス（ブック内のシートの位置）でシートを指定しています。シートを削除するとインデックスが繰り上がってしまうので、繰り上がりが発生してもすべてのシートを走査できるように逆順でループを回しています。

（２）仕訳シートの各行を総勘定元帳に転記する

ここが最大の山場です。

'仕訳シートの各行を総勘定元帳に転記する
Sub WriteLedgers(StartRow As Long, EndRow As Long)
    '変数の宣言
    Dim i As Long
    Dim TransactionDate As Date
    Dim Debit As String
    Dim DebitAmount As Currency
    Dim Credit As String
    Dim CreditAmount As Currency
    Dim Summary As String

    For i = StartRow To EndRow
        '各行について必要な変数データの設定

        '分離して入力されている年・月・日を組み立ててDate型のTransactionDate変数に格納する…①
        TransactionDate = GetTransactionDate(i)

        With JournalSheet
            '年月日一体型の日付を仕訳帳のC列に入れる場合は「TransactionDate = GetTransactionDate(i)」をコメントアウトして代わりに下の行をコメントイン
            'TransactionDate = .getCellByPosition(2, i-1).value
            Debit = .getCellByPosition(3, i-1).String
            DebitAmount = .getCellByPosition(4, i-1).value
            Credit = .getCellByPosition(5, i-1).String
            CreditAmount = .getCellByPosition(6, i-1).value
            Summary = .getCellByPosition(7, i-1).String
        End With

        '借方の勘定科目に対応する総勘定元帳シートがなければ作成する…②
        CreateLedgerSheetIfNotExists(Debit)

        '借方の勘定科目を総勘定元帳に転記する…③
        Transcribe(TransactionDate, Debit, DebitAmount, Credit, Summary, True)

        '貸方の勘定科目に対応する総勘定元帳シートがなければ作成する…②
        CreateLedgerSheetIfNotExists(Credit)

        '貸方の勘定科目を総勘定元帳に転記する…③
        Transcribe(TransactionDate, Credit, CreditAmount, Debit, Summary, False)
    Next
End Sub

仕訳帳の各行のデータを読み込み、借方、貸方の順番で処理していきます。年月日一体型の日付を仕訳帳のC列に入れる場合は①を考えなくてすみます。

①分離して入力されている年・月・日を組み立ててdate型のTransactionDate変数に格納する

最初から仕訳帳にDate型で入力していれば単に入力値を取得するだけでよかったのですが、会計ソフトのように年・月・日を分離して記載したかったのでこの処理を考えました。

'分離して入力されている年・月・日を組み立ててDate型で返す
Function GetTransactionDate(i As Long) As Date
    '変数の宣言
    Dim TransactionYear As Long
    Dim TransactionMonth As Long
    Dim TransactionDay As Long

    '年・月・日を取得してDate型にして返す
    TransactionYear = GetValueOfThisCellOrUpperCell(i, 0)
    TransactionMonth = GetValueOfThisCellOrUpperCell(i, 1)
    TransactionDay = GetValueOfThisCellOrUpperCell(i, 2)
    GetTransactionDate = DateSerial(TransactionYear, TransactionMonth, TransactionDay)
End Function

年・月・日をそれぞれ取得して、DateSerial関数でDate型にします。

年・月・日は、そのセルに値が入っていればその値を、そうでなければ直近の上の行の値を取得します。

'仕訳帳の日付の書き方から、空白の場合は上にさかのぼって年・月・日の数字を取得する
Function GetValueOfThisCellOrUpperCell(i As Long, column As Long) As Long
    '変数の宣言
    Dim TargetCellValue As Long

    'そのセルの値を取得し、0でなければその値を返し、0なら再帰的にこの関数を適用する
    TargetCellValue = JournalSheet.getCellByPosition(column, i-1).value
    If TargetCellValue <> 0 Then
        GetValueOfThisCellOrUpperCell = TargetCellValue
    Else
        GetValueOfThisCellOrUpperCell = GetValueOfThisCellOrUpperCell(i-1, column)
    Endif
End Function
②借方または貸方の勘定科目に対応する総勘定元帳シートがなければ作成する
'その勘定科目名のシートが存在しなければ「元帳ブランク」シートをコピーすることにより作成し、A1セルにその勘定科目名を記入する
Sub CreateLedgerSheetIfNotExists(SheetName As String)
    If Not ThisComponent.Sheets.hasByName(SheetName) Then
        ThisComponent.Sheets.copyByName("元帳ブランク", SheetName, 3)
        ThisComponent.Sheets.getByName(SheetName).getCellRangeByName("A1").string = SheetName
    Endif
End Sub

見たままのコードかなと思います。

元帳ブランクシートのE3の期首残高セルには「=VLOOKUP(A1, $試算表.A3:C500, 3, 0)」という関数を仕込んであります。その元帳ブランクをコピーして作成した総勘定元帳の勘定科目シート名をA1セルに記入することでその勘定科目の期首残高を試算表から取得するという仕組みです。

また、元帳ブランクシートのA列は日付形式に、C列からE列はカンマ桁区切りの数値形式にセルの書式設定をしてあります。

③借方または貸方の勘定科目を総勘定元帳に転記する
'借方または貸方を総勘定元帳に転記する
Sub Transcribe(TransactionDate As Date, Account As String, Amount As Long, CorrespondingAccount As String, Summary As string, DebitFlag As Boolean)
    '変数の宣言
    Dim LedgerSheet As Object
    Dim LastRow As Long

    '変数の設定
    LedgerSheet = ThisComponent.Sheets.getByName(Account)
    LastRow = GetLastRow(LedgerSheet)

    '取引日、相手科目、金額、残高、摘要の記入
    With LedgerSheet
        .getCellByPosition(0, LastRow).value = TransactionDate
        .getCellByPosition(1, LastRow).string = CorrespondingAccount
        '金額の入力欄と残高の計算式は借方かどうかで条件分岐
        If DebitFlag Then
            .getCellByPosition(2, LastRow).value = Amount
            .getCellByPosition(4, LastRow).value = .getCellByPosition(4, LastRow-1).value + Amount
        Else
            .getCellByPosition(3, LastRow).value = Amount
            .getCellByPosition(4, LastRow).value = .getCellByPosition(4, LastRow-1).value - Amount
        Endif
        .getCellByPosition(5, LastRow).string = Summary
    End With
End Sub

取引日、勘定科目、金額、相手科目、摘要、借方フラグを引数に取っています。

取引日、相手科目、金額、残高、摘要のそれぞれを適切な箇所に転記しています。

（３）総勘定元帳シートを試算表の並び順にソートする

試算表は慣行や必要性に従って並び順を整理することになるでしょうから、総勘定元帳もその順番でソートします。

'総勘定元帳シートを試算表の並び順にソートする
Sub SortSheets
    '変数の宣言
    Dim i As Long
    Dim TrialBalanceSheet As Object
    Dim LastRow As Long
    Dim Account As String

    '変数の設定
    TrialBalanceSheet = ThisComponent.Sheets.getByName("試算表")
    LastRow = GetLastRow(TrialBalanceSheet)

    '試算表シートのA列の3行目から勘定科目を順に読み込み、その勘定科目名のシートがあれば末尾に移動させる
    For i = 3 To LastRow
        With ThisComponent.Sheets
            Account = TrialBalanceSheet.getCellByPosition(0, i-1).string
            If .hasByName(Account) Then
                .moveByName(Account, .count)
            End If
        End With
    Next
End Sub

少しややこしいですが、これも見たままかなと思います。

５．思考プロセスその他細かいことなど

最初は「Option VBASupport 1」を設定してエクセルのVBA互換で作ろうかなとも思ったのですが、何が互換していて何が互換していないとかを考えるのが面倒だったので、どうせなら自分で一から作ろうと決意しました。

LibreOffice固有のマクロを作るとして、basicにするかpythonにするかも少し悩みました。しかしマクロでpythonを使うための資料が少なく、環境構築も面倒そうだったので、basicにしました。pythonを使うのであれば、マクロではなく、pythonをベースにしてデータを読み込んで処理する形にするほうが見通しがよいでしょう。

「Option Explicit」を設定して変数は宣言するようにしています。String, Long, Date, Objectという分類で型指定もしています。

わかりやすさのために、関数やsubプロシージャは細かく切り出しています。戻り値が必要な場合は関数を、そうではなく処理をするだけの場合はsubプロシージャを使っています。

関数や変数の命名についてもわかりやすさを重視して長い名前を付けました。

手元の環境で数百件の仕訳データから総勘定元帳を作成すると数十秒かかりました。これくらいなら許容範囲ですから、速度のことを特に意識はしていません。年月日一体型の日付を仕訳帳に入力するようにすれば処理が減って時間が短縮されます。

不要なシートを削除するDeleteUnnecessarySheetsプロシージャを作る際に、ループを前から順番に回して削除されるシートと削除されないシートが出てきたことに悩みました。オブジェクトのディープコピーをして解決する方向も探りましたが難しそうだったので断念し、ループを逆順で回すという解決に落ち着きました。

これだけを作るのに何日もかかりました。いろいろ検索して調べようとしても資料が少なかったです。本もほとんどなさそうですし。この記事中でも示した公式ドキュメントも貧弱で、試行錯誤を繰り返しました。

 




コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です