Excelで棒グラフを作成したものの、複数の系列が横に並んでいて比較しにくい、棒を重ねて実績と目標を見せたい、棒グラフと散布図を組み合わせたいと感じることがあります。
グラフの見た目は、データの伝わり方を大きく左右します。
系列の重なり、要素の間隔、グラフの種類、そして第2軸の設定を使い分ければ、意図に合った重ね方に調整できます。
棒グラフを重ねる基本操作は、グラフを選択してデータ系列の書式設定を開き、系列の重なりを調整する方法です。
棒グラフと散布図をくっつける場合は、組み合わせグラフと第2軸を利用します。
この記事では、Excelの棒グラフを重ねる方法、系列を重ねる割合の決め方、棒と散布図を組み合わせる手順、見やすく整えるコツを解説します。
それでは、操作の流れを順番に確認していきましょう。
エクセルの棒グラフを重ねる方法【系列の重なりの設定】
それではまず、棒グラフの系列を重ねる基本操作について解説していきます。
| 月 | 目標売上 | 実績売上 |
|---|---|---|
| 4月 | 120 | 108 |
| 5月 | 140 | 132 |
| 6月 | 130 | 145 |
ここでは、1行目に見出しがあり、A列に月、B列に目標売上、C列に実績売上を入力したサンプルを使用します。
集合縦棒グラフの作成
最初に、元になる集合縦棒グラフを作成します。
セル範囲A1:C4をドラッグして選択し、挿入タブから縦棒または横棒グラフを選び、集合縦棒をクリックします。
集合縦棒は、各月の目標と実績を横並びで表示する標準的なグラフです。
作成直後は棒同士が離れているため、どちらが大きいかは分かっても、目標の達成状況を一目で表す用途にはやや不向きかもしれません。
グラフが表示されたら、凡例に目標売上と実績売上が登録されていること、横軸に4月から6月が表示されていることを確認します。
データ範囲を選ぶ際は、月の列も含めることが大切です。
A列を含めずに数値だけを選択すると、横軸が月ではなく連番になる場合があります。
データ系列の書式設定
続いて、棒を重ねるための設定画面を開きます。
グラフ内にある目標売上または実績売上の棒を1回クリックし、同じ系列の棒すべてが選択された状態にします。
右クリックしてデータ系列の書式設定を選択するか、Ctrlキーを押しながら1キーを押して作業ウィンドウを表示します。
グラフ全体ではなく、棒そのものを選択してから操作することが重要です。
右側の作業ウィンドウで系列のオプションを開くと、系列の重なりと要素の間隔を変更できます。
選択対象が間違っていると、グラフエリアの書式設定やプロットエリアの書式設定が開くため、棒が選択されているかを確認しましょう。
系列の重なりの調整
データ系列の書式設定にある系列の重なりを、100パーセントに設定します。
既定値は0パーセント付近であり、この状態では目標と実績の棒が横に並びます。
重なりを100パーセントにすると、同じ月の棒が同じ位置に配置されます。
実績の棒を前面に見せるには、実績系列を細くし、目標系列を背景のように広くする調整が効果的です。
要素の間隔は、月ごとの棒の間の余白です。
要素の間隔を小さくすると棒が太くなり、大きくすると月と月の間が広がります。
【操作のポイント】系列の重なりを100パーセントにしただけでは、前後の棒が同じ幅で完全に隠れることがあります。
目標系列を太く、実績系列を細くして、色と透明度も変えると比較しやすくなります。
棒グラフをくっつける配色と幅の調整
続いては、重ねた棒グラフを見やすくくっつけるための配色と棒幅を確認していきます。
| 月 | 予算 | 支出 |
|---|---|---|
| 7月 | 80,000 | 66,000 |
| 8月 | 90,000 | 94,000 |
| 9月 | 85,000 | 72,000 |
予算を背景の棒、支出を前面の棒にすると、予算内か超過かを直感的に把握しやすくなります。
背景系列と前面系列の色分け
棒が重なったグラフでは、色の役割を先に決めておくと読み手が迷いません。
目標、予算、前年実績などの基準となる系列には、薄いグレー、淡い青、淡い緑などの控えめな色を使います。
実績、現在値、達成値など注目させたい系列には、濃い青、緑、オレンジなどを使うとよいでしょう。
背景の棒は淡色、前面の棒は濃色という関係を保つことで、重ねてもデータの意味が判別できます。
棒を右クリックし、塗りつぶしから単色またはグラデーションを選べます。
背景系列に透明度を設定する場合は、20パーセントから45パーセント程度を目安にすると、前面系列との違いを残しながら圧迫感を減らせます。
棒の幅と要素の間隔
Excelの標準機能では、同じグラフ内にある系列ごとに棒幅を個別設定することはできません。
そのため、背景を太く前面を細く見せたい場合は、系列の順序と補助的なグラフ設定を組み合わせます。
まず背景となる系列を選択し、系列のオプションで要素の間隔を調整します。
次に、前面に置きたい系列を第2軸に移して重ねると、主軸と第2軸で異なる要素の間隔を設定できます。
第2軸を利用する方法は、太い背景棒と細い前面棒を作る実用的な方法です。
ただし左右の縦軸の最大値と最小値が違うと、棒の高さを正しく比較できません。
両方の軸の最小値、最大値、目盛間隔を同じ数値に設定したうえで、第2軸は非表示にします。
主軸と第2軸を使う場合の基本条件は、主軸の最小値＝第2軸の最小値、主軸の最大値＝第2軸の最大値です。
両軸の尺度がそろって初めて、重なった棒の高さを公平に比較できます。
系列の順序と凡例の確認
重なった棒の前後関係は、データの選択画面にある系列の順序で変わります。
グラフを右クリックしてデータの選択を開き、凡例項目の一覧から系列を選択します。
上へ移動、下へ移動を使うと、描画順を調整できます。
表示順はExcelのバージョンやグラフ種類によって見え方が変わるため、変更後は必ずグラフを確認しましょう。
凡例も同じ順序で表示される場合があるため、背景系列と前面系列の名称が分かりやすいかも確認してください。
【操作のポイント】重ねる目的が比較なら、凡例の名称は目標、実績、予算、支出のように短く明確にします。
系列名が長い場合は、表の見出しを短くしてグラフの横幅を確保しましょう。
棒グラフと散布図を重ねる組み合わせグラフ
続いては、棒グラフに散布図をくっつける組み合わせグラフを確認していきます。
| 月 | 売上 | 満足度 |
|---|---|---|
| 1月 | 220 | 4.1 |
| 2月 | 250 | 4.4 |
| 3月 | 235 | 3.9 |
売上は金額、満足度は5点満点というように、単位や桁が異なるデータを一つのグラフで扱うときに組み合わせグラフが役立ちます。
組み合わせグラフへの変更
棒グラフを作成した後に、グラフを選択してグラフのデザインタブを開きます。
グラフの種類の変更をクリックし、左側から組み合わせを選択します。
売上は集合縦棒、満足度は散布図またはマーカー付き折れ線に設定します。
カテゴリごとの位置を棒とそろえたい場合は、散布図よりマーカー付き折れ線の方が扱いやすいことがあります。
散布図は横軸を数値として扱うため、月の位置を厳密に合わせるにはX値の設定が必要です。
日付や連続した数値を横軸に使う分析では、散布図の柔軟性が生きます。
第2軸の設定
満足度の系列は、組み合わせグラフの設定画面で第2軸にチェックを入れます。
売上が200前後、満足度が4前後のまま同じ縦軸に表示すると、満足度の点はグラフ下部に張り付いて読めません。
単位や値の範囲が大きく違う系列は、第2軸に配置するのが基本です。
第2軸を表示したら、軸を右クリックして軸の書式設定を開き、最小値を3、最大値を5など、データに合う範囲へ調整します。
満足度の変動を強調したいからといって範囲を極端に狭くすると、わずかな差が大きく見えるため注意が必要です。
散布図の位置を棒に合わせる設定
散布図を使う場合は、横軸の数値と棒グラフのカテゴリ位置を合わせる工夫が必要です。
月ごとの棒の中央に点を置きたいときは、散布図のX値を1、2、3のような連番にします。
散布図のX値を作る数式の例は、D2に=ROW()-1と入力する方法です。
1行目がヘッダーの場合、D2は1になり、D3以降へオートフィルすると2、3、4と連番を作成できます。
数式を入力したD2の右下にあるフィルハンドルを下へドラッグすると、下のセルにも連番が反映されます。
散布図のデータの選択で系列を編集し、系列Xの値にD2:D4、系列Yの値にC2:C4を指定します。
横軸の最小値や最大値、主単位を調整し、点が棒の中央付近に来るか確認します。
【操作のポイント】散布図の点と棒の位置がずれる場合は、散布図のX値と横軸の範囲を確認します。
月名のカテゴリ軸と散布図の数値軸は仕組みが異なるため、位置合わせには連番列が便利です。
横棒グラフを重ねる設定
続いては、横棒グラフで系列を重ねる設定について確認していきます。
| 担当者 | 目標件数 | 対応件数 |
|---|---|---|
| 田中 | 40 | 36 |
| 佐藤 | 35 | 39 |
| 鈴木 | 45 | 42 |
担当者名や商品名など、横軸に長い文字列を表示する場合は横棒グラフが向いています。
集合横棒グラフへの変更
既に縦棒グラフがある場合は、グラフの種類の変更から集合横棒を選択すれば切り替えられます。
最初から作成する場合は、表を選択して挿入タブの横棒グラフから集合横棒を選びます。
横棒グラフでは、カテゴリが縦方向、数値が横方向に表示されます。
項目名を途中で省略せずに表示したいときは、横棒グラフが有利です。
グラフの高さを十分に確保すると、担当者や製品名が読みやすくなります。
横棒の重なりと表示順
横棒でも、データ系列の書式設定にある系列の重なりを使う手順は縦棒グラフと同じです。
棒を選択して系列の重なりを100パーセントに設定すると、対応件数を目標件数の上に表示できます。
カテゴリの表示順が意図と逆に見える場合は、縦軸を右クリックし、軸の書式設定から項目を逆順にする設定を確認します。
上から重要な項目を表示したい資料では、並び順を整えるだけで読みやすさが大きく変わります。
データラベルと基準線の追加
重ねた横棒グラフでは、前面の値だけではなく、背景の目標値も知りたいことがあります。
前面系列にはグラフ要素の追加からデータラベルを設定し、値を棒の外側または内側に表示します。
背景系列のデータラベルは情報量が増えすぎる場合があるため、必要な場合だけ表示するのが無難です。
ラベルはすべてに付けるより、伝えるべき系列に絞る方が見やすいでしょう。
目標値が全項目で共通の場合は、目標を別系列として棒にするより、折れ線や図形の線で基準を示す方法もあります。
【操作のポイント】横棒グラフでは、項目名の長さに応じてグラフの左側余白を広げます。
文字が省略される場合はグラフを横方向に広げるか、フォントサイズを少し下げて調整しましょう。
系列が重ならない場合の確認項目
続いては、設定しても棒グラフが重ならない場合の確認項目について解説していきます。
| 確認対象 | 確認内容 |
|---|---|
| グラフ種類 | 積み上げではなく集合棒を基準にしているか |
| 軸 | 主軸と第2軸の尺度が一致しているか |
| データ範囲 | カテゴリと系列が正しく認識されているか |
設定を変更しても見た目が期待通りにならないときは、グラフの構造を一つずつ確認すると原因を見つけやすくなります。
積み上げ棒グラフとの違い
積み上げ縦棒グラフは、複数の値を合計して一つの棒として表示するグラフです。
一方で系列の重なりは、複数の棒を同じ位置に配置して比較するための設定です。
合計を見せたいなら積み上げ、基準値と実績値を比べたいなら重なりという違いを押さえましょう。
例えば、部門別売上の構成比を示すなら積み上げ棒グラフが適しています。
予算と実績を比較するなら、背景に予算、前面に実績を置く重ね棒グラフが理解されやすい表現です。
主軸と第2軸の目盛り
第2軸を使って棒の幅を変えた場合、左右の軸の設定が異なると棒の高さが一致しません。
たとえば主軸の最大値が200、第2軸の最大値が300なら、同じ100という値でも表示される高さは異なります。
棒の表示比率は、値を軸の範囲で割った割合によって決まります。
100という値でも、最大値200の軸では50パーセント、最大値300の軸では約33パーセントの高さになります。
比較用の重ね棒では、主軸と第2軸の最小値、最大値、主単位をそろえることが欠かせません。
設定後は第2軸の目盛線やラベルを削除すると、グラフがすっきりします。
行と列の切り替え
Excelが表を意図と違う向きに読み取ると、月が凡例になり、目標売上と実績売上が横軸になる場合があります。
この場合は、グラフのデザインタブにある行と列の切り替えをクリックします。
データの選択画面を開けば、系列名、系列値、横軸ラベルの範囲を個別に確認できます。
1行目をヘッダー、A列を項目名として整えた表にしておくと、グラフ作成時の認識ミスを減らせます。
【操作のポイント】グラフの設定を直す前に、元データの空白行、結合セル、文字列として入力された数値がないか確認します。
表を整えることが、グラフを正しく重ねる近道です。
まとめ エクセルの棒グラフを重ねる方法
Excelの棒グラフを重ねるには、集合縦棒または集合横棒グラフを作成し、データ系列の書式設定で系列の重なりを調整します。
基本は系列の重なりを100パーセントに設定することです。
目標や予算のような基準値は淡い色で太く見せ、実績や現在値は濃い色で前面に置くと、グラフの意味が伝わりやすくなります。
棒グラフと散布図をくっつける場合は、組み合わせグラフを使い、単位が異なる散布図の系列は第2軸に設定します。
散布図の点を棒の中央に合わせたい場合は、1行目をヘッダーとした表で連番のX値列を作り、=ROW()-1のような数式をオートフィルでコピーすると便利です。
重ならない、位置がずれる、高さが合わないと感じたときは、グラフ種類、系列の順序、行と列の切り替え、主軸と第2軸の尺度を確認しましょう。
目的に合わせた重ね方を選び、数値が読み取りやすいExcelグラフに仕上げていきましょう。
