わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】積み上げ棒グラフで異なる種類のデータ系列を横に並べて積み上げる


Excelの積み上げ棒グラフは、複数のデータ系列を1本の棒の形にしてつなげることによって、棒全体として合計値を表すグラフ(合計を比較するグラフ)です。

f:id:waenavi:20190515185239j:plain

 

しかし、収入と支出のように異なる種類のデータを隣に並べたほうが見やすいこともあります。この場合、積み上げるものが違いますので、「表の作り方」を工夫する必要があります。さらに、このグループが複数ある場合には、棒と棒の間を少しあける必要があります。この場合も「表の作り方」を工夫する必要があります。つまり、元の表の作り方次第で積み上げ棒グラフの形を変えることができるということです。

f:id:waenavi:20190515190051j:plain

 

そこで、今回は、異なる種類のデータ系列を積み上げ、さらに棒グラフの間隔を広げてグループ化する方法について出題します。

 

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.積み上げ棒グラフの範囲選択

積み上げ棒グラフの基本について、詳しくはこちらの記事をご覧ください。

 

2.積み上げ棒グラフの隣に別の棒グラフを並べる

問題

次の表は、3か月間の目標と実績をまとめたものである。

f:id:waenavi:20190514235751j:plain

 

次の2つのグラフを作りなさい。

f:id:waenavi:20190514235920j:plain

 

解説

目標と実績を比較するのであれば、3か月間の目標と3か月の合計だけを選択して比較します。

f:id:waenavi:20190515000749j:plain

 

縦棒グラフを挿入します。

f:id:waenavi:20190515000751j:plain

 

この実績を10月から12月までの3か月に色分けできないかを考えます。

f:id:waenavi:20190515000755j:plain

 

そこで、合計の目標と各月の実績を選択したとします。行列を切り替えます(参考:【Excel】複数系列のグラフで「行列の切り替え」をするのはなぜか?)。

f:id:waenavi:20190515000758j:plain

 

この場合、実績を積み上げて、合計の形にしないと比較はできません。

f:id:waenavi:20190515000802j:plain

 

グラフの変更をします。積み上げ棒グラフに変更すると、他の系列も積み上げグラフになります。

f:id:waenavi:20190515000805j:plain

 

このように、一部の系列だけを積み上げグラフにすることはできません。Excelでは集合棒グラフと積み上げ棒グラフを併用することはできません。

f:id:waenavi:20190515000808j:plain

 

1行ずつ選択します。行を挿入します。

f:id:waenavi:20190515000811j:plain

 

範囲選択して下に移動します。

f:id:waenavi:20190515000813j:plain

 

1列挿入して項目名を入力します。

f:id:waenavi:20190515000816j:plain

 

範囲選択して積み上げ棒グラフを作ります。

f:id:waenavi:20190515000820j:plain

 

これで完成です。

f:id:waenavi:20190515000822j:plain

 

積み上げグラフは横に並んでいる系列を積み上げます。

f:id:waenavi:20190515000826j:plain

 

実績だけを積み上げにして、2本の棒にするには、2行に分ける必要があります。

f:id:waenavi:20190515000829j:plain

 

3.異なる種類の系列を積み上げる

問題

次の表は、1か月分の家計をまとめたものである。

f:id:waenavi:20190514235843j:plain

 

次のグラフを作り、58923円の貯金ができることを示しなさい。

f:id:waenavi:20190515000005j:plain

 

解説

完成イメージを見ると、積み上げている棒は2本で収入と支出です。

f:id:waenavi:20190515001949j:plain

 

1列ではこのようなグラフはかけません。

f:id:waenavi:20190515001952j:plain

 

収入と支出の2つの列が必要です。

f:id:waenavi:20190515001956j:plain

 

それぞれ金額を並べるのと同時に、項目も1行目に入力します。これでグラフを作るための表は完成です。範囲選択して積み上げ横棒グラフを作ります。

f:id:waenavi:20190515002000j:plain

 

行列を切り替えます。

f:id:waenavi:20190515002003j:plain

 

