« Wi-Fi 7 Router Aterm 19000T12BE | トップページ | 数量と単価の表が別で、単価表は改定日のみの場合のExcelによる売上金額計算 »

Excel Power Pivotで利益偏差を単価要因と数量要因と原価要因に分ける

1. 前書き

予算と実績の売上偏差や利益偏差を売価要因と数量要因と原価要因に分ける計算を、Excel Power Pivotのメジャー(DAX)で行いました。
メジャーを使うと、ワークシート式より式の数が少なくなり、式にセルアドレスを使用しないので可読性が向上し、ミスが減ります。
本例では、Power Queryのヘルパークエリ[*1]を用いた複数シートの結合など、モダンExcelの色々な機能を使用していますが、モダンExcelを知らない人でも作成できるように手順を記載しています。
モダンExcelを知っている人は、途中を飛ばしてメジャーだけを見てください。
検証をあまりしていないので、使用される場合は検証を行い、自己責任で使用して下さい。

[*1] ヘルパークエリを使うと、Power Query Mのコードを直接書かずに、クリック操作で関数クエリを作成・編集することができます。

対予算売上偏差の数量要因と売価要因

売上偏差 = 実績数量 × 実績売価 - 予算数量 × 予算価
= 純粋な数量要因 + 純粋な売価要因 + 交差項(数量と売価が同時に動いた分)

「按分」なので、交差項を数量側・売価側に均等に振り分ける平均法(折半法)を使います。

数量要因 = (実績数量-予算数量) × (予算売価+実績売価)/2 ← 平均売価を使う
売価要因 = (実績売価-予算売価) × (予算数量+実績数量)/2 ← 平均数量を使う

とすると、数式的に必ず「数量要因 + 売価要因 = 売上偏差」が成立します(交差項がちょうど半分ずつ両方に含まれる形になるため)。

対予算利益偏差の数量要因と売価要因と原価要因

利益は 数量 × (売価 − 総原価) なので、変数を「数量」と「粗利売価(売価−原価)」の2つとみなすと、既存の M数量要因予 / M売価要因予 と全く同じ「平均法」の式が使えます。

粗利売価の差 Δ(売価−原価) をさらに「売価要因」と「原価要因」に分解すればよいので、3要因に拡張した形になります。

数量要因 = Δ数量 ×(実績粗利売価+予算粗利売価)/2
売価要因 = Δ売価 ×(実績数量+予算数量)/2
原価要因 = −Δ原価 ×(実績数量+予算数量)/2 ※原価上昇は利益を下げるのでマイナス

この3つを足すと [M利益差予] に一致します(売上偏差の2要因分解と同じ代数的恒等式を使っているため)。

💡 価偏差要因の調査方法

総原価の要因分析は、掘り下げが必要です。総原価の科目別などの内訳をモデル化すれば、掘り下げが可能になります。Excelのピボットテーブルをダブルクリックするとドリルダウンができまが、要因分析をするならPower BI他のBIツールの方が便利だと思います。例えば、Power BIの分解ツリーは、影響の大きいい要因をツリーを辿ることで調べることができます。

余談 : このビジュアルはExcelに欲しいですね。ExcelもGoogle Sheetsにシェアを奪われ始めているので、ExcelのBI機能を強化して欲しいです。Power BI for Excel Add Onがあると嬉しいです。個人作業の調査ツールとして使うのであれば、無料のPower BI Desktopで分解ツリーを使うことができます。Power Queryとメジャーは、Excelと全く同じものが使えます。

 

2. 入力

本記事の説明で取り扱うデータ構成のは一例であり、これ以外のファイル構成、シート構成、表の形でも、Power Queryで対応することができます。

1) 予算

26年度予算

項目 製品 26/04 26/05 途中省略 27/03
数量 製品A 11 11 16
製品B 6 6 8
売価 製品A 105 105 105
製品B 95 95 95
総原価 製品A 100 100 100
製品B 90 90 90

