当サイトはアフィリエイト広告を利用しています。
Excelで数値データをグラフ化するときは、平均値や中央値もグラフに入れると、「中心的な値と比較してどれくらいなのか？」といったことがひと目でわかるので便利です。
そこで今回は、Excelで散布図、棒グラフ、折れ線グラフなどのグラフに直線で平均値や中央値を入れる方法をご紹介します。
Excelでグラフに直線で平均値や中央値を入れる方法
Excelでグラフに直線で平均値や中央値を入れる手順は以下のとおりです。
- 平均値や中央値のデータを作成する
- 折れ線グラフを追加する
- 折れ線グラフの書式を整える
手順1：平均値や中央値のデータを作成する
まずは平均値や中央値のデータを作成します。
たとえば月別売上のグラフに平均値を入れたい場合は、平均値を表示したい最初のセルに=AVERAGE(平均を求めたいセル範囲の絶対参照)と入力し、最後のセルまでコピーします。
なお中央値を入れたい場合、数式は=MEDIAN(平均を求めたいセル範囲の絶対参照)です。
手順2：折れ線グラフを追加する
次に、作成したデータを折れ線グラフで元のグラフに追加します。
まず元のグラフのグラフエリアを選択して「グラフのデザイン」タブ⇒「データの選択」をクリックします。
「データソースの選択」が表示されたら、「凡例項目」の「追加」をクリックします。
「系列の編集」が表示されたら、「系列名」に「平均値」または「中央値」などと入力します。セルに入力している場合は当該セルを選択してもOKです。
「系列値」の入力内容を削除し、平均値または中央値が入力されたセル範囲を選択します。
完了したら「OK」をクリックします。
「データソースの選択」に戻ったら「OK」をクリックします。
「グラフの種類の変更」をクリックします。
「グラフの種類の変更」が表示されたら、「組み合わせ」をクリックし、平均値または中央値のグラフの種類が「折れ線」になっていることを確認して「OK」をクリックします。
これでグラフに直線で平均値や中央値が入ります。
手順3：折れ線グラフの書式を整える
最後に折れ線グラフの書式を見やすく整えます。
このままだと線が中途半端な長さなので、もう少し長くします。折れ線グラフを右クリックして「近似曲線の追加」をクリックします。
「近似曲線の書式設定」が表示されたら、「予測」の「前方補外」と「後方補外」をそれぞれ「0.5」にします。
「塗りつぶしと線」をクリックし、「実線/点線」から「実線」を選択します。
「幅」の値を元の折れ線グラフの幅と同じになるよう調整します。
これで直線が見やすくなります。
Excelで最初から平均値や中央値の直線が入ったグラフを作成する方法
作成済みのグラフにあとから平均値や中央値の直線を入れるのではなく、最初から平均値や中央値の直線が入ったグラフを作成したい場合は、平均値や中央値を含むデータ範囲を選択した状態で組み合わせグラフを作成します。
先ほどの例だと、A1からM3まで選択した状態で「挿入」タブ⇒「おすすめグラフ」をクリックします。
「グラフの挿入」が表示されたら、「すべてのグラフ」タブ⇒「組み合わせ」をクリック⇒必要に応じて「グラフの種類」を変更⇒「OK」をクリックします。
これで平均値や中央値の直線が入ったグラフが出来上がります。あとは直線の書式を整えればOKです。
Excelで平均値と標準偏差をグラフにする方法
全体の平均値と値のバラつきを見たい場合は、平均値をグラフ化し、そのグラフに標準偏差を入れると良いでしょう。
標準偏差とは、各データが平均値からどれくらい離れているかを示す値のことです。標準偏差が大きければ「最小値と最大値の差が大きい」ということが、小さければ「平均値に近い値が多い」ということがわかります。
Excelで平均値と標準偏差をグラフにする手順は以下のとおりです。
- 平均値と標準偏差のデータを作成する
- 平均値のグラフを作成する
- グラフに誤差範囲として標準偏差を指定する
手順1：平均値と標準偏差のデータを作成する
まずは平均値と標準偏差のデータを作成します。
たとえばクラス別のテストの平均点のグラフに標準偏差を入れたい場合は、平均値を表示したい最初のセルに=AVERAGE(平均を求めたいセル範囲)と入力し、最後のセルまでコピーします。
標準偏差を求めるにはSTDEV.P関数を使います。
STDEV.P関数（スタンダードディービエーション・ピー関数）は、母集団全体の標準偏差を求める関数です。
STDEV.P（数値1, ［数値2］, …）
| 数値1（必須） | 標準偏差を求めたい数値を指定する。 |
|---|---|
| 数値2（省略可） | 標準偏差を求めたい追加の数値を指定する。 |
なお、「数値」は最大255個まで指定できます。
やり方は平均値のときと同じで、標準偏差を表示したい最初のセルに=STDEV.P(標準偏差を求めたいセル範囲)と入力し、最後のセルまでコピーするだけです。
手順2：平均値のグラフを作成する
次に作成した平均値のデータを使ってグラフを作成します。
まずクラス名のセル範囲と平均値のセル範囲を選択します。離れたセルを選択するときは最初のセル範囲を選択したあと、［Ctrl］を押しながら追加のセル範囲を選択します。
「挿入」タブを開き、「グラフ」グループから好きなグラフを選択します。今回は棒グラフを選択します。
これで平均値のグラフが作成されます。
手順3：グラフに誤差範囲として標準偏差を指定する
最後にグラフに誤差範囲として標準偏差を指定します。
まずグラフエリアを選択し、右上の「＋」をクリックして「誤差範囲」にチェックを付けます。
するとグラフに「エ」のような形で誤差範囲が表示されます。
続いてもう一度グラフエリアを選択し、「＋」をクリック⇒「誤差範囲」にカーソルを合わせて「＞」⇒「その他のオプション」をクリックします。
「誤差範囲の書式設定」が表示されたら、「誤差範囲」で「ユーザー設定」を選択し、「値の指定」をクリックします。
「ユーザー設定の誤差範囲」が表示されたら、「正の誤差の値」の元の値を削除し、標準偏差のセル範囲を選択します。
「負の誤差の値」でも同じように標準偏差のセル範囲を選択して「OK」をクリックします。
これで平均値と標準偏差のグラフが出来上がります。今回の例だとC組とD組がほぼ同じ平均値ですが、C組は5回とも平均値に近い点数なのに対し、D組は低いときと高いときの差が大きいということがわかりますね。
まとめ
今回は、Excelのグラフに直線で平均値や中央値を入れる方法をご紹介しました。
Excelのグラフに直線で平均値や中央値を入れるには、平均値や中央値のデータを作成してから、そのデータを折れ線グラフとして追加し、最後に書式を整えます。平均値や中央値だけでなく、目標値なども同じやり方で追加できますよ。
Udemyでは、買い切りの動画講座で好きな分野について自分のペースで学ぶことができます。
パソコンだけでなくスマホにも対応しているので、移動時間や休憩時間にスマホで学習することも可能です。
30日間の返金保証付きなので、初めてでも安心して受講できますよ。
▼ おすすめのExcel VBA講座はこちら ▼
