Excel ピボットテーブル データベース化 小技集

はじめに

経営ダッシュボードを作成する際に、ピボットテーブルにデータをおとしてからグラフを作成したが、ここではピボットテーブルをデータベースとして扱う方法を解説する。

データベース化する前処理として、パワークエリでデータをリスト形式にし、パワーピボットでリレーションを組むことで、Accessと同様な処理をExcelで行うことができる。

ピボットテーブルをデータベース化するとはリレーションを組んだデータモデルに対して、経営ダッシュボードでスライサーで行ってきたことを、フィールドなどによって抽出し、データを表示させながら同時にグラフ化することにある。

出発点はすでにデータモデルとしてExcelに取り込みピボットテーブルを作成したところから解説をしていく。扱うデータは経営ダッシュボードでも使用したほか弁のデータである。年度4月始まりのデータモデルで今回は四半期のデータも取り扱う。

昇順・降順の並べ替え

金額の大きい順、小さい順に並べ替えるには、降順、昇順機能を使う。

始めのピボットテーブル。分類の小計がない

始めに分類の小計を表示する。

ピボットテーブルのセルを選択して、デザイン > 小計 から行う

小計が表示されたピボットテーブル。このテーブルで昇順・降順を行う

分類は昇順、商品は降順にしてみる。

分類の小計を選択し、右クリック。

並べ替えをクリックしてから昇順をクリックする

分類が昇順になったピボットテーブル。

次に商品を降順とする。

商品のひとつを選択して右クリック。

商品のひとつを右クリックして、並べ替え>降順をクリック

分類のなかの商品が降順になった。分類は昇順のまま

並べ替えはこれでいいのだが、問題は解除である。Excelには並べ替えの解除機能がついていないので、仕方なく、Excelメニューの上にある戻る矢印を押してもとの状態にする。

戻るボタンを押すか、Ctrl+zで行うかは好みの問題
分類を自由に並べ替えをする

分類の項目をクリックし、マウスポインタが矢印付き十字に変わったら、動かしたい場所へ移動(ドラック)すればいい。(✙のマークではない)

レギュラーと丼物をイベントの上に配置した図

独自のルールで分類を並べ替える方法もあるが、ドラックした方が早いので、この方法はやめた方がいい。

抽出

特定の文字を含むものを抽出する。

元表は分類を除いたもので行う

特定の文字、たとえば、最後の文字が弁で終わるものを抽出してみる。

行ラベルの右下の▼をクリックして、ラベルフィルター>指定の値で終わる

この画面がでたら、弁の文字を入れて、OKを押す

弁で終わる商品が抽出できた

抽出の場合は、解除機能があるので、それを使う。

行ラベルのじょうごをクリックする。

「商品からフィルターをクリア」をクリックする

元の表に戻る
階層構造になっている場合

行ラベルの▼をクリックする。

フィールドの選択で、右の下矢印をクリックして商品をクリックする

ラベルフィルターの指定の値で終わるをクリックする

弁を入力し、OKをクリックする

このように分類と商品が抽出される

解除をするときも、「フィールドの選択>商品をクリック>商品からフィルターをクリア」をクリックすればいい。

表示させたい分類名を抽出する

行ラベルの▼をクリックする。

フィールドの選択で分類とし、すべて選択のチェックを外して必要な分類にチェックを入れてOKを押す(ここではサイドと丼物を選択)

抽出した結果(分類と下位の商品名が表示される)

解除は行ラベルのじょうごをクリックして、「分類からフィルターをクリア」をクリックすればいい。

表示させたい商品名を抽出する

行ラベルの▼をクリックする。

フィールドの選択で右の矢印をクリックして商品をクリック。すべて選択のチェックを外して表示させたい商品名にチェックを入れOKを押す(ここではサラダ、とりめし、鮭弁)

抽出した結果(分類名と商品名が表示される)

解除は行ラベルのじょうごをクリックして、「商品からフィルターをクリア」をクリックすればいい。

特定の金額以上のものを抽出する

1千万円以上のものだけを抽出してみる。

抽出に使う元表

行ラベルの▼をクリックする。

値フィルターをクリックして指定の値以上をクリックする

真ん中の空欄に1千万円(10000000)と入力してOKを押す

1千万円以上の商品が抽出された

解除は行ラベルのじょうごをクリックして、「商品からフィルターをクリア」をクリックすればいい。

階層構造で商品が1千万円以下のものを抽出する

抽出する元表

行ラベルの▼をクリックし、フィールドの選択で右の矢印をクリックして商品をクリックする。

値フィルターをクリックし、指定の値以下をクリックしてOKを押す

空欄に1千万(10000000)を入力してOKを押す

1千万円以下の商品が分類名とともに抽出された