260721diff1

  • 予算ブックは1つで、各年度のシートがある。
  • 年月はyy/mm形式のテキスト型。

2) 実績

26年度実績

日付 製品 数量 売価 総原価
26/04/01 製品A 11 105 100
26/04/01 製品B 6 95 90
26/05/01 製品A 12 105 100
26/05/01 製品B 6 96 91
途中省略
27/03/01 製品A 15 105 100
27/03/01 製品B 8 94 89

260721diff2

  • 実績ブックは1つで、各年度のシートがある。
  • 日付はシリアル値。

3. 出力

1) 対予算偏差

値 製品 26年度 途中省略 26年度 集計
1Q 1Q 集計
4月 5月 6月
M売上 製品A 1,155 1,260 1,155 3,570 16,972
製品B 570 576 564 1,710 7,979
M利益 製品A 55 60 55 170 720
製品B 30 30 30 90 377
M利益差予 製品A ±0 +5 -5 ±0 -90
製品B ±0 ±0 ±0 ±0 -43
M利益差数量要因予 製品A ±0 +5 -5 ±0 ±0
製品B ±0 ±0 ±0 ±0 ±0
M利益差売価要因予 製品A ±0 ±0 ±0 ±0 -38
製品B ±0 +6 -6 ±0 -1
M利益差原価要因予 製品A ±0 ±0 ±0 ±0 -52
製品B ±0 -6 +6 ±0 -42
全体の M売上 1,725 1,836 1,719 5,280 24,951
全体の M利益 85 90 85 260 1,097
全体の M利益差予 ±0 +5 -5 ±0 -133
全体の M利益差数量要因予 ±0 +5 -5 ±0 ±0
全体の M利益差売価要因予 ±0 +6 -6 ±0 -39
全体の M利益差原価要因予 ±0 -6 +6 ±0 -94

260721diff3

2) 対前年偏差

値 製品 26年度 途中省略 26年度 集計
1Q 1Q 集計
4月 5月 6月
M売上 製品A 1,155 1,260 1,155 3,570 16,972
製品B 570 576 564 1,710 7,979
M売上前年 製品A 990 1,089 1,287 3,366 15,828
製品B 623 534 445 1,602 7,522
M売上偏差前年 製品A +165 +171 -132 +204 +1,144
製品B -53 +42 +119 +108 +457
M数量要因前年 製品A +102 +102 -204 ±0 +306
製品B -92 ±0 +92 ±0 ±0
M売価要因前年 製品A +63 +69 +72 +204 +838
製品B +39 +42 +28 +108 +457
全体の M売上 1,725 1,836 1,719 5,280 24,951
全体の M売上前年 1,613 1,623 1,732 4,968 23,350
全体の M売上偏差前年 +112 +213 -13 +312 +1,601
全体の M数量要因前年 +10 +102 -113 ±0 +306
全体の M売価要因前年 +102 +111 +100 +312 +1,295

260721diff4

4. データモデルの作成

1) 予算データの取込みと整形

複数シートのクロス表をデータベース形式に整形し、縦結合してデータモデルに追加します。

データの取得

  1. [データ]-[データの取得v]-[ファイルから>]-[Excelブックから]で予算ブックを選択し[インポート]、整形のサンプルとするシートを選択し[データの変換]でPower Queryエディタに取込み、右のクエリの名前を"サンプルシート予"に変更。
  2. 右の"ソース"と"ナビゲーション"以外のステップの左の✘をクリックして削除。ソースとナビゲーションの2ステップだけにする。 ⇒ サンプルシート予が完成。

パラメータクエリの作成

  1. [ホーム]-[新しいソースv]-[その他のソース>]-[空のクエリ]で右の名前を"パラメータ予"に変更。
  2. 左の"パラメータ1"を右クリックし、[パラメータへ変換する]。
  3. [ホーム]-[詳細エディタ]で次のように修正し[完了]。 ⇒ ヘルパークエリの"パラメータ予"が完成。
