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

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

【Excel】縦に並んでいるデータはグラフが作れないのでピボットテーブルで作り直せ!

Excelの挿入タブには各種グラフのボタンが用意され、ているため、「Excelのグラフは、Excelシートに表を作ってボタンを押せば簡単にできる」と勘違いしている初心者が多いようです。そんな簡単にグラフが作れたら苦労はしません。それは、「どんな主張をするためにグラフを作るのか」はExcelが決めることではなく、グラフを作る本人が決めることからです(参考:【Excelグラフ】「見やすい」「分かりやすい」とかいう以前の問題)。

特に、数値データを縦に並べて入力した表は、1系列グラフしか作ることができないので、仮に分類を入力したとしても、Excelが勝手に判断して分類ごとに分けて、複数系列のグラフを作ってくれるわけではありません。

Excelが項目と系列をどのように判断してグラフを作っているのかが分かれば、正しい表の作り方も分かるはずです。そこで、今回は、縦に並んでいる数値データはなぜ1系列のグラフしか作れないのか、ピボットテーブルを用いて複数系列のグラフを作る方法等について出題します。

目次

1.系列について

1系列グラフと複数系列グラフの違いについて、詳しくはこちらの記事をご覧ください。

 

2.項目と系列の数

問題

ある会社は札幌を本社とし、大阪、名古屋に支店がある。それぞれ第1~第4四半期の売上金額を入力した。セル範囲A1:C13を作成範囲とするグラフの項目と系列の数はそれぞれいくつか。

f:id:waenavi:20201015201009j:plain

 

解説

セル範囲A1:C13を選択します(参考:【Excelグラフ】何を比較して何を主張するために、どの範囲を選択するのか)。

f:id:waenavi:20201015201823j:plain

 

これをもとに集合縦棒グラフを挿入したとします。棒の色は1種類で、12本の棒が並びます。

f:id:waenavi:20201015202346j:plain

 

項目軸は、各支店でグループ化されています。これを「複数レベルの項目軸ラベル」といいます。

f:id:waenavi:20201015202434j:plain

 

元の表の選択範囲には、数値が12行1列で入力されています。

f:id:waenavi:20201015202704j:plain

 

この数値を中心にして考えると、左側に12個の項目、上には1個の項目が入力されています。

f:id:waenavi:20201015203246j:plain

 

このような表を入力すると、作成されるグラフも「12×1」または「1×12」の形式になります。

  • 項目軸に12個の項目が並び、1色の棒が12本(1系列のグラフ)
  • 項目軸に1個だけ項目があって、12色の棒が1本ずつ(12系列のグラフ)

この切り替えは「行列の切り替え」によって行います(参考:【Excel】複数系列のグラフで「行列の切り替え」をするのはなぜか?)。

f:id:waenavi:20201015203916j:plain

 

このうち、1系列のグラフについて、「グラフの種類の変更」によって、積み上げ棒グラフにしてもこの形は変わりません。

f:id:waenavi:20201015204110j:plain

 

積み上げ棒グラフというのは、それぞれの項目に対して、系列が複数あった場合に1本の棒につなげるグラフです(参考:【Excel】積み上げ棒グラフと区分線、合計の表示、100%積み上げ)。12項目・1系列のグラフの場合、1個の項目に対して棒が1本しかないので、積み上げることができないのです。

f:id:waenavi:20201015204346j:plain

 

また、12系列のグラフを積み上げグラフにすると1本になります。12個の系列が1本にまとまるだけであって、それぞれの支店ごとにまとめることはできません。

f:id:waenavi:20201015210157j:plain

 

このことはグラフの種類を問いません。例えば、折れ線グラフにしても1本の折れ線が描画されるだけで、店舗別・四半期ごとに折れ線を分けることはできません。

f:id:waenavi:20201015210352j:plain

 

元の表が「12×1」であれば、できあがるグラフも「12×1」になります。

f:id:waenavi:20201015210856j:plain

 

3.リスト形式にするのは結構ですが・・・

問題

セル範囲A1:C13を作成範囲とするグラフの項目と系列の数はそれぞれいくつか。

f:id:waenavi:20201015211053j:plain

 

解説

データを入力する場合、セルの結合をせず、リスト形式の表を作るのが正しいです(参考:【神Excel】8個の基本パターンで完全習得「リスト形式」の教科書)。この形式はデータを格納するのに適しています。しかし、グラフの作成には適していないことに注意しなければなりません。

セル範囲A1:C13を選択して、集合縦棒グラフを挿入します。さきほどの問題と同じように12項目×1系列のグラフになります。

f:id:waenavi:20201015211425j:plain

 

折れ線グラフにしても同じです。

f:id:waenavi:20201015211508j:plain

 

作った表が「12×1」であれば、できあがるグラフも「12×1」になります。

f:id:waenavi:20201015212013j:plain

 

<補足>支店名の入力を1つにすれば、複数レベルの項目軸ラベルになりますが、項目軸の表示方法が変わるだけで、グラフの形が変わるわけではありません。

