【家計管理簿記】やり方（エクセル編）手順５_家計管理グラフを作成する

っと、いう記事のエクセル編です。

では本題です。

家計管理グラフ、こんなものを作成します。

前回、作成した２月までの精算表からのつづきを前提に進めてまいります。

行列枠の設定

新しいシートを作成し、「FS_１月」っという、名前を付けます。
ちなみにFSとは「Financial Statement」、財務諸表の意味です。

行列幅は基本的に任意ですが、A4印刷に程よいものとして参考にしてください。

こんな感じで、タイトルや表枠を作成します↓

４つのグラフを作成するので、４ブロックの区切りの意味でB2～E25の外枠を青太字罫線枠を作成、G2～J25も同じようにします。

下段の青太字罫線外枠の範囲は、B27～E49、G27～J49。

セルの設定

まずC１を右クリック、セル書式設定で数値⇒日付⇒YYYY/MM/DDの西暦に設定、このセルはこのシートで唯一の入力セルなので、青太字を設定します。

次に以下の各セルに関数をコピペします。

D2⇒　　=TEXT(EDATE(C1,-1),”yy/mm”)
I2⇒　　 =TEXT(EDATE(C1,0),”yy/mm”)
D27⇒　 =TEXT(EDATE(C1,0),”yy/mm”)
I27⇒ 　 =TEXT(EDATE(C1,0),”yy/mm”)

これらはC1の年月日から年/月を返す、っという意味です。

表枠の左項目には、精算表で設定した中分類項目を入力します。
（本当はプルダウン入力がおススメですが、間違いなく入力すれば手打ちでも構いません）

こんな感じですね。

データの抽出

次は、精算表からデータを引いてきます。
C3にカーソルを合わせ以下をコピペ

=IF(INDEX(清算表!$D$3:$R$77,MATCH($B3,清算表!$D$3:$D$77,0),MATCH($D$2,清算表!$D$3:$R$3,0))<=0,0,ABS(INDEX(清算表!$D$3:$R$77,MATCH($B3,清算表!$D$3:$D$77,0),MATCH($D$2,清算表!$D$3:$R$3,0))))

分解して意訳します。

=IF(INDEX(清算表!$D$3:$R$77,MATCH($B3,清算表!$D$3:$D$77,0),MATCH($D$2,清算表!$D$3:$R$3,0))<=0,0,ABS(INDEX(清算表!$D$3:$R$77,MATCH($B3,清算表!$D$3:$D$77,0),MATCH($D$2,清算表!$D$3:$R$3,0))))

青いアンダーラインは精算表から引いてきた数値（現金口座）です。
なのでこの関数の意味は
＝もし（IF）（数値が＜＝0ならば,0と表示し,それ以外は数値を絶対値（ABS）で表示する
っと、いう意味、
さらに青いアンダーラインの数値は、
選択した表の行列索引（INDEX）（抽出したいデータの選択,そのデータの行番号,列番号）、
行列番号の指定はMATCH関数を入れ子します。

深堀してみましょう↓

これでC3に現金口座の金額を引いてくることができました。
これを基準にコピペ及び微修正をかけていきます。

C3をD3にコピペすると同じ数値を引いてきます。
貸方には表示する必要がありませんので、その修正を行います
<　⇒　>　にするだけです。

=IF(INDEX(清算表!$D$3:$R$77,MATCH($B3,清算表!$D$3:$D$77,0),MATCH($D$2,清算表!$D$3:$R$3,0))>=0,0,ABS(INDEX(清算表!$D$3:$R$77,MATCH($B3,清算表!$D$3:$D$77,0),MATCH($D$2,清算表!$D$3:$R$3,0))))

D3に0が表示されれば正解です。

次にC3～D3を選択し、純資産の１０行目までコピペすると、ダッダーっと引いてきます。

次はC3をH3にコピーして、↓の箇所の BをG に、DをI に、修正します。
（少しでも入力を間違うとエラーになりますので慎重に・・・）

=IF(INDEX(清算表!$D$3:$R$77,MATCH($G3,清算表!$D$3:$D$77,0),MATCH($I$2,清算表!$D$3:$R$3,0))<=0,0,ABS(INDEX(清算表!$D$3:$R$77,MATCH($G3,清算表!$D$3:$D$77,0),MATCH($I$2,清算表!$D$3:$R$3,0))))

そしてH3をI3にコピーぺして、I3の　<　を　>　に、

=IF(INDEX(清算表!$D$3:$R$77,MATCH($G3,清算表!$D$3:$D$77,0),MATCH($I$2,清算表!$D$3:$R$3,0))>=0,0,ABS(INDEX(清算表!$D$3:$R$77,MATCH($G3,清算表!$D$3:$D$77,0),MATCH($I$2,清算表!$D$3:$R$3,0))))

そしてH3～I3を選択し、純資産の１０行目までコピペして、ダー

次はD3をコピーしてD28に張り付け、↓のように修正

=IF(INDEX(清算表!$D$3:$R$77,MATCH($B28,清算表!$D$3:$D$77,0),MATCH($D$27,清算表!$D$3:$R$3,0))>=0,0,ABS(INDEX(清算表!$D$3:$R$77,MATCH($B28,清算表!$D$3:$D$77,0),MATCH($D$27,清算表!$D$3:$R$3,0))))

そして、D28をC28にコピーして　>　を　<　に