解除は行ラベルのじょうごをクリックし、フィールドの選択で商品をクリックして、「商品からフィルターをクリア」をクリックする。

クロス集計表から1千万円以下の商品を抽出する

クロス集計表の場合、1千万円以下の商品かどうかを判定するのは、右端の列の総計からである。

クロス集計表の元表

行ラベルの▼をクリックして、フィールドの選択で右の矢印をクリックして商品を選択し、値フィルターをクリックする。

指定の値以下をクリックする

空欄に1千万(10000000)を入力してOKを押す

抽出した結果(分類名と商品名、年度が表示される)

解除は行ラベルのじょうごをクリックして、フィールドの選択で商品をクリックし、「商品からフィルターをクリア」をクリックすればいい。

上位5位の商品名を抽出する

抽出する元表

行ラベルの▼をクリックして、値フィルターをクリックして、トップテンをクリックする。

抽出する過程の図

OKを押すと、上位5位までの商品が抽出できる。

上位5位の商品が抽出された

解除は行ラベルのじょうごをクリックして、「商品からフィルターをクリア」をクリックすればいい。

階層構造の下位1位を抽出する

行ラベルの▼をクリックして、フィールドの選択を右の矢印から商品にして、値フィルターをクリックし、トップテンをクリックする。

トップテンをクリックしたところまでの図

図のように右の矢印で選択する

抽出した結果

階層構造の場合、下位の商品の一番小さいものが抽出される。

階層構造のクロス集計表の上位2位の抽出

クロス集計表の場合、元表の一番右端の総計の列で判断される

クロス集計表の元表

行ラベルの▼をクリックして、フィールドの選択で商品をクリックし、値フィルターをクリックして、トップテンをクリックする。

このように操作をすると下記の図が出てくる

すべての分類の下位2位までの商品を抽出する。

抽出した結果

解除はいままでと同様な手順で行う。

抽出したデータに色をつける

任意の数値以上に色をつける

総計が邪魔になるので、総計をカットする。

ピボットテーブルのセルを選択>デザイン>総計>行と列の集計を行わない をクリックする。

このような手順になる

元表はこのようになる

データの範囲をすべて選択する

ホーム>条件付き書式>セルの強調表示ルール>指定の値より大きい をクリック

次の値より大きいセルの書式設定に2千万と入力しOKを押す

2千万より大きいセルに色がついた

解除の方法は、表のセル全体を選択して、ホーム>条件付き書式>ルールのクリア>選択したセルからルールをクリア をクリックする。

このような手順になる
階層構造のセルの強調表示

総計が邪魔なので、任意の数値以上に色をつける、と同様な手順でカットする。

元表はこれを使う

分類の小計を削除する。ピボットテーブルの1つのセルを選択する>デザイン>小計>小計を表示しない をクリックする。

手順はこのようになる

セル範囲のすべてを選択して、ホーム>条件付き書式>セルの強調表示ルール>指定の値より大きい をクリックする。

セル範囲全体を選択する

次の値より小さいセルの書式設定に1千万と入力しOKを押す

1千万より小さい値に色がついた

解除の方法は、任意の数値以上に色をつける、と同様にすればいい。

階層構造付きクロス集計表に色をつける

小計と総計をカットした元表

表のセル全体を選択する

ホーム>条件付き書式>セルの強調表示ルール>指定の値より大きい をクリック

次の値より大きいセルを書式設定に1千万と入力し、OKを押す

1千万より大きい値に色がついた

解除の方法は、任意の数値以上に色をつける、と同様にすればいい。

1位から5位までの値に色をつける

総計をカットした元表

セル範囲全体を選択する

ホーム>条件付き書式>上位/下位ルール>上位10項目 をクリック

上位に入るセルを書式設定で10を5に変更してOKを押す

結果はこのようになる

解除の方法は、任意の数値以上に色をつける、と同様にすればいい。

階層構造つきクロス集計表

小計と総計をカットし、セル範囲を選択した元表

ホーム>条件付き書式>上位/下位ルール>上位10項目 をクリック

上位に入るセルを書式設定で10を5に変更してOKを押す

上位5位の商品が抽出された

解除の方法は、任意の数値以上に色をつける、と同様にすればいい。

平均値を下回ったものに色をつける

小計と総計をカットし、セル範囲を選択した元表

Excelメニュー>ホーム>条件付き書式>上位/下位ルール>平均より下 をクリックする

この画面でOKを押す

結果はこのようになる

解除の方法は、任意の数値以上に色をつける、と同様にすればいい。

任意のルールで色をつける

小計と総計をカットし、セル範囲を選択した元表

Excelメニュー>ホーム>条件付き書式>新しいルール をクリックする

ルールの種類を上位または下位に入る値だけを書式設定にクリック

