このブログを検索

こぶり会計帳簿のカラクリ

こぶり会計帳簿は複式簿記システムと名乗ってはいるが、実はGoogleスプレッドシートのデータに過ぎない。

これをコピーして行を追加、削除し、入力値を変更する事で簡単に自分の会社のその年度の会計帳簿が完成する。

更に、機能は全て関数によって実現していて マクロ(Google Apps Script)は一切使っていないので、第三者でもセキュリティ面から安心して使える。

Googleスプレッドシートに特有の関数は使っていないので、Microsoft Excel 形式か OpenDocument 形式でダウンロードすれば、代表的な表計算ソフト上でも動作する。

シートの構成

こぶり会計帳簿は「仕訳」「集計」「諸表」「在庫」「説明」の5枚のシートで構成した。全てのシートは同じ横幅にしたので、印刷した時の一行の高さや文字の大きさも同じになる。
  • 「仕訳」シート…取引の数に応じて行を追加、削除する。
  • 「集計」シート…登録する勘定科目の数に応じて行を追加、削除する。
  • 「諸表」シート…持分会社で配当金を一切出さない設定。
  • 「在庫」シート…登録する商品の数に応じて行を追加、削除する。
  • 「説明」シート…使い方の説明。
行の追加は、淡黄色のセルのある行を指定して、行を挿入し、同一シート中の淡黄色のセルのある行(どれでもOK)をコピーすれば良いようにした。

従って、同一シートの淡黄色のセルのある行は、どの行でも全て同じ関数が入っている。

範囲と名前付き範囲

範囲はセルの番号で、A1:A3 のように参照するが、別のシートの範囲は 集計!A1:A3 のようにセルの番号の前にシート名を入れて参照する必要がある。

そして、集計!A1:A3 を「勘定科目」というように、範囲には名前を付ける事が出来る。

こぶり会計帳簿では、別のシートから参照される範囲には必ず名前を付けるようにしている。従って、関数中のセルの参照にシート名は現れない。

計算する範囲は、範囲内での行の追加や削除に応じて 表計算ソフトの機能で自動的に伸び縮みする。上記の例では、
  • 2行目を挿入すれば、A1:A3という範囲は自動的に A1:A4 へと書き換わる。
  • 2行目を削除すれば、A1:A3という範囲は自動的に A1:A2 へと書き換わる。
勿論、範囲外での行の追加や削除では、計算する範囲は変わらない。

その追加する行を行の並びの先頭や末尾にしようと考えるのは自然な事だが、そうすると、追加した行を自動的に範囲に含めては貰えない…という問題が発生する。

これを回避するには、範囲を行の並びよりも上と下に一行以上広く取ることだ。その余分な行に(その欄の合計値を表示するような)数式を入れると循環参照というエラーを起こす可能性があるので、余分な行には何の機能も入れずにダミー行として、機能を持たせたい場合は範囲外に置くようにする。

範囲の名前

Googleスプレッドシートのメニューから、 データ → 名前付き範囲… とすると、定義してる名前付き範囲の内容が右側に表示される。こぶり会計帳簿では、下記の12の名前を定義している。
  • 日付 … 仕訳シート A列 の1行目と最終行を除く範囲
  • 科目 … 仕訳シート C列 の1行目と最終行を除く範囲
  • 借方 … 仕訳シート D列 の1行目と最終行を除く範囲
  • 貸方 … 仕訳シート E列 の1行目と最終行を除く範囲
  • 品番 … 仕訳シート G列 の1行目と最終行を除く範囲
  • 出庫 … 仕訳シート H列 の1行目と最終行を除く範囲
  • 入庫 … 仕訳シート I列 の1行目と最終行を除く範囲
  • 期首残高 … 仕訳シート J列 の1行目と最終行を除く範囲
  • 科目設定 … 集計シート A列～M列 の1行目と 費用の部合計以降の行を除く範囲
  • 負資収 … 集計シート A列 の内、資産の部合計の以前の行と収益の部合計以降の行を除く範囲
  • 診断 … 諸表シート A列1行目のセル
  • 品番設定 … 在庫シート A列～B列 の1行目と最終行を除く範囲
