【エクセルは本当に便利なツール。但し、効率と効果を上げるには限界があります】
資金繰り表はエクセルに向いている業務の典型です
それは何故か？
上の画像の黄色のセルには数式が入っており、預金残高の予想残高を計算しています
但し、この数式を必要に応じて実際の預金残高「数値」で上書きすることができます
実はこれってエクセル以外のソフトだと難しいんです
「数式」と「数値」を共存できるエクセルは本当に便利なんです
ではエクセルで業務を続けていたらそれでいいのか？
それはそれで問題があります
下の画像の「2023/1/5」の売上高の欄には120と入っています
入力数字は120でも、実は120という数字を別な表で複数の顧客の数字を積み上げて入力されているかもしれません
それに、表に存在する日付/列単位が膨大になってくると、入力箇所を見つけるのも面倒です
つまり量が膨大になってくると、この資金繰り表の1セルを入力する作業がかなり面倒になってきます
その理由は一般的なエクセルの資金繰り表では「データ蓄積」「作業」「計算」「表示」が分かれていないからです
ですので、作業や計算スペースを作る関係上、データの並びが不規則になってしまったりしますし、入力しやすいようなスペースを作ることも困難です
更に言えば、資金繰り表のシートのデータを活用して他のシートで別の目的の表を作成しようとするとかなり複雑なものになってしまいます
実は、Power BIのDAXを使用すれば預金口座の残高も、実残高と予想残高を共存させながら計算できます
今回の記事ではPower BIとPower Appsを使用して、エクセルの資金繰り表の「データ蓄積」「作業」「計算」「表示」を分けて管理できる仕組みの概要を紹介します
そして、Power Platformの便利さに触れて頂けたら幸いです
ポイント
Power Platformを使って資金繰り表を作成するには、分解が結合がポイントです
データの分解・結合
預金残高は数式と数値を共存できるように、以下にデータを分けて管理します
・預金残高（実残高）
・入金
・出金
更にカレンダーテーブル
上記の４つをDAXで結合して使用します
今回はSharepointリストを使用します
入出力と表示の分解
今回は入出力はPower Appsで行います
Power Appsならページを分けて、売掛金や経費などの入出力専用ページを作成できます
そして、表示や主な計算はPower BIで行います
Power BIは集約計算の簡単にできるし、DAXで複雑な計算も行えますので、Power BIの機能もフル活用します
エクセルで日付毎に複数の顧客の売掛金を合計していた作業は、前述のテーブルを作成しておけば勝手にPower BIが行ってくれます
入出力と表示が分かれていると不便だろうという場合は、後述するPower BI内でPower Appsを使う方法もあります
預金残高を表示するDAXの作成
ここがエクセルの資金繰り表を他のソフトで行う最大の難所です
日付の管理がとても重要です
・実際に預金残高を入力した日付の残高⇒そのまま残高を表示
・預金残高を入力していない日付の残高⇒実際に預金口座を入力した日付以降の入出金を加味して計算
例えば、1月1日の残高が１００、1月2日の入金が１００、1月３日の出金が１００だったとします
この場合は
１月１日残高⇒１００/実績残高
１月２日残高⇒２００（１００＋１００）/予想残高
１月３日残高⇒１００（１００＋１００ー１００）/予想残高
となります
こちらを実際にDAX式に直したのが以下です
＊CashBalance:実際の預金残高、Receipt:入金、Disbursement:出金
預金残高 =
VAR currentDay=MAX(‘Calendar'[Date])
VAR maxDay=CALCULATE(MAX(‘CashBalance'[Date]),FILTER(All(CashBalance),’CashBalance'[Date]<=currentDay))
VAR cashAmount=SUMX(FILTER(ALL(‘CashBalance’),’CashBalance'[Date]=maxDay),’CashBalance'[CashBalance])
VAR cashReceipt=CALCULATE(SUM(‘Receipt'[Amount]),FILTER(All(‘Receipt’),’Receipt'[Date]>maxDay &&’Receipt'[Date]<=currentDay))
VAR CashDisbursement=CALCULATE(SUM(‘Disbursement'[Amount]),FILTER(All(‘Disbursement’),’Disbursement'[Date]>maxDay &&’Disbursement'[Date]<=currentDay))
RETURN cashAmount+cashReceipt-cashDisbursement
今回の記事では詳細には解説を行いませんが、一見複雑なこの式の最大のポイントは「預金残高の最大日付：maxDay」を算出することです
冒頭のたとえを使って説明すると、今日が１月３日だとしたらその前に預金残高がテーブル：CashBalanceに貴重された日付の１月１日を算出するということになります
最大日付が算出できれば、おのずと入金金額を算出する期間と出金金額を算出する期間が求まります
ただ実際には、フィルターを意識してAll関数をうまく組み合わせていく必要があります
専用アプリによる預金残高表示
Power BIには様々なアプリ/ビジュアルがあります
今回は資金繰り表に活用できるビジュアルを１つ紹介します
その他のビジュアルの取得をクリックして「Calendar」と検索してください
すると「Beyondsoft Calendar」というビジュアルをダウンロードできます
こちらのビジュアルは日々の預金残高をカレンダー上で表示できるという優れものです
残高の状態によって色を変えたりもできます
エクセルではなくPower BIを使用することで、専用アプリを活用することもできるんです
Power BIとPower Appsの連携
Power AppsをPower BI内で使用したい場合は、こちらをクリックするところからはじめます
すると、Power Appsに使用するフィールドの入力ができるようになります
実は、こちらはなんでも構わないんです。取り合えば何か設定すればPower BI内で表示したいPower Appsのアプリを選択できるようになります
＜まとめ＞
今回は脱エクセルについて考えると称して、エクセルの資金繰り表をPower AppsとPower BIを連携させて作成する方法の概要を紹介しました
紹介したのはあくまで概要ですが、エクセル作業が改善されていく可能性を感じて頂けたら幸いです
現実に、エクセルは表計算ソフトですので大量のデータを処理するには向いていません
なので、Power PlatFormの仕組みを利用して「データ蓄積」「作業」「計算」「表示」を分解・結合できるようにすることはとても意義があります
そうは言っても、誰でも手軽に使えるエクセルと違ってPower BIのDAXなどが複雑であったりするので専門知識が必要です
それに当然、手間暇も必要です
ですので、最終的にはどの程度の量の業務をどんな風に変えたいかが一番のポイントなのかもしれませんね