縦軸を反転します(参考:【Excelグラフ】縦軸横軸の交点と反転、軸目盛ラベルの特訓問題_グラフの軸を徹底的に理解せよ!)。

f:id:waenavi:20190515002007j:plain

 

要素の間隔を狭くします(参考:【Excel3級レベル】棒グラフと折れ線グラフのグラフ要素の練習問題10問)。

f:id:waenavi:20190515002011j:plain

 

収入から支出を引いて利益があれば、その金額を支出に入れます(それぞれ合計を算出して引き算をする)。

f:id:waenavi:20190515002015j:plain

 

グラフの範囲を下に伸ばします。

f:id:waenavi:20190515002020j:plain

 

塗りつぶしを白色にして、枠線を破線にします。

f:id:waenavi:20190515002023j:plain

 

データラベルを表示します(参考:【Excel】文字列を挿入するグラフ要素(グラフタイトル・軸ラベル・データラベル・テキストボックス))。これで完成です。

f:id:waenavi:20190515002027j:plain

 

4.補足説明:利益は支出なのか?

さきほどのグラフで、貯金額(利益)を支出として入力しています。利益の金額は収入のように見えますが間違いです。今月末時点での利益は支出です。

それは、今月末の23時59分59秒の時点で余っているお金があれば、それは翌月の0時以降に使うからです。余ったお金は今月の収入ではなく「翌月の収入」です。今月末の23時59分59秒に余っているお金を財布の中からいったん外に出して、翌月の0時に臨時収入があったものと考えますので、今月は「支出」となります。これを繰り越しといいます。

 

5.棒グラフの色分け

(1)棒グラフの一部の棒だけ一括で色を変える方法

 棒グラフの色分けについては、別の記事で解説しています。

 

(2)斜めに色分けをする

問題

次の表は、ある中学校の学年別生徒数を示している。

f:id:waenavi:20190515004959j:plain

 

次の2つのグラフを作りなさい。

f:id:waenavi:20190515005002j:plain

 

解説

積み上げ縦棒グラフを挿入します。

f:id:waenavi:20190515005829j:plain

 

学年別で色分けされます。

f:id:waenavi:20190515005833j:plain

 

通常はこのようなグラフでよいですが、中学校の場合、1年ごとに進級するので、入学した年の1年生は、次の年には2年生になり、さらに次の年には3年生になって卒業します。

f:id:waenavi:20190515005836j:plain

f:id:waenavi:20190515005839j:plain

 

そこで、入学年度で色分けをすることを考えます。

f:id:waenavi:20190515005841j:plain

 

色分けをするにはこのような斜めの表にします。同じ学年のデータが同じ列になるようにします。

f:id:waenavi:20190515005844j:plain

 

積み上げ縦棒グラフをかきます。

f:id:waenavi:20190515005846j:plain

 

行列を入れ替えます。

f:id:waenavi:20190515005849j:plain

 

区分線を追加します(参考:【Excel】積み上げ棒グラフと区分線、合計の表示、100%積み上げ)。

f:id:waenavi:20190515005853j:plain

 

これで完成です。

f:id:waenavi:20190515005856j:plain

 

6.棒グラフの間隔を広げてグループにする

(1)棒グラフの間隔を自由に変えることができない理由

問題

縦棒グラフを作成しました。要素の間隔を狭くすると全ての項目の間隔が狭くなります。

f:id:waenavi:20190515181355j:plain

 

四半期ごとに少しだけ間隔を広げようとしましたができませんでした。

f:id:waenavi:20190515181358j:plain

 

Excelで一部の項目の間だけ間隔を変えることができない理由を述べなさい。

f:id:waenavi:20190515181402j:plain

 

解説

グラフを書くときに項目名や系列名を選択しなければ、連番が表示されます。

f:id:waenavi:20190515181726j:plain

 

グラフの縦軸と横軸は単なる直線ではなく、それぞれ原点から伸びる数直線です。

f:id:waenavi:20190515181729j:plain

 

そして、数直線では、整数は等間隔で並びます。この間隔が変わることはありません。

f:id:waenavi:20190515181732j:plain

 