診断を除く上記の名前の範囲は、実際に使う行よりも上下に1行づつ多くすることで行の挿入位置に関わらず範囲が確実に拡張するようにしている。

名前付き範囲は、その名前によって範囲の意味を表現出来る点が優れているが、名前が増え過ぎても分かりにくいので、他のシートからも参照されている範囲にのみ名前を付けた。

絶対参照と相対参照

セルの位置は、列を示すアルファベット(A、B、C、…)と行を示す数字(1、2、3、…)の組で表す。例えば、A1 という書き方をする。これは相対参照と呼ばれる。

「相対」は、セルをコピーした際に変化するという意味である。例えば、B2セル に =A1 という関数がある時、B2セル を D4セルに コピーすると、関数は =C3 になっている。

セルの位置が 列…B→D (2増加)、行…2→4 (2増加)、と変化したため、関数で参照するセルも 列…A→C、行…1→3 に書き換わった。

しかし、コピーしても書き換わって欲しくない場合もある。その時は絶対参照を使う。絶対参照は、行もしくは列の前に $ を付ける。例えば、 A$1、$A1、$A$1、という書き方をする。参照するセルは何れも A1 である。セルをコピーした場合に参照するセルがずれるか否かが相対参照と違うだけである。

こぶり会計帳簿では行をコピーして使うようにしているので、列は相対参照でも変化することは無い。従って、絶対参照が必要な場合でも $ を付けるのは数字の前だけである。その方が見易い。

エラー表示の方法

Googleスプレッドシートには表示形式に「条件付き書式」というものがあって、セルの値によって書体や色を変化させることが出来る。

この機能を利用して、エラー時にはセルの値を -1 とし、-1 の時には書体と背景の色を赤くしてエラーを表示することにした。尚、エラーで無い時も書体の色をそのセルの背景の色にして、数字は見えないようにしている。

仕訳シートのソート対策

仕訳データの行の上下にダミーの行を入れて、行を何処に追加しても正しく範囲が増やされるようにしたら、ソートして総勘定元帳を作ろうとした場合にダミーの行が入れ替わる問題が発生した。

これを防ぐため、「年-月-日」、「取引」、「科目ID] の欄にダミーのデータを入れ、ソートしても仕訳データの行の上下に留まるようにした。字の色を背景と同じにしてダミーのデータは見えないようにした。(セルを選択すると、数式バーでデータは確認出来る)

年-月-日取引科目ID
上のダミーのデータ1990-01-01-1-1
下のダミーのデータ2100-12-3110000

即ち、2101年以降はデータを書き換えないとこのシステムは使えない。

しかし、ダミーのデータを入れた影響で、今迄、「年-月-日」の欄での最も古い日付を期首、最も新しい日付を期末としていたので、諸表シート中に表示していた期首と期末の日付がおかしくなった。

これは、編集シートに日付を手入力すれば 問題にするような話では無いのだが、仕訳データに含まれている日付のデータを活用したかったので無理をしてみる事にした。

ダミーのデータはソートしても範囲の先頭と末尾にあるはずだから、範囲を1つだけずらす事で解決した。(諸表シートの I1セル と J1セル にその関数が入っているが、セルの背景と同色にしてある。)
  • =MAX(日付) → =MAX(offset(日付,-1,0))
  • =MIN(日付) → =MIN(offset(日付,1,0))

取引毎にチェックするしくみ

複式簿記では借方と貸方の金額が一致しているのが鉄則だ。それぞれの合計値は、
  • =SUM(借方)
  • =SUM(貸方)
