Windows 10 & Office 2016 Excel 2016

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

投稿日:2018年1月15日 更新日:

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

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

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

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

棒グラフと折れ線グラフの組み合わせグラフ

【折れ線グラフ】

折れ線グラフ

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

横棒グラフに平均値を追加するには

横棒グラフに平均値のラインを表示しようとすると、少し工夫しないと作成できません。 折れ線グラフは、縦方向には作成できないので、代わりに散布図 ...

続きを見る

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

目標値となる線を追加した縦棒グラフを作成(図形と折れ線/散布図)

Excelで縦棒グラフを作成し、それに目標値となる線を追加する方法です。 平均値のように元データの値の変化に伴って変化するような値ではなく、 ...

続きを見る

データの確認

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

グラフのデータ

縦棒グラフの作成

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

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

2-D縦棒から

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

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

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

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

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

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

集合縦棒

この種類のグラフの使用目的:
・いくつかの項目の値を比較します。

使用ケース:
・項目の順序が重要でない場合に使います。

集合縦棒

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

集合縦棒グラフの挿入

おすすめグラフから

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

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

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

おすすめグラフ

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

おすすめグラフに複合グラフを表示する条件と項目軸(横軸)の設定

Excel2013のおすすめグラフはとても便利です。 [おすすめグラフ]ボタンを使用すると、選択したデータに適した数種類のグラフが提示されま ...

続きを見る

複合グラフの作成は[おすすめグラフ]か[複合グラフの挿入]から

Excel2013から追加された機能に、「おすすめグラフ」というのがあります。 集合縦棒グラフと折れ線グラフの複合グラフも数回のクリックで作 ...

続きを見る

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

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

セル[C3]に[平均]と入力します。

平均列の追加

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

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

フィルタオプションをドラッグ

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

オートフィルオプションの書式のみコピーをクリック

下のようになります。

書式のみをコピーした後のセル

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

桁区切りスタイル

桁区切り記号を付けて書式を設定します。

桁区切りスタイル

数式を求める[AVERAGE]関数を入力

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

平均をクリック

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

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

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

範囲選択

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

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

[Enter]キーを押して確定します。

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

相対参照と絶対参照と複合参照は[ F4 ]キーで切り替え

セルの参照方法は、相対参照、絶対参照、複合参照があります。 絶対参照と複合参照では、[$]を挿入する必要があります。 このとき、直接入力して ...

続きを見る

絶対参照を付加

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

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

ここでは、セル[C9]までですね。

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

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

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

平均値

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

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

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

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

データ範囲の拡大

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

グラフのデータを拡大後

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

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

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

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

系列グラフの種類の変更

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

[平均]の▼ボタンをクリックします。

[グラフの種類の変更]ダイアログボックス

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

折れ線グラフを選択

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

折れ線グラフを選択した状態の[グラフの種類の変更]ダイアログボックス

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

変更後のグラフ

レイアウトの変更

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

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

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

レイアウトの変更

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

グラフレイアウトの変更後

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

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

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

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

グラフタイトルの変更

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

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

グラフタイトルの変更後

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

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

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

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

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

平均の折れ線で右クリック-[近似曲線の追加]

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

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

[近似曲線の書式設定]作業ウィンドウの[近似曲線のオプション]

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

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

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

[近似曲線の書式設定]の[塗りつぶしと線]-[幅]と[実線/点線]

凡例を表示している場合は、凡例の[線形(平均)]を削除します。

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

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

凡例を削除

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

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

折れ線をプロットエリア幅に変更

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

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

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

散布図を使った複合グラフ

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

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

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

列のショートカットメニュー[非表示]

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

平均の折れ線グラフが消えた棒グラフ

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

[グラフのデザイン]タブの[データ]グループの[データの選択]

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

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

[データソースの選択]ダイアログボックスの[非表示および空白セル]

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

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

折れ線グラフが表示された複合グラフ

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

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

折れ線グラフの背景を一定間隔ごとに縦に色分けしたグラフの作成

折れ線グラフの背景を一定間隔ごとに縦に色分けする方法です。 折れ線グラフと縦棒グラフの複合グラフを使うと、縦棒グラフを折れ線グラフの背景とみ ...

続きを見る

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

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

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

売上の系列で右クリック

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

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

[グラフの種類の変更]ダイアログボックス

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

折れ線グラフ

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

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

平均値の折れ線をプロットエリア幅に変更したグラフ

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

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

C列を非表示にした折れ線グラフ

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

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

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

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

デザインタブのデータの選択

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

[平均]を選択して、[▲](上へ移動)ボタンをクリックします。

[OK]ボタンをクリックして閉じます。

データソースの選択

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

変更後のグラフ 平均値が売上の背景に配置されたグラフ

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

データ系列の書式変更

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

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

ショートカットメニューから[データ系列の書式設定]をクリック

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

[データ系列の書式設定]から色の変更

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

設定変更後のグラフ

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

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

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

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

おすすめグラフ

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

折れ線グラフに縦の目盛線や降下線を表示する

Excel2016で折れ線グラフを作成したとき、折れ線グラフと横軸の目盛の対応をはっきりさせるために、下のように縦の目盛線を表示したい場合の ...

続きを見る

折れ線グラフに系列名をつける

Excelで下のような折れ線グラフを作成しました。 凡例に系列名が表示されていますが、 下のように各折れ線グラフの横に系列名を表示した方が分 ...

続きを見る

折れ線グラフを途切れさせない(データ要素を線で結ぶ)

Excel2013でデータに空白セルがあっても、折れ線グラフを作成した時に折れ線を途切れさせないようにする機能を紹介します。 たとえば、下の ...

続きを見る

体重推移表と体重推移グラフを作ろう(朝と夜計測して平均を求める)

ダイエットのためには、毎日体重計に乗ることが一番なのだそうです。 健康のためにも自分の体重の推移は記録して意識しておいたほうがいいですね。 ...

続きを見る

折れ線グラフの種類と積み上げ折れ線グラフについて

Excelには、多くのグラフの種類があり、用途や目的に合わせてグラフを選択できます。 以下は、Microsoftのページです。 利用可能なグ ...

続きを見る

近似曲線を追加して来月の売上を予測(グラフの数式を利用して予測値も分かる)

作成した折れ線グラフに[近似曲線]を追加して、データの傾向を可視化することができます。 また、来月、再来月などの未来の売上の予測もできます。 ...

続きを見る

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

予測シートはデータから先の傾向を予測してグラフを表示

Excel2016の[データ]タブには、[予測シート]という機能があります。 Excel2016からの新機能です。 データを基に先の予測をグ ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

関連記事30件

Profile

-Windows 10 & Office 2016 Excel 2016

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.