変更前
"" meta [IsParameterQuery=true, Type="any", IsParameterQueryRequired=true]
変更後
#"サンプルシート予" meta [IsParameterQuery=true, BinaryIdentifier=#"サンプルシート予", Type="Table", IsParameterQueryRequired=true]

サンプルシートの変換クエリの作成

  1. [ホーム]-[新しいソースv]-[その他のソース>]-[空のクエリ]で新しいクエリを作成し、右の名前を"サンプルシート予の変換"に変更します。
  2. 数式バーに次の様に入力し、✓をクリックする。 ⇒ サンプルシートが表示される。
= パラメータ予

右の"ソース"以外の左の✘をクリックして削除。ソースだけにする。
  1. [ホーム]-[行の削除v]-[上位の行の削除]で行数に1を入れ[OK]。 ⇒ 不要な行が削除される。
  2. "Coloumn1"列を選択し[変換]-[フィルv]-[下へ]。>項目列のnullが埋まる。
  3. [変換]-[1行目をヘッダーとして使用v]-[1行目をヘッダーとして使用]。 ⇒ 項目名がヘッダーになる。
  4. "項目"と"製品"列をCtrlキーを押しながら選択し、[変換]-[列のピボット解除v]-[その他の列のピボット解除]。 ⇒ 年月が1列に並ぶ。
  5. 変更された型が生成されたら、それを削除。ピボット解除された他の列の数式バーの"属性"を"年月"に書き換える。
  6. "項目"列を選択し[変換]-[列のピボット]で値列に[値]、詳細設定オプションに[集計しない]で[OK]。 ⇒ "数量"列と"売価"列ができる。
  7. [列の追加]-[カスタム列]で次のように入力し[OK]。 ⇒ テキスト形式の"年月"列から日付形式の"日付"列が生成される。
新しい列名:日付
カスタム列の式:
= #date(
Number.FromText("20"&Text.BeforeDelimiter([年月],"/")),
Number.FromText(Text.AfterDelimiter([年月],"/")),
1)

Ctrl+aで全列を選択し、[変換]-[データ型の検出]。正しく型設定されない場合は、個別に列を選択して[変換]-[データ型]で適切な型に変更。 ⇒ 型が設定される。

関数クエリの作成

  1. 左の"サンプル予シートの変換"クエリを右クリックし[関数の作成...]。
    関数名に"シート予の変換"と入力し[OK]。

注意:関数クエリのコードを直接編集しないでください。編集する場合は、サンプルシートの変換クエリを編集します。すると、関数クエリが連動して修正されます。これがヘルパークエリの機能です。

結合クエリの作成

  1. 左の"サンプル予シート"クエリを右クリックし[複製]で新しいクエリを作成し、右の名前を"予算"に変更する。
  2. 右の"ナビゲーション"のステップを削除し、ソースだけにする。 ⇒ これでブック全体が取り込まれた状態になる。
  3. "Kind"列の▼-[テキストフィルター>]-[指定の値に等しい...]で値にSheetを選択し[OK]。 ⇒ 縦結合するシートのみが残る。
  4. [列の追加]-[カスタム関数の呼び出し]で関数クエリに[シート予の変換]、パラメータ1に[Date]、新しい列名に"シート予の変換"を入力し[OK]。 ⇒ 各シートを関数クエリで変換したTableが格納された列が追加される。
  5. "シート予の変換"列を右クリックし[他の列の削除]。 ⇒ 不要な列が削除される。
  6. "Table"列の右の展開アイコンをクリックし、さらに読み込むをクリックし、"プレフィックスを追加する"のチェックを外し[OK]。 ⇒ 各シートの変換後の表を展開して縦結合される。
  7. [変換]-[データ型の検出]。正しく型設定されない場合は、個別に列を選択して[変換]-[データ型]で適切な型に変更。 ⇒ 型が設定される。
  8. 左の"サンプル予シート"を"シート予の変換"グループに移動。
  9. [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"接続の作成のみ"を選択し[OK]。 ⇒ Excelに戻る。
  10. "クエリと接続"の'予算'クエリを右クリックし、[読み込み先]で"接続の作成のみ"と"このモデルをデータモデルに追加する"を選択し[OK]。 ⇒ '予算'データモデルが完成。

2) 実績データの取込みと整形

