エクセルで青色申告をしよう(8)　仕訳帳から総勘定元帳を作る

前回は、4つの簡易帳簿（と場合によっては特定取引仕訳帳）から仕訳帳を作りました。

今回は、エクセルを使って仕訳帳から総勘定元帳を作りたいと思います。

仕訳帳のときほど複雑な数式は使わないので、ご安心ください。

総勘定元帳とは、各勘定科目の元帳の集まりのことです。

元帳とは、例えば、現金元帳であれば、現金が借方または貸方に来る取引をすべてまとめたものです。

元帳（現金）

日付	相手科目	摘要	借方金額	貸方金額	残高
1/31	普通預金	事業用口座から現金引き出し	5,000		5,000

エクセルを使えば仕訳帳から元帳に簡単に転記できますので、そのやり方をご紹介します（注1）。

１．元帳のフォーマットを作る

以下では、現金元帳の作り方を説明しますが、他の勘定科目の元帳でも作り方は一緒です。

まずは、以下のように、一つのシートに仕訳帳のフォーマットと元帳のフォーマットを作成します。

また、仕訳帳の方には、フィルタの設定をしておきます（エクセルの「並び替えとフィルタ」→「フィルタ」で設定できます）。

２．元帳の横の仕訳帳にコピーする

続いて、前回作成した仕訳帳のページに戻ります。

借方科目の三角ボタンを押して、「現金」だけをチェックします。最初に「すべて選択」を押すと、全部のチェックが解除されるので、その次に「現金」をチェックすると、簡単に現金だけをチェックできます。

現金だけをチェックすると以下のようになります。

これで仕訳した取引のうち借方科目に現金が来る取引だけが全て表示されました。

ここで表示されたものを全てコピーして、現金元帳の横の仕訳帳のフォーマットにリンク貼り付けをします。

続いて、仕訳帳のシートに戻り、借方科目の三角ボタンから「すべて選択」を押して、選択を解除します。

そして、今度は、貸方科目の三角ボタンから現金だけを選択します。

これで、貸方科目に現金が来る取引がだけ全て表示されますので、先ほどと同じように、コピーして、現金元帳の横の仕訳帳のフォーマットにリンク貼り付けをします。

このままの状態だと日付が早い順になっていないので、日付の三角ボタンから「昇順」を選択して、順番を並び替えます。

これで、仕訳された取引の中で、現金が借方科目または貸方科目に来るものが全て現金元帳の横の仕訳帳にコピーされました。

３．仕訳帳から元帳に転記する

続いて、仕訳帳から現金元帳に転記していきます。

なお、フリーランス1年目の方は、前年からの繰越はありませんが、2年目以降の方は、前期末の現金の金額を入力して下さい。

３．１　元帳の日付欄    

元帳の日付欄には、仕訳帳の日付欄と同じものが表示されるようにします。

G7のセル（現金元帳の三行目の日付欄）には、以下の数式を入力します。

=A7

３．２　元帳の相手科目欄    

元帳の相手科目欄には、現金とセットになっている勘定科目が表示されるようにします。

つまり、仕訳帳の借方科目が現金の場合は、貸方科目の勘定科目が表示され、借方科目が現金以外の場合は、借方科目の勘定科目が表示されるようにすればいいわけです。

H7のセル（元帳の三行目の相手科目欄）には、以下の数式を入力します。

=IF(C7=”現金”,D7,C7)　

これは、C7のセル（仕訳帳の三行目の借方科目欄）が現金のときは、D7のセル（仕訳帳の三行目の貸方科目欄）と同じものを表示し、そうでないときは、C7のセルと同じものを表示する、という意味です。

３．３　元帳の摘要欄    

元帳の摘要欄には、仕訳帳の摘要欄と同じものが表示されるようにします。

I7のセル（現金元帳の三行目の摘要欄）には、以下の数式を入力します。

=B7

３．４　元帳の借方金額欄    

仕訳帳の借方科目が現金のときは、元帳の借方金額欄に仕訳帳の金額が表示されるようにし、貸方金額欄には何も表示されないようにします。

また、仕訳帳の貸方科目が現金のときは、元帳の貸方金額欄に仕訳帳の金額が表示されるようにし、借方金額欄には何も表示されないようにします。

そのために、J7のセル（元帳の三行目の借方金額欄）には、以下の数式を入力します。

=IF(C7=”現金”,E7,0)　

これは、C7のセル（仕訳帳の三行目の借方科目欄）が現金のときは、E7のセル（仕訳帳の三行目の金額欄）と同じ数値を表示し、そうでないときは、0（ゼロ）を表示する、という意味です。

