エクセルでは、売上の金額と達成率、件数と平均値のように、単位も数値の大きさも異なるデータを一つのグラフで確認したい場面があります。
そのようなときに便利なのが、棒グラフと折れ線グラフを同時に表示する複合グラフです。
棒で数量や金額の変化を示し、折れ線で割合や推移を重ねることで、表だけでは気付きにくい傾向を視覚的に読み取りやすくなります。
棒グラフと折れ線グラフの組み合わせは、グラフの種類の変更から設定できます。
数値の桁が大きく異なる場合は、第2軸を使うことが重要です。
系列ごとにグラフの種類と軸を分けると、売上と達成率を見やすく同時表示できます。
この記事では、棒グラフと折れ線グラフを重ねる基本操作、2軸の設定、見やすいデザインへの整え方までを順番に解説します。
なお、サンプルデータは1行目が見出し行である前提で説明します。
エクセルで棒グラフと折れ線グラフを組み合わせる方法
それではまず、棒グラフと折れ線グラフを一つの図として作成する基本手順について解説していきます。
| 月 | 売上 | 達成率 |
|---|---|---|
| 4月 | 820000 | 82% |
| 5月 | 980000 | 98% |
| 6月 | 1120000 | 112% |
| 7月 | 1050000 | 105% |
データ範囲の選択
複合グラフを作る最初の作業は、グラフに含めるセル範囲を正しく選択することです。
上の例では、月、売上、達成率が入力されているA1からC5までを選択します。
1行目の見出しは系列名となり、A列の月は横軸の項目として扱われます。
空白の行や合計行まで選択すると、不要な項目がグラフに入ることがあります。
まずは明細データだけを範囲に含めることが、あとで修正を減らす近道です。
数値を文字列として入力していると、棒の高さや折れ線の位置が期待どおりにならない場合があります。
売上は数値形式、達成率はパーセント表示の数値形式になっているかも確認しましょう。
選択範囲の目安は、横軸にする項目列を1列、比較する数値列を2列以上です。
途中に空白列を置かず、見出しとデータを連続した表にしておくと、エクセルが系列を認識しやすくなります。
組み合わせグラフの選択
続いて、選択した表から複合グラフを挿入する操作を確認していきます。
リボンの挿入タブを開き、グラフのグループにある組み合わせグラフのボタンを選びます。
表示された候補から、集合縦棒と折れ線を組み合わせた種類を選択しましょう。
エクセルの版によっては、おすすめグラフを選んだあとにすべてのグラフタブから組み合わせを開く方法でも設定できます。
売上を集合縦棒、達成率を折れ線にするのが、金額と比率を比較する代表的な設定です。
最初に通常の棒グラフを作成してしまっても問題ありません。
グラフを選択してからグラフの種類の変更を開けば、あとから組み合わせグラフへ変更できます。
【操作のポイント】最初から完成形を求めず、いったんグラフを挿入してから系列ごとに種類を整えると、操作画面の違いによる迷いを減らせます。
系列ごとの種類の指定
続いては、売上と達成率の表示方法を個別に指定していきます。
グラフの種類の変更画面では、各系列の左側に系列名、右側にグラフの種類を選ぶ欄が表示されます。
売上の行では集合縦棒を選び、達成率の行では折れ線を選択します。
ここで達成率に第2軸を設定するかどうかは、売上との数値差を見て判断します。
売上が数十万から数百万円、達成率が80パーセントから120パーセントのような組み合わせなら、第2軸を使うほうが折れ線を読み取りやすくなります。
設定後にOKを押すと、棒と線が重なったグラフがシート上に表示されます。
【操作のポイント】系列名の取り違えを防ぐため、変更画面で売上と達成率の行を一つずつ確認してからOKを選びましょう。
第2軸を使った売上と達成率の表示
続いては、2軸を設定して数値の単位が異なる系列を見やすくする方法を確認していきます。
| 月 | 広告費 | 問合せ件数 |
|---|---|---|
| 4月 | 180000 | 46 |
| 5月 | 220000 | 63 |
| 6月 | 240000 | 58 |
| 7月 | 210000 | 71 |
第2軸が必要になる数値差
売上と達成率、広告費と問合せ件数のように、系列間で桁が大きく離れている場合には第2軸が役立ちます。
たとえば180000円と46件を同じ縦軸で表示すると、46件の折れ線はグラフ下部に張り付いたように見えます。
これはデータが間違っているのではなく、同じ目盛りを共有しているためです。
左側の主軸を金額用、右側の第2軸を件数や割合用に分けると、両方の変化を自然な高さで確認できます。
第2軸を使うときは、左右の軸の単位が違うことをグラフタイトルや軸ラベルで明確に伝えることが大切です。
軸の意味が分からないまま棒の高さと線の高さを単純比較すると、誤解につながるかもしれません。
主軸は売上金額や費用などの大きな数値に使います。
第2軸は比率、率、件数、平均単価などの比較的小さい数値に使います。
同じ単位で桁も近い系列なら、無理に第2軸を使う必要はありません。
グラフの種類の変更画面での設定
続いては、組み合わせグラフの画面から第2軸を追加する手順を確認していきます。
対象のグラフをクリックし、グラフのデザインタブからグラフの種類の変更を選択します。
左側の一覧で組み合わせを選ぶと、各系列のグラフ種類と第2軸のチェック欄が並びます。
折れ線にする系列の第2軸欄にチェックを入れて、OKを選択してください。
設定後、グラフ右側に新しい縦軸が表示されます。
右軸が0パーセントから140パーセント、左軸が0円から120万円というように、それぞれのデータに合った目盛りになっていれば成功です。
第2軸のチェックは、通常は折れ線系列に付けます。
棒グラフに第2軸を付けることも可能ですが、意図しない比較にならないよう表示の目的を先に決めましょう。
【操作のポイント】第2軸を設定した直後は、右側の目盛りがデータの単位と合っているかを必ず確認します。
第2軸の目盛りと表示形式
続いては、右側に表示された第2軸を読みやすく整える方法を確認していきます。
右側の縦軸をクリックして右クリックし、軸の書式設定を開きます。
達成率であれば、表示形式をパーセンテージにし、小数点以下の桁数を0桁または1桁に整えると見やすくなります。
最小値と最大値も自動任せではなく、比較したい範囲に合わせて調整できます。
たとえば達成率を80パーセントから120パーセントの範囲で比べたい場合、最小値を0.8、最大値を1.2に設定します。
パーセントの軸では、80パーセントは0.8、100パーセントは1、120パーセントは1.2として入力します。
これはエクセルがパーセントを小数の値として管理しているためです。
目盛りの幅を0.1にすると、10パーセントごとの線が表示されます。
軸の範囲を狭くしすぎると小さな差が大きく見えるため、資料の目的に応じて慎重に設定しましょう。
【操作のポイント】右軸の最小値と最大値を変更したら、グラフ全体の印象が必要以上に誇張されていないか確認します。
作成済み棒グラフへの折れ線の追加
続いては、すでに作成してある棒グラフに折れ線系列を追加する方法を確認していきます。
| 月 | 受注額 | 利益率 |
|---|---|---|
| 8月 | 760000 | 18% |
| 9月 | 890000 | 21% |
| 10月 | 940000 | 19% |
| 11月 | 1080000 | 23% |
データの選択から系列を追加する操作
すでに受注額だけの棒グラフを作っている場合でも、最初から作り直す必要はありません。
グラフをクリックし、グラフのデザインタブにあるデータの選択を開きます。
凡例項目の一覧で追加を選び、系列名には利益率の見出しセル、系列値には利益率の数値セル範囲を指定します。
たとえば系列名をC1、系列値をC2からC5に設定すると、利益率のデータがグラフへ追加されます。
この段階では新しい系列も棒として表示されることがありますが、次の操作で折れ線へ変えられます。
データの選択は、元のグラフを残したまま比較項目を増やせる便利な機能です。
【操作のポイント】系列値には見出しを含めず、数値が入った2行目以降のセルだけを指定します。
追加した系列の折れ線への変更
続いては、追加した利益率の系列だけを折れ線グラフへ変更していきます。
グラフを選択した状態でグラフの種類の変更を開き、組み合わせを選択します。
受注額は集合縦棒のままにして、利益率の行だけを折れ線に変更します。
利益率は金額と単位が異なるため、第2軸にもチェックを入れるのが一般的です。
設定を確定すると、棒グラフの上に利益率の推移を示す線が重なります。
売上や受注額は、結果の大きさを示す棒グラフに適しています。
利益率、達成率、成長率は、月ごとの上下や方向を追いやすい折れ線グラフに適しています。
この役割分担を意識すると、複合グラフの意味が伝わりやすくなります。
画面では、赤枠で示したグラフの種類の変更を選択し、利益率の系列を折れ線と第2軸に設定します。
【操作のポイント】系列を追加しただけでは棒のままなので、必ず組み合わせグラフの画面で種類を変更します。
データ範囲を広げたときの反映
続いては、新しい月のデータを追加した際にグラフへ反映させる考え方を確認していきます。
通常のセル範囲から作ったグラフでは、データを下の行に追加しても自動では範囲に含まれないことがあります。
この場合は、グラフのデザインタブからデータの選択を開き、グラフデータの範囲を追加行まで広げます。
月の範囲、棒グラフの系列値、折れ線の系列値を同じ最終行までそろえることが重要です。
たとえばデータをExcelテーブルとして整形しておくと、表の末尾に新しい行を追加したときにグラフ範囲も拡張されやすくなります。
毎月データを追加する集計表では、テーブル機能と複合グラフを組み合わせると更新作業を効率化できます。
【操作のポイント】月だけが増えて数値系列が増えていない状態にならないよう、横軸と各系列の行数をそろえます。
棒グラフと折れ線グラフを重ねる表示設定
続いては、棒と折れ線を見分けやすく重ねるための表示設定を確認していきます。
| 月 | 販売数 | 平均単価 |
|---|---|---|
| 1月 | 320 | 4800 |
| 2月 | 360 | 4650 |
| 3月 | 410 | 4900 |
| 4月 | 390 | 5100 |
棒の間隔幅の調整
棒グラフが太すぎたり細すぎたりすると、折れ線との重なりが見づらくなります。
棒のいずれかをクリックして右クリックし、データ系列の書式設定を開きます。
系列のオプションにある要素の間隔を調整すると、棒と棒のすき間を変更できます。
間隔幅を大きくすると棒は細くなり、折れ線やマーカーを確認しやすくなります。
反対に棒を強調したい場合は、間隔幅を小さくします。
棒と線を組み合わせるグラフでは、棒を少し細めにすると折れ線の推移が埋もれにくくなります。
【操作のポイント】数値が多いグラフほど、棒を太くしすぎず、横軸の項目名も読める余白を残しましょう。
折れ線の色とマーカーの設定
続いては、折れ線系列を目立たせる色とマーカーの設定を確認していきます。
折れ線をクリックしてデータ系列の書式設定を開くと、線の色、太さ、実線や破線、マーカーの種類を変更できます。
棒を青系にした場合は、折れ線をオレンジ系や赤系にすると役割の違いを直感的に伝えられます。
各月の数値を追わせたいときは、丸形のマーカーを表示すると便利です。
ただしマーカーを大きくしすぎると、月数が多いグラフでは線が見えにくくなります。
棒は淡めの色、折れ線は濃い色にすると前後関係が分かりやすくなります。
折れ線の太さは、通常の資料では2ポイント前後から試すと読みやすい傾向があります。
色だけに頼らず、マーカーや凡例も併用すると印刷時にも判別しやすくなります。
重要な指標を示す折れ線には、背景色と十分なコントラストがある色を選びましょう。
【操作のポイント】複数の折れ線を使う場合は、同系色を連続して使わず、凡例を見なくても区別しやすい配色にします。
データラベルと凡例の配置
続いては、数値の補足情報を表示するデータラベルと凡例の整え方を確認していきます。
グラフ右上のプラス記号からデータラベルにチェックを入れると、棒や折れ線の近くに値を表示できます。
すべての系列へラベルを付けると情報量が多くなりすぎるため、特に伝えたい折れ線だけに表示する方法も有効です。
凡例は、グラフの上部または下部に置くと、プロットエリアを圧迫しにくくなります。
系列名は売上、達成率のような短く具体的な名前にしておきましょう。
データラベルは、読者が表を見なくても重要値を把握できる反面、付けすぎるとグラフの視認性を下げます。
【操作のポイント】ラベルを追加したあとに文字が重なる場合は、表示する系列を絞るか、グラフ自体を少し大きくします。
複合グラフで使える集計式とデータ作成
続いては、棒グラフと折れ線グラフに使いやすい集計データと数式を確認していきます。
| 月 | 実績 | 目標 | 達成率 |
|---|---|---|---|
| 4月 | 820000 | 1000000 | 82% |
| 5月 | 980000 | 1000000 | 98% |
| 6月 | 1120000 | 1000000 | 112% |
達成率を求める数式
実績を棒、達成率を折れ線で表示する場合は、達成率の列を数式で計算します。
実績がB列、目標がC列、達成率がD列の場合、D2セルには次の数式を入力します。
=B2/C2
この式は、B2セルの実績をC2セルの目標で割り、目標に対してどの程度達成しているかを計算する式です。
たとえば実績が820000、目標が1000000なら、計算結果は0.82になります。
D2セルの表示形式をパーセンテージにすると、0.82は82パーセントと表示されます。
数式を入力したら、先頭セルの右下にあるフィルハンドルを下へドラッグしてオートフィルを実行します。
これにより、D3以降も各行の実績と目標を使った達成率に自動調整されます。
【操作のポイント】目標が空白または0の行があると割り算エラーになるため、グラフ化する前に元データを確認します。
前年比や前月比の計算
続いては、売上の棒グラフに成長率の折れ線を組み合わせる場合の数式を確認していきます。
前月の実績がB2、当月の実績がB3にあるとき、前月比をC3セルへ計算する式は次のとおりです。
=B3/B2-1
この数式では、当月の値を前月の値で割ってから1を引きます。
たとえば前月が820000、当月が980000の場合、結果は約0.195となり、パーセンテージ表示では約19.5パーセントです。
これは前月より19.5パーセント増えたことを示します。
前年比を求める場合も考え方は同じで、比較する前年同月のセルを分母に指定します。
比率を折れ線にすると、棒グラフだけでは分かりにくい伸び率の変化を追いやすくなります。
【操作のポイント】比較元となる前月データがない最初の行は、空欄にするなどして折れ線の始点を意図的に扱います。
エラーを表示しない数式
続いては、目標が未入力の行でもグラフを見やすく保つ数式を確認していきます。
単純に=B2/C2と入力すると、C列が0または空欄の場合にゼロ除算エラーが表示されます。
このような場合は、IFERROR関数を使う方法が便利です。
=IFERROR(B2/C2,””)
IFERROR関数は、計算結果がエラーになったときに、指定した値へ置き換える関数です。
上の式では、エラー時に空文字を返すため、セル上のエラー表示を抑えられます。
ただし空文字を含む系列の扱いはグラフ設定により変わることがあります。
必要なら、グラフのデザインタブからデータの選択を開き、非表示および空白セルの設定で空白セルを間隔として表示するか確認しましょう。
数式のエラーを隠すだけでなく、空欄が本当に未入力なのか、データ不備なのかを元表で確認する姿勢も大切です。
【操作のポイント】グラフ用の計算列では、エラー値を残さないようにすると、凡例や軸の表示が乱れにくくなります。
まとめ エクセルの棒グラフと折れ線グラフを組み合わせる方法
エクセルで棒グラフと折れ線グラフを組み合わせると、金額や件数の実績と、達成率や成長率などの推移を同時に確認できます。
まずデータ範囲を選択し、挿入タブまたはグラフの種類の変更から組み合わせグラフを選びます。
金額を棒、割合を折れ線に設定し、数値の桁が異なる場合は折れ線系列に第2軸を設定するのが基本です。
2軸を使えば、単位の違うデータも一つのグラフで無理なく比較できます。
棒の間隔、折れ線の色、マーカー、データラベル、凡例を整えると、報告書や会議資料でも伝わりやすいグラフになります。
達成率は実績を目標で割る数式、前月比は当月を前月で割って1を引く数式で作成できます。
データが増える表では、系列範囲の更新やテーブル化も意識しましょう。
棒と線それぞれの役割を分け、左右の軸の単位を明確にしたうえで、目的に合う見やすい複合グラフを作成してみてください。