複数シートのデータベース表を縦結合して、データモデルに追加します。

データの取得

  1. [データ]-[データの取得v]-[ファイルから>]-[Excelブックから]で実績ブックを選択し[インポート]、整形のサンプルとするシートを選択し[データの変換]でPower Queryエディタに取込み、右のクエリの名前を"サンプルシート実"に変更。
  2. 右の"ソース"と"ナビゲーション"以外のステップの左の✘をクリックして削除。ソースとナビゲーションの2ステップだけにする。 ⇒ サンプルシート実が完成。

パラメータクエリの作成

  1. [ホーム]-[新しいソースv]-[その他のソース>]-[空のクエリ]で右の名前を"パラメータ実"に変更。
  2. 左の"パラメータ1"を右クリックし、[パラメータへ変換する]。
  3. [ホーム]-[詳細エディタ]で次のように修正し[完了]。 ⇒ ヘルパークエリの"パラメータ実"が完成。
変更前
"" meta [IsParameterQuery=true, Type="any", IsParameterQueryRequired=true]
変更後
#"サンプルシート実" meta [IsParameterQuery=true, BinaryIdentifier=#"サンプルシート実", Type="Table", IsParameterQueryRequired=true]

サンプルシートの変換クエリの作成
  1. [ホーム]-[新しいソースv]-[その他のソース>]-[空のクエリ]で新しいクエリを作成し、右の名前を"サンプルシート実の変換"に変更します。
  2. 数式バーに次の様に入力し、✓をクリックする。 ⇒ サンプルシートが表示される。
= パラメータ実

右の"ソース"以外の左の×をクリックして削除。ソースだけにする。
  1. [ホーム]-[行の削除v]-[上位の行の削除]で行数に1を入れ[OK]。 ⇒ 不要な行が削除される。
  2. [変換]-[1行目をヘッダーとして使用v]-[1行目をヘッダーとして使用]。 ⇒ 項目名がヘッダーになる。
  3. 正しい型になっていない列を選択して[変換]-[データ型]で適切な型に変更。 ⇒ 型が設定される。

関数クエリの作成

  1. 左の"サンプル実シートの変換"クエリを右クリックし[関数の作成...]。
    関数名に"シート実の変換"と入力し[OK]。

結合クエリの作成

  1. 左の"サンプル実シート"クエリを右クリックし[複製]で新しいクエリを作成し、右の名前を"実績"に変更するす。
  2. 右の"ナビゲーション"のステップを削除し、ソースだけにする。 ⇒ これでブック全体が取り込まれた状態になる。
  3. "Kind"列の▼-[テキストフィルター>]-[指定の値に等しい...]で値にSheetを選択し[OK]。 ⇒ 縦結合するシートのみが残る。
  4. [列の追加]-[カスタム関数の呼び出し]で、関数クエリに[シート実の変換]、パラメータ1に[Date]、新しい列名に"シート実の変換"を入力し[OK]。 ⇒ 各シートを関数クエリで変換したTableが格納された列が追加される。
  5. "シート実の変換"列を右クリックし[他の列の削除]。 ⇒ 不要な列が削除される。
  6. "Table"列の右の展開アイコンをクリックし、さらに読み込むをクリックし、"プレフィックスを追加する"のチェックを外し[OK]。 ⇒ 各シートの変換後の表を展開して縦結合される。
  7. [変換]-[データ型の検出]。正しく型設定されない場合は、個別に列を選択して[変換]-[データ型]で適切な型に変更。 ⇒ 型が設定される。
  8. 左の"サンプル実シート"を"シート実の変換"グループに移動。
  9. [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で、"接続の作成のみ"を選択し[OK]。 ⇒ Excelに戻る。
  10. "クエリと接続"の'実績'クエリを右クリックし、[読み込み先]で"接続の作成のみ"と"このモデルをデータモデルに追加する"を選択し[OK]。 ⇒ '実績'データモデルが完成。

3) 日付テーブルの生成

  1. [データ]-[データの取得v]-[その他のデータソースから]-[空のクエリ]でPower Queryエディタに"クエリ1"を生成し、クエリの名前を"日付"に変更する。
  2. [ホーム]-[詳細エディタ]で、既存のコードを次のコードで上書きする。