という関数で求める事が出来て一致しているかどうかはチェック出来る。しかし、一致してない場合に、どの取引で合ってないのかが分からないと簡単には修正出来ない。そこで、一致してない取引をエラー表示できるようにした。3行目を例に取ると、
N列には =INT(A3)+B3/100 という関数が入っている。
これは、(日付を整数にした値) に (取引/100) という値を加えることで、取引毎に異なる値になる。(但し、取引は 1 から 99 迄) 一つの取引の借方と貸方の合計値は、
  • =SUMIF(N$2:N$98,N3,借方)
  • =SUMIF(N$2:N$98,N3,貸方)
という関数で求める事が出来る。(上下のダミー行が2行目と98行目の場合)

J3のセルには、これが一致してない場合に値を -1 にするような関数があり、セルを赤くしてエラーを表示する。一致する場合は、通常は値を 0 としているが、内容の欄に「前期繰越」(注)と書いた場合には値を「その行の科目のID (C列)の値」となるようにする。
(注) O1セルに書かれた文字列のこと。このセルの文字列と内容欄の文字列を比較している。

これは、諸表の社員資本等変動計算書のデータで当期首の値が必要なので、それを自動的に取得させる意図でそのようにしている。そういう訳で、J列には「期首残高」という名前の範囲がある。

相手科目を表示するしくみ

総勘定元帳では相手科目を表示する必要がある。相手の意味は、自己の取引の借方・貸方とは異なる方、という意味になる。表示するのは、その取引に関わる相手科目の数が、
  1. 1つの場合… その相手科目の勘定科目名
  2. 2つ以上の場合… 「諸口」
  3. 内容欄に「前期繰越」(注)と書いた場合…「前期繰越」
とする。(注)の内容は上記と同じ。

ところで、仕訳において、一つの行には「借方金額」か「貸方金額」のどちらか一つに金額を必ず書く。従って、もし「借方金額」の欄が空白なら、それは「貸方」であり、空白で無ければ「借方」だと決める。そして、3行目を例に取ると、
O列には =IF(D3="",N3,-N3) という関数が入っている。
これは、N列の値を 貸方の場合…そのまま、借方の場合…符号を反転 という作用がある。そして、P列では相手科目を以下の関数で表示する。
=IF(COUNTIF(F3,O$1),"前期繰越",
IF(COUNTIF(O$2:O$98,-O3)=1,
VLOOKUP(INDEX(科目,match(-O3,O$2:O$98,0)),科目設定,2,0),
"諸口"))
1行の関数だが 4つのパートに分けて4行で表示した。
  1. 内容欄が「前期繰越」なら「前期繰越」を表示する。
  2. O列でその行の持つ値と絶対値が同じで符号が反転している行(=相手科目)がただ一つだけある時は、3.をする。そうで無ければ、4.をする。
  3. match(-O3,O$2:O$98,0) で相手科目の行の位置を割り出し、INDEX(科目,行の位置) で相手科目の科目IDを割り出し、VLOOKUP(科目ID,科目設定,2,0)で相手科目の名前を表示する。
  4. 「諸口」を表示する。

仕訳帳・総勘定元帳の残高を表示するしくみ

総勘定元帳では、取引毎にその科目の残高を表示するのが一般的である。これは、取引が日付順に並んでいるとすれば、一番上の行からその取引の行までのその科目に関する借方・貸方の合計金額の差額となる。

これは、値がプラスになる方に表示するのが一般的な簿記の表示だが、こぶり会計帳簿では、負債・資本・収益に属する勘定科目では貸方、それ以外は借方で表示する。(値がマイナスになったとしても。) それには、以下の目的がある。
  • 本来プラスであるべき値がマイナスなら異常に気付き易いが、借方・貸方が入れ替わってもマイナスである事は分かりにくい。(少なくとも私には)
  • 一つの欄で残高の表示が可能。集計シートでは親子残高、項目残高については一つの欄で済ませている。
3行目を例に取ると、借方残高の欄には以下の関数が入っている。
=IF(COUNTIF(負資収,C3),"",
SUMIF(C$2:C3,C3,借方)-SUMIF(C$2:C3,C3,貸方))
1行の関数だが 2つのパートに分けて2行で表示した。
  1. 科目が「負債・資本・収益」に属するなら、空白を表示する。そうで無ければ、2.をする。
  2. 2行目(固定)から3行目(その行の位置)までのその科目の借方金額の合計値から貸方金額の合計値を引いて表示する。
