Excel ピボットテーブル 計算の種類 使い方とグラフ

はじめに

ダッシュボードの作成では、全体像を表示した後、それに向かってグラフを作成していくという形式をとった。

これから行うのは、ダッシュボードの作成に役立つかもしれないピボットテーブルの計算の種類を順次解説していく。中にはグラフ化が不可能な計算もある。それはその都度指摘していく。

解説順はExcelの計算の種類に出てくる項目順となる。またグラフを見やすくするために項目は絞っている。

前提条件

ここで扱うデータは、ダッシュボードの作成(Excel)で使ったものを使う。ただし、そのデータをダウンロードしてもらう方法がわからないので、各自が作成した独自のデータで構わないが、最低限、年度(年でもよい)、分類、売上が入っていることが最低条件となる。

またグラフを見やすくするために、月年度までは落とさず、四半期(ほとんど使わないが)までとする。

四半期データの作り方は

Power Pivotの管理を左クリック

Power Pivotの画面の予定表で列の追加を左ダブルクリックして四半期と入力する

=の後ろに

IF([月番号]<4,"Q4",IF([月番号]<7,"Q1",IF([月番号]<10,"Q2","Q3")))

をコピペすれば四半期が使えるようになる。(ここでは、「月の番号」を「月番号」としてある)

従って、下準備としては

データがエクセルに取り込まれていること

四半期の項目が予定表にくみこんであること

の二つとなる。

ピボットテーブルの作成に関しては、ダッシュボードの作成で何度も使っているので、ここでの説明は割愛する。

総計に対する比率

この計算は

全データに対する各セルのデータの構成比を出すために行う

ことである。

年度、分類、売上の3つのデータに対して、総計に対する比率をかけてみると次のようになる。

注目すべきは総計の縦と横の交差したセルだけ100%になっているという点である

例えば2018年度の総計(すべての分類)は全体の11.02%を占めているにすぎず、またイベントの総計(すべての年度)は全体の54.19%を占めていることを示している。

また各セル、例えば2018年度のサイドの0.78%は全体の中での構成比である。

これをグラフ化するには、横棒グラフが見やすい。

年度を中心にして分類の割合を見たい場合は

ダッシュボードと違い、色の変更、要らないものの削除は行っていない

となる。

これを分類を中心にして各年度がどれほど占めているかを調べたければ、ピボットフィールドで項目を入れ替える必要はなく、

グラフを左クリックして、ピボットグラフツール>デザイン>行/列の切り替え を実行

をやるだけでよい。そのときできたピボットテーブルとグラフは以下のようになる。

このように、グラフは行に対して機能することに注意すること

列集計に対する比率

これは

各列の総計を100%として、1列ごとの構成比を求めるため

の計算である。

列集計に対する比率。100%を生かすグラフはできない

この計算に対する適切なグラフはないといっていい。もちろんグラフは作成できるが、とくに情報が拾えるようなものはできない。

このようなデータの羅列を扱うときには、グラフ化ではなく、トップ5のセルの色分けがいい。

Excelメニュー>ホーム>条件付き書式 を左クリック

新しいルールを左クリック

図のようにチェックを入れ、上位の右を5に変更し、書式を左クリックする

書式設定の塗りつぶしを左クリックして、色を選択する(ここでは、黄色にした)

このようになったらOKを押す

このようになったらOKを押す

このように上位5位までのセルが黄色で表示される

このピボットテーブルをダッシュボードに貼り付けるかどうかは、各自の好みであるが、ダッシュボードの基本は個々のデータを見ていくものではなく、全体の動きの中で最善と考えられる方策を取ることにある。

列(ここでは年度)が100%となっているものをグラフ化するには次の計算でできる。

行集計に対する比率

これは

各行を100%にして、1行づつの構成比を求めるため

の計算である。

この図は分類項目を各年度別に比率を出して、100%としている

ピボットテーブルツール>ピボットグラフを左クリック

横棒グラフの100%表示のグラフを選択し、OKを押す

分類を100%とした各年度の構成比を示している

こんどは年度を100%として、各分類の構成比を出すには、先ほどやったように、グラフの行/列の入れ替えを実行すればよい。

年度を100%としたときの分類の構成比を表している

基準値に対する比率

これは

ひとつの行を100%としたとき、そのほかの行が何%になるのか

を計算するものである。

ダッシュボードを作成したときの商品伸び率がこれに相当する。

2018年度を基準とした場合。グラフの表示が逆になっている

グラフの表示が2018年度からにしたい場合は、グラフの左下の年度を左クリックして、出てくるメニューの降順を左クリックすればよい。

降順を左クリック