let
Source = 実績,
    // ── 集計対象期間の決定 ──────────────────────────
MinDate = Date.StartOfMonth(List.Min(Source[日付])),
MaxDate = Date.EndOfMonth(List.Max(Source[日付])),
    // ── 日単位カレンダーの生成 ──────────────────────
DateList = List.Dates(MinDate, Duration.Days(MaxDate - MinDate) + 1, #duration(1, 0, 0, 0)),
CalendarTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"日付"}),
型設定済 = Table.TransformColumnTypes(CalendarTable, {{"日付", type date}}),
    // ── 会計年度(4月始まり)の基準日・年・月を1回だけ算出 ──
// EDATE([@日付], -3) 相当:3ヶ月前にずらすことで
// 会計年度の「年」と「月番号(4月=1~翌3月=12)」を取得
会計基準日追加 = Table.AddColumn(型設定済, "会計基準日", each Date.AddMonths([日付], -3), type date),
会計年追加 = Table.AddColumn(会計基準日追加, "会計年", each Date.Year([会計基準日]), Int64.Type),
会計月追加 = Table.AddColumn(会計年追加, "会計月", each Date.Month([会計基準日]), Int64.Type),
    // ── 表示用列の追加 ──────────────────────────────
    // 年:YEAR([@日付])
列_年 = Table.AddColumn(会計月追加, "年", each Date.Year([日付]), Int64.Type),
    // 月:TEXT([@日付],"m月") ※ゼロ埋めなし
列_月 = Table.AddColumn(列_年, "月", each Text.From(Date.Month([日付])) & "月", type text),
    // 年月:TEXT([@日付],"yy/mm") ※ゼロ埋めあり
列_年月 = Table.AddColumn(列_月, "年月", each
let
yy = Text.PadStart(Text.From(Date.Year([日付]) - 2000), 2, "0"),
mm = Text.PadStart(Text.From(Date.Month([日付])), 2, "0")
in
yy & "/" & mm,
type text),
    // 四半期.:INT((MONTH(x)-1)/3)+1 & "Q" ※年度なし
列_四半期ドット = Table.AddColumn(列_年月, "四半期.", each
let
四半期番号 = Number.IntegerDivide([会計月] - 1, 3) + 1
in
Text.From(四半期番号) & "Q",
type text),
    // 四半期:(YEAR(x)-2000) & "/" & 四半期番号 & "Q" ※ゼロ埋めなし
列_四半期 = Table.AddColumn(列_四半期ドット, "四半期", each
let
四半期番号 = Number.IntegerDivide([会計月] - 1, 3) + 1
in
Text.From([会計年] - 2000) & "/" & Text.From(四半期番号) & "Q",
type text),
    // 半期:(YEAR(x)-2000) & "/" & 半期番号 & "H" ※ゼロ埋めなし
列_半期 = Table.AddColumn(列_四半期, "半期", each
let
半期番号 = Number.IntegerDivide([会計月] - 1, 6) + 1
in
Text.From([会計年] - 2000) & "/" & Text.From(半期番号) & "H",
type text),
    // 期:IF(MONTH(x)<7,"上期","下期") ※年度なし
