バージョン
- 最初のバージョンは家計簿として使えるものを目指す
- 次のバージョンは個人事業主が青色申告に使える決算書を生成することを目指す
全体の構成
- 科目定義 仕訳科目を定義するシート(未完成)
- 仕訳帳 仕訳を入力するシート(未完成)
- 計算書 仕訳項目の定義及び1-12月集計を一覧するシート(未完成)
- 仕訳元帳 仕訳科目をキーにして仕訳を抽出するシート(未完成)
以上が複式簿記の最低限の機能を持つセットとなる。
- 勘定元帳 勘定科目をキーにして仕訳を抽出するシート(未作成)
- 決算書1 青色申告の「xx年度分所得税青色申告決算書(一般用)」を表示するシート(未作成)
- 決算書2 青色申告の、月別売上金及び仕入金額を表示するシート(未作成)
- 貸借対照表(未作成)
科目定義シートの作成
シートに「科目定義」と名前を付け、最初の2行を以下のようなデザインにする。
- A列 仕訳科目
- B列 区分 勘定科目のグループ(収益、費用、資産、負債、資本)の事。それらをまとめていう言葉が見当たらなかったので、ここでは区分という言葉を使うことにした。
- C列 備考 自由なメモ欄
このシートでは、仕訳科目を第3行以下で定義します。
上記ではとりあえず家計簿で使いそうな仕訳科目の例を記載しています。上記の仕訳科目の例は、以後の使い方の説明の中で使います。
ですが実際には各自自分で使うものを定義してください。
上記ではとりあえず家計簿で使いそうな仕訳科目の例を記載しています。上記の仕訳科目の例は、以後の使い方の説明の中で使います。
ですが実際には各自自分で使うものを定義してください。
仕訳帳シートの作成
シートを追加し「仕訳帳」と名前を付け、以下のようなデザインにする。
※記帳するときに手入力するセルは、うす緑に着色している。
※記帳するときに手入力するセルは、うす緑に着色している。
- A列 取引番号 あらかじめ連番を入れておく
- B列 日付 セルB1に集計開始日を置くことにする。ただし今のところ1月1日にしか対応していない。つまり4月開始のつもりで4/1を入れても12月までしか集計できない。改良すべき点。
- C列 借方仕訳科目 計算書の仕訳項目から選択入力できるようにする。文字入力すれば補完されるように、以下で作っていく。
- D列 金額
- E列 貸方仕訳科目 計算書の仕訳項目から選択入力できるようにする。文字入力すれば補完されるように、以下で作っていく。
- F列 摘要 任意のコメントを記帳する
- G列 クレジットカードの引落月を記帳する列。クレジットカード会社ごとに引落月が違うため自動化できないので手入力するための列。
- H列 作業用の列。普段は非表示にしておく列。月で集計をする計算式が簡単になるように、B列の月の値だけを表示する。このとき、セルB1に指定した集計開始基準日より以前の日付の場合は-1と表示するような式にして、前年度からの繰越仕訳を取り扱えるようにする。セルH3に次の式を入れる。
=ARRAYFORMULA(if(isblank(B3:B),"", if(B3:B<$B$1,-1, month(B3:B))))
- I列 作業用の列。普段は非表示にしておく列。仕訳帳で使われている仕訳科目を抽出する。元帳シートから参照する。セルI3に次の式を入れる。
=uniqe({ $C$3:$C ; $E$3:$E })
uniqe関数の範囲を指定する引数について補足、{}で囲んで;で列を区切ると、その全体が一つの列として扱われる。
仕訳科目の入力規則を設定する
以下のように、Googleスプレッドシートの「入力規則」をセルに設定すると、入力の手間が軽減される。
借方仕訳科目の入力規則の設定
C列の借方仕訳科目の入力値について、科目定義シートで定義されている仕訳科目から選択できるようにする。以下その手順。
- 借方の入力セルである、セルC3を選択、その状態で、
- メニューから データ＞データの入力規則 を選択すると、画面右側に データの入力規則 パネルが開く
- 「＋ルールを追加」ボタンを押す
- 範囲の指定を「'仕訳帳'!C3:C」とする
- 条件から「プルダウン(範囲内)」を選択する。そうすると、その下に範囲選択ボックスが現れる。
- 範囲選択ボックス内の 田 マークを押すと、データ範囲の選択 ダイアログボックスが現れる。その状態で、
- 科目定義シート に切り替え、セルA3から下方向へ仕訳科目を入れてある範囲全部を含むだけセルを選択する。余分に未入力セルを選択しておいてもかまわない。もしくは直接「'科目定義'!$A$3:$A」とタイプしてもかまわない。念のため絶対参照にしておく。
- 「OK」ボタンで範囲指定ダイアログを閉じ、値の範囲が指定できた。
- 「完了」ボタンで一つの入力規則の定義を終える。定義したルールが左の入力規則パネルから消えたように見えるが、アクティブなシートを 仕訳帳 に切り替えたらまた現れる。開いているシートの入力規則だけが表示されるため。
貸方仕訳科目の入力規則の設定
貸方の入力セルである E3:E にも、上記と同じ手順で入力規則を設定する。
計算書シートの作成
新たにシートを追加し、「計算書」と名前を付け、以下のようなデザインにする。
- A列 仕訳科目
- B列 区分 収益、費用、資産、負債、資本のいずれかが入る列。
- C列 前年繰入 資産や負債について、前年度からの繰入額。
- D列 年計 1-12月の合計。
- E-P列 1-12月に対応する月毎集計列。行2のEからPまでそれぞれに1から12の数値を入れる。次にその書式を「表示形式＞数字＞カスタム数値形式」で「0"月"」とする。見た目は「n月」であるが、式から参照すると数値「n」となる。
計算書シートの計算式の設定
セルA3に以下の計算式を入れる。
={'科目定義'!$A$3:$B}
これで、仕訳科目と区分について、科目定義からそのまま持ってこれる。
C列は前年度からの繰入額の欄で、仕訳帳から、集計基準日以前の仕訳を集計することで実現する。セルC3 に以下の式を入れる。
=if(isblank($A3),"",
(
sumifs('仕訳帳'!$D$3:$D, '仕訳帳'!$C$3:$C,$A3, '仕訳帳'!$H$3:$H,-1)
-sumifs('仕訳帳'!$D$3:$D, '仕訳帳'!$E$3:$E,$A3, '仕訳帳'!$H$3:$H,-1)
)
* if( or($B3="資産",$B3="費用",$B3="-資本"), 1, -1)
)
D列の年計の式: セルD3 に以下の式を入れる。
普通は1月-12月の計だけだが、資産と資本と負債に関しては前年度からの繰入(C列)も加算するようにする。
普通は1月-12月の計だけだが、資産と資本と負債に関しては前年度からの繰入(C列)も加算するようにする。
=if(isblank($A3),"", sum($E3:$P3)+if(or($B3="資産", $B3="資本", $B3="-資本", $B3="負債"), $C3,0))
E-P列の各月の集計列の式: セルE3 に以下の式を入れる。
A列の仕訳科目に対応する金額を、行2の月に関して集計する。
A列の仕訳科目に対応する金額を、行2の月に関して集計する。
=if(isblank($A3),"",
(
sumifs('仕訳帳'!$D$3:$D, '仕訳帳'!$C$3:$C,$A3, '仕訳帳'!$H$3:$H,E$2)
-sumifs('仕訳帳'!$D$3:$D, '仕訳帳'!$E$3:$E,$A3, '仕訳帳'!$H$3:$H,E$2)
)
* if( or($B3="資産",$B3="費用",$B3="-資本"), 1, -1)
)
月計の式の展開手順:
- セルE3を一度クリックし、シフトキーを押しながら、セルP3をクリックする。シフトキーを離す。
- Ctrlキーを押しながらRキーを押す。
以上で、うまく参照を整えながら式が右方向へ展開される。
第3行の式の、下行への展開手順:
- セルC3を一度クリックし、次に、シフトキーを押しながらセルP30(※P列で、科目定義されている行すべてを含むようなセル)をクリックする。シフトキーを離す。
- Ctrlキーを押しながらDキーを押す。
以上で、うまく参照を整えながら、式が下方向へ展開される。
仕訳元帳シートの作成
元帳とは、仕訳帳から特定の勘定科目の取引だけを抽出して一覧できるシートです。ここでは、勘定科目以外に、さらには摘要欄や引落月や使用月で絞り込めるようなシートを作成します。
新たにシートを追加し、「仕訳元帳」と名前を付け、以下のようなデザインにする。
- セルC1,D1,E1を選択し、結合する。集計したい勘定科目を入れるセル。セルC1として参照できる。センタリングしておくとよい。
- セルC1に入力規則を設定する。条件は「プルダウン(範囲内)」で、範囲は「='仕訳帳'!$I$3:I$」とする。これで、仕訳帳に入力済みの勘定科目を選択可能になる。
- セルA3に次の式を入れる。
=ifna(
filter( '仕訳帳'!$A$3:$H,
('仕訳帳'!$B$3:$B<>"")
* ( ('仕訳帳'!$C$3:$C=$C$1) + ('仕訳帳'!$E$3:$E=$C$1) )
* ( isbalnk($F$1) + isnumber(search($F$1, '仕訳帳'!$F$3:$F)) )
* ( isblank($G$1) + ('仕訳帳'!$G$3:$G = $G$1) )
* ( isblank($H$1) + ('仕訳帳'!$H$3:$H = $H$1) )
)
,"記帳無し")
- セルA3のfilter関数の条件節は、*をand、+をorとするような計算式となっている。
仕訳元帳の使い方
- セルC1はドロップダウンリストになっていて、リストから選んでも、文字列入力してタブキーで補完してもよい。それで指定の勘定科目の取引が一覧される。
- セルF1は普段は空欄で、摘要で絞り込みたいときキーワードとなる文字列を入れる。
- セルG1はクレジットカード引落月で絞り込みたいときにその月を入れる。
- セルH1は取引月で絞り込みたいときにその月を入れる。
- 条件に合う取引が無い場合は「記帳無し」と返る。
複式簿記の原理
勘定科目のグループ(区分)
勘定科目は、次の五つのグループのいずれかに属する
- 資産 正の財産、現金、預金など
- 負債 返済や支払い義務のある負の財産、クレジットカード(での買い物)、借金(借入金)など
- 資本 ここでは純資産と同義に扱っていて、会社の場合は資産から負債を差し引いたものに相当するが、個人では年初の元入金や年末の繰越金に相当する
- 収益 収入の事で、負債とは異なるもの、返さなくていいもの
- 費用 支出の事で、負債の返済ではないもの
これらをまとめて呼ぶ言葉があるのか探してみたが、「貸借対照表の勘定科目」(資産・負債・純資産)と「損益計算書の勘定科目」(収益・費用)という感じの呼び方しか見つからない。ここでは資産、負債、資本、収益、費用をまとめて「勘定科目の区分」または単に「区分」と呼ぶことにする。まだ、複式簿記から青色申告のための財務諸表を作るには、もう少し追加の勘定科目があったほうが計算式が簡単になる。
仕訳帳と勘定科目の区分
勘定科目は、資産、費用、負債、収益、資本の五つの区分に分けることができる。
例えば、現金や預金は資産である。日常の様々な出費はたいていが費用である。クレジットカードでの買い物は、負債を背負って費用に充てている。銀行からクレジットカードの負債が引き落とされたとき、資産から負債を返済したということになる。給料が銀行に振り込まれたら、収益が資産になったということである。
例えば、現金や預金は資産である。日常の様々な出費はたいていが費用である。クレジットカードでの買い物は、負債を背負って費用に充てている。銀行からクレジットカードの負債が引き落とされたとき、資産から負債を返済したということになる。給料が銀行に振り込まれたら、収益が資産になったということである。
仕訳帳の借方貸方のそれぞれに勘定科目の区分を振り分けた時、その勘定科目の区分の値が増えるのか減るのかという点に注目すると、以下のように表現できる。
借方 貸方 ----- ----- 資産+ 資産- 費用+ 費用- 負債- 負債+ 収益- 収益+ 資本- 資本+
上の表を資産について説明すると、資産に属するある勘定科目が借方にある仕訳の場合、その勘定科目が「+」つまり増加するよう計算するということである。
計算式に直すと
計算式に直すと
その勘定科目の総計 = ( (その勘定科目が借方に現れたときの金額の総計) - (その勘定科目が貸方に現れた時の金額の総計) ) * (if その勘定科目が、資産、費用のいずれか then 1 else -1)
となる。この式を基本として、計算書シートや元帳シートの計算式は作られている。
ちょっとメモ
仕訳元帳のセルA3の計算式について
=ifna(
filter( '仕訳帳'!$A$3:$H,
('仕訳帳'!$B$3:$B<>"")
* ( ('仕訳帳'!$C$3:$C=$C$1) + ('仕訳帳'!$E$3:$E=$C$1) )
* ( isbalnk($F$1) + isnumber(search($F$1, '仕訳帳'!$F$3:$F)) )
* ( isblank($G$1) + ('仕訳帳'!$G$3:$G = $G$1) )
* ( isblank($H$1) + ('仕訳帳'!$H$3:$H = $H$1) )
)
,"記帳無し")
の式を、filterの条件節を分けて(コンマで区切る)、if関数で書き換えてみた次の式は、機能しなかった。
=ifna(
filter('仕訳帳'!$A$3:$H,
'仕訳帳'!$B$3:$B<>"",
or('仕訳帳'!$C$3:$C=$C$1, '仕訳帳'!$E$3:$E=$C$1),
if( isblank($F$1), true, isnumber(search($F$1, '仕訳帳'!$F$3:$F)) ),
if( isblank($G$1), true, '仕訳帳'!$G$3:$G=$G$1),
if( isblank($H$1), true, '仕訳帳'!$H$3:$H=$H$1)
)
,"記帳無し")
理由は、filterの条件節は、各行の真偽を指定してほしいのだが、or関数やif関数の戻り値が配列ではなく単純な真偽値になることがあるため。if(条件,true)としてしまうとtrueは配列ではないので各行の条件ではなくなる。or関数も戻り値は単純なtrue/falseとなり、各行の条件という形では返さない。
次の形なら大丈夫。
次の形なら大丈夫。
=ifna(
filter('仕訳帳'!$A$3:$H,
'仕訳帳'!$B$3:$B<>"",
('仕訳帳'!$C$3:$C=$C$1) + ('仕訳帳'!$E$3:$E=$C$1),
isblank($F$1) + isnumber(search($F$1, '仕訳帳'!$F$3:$F)),
isblank($G$1) + ('仕訳帳'!$G$3:$G=$G$1),
isblank($H$1) + ('仕訳帳'!$H$3:$H=$H$1)
)
,"記帳無し")
ところで
isblank($G$1)
という式は単純な真偽値のはずであるが、
isblank($G$1) + ('仕訳帳'!$G$3:$G=$G$1)
と式にすると左辺は配列になるので、おそらく結果がtrueの配列に変換されているのではないかと思って、
=isblank($G$1) + ('仕訳帳'!$G$3:$G=$G$1)
という式を別のセルに入れて配列展開されているかどうか調べたが、式を入れたセルに0/1が表示されるだけ。配列展開されていてもそれが見えないだけか、他に勘違いがあるのかがわからない。
あと、(TRUE)=1、(FALSE)=0と、真偽値に()をつけると計算されて数値に変わることに気づいた。
あと、(TRUE)=1、(FALSE)=0と、真偽値に()をつけると計算されて数値に変わることに気づいた。