グラフの項目軸も数直線です。数直線上に、等間隔に整数が並んでいます。その上に、棒グラフを書くため、棒グラフの間隔は等しくなります。

f:id:waenavi:20190515181736j:plain

 

項目名を選択してグラフをかくと、項目名に対して連番が振られます。この番号に従って項目軸を書くので、等間隔に並びます。

f:id:waenavi:20190515181738j:plain

 

この間隔が変えられないのは、連番で管理されている項目を、数直線上に並べるからです。

f:id:waenavi:20190515181742j:plain

 

(2)棒グラフの間をあける

問題

次の図のように、四半期ごとに間をあけたグラフにしなさい。

f:id:waenavi:20190515181408j:plain

 

解説

項目は連番で管理されています。

f:id:waenavi:20190515182602j:plain

 

棒と棒の間をあけるということは、その番号の上に棒を書かないということですから、欠番となります。

f:id:waenavi:20190515182604j:plain

 

つまり、1行あけるということです。

f:id:waenavi:20190515182607j:plain

 

4月、7月、10月の上に1行挿入します。これで完成です。

f:id:waenavi:20190515182612j:plain

 

(3)複数レベルのラベルと空白のグループ

問題

次のグラフをかきなさい。

f:id:waenavi:20190515181415j:plain

 

解説

項目を2列として選択します。縦棒グラフを挿入します。

f:id:waenavi:20190515183104j:plain

 

要素の間隔を100%にします。

f:id:waenavi:20190515183108j:plain

 

項目名が2行以上の場合、グループの区切りが表示されます。このような表示を「複数レベルのラベル」といいます。

f:id:waenavi:20190515183112j:plain

 

複数レベルの場合も間をあけるには行を挿入します。行を挿入すると四半期の文字の位置がずれます。

f:id:waenavi:20190515183114j:plain

 

そこで、第1四半期と第2四半期の間にあるセルに、スペースを入力します。

f:id:waenavi:20190515183117j:plain

 

空白のグループができます。

f:id:waenavi:20190515183121j:plain

 

同じように空白を入力します。これで7つのグループができて、区切りが正しくなります。

f:id:waenavi:20190515183125j:plain

 

行を挿入してから、空白を入力して、空白のグループにします。

f:id:waenavi:20190515183130j:plain

 

7.元の表を変えたくなければグラフ作成用の表を作ればよい

問題

次の表とグラフを作りなさい。

f:id:waenavi:20190515183348j:plain

 

解説

完成イメージでは、売上だけの棒と、労務費と経費を積み上げた棒があります。このように異なる系列を積み上げている場合は、元の表を2列にします。

f:id:waenavi:20190515183831j:plain

 

元の表の形をそのままにしたい場合は、元の表をコピーして、リンク貼り付けをすればよいです。

f:id:waenavi:20190515183835j:plain

 

4月、5月、6月をそれぞれ、売上と費用の2列にします。

f:id:waenavi:20190515183841j:plain

 

積み上げ縦棒グラフを挿入します。

f:id:waenavi:20190515183844j:plain

 

系列の書式設定で要素の間隔を0にします。全部くっつきます。

f:id:waenavi:20190515183848j:plain

 

4月、5月、6月の間をあけるには、1列挿入すればよいです。これで間が空きます。

f:id:waenavi:20190515183851j:plain

 

5月の左のセルと、6月の左のセルにスペースを入力します。

f:id:waenavi:20190515183854j:plain

 

グラフ作成用の表を非表示にしたい場合は、データの選択のボタンを押します。「非表示の行と列のデータを表示する」のチェックを入れます。

f:id:waenavi:20190515183856j:plain

 

非表示にします。

f:id:waenavi:20190515183859j:plain

 

これで完成です。

f:id:waenavi:20190515183902j:plain

 


解説は以上です。


8.動画版はこちら(無料)

この記事は、わえなび実力養成講座「Excel新演習5」Program 5-11 と 5-16 の2本のYoutube動画を書き起こして、加筆修正したものです。

ブログ記事と動画解説で使用しているExcelファイルは、わえなび公式サイトで配布しています。「サンプルファイル(ZIP)」をダウンロードします。

 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