列_期 = Table.AddColumn(列_半期, "期", each
if [会計月] < 7 then "上期" else "下期",
type text),
    // 年度:(YEAR(x)-2000) & "年度" ※ゼロ埋めなし
列_年度 = Table.AddColumn(列_期, "年度", each
Text.From([会計年] - 2000) & "年度",
type text),
    // 年度.:YEAR(x) ※西暦そのまま、年度なし表示との組み合わせ用
列_年度ドット = Table.AddColumn(列_年度, "年度.", each [会計年], Int64.Type),
    // 内部計算用の一時列を削除し、ご指定の列順に並べ替え
不要列削除済 = Table.RemoveColumns(列_年度ドット, {"会計基準日", "会計年", "会計月"}),
最終結果 = Table.ReorderColumns(不要列削除済,
{"日付", "年月", "四半期", "半期", "年度", "年度.", "四半期.", "期", "月", "年"})
in
最終結果
  1. [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で、"接続の作成のみ"と"このモデルをデータモデルに追加する"を選択し[OK]。 ⇒ '日付'データモデルが完成。

参考:日付テーブルをPower Query Mのコードで生成する代わりに、Excelのワークシートで作ることもできます。この場合、ワークシートに作る日付の範囲は、予算と実績ファイルの全ての日付を含むようにしてください。

4) 製品テーブルの生成

  1. [データ]-[クエリと接続]で右に"クエリと接続"を表示し、'予算'クエリを右クリックして[編集]でPower Queryエディタを開く。
  2. '予算'クエリを右クリックし[参照]で新しいクエリを作成し、クエリの名前を'製品'に変更する。
  3. [ホーム]-[クエリの追加v]-[クエリの追加]で[実績]を選択し[OK]。
  4. [品名]列を右クリックし[他の列の削除]。
  5. [品名]列を右クリックし[重複の削除]。
  6. [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で、"接続の作成のみ"と"このモデルをデータモデルに追加する"を選択し[OK]。 ⇒ '製品'データモデルが完成。

5. リレーションの作成

  1. 次のいずれかの方法でリレーションを設定する。
    a) [Power Pivot]-[管理]-[表示]-[ダイアグラムビュー]。
    b) [データ]-[データモデルv(緑アイコン)]-[データモデルの管理]-[ホーム]-[表示]-[ダイアグラムビュー]。
    c) [データ]-[データモデルv(緑アイコン)]-[リレーションシップ]-[編集]。

ファクトテーブル *--<--1 ディメンジョンテーブル(関連)
'予算'[日付] *--<--1 '日付'[日付]
'実績'[日付] *--<--1 '日付'[日付]
'予算'[製品] *--<--1 '製品'[製品]
'実績'[製品] *--<--1 '製品'[製品]

ダイアグラムビューでは、*(多)から1へ項目をドラッグする。
リレーションシップでは、上段が*(多)で下段の関連が1になるように項目を設定する。

6. ピボットテーブルの生成

  1. [挿入]-[ピボットテーブルv]-[データモデルから]で、ピボットテーブルを生成する。

7. メジャーの作成

  1. 次のいずれかの方法でメジャーを作成する。
    a) ① ピボットテーブルを選択し、右に"ピボットテーブルのフィールド"を表示。② メジャーを作成するテーブル('予算'または'実績)を右クリックし[メジャーの追加]。式の先頭は「=」から始める。
    b) [Power Pivot]-[管理]でメジャーを書くテーブルの下の空白セルを選択。数式バーにメジャーを入力する。先頭は、「メジャー名:=」から始める。
    c) [データ]-[データモデル(緑アイコン)v]-[データモデルの管理]で、メジャーを書くテーブルの下の空白セルを選択。数式バーにメジャーを入力する。先頭は、「メジャー名:=」から始める。

予算に作成するメジャー

M予売上:=SUMX('予算', '予算'[数量] * '予算'[売価])

コンテキストの'予算'テーブルの1行毎に「'予算'[数量] * '予算'[売価]」を計算して合計する。
M予数量:=SUM('予算'[数量])
M予売価:=DIVIDE([M予売上], [M予数量])

