【パワークエリ】試算表データから損益計算書を作成する。
パワークエリを使って会計システム等の試算表科目別のデータから損益計算書（ＰＬ）の体裁の表を作成する方法を書いてみたいと思います。科目や金額については適当に割り当てたものを使用しています。
【目次】
１．やりたいこと
試算表データから 損益計算書（以下、ＰＬ）の体裁にしたものをパワークエリ上で出力します。
ポイントは「売上総利益」や「営業利益」等、基のデータにない行をどのようにクエリ上で追加するかです。
今回のＰＬ作成にあたり、次の科目マスタテーブルを準備しておきます。各試算表科目に対応するＰＬの表示科目の紐づけとなります。「No」列はＰＬ科目の表示順の為に振っています。「計算区」列は収益科目は「1」、費用科目は「-1」としており、最終的にＰＬ科目で集計した際に各ＰＬ科目の合計が利益となるようにします。
２．基データと科目マスタのマージ～ＰＬ科目のグループ化
この項目については、若干速足で説明します。
パワークエリエディターを開き、試算表データの状態でホームタブ→クエリのマージをクリックします。
試算表データ(ＰＬ作成テーブル）の試算表科目コード列と科目マスタテーブルの試算表科目コード列を選択し、結合の種類は左外部（デフォルト）としてＯＫをクリックします。
科目マスタ列が追加されたら列見出しの右側の矢印ボタンをクリックします。展開する列の候補が出たら、「No」「ＰＬ科目」「計算区」のみチェックをつけ、更に「元の列名をプレフィックスとして使用します」のチェックを外し、ＯＫをクリックします。
科目マスタの列がマージされた状態になりました。次に「No」列を選択した状態で、ホームタブ→並べ替え 昇順ボタンをクリックします。
次に「金額」列と「計算区」列を選択した状態で、列の追加タブ→標準 から乗算をクリックします。乗算列が追加されます。これで収益科目はプラス表示、費用科目はマイナス表示されるようになりました。
「乗算」列の名前を「ＰＬ金額」に変更しておきます。（任意）
ホームタブ→グループ化 ボタンをクリックします。
詳細設定のラジオボタンを選択し、グループ化の項目は「No」「ＰＬ科目」の順に選択します。新しい列名は「金額」、操作は「合計」、列は「ＰＬ金額」を選択し、ＯＫをクリックします。
これでＰＬ科目別、表示順毎にグループ化されました。
２．損益計算書の各集計行を追加する。
ここからがポイントとなりますのでじっくり説明していきたいと思います。
また、今回の集計行追加の前提としてＰＬ科目の項目数は固定であるものとします。
各集計行の追加には次の3つのＭ関数を使用します。
Table.InsertRows
List.Sum
List.FirstN
まず、前項のグループ化された行の状態から、数式バーの左にあるfx（ステップの追加）をクリックします。そうすると、クエリの設定ペインに「カスタム1」のステップが追加され、数式バーには前ステップである「=グループ化された行」が表示されます。この数式バーの「=グループ化された行」を直接次の数式に書き換えます。
変更前：
= グループ化された行
変更後：
= Table.InsertRows(グループ化された行,2,{[No=null,ＰＬ科目=" 売上総利益",金額=List.Sum(List.FirstN(グループ化された行[金額],2))]})
これで、「売上総利益」の行が追加されました。
金額も 売上高 19,870,000 + 売上原価 △12,885,000 = 売上総利益 6,985,000
と正しく計算されています。
このM関数を使った数式について次の画像で詳しく説明します。
次にクエリの設定ペインで、先ほど数式を変更したステップ「カスタム1」を右クリック→名前の変更をクリックし、名前を「売上総利益の追加」に変更します。
次は、営業利益の行（売上総利益 + △販売費及び一般管理費）を追加していきます。
先ほどと同様に数式バーの左にあるfx（ステップの追加）をクリックし、数式を直接変更します。
変更前：
= 売上総利益の追加
変更後：
= Table.InsertRows(売上総利益の追加,4,{[No=null,ＰＬ科目=" 営業利益",金額=List.Sum(List.FirstN(グループ化された行[金額],3))]})
次の画像で詳細を説明しますが、ここで重要なことは、
Table.InsertRows関数の対象は前ステップの「売上総利益の追加」に対してセットしますが、List.Sumの対象のList.FirstNの対象は売上総利益を追加する前のステップ「グループ化された行」を対象とすることです。
残りの追加する行については基本的に営業利益同じ方法となりますので説明は割愛します。ただし、営業外収益と営業外費用については項目のみの行となる為、数式は次の通りとなります。
営業外収益の追加
= Table.InsertRows(営業利益の追加,5,{[No=null,ＰＬ科目="営業外収益",金額=null]})
営業外費用の追加
= Table.InsertRows(営業外収益の追加,8,{[No=null,ＰＬ科目="営業外費用",金額=null]})
最後に「No」列は不要となりますので列を削除したら完成です。
