Excel ピボットテーブル 基本活用術

はじめに

経営ダッシュボードを作成するのに、いままではトップダウン形式の流れで解説してきた。

始めに使用するグラフとその解釈を行い、全体のグラフをまとめて経営ダッシュボードを示し、ExcelとPower BIによる作り方を解説し、パワークエリとExcelの連携でデータの作り方を例示し、最後に、経営ダッシュボードで使用したExcelの計算の種類とPower BI改訂版で使用したクイックメジャーを項目ごとに評価した。

今回は、Excelのピボットテーブルに関する計算の種類、階層構造、基礎分析、を扱う。経営ダッシュボードをExcelで行うにはピボットテーブルをどれだけ使い倒せるかが鍵となるからである。

扱うデータは、ダッシュボード グラフと解釈 で用いた製造業とダッシュボード 作り方 Excel編 のほか弁のものを、適宜使い分けて解説する。

計算の種類・階層構造・基礎分析

規定値の合計を平均や最大値などに変更する(ほか弁データ)

この集計結果は合計値か?

……まあ、そうですが……

大至急、平均値、最大値、最小値など合計値と並べて表を作れ!

……うぅぅぅ~……

これがはじめに提出した分類の表

ここで、ピボットテーブルのフィールドに売上を4つ追加する。

値に売上を4つ追加する。計5つ合計/売上ができる

売上を追加したピボットテーブル

ここで合計/売上2を選択して右クリックする。

値の集計方法 > 平均を選択して左クリックする

平均の小数点以下を2桁に直す

平均/売上の列全体を選択したら、書式設定を左クリックする

数値を選択し、小数点以下の桁数を2にしてOKを押す

このように桁数が揃う

同様の手順で、残りの合計/売上を最大値、最小値、個数カウントを行うと次のようなピボットテーブルになる。

出来上がったピボットテーブル

あとは、表題(一番上)の名前を変更する。変更の仕方は表題を選択して名前を変える。

名前の変更。ここでは行ラベルを分類にした例。リターンキーを押す

同様に、合計/売上の/売上を削除する。同じ操作をすべての表題に行う。

出来上がったピボットテーブル。これを提出する
販売エリアごとに商品の構成比を求める(製造業データ)

おい!

……はあ、なにか?……

販売地域ごとに各商品の売上比率を求めてくれ

……そうですか。いますぐですか?……

決まってんだろうが。ボーっとしてないで早くやれ

……う~……

ピボットテーブルのフィールドに販売網、商品分類、純売上を入れる

元となるピボットテーブル

ピボットテーブルの左上を選択して右クリック。計算の種類にカーソルを合わせて
列集計に対する比率を左クリック

出来上がったピボットテーブル。縦に見ていく
商品ごとに販売エリアの構成比を求める(製造業データ)

上と同様の手順で、行集計に対する比率、を左クリックする。

出来上がったピボットテーブル。横に見ていく
レギュラーを基準にして他の商品の比率を求める(ほか弁データ)

おい、うちの主力商品がレギュラーなのは知っているな?

……はあ、まあ

よし。レギュラーを中心に他がどれほど売れているのかを、四半期ぐらいまででいいから表にまとめろ

……ええと、そうですなあ……

ピボットテーブルのフィールドの各値に、このように入れる

元になるピボットテーブル

レギュラーの左端の数値を選択して右クリック。図のように選択する

図のように設定できたら、OKを押す

出来上がったピボットテーブル
イベントの項目を外す(ほか弁データ)

これをみるとイベントが異様に多いな

そうですね

よし、イベントを外して同じ表を作れ、今すぐにな

あぅーーーー

元となるピボットテーブル

分類の右の▼を左クリックする。

イベントのチェックを外し、OKを押す

出来上がったピボットテーブル
100%以上のものをハイライト表示する(ほか弁データ)

ごちゃごちゃしてわかんねえからよ、100%より大きい値をハイライト表示してくれ

……またっすか……

Excelメニュー > ホーム > 条件付き書式 > 新しいルール を選択

新しい書式ルールで

ルールの適用範囲は一番下をチェック
ルールの種類では上から二番目をチェック
ルールの内容は、セルの値、次の値より大きいは右の矢印をクリックして選択し、100%はキーボードから入力する
右下の書式をクリックして、色を選択する

この画面ができたらOKを押す

このようなピボットテーブルができたらOK
レギュラーとそれ以外に分ける(ほか弁データ)

おい、イベントは商品単価が高いから、他との比較にはならないよな

そう思います

イベントを外して、主力商品であるレギュラーとそれ以外の2つに分けて表を作れ

……いま、ですか?

いま、俺がいっただろう。わかってるよな

あぁうぅーーー

元となるピボットテーブル。イベントを外した状態

