Excelで棒グラフを作成したものの、目標値や平均値、特定の区分を示す線がなく、数値の違いをひと目で判断しにくいと感じることがあります。
棒の高さだけでも比較はできますが、基準線や比較線を加えると、達成状況と差の理由を伝えやすくなります。
たとえば売上の棒グラフに予算線を重ねたり、平均値を横線で示したり、月の切り替わり位置に縦線を置いたりすると、報告資料としての読みやすさが上がります。
この記事では、棒グラフに区分線・基準線・平均線・補助線・縦線を追加する代表的な方法を、サンプルデータと数式を交えて解説します。
棒グラフへ線を追加する主な考え方は、基準となる数値を別系列として表に用意し、その系列を折れ線や散布図として重ねることです。
横方向の基準線には目標値・平均値・上限値を使い、縦方向の区分線には月・四半期・部門の境目を使うと、グラフの目的が明確になります。
なお、以下のサンプルは1行目を見出し行とした表です。
棒グラフに目標の基準線を追加する方法
それではまず、棒グラフへ一定の目標値を比較線として表示する方法について解説していきます。
| 月 | 実績売上 | 目標売上 |
|---|---|---|
| 4月 | 820 | 900 |
| 5月 | 960 | 900 |
| 6月 | 880 | 900 |
基準値の列を作成する手順
目標線を表示するには、元データの右側に基準値専用の列を作成します。
上の例ではC列に目標売上を入力し、C2からC4まで同じ900を入れます。
横一直線の比較線を作る場合、すべてのカテゴリに同じ値を用意することが重要です。
毎月の予算が同じなら、C2に900を入力したあと、フィルハンドルを下方向へドラッグしてコピーしましょう。
固定の数値ではなく、別セルで目標値を管理したい場合は、たとえばF2に900を入力し、C2へ=$F$2と入力します。
数式は =$F$2 です。
ドル記号を付けた絶対参照にしておくと、C2の数式を下へコピーしても、すべての行がF2の目標値を参照します。
目標が変更されたときはF2だけを書き換えればよく、棒グラフの比較線も自動で動きます。
【操作のポイント】基準値の列はグラフの横軸と同じ行数にそろえ、空白セルを作らないようにします。
集合縦棒グラフを作成する手順
続いて、実績と目標を含む表から、まず基本となる棒グラフを作成します。
A1からC4を選択し、挿入タブのグラフから集合縦棒を選びます。
この段階では実績売上と目標売上の両方が棒として表示される場合がありますが、次の操作で目標だけを線に変更できます。
グラフを選択すると、グラフのデザインタブと書式タブが表示されます。
系列が想定どおりに表示されない場合は、グラフのデザインタブにあるデータの選択から、横軸ラベルがA2からA4、系列名がB1とC1になっているかを確認します。
表の見出しを含めて範囲選択すると、系列名と凡例名を正しく引き継ぎやすくなります。
数値の単位が万円などの場合は、表の見出しにも単位を添えると、グラフだけを切り出した資料でも誤解を防げます。
【操作のポイント】グラフ作成前に不要な空白行や結合セルをなくすと、選択範囲の認識ミスを減らせます。
組み合わせグラフで線へ変更する手順
最後に、目標売上の棒を折れ線へ変更して基準線として仕上げます。
グラフをクリックし、グラフのデザインタブからグラフの種類の変更を選びます。
左側で組み合わせを選び、実績売上は集合縦棒、目標売上は折れ線に設定します。
目標売上の第2軸は通常不要なので、チェックを外した状態にしましょう。
同じ目盛りの主軸に表示することで、棒の先端と基準線の上下関係を正しく比較できます。
OKをクリックしたら、目標線を右クリックしてデータ系列の書式設定を開き、線の色を赤や濃いオレンジに変更します。
マーカーが多すぎると横線の印象が弱くなるため、マーカーをなしに設定するのも有効です。
予算線は太さを1.5ポイントから2ポイント程度にし、実績の棒とは異なる色にすると、会議資料でも視認性を保ちやすくなります。
【操作のポイント】基準線を第2軸にすると目盛りが別になるため、特別な事情がない限り主軸へ配置します。
平均値を横線で表示する方法
続いては、各データの平均を計算し、棒グラフに平均線として追加する方法を確認していきます。
| 担当者 | 受注件数 | 平均件数 |
|---|---|---|
| 田中 | 18 | 数式 |
| 佐藤 | 25 | 数式 |
| 鈴木 | 17 | 数式 |
AVERAGE関数で平均を求める方法
平均線では、実績データの平均値を計算した結果を、各行へ繰り返して表示します。
C2に =AVERAGE($B$2:$B$4) と入力してください。
数式は =AVERAGE($B$2:$B$4) です。
AVERAGE関数はB2からB4までの数値の合計を件数で割り、平均受注件数を返します。
範囲に絶対参照を指定するため、C2の数式をC4までコピーしても、平均対象のB2からB4は動きません。
この例では平均値が20となり、C2からC4にはすべて20が表示されます。
平均値を別の列に同じ値で並べることが、棒グラフ上に水平な平均線を描く準備になります。
空白や文字列が含まれるデータでは、AVERAGE関数は数値だけを平均します。
ゼロも平均に含めたいか、未入力として除外したいかは、集計ルールに合わせて事前に確認しましょう。
【操作のポイント】平均の対象範囲には見出し行を含めず、実績値だけが入ったセルを指定します。
平均系列を折れ線に変更する手順
平均値の列を作成したら、A列からC列を選択して集合縦棒グラフを挿入します。
すでに実績の棒グラフがある場合は、グラフを右クリックしてデータの選択を開き、追加から平均件数の系列を登録しても構いません。
その後、グラフの種類の変更から組み合わせを選択し、平均件数だけを折れ線にします。
平均線には、実績と区別しやすいグレー、青、赤などを選ぶとよいでしょう。
線のスタイルを破線にすると、平均という補助情報を、実績値より控えめに見せられます。
凡例は平均件数ではなく全体平均など、読み手が理解しやすい名前に変更することもできます。
凡例名は元の表のC1セルを変更すると連動します。
【操作のポイント】平均線も主軸に設定し、棒と線の目盛りを同じ基準で比較できるようにします。
平均線を見やすく整える方法
平均線を追加しただけでは、棒と重なって見づらい場合があります。
データ系列の書式設定で線の幅を少し太くし、透明度を低くして輪郭を明確にしましょう。
マーカーを付ける場合は、円形で小さめのサイズにすると、各カテゴリの平均値であることが伝わります。
一方で全カテゴリが同じ平均値なら、マーカーなしの直線の方がすっきり見えることもあります。
平均線のラベルを最後の点だけに表示したい場合は、線の最後のデータ点をもう一度クリックして選択し、データラベルの追加を実行します。
グラフタイトルに平均値を含める、または注記を近くに置く方法も便利です。
平均線は判断の補助であり、個別の目標ラインとは役割が異なります。
比較したい内容に合わせて、平均・予算・前年実績のどれを線にするか決めましょう。
【操作のポイント】線の色・太さ・破線を同時に変えすぎず、棒系列との区別を優先します。
縦線による区分線の追加方法
続いては、月や四半期などの境目を示す縦線を、棒グラフへ追加する方法を確認していきます。
| 月 | 売上 | 区分位置 |
|---|---|---|
| 4月 | 80 | 3.5 |
| 5月 | 92 | 3.5 |
| 6月 | 75 | 3.5 |
区分線用の補助データを作る方法
縦線を正確な位置へ引くには、散布図系列として使う補助データを作成します。
たとえば4月から6月を第1四半期、7月から9月を第2四半期として分けたい場合、6月と7月の間を示す横軸の位置は3.5です。
補助表では、横方向の値を3.5、縦方向の値を0と最大値にします。
縦線用の例では、X値を3.5と3.5、Y値を0と120にします。
Y値の120は棒グラフの縦軸の最大値以上に設定します。
同じX値を2行に入力し、Y値だけを下端と上端に分けると、縦向きの線として描画できます。
棒グラフの項目が増える場合、境目の位置はカテゴリ番号の間となります。
3番目と4番目の棒の間なら3.5、6番目と7番目の棒の間なら6.5です。
【操作のポイント】区分線のY値はグラフの最大目盛りより大きくし、線が上端まで届くようにします。
散布図系列を棒グラフに重ねる方法
ここからは、補助データを既存の棒グラフへ散布図として追加します。
グラフを右クリックしてデータの選択を開き、追加をクリックします。
系列名には区分線、系列Xの値には補助表のX値2セル、系列Yの値にはY値2セルを指定します。
追加後にグラフの種類の変更を開き、区分線の系列だけを直線付き散布図へ変更しましょう。
散布図系列は数値として横軸の位置を扱えるため、棒と棒の間に縦線を配置できます。
【操作のポイント】散布図の横軸と棒グラフの横軸は初期状態ではずれることがあるため、次の軸設定まで行います。
第2軸を合わせて縦線を整える方法
散布図を重ねると、第2横軸と第2縦軸が表示されることがあります。
第2横軸を選択し、最小値を0.5、最大値をカテゴリ数に0.5を足した数、主単位を1に設定します。
6個のカテゴリなら、最小値は0.5、最大値は6.5です。
第2縦軸は主軸と同じ最小値・最大値に設定します。
カテゴリ数が6で、主軸の縦軸が0から120の場合、第2横軸は0.5から6.5、第2縦軸は0から120にそろえます。
設定後、第2横軸と第2縦軸の線・目盛・ラベルは非表示にします。
補助軸を隠しても散布図の位置情報は残るため、縦線だけを自然に見せられます。
区分線の色は薄いグレー、線種は破線にすると、実績データを邪魔せず境目を示せます。
四半期名を伝えたい場合は、テキストボックスをグラフ上部へ配置する方法もあります。
【操作のポイント】主軸と第2軸の最大値が異なると縦線の高さがずれるため、両方の軸設定を確認します。
比較線と補助線の使い分け
続いては、目標線・前年線・平均線・目盛線をどのように使い分けるかを確認していきます。
| 線の種類 | 主な用途 | 見せ方 |
|---|---|---|
| 目標線 | 達成判定 | 実線 |
| 平均線 | 全体比較 | 破線 |
| 前年線 | 推移比較 | 折れ線 |
目標線と実績の比較方法
目標線は、各カテゴリが達成したかを判断するための基準です。
目標が全期間で同じなら水平線、月ごとに異なるなら値が変化する折れ線として表示します。
未達を強調したいときは、棒の色を条件によって分ける方法と目標線を組み合わせると効果的です。
ただし、グラフ内に赤色を多用すると視線が散るため、目標線は1色に固定するのが無難です。
月ごとの目標がD列にある場合は、D列をそのまま折れ線系列として追加します。
固定目標のように同じ値を繰り返す必要はありません。
目標の根拠が前年実績や予算で異なる場合は、グラフの注記にも基準の意味を記載すると親切です。
【操作のポイント】目標線の凡例名は予算・目標・最低基準など、判断基準が伝わる言葉にします。
前年実績を比較線にする方法
前年同月比を見せたい場合は、前年実績の列を追加し、組み合わせグラフの折れ線として重ねます。
今年実績を棒、前年実績を折れ線にすると、月ごとの差だけでなく全体的な傾向も確認できます。
前年値の系列はマーカーを付けると、各月の比較点が分かりやすくなります。
棒と前年線が大きく離れている月は、要因分析が必要な箇所として見つけやすくなります。
前年と今年で数値の桁が大きく違う場合だけ、第2軸の利用を検討します。
ただし第2軸は比較の印象を変えやすいため、目盛りと単位を必ず明示しましょう。
【操作のポイント】前年比較では横軸の月順をそろえ、欠損月がないかを確認してからグラフ化します。
目盛線を補助線として調整する方法
グラフには最初から主要横目盛線が表示されることがあります。
この目盛線は基準線とは異なり、数値を読み取るための補助線です。
グラフ要素の追加から目盛線を選ぶと、主要横目盛線・補助横目盛線などを表示できます。
補助目盛線は薄いグレーにして、データ系列より目立たせないことが基本です。
縦棒グラフでは横方向の目盛線が数値の高さを読む助けになり、横棒グラフでは縦方向の目盛線が役立ちます。
細かい補助目盛線を増やしすぎると、かえってグラフ全体が網目状になります。
報告用グラフでは主要目盛線だけを残し、細かな数値はデータラベルや表で補うと、情報量と見やすさのバランスを取りやすくなります。
【操作のポイント】補助線は測定を助ける役割にとどめ、比較したい基準線とは色と線種を変えます。
棒グラフの線が表示されない場合の確認項目
続いては、追加した比較線や縦線が見えない、位置がずれる場合の確認項目を解説していきます。
| 症状 | 主な原因 | 確認内容 |
|---|---|---|
| 線が見えない | 値が空白 | 系列範囲 |
| 線が上にある | 第2軸 | 軸の最大値 |
| 縦線がずれる | 横軸設定 | 最小値と最大値 |
データ範囲と空白セルの確認
折れ線が表示されないときは、まずデータの選択画面で系列の参照範囲を確認します。
目標値の列に空白セルや文字列があると、線が途切れたり、グラフに反映されなかったりします。
数式を使う場合は、数式の結果が数値になっているかをセル上で確認します。
たとえばIF関数で空文字を返しているセルは、Excelの設定によって空白として扱われます。
必要なら、グラフの非表示および空白セルの設定から、空白セルを間隔・ゼロ・データ要素を線で結ぶのどれとして扱うかを選びます。
【操作のポイント】グラフ系列の参照範囲は、見出しではなく実際のデータ行まで含まれているかを確認します。
主軸と第2軸の設定確認
線が棒の上端より極端に高い、または低い位置に出る場合は、第2軸へ設定されている可能性があります。
対象の線をクリックし、データ系列の書式設定から系列のオプションを開きます。
主軸と第2軸の選択状態を確認し、同じ単位を比較する線なら主軸に変更しましょう。
第2軸は単位や桁が異なるデータを併記するための機能であり、単なる目標線には通常不要です。
第2軸を使う必要がある場合は、左右の軸タイトルを表示し、何を表す目盛りか明確にします。
【操作のポイント】目標・平均・前年実績が同じ単位なら、主軸へ統一すると誤読を防げます。
グラフ種類と軸の位置確認
縦線が棒の中央ではなく端に寄る場合は、散布図の第2横軸の最小値・最大値・主単位を再確認します。
棒が6本なら横方向の数値位置は1から6に対応するため、軸の範囲を0.5から6.5に設定する考え方です。
棒グラフの横軸が日付軸になっていると、カテゴリ軸として扱う場合と位置が異なることがあります。
3本目と4本目の間へ線を置く数値は3.5です。
散布図のX値に3.5を指定し、第2横軸の主単位を1にすると、境目を狙いやすくなります。
複雑な縦線を作る前に、コピーしたグラフで軸設定を試すと、元の資料を安全に保てます。
【操作のポイント】縦線がずれる場合は、散布図の数式より先に第2横軸の範囲を確認します。
まとめ 棒グラフへの区分線・基準線の追加方法
Excelの棒グラフに区分線・基準線を追加するには、比較したい数値を別の系列として表に用意し、組み合わせグラフで折れ線へ変更する方法が基本です。
固定の目標値なら同じ数値を各行に並べ、平均値ならAVERAGE関数の結果を各行へコピーすると、横方向の比較線を作れます。
棒グラフは実績、折れ線は基準や比較対象という役割分担にすると、情報が伝わりやすくなります。
月や四半期の区切りを示す縦線は、散布図系列と第2軸を使うことで、棒と棒の間へ正確に配置できます。
目標線・平均線・前年線・目盛線は目的が異なるため、色や線種を整理し、グラフ内で強調する情報を絞ることが大切です。
線が表示されない場合は、系列の参照範囲、空白セル、主軸と第2軸、横軸の設定を順に確認しましょう。
比較線と補助線を適切に使い分け、数値の差と判断基準がひと目で伝わるExcelグラフを作成していきましょう。