３．５　元帳の貸方金額欄    

借方金額欄で説明したとおり、仕訳帳の貸方科目が現金のときは、元帳の貸方金額欄に仕訳帳の金額が表示されるようにし、そうでないとき（仕訳帳の借方科目が現金のとき）は、元帳の貸方金額欄には何も表示されないようにします。

そのために、K7のセル（元帳の三行目の貸方金額欄）には、以下の数式を入力します。

=IF(D7=”現金”,E7,0)　

これは、D7のセル（仕訳帳の三行目の貸方科目欄）が現金のときは、E7のセル（仕訳帳の三行目の金額欄）と同じ数値を表示し、そうでないときは、0（ゼロ）を表示する、という意味です。

なお、このとき貸方金額欄に「0」が表示される場合は、「エクセルのオプション」→「詳細設定」で、「ゼロ値のセルにゼロを表示する」のチェックを解除することで、空欄にすることができます。

３．６　元帳の残高金額欄    

貸借対照表、損益計算書の借方（左側）に位置する「資産」と「費用」については、増えると借方金額に記録し、減ると貸方金額に記録しますので、残高は、借方金額から貸方金額を引いた金額の合計となります。

反対に、貸借対照表、損益計算書の貸方（右側）に位置する「負債」と「収益」については、増えると貸方金額に記録し、減ると借方金額に記録しますので、残高は、貸方金額から借方金額を引いた金額の合計となります。

現金は資産の勘定科目ですので、借方金額から貸方金額を引いた金額を合計します。

以上から、L7のセル（元帳の三行目の残高金額欄）には、以下の数式を入力します。

=L6+J7-K7　

以上で、三行目の入力が終わりましたので、四行目以降にもコピーすれば、現金元帳の完成です。

４．他の勘定科目の元帳を作る

現金元帳と同じ要領で、他の勘定科目の元帳も作っていきます。

現金元帳のシートをコピーして、元帳内の数式の”現金”とある部分を、別の勘定科目に変えれば簡単に他の元帳も作れます。

例えば、現金元帳をコピーして、普通預金元帳を作ろうとすると、元帳に入力した数式も普通預金に対応したものにする必要があります。

まず、上のように、左側の仕訳帳部分には、先ほどの手順で、普通預金が借方または貸方にくる取引を並べます。

右側の元帳部分は、現金元帳をコピーしただけですので、まだ普通預金元帳に対応していません。

そこで、数式内の”現金”となっている部分を”普通預金”に変えます。

そうすると、次のようになります。

これで、普通預金元帳の完成です。

但し、先ほども説明したように、残高金額については、資産、費用に分類される勘定科目と、負債、収益に分類される勘定科目では、逆の計算になるので注意が必要です。

例えば、未払金の場合、負債に分類されるので、上の図のように、残高金額の合計は、貸方金額から借方金額を引いた金額を合計したものになります。

この点だけ気をつければ、すべての勘定科目の元帳（総勘定元帳）も簡単に作れると思います。

５．補足説明

５．１　修正したい事項が出てきたとき    

仕訳帳、元帳のデータはすべて4つの簡易帳簿（現金出納帳、預金出納帳、売掛帳、経費帳）とリンクしています。

もし、修正したい事項が出てきたとき（例えば、金額を間違えて入力していた）は、元となった帳簿のデータを修正してください。そちらを修正すれば、仕訳帳、元帳のデータも全て修正後のものに反映されます。

５．２　仕訳忘れがあったとき    

元帳のデータは、仕訳帳の「特定の行」のデータとリンクしていますが、コピー元である仕訳帳の行がずれると、元帳のデータもずれてしまいます。

そのため、もし、仕訳を忘れた取引があり、仕訳帳に後から加える必要がでたとき（それにより仕訳帳の行がずれるとき）には、仕訳帳から各元帳シートの仕訳帳へのコピー＆リンク貼り付けをやり直す必要があるので注意してください。

６．終わりに

以上で、総勘定元帳の作成は完了です。

それでは、次回、いよいよ試算表それから貸借対照表と損益計算書を作りたいと思います。

ここまできたら残りは簡単な作業だけですので、もう少しだけ頑張ってください。

《参考文献》

注1　ここでご紹介する方法は、前回同様、笠原清明著「フリーで仕事を始めたらまっさきに読む経理・税金・申告の本」（株式会社クロスメディアパブリッシング・2014）p.177以下を参考にしたものです。

フリーで仕事を始めたらまっさきに読む経理・税金・申告の本 [ 笠原清明 ] 価格：1814円（税込、送料無料) (2018/3/27時点)