[M予売上]÷[M予数量]の意味ですが、[M予数量]が0の時はエラーを返しません。

 

M予原価:=DIVIDE(SUMX('予算', '予算'[数量] * '予算'[総原価]), [M予数量])
M予利益:=SUMX('予算', ('予算'[売価] - '予算'[総原価]) * '予算'[数量])
M予利益率:=DIVIDE([M予利益], [M予売上])
M利益差予:=[M利益] - [M予利益]
M利益率差予:=[M利益率] - [M予利益率]
M利益差数量要因予:=SUMX(
VALUES('製品'[製品]), // CROSSJOIN(VALUES('製品'[製品]), VALUES('顧客'[顧客]))
([M数量] - [M予数量])
* (([M売価] - [M原価]) + ([M予売価] - [M予原価])) / 2
)


対予算利益差数量要因 = 数量差 × 平均利益 です。利益 = 売価 - 原価 です。利益の実績と予算の平均を用います。

 

M利益差売価要因予:=SUMX(
VALUES('製品'[製品]), // CROSSJOIN(VALUES('製品'[製品]), VALUES('顧客'[顧客]))
([M売価] - [M予売価]) * ([M数量] + [M予数量]) / 2
)


対予算利益差売価要因 = 売価差 × 平均数量 です。数量に実績と予算の平均数量を用います。これは[M売価要因予]と同じです。

 

M利益差原価要因予:=SUMX(
VALUES('製品'[製品]), // CROSSJOIN(VALUES('製品'[製品]), VALUES('顧客'[顧客]))
-([M原価] - [M予原価]) * ([M数量] + [M予数量]) / 2
)


対予算利益差原価要因 = 原価差 × 平均数量 です。数量に実績と予算の平均数量を用います。

 

M売上偏差予:=[M売上] - [M予売上]

M数量要因予:=SUMX(
VALUES('製品'[製品]), // CROSSJOIN(VALUES('製品'[製品]), VALUES('顧客'[顧客])),
([M数量] - [M予数量]) * ([M売価] + [M予売価]) / 2
)


売上数量偏差 = 数量差 × 売価 です。売価に実績と予算の平均売価を用います。[*1]

 

M売価要因予:=SUMX(
VALUES('製品'[製品]), // CROSSJOIN(VALUES('製品'[製品]), VALUES('顧客'[顧客])),
([M売価] - [M予売価]) * ([M数量] + [M予数量]) / 2
)


売上売価偏差 = 売価差 × 数量 です。数量に当年と前年の平均数量を用います。これは[M利益差売価要因予]と同じです。[*1]

 

実績に作成するメジャー

M売上:=SUMX('実績', '実績'[数量] * '実績'[売価])

コンテキストの'実績'テーブルの1行毎に「'実績'[数量] * '実績'[売価]」を計算して合計する。
M数量:=SUM('実績'[数量])

M売価:=DIVIDE([M売上], [M数量])

[M売上]÷[M数量]の意味ですが、[M数量]が0の時はエラーを返しません。

 

M原価:=DIVIDE(SUMX('実績', '実績'[数量] * '実績'[総原価]), [M数量])
M利益:=SUMX('実績', ('実績'[売価] - '実績'[総原価]) * '実績'[M数量])

利益 = (売価 - 原価) × 数量 です。

 

M利益率:=DIVIDE([M利益], [M売上])
M売上前年:=CALCULATE([M売上], SAMEPERIODLASTYEAR('日付'[日付]))

CALCULATEで前年同期を示すSAMEPERIODLASTYEARにコンテキストを変更して[M売上]の中で合計SUMを求める。
M数量前年:=CALCULATE([M数量], SAMEPERIODLASTYEAR('日付'[日付]))

CALCULATEで前年同期を示すSAMEPERIODLASTYEARにコンテキストを変更して[M数量]の中で合計SUMを求める。
M売価前年:=DIVIDE([M売上前年], [M数量前年])