グラフは意図したものになり、年度ごとの各分類の伸び率を示すことができた。

分類の伸び率のグラフ

主力の分類(ここではイベント)を100%としたときに、各年度の伸び率をグラフ化する場合にも使用できる。

イベントを100%としたときの、各分類の比率

イベントを100%としたときに、そのほかの分類の年度ごとの構成比が把握できる。

親行集計に対する比率

ここからは、階層構造のピボットテーブルを扱うことになる。ピボットテーブルのフィールドの年度の下に四半期、分類の下に商品、値は売上だけでいい。

計算の種類で対応するものは、

列集計に対する比率⇔親行集計に対する比率

行集計に対する比率⇔親列集計に対する比率

となるのだが、ピボットテーブルの違いを把握して欲しい。

図1:列集計に対する比率のピボットテーブル

図2:親行集計に対する比率のピボットテーブル

この計算は

図1では、各商品ごとの列の合計が100%になるのに対して、図2では、各年度ごとの各商品の列の合計が100%になっている

ことに注意すること。

階層構造を取らない列集計に対する比率に適切なグラフはないと書いたが、この場合も同様で、図1,図2に総計100%を生かし切る適切なグラフはない。トップ10などセルごとに色分けした方がいい。

親列集計に対する比率

階層構造を使用する。ピボットテーブルは次のようになる。

図1:行集計に対する比率

図2:親列集計に対する比率

この計算は

図1が各行ごとの合計が100%になっているのに対して、図2は各分類の商品の合計が100%になっている

ことに注意すること。

図1では総計100%を生かし切るグラフは作成できたが、図2では作成できない。これもトップ10を表示させるしかない。

親集計に対する比率

これは

親列集計に対する比率、親行集計に対する比率を基準フィールド

によって計算するものである。

親集計に対する比率を左クリックすると次の画面がでてくるので、右端の矢印をクリックする。

右端の矢印をクリックすると選択項目が出てくる

ここで、分類を選択すれば親列集計に対する比率と同じピボットテーブルができる。年度を選択すれば、親行集計に対する比率と同じピボットテーブルができる。それ以外の四半期、商品(下位の層)を選択するとピボットテーブルは100%だけの表示になって使い物にならない。

これまで見てきたように、親列・親行集計に対する比率に100%を生かし切る最適なグラフはないので、親集計に対する比率もトップ10などを表示させて使う方がいい。

基準値との差分

これは

基準値との差を計算するもので、ここでは、2018年度の売上を基準にして2019年度~2021年度までの売上の推移

を計算するものである。

階層構造ではなかなか適したグラフができないので、年度、分類、値(売上)を使う。

計算の種類で、基準値との差分を左クリックすると次の画面がでる。

右端の矢印をクリックここでは基準フィールドに年度、基準アイテムに2018年度を選択してOKを押す

基準値との差分 ピボットテーブルと折れ線グラフ

基準値との差分の比率

これは

基準値との差の比率を計算するものであり、ここでは2018年度を基準として2019年度~2021年度までの比率

を計算している。

ここでも階層構造では適切なグラフができないので、年度、分類、値(売上)を使う。

基準値との差分の比率を左クリックすると

基準フィールドに年度、基準アイテムに2018年度を選択してOKを押す

基準値との差分の比率 ピボットテーブルと折れ線グラフ

累計

これは

期間を区切ったなかで、各期間の累計を出していく

計算である。

ここでは、四半期ごとに、分類の売上の合計値を計算する。ピボットテーブルと折れ線グラフは以下の通りである。

四半期ごとに合計値を計算し、年度でリセットされている

比率の累計

これは

ある期間の中での列の比率を、期間内で100%になるように比率を求めて累計していくため

の計算である。

ここでは、四半期ごとに各分類の比率の合計を出している。ピボットテーブルと折れ線グラフは以下の通りである。

四半期ごとに比率の合計を計算し、年度でリセットされている

おわりに

計算の種類では、このあと昇順、降順、インデックスとあるが、これはグラフと直接関係がないので割愛する。

手順の詳述はダッシュボードの作り方、Excel編ですでに累計と商品伸び率で解説しているので、計算の種類の意味とピボットテーブルとグラフを示すことを中心に行ったことをお断りしておく。

サイトご利用方法

次のページ・前のページを利用するよりも、グローバルメニュー(ヘッダー部分にある項目)をクリックしていただければ、その項目の全体像が一目でみることができ、クリックすればそのサイトへ飛びます。

google、yahoo、Bingなどで検索する場合、検索ワードは先頭に、孤立じじい、と入力しその後に、ダッシュボード or インテリア or 統計 or 談話室、とどれかひとつを入力すると、その検索サイトが上位表示されます。