縦棒グラフや折れ線グラフに平均値を追加するには

Excelで支店別の売上を棒グラフや折れ線グラフで作成した後に、平均値のラインを追加したいなと思われることもあると思います。その場合の方法を解説します。

【縦棒グラフと折れ線グラフの複合グラフ（組み合わせグラフ）】

【折れ線グラフ】

関連横棒に平均値を表示する方法については、以下の記事で解説しています。

データの確認

グラフを作成するためのデータは下のようになっています。

縦棒グラフの作成

上記のデータを元に縦棒グラフを作成します。セル[A3]からセル[B9]までを範囲選択します。注意項目名を含めて選択します。

2-D縦棒から

[挿入]タブの[グラフ]グループにある[縦棒/横棒グラフの挿入]をクリックして、[集合縦棒]をクリックします。参考メニューの中のグラフをポイントするだけで、グラフを確認することができます。リアルタイムプレビューという機能です。クリックして確定する前に結果のグラフを確認することができます。確定前に結果を確認できるため、繰り返し設定しなおす手間を省くことができます。

集合縦棒のポップヒントには以下のように記されています。ポップヒントには、グラフの使用目的などが記されていますので、グラフの選択に迷ったら、ポップヒントで確認するのもいいと思います。

下のようなグラフが挿入されます。

おすすめグラフから

または、[挿入]タブの[グラフ]グループにある[おすすめグラフ]をクリックします。[グラフの挿入]ダイアログボックスが表示されますので、[集合縦棒]をクリックします。参考[おすすめグラフ]は、Excel2013からの機能です。

おすすめグラフについては、以下の記事が参考になると思います。

平均値を求める列を追加

データリストに平均値の列を追加します。セル[C3]に[平均]と入力します。

隣のセルと同じ書式にするには

左隣りのセルを選択して、セルの右下に現れる■にマウスポインタを合わせて、＋になったときに右へドラッグします。

右下に現れる[オートフィルオプション]をクリックして、メニューの中から[書式のみコピー]をクリックします。

下のようになります。
[売上]と[平均]のセルには、[ホーム]タブの[数値]グループにある[桁区切りスタイル]の設定を事前に行なっています。

数式の入力

セル[C4]を選択して、[ホーム]タブの[編集]グループにある[合計]の▼ボタンをクリックして、メニューの中から[平均]をクリックします。

下のように[AVERAGE]関数が挿入されます。売上値の範囲を選択します。ここではセル[B4]からセル[B9]までを選択します。

そして、絶対参照を付けるために[F4]キーを押します。範囲選択した後、[F4]キーをポンと押すと、下の数式バーのように[$]マークが挿入されます。（数式を下へドラッグしてコピーしても参照範囲が変わらないようにするためです。）
[Enter]キーを押して確定します。参考絶対参照については、以下の記事で解説しています。

求めた平均値を下のセルへコピーします。数式を入力したセル[C4]を選択すると、セルの右下に■が現れます。その■にマウスポインタを合わせると、＋になるので、その時に下へドラッグします。ここでは、セル[C9]までですね。または、フィルハンドルが＋の時にダブルクリックします。ダブルクリックすると、隣の列と同じ行までコピーされます。注意隣の列に空白行があると、空白行の上の行までとなります。

C列に平均値が入力されました。

この平均値を追加したデータから、新しいグラフを作成する場合は、平均値を求めた表を作成後に新しいグラフを作成する場合を参照してください。

グラフにデータを追加

グラフを選択すると、データが選択されます。選択した範囲の右下の■にマウスポインタを合わせるとマウスポインタが↔の形に変わるので、その時に隣のセルへドラッグします。

グラフの対象範囲が拡大されて、平均値の棒グラフが追加されます。

平均値の棒グラフを折れ線グラフに変更

追加した平均値の棒グラフを折れ線グラフに変更します。平均値の棒グラフを選択して、右クリックします。ショートカットメニューから[系列グラフの種類の変更]をクリックします。
[グラフの種類の変更]ダイアログボックスが表示されます。[平均]の▼ボタンをクリックします。

グラフの種類の中から[折れ線]をクリックします。
[平均]には、[折れ線]が選択されていることと、グラフのプレビューも確認して問題がなければ、[OK]ボタンをクリックします。

グラフは下のようになります。

レイアウトの変更

レイアウトを変更する場合は、グラフを選択した状態で[デザイン]タブの右端にある[グラフのレイアウト]グループにある[クイックレイアウト]をクリックします。ここもリアルタイムプレビューの機能があるので、各レイアウトをポイントするだけで結果を確認することができます。ここでは、[レイアウト3]を選択します。

グラフは下のようになります。

グラフタイトルはセル参照で

グラフタイトルは、セル[A1]の文字列をリンクしたいと思います。
グラフタイトルのテキストボックスを選択します。数式バーにカーソルをおいて、[=]を入力した後にセル[A1]をクリックします。
[Enter]キーで確定すると、下のようにグラフタイトルを変更することができます。もちろん、グラフタイトルは直接入力しても構いません。

棒グラフを折れ線グラフに変更

もし、上記のような縦棒グラフと折れ線グラフの複合グラフ（組み合わせグラフ）ではなく、折れ線グラフのみのグラフにする場合は、売上の縦棒グラフを選択して右クリックします。ショートカットメニューから[系列グラフの種類の変更]をクリックします。
[グラフの種類の変更]ダイアログボックスが表示されるので、[売上]の[集合縦棒]を[折れ線]に変更します。[売上]の▼ボタンをクリックして、グラフの種類を折れ線に変更します。

グラフは下のようになります。

折れ線グラフの重なりを変更

[平均値]の折れ線を[売上]の背景へ移動させる場合の方法です。今は、[平均値]の折れ線が[売上]の折れ線の手前にあるので、それを逆にします。
グラフエリアを選択した状態で[デザイン]タブの[データ]グループにある[データの選択]をクリックします。
[データソースの選択]ダイアログボックスが表示されます。[平均]を選択して、[▲]（上へ移動）ボタンをクリックします。[OK]ボタンをクリックして閉じます。

グラフは下のようになります。

参考上記のグラフの重なりの変更方法は、同種類のグラフの場合のみに有効です。

データ系列の書式変更

データ系列の色を変更したい場合は、データ系列を選択して、ショートカットメニューから[データ系列の書式設定]をクリックします。参考データ系列をダブルクリックしても書式設定を表示することができます。

ウィンドウの右側に[データ系列の書式設定]が表示されますので、[線]の[色]から任意の色を選択して変更することができます。

設定変更後、グラフは下のようになります。

平均値を求めた表を作成後に新しいグラフを作成する場合

もし、売上グラフを作成する前で、平均値を求めた列を追加した後にグラフを新規に作成するのであれば、データ範囲を選択して[挿入]タブの[グラフ]グループにある[おすすめグラフ]をクリックします。[グラフの挿入]ダイアログボックスが表示されますので、サンプルの中に目的のグラフがあれば、それをクリックして作成すると効率的です。おすすめグラフは、Excel2013からの機能です。