【Excelで家計簿を作る】 #4 Excelのピボットテーブルをグラフ化

夜に湯船に浸かると気持ちいい。

秋のライフハックです。

SQL serverからデータ連携には成功したものの

グラフをどうやって作るかを悩んでいます。

というわけで今更要件定義じみたものをしたいと思います。

実装したいグラフ

・収入と支出の対比表

　2本の色分けした折れ線グラフで、

　時点ごとにどっちの金額が多いかを見るもの

これは必ず実装するとして、実装するにしても

日付・金額の2列だけのデータソース（View）を用意することで、実装できてしまいそうです。

ただ、収入はポイント等の非キャッシュも含めるかどうかを考える必要があります。

・カテゴリー、コンテンツ単位での収入・支出推移

　これはフィルタか、Excelのセル値に反応して

　動的にグラフに表示されるデータを変更する仕組みにしたいなーと思います。

　ただ、そうなるとSQL server側ではなくてExcel側で実装する必要があります。

　んで、上のやつとわざわざ実装方法を分ける必要があるのかなーという感じ。

で、2番目のほうが家計簿としては気になるので、前回Viewでうんたらかんたら言ってましたがExcel側の機能を触っていきたいと思います。

（1番目の方はSQLで日付を好きな単位で絞って、負の数・正の数別々にSUMすれば簡単に出せる気もするので後回しでもいいかな・・・）

ということで今回からひたすらExcel触っていきます。

で、まずはデータソースをそのままピボットテーブルにしてみます。

このデータソースをピボットテーブルにそのまま実装するとどうなるか。

*項目名日本語にしたほうがよかった気がする

フィルターに計算対象：１

そして単純に日付と金額だけを出したような状態です。

計算対象というのは0だと家計簿アプリ上でも収支の計算から除外するということです。

具体的には振替項目等ですね。例えばAmazon、クレカの明細からは「Amazon」という引き落としにしかなりませんが、Amazonのアカウントを連携することでAmazonの何を買って金額がいくらかを特定できるようになります。

ただ、そのままではクレカの明細とAmazonの明細で2重計上になってしまうので、

クレカ側にAmazonで同一明細があるということで振替るという扱いになります。

アプリの画面：

上がクレカの明細で下がAmazonの明細です。

左のチェックマークがデータソースのCalculation_targetになります。

では、計算対象を1に絞って正しい結果になっているかの確認になります。

アプリの画面：

はい、上の2020/09が同数値なのであっていますね。

アプリ画面側のエビデンスに日付が表示されていないのでエビデンスとしてはNGになりそうですがテストではないので・・・

このピボットテーブルがアプリの計算と同じ計算で収支を計算できることは確認できました。なので、このピボットテーブルをそのままグラフにしてしまえば収支の推移を出すことができます。

*ピボットテーブルのセルをアクティブにしておいて、挿入 > グラフを選んで1クリック

グラフ拡大図：

Excelってグラフに使う要素だけ絞ってグラフ起こすと思い通りのグラフになるんですね。

Excelのグラフ機能殆ど使ったことないので勉強になりますん。

いやや、ホントはデータソース直でグラフ用意してそのまま加工したいんや。

変にBIツールの使用経験があるせいで拗らせてしまっている。

で、ピボットテーブルからグラフ作るなんて初めてなんですが、

これはびっくり、フィルタ機能がグラフ上に実装されるんですね。

あ、しかもめっちゃグラフからいじれるし、

てっきり用途ごとにピボットテーブル用意しないといけないのかと思ってたんですが、どうもデータソースとそれを元にしたピボットテーブルが1セットあればあとは好きなグラフを量産していけるみたいですね。

適当に遊んで生活費の推移出してみました。

電気代がバグってるし水道代が表示されない。

これは慣れないと大変そうだ。

・・・

第4回にしてやりたいことが概ね実装できることが判明してしまった。

後は色んなグラフ作り込むだけになっちゃうな。

SQL severのテーブルにINSERTしてExcelで反映されるかの確認ぐらいはこのブログでも確認しておこうかなと思います。

うーんもっとSQL server使いたいですが何か無いかな。

取り敢えずExcelで家計簿作るのは特に困らず簡単でした。

家計簿っていうのかなこの作業は・・・

明細の帳簿を家計簿というならそれは全部アプリのcsvだしモヤモヤしますが...

でもSQL serverとExcelのいい勉強にはなりましたね。

DBが違うとここまでSQLの勝手が違うのはちょっと驚きでした。

Oracleだけ触ってるのはよくないですね。

adieu.

本日のBGM：バンドリ全般