【誰にでも簡単にできる!】エクセルで残高試算表を作成

【誰にでも簡単にできる!】エクセルで残高試算表を作成 IT

会計ソフトを使わなくても、エクセルで仕訳帳から残高試算表を作ることができます。
残高試算表を切り分けると、貸借対照表と損益計算書ができあがります。

エクセルで残高試算表を作る方法として、SUMIF関数で直接集計する方法と、ピボットテーブルにより集計し、VLOOKUP関数で転記する方法を紹介します。

エクセルの関数で作った残高試算表、マクロで作った総勘定元帳を使っていますが、エクセルは好きなようにカスタマイズでき、会計ソフトにはない利便性があります。

残高試算表とは

青色申告特別控除で55万円を受けるには、正規の簿記の原則に従い、貸借対照表と損益計算書を作成する必要があります。
正規の簿記とは、一般的に複式簿記をいい、貸借が一致するように組織的に記帳することが求められます。
簡単にいうと、(借方)現金100/(貸方)売上100、というように、貸借が一致するように仕訳をちゃんとしてくださいということです。

記帳から決算までの流れは以下のとおりです。
取引発生→仕訳帳→総勘定元帳→残高試算表→決算書(貸借対照表、損益計算書)
仕訳や総勘定元帳への記帳は日々行い、残高試算表や決算書は、年末、決算期末に作成します。

残高試算表は、総勘定元帳の残高を記載して、貸借が一致していることを確認するために作成します。
残高試算表を切り分けると、貸借対照表と損益計算書ができあがるため、もとになる残高試算表の金額は大切です。

エクセルで残高試算表を作る理由

会計ソフトを利用するには費用がかかります。
会計ソフトの残高試算表はフォーマットが決まっており、融通もききません。

エクセルであれば、費用がかかりません。
自分が好きなようにカスタマイズすることができます。

取引が少なく、複雑な仕訳がないのであれば、エクセルで残高試算表を作るべきでしょう。

エクセルのSUMIF関数を使って、残高試算表を作成

以下のような仕訳があるとします。
シート名を仕訳帳とします。

以下のような残高試算表を作成します。

科目、借方貸方判定、期首残高をあらかじめ入力しておきます。
借方貸方判定は、借方科目が1、貸方科目-1になります。
借方貸方判定の使い方については、後ほど期末残高の計算のときに説明します。

Ctrl+Shift+@で式を表示させたものになります。

借方金額、貸方金額にSUMUIF関数の式を入力しています。
SUMIF関数によって、仕訳帳シートの借方科目と貸方科目から、残高試算表の科目を検索し、一致した場合に、その金額の合計を表示させます。

実際に、残高試算表の最初の科目である現金で確認してみます。
借方金額の式は、SUMIF(仕訳帳!C:C,A2,仕訳帳!E:E)になります。
仕訳帳シート(仕訳帳!)のC列(C:C)の借方科目から、このシートの科目である現金(A2)を検索し、一致した場合に、仕訳帳シート(仕訳帳!)のE列(E:E)の金額の合計を表示させます。

貸方金額の式も同じ内容で、仕訳帳シート(仕訳帳!)のD列(D:D)の貸方科目に変えたものになります。

現金の期末残高の式は、C2+B2*D2-B2*E2で、期首残高+借方貸方判定×借方金額-借方貸方判定×貸方金額になります。
なぜ、借方貸方判定をかける必要があるのでしょうか。

資産、費用科目は、期首残高+借方金額-貸方金額=期末残高になり、負債、資本、収益科目は、期首残高+貸方金額-借方金額=期末残高になるため、本来、期末残高の計算式を区別する必要があります。

そこで、資産、費用科目は、期首残高+(1)借方科目-(1)貸方科目=期末残高とし、負債、資本、収益科目は、期首残高+(-1)借方科目-(-1)貸方科目=期末残高とすることによって、計算式をF列のセルにコピーして貼り付けることができます。

エクセルのピボットテーブル、VLOOKUP関数を使って、残高試算表を作成

以下のように、ピボットテーブルにより、先ほどの仕訳帳シートの借方科目と貸方科目の金額を集計します。
シート名をピボットテーブルとします。

残高試算表は先ほどものと変わりません。

先ほど同様、科目、借方貸方判定、期首残高をあらかじめ入力しておきます。

Ctrl+Shift+@で式を表示させたものになります。

借方金額、貸方金額にIFERROR関数とVLOOKUP関数の式を入力しています。

まずはVLOOKUP関数の説明をします。
VLOOKUP関数によって、残高試算表の科目とピボットテーブルシートの借方科目と貸方科目が一致した場合に、ピボットテーブルシートの集計金額を転記させることができます。

実際に残高試算表の最初の科目である現金で確認してみます。
現金の借方金額の式は、VLOOKUP(A2,ピボットテーブル!A:B,2,FALSE)になります。

このシートの科目である現金(A2)とピボットテーブルシート(ピボットテーブル!)の範囲AB列(A:B)のA列の借方科目と一致した場合に、A列から2列目であるB列の集計金額を転記させます。

貸方金額の式も同じ内容で、ピボットテーブルシート(ピボットテーブル!)の範囲CD列(C:D)に変えたものになります。

最後にIFERROR関数の説明をします。
ピボットテーブルシートの借方科目と貸方科目に該当する科目がない場合、エラーが出ます。
その結果、期末残高が計算できなくなるため、IFERROR(VLOOKUP関数,”0″)を使い、エラーを0として表示させます。

誤りなく、エクセルで集計することが大切

最後に残高試算表の損益科目を切り分け、以下のような損益計算書を作り、損益状況を把握することができます。

SUMIF関数を使って、残高試算表を作る方が、シンプルでいいかもしれません。

仕訳帳から残高試算表を作るときに誤って集計してはいけません。
仕訳帳の金額、残高試算表の借方金額、貸方金額の合計を表示させ、一致していることを確認します。

エクセルの仕訳帳は1行を前提としているため、勘定科目が複数でてくる仕訳は若干手間がかかります。
例えば、建物200万円を300万円で売却し、現金100万円を受け取り、残りは後でもらうことにしたという取引があるとします。

仕訳は2行になります。
現金100万円/建物200万円
未収入金200万円/建物売却益100万円

エクセルの仕訳帳には3行で入力する必要があります。
現金/建物100万円
未収入金/建物100万円
未収入金/建物売却益100万円

消費税の税抜経理をすると、更に、仕訳の数が増えます。
そのような場合には会計ソフトに切り替えることを検討しましょう。

タイトルとURLをコピーしました