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

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【Excelグラフ】2軸の複合グラフを作る方法とパレート図の作り方

通常のグラフの場合、縦軸と横軸が1本ずつで、グラフは1種類です。しかし、棒グラフと折れ線グラフの2種類にする場合には、縦軸を2本にすることが多いです。Excelでは、グラフの種類を2種類にする(複合グラフ)のと、数値軸を2本にする(2軸グラフ)のは別の操作が必要です。

 

そこで、今回は、数値軸を2本にして単位の異なるデータを1つのグラフで重ねて表す方法について出題します。

 

グラフ関連記事一覧

グラフをはじめから学習したい人のために「グラフ」の記事をまとめました
【Excelまとめ】グラフを作成するための「理論」を本気で勉強すべきである
https://www.waenavi.com/entry/20190706/1562421191

 

目次

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

1.2軸グラフ(降水量と気温)

問題

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

f:id:waenavi:20190216145525j:plain

 

また、問題点を指摘したうえで、次のように修正しなさい。

f:id:waenavi:20190216145530j:plain

 

解説

表を選択します。集合棒グラフを挿入します。

f:id:waenavi:20190216154848j:plain

 

平均気温を選択します。

f:id:waenavi:20190216154851j:plain

 

グラフの種類を変更して、マーカー付き折れ線グラフにします。

f:id:waenavi:20190216154855j:plain

 

複合グラフはグラフの種類を2種類にしたグラフです。異なる種類のデータであることを表すことができます。

f:id:waenavi:20190216154900j:plain

 

しかし、降水量と気温では単位が違うので、1つの軸で表すのはあまりよくないことです。

f:id:waenavi:20190216154905j:plain

 

また、軸の最大値が450になっているので、気温が上がっているのか下がっているのかが全く分かりません。

f:id:waenavi:20190216154910j:plain

 

気温を選択します。書式設定で第2軸にします。これで完成です。

f:id:waenavi:20190216154914j:plain

 

最大値や単位が異なる系列を1つのグラフにまとめるときは、それぞれに合わせて目盛りが必要です。

f:id:waenavi:20190216154918j:plain

 

通常の軸のことを主軸といい、もう1つの軸のことを第2軸といいます。このように、2本の数値軸のあるグラフを「2軸グラフ」といいます。

f:id:waenavi:20190216154922j:plain

 

複合グラフはグラフの種類が2種類以上あるものをいいます。2軸グラフは種類に関係なく(何種類でも良い)軸が2本あるグラフのことです。

f:id:waenavi:20190216154928j:plain

 

右のグラフは複合グラフであり、2軸グラフでもあります。

f:id:waenavi:20190216154931j:plain

 

別解

表を選択します。集合棒グラフを挿入します。

f:id:waenavi:20190216163309j:plain

 

平均気温を選択します。グラフの種類を変更して、折れ線グラフにします。このとき、第2軸のチェックを入れれば、2軸グラフになります。

f:id:waenavi:20190216163312j:plain

f:id:waenavi:20190216163316j:plain

 

2.比率を折れ線にする

(1)比率が見えなくなる

問題

次のデータを用いて、グラフを作りなさい。

f:id:waenavi:20190216145714j:plain

 

解説

グラフを作るまえに投票率を計算しておきます。

f:id:waenavi:20190216171741j:plain

 

投票率は投票者数の割合です。

f:id:waenavi:20190216171744j:plain

 

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

f:id:waenavi:20190216171747j:plain

 

有権者数と投票率では桁が違いますので、投票率が見えません。

f:id:waenavi:20190216171751j:plain

 

グラフの種類の変更で、投票率だけマーカー付き折れ線グラフ・第2軸にします。

f:id:waenavi:20190216171802j:plain

 

第2軸を選択します。

f:id:waenavi:20190216171806j:plain

 

最小値を0、最大値を1、目盛間隔を0.2にします。これで完成です。

f:id:waenavi:20190216171809j:plain

 

本来は時系列のないものに折れ線を使うことは適切ではありませんが、例外として、第2軸のデータであることを表すために、折れ線にすることがあります。

f:id:waenavi:20190216171812j:plain

f:id:waenavi:20190216171815j:plain

 

