kansapoのデータを使ったExcelオンラインの予実管理TIPS

月次の財務モニタリングはkansapo上で、4半期は組織のFMTに合わせて財務報告をしたいというニーズが先日ありました。そのExcelを作成したときのTIPSをまとめておきます。

今回やりたいこと

kansapoから出力した部署ごとの実績を予実管理シートに反映させていきます。

予実管理シートサンプル

今回はとてもシンプルにしていますが、月ごとの予実を比較する場合もほぼ同様の関数で対応できると思います。

kansapoの「予実月次推移」から出力されるデータ

kansapoの「予実月次推移」から部署を選択して出力すると、「科目 > （明細） > 数値項目 > 月ごと～… > 合計」という形式で出力されます。

サンプル

Excelへ貼り付け

それでは出力したデータをExcelの新しいシートに貼り付けます。
貼り付けた後、そのデータをテーブル化します。
今回テーブル名は「amount」という名前を付けました

関数を使うときにテーブル名で参照させることができるのでおススメです。

予実管理シートへの反映

Excelを利用する場合、ローカルのExcelファイルを使う場合と、Excelオンラインを使う場合があります。今回はExcelオンラインでも開ける関数を利用していきます。（ローカルのみの場合は配列を使うのをお勧めします）

準備

kansapoのデータを参照できるように、事前に準備をしていきます。

1行目にkansapoの列名を取得できるようにしています。
A列の右側に列を追加し、kansapoの勘定科目名を入力します。※予実管理シートと会計上の科目名が完全に一致している場合は不要です。

予実管理でモニタリングするときは不要なセルなので、非表示にしましょう。

年間予算への反映

年間予算は、kansapoデータから以下の条件で取得できます。

• sum列の値

• itemNameがB列の勘定科目名と同様

• rowTypeが「budgetAmount」

これをExcel関数で表現すると以下になります。

=SUMIFS(amount[sum],amount[itemName],[@勘定科目],amount[rowType],"budgetAmount")

こうすることで、勘定科目が増えたり、並び替えがあったりしても修正が容易になります。

月次実績の反映

さて、年間予算と同様に反映させていきます。

• 各月列の値

• itemNameがB列の勘定科目名と同様

• rowTypeが「amount」

=SUMIFS(amount[_202204],amount[itemName],[@勘定科目],amount[rowType],"amount")

ただ、各月の列が動的に変わらないので、変更が大変になってしまいます。

そこで、INDIRECT関数を使っていきます。
INDIRECT関数を使うことで指定するテーブル名を可変的に扱うことができます。

あとは「合計」列と、「費消率」を入れて完了です。

基本はkansapoで完結する業務調整を

今回はkansapoから出力したデータをExcelに貼り付け、Excel上で予実管理をするTIPSについて紹介してきました。

基本はkansapoで業務が完了するように設計するのがおススメですが、何かしらハードルがありExcel運用が残ることも少なくはないと思います。そんな時この記事が参考になれば嬉しいです。

それではまた次回。