f:id:waenavi:20201015211615j:plain

 

4.重なったグラフにするにはどうすればよいか

問題

さきほどの問題で、支店ごとに、第1~第4四半期の売上をくっつけて表示するにはどうすればよいか。また、第1~第4四半期を折れ線グラフにして、札幌・大阪・名古屋の3本の折れ線を重ねるにはどうすればよいか。

f:id:waenavi:20201015212536j:plain

 

解説

Excelのグラフは、表の形、特に数値データの並び方を自動的に認識して作成されます。

f:id:waenavi:20201015213230j:plain

 

分類を入力したからと言って、分類を認識するわけではありません。Excelは人工知能ではないので、入力されている表が「グループごとに集約すべき表」かを勝手に判断することができないのです。分類を入力したら、分類ごとにグラフができるというのは大間違いです!

したがって、この表の形のままでは、支店または四半期ごとに縦棒をくっつけたり、支店または四半期ごとに折れ線をかいて重ねたりすることは不可能です。

f:id:waenavi:20201015213305j:plain

 

ここで、ピボットテーブルを挿入します(参考:【Excel】ピボットテーブルは集計の前の基礎トレーニングが大事!ピボットテーブル総まとめ)。

f:id:waenavi:20201015215149j:plain

 

行ラベルを支店名、列ラベルを四半期、値を売上の合計(合計とはいっても1つしかない)とします。

f:id:waenavi:20201015215222j:plain

 

数値データが3行4列になります(集計行除く)。

f:id:waenavi:20201015215313j:plain

 

ピボットグラフ(集合縦棒グラフ)を作ります(参考:【Excel】ピボットテーブルで度数分布表(ヒストグラム)を作成する方法)。

f:id:waenavi:20201015215400j:plain

 

支店ごとで、第1~第4四半期の売上をくっつけて表示することができました。

f:id:waenavi:20201015215442j:plain

 

これは3つの項目に対する4系列(4色)のグラフです。

f:id:waenavi:20201015220341j:plain

 

行列の切り替えをします。

f:id:waenavi:20201015215521j:plain

 

さらに、グラフの種類を変更して折れ線グラフにします。第1~第4四半期を項目として、3種類(3色、3系列)のグラフになります。

f:id:waenavi:20201015215635j:plain

 

これは4つの項目に対する3系列(3色)のグラフです。

f:id:waenavi:20201015220126j:plain

 

このように「3×4」の形で表を作れば、「4項目3系列」または「3項目4系列」のグラフになります。「3×4」または「4×3」の形で表を作らなければ、「3×4」または「4×3」のグラフにはならないのです。

f:id:waenavi:20201015220908j:plain

 

分類ごとにグラフを作りたければ、あらかじめ分類ごとに集約した表を作らなければなりません。

f:id:waenavi:20201015221357j:plain

 

リスト形式の表を複数系列の表に作り替えるにはピボットテーブルの機能を使うと便利です。ピボットテーブルは集計をするだけでなく、表の形式を変えるときにも使います。

f:id:waenavi:20201015221604j:plain

 

5.グラフの完成イメージから表の形を考える

問題

A列にコード番号(アルファベット1文字+数字3桁)、B列に数値を入力した。アルファベットごとに701~703の内訳を示す積み上げ縦棒グラフを作成しなさい。

f:id:waenavi:20201015221951j:plain

 

解説

グラフの完成イメージから、項目が4つ、系列が3つであることが分かります。したがって、数値データが4行3列または3行4列に並ぶ表を作成しなければなりません。

f:id:waenavi:20201015225226j:plain

 

ところが、元の表は数値データが12×1で並んでいるうえに、文字と数字3桁が1列で入力されています。

f:id:waenavi:20201016113843j:plain

 

そこで、まず、LEFT関数とRIGHT関数を用いて、文字と数字を分離します(参考:【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題)。

  • =LEFT(A2)
  • =RIGHT(A2,3)

f:id:waenavi:20201016120157j:plain

 

これをもとにピボットテーブルを作ります。

f:id:waenavi:20201016120326j:plain

 

行ラベルを文字のみ、列ラベルを数字3桁、値を数値の合計(合計とはいっても1つしかない)とします。

f:id:waenavi:20201016120400j:plain

f:id:waenavi:20201016120437j:plain

 

ピボットグラフ(積み上げ縦棒グラフ)を作ります。A~Dを項目名として、701~703の数値を積み上げて表示することができました。これは4つの項目に対する3系列(3色)のグラフです。

f:id:waenavi:20201016120543j:plain

 

6.グラフは勝手に集計してくれない

問題

3つの店の売上合計について、第1四半期~第4四半期の売上の推移をマーカー付き折れ線グラフで表しなさい。

f:id:waenavi:20201016121100j:plain

 

解説

元の表の数値データは12行1列で入力されています。この形の表からは「12×1」または「1×12」のグラフしかできません。グラフを作れば、勝手に分類ごとの合計を求めてくれるわけではありませんし、そんな機能もありません。

