Excelで売上と利益、件数と金額のように単位や数値の大きさが異なるデータを棒グラフにすると、小さい数値の棒が見えにくくなることがあります。
このような場合は、第2軸を設定した複合グラフに変更すると、異なる単位のデータを1つのグラフで比較しやすくなります。
ただし、棒グラフ同士を2軸にすると重なりやすいため、系列の重なりや要素の間隔も調整することが大切です。
・数値の桁が大きく違う場合は第2軸を使う
・棒グラフを横に並べたい場合は系列の重なりを調整する
・単位が違うデータは軸ラベルと凡例を明確にする
この記事では、Excelの棒グラフを2軸にする操作、横に並べるための設定、単位が異なるデータを見やすく表示するコツを解説します。
売上だけでなく、達成率や件数などを同時に確認したい場面で役立つ内容です。
エクセルの棒グラフを2軸にする方法
| 月 | 売上 | 広告費 |
|---|---|---|
| 4月 | 1250000 | 85000 |
| 5月 | 1480000 | 96000 |
| 6月 | 1320000 | 78000 |
それではまず、売上と広告費のような異なる桁の棒グラフに第2軸を設定する方法について解説していきます。
第2軸とは、グラフの右側に追加するもう1本の目盛り軸です。
左側の第1軸を売上、右側の第2軸を広告費に割り当てると、それぞれの棒の高さを適切な範囲で表示できます。
元データと集合縦棒グラフの作成
最初に、1行目を見出しとした表を用意します。
A列には月、B列には売上、C列には広告費を入力する構成です。
売上が百万円単位、広告費が数万円単位のように大きく離れていると、通常の集合縦棒グラフでは広告費の棒が極端に低く表示されます。
グラフ化する範囲には、項目名を含めて選択することがポイントです。
表のA1からC4をドラッグで選択し、挿入タブから縦棒または横棒グラフを選びます。
横方向に比較したいときは集合横棒、月ごとの変化を上方向に見せたいときは集合縦棒が扱いやすいでしょう。
作成直後は2系列とも左側の第1軸を使用しているため、広告費の棒がほぼ見えない状態でも問題ありません。
次の操作で広告費だけを第2軸に移します。
系列の第2軸への変更
続いては、広告費の棒を選択して第2軸へ変更する操作を確認していきます。
グラフ内の広告費の棒を1回クリックすると、広告費系列全体が選択されます。
選択した棒を右クリックし、データ系列の書式設定を開きます。
表示された作業ウィンドウの系列オプションで、第2軸を選択してください。
これでグラフの右側に広告費用の目盛りが現れ、広告費の棒も比較できる高さになります。
第2軸に設定するのは、一般的に数値の小さい系列です。
売上と広告費のどちらを右軸にするか迷った場合は、金額が大きい主指標を左軸、補助的に見る指標を右軸に置くと読み手が理解しやすくなります。
【操作のポイント】棒そのものを選択してから右クリックすると、グラフ全体ではなく対象系列の設定画面を開けます。
第1軸と第2軸の目盛り調整
続いては、左右の目盛りを見やすく整える方法を確認していきます。
左側の軸をクリックして右クリックし、軸の書式設定を開きます。
最大値が自動設定のままでも使用できますが、月ごとの比較を続ける資料では最大値を固定した方が見やすい場合があります。
たとえば売上が100万円から160万円の範囲なら、最大値を2000000に設定すると余白を含めて安定した表示になります。
広告費が70000から100000程度であれば、右側の第2軸は最大値を120000程度にすると棒の違いを確認しやすくなります。
売上の表示例
最小値 0
最大値 2000000
主単位 500000
広告費の表示例
最小値 0
最大値 120000
主単位 20000
左右の軸は別々の尺度であるため、棒の高さだけで大小関係を判断しないことも重要です。
【操作のポイント】資料ごとに軸の最大値が変わると比較しにくくなるため、定例レポートでは目盛りを固定します。
棒グラフを横に並べて重ならない表示にする設定
| 商品 | 販売数 | 返品数 |
|---|---|---|
| 商品A | 420 | 18 |
| 商品B | 380 | 25 |
| 商品C | 510 | 14 |
続いては、第1軸と第2軸の棒が重なってしまう場合に、横に並べて表示する考え方を確認していきます。
Excelでは、異なる軸に配置した縦棒系列は、既定の状態で同じ位置に重なって表示されることがあります。
第2軸の棒を使うだけでは、自動的に横並びにはならない点に注意が必要です。
系列の重なりと要素の間隔
棒が重なったときは、いずれかの棒を右クリックし、データ系列の書式設定を開きます。
系列オプションには、系列の重なりと要素の間隔という項目があります。
系列の重なりを0パーセント付近にすると、同じ軸にある複数の棒は並んで表示されます。
要素の間隔は、項目ごとの棒のまとまりの間にある余白です。
数値を小さくすると棒が太くなり、大きくすると棒が細くなって間隔が広がります。
棒が密集して読みにくい場合は、要素の間隔を80パーセントから120パーセント程度で調整します。
カテゴリ数が少ないグラフでは、棒を少し太くすると見栄えが整います。
【操作のポイント】系列の重なりは棒同士の位置、要素の間隔は項目ごとの余白を決める設定です。
第2軸の棒を細くする調整
続いては、重なりを視認しやすくするために第2軸側の棒を細くする調整を確認していきます。
第1軸と第2軸の棒は別のグループとして扱われるため、片方だけの棒の太さを調整できる場合があります。
第2軸の棒を選択し、データ系列の書式設定で要素の間隔を大きくします。
これにより第2軸の棒が細くなり、前面または背面にあっても主系列との区別が付きやすくなります。
主となる系列を太く、副次的な系列を細くする設計は、売上と予算、販売数と返品数のような比較に向いています。
色も主系列は濃い青や緑、副系列は淡い色にすると、凡例を見なくても役割を想像しやすくなります。
【操作のポイント】同じ色合いの棒を使うと2軸の意味が伝わりにくいため、系列ごとに明確な色の差を付けます。
集合棒グラフと複合グラフの使い分け
続いては、棒グラフを2軸にする前に確認したいグラフ種類の使い分けを解説していきます。
販売数と返品数のように両方とも個数であり、桁の差も小さい場合は、2軸を使わず集合棒グラフにする方が正確です。
2軸は便利ですが、左右で目盛りが違うため、棒の見た目の高さが比較を誤らせることがあります。
一方、売上金額と達成率、売上金額と来店者数のように単位が異なる場合は、複合グラフが有効です。
単位が同じなら集合棒、単位や桁が違うなら第2軸を含む複合グラフという考え方が基本になります。
【操作のポイント】棒を横に並べることが目的なのか、異なる単位を同時に示すことが目的なのかを先に決めます。
組み合わせグラフによる棒と折れ線の表示
| 月 | 売上 | 達成率 |
|---|---|---|
| 4月 | 1250000 | 92% |
| 5月 | 1480000 | 108% |
| 6月 | 1320000 | 97% |
続いては、棒グラフを横に並べるよりも見やすくなることが多い、棒グラフと折れ線グラフの組み合わせを確認していきます。
売上を棒、達成率を折れ線にすると、金額と割合の違いが一目で分かります。
達成率を計算する数式の例です。
1行目がヘッダーの場合、D2に次の数式を入力します。
=B2/C2
ここでB2を実績売上、C2を目標売上として、表示形式をパーセンテージに変更します。
組み合わせグラフへの変更
グラフを選択した状態で、グラフのデザインタブからグラフの種類の変更を選びます。
左側の一覧で組み合わせを選択すると、系列ごとにグラフの種類を指定する画面が表示されます。
売上は集合縦棒、達成率は折れ線を選択します。
達成率の行にある第2軸のチェックボックスをオンにしてください。
棒と折れ線を使い分けると、異なる単位の系列が重ならず、読み取る順序も自然になります。
【操作のポイント】割合の系列は折れ線と第2軸を組み合わせると、百分率の変化を追いやすくなります。
達成率の数式とオートフィル
続いては、達成率を計算してグラフに利用する数式を確認していきます。
D2セルに=B2/C2と入力すると、実績売上を目標売上で割った割合を求められます。
数式を入力したD2セルを選択し、セル右下のフィルハンドルを下方向へドラッグすると、D3以降にも数式をコピーできます。
この操作はオートフィルと呼ばれ、行番号に合わせて=B3/C3、=B4/C4のように参照先が自動で変わります。
割り算の結果は小数で表示されるため、パーセンテージ表示に設定することを忘れないようにしましょう。
目標が未入力の行では割り算エラーになる可能性があるため、必要に応じて=IFERROR(B2/C2,””)のようにエラー処理を加える方法もあります。
【操作のポイント】計算列もグラフ範囲に含めると、更新した売上や目標がグラフへ自動反映されます。
右軸のパーセンテージ表示
続いては、折れ線用の第2軸をパーセンテージ表示に整える方法を確認していきます。
右側の軸を右クリックし、軸の書式設定を開きます。
表示形式からパーセンテージを選び、小数点以下の桁数を0または1に設定します。
達成率の変動を詳しく見たい場合は、最小値を80パーセント、最大値を120パーセントのように設定する方法があります。
ただし、範囲を狭くしすぎると変化が実際以上に大きく見えることもあります。
目盛りの範囲を資料内で明示し、印象だけで判断されない表示を心掛けましょう。
【操作のポイント】右軸をパーセンテージにしたら、軸タイトルにも達成率パーセントなどの単位を入れます。
単位が違うデータを比較するグラフの工夫
| 部門 | 売上 | 来店者数 |
|---|---|---|
| 東日本 | 3800000 | 1240 |
| 西日本 | 4200000 | 1380 |
| 中部 | 3100000 | 960 |
続いては、円、個、人、パーセントなど単位が違うデータを2軸グラフで扱う際の工夫を解説していきます。
第2軸を設定しても、何を表している棒や線なのかが伝わらなければ、正確な比較にはつながりません。
軸タイトルと単位の明記
グラフ要素の追加から軸ラベルを表示し、左軸には売上円、右軸には来店者数人のように入力します。
単位をタイトルや軸に直接書くことで、閲覧者が左右の目盛りを取り違えるリスクを減らせます。
第2軸を使用するグラフでは、軸タイトルが説明の中心になります。
売上を千円単位で表すなら、元データを1000で割った列を作る方法もあります。
たとえばB2に売上金額がある場合、千円単位の表示用セルには次の数式を入力できます。
=B2/1000
グラフの軸タイトルには売上千円と記載すると、目盛りと数値の関係が明確になります。
【操作のポイント】凡例だけに頼らず、左軸と右軸の両方へ単位を書きます。
データラベルと凡例の整理
続いては、棒や折れ線の内容をさらに分かりやすくするデータラベルの設定を確認していきます。
グラフを選択し、右上のグラフ要素からデータラベルにチェックを入れると、各棒の近くに数値を表示できます。
数値が多いグラフでは、すべてのラベルを表示するとかえって読みにくくなることがあります。
その場合は、重要な系列だけにデータラベルを追加する方法が有効です。
凡例の系列名は、売上や広告費ではなく、売上円や広告費円のように単位まで含めると親切です。
元表の1行目に入力した見出しは凡例へ反映されるため、グラフ作成前に列名を整えておくと作業が減ります。
【操作のポイント】ラベルを増やしすぎず、グラフで伝えたい指標を優先して表示します。
2軸グラフで誤解を防ぐ確認
続いては、2軸グラフを完成させる前の確認項目を解説していきます。
左右の軸で単位が違う場合、左側の棒より右側の棒が高く見えても、値そのものが大きいとは限りません。
グラフの近くに単位の説明を添え、必要なら元データ表も一緒に示すと誤解を抑えられます。
2軸グラフは相関の傾向を見るための表現であり、棒の高さだけで絶対値を比べるための表現ではありません。
社内報告や顧客向け資料では、軸の開始値、最大値、単位が意図したものになっているかを最後に確認しましょう。
【操作のポイント】左右の軸の数値範囲を変えた後は、グラフから受ける印象が変わっていないか確認します。
棒グラフが2軸でうまく表示されない原因
| 確認項目 | 症状 | 確認内容 |
|---|---|---|
| データ範囲 | 系列が表示されない | 見出しを含む範囲か |
| 軸設定 | 棒が低すぎる | 第2軸になっているか |
| グラフ種類 | 棒が重なる | 組み合わせを確認 |
続いては、棒グラフを2軸にしても見づらい、系列が表示されないといった場合の原因を確認していきます。
第2軸が表示されない状態
右側の軸が表示されない場合は、対象の系列が本当に第2軸に設定されているかを確認します。
グラフ全体ではなく、変更したい棒または折れ線を選択してから書式設定を開くことが必要です。
選択対象を間違えると、第1軸の設定だけを変更してしまうことがあります。
右側の目盛りが現れたかどうかを確認してから、軸の最大値を調整すると作業が確実です。
【操作のポイント】系列名をクリックして選択対象を確認し、第2軸のラジオボタンを選びます。
棒が完全に重なってしまう状態
2つの棒が同じ位置に重なる場合は、棒グラフ同士を別軸にしていることが原因の一つです。
比較の目的が単位の違いではなく、単純な横並びであれば、両系列を第1軸に戻して集合縦棒または集合横棒にします。
単位が違うため2軸が必要なら、片方を折れ線へ変更する方が読みやすい場合があります。
棒の前後関係を残す場合は、色の透明度や枠線を調整して、隠れている棒がないかを確認してください。
【操作のポイント】横並びを優先する場合は集合棒、単位の差を優先する場合は棒と折れ線の複合を検討します。
元データの空白と文字列
グラフの棒が途中で消える、意図しない値になる場合は、元データに空白や文字列が混じっていないかを確認します。
数値に見えても、先頭にアポストロフィが付いた文字列や、全角記号を含むデータは計算やグラフ表示に影響することがあります。
セルの表示形式を標準または数値に変更し、数式バーで実際の内容を確認しましょう。
売上の合計を作る場合は、1行目をヘッダーとした表で=SUM(B2:B13)のように入力します。
グラフの不具合に見えても、原因が元データの形式にあるケースは少なくありません。
【操作のポイント】グラフの設定を直す前に、数値セルが文字列扱いになっていないかを確認します。
まとめ エクセルの棒グラフを2軸にする方法
| 目的 | おすすめの表示 |
|---|---|
| 同じ単位の棒を比較 | 集合縦棒または集合横棒 |
| 金額と割合を比較 | 棒と折れ線の第2軸 |
| 金額と件数を比較 | 組み合わせグラフと軸タイトル |
エクセルの棒グラフを2軸にするには、対象のデータ系列を選択し、データ系列の書式設定で第2軸を選択します。
売上と広告費のように桁が大きく違う数値は、第2軸を使うことで両方の変化を確認しやすくなります。
一方で、棒グラフ同士を2軸にすると重なりやすいため、横に並べたいだけなら集合棒グラフを選ぶのが基本です。
単位が違うデータでは、棒と折れ線を組み合わせた複合グラフが見やすく実用的です。
左右の軸タイトル、単位、目盛りの範囲、凡例の名称まで整えると、誤解の少ないグラフになります。
グラフの目的に合わせて第1軸と第2軸を使い分け、数値の比較と傾向の把握に活用していきましょう。
