Excelでグラフを作成すると、棒グラフや折れ線グラフに誤差範囲を表示したい場面があります。
しかし、標準誤差やパーセンテージは選べても、任意のセル範囲を使うユーザー設定ができない、設定画面が表示されない、値が反映されないと悩むことも少なくありません。
誤差範囲は、測定値や集計値のばらつき、不確かさ、上限値と下限値を視覚的に伝えるための機能です。
この記事では、グラフの誤差範囲を追加する基本操作から、ユーザー設定で正負の値を別々に指定する方法、設定できない場合の確認項目までを解説します。
任意の誤差範囲を表示するには、グラフを選択してから誤差範囲を追加し、その他のオプションでユーザー設定を開くことが基本です。
正の誤差の値と負の誤差の値には、1行目を見出しとした同じ個数の数値セル範囲を指定します。
数式やサンプル表も確認しながら、見やすく正確なグラフに整えていきましょう。
エクセルで誤差範囲をユーザー設定する方法
それではまず、任意のセルを指定して誤差範囲を設定する手順について解説していきます。
| 月 | 平均売上 | 正の誤差 | 負の誤差 |
|---|---|---|---|
| 4月 | 120 | 12 | 8 |
| 5月 | 145 | 15 | 10 |
| 6月 | 132 | 9 | 11 |
グラフ要素からの誤差範囲追加
グラフ要素からの追加について解説していきます。
先に、月と平均売上の列を使って集合縦棒グラフまたは折れ線グラフを作成しておきます。
グラフの余白部分をクリックし、グラフ全体が選択された状態にしてください。
グラフ右上に表示されるプラス記号のグラフ要素をクリックすると、追加できる要素の一覧が開きます。
一覧にある誤差範囲へマウスポインターを合わせ、右側の三角形を選択します。
標準誤差、パーセンテージ、標準偏差などの候補が表示されますが、ここではいずれかを仮に選んでも構いません。
この段階では数値の種類を決めるのではなく、誤差範囲をグラフへ追加することが目的です。
棒や折れ線の各データ点に、上下へ伸びる細い線が表示されれば追加は完了です。
その他のオプション画面の表示
続いては、ユーザー設定に進むための作業ウィンドウを確認していきます。
誤差範囲が表示されたグラフで、上下の線のいずれかをクリックします。
選択できると、すべての誤差範囲に選択ハンドルが付くため、グラフ系列ではなく誤差範囲を選べたか判断できます。
右クリックして誤差範囲の書式設定を選ぶか、グラフ要素のメニューからその他のオプションを選択します。
画面右側に誤差範囲の書式設定ペインが開いたら、誤差範囲のオプションを確認します。
ここには方向、終点のスタイル、誤差の量が並びます。
ユーザー設定は誤差の量の中にあり、グラフそのものの書式設定を開いても表示されない点に注意しましょう。
ユーザー設定を選んだ後に値の指定ボタンを押すと、正の誤差値と負の誤差値を入力する画面へ移動できます。
正負のセル範囲指定
次に、正の値と負の値をそれぞれセル範囲から設定する方法を解説していきます。
値の指定画面には、正の誤差の値と負の誤差の値という2つの入力欄があります。
正の誤差の値には、たとえばC2からC4を指定します。
負の誤差の値には、D2からD4を指定しましょう。
見出しである1行目を含めず、グラフのデータ点と同じ3件分を指定することが重要です。
正の誤差の値としてC2からC4、負の誤差の値としてD2からD4を指定すると、4月は上方向に12、下方向に8の幅が描かれます。
5月と6月も、それぞれ対応する行の値で誤差範囲が表示されます。
上側と下側を同じ幅にしたい場合でも、正負それぞれに同じセル範囲を指定すると設定ミスを防げます。
範囲指定後にOKを押し、グラフへ戻って各棒の誤差範囲が異なる長さで表示されているか確認してください。
【操作のポイント】セル範囲はデータ系列の件数と一致させます。グラフが3項目なら、誤差値も3セル分にそろえることが必要です。
誤差範囲の種類と使い分け
続いては、Excelで選択できる誤差範囲の種類と、ユーザー設定を使う場面を確認していきます。
| 設定種類 | 特徴 | 向く用途 |
|---|---|---|
| 標準誤差 | データの標準誤差を自動計算 | 統計的な傾向の表示 |
| パーセンテージ | 値に対する一定割合 | 概算の許容幅 |
| ユーザー設定 | セルごとに別の値を指定 | 測定誤差や上下限の表示 |
固定値とパーセンテージ
固定値とパーセンテージについて解説していきます。
固定値は、すべてのデータ点に同一の誤差幅を付ける設定です。
たとえば誤差の量を10と指定すると、各値に対して上方向と下方向へ10ずつの誤差範囲が付きます。
パーセンテージは、元の値に対して一定割合の幅を設定する方法です。
値が100でパーセンテージを5にした場合、誤差範囲はおおむね上下5となります。
値が200なら上下10になるため、数値の規模に応じて幅も変わります。
すべての項目へ一律の基準を適用したい場合は固定値、値に比例する許容幅を見せたい場合はパーセンテージが適しています。
ただし、実際の測定結果や検査値が項目ごとに異なる場合、固定値やパーセンテージでは正確な情報を表現できません。
標準偏差と標準誤差
続いては、標準偏差と標準誤差の違いを確認していきます。
標準偏差は、複数の数値が平均値の周辺にどの程度散らばっているかを表す指標です。
標準誤差は、標本から算出した平均値がどの程度安定しているかを見るために使われます。
同じ名称に見えても、示している内容は異なります。
標準偏差の代表的な考え方は、各値と平均値との差を二乗して平均し、その平方根を取る方法です。
ExcelではSTDEV.S関数などで標本標準偏差を求められます。
グラフの誤差範囲で標準偏差または標準誤差を選ぶと、Excelが系列データをもとに自動的に計算します。
検査成績書に記載された許容差や実測されたばらつきを表示したい場合は、自動計算ではなくユーザー設定を選ぶほうが分かりやすいでしょう。
統計処理の目的と、グラフで読者へ伝えたい意味を分けて考えることが大切です。
方向と終点スタイル
方向と終点スタイルについて解説していきます。
方向には両方向、正方向、負方向があります。
両方向は、データ点を中心として上側と下側の両方に誤差範囲を表示する設定です。
正方向は上側だけ、負方向は下側だけに表示します。
たとえば、予定値を超えた可能性だけを示すなら正方向、値引きや減少の可能性だけを見るなら負方向を選ぶケースもあります。
終点のスタイルでは、誤差範囲の先端に横線を表示するキャップありと、線だけで終えるキャップなしを選択できます。
誤差の上限と下限を明確に読み取らせたいグラフでは、キャップありを選ぶと視認性が高まります。
【操作のポイント】誤差範囲の意味が測定の不確かさなのか、目標値との差なのかを明記すると、方向や幅を誤解されにくくなります。
ユーザー設定できない場合の確認項目
続いては、ユーザー設定が選択できない、またはセル範囲が反映されない場合の確認項目を解説していきます。
| 確認箇所 | 正しい状態 | よくある原因 |
|---|---|---|
| 選択対象 | 誤差範囲を選択 | グラフ全体を選択している |
| グラフ種類 | 対応するグラフ | 円グラフなどを使用 |
| 参照範囲 | 数値のみで件数一致 | 文字列や空白を含む |
対応グラフの確認
対応グラフの確認について解説していきます。
誤差範囲は、すべてのグラフで使える機能ではありません。
縦棒グラフ、横棒グラフ、折れ線グラフ、散布図などでは利用できますが、円グラフやドーナツグラフでは通常の誤差範囲を追加できません。
円グラフは全体に対する構成比を示す形式であり、各要素の上下方向へ数値の幅を表示する設計ではないためです。
誤差範囲の項目そのものが表示されない場合は、まず現在のグラフ種類を確認します。
グラフを選択し、グラフのデザインタブからグラフの種類の変更を選びます。
棒グラフまたは散布図へ変更してから、再度グラフ要素を開くと設定できるようになる場合があります。
複数系列のグラフでは、系列ごとに誤差範囲を追加する必要がある点も覚えておきましょう。
誤差範囲の選択状態
続いては、選択状態による設定の違いを確認していきます。
グラフをクリックしただけでは、グラフ全体が選ばれている状態です。
この状態で右クリックすると、グラフエリアの書式設定やグラフタイトルの設定が表示されることがあります。
誤差の量を変更するには、表示済みの誤差範囲そのものを選択しなければなりません。
選択が難しいときは、グラフの書式タブにある現在の選択範囲を利用します。
ドロップダウン一覧から系列の誤差範囲を選び、選択対象の書式設定をクリックしてください。
右側ペインの見出しが系列の書式設定ではなく、誤差範囲の書式設定になっていることを確認すると安心です。
複数の系列がある場合は、意図した系列の誤差範囲を選んでいるかも見直しましょう。
セル参照とデータ件数
セル参照とデータ件数について解説していきます。
ユーザー設定の誤差値として指定できるのは、数値が入力されたセル範囲です。
文字列、エラー値、結合セル、データ点数と合わない範囲を含めると、設定エラーや意図しない表示につながる場合があります。
たとえばグラフの系列がB2からB4の3件なのに、正の誤差の値としてC2からC5の4件を指定することはできません。
グラフのデータ件数をn個とすると、正の誤差値と負の誤差値も原則としてそれぞれn個必要です。
数式で誤差値を作る場合も、1行目はヘッダーとして、2行目からグラフの最終行まで式をコピーします。
空欄をゼロ扱いにしたい場合は、空欄のままではなく0を入力するか、IFERROR関数などで数値を返すよう整える方法があります。
セルに数式が入っていても、その計算結果が数値なら誤差範囲の参照先として使用可能です。
【操作のポイント】参照範囲を選ぶ前に、グラフ元データと誤差値の開始行、終了行、件数を横並びで確認すると入力ミスを減らせます。
誤差値を数式で作成する方法
続いては、誤差範囲に使う数値を数式で算出する方法について解説していきます。
| 商品 | 実績 | 想定誤差率 | 誤差値 |
|---|---|---|---|
| A商品 | 1200 | 5% | 60 |
| B商品 | 900 | 8% | 72 |
| C商品 | 1500 | 3% | 45 |
割合から誤差値を求める数式
割合から誤差値を求める数式について解説していきます。
実績値に対して許容誤差率が決まっている場合、誤差値の列を作ればユーザー設定へ簡単に利用できます。
1行目をヘッダーとし、B列に実績、C列に誤差率、D列に誤差値を入力する例で考えます。
D2セルには、実績と誤差率を掛け合わせる数式を入力します。
D2セルの数式
=B2*C2
B2が1200、C2が5パーセントであれば、D2の結果は60です。
このD2セルの右下にあるフィルハンドルを下へドラッグすると、D3以降にも相対参照で数式がコピーされます。
数式をコピーした後は、誤差値の列だけを正の誤差値と負の誤差値の両方に指定できます。
正負で同じ幅の誤差を示す場合に、もっとも管理しやすい構成です。
上限値と下限値からの換算
続いては、上限値と下限値から誤差値へ換算する方法を確認していきます。
検査データなどでは、誤差そのものではなく上限値と下限値が記録されていることがあります。
この場合、実績値との差を計算して、上方向と下方向の誤差値を別の列へ作成します。
実績がB2、上限値がC2、下限値がD2の場合、正の誤差値は=C2-B2で求めます。
負の誤差値は=B2-D2で求めます。
上限値が130、実績が120なら正の誤差値は10です。
下限値が113なら負の誤差値は7になります。
上限値や下限値そのものをユーザー設定へ指定するのではなく、中心となる値との差を指定することが重要です。
上側と下側で幅が異なる非対称の誤差範囲も、これで正しく描画できます。
オートフィルと参照範囲
オートフィルと参照範囲について解説していきます。
数式を入力した後は、先頭セルのフィルハンドルを使って最終データ行までコピーします。
Excelのテーブル機能を利用している場合は、列内に数式を入力するだけで自動展開されることもあります。
誤差値の計算に固定の基準セルを使うなら、絶対参照を使います。
たとえばF1セルに共通の誤差率を入力している場合、D2セルの数式は=B2*$F$1です。
F1を絶対参照にすると、オートフィル後もすべての行が同じ誤差率を参照します。
グラフのユーザー設定では、オートフィル後のD2からD4のように、完成した数値範囲を指定します。
グラフのデータ行を追加したときは、誤差範囲の参照範囲も最終行まで拡張されているか確認しましょう。
【操作のポイント】数式列を作ることで、元データが変わったときにも誤差範囲を連動して更新できます。
見やすい誤差範囲グラフの整え方
続いては、誤差範囲を読み取りやすくするグラフの整え方について解説していきます。
| 調整対象 | 確認内容 | 期待できる効果 |
|---|---|---|
| 線の色 | 系列と近い色にする | 対応関係が分かる |
| 軸の目盛 | 最小値と最大値を確認 | 差が読み取れる |
| 凡例と注記 | 誤差の意味を補足 | 誤解を防げる |
系列ごとの色分け
系列ごとの色分けについて解説していきます。
複数系列の棒グラフでは、どの誤差範囲がどの系列に対応するのか分かりにくくなることがあります。
誤差範囲を選択して線の設定を開き、系列の塗りつぶし色に近い色を指定すると対応関係が見やすくなります。
ただし、誤差範囲まで濃い色にすると、グラフ全体が重く見えるかもしれません。
系列より少し暗い色、または同系統のグレーを使うと、主データを邪魔せずに情報を補足できます。
誤差範囲は主役の数値を補助する要素なので、棒や折れ線より目立たせすぎないことが見やすさにつながります。
白黒印刷を想定する資料では、色だけに頼らず線種やキャップの有無も使い分けましょう。
縦軸の最小値と最大値
続いては、縦軸の表示範囲を確認していきます。
誤差範囲があるグラフでは、縦軸の最大値が小さすぎると上側のキャップが見切れてしまいます。
反対に表示範囲が広すぎると、誤差の差が小さく見えて比較しにくくなります。
縦軸を右クリックして軸の書式設定を開き、境界値の最小値と最大値を確認してください。
実績値だけではなく、実績値に正の誤差を足した最大値、実績値から負の誤差を引いた最小値まで含めて考えます。
表示上限の目安は、最大の実績値と正の誤差値の合計より少し大きい値です。
表示下限の目安は、最小の実績値から負の誤差値を引いた値より少し小さい値です。
軸の範囲を調整するときは、誤差範囲を切り取らず、かつ不要な余白を増やしすぎないバランスを意識します。
グラフタイトルと注記
グラフタイトルと注記について解説していきます。
誤差範囲の長さだけを見ても、それが標準偏差なのか、測定誤差なのか、許容範囲なのかは判断できません。
グラフタイトル、軸ラベル、テキストボックスなどで誤差の意味を補足してください。
たとえば、売上実績と予測誤差、平均測定値と標準偏差、検査値と許容差のように表記します。
社内資料なら、データの対象期間、対象件数、集計方法も近くに記載すると確認しやすくなります。
誤差範囲は数値の不確かさを表すため、根拠となる計算方法や単位を注記することが大切です。
【操作のポイント】グラフを初めて見る人が、誤差線の意味を本文なしでも理解できるかを基準にタイトルと注記を整えましょう。
まとめ エクセルの誤差範囲設定できない場合の対処方法
Excelの誤差範囲は、グラフを選択してグラフ要素から追加し、誤差範囲の書式設定でユーザー設定を選ぶことで、任意のセル範囲から設定できます。
正の誤差の値と負の誤差の値は別々に指定できるため、上限と下限の幅が違うデータにも対応可能です。
ユーザー設定できないときは、対応するグラフ種類か、誤差範囲そのものを選択しているか、参照セル数がデータ点数と一致しているかを順番に確認しましょう。
割合から誤差値を求める場合は、実績値と誤差率を掛ける数式を入力し、先頭セルからオートフィルで最終行までコピーします。
上限値と下限値がある場合は、中心値との差を計算した列を正負それぞれの誤差値として指定します。
色、軸の範囲、注記も整えると、単なる数値の比較では伝わらないばらつきや不確かさを、読み手へ正確に伝えられるグラフになります。
