【Excel】ピボットグラフに総計の折れ線を重ねる方法

【Excel】ピボットグラフに総計の折れ線を重ねる方法のアイキャッチ画像 実務設計

部署別・月別の売上を、積み上げ縦棒のピボットグラフにします。棒の高さは月ごとの総計と同じですが、その数字はグラフのどこにも出てきません。総計を折れ線で重ねて、数字で読めるようにしたい。ピボットグラフを一段使いこなすときに出てくる場面です。

この記事では、集計アイテムで総計の系列を作り、折れ線に変える方法を書きます。作るだけでなく、フィルターや部署の追加で折れ線が棒に戻る条件も、手元のExcelで確かめました。

ピボットグラフには総計の系列がない

サンプルは、架空の3部署(営業・開発・総務)の1〜6月の売上です。ピボットテーブルは、行に月、列に部署、値に売上の合計を置き、ピボットグラフは積み上げ縦棒にしました。

部署別・月別の売上のピボットテーブルと、総計の線がない積み上げ縦棒のピボットグラフ

ピボットテーブルには「総計」の列と行があります。ところがグラフの系列は、部署の3本だけです。マクロで系列を数えても3本で、総計は系列になりません。折れ線の元になる値がないので、総計と同じ値の系列を自分で作ります。

なお、重ねる総計は「個別の値を足した合計」です。率のように足せない値は、合計どうしの比で出す必要があります。その考え方は【Excel関数】率の平均はAVERAGEでなくSUM/SUMにまとめています。

集計アイテムで「合計線」を作る

ソースの表を触らずに作れるのが、集計アイテムです。手順は次のとおりです。

  1. ピボットテーブルの「営業」など、部署の見出しのセルを選ぶ
  2. リボンの「フィールド/アイテム/セット」から「集計アイテム」を開く
  3. 名前を「合計線」、数式を ='営業'+'開発'+'総務' にして、OKを押す
「部署への集計アイテムの挿入」画面。名前に合計線、数式に営業・開発・総務の合計を入れたところ

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

合計線の列が加わり、右端の総計が3424と二重になったピボットテーブルと、合計線が棒として積み上がったグラフ

右端の「総計」にも注意してください。合計線を含めて足されるので、本来の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・他のバージョンは未検証

コメント

タイトルとURLをコピーしました