３種類の複合グラフを作る｜Excel

グラフ機能では「棒グラフと折れ線グラフ」の組み合わせたグラフをつくることができます。
今回は「棒グラフの１つは積み上げグラフで」という条件に対して作成したグラフの覚え書きです。

はじめに

・Excel：Microsoft 365　バージョン 2104　で作成しています
・記事作成時の情報です
・実際に作業した手順を記載しており、必ずしも効率的とは言えません
・グラフの作り方の記録なので依頼内容についての最適解ではないです
・ご質問をいただいても返信は難しいです
・noteに慣れていないので記事内を編集する場合があります

記事にした理由

最初に書いたとおり「覚え書き」です。
このグラフを作成するのに５件以上の記事を探して確認し、実際に記載通りに作業してみました。

しかしすでにメニューが変わっていたり、思った通りに出来なかったり、私の理解不足でなぜこの方法なのかがわからなかったりしました。

また作成する時になるべく迷わないようにしたいから。そして悩まれているかたにこの記事が役に立っていただけるとうれしいなと考えています。

完成したグラフ

棒グラフ／積み上げグラフ／折れ線グラフ　の３種類で構成されています。

グラフ作成の要望内容

ある日こんな感じの依頼が届きました。

・５種類の数値をグラフ化する
・AとBは「合算して棒グラフ」
・CとDは色分けの積み上げグラフ
・Eは折れ線グラフ
※数値や項目はダミーです

1.まずはグラフにしてみる

このままではグラフ用の表としては不便なのでグラフ化しやすいように表を変更してみました。
数値表とグラフを同時に載せる場合は、グラフ用に別表を作成します。

合算するという項目を合算して１行にしてみました。

グラフの選択範囲は上記の状態。
グラフを作成する時の範囲は四角の状態でないと正しく範囲を読み込んでくれない確率が高くなります。いや、怒られるかもｗ

メニューリボンから、
「挿入」→「組み合わせ」→「集合縦棒-折れ線」を選択

一応グラフの形になりましたが、DとEは折れ線グラフになっています。

グラフの余白部分をクリックし、リボンメニューから
「グラフのデザイン」→種類の「グラフの種類の変更」を選択
「グラフの種類の変更」画面を見ると系列名とグラフの種類が記載されています。

D列のグラフを「積み上げ縦棒」に変更します。

すると上のグラフがすべて「積み上げ縦棒」に変更されてしまいました。

そうなのです。棒グラフの種類は１つしか選べないのです。

試しに１つを「集合縦棒」に変更すると棒グラフに指定したものはすべて同じ種類のグラフに変更されました。

2.表の形を変更してグラフを作成する

このままではグラフができあがらないので表の形を変更します。

前の形からグラフ用に変更すると頭がこんがらがってしまうので、縦横の項目を入れ替えました。

縦と横の項目が変わっただけで中身は変わっておりません。

そして棒グラフごとに数値を振り分けます。
各月２行ずつにして、「A＋B」と「C」「D」を別の行にします。
折れ線グラフは「A＋B」と同じ行のままにします。

この状態にしてグラフを作成します。

やはり「D」は折れ線グラフになるのか。。
なので先ほどの方法でグラフの種類を変更します。
今度は「積み上げ縦棒」にします。

E列は「第2軸」にチェックを付けます。

棒グラフは理想の形になりました！
Eの折れ線が消えているのが気になりますけど。。

グラフの数値を分けることによって、２本の「積み上げ縦棒」を作成している形になります。
１行に１本作成する形です。
300＋0＋0 と 0＋80＋20 の２本の「積み上げ縦棒」が出来るのです。

2-1.折れ線グラフを探す

折れ線グラフが見えない原因を探します。
グラフ作成エリアの余白部分をダブルクリックすると「グラフ エリアの書式設定」が右側に開きます。
「グラフのオプション」→「系列"E"」を選択。

グラフに点が現れます。色がなくて見えていないようですが、数値の位置にいることは確かです。

「マーカー」をクリックすると「マーカーのオプション」が表示されます。

今回は「なし」にラジオボタンがあったので「組み込み」を選択しました。
わかりやすいように「種類」と「サイズ」も変更しました。

こちらで点が表示されますが。。線がない！

隣の「線」を見ても色がついているので見えるはずなのに見えない。。
後ほど変更しましょう。

これですべての項目がグラフに表示されたことになります。

3.グラフの隙間を調整する

２本のグラフを見ると隙間が空いていて月のまとまりがない感じに見えます。

２本のグラフの隙間を埋めて、月ごとにまとまって見えるようにします。

棒グラフのどちらかをクリック（先ほどの左側メニューを閉じてしまった場合はダブルクリック）し、右メニューが「系列のオプション」となります。

「要素の間隔」を「0％」にすると、棒グラフの幅が広がり、間隔がなくなりました。

しかし！
月のまとまりどころか、全部が一緒になっちゃったじゃない！

