縦棒/折れ線グラフに平均値を追加するには（データは非表示にできる）

Excelで支店別の売上を棒グラフや折れ線グラフで作成した後に、平均値のラインやノルマ、または目標値を追加したいなと思われることもあると思います。

その場合の方法を解説します。

平均の折れ線をプロットエリア幅に引き延ばす方法と、平均値のデータを非表示にしてもグラフは消えないようにする方法も紹介します。

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

【折れ線グラフ】

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

目標値のような決まった数値であれば、図形を使って追加してもいいと思います。

データの確認

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

縦棒グラフの作成

上記のデータをもとに縦棒グラフを作成します。セル［A3］からセル［B9］までを範囲選択します。

注意項目名を含めて選択します。

2-D縦棒から

［挿入］タブの［グラフ］グループにある［縦棒/横棒グラフの挿入］をクリックして、［集合縦棒］をクリックします。

メニューの中のグラフをポイントするだけで、グラフを確認することができます。

リアルタイムプレビューという機能です。クリックして確定する前に結果のグラフを確認できます。

確定前に結果を確認できるため、繰り返し設定しなおす手間を省くことができます。

集合縦棒のポップヒントには以下のように記されています。

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

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

おすすめグラフから

または、［挿入］タブの［グラフ］グループにある［おすすめグラフ］をクリックします。

［グラフの挿入］ダイアログボックスが表示されますので、［集合縦棒］をクリックします。

［おすすめグラフ］は、Excel2013からの機能です。

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

平均値を求める列を縦棒グラフのデータに追加

データリストに平均値の列を追加します。

セル［C3］に［平均］と入力します。

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

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

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

下のようになります。

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

数式を求める［AVERAGE］関数を入力

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

下のように［AVERAGE］関数が挿入されます。

売上値の範囲を選択します。

ここではセル［B4］からセル［B9］までを選択します。

そして、絶対参照を付けるために［F4］キーを押します。

範囲選択した後、［F4］キーをポンと押すと、下の数式バーのように［$］マークが挿入されます。（数式を下へドラッグしてコピーしても参照範囲が変わらないようにするためです。）

［Enter］キーを押して確定します。

絶対参照については、以下の記事で解説しています。

求めた平均値を下のセルへコピーします。

数式を入力したセル［C4］を選択すると、セルの右下に■が現れます。その■にマウスポインタを合わせると、＋になるので、その時に下へドラッグします。

ここでは、セル［C9］までですね。

または、フィルハンドルが＋の時にダブルクリックします。ダブルクリックすると、隣の列と同じ行までコピーされます。

注意隣の列に空白行があると、空白行の上の行までとなります。

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

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

縦棒グラフにデータを追加

グラフを選択すると、データが選択されます。

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

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

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

追加した平均値の棒グラフを折れ線グラフに変更します。

平均値の棒グラフを選択して、右クリックします。

ショートカットメニューから［系列グラフの種類の変更］をクリックします。

［グラフの種類の変更］ダイアログボックスが表示されます。

［平均］の▼ボタンをクリックします。

グラフの種類の中から［折れ線］をクリックします。

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

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

レイアウトの変更

レイアウトを変更する場合は、グラフを選択した状態で［デザイン］タブの右端にある［グラフのレイアウト］グループにある［クイックレイアウト］をクリックします。

ここもリアルタイムプレビューの機能があるので、各レイアウトをポイントするだけで結果を確認することができます。

ここでは、［レイアウト3］を選択します。

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

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

グラフタイトルは、セル［A1］の文字列をリンクしたいと思います。

グラフタイトルのテキストボックスを選択します。

数式バーにカーソルをおいて、［=］を入力した後にセル［A1］をクリックします。

［Enter］キーで確定すると、下のようにグラフタイトルを変更できます。

もちろん、グラフタイトルは直接入力しても構いません。

参考ここでは、グラフタイトルは、すでに入力したものがあったのでセルをリンクする方法を紹介していますが、グラフタイトルは直接入力しても構いません。

また、Office2016から追加された新グラフでは、タイトルのセル参照はできません。

平均値の折れ線を横へ長くするには（プロットエリア幅に変更）

平均値の折れ線グラフをプロットエリアいっぱいに引き延ばしたい場合の方法です。

折れ線グラフを選択して、右クリックします。ショートカットメニューから［近似曲線の追加］をクリックします。

