【パワークエリ】試算表データから貸借対照表を作成する。

今回はパワークエリを使って会計システム等の試算表データから貸借対照表（B/S）を作成してみたいと思います。貸借対照表の書式は経団連のひな型ベースで作成します。科目・金額は適当なものを使用しました。

【目次】

 １．やりたいこと

試算表データから、貸借対照表（以下、BS）の体裁にしたものをパワークエリ上で出力できるようにします。

今回のBS作成にあたり、次の科目マスタテーブルを準備しておきます。各試算表科目に対応するBSの公表表示科目の紐づけとなります。「表示順」列はBS科目の表示順の為に振っています。「金額区」列は「1」または「-1」が入っており、資産の部でマイナス表示する「貸倒引当金」のみ「-1」としています。

２．作成のポイント（Ｍ関数）

①「流動資産」や「固定資産」の基のデータにない合計行の表示箇所の設定

→（作成タイミングの科目の増減への対応：例えば1Ｑ末で有価証券の残高があったが、2Qで売却し残高が0となった場合、又はその逆の場合の増減に対応するため各合計行の表示箇所をインタラクティブにしておく必要がある。

※前回、損益計算書の作成時はこの点は考慮していなかった。

②上記 各合計行の金額集計方法

→これも基のデータにはないので集計した結果を表示する必要がある。

③最終的なBSの貸借表示方法

→損益計算書は縦に作成して合計行を追加すればよかったが、BSは貸借表示なのでこれをクエリ上でどう表現するか。

３．実践（1）試算表データ調整～科目マスタとのマージ～科目別のグループ化 etc

この項目は若干速足（雑）に説明します。取り込んだ試算表データのクエリ名は「BS作成」、科目マスタのクエリ名は「科目マスタ」としています。

まず、「BS作成」にて取り込んだ試算表データの残高列にフィルターをかけて、 0　のチェックを外しOKをクリックします。

ホームタブ → クエリのマージ をクリックします。

BS作成 の「総勘定科目コード」列と科目マスタの「総勘定科目コード」列を選択し、結合の種類は「左外部」（デフォルト）としてOKをクリックします。

追加された科目マスタ列 右上の矢印ボタンをクリックします。

BS作成クエリと重複している「総勘定科目コード」「科目名」のチェックを外し、更に「元の列名をプレフィックスとして使用します」のチェックを外しOKをクリックします。

試算表データに科目マスタの列がマージされました。

次に、「残高」列と「金額区」列を選択した状態で、列の追加タブ → 標準 → 乗算 をクリックします。

乗算列が追加されたら、残高列を削除します。その後、「乗算」列の名称を「残高」に変更します。

次にホームタブ → グループ化 をクリックします。

詳細設定のラジオボタンを選択し、次の画像の通り「区分0」「区分1」「区分2」「表示順」「科目」列を設定します。下の新しい列名は「金額」、操作「合計」、列「残高」としOKをクリックします。

グループ化されました。次に、表示順列を選択した状態で、ホームタブ → 並べ替え（昇順）ボタンをクリックします。

グループ化されたクエリが表示順を番号に並びました。クエリの設定ペインのステップは「並べ替えられた行」となっています。この状態から次項目でどのように集計行を追加していくかがポイントとなります。

４．実践（2）各集計行の追加～Ｍ関数～

数式バーの左にあるfx（ステップの追加）をクリックします。そうすると、クエリの設定ペインに「カスタム1」のステップが追加され、数式バーには前ステップである「=並べ替えられた行」が表示されます。この数式バーの「=並べ替えられた行」を直接次の数式に書き換えます。

変更前：

＝並べ替えられた行

変更後：

= Table.InsertRows(並べ替えられた行,0,{[区分0="資産",区分1=null,区分2=null,表示順=null,科目="　　　(資産の部)" ,金額=null]})

これで、（資産の部）の行が追加されました。ステップ名は「カスタム1」は「資産の部の追加」に変更しておきます。

上記Ｍ関数 Table.InsertRowsはテーブルに行を挿入する関数で、”並べ替えられた行”の0行目（Ｍ関数では0が1）に、それぞれ次の列の内容 「区分0」→資産、「区分1」→空白、「区分2」→空白、「表示順」→空白、「科目」→　　　(資産の部)、「金額」→空白　を挿入します。

※「区分0 」に資産を入れるのは最後の項目でフィルタする為

※「科目」の文字の前には全角スペース×3を入れています。

次に「流動資産」の合計行を挿入します。先ほどと同様に数式バーの左にあるfxをクリックし、前ステップが入った数式を書き換えます。（以降、ここの画像は省略します）

変更前：

＝資産の部の追加

変更後：

= Table.InsertRows(資産の部の追加,1,{[区分0="資産",区分1=null,区分2=null,表示順=null,科目="流動資産"

,金額=Table.Group(#"名前が変更された列 ", {"区分1"}, {{"区分1計", each List.Sum([残高]), type number}}){[区分1="　流動資産"]}[区分1計]]})

上記数式の 金額列 部分について次の画像で詳しく説明します。

流動資産の合計行が追加されました。挿入位置は（資産の部）の次の行なので、1としました。金額も各流動資産を合計した金額が正しく表示されました。ステップ名は「流動資産の追加」に変更しておきます。

続けて「固定資産」の合計行を挿入します。同様に数式バーの左にあるfxをクリックし、前ステップが入った数式を書き換えます。

変更前：

＝流動資産の追加

変更後：

= Table.InsertRows(流動資産の追加,　　　　　　　　　　　　　　　　　　　　　　　   List.Count(List.Select(並べ替えられた行[区分1], each _="　流動資産"))+2   
 ,{[区分0="資産",区分1=null,区分2=null,表示順=null,科目="固定資産"
 ,金額=Table.Group(#"名前が変更された列 ", {"区分1"}, {{"区分1計", each List.Sum([残高]), type number}}){[区分1="　固定資産"]}[区分1計]]})

ここでポイントとなるのが固定資産の合計行の挿入位置です。次の画像の通り、挿入したい位置は流動資産の科目（最後）と固定資産の科目（最初）の間となります。

その挿入位置を示す部分のＭ関数について次の画像で詳しく説明します。次の画像と前の画像を比べてみるとわかりやすいと思います。

固定資産の合計行が13行目に挿入されました。これで、科目が増減した場合も流動資産と固定資産の科目の間に固定資産の合計行が挿入されるはずです。金額も正しく計算されました。ステップ名は「固定資産の追加」に変更しておきます。

この後、「有形固定資産」～「負債・純資産合計」の行を挿入していきますが、数式の内容は前述とほぼ変わりませんので個別の説明は省略します。次の画像は最後の「負債・純資産合計」まで挿入した状態となります。

また、ここまでステップの追加はfxボタンをクリックして数式を入力する説明をしてきましたが、ホームタブ→ 詳細エディター にて直接数式を書いていく方が効率的だと思います。詳細エディターで書く時の主な注意点は次の通りです。

・各ステップの数式 ＝ の前にステップ名を書くこと  →ここで書いたステップ名がクエリの設定ペインに反映されます。

・最後のステップ以外は各ステップの数式の末尾に  ,　を入力すること。

・最後のステップの後の　in　の後には最後のステップ名を入力すること。

また、見やすいように各ステップ間にスペース行を入れたり数式を改行したりできますし、// (スラッシュ×2）を入力した後に入力する文字は薄緑色となり、補足説明やメモとすることができます。

同じものの繰り返しになってしまいますが詳細エディターの「有形固定資産」以降の行追加を参考まで末尾脚注に記しておきます。

 【脚注】詳細エディター「有形固定資産」以降の行追加*1