f:id:waenavi:20201016121437j:plain

 

ピボットテーブルを挿入します。行ラベルを四半期とし、値を売上の合計とします。4行3列の表になります。

f:id:waenavi:20201016121840j:plain

 

ピボットグラフ(マーカー付き折れ線グラフ)を作成します。4行3列の表からは「4×3」すなわち4項目3系列のグラフが作られます。

f:id:waenavi:20201016121918j:plain

 

7.日付+時刻の場合

問題

A列に日付と時刻(シリアル値)、B列に数値を入力した。時刻を項目とする折れ線グラフを3日分重ねて表示しなさい。

f:id:waenavi:20201016122027j:plain

 

解説

12行1列の数値データでは、4×3または3×4のグラフを作ることはできません。時刻を項目として、日付ごとにグラフを作るのであれば、時刻と日付を縦と横に並べて、4行3列または3行4列の表を作らなければなりません。

f:id:waenavi:20201016122553j:plain

 

ところで、Excelで「m/d h:mm」の形式で入力すると、「yyyy/m/d h:mm」の表示形式になります。

f:id:waenavi:20201016123648j:plain

f:id:waenavi:20201016123706j:plain

 

表示形式を標準にすると小数になります。これはシリアル値です(参考:【Excel】日付の「シリアル値」を本気で理解するには何を練習するべきか?)。

f:id:waenavi:20201016123721j:plain

 

シリアル値のうち、小数点の左側の整数部分は1900/1/1から起算した経過日数です。2020/7/1の場合は44013日目にあたります。

f:id:waenavi:20201016124003j:plain

 

通常、シリアル値は午前0時を基準としますので、0:00の場合、シリアル値は整数になります。

f:id:waenavi:20201016134415j:plain

 

しかし、0時ではない時刻を「yyyy/m/d h:mm」の形式で入力した時は、24時間=1として、時間を換算して小数で表します(参考:【Excel】時刻のシリアル値と時間計算の基本を理解するための練習問題)。例えば、「2020/7/1 6:00」は「2020/7/1 0:00」の6時間後(1日の1/4)なので、44013に0.25を加算した44013.25となります。

f:id:waenavi:20201016134306j:plain

 

シリアル値を整数部分と小数部分に分離すると、日付のシリアル値と時刻のシリアル値に分離したことになります。

f:id:waenavi:20201016134755j:plain

 

整数部分(日付)はINT関数で小数点以下を切り捨てます。時刻の部分がすべて0:00になります(時刻を表す部分のデータが無くなっていることを表す)。

  • =INT(A2)

f:id:waenavi:20201016144710j:plain

 

表示形式を「短い日付形式」にします。

f:id:waenavi:20201016144746j:plain

 

小数部分は元のシリアル値から整数部分を引けばよいです。

  • =A2-C2

f:id:waenavi:20201016144821j:plain

 

表示形式を「時刻」にします。これで日付+時刻のシリアル値を日付と時刻に分離できました。

f:id:waenavi:20201016144905j:plain

 

ピボットテーブルを挿入します。

f:id:waenavi:20201016144548j:plain

 

行ラベルを時刻のみ、列ラベルを日付のみとし、値を数値の合計とします。4行3列の表になります。

f:id:waenavi:20201016144955j:plain

 

ピボットグラフ(折れ線グラフ)を作成します。4行3列の表からは「4×3」すなわち4項目3系列のグラフが作られます。

f:id:waenavi:20201016145036j:plain

 

別解

日付+時刻のシリアル値から、時刻の部分のみを取り出すには小数部分だけを求めます。元の数値からINTを引く方法のほかに、MOD関数を使う方法もあります。

  • =A2-INT(A2)
  • =MOD(A2,1)

f:id:waenavi:20201016145142j:plain

 

ピボットテーブルを挿入します。行ラベルを時刻のみ、列ラベルを日付+時刻とし、値を数値の合計とします。4行12列の表になります。

f:id:waenavi:20201016145245j:plain

 

任意の列ラベルをクリックして、フィールドのグループ化をします(参考:【Excel】ピボットテーブルで度数分布表(ヒストグラム)を作成する方法)。

f:id:waenavi:20201016145325j:plain

 

日単位にします。

f:id:waenavi:20201016150911j:plain

 

これで1日単位の合計になります。ピボットグラフ(折れ線グラフ)を作成します。これで完成です。

f:id:waenavi:20201016151001j:plain

 

8.最後に:ピボットグラフの補足

最近のExcelのバージョンでは、元の表からダイレクトにピボットグラフを作る機能があります。挿入タブ-ピボットグラフボタンです。

f:id:waenavi:20201016154221j:plain

 

しかし、この操作方法であっても、ピボットグラフだけを作るのではなく、ピボットテーブルとピボットグラフを同時に作るだけなので、結局、上記とほぼ同じ操作をすることになります。上記の内容を理解しなくてもいいというわけではありません

f:id:waenavi:20201016154325j:plain

 


解説は以上です。


 


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