関数中の「負資収」とは範囲の名前である。集計シートに於いて、科目を定義する行は上から順に、資産、| 負債、資本、収益 |、費用 と並んでいるから、一つの範囲で、「負債・資本・収益」は範囲内、「資産・費用」は範囲外と区別出来る。

集計シートのしくみ

任意の項目の下に行を挿入し、淡黄色のセルを持つ他の行をコピーし、白色のセルの情報を書き換える事で勘定科目を追加登録する。

勘定科目は必ず項目に属するので、項目の欄には項目の科目IDを入力する。但し、子科目は親科目の項目を受け継ぐので、親科目の欄に親科目の科目IDを入力する。(項目の欄は空白にする。)

集計値は、借方合計、貸方合計、借方残高、貸方残高、親子残高、項目残高の6項目ある。仕訳シートの範囲名を使う事で仕訳シートのデータを扱う。3行目を例にとると、
  • 借方合計は、=SUMIF(科目,A3,借方)
  • 貸方合計は、=SUMIF(科目,A3,貸方)
  • 借方残高は、=IF(COUNTIF(負資収,A3),"",H3-I3)
  • 貸方残高は、=IF(COUNTIF(負資収,A3),I3-H3,"")
COUNTIF(負資収,A3) で3行目の勘定科目が「負債・資本・収益」の部に属するか判定し、否なら 借方合計-貸方合計 を借方残高に、是なら 貸方合計-借方合計 を貸方残高に表示する。
  • 親子残高の関数は、
=IF(ISBLANK(C3),IF(ISBLANK(D3),
IF(COUNTIF(負資収,A3),K3,J3),
IF(COUNTIF(負資収,A3),
K3+SUMIF(C$2:C$99,A3,K$2:K$99),
J3+SUMIF(C$2:C$99,A3,J$2:J$99))),"")
である。まず、親科目の欄が空白かチェックする。親科目の欄が空白で無いなら子科目なので、親子残高の表示は空白にする。この時、項目の欄に何か入っていても動作に影響しない。

親科目の欄が空白で項目の欄も空白なら項目なので、その残高を表示する。その残高は借方残高か貸方残高のどちらかなので COUNTIF(負資収,A3) で判定する。

親科目の欄が空白で項目の欄が空白で無いなら親科目なので、親子残高を計算する。親子残高は、3行目の残高と子科目の残高の合計になる。残高が借方残高にあるか貸方残高にあるかは COUNTIF(負資収,A3) で判定する。
  • 項目残高の関数は、
=IF(AND(ISBLANK(C3),ISBLANK(D3)),
L3+SUMIF(D$2:D$99,A3,L$2:L$99),"")
である。親科目の欄と項目の欄が両方とも空白なら項目である。項目自体の残高も、項目に属する科目の残高も、全て親子残高にあるから、親子残高の欄の値だけを使って合計する。項目では無い場合は、空白を表示する。

消費税の欄

「消費税」の欄は E列、「簡易課税制度の区分」の行は103行にある。例として H列にある関数を示すと、
=SUMIF($E$2:$E$99,H103,$I$2:$I$99)-SUMIF($E$2:$E$99,H103,$H$2:$H$99)
SUMIF関数を使って、H103セルの文字とE列の文字がマッチしたら マッチした勘定科目の貸方合計を合計し、同様に借方合計も合計し、その差額を表示する。子科目や借方の科目を含めてどんな勘定科目にも使えるようにするために、この方法にした。

集計シートのエラー

エラーがあれば、G列のセルを赤くして表示する。

3行目には、=IF(診断,-1,IF(COUNTIF(A$2:A$99,A3)=1,0,-1))

という関数が入っていて、科目IDに同じ値が複数あれば、このセルの値を -1 にする。

