列指向形式データで前年同月対比を算出する方法 (エクセルピボット編)

公開日 2021年7月20日最終更新日 2021年7月20日

関数も演算子も不要、クリックすれば前年同月対比・前年差・差の比率を算出できます。

この投稿は、無料販売データ「商業動態統計調査_店舗数_月次 1997年4月-2021年5月 (列指向形式)」を使用して説明しています。

　前年同月対比を算出したいとき、データ表がクロス集計形式ならセルの計算式で算出できることはすぐにわかります。

・データを日付順、業種順に並び替えます。
・画像の場合は「C13/C2」の計算式をD列へ入れる
・並び替えがくずれると計算結果の値が変わるので、計算結果をコピーして値貼り付けする。

　前月対比も計算したいのでE列へ「C3/C2」の計算式を・・・

　年間ごとの合計前年対比を計算したいので、13行と14行のあいだへ行を挿入、縦計して・・・

　このような手順は必要ありません！

　前年同月対比の計算なので、軸へ「日付の年」と「日付の月」を設定します。
・値は「店舗数」です。

　解説のため「日付の年」を2019年～2021年、「」日付の月」を1月～5月、「業種」をドラッグストアにフィルターしています。

・「店舗数」を値へ追加します。
　表示が「店舗数2」になります。ピボットテーブルのB列、C列は同じ値です。

・「店舗数2」の右側の▼ (三角) をクリック
・「値フィールドの設定」を選択します。

・開いた窓の「計算の種類」タブ
・「計算なし」の表記の右側のドロップダウンマークをクリックします。

・ドロップダウンのなかの「基準値に対する比率」を選択します。

・「基準フィールド」から「日付の年」
・「基準アイテム」から「(前の値)」を選択してOKです。

　ピボットテーブルのC列の値が前年同月対比になります。「B11/B5」の値が「C11」になっていれば成功です。

・軸から「日付の月」を外します。「B5/B4」の値が「C5」になっていれば成功です。

　値が店舗数なので年間合計表示すると実際とはかけ離れた店舗数になります。売上高なら合計でよいのですが、店舗数を平均へ変更します。
・「店舗数」の▼ (三角) をクリック
・「値フィールドの設定」を選択します。

・「集計方法」タブ
・「平均」を選択してOKです。

　店舗数が平均値になりました。

「基準フィールド」「基準アイテム」で選択する項目が対比計算の分母になります。

・「基準フィールド」＝「日付の年」、「基準アイテム」＝「2019」のときの計算は
　2020年1月が分子、2019年1月が分母
　2020年2月が分子、2019年2月が分母
　2021年2月が分子、2019年2月が分母
　常に2019年の月を分母にした計算になります。

・「基準フィールド」＝「日付の月」、「基準アイテム」＝「(前の値)」のときの計算は
　前月対比になります。

　「基準フィールド」と「基準アイテム」の組み合わせで分母を設定してください。

計算の種類から選択できます。

・「基準値との差分」は、「値 – 基準値」
・「基準値との差分の比率」は、「値 / 基準値 – 1」です。