パワークエリで現金預金推移グラフを作成する方法

会社の資金状況を把握するうえで、現金預金の推移を見ることはとても重要です。
会計ソフトでも確認はできますが、自分用につくり変えるのは難しいでしょう。

もちろん、Excelを活用すれば、
オリジナルのグラフを作成できます。
ですが、毎回作成するのは面倒ですよね。

そこで便利なのがパワークエリです。
総勘定元帳のデータを読み込んで、データ整形の手順をつくっておけば、
次回からはデータを更新するだけで現金預金の推移グラフを作ることができます。

今回は「パワークエリで現金預金推移グラフを作成する方法」というお話です。

※数字はすべてダミーデータです。

なぜ現金預金の推移を見るのか！？

おカネは会社の生命線。
だからこそ、おカネの動きには常に目を配りたいところ。

ただし、残高だけを見ても、

・資金が増えているのか
・減っているのか
・どのタイミングで減っているのか

までは分かりにくいことがあります。

そこで役立つのが 残高の推移グラフです。
月ごとの残高をグラフにすると、

・資金が減る時期
・資金が増える時期
・資金繰りの傾向

といったものが視覚的に分かるようになります。

また、資金繰りの説明をする際にも、グラフがあると理解してもらいやすくなります。
パワークエリで仕組みをつくっておけば、データを更新するだけでグラフも自動更新されるため、
継続的な資金管理にも役立ちます。

パワークエリで現金預金推移グラフを作成する

まずは、データの読み込みからです。
「データ」→「テキストまたはCSVから」、もしくは「Alt」→「A」→「FT1」。

今回はfreeeの元帳を活用しています。

「データの変換」をクリック。

1行目にタイトルがあるので削除します。
「ホーム」→「行の削除」→「上位の行の削除」→行数に「1」→「OK」です。

その後、1行目をヘッダーとして活用するため、
「ホーム」→「1行目をヘッダーとして使用」をクリック。

次に、余計な列を削除します。
「Ctrl」を押した状態で、必要な列のみ選択。

今回の場合は、

• 勘定科目
• 取引日
• 残高

だけ残します。

「取引日」が日付型になっているか確認。
今回は、日付型になっているのでOKです。

もし、なっていない場合は、
取引日の左横にあるマークをクリックします。
（今回の場合は「カレンダーマーク」）

すると、型のリストが出るので、
「日付」を選択します。

パワークエリ画面の「取引日」列をクリック→「列の追加」タブ→「日付」→「月」→「月の開始日」を選択。

元帳は「下にあるものほど新しい」というルールがあるため、インデックス列を追加して、
その順番を数字で記録します。

「列の追加」→「インデックス列」→「0から」。
これでインデックス列が追加されます。

次にグループ化をします。
「ホーム」→「グループ化」→上の画像を参照→「OK」です。

こういった列が追加されます。
グループ化をすることで、バラバラの明細を「月ごと・口座ごと」に分けています。

カスタム列を追加します。
「列の追加」→「カスタム列」をクリック。
新しい列名を「月末残高」として、カスタム列の式に「Table.LastN([すべて], 1)」と入力→「OK」。

「Table.LastN([すべて], 1)」は、

• 「Table.LastN」で指定したテーブルの「下から数えてN行分」を取り出す関数
• 「[すべて]」は、グループ化した1か月分の取引をひとまとめにした「テーブル」が入っている列の名前
• 「1」は取り出す行数（1番下の「最終残高」を拾っています。）

という意味があります。

後は、月末残高の右横の「展開←→」ボタンをクリック→「残高」だけにチェックを入れて「OK」です。

最後に「閉じて読み込む」をすればテーブルが完成します。

次にピボットテーブルを作成して、ピボットグラフをつくります。
テーブルを選択した状態で、「Alt」→「JT」→「V」→「OK」。

• 「月の開始日」 を 「行」 へドラッグ
• 「月末残高」 を 「値」 へドラッグ

「ピボットテーブル分析」→「ピボットグラフ」を選択。

折れ線グラフを選択して「OK」です。

今回は「テキストまたはCSV」からデータを取得しました。

ですが、パワークエリではフォルダを参照することもできます。

フォルダを参照する設定にしておけば、毎月、総勘定元帳のCSVをフォルダに入れるだけで、
現金預金推移グラフをほぼ自動で更新することができます。

残高だけなら、会計ソフトでも確認できますが、
その他の項目を追加することで、違った視点でも分析が可能です。
これがExcelの大きな強みですよね。

---

【ログ帳】
昨日は、朝にブログを更新。
その後は、オフ。
実家に子どもと行きました。