部署別・月別の売上を、積み上げ縦棒のピボットグラフにします。棒の高さは月ごとの総計と同じですが、その数字はグラフのどこにも出てきません。総計を折れ線で重ねて、数字で読めるようにしたい。ピボットグラフを一段使いこなすときに出てくる場面です。
この記事では、集計アイテムで総計の系列を作り、折れ線に変える方法を書きます。作るだけでなく、フィルターや部署の追加で折れ線が棒に戻る条件も、手元のExcelで確かめました。
ピボットグラフには総計の系列がない
サンプルは、架空の3部署(営業・開発・総務)の1〜6月の売上です。ピボットテーブルは、行に月、列に部署、値に売上の合計を置き、ピボットグラフは積み上げ縦棒にしました。

ピボットテーブルには「総計」の列と行があります。ところがグラフの系列は、部署の3本だけです。マクロで系列を数えても3本で、総計は系列になりません。折れ線の元になる値がないので、総計と同じ値の系列を自分で作ります。
なお、重ねる総計は「個別の値を足した合計」です。率のように足せない値は、合計どうしの比で出す必要があります。その考え方は【Excel関数】率の平均はAVERAGEでなくSUM/SUMにまとめています。
集計アイテムで「合計線」を作る
ソースの表を触らずに作れるのが、集計アイテムです。手順は次のとおりです。
- ピボットテーブルの「営業」など、部署の見出しのセルを選ぶ
- リボンの「フィールド/アイテム/セット」から「集計アイテム」を開く
- 名前を「合計線」、数式を
='営業'+'開発'+'総務'にして、OKを押す

ピボットテーブルに「合計線」の列ができ、グラフにも系列が1本増えます。この時点では、合計線は棒として積み上がっています。

右端の「総計」にも注意してください。合計線を含めて足されるので、本来の1,712が3,424になります。グラフ用のピボットでは、総計の行と列を非表示にしておくと安全です。
続いて、グラフを右クリックして「グラフの種類の変更」を開きます。左の「組み合わせ」を選び、「合計線」だけを「折れ線」にします。


合計線の値は 240、270、285、282、303、332 で、棒の高さと一致しました。ほかの系列は積み上げ縦棒のままです。
ほかの作り方との比較
実機で、ほかの3つの方法も試しました。
| 方法 | ソースの表 | 部署が増えたとき |
|---|---|---|
| 集計アイテム(上の方法) | 変えない | 式に入っていないので、合計から漏れる |
| ソースに「総計」の行を足す | 総計行が混ざる | 総計行を更新しないと、折れ線が棒からずれる |
| 補助列(月合計)の平均を2つ目の値にする | 補助列を足す | 部署が増えても値は追従する |
| ピボットから離れた範囲でグラフを作る | 変えない | 範囲を伸ばさないと反映されない |
- 集計アイテムは、部署(経理)を足して更新すると、棒の高さが 250、280…、折れ線が 240、270… とずれました。式に入れていない部署は、合計線に入りません。
- ソースに総計行を足す方法も、同じようにずれました。ピボットテーブルの総計も二重になります(1,712が3,424)。ソースに集計結果を混ぜると、ほかのピボットでも二重に数えてしまいます。行が意図せず増えて数字が合わなくなる仕組みは、【Power Query】結合で行が増える原因と重複の検知方法でも扱っています。
- 補助列の方法では、ソースの表に月合計の列(
=SUMIFS(C:C,A:A,A2))を足し、その平均を2つ目の値にします。系列が部署ごとに増え、同じ値の折れ線が3本重なります。値は、部署を足しても月の総計に追従しました。 - ピボットから離れた範囲のグラフは、ピボットグラフではなくなります。系列ごとに種類を変えるのは自由ですが、GETPIVOTDATAで値を取ると、部署を非表示にした時点で #REF! になりました。ピボットが7か月に増えても、グラフは6か月のままでした。
どの方法にも弱点があります。ソースを汚さず、手順も短いのは集計アイテムです。この記事では、集計アイテムを軸に進めます。
変えられる種類と、変えられない種類
ピボットグラフの1つの系列だけを変えられる種類は、折れ線・マーカー付き折れ線・面・積み上げ横棒などでした。散布図・バブル・株価は設定できず、エラーになりました(マクロで確認)。縦棒(集合)に変えると、全系列が変わります。これは通常のグラフと同じです。
ピボットグラフそのものの作り方は、Microsoftの公式ページCreate a PivotChart(英語)にあります。
折れ線が棒に戻る条件
実用上、いちばん困るのがここです。折れ線にしたあとの操作で、種類がどうなるかを調べました。
| 操作 | 折れ線 |
|---|---|
| 更新(再読み込み) | 残る |
| ソースに月を足して更新 | 残る(点が7つになる) |
| 色・太さ・データラベル | 残る |
| 保存して開き直す | 残る(補助列の版と集計アイテムの版で確認) |
| 部署を1つ非表示にする | 棒に戻る |
| 非表示を解除する | 戻ったまま |
| 行と列を入れ替える | 棒に戻る |
| 部署を足して更新する | 棒に戻る |
ピボットグラフは、系列の顔ぶれが変わると、系列ごとの種類がまとめて積み上げ縦棒に戻りました。月の追加のように、系列が変わらない更新では残ります。
画面で「総務」のチェックを外すと、次のとおり折れ線が棒に戻りました。合計線の値は、非表示にした総務を含んだままの 240 です。

戻ったら、設定し直せば折れ線に戻ります。マクロにしておくと、1回の実行で済みます。
Sub ReapplyLine(ByVal ws As Worksheet, ByVal seriesName As String)
Dim co As ChartObject, s As Series
For Each co In ws.ChartObjects
For Each s In co.Chart.SeriesCollection
If s.Name = seriesName Then s.ChartType = xlLine
Next s
Next co
End Sub
呼び出しは ReapplyLine ActiveSheet, "合計線" です。
まとめ
- ピボットグラフに総計の系列はないので、同じ値の系列を作って折れ線にする
- ソースを変えずに作れるのは集計アイテム。部署が増えたときは式を直す
- フィルター・行と列の入れ替え・部署の追加で、折れ線は棒に戻る。設定し直しが必要
- 確認した環境はMicrosoft 365(Excel 16.0、日本語)。Excel 2021未満・Mac・他のバージョンは未検証


コメント