［近似曲線の書式設定］作業ウィンドウが表示されますので、［近似曲線のオプション］をクリックします。

［予測］の［前方補外］と［後方補外］に［0.5］と入力します。

今度は、［塗りつぶしと線］をクリックします。［線］の［幅］を［2.25pt］にします。折れ線の幅に合わせます。

［実線/点線］の▼ボタンをクリックして、［実線］を選択します。

これでプロットエリアの幅いっぱいに折れ線を長くできます。

凡例を表示している場合は、凡例の［線形（平均）］を削除します。

凡例の箇所を1回クリックすると、全体が選択されますが、その後、削除したい［線形（平均）］を選択します。

右クリックしてショートカットメニューから［削除］をクリックします。

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

売上データを変更しても、平均値も自動で変更されますし、折れ線も自動的に変更されます。

参考散布図を使って、横線を表示することもできます。以下の記事では、ここの記事と同じデータを使ってますので分かりやすいと思います。

近似曲線を使用せずに、プロットエリア幅に表示できます。平均ではなく［目標値］を表示する内容ですが、平均値を入力して作成できます。

散布図を使って横線を表示する方法

平均の列を非表示にしてもグラフは消えないようにするには

平均の折れ線グラフは追加したけど、データは非表示にしたいということもあると思います。簡単に設定できます。

C列で右クリックしてショートカットメニューから［非表示］をクリックします。

C列を非表示にすると、グラフも消えてしまいます。でも、大丈夫です。

グラフを選択して、［グラフのデザイン］タブの［データ］グループの［データの選択］をクリックします。

［データソースの選択］ダイアログボックスが表示されます

［非表示および空白セル］ボタンをクリックします。

［非表示および空白のセルの設定］ダイアログボックスが表示されますので、［非表示の行と列のデータを表示する］のチェックボックスをオンにして［OK］ボタンをクリックします。

これで、折れ線グラフは表示されるようになります。凡例も表示されます。

参考非表示にしなくても平均値の列の白文字にして見えないようにしてもいいと思います。

以下の記事でも、非表示データを表示する設定を解説しています。折れ線と集合縦棒の複合グラフで折れ線の背景を色分けしています。

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

もし、上記のような縦棒グラフと折れ線グラフの複合グラフ（組み合わせグラフ）ではなく、折れ線グラフのみのグラフにする場合は、売上の縦棒グラフを選択して右クリックします。

ショートカットメニューから［系列グラフの種類の変更］をクリックします。

［グラフの種類の変更］ダイアログボックスが表示されるので、［売上］の［集合縦棒］を［折れ線］に変更します。

［売上］の▼ボタンをクリックして、グラフの種類を折れ線に変更します。

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

参考平均値の折れ線をプロットエリアの幅と同じにしたいのであれば、縦棒グラフで解説した方法（近似曲線で設定）することができます。

近似曲線に変更した場合は、重なりを変更することはできません。

折れ線グラフでも平均のデータ列を非表示にしても、グラフに表示させることができます。

平均の列を非表示にしてもグラフは消えないようにするには

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

［平均値］の折れ線を［売上］の背景へ移動させる場合の方法です。

今は、［平均値］の折れ線が［売上］の折れ線の手前にあるので、それを逆にします。

グラフエリアを選択した状態で［デザイン］タブの［データ］グループにある［データの選択］をクリックします。

［データソースの選択］ダイアログボックスが表示されます。

［平均］を選択して、［▲］（上へ移動）ボタンをクリックします。

［OK］ボタンをクリックして閉じます。

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

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

データ系列の書式変更

データ系列の色を変更したい場合は、データ系列を選択して、ショートカットメニューから［データ系列の書式設定］をクリックします。

データ系列をダブルクリックしても［データ系列の書式設定］を表示することができます。

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

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

参考［データ系列の書式設定］作業ウィンドウを表示する方法は、他にもあります。

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

もし、売上グラフを作成する前で、平均値を求めた列を追加した後にグラフを新規に作成するのであれば、データ範囲を選択して［挿入］タブの［グラフ］グループにある［おすすめグラフ］をクリックします。

［グラフの挿入］ダイアログボックスが表示されますので、サンプルの中に目的のグラフがあれば、それをクリックして作成すると効率的です。

おすすめグラフは、Excel2013からの機能です。

関連以下は、折れ線グラフに関する記事です。

Excel2016には、予測シートという便利な機能があります。