=IF(INDEX(清算表!$D$3:$R$77,MATCH($B28,清算表!$D$3:$D$77,0),MATCH($D$27,清算表!$D$3:$R$3,0))<=0,0,ABS(INDEX(清算表!$D$3:$R$77,MATCH($B28,清算表!$D$3:$D$77,0),MATCH($D$27,清算表!$D$3:$R$3,0))))

そしてC28～D28を選択し、損益の34行目までコピペして、ダー

次はH28に↓をコピペ

=IF(INDEX(清算表!$D$3:$R$77,MATCH($G28,清算表!$B$3:$B$77,0),MATCH($I$27,清算表!$D$3:$R$3,0))<=0,0,ABS(INDEX(清算表!$D$3:$R$77,MATCH($G28,清算表!$B$3:$B$77,0),MATCH($I$27,清算表!$D$3:$R$3,0))))

H28をI28にコピペして、　<　を　>　に
そしてH28～I28を選択し、30行目までコピペして、ダー
（ここでは投資評価益は手入力にします）

最後に各表の縦計をSUM関数で小計、これで表ができました。

（ここでもうひと手間かけます。）
どうも、0の表記が気になって0を空欄にしたいのです。
でも、前のIF構文で、
＝もし（IF）（数値が＜（又は＞）＝0ならば,0と表示し,それ以外は・・・
のところを、　,””,　にして空欄にしてしまうと、次のグラフを作る工程で不都合が生じてしまいます。
なので、「計算結果の０をセルの中で認識しつつ表示しない」　っという細工をします。

A1セルの左上をクリックしてセル全体を選択し、
ホームタブの条件付き書式の設定⇒新しいルール⇒指定の値を含むセルだけを書式設定⇒セルの値⇒次のセルに等しい⇒0を入力⇒そして書式をクリックして、書式設定に切り替え

数値タグのカテゴリー⇒ユーザー設定⇒種類に　;;;　と入力（セミコロン３つ）⇒OK

;;;　は、正の値も、負の値も、文字列も、表示しない、っという設定です。
これでスッキリしました。

グラフの作成

何事も準備というのは大変です、やっと今から本題のグラフを作成します。
グラフの種類は　「積み上げ棒グラフ」　です。

B3～D10の小計を除く表を選択⇒挿入⇒グラフ⇒縦棒⇒積み上げ縦棒
するとこんな感じのグラフができます。

グラフの右上辺りのグラフエリアをクリックして⇒グラフツールタブ⇒行/列の切り替え
それっぽくなってきました。

どこでもいいのでグラフの色塗りを選択し右クリック⇒データ系列の書式設定
系列オプションの系列タグ⇒要素の間隔⇒これを0％に設定
そして不要なものを消してしまいましょう。
グラフタイトル、凡例、横軸を消します。

こうしてグラフをくっつけます。
次にグラフ内のグラフエリアを選択し⇒グラフツールタブ⇒グラフ要素の追加⇒データラベル⇒中央
すると金額が表示されます。

これから少し面倒ですが各ラベルの金額を選択して右クリック⇒データラベルの書式設定⇒ラベルオプション⇒ラベル⇒系列名に☑
すると反対側の0を認識して0の項目（系列名）も表記されてしまいますが、選択クリックの回数を駆使して不要なものを消してしまいます。（数回クリックして不要のものだけ消すことができます）
これを繰り返して勘定科目と金額を表記します。

次に順番が気に入らないので、お好みに合わせて入れ替えます。
グラフエリアを選択し、グラフツール⇒データ選択
入れ替えたい勘定科目を選択して↑↓ボタンで入れ替えます。
やはり、架空資産と固定負債は一番下に、純資産は一番上に持ってきたいです。

次に色合い、これもお好みで、
ラベルにかからない色塗りのとことを選択して塗りつぶしの色を変更できます。
最後に収まりよくサイズを調整、これで先月の貸借対照表グラフの完成です。

完成したこのグラフを一応テンプレートに保存しておきます。
グラフエリアを選択し右クリック⇒テンプレートの保存⇒名前を付けて保存します。
呼び出すときにはデータを選択し、挿入⇒グラフ⇒テンプレート⇒この中に名前を付けたものがあります。
ですが、行列入れ替えや、色塗り列順など、完全にグラフ書式がコピーされたものではありません。
多少手間ではありますが、個別に同じ設定をしていきます。
こうして、先月、当月の貸借対照表グラフの完成。

同じやり方で損益計算書、そして投資評価益を含めたおまけの貸借対照表グラフを作成します。

FS（財務諸表）シートの完成

これで「FS_１月」シートが完成できました。

今後のやり方としては、シートコピーして名前を「FS_２月」にして、月末の日にちを入力すれば一瞬で完成できます。

さて、５回にわたって解説してきました、家計管理簿記のやり方エクセル編はこれで完結です。

家計管理簿記の最終目的は、「自由になるお金」を把握することにあります。

現状の生活環境に合わせて、純資産を把握し、家計体力の推移をみながらその純度を上げていき、
「自由になるお金」（純度を上げた純資産）が今いくらあるのかを把握する。

こうすることでお金に関する不安がすべてが解消され、自身の立ち位置がわかって、今後どうあるべきかの行動起点につなげていくのです。

では、次回はより具体的なやり方の解説などしてみようかなあ・・・。

ではまた！