この記事では、ExcelでCAGR（年平均成長率）の計算方法とグラフへの表示のさせ方を解説します。CAGRの計算ではデータを追加すれば自動的に更新されるようにする方法も紹介します。
CAGR（年平均成長率）の計算方法
CAGR（年平均成長率）は以下の計算式で計算することができます。
CAGR = (最終年度の数値 / 開始年度の数値)^(1 / 年数) – 1
期間が決まっている場合
たとえば、このような5年間の売上データを計算します。年数は数式で計算できるように、年は数値データとして入力します。
CAGRを表示させるセルには以下のように数式を入力します。
=(B6/B2)^(1/(A6-A2+1))-1
「B6/B2」は「2024年の売上/2020年の売上」を意味します。「A6-A2+1」は2024年から2020年までの年数5年を計算しています。
これでCAGRを計算することができます。
「Ctrl+Shift+5」を押せば、パーセンテージ表示になります。
小数点以下の桁数は、ホームタブの「数値」ツールから増やしたり、減らしたりすることができます。
データを追加すると自動的に更新される方法
使用頻度は少ないかもしれませんが、データを追加しても自動的にCAGRの数値を更新させる方法もあります。期間が定まっていない場合などに活用することができます。
ここではINDEX関数を使用して「最終年度の数値」「開始年度の数値」を取得します。
| INDEX(配列,行番号,[列番号]) |
|---|
| 範囲内のセルと行または列を指定してその値を取得する。 |
CAGRを表示させるセルには以下のように数式を入力します。
=(INDEX(B:B,COUNTA(B:B))/INDEX(B:B,2))^(1/(COUNTA(A:A)-1))-1
| 数式 | 役割 |
|---|---|
| INDEX(B:B,COUNTA(B:B)) | COUNTA関数でB列のデータが入力されているセルの数を数え、最終年度の売上のセルを参照する。 |
| INDEX(B:B,2) | B列の2行目の開始年度の売上のセルを参照する。 |
| COUNTA(A:A)-1 | A列のデータが入力されているセルの数を数え、見出しの1行目を引くことで、年数を算出する。 |
これで上記で計算したものと同じ結果が表示されます。
データを追加すれば、自動的にCAGRの数値も更新されます。
グラフに表示させる方法
CAGRは棒グラフと併せて示すことで、成長性などを示すことができます。表示のさせ方は様々ありますが、ここでは自由度の高いテキストボックスを使って表示させる方法を紹介します。
データを選択し、縦棒グラフを選択します。
以下のように売上高のグラフを作成します。
まずは売上の成長を示す矢印を追加します。グラフを選択した状態で、書式タブから図形の挿入で「矢印」アイコンをクリックします。
ドラッグしてグラフ上に矢印を挿入します。グラフを選択しながら図形を挿入することで、グラフを移動させれば、図形も一緒に移動させることができます。
矢印を色や太さなどを任意に設定します。
次に矢印の上にCAGRの値を表示させます。同じようにグラフを選択した状態でテキストボックスを選択します。
矢印の上にテキストボックスを挿入します。
次にこのテキストボックスにCAGRの値を表示させます。ここでは直接入力するのではなく、CAGRを表示させているセルを参照して表示させます。テキストボックスを選択した状態で、F2キーを押すと数式バーが入力状態になります。
CAGRが表示されているセルを
そうすると、テキストボックスにCAGRが表示されます。
文字の色や大きさを調整します。
セル参照を使用しているので、データを更新すれば自動的にグラフ上のCAGRの値も更新されます。
コメント