この他には、資産の部合計 と 費用の部合計 の行では 各部の借方残高と項目残高が一致しているかチェック、負債資本の部合計 と 収益の部合計 の行では各部の貸方残高と項目残高が一致しているかチェックしている。

更に、試算表合計 の行では 仕訳シートと集計シートで借方合計同士の一致、借方合計同士の一致をチェックしている。もし仕訳シートで未登録の科目IDが使われていればエラーを起こす。

諸表シートのしくみ

損益計算書、貸借対照表、社員資本等変動計算書、の性格が異なる3つの計算書類(と個別注記表)を一つのシートに纏めて､保存すべきプリントの枚数を減らした。

同一のシート内で淡黄色のセルのある行は任意の行をコピーして使えるという規則は守りたかったので、D欄が空白の場合は損益計算書と貸借対照表、D欄に何か入っていると社員資本等変動計算書として動作するようにした。

損益計算書と貸借対照表は、集計シートの項目残高の値を、項目が「負債・資本・収益」に属するなら貸方残高に、そうで無ければ借方残高に表示する。但し、損益計算書では貸方のタイトルは収益、借方のタイトルは費用になっている。

セルにある関数の中で貸借対照表(と損益計算書)に関する部分は、26行目を例にとると、

借方残高の列のセルでは、
IF(COUNTIF(負資収,C26),"",VLOOKUP(C26,科目設定,13,0))
貸方残高の列のセルでは、
IF(COUNTIF(負資収,C26),VLOOKUP(C26,科目設定,13,0),"")
VLOOKUP の中で 13 という数字は、集計シートの項目残高の欄を示し、0 という数字は検索が完全一致である事を示す。

会社計算規則の規定通りに、売上総利益、営業利益、経常利益、税引前当期純利益、当期純利益、のタイトルは値がマイナスになる場合には「利益」を「損失」に変え、値もマイナス符号は付けずに表示する。そのタイトル行の収益の欄に於いて元の値を計算しているが、値が表示されないように文字の色はセルの背景の色に合わせている。

 社員資本等変動計算書については、当期首と当期末の残高を知る必要がある。全て資本の項目なので、貸方を参照する。当期末なら、集計シートの貸方残高の欄、当期首なら、仕訳シートの貸方の欄からデータを得る。47行目を例にとると、

借方残高の列(タイトルは当期首残高になる)のセルでは、
=IF(COUNTIF(D47,D$1),
INDEX(貸方,MATCH(C47,期首残高,0)),貸借対照表に関する部分)
ここで、D1セルには「当期首」という文字が入っている。D47に「当期首」という文字を入れると、INDEX関数の部分が実行される。

期首残高という範囲は、仕訳シートの内容欄に「前期繰越」の文字がある科目IDの欄になっている。これとC47にある科目IDに一致する行の位置をMATCH関数で得て、INDEX関数でその行の貸方金額を得る。

D47に「当期首」以外の文字(例えば「当期末」)を入れると、貸借対照表に関する部分が実行されるが、資本の項目なので、借方残高(=当期首残高)は空白になる。

貸方残高の列(タイトルは当期末残高になる)のセルでは、
=IF(COUNTIF(D47,D$1),"",IF(ISBLANK(D47),
貸借対照表に関する部分,VLOOKUP(C47,科目設定,11,0)))
D47に「当期首」という文字を入れると、空白が表示される。

D47に「当期首」以外の文字(例えば「当期末」)を入れると、ISBLANK関数は偽だから、VLOOKUP関数の部分が実行される。その中の 11 は集計シートの貸方残高の欄を示すので、当期末残高が表示される。

在庫シートのしくみ

出庫数と入庫数の欄のデータは仕訳シートの出庫数と入庫数の仕訳の値を SUMIF関数を使って積算したものが表示される。

G列はエラー表示の欄で、品番の登録で重複があった場合にセルを赤くする。しくみは、集計シートのエラー表示と同じである。
登録: 投稿 (Atom)