(2)比率の最大最小を変えて変化を見せる

問題

さきほどのグラフを次のように変えなさい。

f:id:waenavi:20190216145753j:plain

 

解説

有権者数を積み上げグラフで表すためには、投票した人と投票しなかった人の人数が必要です。

f:id:waenavi:20190219202316j:plain

 

1列挿入します。

f:id:waenavi:20190219202320j:plain

 

棄権者数をもとめます。

f:id:waenavi:20190219202324j:plain

 

データの選択で、範囲の選択をし直します。

f:id:waenavi:20190219202337j:plain

 

グラフの種類を積み上げ縦棒グラフにします。グラフの種類を変更します。投票率を、マーカー付き折れ線グラフに変更するのと同時に、第2軸にします。

f:id:waenavi:20190219202344j:plain

 

第2軸を選択します。最小値を0.5、最大値を0.7、目盛間隔を0.02にします。

f:id:waenavi:20190219202348j:plain

 

小数点以下桁数を0にします。これで完成です。

f:id:waenavi:20190219202351j:plain

 

3.累計構成比

問題

5つの事業部の営業利益が次の表のとおりであった。

f:id:waenavi:20190219203038j:plain

 

このデータを用いて、次のグラフを作りなさい。

f:id:waenavi:20190219203045j:plain

 

解説

まず、グラフを作る前に累計構成比を求める必要があります。累計構成比は累計の構成比のことです。

f:id:waenavi:20190219203048j:plain

 

累計を求めます。

f:id:waenavi:20190219203058j:plain

f:id:waenavi:20190219203101j:plain

 

累計の最後の数は営業利益を合計したものになります。構成比率を求めます。合計で割ります。

f:id:waenavi:20190219203108j:plain

 

パーセンテージにします。事業部と営業利益、累計構成比を選択します。

f:id:waenavi:20190219203112j:plain

 

とりあえず棒グラフを挿入します。グラフの種類を変更します。

f:id:waenavi:20190219203115j:plain

 

累計構成比を、マーカー付き折れ線グラフの第2軸にします。

f:id:waenavi:20190219203119j:plain

 

累計構成比の最大値はかならず100%なので、最大を1にします。これで完成です。

f:id:waenavi:20190219203126j:plain

 

4.パレート図

問題

次の表は、10種類の商品の単価と年間販売数を表している。

f:id:waenavi:20190219133413j:plain

 

(1)各商品の年間売上金額について、次のようなグラフを作りなさい。

(2)このグラフから分かることを簡潔に述べなさい。

f:id:waenavi:20190219133422j:plain

 

解説

(1)パレート図の基本

単価と販売数があるとき、売上金額は掛け算で求められます。

f:id:waenavi:20190219133442j:plain

 

表を選択します。並べ替えをします。

f:id:waenavi:20190219134159j:plain

 

売上金額の降順にします。

f:id:waenavi:20190219134203j:plain

 

累計を求めます。

f:id:waenavi:20190219134207j:plain

f:id:waenavi:20190219134211j:plain

 

そして、一番下にある合計を使って、構成比率を求めます。

f:id:waenavi:20190219134215j:plain

 

パーセンテージにします。商品番号、年間売上、累計構成比を選択します。

f:id:waenavi:20190219134218j:plain

 

とりあえず棒グラフを挿入します。

f:id:waenavi:20190219134222j:plain

 

グラフを変更します。累計構成比を、マーカー付き折れ線グラフの第2軸にします。

f:id:waenavi:20190219134225j:plain

 

累計構成比の最大値はかならず100%なので、最大を1にします。目盛間隔を0.1にします。

f:id:waenavi:20190219134228j:plain

 

目盛線は主軸(左側の軸)に合わせて引いているので、目盛線の間隔が必ずしも10%間隔になるとは限りません。この目盛線を消します。

f:id:waenavi:20190219134233j:plain

 

第2軸の横向きの目盛線にします。これで完成です。

f:id:waenavi:20190219134237j:plain

 

(2)パレートの法則

累計構成比を折れ線グラフで表すときには、通常大きい値から順に並べます。

f:id:waenavi:20190219205036j:plain

 