M売上偏差前年:=[M売上] - [M売上前年]

M数量要因前年:=SUMX(
VALUES('製品'[製品]), // CROSSJOIN(VALUES('製品'[製品]), VALUES('顧客'[顧客])),
([M数量] - [M数量前年]) * ([M売価] + [M売価前年]) / 2
)


売上数量偏差 = 数量差 × 売価 です。売価に当年と前年の平均売価を用います。[*1]

 

M売価要因前年:=SUMX(
VALUES('製品'[製品]), // CROSSJOIN(VALUES('製品'[製品]), VALUES('顧客'[顧客])),
([M売価] - [M売価前年]) * ([M数量] + [M数量前年]) / 2
)


売上売価偏差 = 売価差 × 数量 です。数量に当年と前年の平均数量を用います。[*1]

 

[*1] : この計算は1グループ毎に行う必要があります。なので、複数の分類を含むグループの場合は、CROSSJOINを用いてグループのリストを作成してください。例えば、顧客と製品でグループ化する場合は、CROSSJOIN(VALUES('製品'[製品]), VALUES('顧客'[顧客])) とします。

8. ピボットテーブルの設定

1) 対予偏差

  1. 次のように項目を設定します。
  • 列 : '日付'[年度]、'日付'[四半期.]、'日付'[月]
  • 行 : Σ値、'製品'[製品]
  • Σ値 : [M売上]、[M利益]、[M利益差予]、[M利益差数量要因予]、[M利益差売価要因予]、[M利益差原価要因予]
  1. ピボットテーブルの[年度]の[▼]で26年度以外のチェックを外す。
  2. [ピボットテーブル分析]-[オプション]-[レイアウトと書式]で"更新時に列幅を調整する"のチェックを外す。
  3. [ピボットテーブル分析]-[オプション]-[表示]で"データのないアイテムを行/列に表示する"のチェックを外す。(これをチェックする場合は、フィルターやスライサーで表示しないディメンジョン項目の選択を解除する。)
  4. [デザイン]-[レポートのレイアウト]-[表形式で表示]。
  5. [デザイン]-[総計v]-[列のみ集計を行う]。
  6. 小計を表示する列項目を右クリックし、["xxx"の小計]をクリックして✓マークを付ける。
  7. [デザイン]-[ピボットテーブルスタイル]でベースとするスタイルを右クリックし[複製]。ユーザー設定のスタイルを右クリックし[変更]でスタイルを変更。
  • 小計列 1 : 右罫線(太); 網掛け
  • 小計列 2 : 右罫線(細); 網掛け
  • 列小見出し 1 : 上罫線(太)
  • 列小見出し 2 : 上罫線(細)

2) 対前年偏差

  1. 対予偏差のピボットテーブルをコピーします。
  2. 次のように項目を設定します。
  • 列 : '日付'[年度]、'日付'[四半期.]、'日付'[月]
  • 行 : Σ値、'製品'[製品]
  • Σ値 : [M売上]、[M売上前年]、[M売上偏差前年]、[M数量要因前年]、[M売価要因前年]

参考:ピボットテーブルの設定は、データモデルに影響しないので、ピボットテーブルをコピーして別の設定をすることが可能です。一方テーブルは、データモデルそのものなので、コピーして別の設定にすることができません。

9. サンプル

ダウンロード - 260721diff.zip

C:\Users\Public\Documents\260721diff に展開した各ファイルを置いてください。

10. 参考

|

« Wi-Fi 7 Router Aterm 19000T12BE | トップページ | 数量と単価の表が別で、単価表は改定日のみの場合のExcelによる売上金額計算 »

コメント

コメントを書く



(ウェブ上には掲載しません)




« Wi-Fi 7 Router Aterm 19000T12BE | トップページ | 数量と単価の表が別で、単価表は改定日のみの場合のExcelによる売上金額計算 »