Zチャートの作成 with Excel

2023/7/10

イントロダクション

Zチャートは，任意のスパンの時系列データを，実績・その累積・移動合計の3つの視点から俯瞰するためのグラフです。3つの情報を一枚のグラフで追えるだけでなく，名前のとおりアルファベットの「Z」様(よう)に描かれる複合折れ線がなした形状から，長短の包括的な動向を直感的に判断できるといった利点があります。また，これはより発展的な季節調整をおこなう際のプリアプローチとしての役目にも優れるかと思います。

ここで想定する金額あるいは数量といったものに関する分析のシーンでは，何より“サイクル”を意識することが肝要と言えるでしょう。具体的には，12ヵ月，4四半期，30日…など，動きを見たい対象の“サイクル”に合わせてスパンを適切に切っていきます。

もっとも，一般によく見られるのは，月別に12ヵ月(1年)のスパンで切ったチャートでしょうか。この場合，移動合計(12ヵ月サイクルの場合は，すなわち「移動年計」)を計算する必要から24ヵ月分(2年)のデータが要求されます。またこのアウトプットから読み取ることのできる情報は「直近12カ月の月別の実績」「直近12カ月の月別の実績の累計」「移動年計」の3つの値およびそれらの推移となります。とりわけ，移動合計の指標は，単月の実績に含まれるいくつかの変動要因を吸収した“すなおな”推移を手軽に理解するのに役立ちます~~(このあたりのロジックについてはストーリーパートでふれています)~~。

以下，ExcelによるZチャートの作り方です。ここでは一連の手続きをサブスクリプション版Excel(ver.2006)で追っています。一部ボタンの配置や名称などが異なる箇所がありますが，手続きそのものは「永続ライセンス版」にいうところのExcel 2019, 2016, 2013, 2010も基本的に同じです。

元データ

元のデータです。

ある販売担当部員の2年間の売上を，月別に集計した表です。

値を積み上げる

あたらしく見出しを配置しておきます。売上累計と移動年計です。

前者は直近12ヵ月の傾向を知るための材料です。また後者は，(2サイクルにわたる)より長期の傾向を窺うために利用します。

直近のデータ(最下行「6月」)から1年さかのぼり，7月の「売上累計」列に，左の隣接セル(「売上」列)の値をそのまま参照する式を入力します。

CELL C14=B14

Excelについてある程度の練度にあれば =SUM($C$14:C14) を最下行までコピーした方がスマートです。この場合，が省けます。

上で入力したセルの直下に，

直前の売上累計の値+当該月の売上の値

となる式を入力します。そして，これを同列の最下行までコピーします。

CELL C15=C14+B15

移動合計を求める

直近のデータ(最下行「6月」)から1年さかのぼった7月の「移動年計」列に，さらにその月よりさかのぼること12ヵ月分(当該月も含む)の「売上」の合計をSum関数で求めます。

したがって，合計する範囲は「売上」列のブラウンの囲み部分です(「後方移動合計」)。

CELL D14=SUM(B3:B14)

上で入力した式を，最下行までコピーします。

「線グラフ」で可視化する

表をもとにグラフをつくります。

左端の「月」列について，1月, 2月，と文字列で用意されている場合は気に留めなくてもOKですが，この例では，月は数字で用意されています。このままグラフを描こうとすると，Excelは月データもY軸にプロットすべき系列と判断してしまうので，それを避けるために見出しの文字列「月」を削除して見出し部分を空欄にしておきます。

これによりカテゴリとして認識されます。表的に見出しが必要ならグラフを挿入した後で再度入力します。

その点を確認のうえ，下図のように，見出しと直近の12カ月分の各データを選択します。