そうなのです！隙間を埋めてしまうとつながってしまうので、透明な棒グラフがもう１本隙間代わりに必要です。

4.表の変更再び

もう１本の「積み上げ縦棒」を作るために表を変更します。
各月１行ずつ増やします。数値は入れません。

改めて範囲を選択しなおすと、１本分は透明な「積み上げ縦棒」となります。

月ごとに固まって棒グラフが並んでいるように見えます。

下のラベルが縦書きになっているのは、１行目に「１月」と記載しているからです。

棒グラフの１本の幅が狭くなったことにより縦書きになってしまいました。
グラフを大きくすると解消されます。

グラフの横幅を広げてみました。ほぼ完成です。
表を作成するときは透明の「積み上げ縦棒」の分まで最初から作るほうが良いですね。

次に折れ線をつなげます。

5.折れ線グラフをつなげる

折れ線グラフが何故つながらないか。。。

棒グラフを作成したとき、空白も数値のひとつとしてカウントされているので、透明な「積み上げ縦棒」ができるのは理解しました。

それと同じ事で「折れ線グラフ」も空白をカウントしているのです。

つまり数値がないものはつなげることができないのです。

試しに空白に「0」を入れると「数値-0-0-数値-0-0」という感じで線がつながります。
でも数値のある点だけをつなぎたいですよね。

グラフエリアの空白部分をクリックして現れる３つの記号の一番下、漏斗マークを選ぶと値がずらりと並んだ窓が表示されます。
一番右下にある「データの選択...」をクリック。

「データ ソースの選択」窓が開きます。左下「非表示および空白のセル」をクリック。

「非表示および空白のセルの設定」窓が開きます。
「データ要素を線で結ぶ」を選択。

数値だけで線をつなげることができました！

この方法は他にも応用できます。

例えば、「年間で数値を折れ線グラフで表示しているが、数ヶ月数値が取れなかった（0かどうかもわからない）」という場合。

この方法を利用すると数ヶ月の間は空白にして途切れることも、あるいは途切れるのがいやで、0で埋めて0のラインに色をつけることもなくつなげてくれます。
※その場合は数値に必ずマーカーをつけることをおすすめします。

これで表は完成です。
次からは体裁を整えていきます。

6.体裁を整える

完成したけど、もうちょっと体裁を整えたいと考えました。

6-1.下のラベルを中央寄りにする

月を表すラベルが左寄りになっています。
もう少し真ん中に寄らないだろうか。。

もちろん、データから消して自分でラベルを貼るのも良いですが、毎月替えたり、大きさを変えたりした時に確認するのは面倒です。

表のラベル位置を変更します。
まずはすべてに月を入れ込んでいきます。

入れ込むとすべての位置にラベルが表示されます。

「１月１月１月」と並ぶと真ん中の１月以外は削除するのが良いとわかります。

真ん中の行以外の月表示を表から削除します。
※青いセルのみを残して削除。

月ごとの中央に下ラベルが表示されました。

一度すべての行に記載してから消しているという面倒な作業を行っています。

真ん中の行にのみ記載しなおして、上の行を消すという作業をするとグラフ範囲の認識がおかしくなってしまう場合があります。ご注意ください。

6-2.データラベルを表示する

グラフにはデータラベルを表示させたい場合があります。

グラフエリアの空白部分をクリックして現れる３つの記号の一番上、プラスマークを選ぶと「グラフ要素」が表示されます。
「データラベル」にチェックをつけます。

データラベルが表示されました。

データラベルの位置などを調整し、グラフタイトルや文字色、グラフの色などを調整して終了です。

まとめ

・複合グラフでも、棒グラフの種類は１種類しか選べない

・「棒グラフ」と「積み上げ縦棒」を同時に表示させるには表示させたい棒グラフごとに「積み上げ縦棒」グラフを作成するように表を作り替える

・折れ線グラフがつながらない場合は空白セルの扱いを指定しなおす

参考にさせていただいた記事

教えて！goo
「積立縦棒＋集合縦棒＋折れ線グラフの作り方」
https://oshiete.goo.ne.jp/qa/5381732.html

Be COOL Users Office
「棒グラフと折れ線グラフを重ねた複合グラフの作り方」
https://www.becoolusers.com/excel/combination-chart.html

よねさんのExcelとWordの使い方
「エクセル2010基本講座：積み上げ縦棒グラフと折れ線グラフの作成」
http://www4.synapse.ne.jp/yone/excel2010/excel2010_graph_fukugou2.html

Qiita
「Excelで集合縦棒・積み上げ縦棒・折れ線グラフを組み合わせる」
https://qiita.com/YamaAri/items/4f5836afadaf0ebcd86d

haku1569 Excel でらくらく データ分析！
「積み上げ縦棒と集合縦棒を組み合わせてみた / グラフ / Excel」
http://www.haku1569.com/article/436128816.html