低所得の青色申告個人事業主のブログ

開業から経験で得た情報をお知らせしていきたいと思います。

= グローバル ナビゲーション =

【仕訳帳】【自作】【Excel】「総勘定元帳」Sheetの作成(3/3)


[ スポンサー リンク ]

 総勘定元帳 シート 作成の記事の続きです。

前の記事を読んでいない方はこちらからどうぞ。

自作するのはめんどうなので完成品がほしい、という方はこちらからどうぞ。

 

 

オートフィルタの設定

前回作成した明細の「勘定科目」列にオートフィルタを設定します。

私は「補助科目」列にも設定していますが、年に1回も使用しないので、この列は設定しなくても問題ありません。

f:id:ACC-DNTST:20190512124704j:plain

勘定科目の選択

 仕訳帳 に1行もないときは選択できません。

また 仕訳帳 で使っていない勘定科目は、 勘定科目 Sheet に登録があっても選択できません。

表示順(並び順)も 勘定科目 Sheet の並びとは関係ないので、勘定科目の頭にコードをつけて制御するなどしてください。

 総勘定元帳 として使用するには、対象の勘定科目を1つだけ選択してください。

昔のExcelと違い、機能的には複数選択が可能なので注意してください。

'(すべてを選択)'をクリックしてすべてにチェックを入れてから、もう一度'(すべてを選択)'をクリックするとすべてのチェックが外れます。

それから対象の勘定科目を選択すると、1つだけ選択することができます。

補助科目はフィルタリングせず、すべて選択にしてください。

 

フッタ部

借方金額貸方金額の総計をそれぞれ集計します。

f:id:ACC-DNTST:20210604182528p:plain

総勘定元帳-フッタ

フッタは明細行から1行空けます。

1行空けない場合は、明細部とみなされてフィルタリング時に消えてしまいます。

1行空けても消える場合は、数行空けて作成し1行残して空白行を消してください。

借方金額の合計は

計算式

= SUBTOTAL ( 9, [借方金額列] )

貸方金額の合計は

計算式

= SUBTOTAL ( 9, [貸方金額列] )

当然SUM関数では正しい数字が取れないので、SUBTOTAL関数を使います。

引数は '9' を指定しましたが、 '109' でも構いません。

'109' を指定すると、非表示に設定した行は集計対象外になります。

 

ヘッダ部:イメージ

f:id:ACC-DNTST:20210604185753p:plain
f:id:ACC-DNTST:20210604185839p:plain
総勘定元帳-ヘッダ

 

ヘッダ部:勘定科目

オートフィルタで選択された勘定科目を表示します。

計算式

= INDIRECT ( "B" & SUBTOTAL ( 5, [行番号列] ) )

オートフィルタがかかった勘定科目列がB列となる前提です。

実態に合わせて修正してください。

フィルタリングしていない(すべて選択)状態では、明細行の1行目(ヘッダ直下)の「(未選択)」と表示されるはずです。

ちなみに、オートフィルタで複数選択した場合は最初に出現した勘定科目が表示されます。

 

ヘッダ部:前期繰越

 勘定科目 シート で前期繰越を設定しない場合は不要です。

計算式

= VLOOKUP ( [勘定科目] , [勘定科目シートの該当範囲], [勘定科目シートの該当範囲の前期繰越の列番号], FALSE )

フィルタリングしていない(すべて選択)状態では、エラー表示です。

私は正しい表現だと思っているのですが、エラー表示が気にいらない方は ISERROR関数 などで回避してください。

 

ヘッダ部:期末残高

 勘定科目 シート で前期繰越を設定する前提です。

実態に合わせて修正してください。

計算式

= [前期繰越] + ( [借方金額総計] - [貸方金額総計] ) * VLOOKUP ( [勘定科目] , [勘定科目シートの該当範囲], [勘定科目シートの該当範囲の残高計算係数の列番号], FALSE )

こちらも、フィルタリングしていない(すべて選択)状態では、エラー表示です。

 


 

以上で 総勘定元帳 シート は完成です。

次回は 月別仕入月別売上 を集計します。

自作するのはめんどうなので完成品がほしい、という方はこちらからどうぞ。

ご質問は下の コメントを書く からお願いします。