Excelで支店別の売上を棒グラフや折れ線グラフで作成した後に、平均値のラインを追加したいなと思われることもあると思います。
その場合の方法を解説します。
【縦棒グラフと折れ線グラフの複合グラフ(組み合わせグラフ)】
【折れ線グラフ】
関連横棒に平均値を表示する方法については、以下の記事で解説しています。
-
横棒グラフに平均値を追加するには
横棒グラフに平均値のラインを表示しようとすると、少し工夫しないと作成できません。 折れ線グラフは、縦方向には作成できないので、代わりに散布図 ...
データの確認
グラフを作成するためのデータは下のようになっています。
縦棒グラフの作成
上記のデータを元に縦棒グラフを作成します。セル[A3]からセル[B9]までを範囲選択します。
注意項目名を含めて選択します。
2-D縦棒から
[挿入]タブの[グラフ]グループにある[縦棒/横棒グラフの挿入]をクリックして、[集合縦棒]をクリックします。
参考メニューの中のグラフをポイントするだけで、グラフを確認することができます。
リアルタイムプレビューという機能です。クリックして確定する前に結果のグラフを確認できます。
確定前に結果を確認できるため、繰り返し設定しなおす手間を省くことができます。
集合縦棒のポップヒントには以下のように記されています。
ポップヒントには、グラフの使用目的などが記されていますので、グラフの選択に迷ったら、ポップヒントで確認するのもいいと思います。
集合縦棒
この種類のグラフの使用目的:
・いくつかの項目の値を比較します。
使用ケース:
・項目の順序が重要でない場合に使います。
下のようなグラフが挿入されます。
おすすめグラフから
または、[挿入]タブの[グラフ]グループにある[おすすめグラフ]をクリックします。
[グラフの挿入]ダイアログボックスが表示されますので、[集合縦棒]をクリックします。
参考[おすすめグラフ]は、Excel2013からの機能です。
おすすめグラフについては、以下の記事が参考になると思います。
-
おすすめグラフに複合グラフを表示するには
Excel2013のおすすめグラフはとても便利です。 複合グラフの作成ならExcel2013の「おすすめグラフ」が便利 複合グラフですが、あ ...
-
Excel2016のおすすめグラフ(縦棒グラフと折れ線グラフの複合グラフを作成)
Excel2013から追加された機能に、「おすすめグラフ」というのがあります。 集合縦棒グラフと折れ線グラフの複合グラフも数回のクリックで作 ...
平均値を求める列を縦棒グラフのデータに追加
データリストに平均値の列を追加します。
セル[C3]に[平均]と入力します。
隣のセルと同じ書式にするには
左隣りのセルを選択して、セルの右下に現れる■にマウスポインタを合わせて、+になったときに右へドラッグします。
右下に現れる[オートフィルオプション]をクリックして、メニューの中から[書式のみコピー]をクリックします。
下のようになります。
[売上]と[平均]のセルには、[ホーム]タブの[数値]グループにある[桁区切りスタイル]の設定を事前に行なっています。
桁区切りスタイル
桁区切り記号を付けて書式を設定します。
数式を求める[AVERAGE]関数を入力
セル[C4]を選択して、[ホーム]タブの[編集]グループにある[合計]の▼ボタンをクリックして、メニューの中から[平均]をクリックします。
下のように[AVERAGE]関数が挿入されます。
売上値の範囲を選択します。
ここではセル[B4]からセル[B9]までを選択します。
そして、絶対参照を付けるために[F4]キーを押します。
範囲選択した後、[F4]キーをポンと押すと、下の数式バーのように[$]マークが挿入されます。(数式を下へドラッグしてコピーしても参照範囲が変わらないようにするためです。)
[Enter]キーを押して確定します。
参考絶対参照については、以下の記事で解説しています。
-
相対参照と絶対参照と複合参照は【 F4 】キーで切替
セルの参照方法は、相対参照、絶対参照、複合参照があります。 絶対参照と複合参照では、[$]を挿入する必要があります。 このとき、直接入力して ...
求めた平均値を下のセルへコピーします。
数式を入力したセル[C4]を選択すると、セルの右下に■が現れます。その■にマウスポインタを合わせると、+になるので、その時に下へドラッグします。
ここでは、セル[C9]までですね。
または、フィルハンドルが+の時にダブルクリックします。ダブルクリックすると、隣の列と同じ行までコピーされます。
注意隣の列に空白行があると、空白行の上の行までとなります。
C列に平均値が入力されました。
この平均値を追加したデータから、新しいグラフを作成する場合は、平均値を求めた表を作成後に新しいグラフを作成する場合を参照してください。
縦棒グラフにデータを追加
グラフを選択すると、データが選択されます。
選択した範囲の右下の■にマウスポインタを合わせるとマウスポインタが↔の形に変わるので、その時に隣のセルへドラッグします。
グラフの対象範囲が拡大されて、平均値の棒グラフが追加されます。
平均値の棒グラフを折れ線グラフに変更
追加した平均値の棒グラフを折れ線グラフに変更します。
平均値の棒グラフを選択して、右クリックします。
ショートカットメニューから[系列グラフの種類の変更]をクリックします。
[グラフの種類の変更]ダイアログボックスが表示されます。
[平均]の▼ボタンをクリックします。
グラフの種類の中から[折れ線]をクリックします。
[平均]には、[折れ線]が選択されていることと、グラフのプレビューも確認して問題がなければ、[OK]ボタンをクリックします。
グラフは下のようになります。
レイアウトの変更
レイアウトを変更する場合は、グラフを選択した状態で[デザイン]タブの右端にある[グラフのレイアウト]グループにある[クイックレイアウト]をクリックします。
ここもリアルタイムプレビューの機能があるので、各レイアウトをポイントするだけで結果を確認することができます。
ここでは、[レイアウト3]を選択します。
グラフは下のようになります。
グラフタイトルはセル参照で
グラフタイトルは、セル[A1]の文字列をリンクしたいと思います。
グラフタイトルのテキストボックスを選択します。
数式バーにカーソルをおいて、[=]を入力した後にセル[A1]をクリックします。
[Enter]キーで確定すると、下のようにグラフタイトルを変更できます。
もちろん、グラフタイトルは直接入力しても構いません。
棒グラフを折れ線グラフに変更
もし、上記のような縦棒グラフと折れ線グラフの複合グラフ(組み合わせグラフ)ではなく、折れ線グラフのみのグラフにする場合は、売上の縦棒グラフを選択して右クリックします。
ショートカットメニューから[系列グラフの種類の変更]をクリックします。
[グラフの種類の変更]ダイアログボックスが表示されるので、[売上]の[集合縦棒]を[折れ線]に変更します。
[売上]の▼ボタンをクリックして、グラフの種類を折れ線に変更します。
グラフは下のようになります。
折れ線グラフの重なりを変更
[平均値]の折れ線を[売上]の背景へ移動させる場合の方法です。
今は、[平均値]の折れ線が[売上]の折れ線の手前にあるので、それを逆にします。
グラフエリアを選択した状態で[デザイン]タブの[データ]グループにある[データの選択]をクリックします。
[データソースの選択]ダイアログボックスが表示されます。
[平均]を選択して、[▲](上へ移動)ボタンをクリックします。
[OK]ボタンをクリックして閉じます。
グラフは下のようになります。
参考上記のグラフの重なりの変更方法は、同種類のグラフの場合のみ有効です。
データ系列の書式変更
データ系列の色を変更したい場合は、データ系列を選択して、ショートカットメニューから[データ系列の書式設定]をクリックします。
参考データ系列をダブルクリックしても[データ系列の書式設定]を表示することができます。
ウィンドウの右側に[データ系列の書式設定]が表示されますので、[線]の[色]から任意の色を選択して変更します。
設定変更後、グラフは下のようになります。
平均値を求めた表を作成後に新しいグラフを作成する場合
もし、売上グラフを作成する前で、平均値を求めた列を追加した後にグラフを新規に作成するのであれば、データ範囲を選択して[挿入]タブの[グラフ]グループにある[おすすめグラフ]をクリックします。
[グラフの挿入]ダイアログボックスが表示されますので、サンプルの中に目的のグラフがあれば、それをクリックして作成すると効率的です。
参考おすすめグラフは、Excel2013からの機能です。
関連以下は、折れ線グラフに関する記事です。
-
折れ線グラフに縦の目盛線や降下線を表示する
Excel2016で折れ線グラフを作成したとき、折れ線グラフと横軸の目盛の対応をはっきりさせるために、下のように縦の目盛線を表示したい場合の ...
-
折れ線グラフに系列名をつける
Excelで下のような折れ線グラフを作成しました。 凡例に系列名が表示されていますが、 下のように各折れ線グラフの横に系列名を表示した方が分 ...
-
折れ線グラフを途切れさせない(データ要素を線で結ぶ)
Excel2013でデータに空白セルがあっても、折れ線グラフを途切れさせないようにする機能を紹介します。 たとえば、下のような空白セルがある ...
-
体重推移表と体重推移グラフを作ろう(朝と夜計測して平均を求める)
ダイエットのためには、毎日体重計に乗ることが一番なのだそうです。 健康のためにも自分の体重の推移は記録して意識しておいたほうがいいですね。 ...
-
折れ線グラフの種類と積み上げ折れ線グラフについて
Excelには、多くのグラフの種類があります。 用途や目的に合わせてグラフを選択することができます。 以下は、Microsoftのページです ...
-
近似曲線を追加して来月の売上を予測(グラフの数式を利用して予測値も分かる)
作成した折れ線グラフに[近似曲線]を追加して、データの傾向を可視化することができます。 また、来月、再来月などの未来の売上の予測もできます。 ...
Excel2016には、予測シートという便利な機能があります。
-
予測シートって便利!ワンクリックで傾向を予測してグラフを表示
Excel2016の[データ]タブには、[予測シート]という機能があります。 Excel2016からの新機能です。 データを基に先の予測をグ ...