【パワークエリ】部門費実績配賦を都度変動する配賦基準で実行する。
１．やりたいこと
下図ＢＥＦＯＲＥのような基データがあります。部門A、B、C で発生した部門費は共通費用となるため、売上高（実発生額）を配賦基準として部門D～Gへ配賦したいというケースです。月単位でこの処理を行うという前提ですので、配賦基準（売上高）は月次の都度変動するものとなります。これをパワークエリ上で実施し、最終的にＡＦＴＥＲのような状態（配賦後の部門費が部門A～Cは0となり、部門D～Gに賦課）にします。また、配賦額と賦課額の合計に計算上の端数差異が発生した場合は配賦先の特定部門で端数調整を行い配賦額列の合計は0になるようにします。
２．エクセルワークシートで関数等により計算する場合（参考）
パワークエリでの処理を説明する前に同じことをエクセルで実行する場合について、参考までに書いておきます。これを見てからの方が理解が早いと思います。
下図Ｇ～Ｊ列は基のデータとなりますので、Ｋ、Ｊ列に数式を入れて作成します。Ｋ3～Ｋ9セルに入力する数式を下図右側に記載しています。Ｊ列の数式は省略していますが単純に部門費_配賦前と配賦額を足すだけとなります。
Ｋ列（配賦額）のＫ3～Ｋ5セルについては配賦元となりますのでＩ3～Ｉ5セル（部門費_配賦前）に -1 を乗じるだけです。Ｋ6～Ｋ9セルは部門A～Ｃの部門費合計額（300+281+422＝1,003）を売上高_配賦基準の割合で案分配賦する数式です。ROUND関数で小数点以下端数は四捨五入するようにしています。ただし、この計算でＫ6～Ｋ9を計算した結果の合計は 1,004 となり 1 の端数が生じてしまうため、部門Ｇの配賦額（K9セル）で、-1 の調整をしています。
（補足）これを実務でやる場合は都度端数を+-するのではなく、Ｋ9セルの数式は、
=SUM(I3:I5)-SUM(K6:K8) とするのが無難だと思います。
さて、これをパワークエリでやるのですが、実は基本的にはやることは前述のエクセルと変わりませんのでここの考え方は覚えておいてください。
３．実践
まず、パワークエリエディタを起動します。基データを取り込んで型を変更した状態（操作省略）です。クエリの設定ペインの適用したステップは「変更された型」となっています。
列の追加タブ → カスタム列 をクリックします。
配賦元と配賦先ははじめは列を分けて作成していきます。
下図のように、新しい列名を 配賦元 とし、カスタム列の式は以下を入力してOKをクリックします。
= if [配賦対象] ="○" then [部門費_配賦前]*-1 else 0)
IF文となります。配賦対象列が ○ であれば 部門費_配賦前 に -1 を乗じた金額
それ以外は 0 とする といった内容です。
※もし基データに配賦対象を示す列がない場合は if 文の分岐を配賦対象の部門毎に記入します。
配賦元列が追加されました。適用したステップは「追加されたカスタム」となります。
※適用したステップの名称は説明上はデフォルトのままとしますが、後でわかりやすい名称に変更するのがベターです。
再度、列の追加タブ → カスタム列 をクリックします。
新しい列名を 配賦元 とし、カスタム列の式は以下を入力してOKをクリックします。
＝if [配賦対象]=null then
Number.Round(
List.Sum(追加されたカスタム[配賦元]) * [売上高_配賦基準]
/ List.Sum(追加されたカスタム[売上高_配賦基準])
,0,RoundingMode.AwayFromZero ) * -1
else 0)
配賦対象列がnull（空白）であれば、
配賦元の合計に売上高_配賦基準を乗じた額を
売上高_配賦基準の合計で除した金額 →これを円未満四捨五入した金額に
-1を乗じた金額とする。
それ以外は 0 とする。
といった内容です。
配賦先列が追加されました。適用したステップは「追加されたカスタム1」となります。更に、列の追加タブ → カスタム列 をクリックします。
新しい列名を 配賦元 とし、カスタム列の式は以下を入力してOKをクリックします。
ここで配賦元合計と配賦先合計に端数が生じた場合、その端数は部門Ｇで調整する記述をするのがポイントとなります。
＝if [部門] = "G" then
[配賦元] + [配賦先]
- List.Sum(追加されたカスタム1[配賦元])
- List.Sum(追加されたカスタム1[配賦先])
else
[配賦元] + [配賦先]
部門がＧの場合は、
配賦元と配賦先の金額を加算した金額から
配賦元列の合計を減算し 更に （→端数調整）
配賦先列の合計を減算する。 （→端数調整）
それ以外は
配賦元と配賦先の金額を加算した金額とする。
といった内容です。
配賦額列が追加されました。部門Ｇの配賦先額 294 に対し、配賦額 が 293 となっており端数調整がされた結果になっていることがわかります。適用したステップは「追加されたカスタム2」となります。
配賦額列ができれば、先に作った配賦元列と配賦先列は不要となりますので列を選択した状態で右クリックし、列の削除をクリックします。
適用したステップに「削除された列」が追加されました。
最後に配賦後の部門費列を作りますので、部門費_配賦前列と配賦額列を選択した状態で、列の追加タブ → 標準 → 加算 をクリックします。
適用したステップに「挿入された加算」ができ、列が追加されますが、この操作の場合新しい列名はデフォルトで「加算」となります。この後、「加算」列の見出しをクリックして名前を変更してもよいのですが、その場合、適用したステップに「名前が変更された列」という余計なステップが追加されてしまいますので、今回は数式バーの列名”加算”となっているところを直接 ”部門費_配賦後”に書き換えることとします。
列名が変更されました。これで完成です。
４．おまけ
最後に完成したものをエディターからワークシートに読み込み数値の確認を行います。
下図が読み込んだままの状態です。これにテーブルの機能で合計行を追加します。
テーブルデータ上で右クリックし、テーブル＞集計行 をクリックします。
集計行ができますので１列ずつプルダウンから合計を選択します。（これを複数列一括で選択する方法が知りたい。）
すると配賦額の合計は0となっており、賦課後の合計金額も賦課前の合計金額と合致した結果になっていることが確認できました。
以上となります。
