Excelのピボットグラフで積み上げ棒と総計折れ線を同時表示する方法

ピボットグラフで分類別の棒グラフと合計の折れ線グラフを同時に作ってみます。
通常のグラフであれば簡単にできますが、ピボットグラフでは単純にはできなかったです。

一見するとおかしな作り方ですが、Excelのピボットグラフの構造的な制約を回避するための手段としては、それなりに使われる？方法のようです。
この方法についてとしてまとめています。

最終的にこのようなグラフ

年月で、分類別に積み上げ棒グラフ。
プラスして、年月内で分類しない総計を折れ線グラフとして入れます。

データ

• 以下のようなデータを想定します

処理日	分類	詳細
2026/4/1	AAA	詳細内容
2026/4/5	CCC	詳細内容
2026/5/10	BBB	詳細内容
⋮	⋮	⋮

パワークエリ

1. 複数クエリで同じテーブルを使うのでソース用のクエリを作る

• シート上のテーブル名は「データリスト」

データリストsource

let
    ソース = Excel.CurrentWorkbook(){[Name="データリスト"]}[Content]
in
    ソース

2. 新たなクエリで必要な列のみ残します

データリスト

let
    ソース =  #"データリストsource",
    削除された列 = Table.RemoveColumns(ソース,{"詳細"}),
    変更された型 = Table.TransformColumnTypes(削除された列,{{"分類", type text}, {"処理日", type date}})
in
    変更された型

3. 分類列のみを別クエリで作ります

データ分類

let
    ソース =  #"データリストsource",
    削除された他の列 = Table.SelectColumns(ソース,{"分類"}),
    変更された型 = Table.TransformColumnTypes(削除された他の列,{{"分類", type text}}),
    削除された重複 = Table.Distinct(変更された型)
in
    削除された重複

4. 分類列へ「総計」という行を追加します

データ分類

let
    ソース =  #"データリストsource",
    削除された他の列 = Table.SelectColumns(ソース,{"分類"}),
    変更された型 = Table.TransformColumnTypes(削除された他の列,{{"分類", type text}}),
    削除された重複 = Table.Distinct(変更された型),
    // ↓を追加
    行の追加 = Table.InsertRows(削除された重複, Table.RowCount(削除された重複), {[分類 = "総計"]})
in
    行の追加

リレーションシップ

1. データと分類でリレーションシップを作ります

• 「データ分類」が1側、「データリスト」が多側になります

2. 日付テーブルも作っておきます

• データモデルの管理から日付テーブルを作成
• グラフの横軸を「年月」などの単位で並べるために使います

3. データと日付テーブルでリレーションシップを作ります

日付テーブルも同様にリレーションします。

ダイアグラムビュー

• リレーションはスタースキーマとなるように作ります

この方法の仕組み

この方法のポイントは、「データ分類」にだけファクトデータと紐づかない「総計」行を足しておくところです。

1. 「総計」は「データリスト」側に存在しない値なので、リレーションシップをたどっても対象は常にない
2. そのままだと「総計」の列（系列）は空になってしまう
3. そのため「総計」が選ばれているときだけALL('データ分類')でフィルターを外し、全件をカウントしている

これで「AAA・BBB・CCC」と並んで「総計」という系列が1つ増え、同じ1つのピボットグラフの中に分類別の値と合計値を同居させられる、という流れになります。

DAX

1. 行カウントするDAXを作ります

fxデータ数

=COUNTA('データリスト'[処理日])

2. 総計の場合は分類のフィルターを除外するDAXを作ります

fxデータ総数あり

=IF(
    HASONEVALUE('データ分類'[分類]),
    IF(
        VALUES('データ分類'[分類]) = "総計",
        CALCULATE(
            COUNTA('データリスト'[処理日]),
            ALL('データ分類')
        ),
        [fxデータ数]
    ),
    BLANK()
)

• HASONEVALUEで「分類が1つに絞り込まれているか」を判定し、絞り込まれているときだけVALUES でその値を取り出して分類により計算を分けています

SELECTEDVALUEを使えばHASONEVALUE + VALUESの入れ子はもっと短く書けるのですが、Excel2024のではSELECTEDVALUEが使えなかったため、上記の書き方にしています。

HASONEVALUE が偽のときは BLANK() を返しているので、ピボットテーブル／ピボットグラフ自体の総計行・総計列は空欄になります。
分類ごとの値と「総計」系列の値を二重に足してしまわないためです。

ピボットグラフ

1. ピボットグラフを挿入します

• ピボットテーブルツールの「ピボットグラフ」から積み上げ縦棒を選びます
• 軸（項目）：日付テーブルの「年」「月」など
• 凡例（系列）：データ分類[分類]
• 値：fxデータ総数あり

これで「AAA・BBB・CCC・総計」の4系列が積み上げ縦棒として並びます。

2. 「総計」の系列だけ折れ線に変えます

• グラフ上の「総計」の系列を選択
• 右クリック →「系列グラフの種類の変更」
• 「総計」の行だけ折れ線に変更
• 総計の折れ線は積み上げ棒の一番上にくるので、第２軸は不要

完成

これで、分類ごとの積み上げ棒と総計の折れ線をまとめてグラフにできました。

この方法について

この方法は少し変わった作り方かもしれません。
凡例は分類を「分ける」ためのものなのに、そこへ「まとめる」方向の総計を混ぜているので、素直な使い方ではありません。

なぜこうしたいのか

Excelのピボットグラフは系列 = 凡例フィールド × 値フィールドでしか構成できず、「凡例を無視する系列だけを1本足す」ということが構造的にできません。
そのため、凡例側（＝ディメンション）に総計を追加して持たせる、という回避策になります。

多次元DB（SSAS/MDX）では階層に[All]メンバーがあり、リーフ（AAA/BBB/CCC）と同居して扱えるようです。
DAXにはその仕組みが無いので、行として自前で足している、という位置づけです。

デメリット

• 凡例のメンバーが排他的でなくなる（「部分の和 = 全体」という積み上げ棒の前提が崩れる）
• ピボット自身の総計行・総計列は使えない（前述のとおりBLANK()で潰している）
• 同じ「分類」をスライサーに使うと、実データに無い「総計」が選択肢に出てくる
• 総計という文字列頼りなので、実データに同名の分類が現れると壊れる

ほかの方法

方法	特徴
ピボットテーブルのセル範囲から通常のグラフを作る	Excelでは一番よくある方法。 データモデルを汚さないが、レイアウト変更に追従しない
CUBEVALUE / CUBEMEMBERでシート上に集計範囲を作る	手間はかかるが、モデルを汚さずスライサーも効く
Power BIの複合グラフを使う	「列の系列」と「線の値」を別々に指定できるので、そもそもこの問題が起きない
そもそもやめる	データ集計する意図を考え直す

ピボットグラフのままで完結させたい場合の選択肢が、今回の方法になります。

参考

• Using SELECTEDVALUE with PowerPivot
• Power BI で複合グラフを作成して使用する
• 【Excel】ピボットグラフで総計を表示する【通常のグラフを使おう】
• スター スキーマと Power BI での重要性を理解する