フォントの色と塗りつぶしを設定したらOKを押す

再びこの画面がでるのでOKを押す

結果はこのようになる

解除の方法は、任意の数値以上に色をつける、と同様にすればいい。

ピボットテーブルの簡易データベース

経営ダッシュボードでは、スライサーを用いて行うことを、フィルターを用いてデータ上で行う。このためにはピボットテーブルのフィールドのフィルターに項目を入れればよい。

ピボットテーブルのフィールドのフィルターに分類と商品を入れる

分類と商品が上下に配置され、一行おいてピボットテーブルが表示される

フィルターは左右にも配置できるのだが、一列おいて配置されて見づらくなるので、上下に配置した方がよい。また、フィルターの数が増えてもピボットテーブルは自動的に一行あけて配置されるので、心配はない。

また、ここではAllとなっているが、本来は(すべて)と表示される。

いま、商品のAllの▼をクリックして、Allの+をクリックし展開して一つの項目、ここではかつ丼を選択すると、ピボットテーブルはかつ丼のみの集計になる。

かつ丼をクリックしてOKを押すと集計はかつ丼のみになる

また、複数のアイテムを選択すれば、選択した項目の合計値がピボットテーブルに反映される。これは分類でも同様である。

ここでは一方を選択したときはもう一方はAllにしておかなければならないが、本来は各フィルターごとに細かく設定することができる。たとえば製造業では、製造ラインと商品名などというふうに設定できるのである。重要なことはAND検索ということで、ある製造ラインとある商品名の両方を満たすものが結果として表示されることである。OR検索ではないことを覚えておいてほしい。(実例集を参照)

 

ピボットテーブルのグラフ化

いままで行ってきたピボットテーブルからグラフの作成は可能であり、抽出などもグラフ化ができる。

ピボットグラフの作成画面

ピボットグラフをクリックすれば、任意のグラフを作成でき、ピボットテーブルの横に配置すればいい。

ピボットテーブルの1つのセルを選択し、ピボットグラフで折れ線表示でかつ丼のみの4年度分の推移を表した。もちろんかつ丼をやめて他の項目でも瞬時にグラフは変化する。

フィルターをかつ丼からみそ汁に変えるだけで、ピボットテーブルをいじらなくても
ピボットグラフは変化する

このような使い方が自由にできるようになると、経営ダッシュボードで作成するグラフと、簡易データベースで作成するグラフを分けて使えるようになる。とくに時系列などは簡易データベースの方がいいだろう。

簡易グラフ・カラースケール

年度をフィルターに設定した商品と四半期のクロス集計表
スパークライン 折れ線の設定

挿入>スパークライン>折れ線をクリック

データ範囲は1行目(ここではD5~G5)を選択 場所の範囲は描画するセル OKを押す

するとH5に折れ線ができるので、それをオートフィルで下にコピーすればいい。

完成したスパークラインの折れ線グラフ

解除の方法は、折れ線グラフをすべて選択し、スパークライン>クリアを押す。

スパークライン 縦棒の設定

折れ線の設定と同様に、縦棒を選択する。

データ範囲はD5~G5 場所の範囲は折れ線の隣 OKを押す

I5に縦棒ができるので、先ほどと同様に、それをオートフィルで下にコピーすればいい。

完成したスパークラインの折れ線と縦棒グラフ

解除の方法は、縦棒グラフをすべて選択し、スパークライン>クリアを押す。

カラースケールの設定

カラースケールは、データのすべての範囲を選択し「ホーム>条件付き書式>カラースケール」の順にクリックして好きなカラーを選択すればいい。

カラースケールとスパークラインで構成されたピボットテーブル

解除の方法は、色分け範囲をすべて選択し、ホーム>条件付き書式>ルールのクリア>選択した範囲からルールをクリア、を押す。

これで、金額の大きさで色分けができ、折れ線と縦棒グラフで推移がわかる。

実例集

製造業 データベース 実例1

これは製造業のデータベースの実例1である。絞り込みは、2018年度の国内の関東地方で販売された徳間ラインで製造された商品の売上である。

製造業 データベース 実例2

これは、国内の中国・四国地方の徳間ラインで製造された小型機械Aの時系列売上を、3カ月移動平均を使ったグラフを描いたものである。

このように、フィルターを用いた絞り込みをデータを見ながら行えることと、それをグラフ化できることが、ピポットテーブルのデータベース化の利点である。これに合わないものを経営ダッシュボードとしてグラフ化すれば、実用としてさほど困ることはないはずである。

サイトご利用方法

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

google、yahoo、Bingなどで検索する場合、検索ワードは先頭に、孤立じじい、と入力しその後に、グローバルメニューのどれかひとつを入力すると、その検索サイトが上位表示されます。