本来なら、ここで集計アイテムを選択して作成できるのだが、ピボットテーブルを作成するのに「データモデルから」を使用して作っているので、グレーアウトとなり選択できない

ただし、いい方法ではないのだが(つまり、ピボットテーブルではなく、最終的にテーブル表示になってしまうという意味で)やり方はある。

この部分をコピーして、別のセルに貼り付ける

次にこの部分をコピーして、先ほど貼り付けたセルの上に貼り付ける

ここまでの結果は次のようになる。

別のセルに貼り付けた結果

今度はレギュラーのみをコピーして上の表に貼り付ける。

結果として、このような表が出来上がる

次にピボットテーブルの分類のじょうごを選択し、サイドと丼物を選択する。

サイドと丼物だけにチェックを入れてOKを押す

ピボットテーブルは次のようになる。

このピボットテーブルができたら、総計を選択してレギュラーの下へコピーする

総計をサイド+丼物と書き換え、範囲を選択してCtrl+tでテーブルにする

結果は次のような表になる。

テーブル表示にした表。ただし、年度しか見出しにならない。また列〇は削除できない
分類を四半期に入力して変更する

賢いやり方ではないが、表だけはこれで完成できる。あとは、セルに関数を入れて計算すればいい。

前四半期との比較を求める(ほか弁データ)

前の四半期に比べた売上の増減表を作ってくれ

……数値ですか?それとも比率で?

決まってんだろッ。両方だ

あーーー、うーーー

【数値の場合 基準値との差分を使用】

元になるピボットテーブル

数値の左上を右クリックして、計算の種類 > 基準値との差分 を選択

基準フィールドに四半期、基準アイテムに(前の値)を右側にある矢印から選択して
OKを押す

出来上がったピボットテーブル

この表をみると2018年度は第三四半期がないので第四四半期は空欄になっている。2019年度~2021年度に関しては第一四半期の前の数値がないので空白になっている。

【比率の場合 基準値との差分の比率を使用】

元となるピボットテーブル

左上の数値を右クリックして、計算の種類 > 基準値との差分の比率 を選択する

右側の矢印から、基準フィールドに四半期、基準アイテムに前の値 を選択しOKを押す

出来上がったピボットテーブル

この表をみると2018年度は第三四半期がないので第四四半期は空欄になっている。2019年度~2021年度に関しては第一四半期の前の数値がないので空白になっている。

1千万円以上のものだけ抽出する(ほか弁データ)

おい、各商品で3年分の売上が1千万円以上のものを抽出してくれ

……、色分けじゃなくて?

何を聞いているんだッ。抽出といっただろうが!

……うぅぅーーー

元になるピボットテーブル

分類の右の▼を左クリックして、値フィルター > 指定の値以上 を選択し左クリック

真ん中の四角に1千万と入力し、OKを押す

1千万円以上の商品を抽出したピボットテーブル
トップ5を抽出する(ほか弁データ)

おい、うちの商品のトップ5を抽出してくれ

はあ、それなら……

あと、ドンケツ5を抽出するのも忘れるなよ

……それもですか……

【トップ5】

元になるピボットテーブル

分類の右の▼を左クリックして、値フィルター > トップテン を左クリック

このような画面ができたらOKを押す

トップ5を抽出したピボットテーブル

【ドンケツ5】

トップ5と同じピボットテーブルから出発して同じ手順で行く。違う箇所は次の通り。

上位から下位に変わっている。他は変更なし

出来上がったドンケツ5のピボットテーブル
特定の地域だけを抽出する(製造業のデータ)

おい、特定の地域だけ選択して商品の売上を表示する表を作れ!

……いま、ですよね?

おれの言うことは、いつでもいますぐなんだよ

う、ぅ、ぅ、……

ここで、ピボットテーブル作成範囲を3行ほどあけてA4から始める。2行目に販売網を設定するためである。

フィルターに販売網を入れると、自動的に右のようなピボットテーブルができる

あとはAllの右下の▼で地域を選ぶと、自動的にピボットテーブルが変化する。

お知らせ

今回でいったん経営ダッシュボードは終了となります。

そこで、ここで使用したほか弁データ、製造業データ、薬局データの3つを配布いたします。

サイドバーのお問い合わせから、件名と内容に「データ希望」と書いてください。名前はハンドルネームで結構ですが、メールアドレスだけは正確にお願いします。

これによって個人情報の収集・不正利用・悪用など一切行いません。

また先着順で順次メールを返送しますので、時間がかかる場合があります。悪しからずご了承ください。

使い方は、各ホルダーにテキストファイルで送ったデータを入れ、フォルダー接続をして、経営ダッシュボードを作成してください。

改変する場合は、Excel自体にテキストファイルを読み込ませて、商品(12個)を置換して、あとは「データ 作り方 パワークエリとExcelの連携」を参照しながら、新しいデータを作成してください。

サイトご利用方法

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

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