降順なので棒グラフにすると右に行くほど減少します。

f:id:waenavi:20190219205041j:plain

 

折れ線は増加し、棒グラフは減少するようにかきます。このようなグラフを、「パレート図」といいます。

f:id:waenavi:20190219205045j:plain

 

パレート図では、累計構成比の80%のラインが重要です。

f:id:waenavi:20190219205049j:plain

 

全体の効果の8割は、2割の要素によって生み出されている、という理論があります。これを「パレートの法則」といいます。世の中で起きる出来事には、かならず、大きな影響を及ぼす重要な原因があるはずだ、という考え方です。

f:id:waenavi:20190219205052j:plain

 

左の2つで全体の売上の80%を占めています。

f:id:waenavi:20190219205055j:plain

 

商品は10種類作っていますが、そのうちの2種類の商品がヒット商品で、会社の経営を支える重要な看板商品です(10種類中2種類ということは2割ということです)。

f:id:waenavi:20190219205058j:plain

 

全体の8割の売上を、2割の商品で生み出している、つまり、パレートの法則が成り立っています。

f:id:waenavi:20190219205102j:plain

 

このパレートの法則を応用して、80%の前後で3つのグループに分ける考え方があります。

f:id:waenavi:20190219205107j:plain

 

これをABC分析といいます。Aグループの2つは最も重要な商品なので、品切れや不良を起こさないように常に注意します。

f:id:waenavi:20190219205112j:plain

 

Bグループの2つはその次に重要な商品です。

f:id:waenavi:20190219205119j:plain

 

5.原点から始まるパレート図

問題

さきほどの問題のグラフを、次のように変更しなさい。

f:id:waenavi:20190219210347j:plain

 

解説

棒グラフを選択します。要素の間隔を0にします。

f:id:waenavi:20190219210116j:plain

 

棒グラフは10本の棒なので項目は10個です。

f:id:waenavi:20190219210120j:plain

 

折れ線グラフは原点から始まっています。0をふくめると、マーカーの数が11個になります。したがって、折れ線グラフの項目は11個です。

f:id:waenavi:20190219210123j:plain

 

グラフツールで第2横軸を追加します。第2軸がある場合に限り第2横軸を追加することができます。

f:id:waenavi:20190219210128j:plain

 

棒グラフは左と下の軸を使います。折れ線グラフは右の第2縦軸と、上の第2横軸を使います。

f:id:waenavi:20190219210131j:plain

 

1行挿入します。

f:id:waenavi:20190219210135j:plain

 

累計構成比のところに0と入力します。

f:id:waenavi:20190219210139j:plain

 

折れ線グラフを選択します。青色の枠を上に伸ばして0を含めます。

f:id:waenavi:20190219210142j:plain

 

これで下の横軸は10個、上の第2横軸は11個になります。

f:id:waenavi:20190219210146j:plain

f:id:waenavi:20190219210150j:plain

 

第2横軸を選択します。軸の位置を目盛にすると、目盛が幅いっぱいに広がるのと同時に、折れ線が原点から始まります。

f:id:waenavi:20190219210154j:plain

f:id:waenavi:20190219210157j:plain

 

折れ線グラフが原点から始まるパレート図をかくには、通常のパレート図をかいた後で、第2横軸の項目を11個にします。

f:id:waenavi:20190219210202j:plain

 

第2横軸を選択します。目盛と目盛ラベルをなしにします。これで完成です。

f:id:waenavi:20190219210206j:plain

 

6.円グラフの第2軸

円グラフやドーナツグラフで第2軸の設定をすると、円グラフが重なります。詳しくはこちらの記事をご覧ください。

 


解説は以上です。


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

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 9-33、9-34 の2本のYoutube動画を書き起こして、加筆修正したものです。

わえなび

 

わえなび

 

ちなみに、Excelのグラフの動画は全部で56本あります。すべて無料公開しています。ぜひご覧ください。

 

グラフ関連記事一覧

グラフをはじめから学習したい人のために「グラフ」の記事をまとめました
【Excelまとめ】グラフを作成するための「理論」を本気で勉強すべきである
https://www.waenavi.com/entry/20190706/1562421191

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]