さて。やっとできたってのが正直なところです。
「パワークエリで仕訳データから総勘定元帳を作れないか」
では早速書いていきたいと思います。
仕訳データから総勘定元帳を作る考え方
まず、仕訳データからどのように変換すれば総勘定元帳になるでしょうか。
一番イメージしやすいのはお小遣い帳です。
こんなやつです。
総勘定元帳も基本的にはこの形式ですよね。
せいぜい相手科目が追加されるぐらいです。
ですので、仕訳データから総勘定元帳までのステップはこのように整理することができます。
- 仕訳データを出納帳の形式に変換（残高列は不要）
- フィルター機能で見たい科目の仕訳を抽出
- 抽出した結果に対して残高列を追加
そして、一番ネックだったのがこの残高列の追加です。
この前編ではこの「残高欄を追加する」方法について見ていきます。
そして後編では仕訳データを変換し任意の科目を選択する方法をお届けする予定です。
ちなみにですが、これだけでは完結しないこともあります。例えば複合仕訳や消費税はどうするのかといった話です。
これらについても後々書いていこうと思います。
この記事でやりたいこと
まず、今回やりたいことを確認しておきましょう。
この左の元データ、残高欄がありません。
これに、パワークエリで残高列を追加します。
残高列のロジック
そもそも残高ってどうやって出しますか？
今回残高 = 前回残高 + 今回収入 － 今回支出
こうですよね。まぁこの前回残高をパワークエリでどうやって拾うかが難しいわけですが。
では次にこのテーブルで考えてみましょう。
そして、残高c1,c2,c3それぞれの残高をだすには
このように計算します。先ほどの方法ですね。
では、cを使わずに表現すればどうでしょうか。
簡単ですね。このように置き換えることができます。
で、これを組み替えると・・・
このように、残高＝収入累計ー支出累計 と置き換えることができるのです。
ここまでくれば、話は見えてきませんか？
以前累計値を出す方法というのを書きましたので、その方法を使えばいいのです。
では、実際の方法を見ていきましょうか。
残高列を追加する手順
データの取得
今回はこのデータで見ていきましょう
まず、取得するデータの形式に応じて、データタブ「データの取得と変換」より対象のデータを取り込みます。
そして取込が完了です。
null値の変換
次にやることはnullを０に変換します。
これをしておかないと累計値が正しく計算されません。
例えばこのテーブル。列1＋列２という加算列を追加するとどうなるでしょうか。
答えはこうです。null値を含む3行目の計算が正しく処理されません。
ですので、null値を排除しておく必要があります。
方法は簡単です。
収入列と支出列を選択した状態で変換タブ「値の置換」を選択します。
そして検索値をnull、置換後の値を０とします。
するとこう。
nullが０に置き換わりました。
インデックス列の追加
次に累計範囲を指定するためのインデックス列を追加します。
列の追加タブ「インデックス列」より「１から」を選びます。
インデックス列を追加したテーブルがこちらです。
残高列の追加
そして残高列の追加です。
残高は「収入累計ー支出累計」と書きました。
それをカスタム列の追加で表現すればいいだけです。
そして列名を残高とし、カスタム列の式を書いていきます。
List.Sum(１～インデックス列の収入リスト）
– List.Sum(１～インデックス列の支出リスト）
となっていることが分かると思います。
一応式の内容をコピーしておきます。
List.Sum(
List.FirstN(追加されたインデックス[収入],[インデックス]))
-List.Sum(
List.FirstN(追加されたインデックス[支出],[インデックス]))
ちなみに式の内容については改行やインデントを使って見やすくしておくことをお勧めします。
これで、残高列が追加されました。
そしてインデックス列の削除やデータ型の変換等で整えて完成です。
あとは用途に応じてテーブルとして貼り付けたり、接続のみにしたりで閉じて完了です。
最後に
いかがだったでしょうか。
今回は出納帳の残高列をパワークエリで作る方法についてお届けしました。
正直これだけならパワークエリを使わずにSUM関数でできることですが、仕訳データから総勘定元帳を作る場合どうしても必要なステップだったため、パワークエリで残高列を追加する方法として書きました。
この出納帳の残高列を追加する方法は分かったと。
次、後編では仕訳データからこの出納帳残高なしの状態まで変換する方法をお届けしたいと思います。
