はじめに
経営ダッシュボードを作成する際に、ピボットテーブルにデータをおとしてからグラフを作成したが、ここではピボットテーブルをデータベースとして扱う方法を解説する。
データベース化する前処理として、パワークエリでデータをリスト形式にし、パワーピボットでリレーションを組むことで、Accessと同様な処理をExcelで行うことができる。
ピボットテーブルをデータベース化するとはリレーションを組んだデータモデルに対して、経営ダッシュボードでスライサーで行ってきたことを、フィールドなどによって抽出し、データを表示させながら同時にグラフ化することにある。
出発点はすでにデータモデルとしてExcelに取り込みピボットテーブルを作成したところから解説をしていく。扱うデータは経営ダッシュボードでも使用したほか弁のデータである。年度4月始まりのデータモデルで今回は四半期のデータも取り扱う。
昇順・降順の並べ替え
金額の大きい順、小さい順に並べ替えるには、降順、昇順機能を使う。
始めに分類の小計を表示する。
分類は昇順、商品は降順にしてみる。
分類の小計を選択し、右クリック。
次に商品を降順とする。
商品のひとつを選択して右クリック。
並べ替えはこれでいいのだが、問題は解除である。Excelには並べ替えの解除機能がついていないので、仕方なく、Excelメニューの上にある戻る矢印を押してもとの状態にする。
分類を自由に並べ替えをする
分類の項目をクリックし、マウスポインタが矢印付き十字に変わったら、動かしたい場所へ移動（ドラック）すればいい。（✙のマークではない）
独自のルールで分類を並べ替える方法もあるが、ドラックした方が早いので、この方法はやめた方がいい。
抽出
特定の文字を含むものを抽出する。
特定の文字、たとえば、最後の文字が弁で終わるものを抽出してみる。
抽出の場合は、解除機能があるので、それを使う。
行ラベルのじょうごをクリックする。
階層構造になっている場合
行ラベルの▼をクリックする。
解除をするときも、「フィールドの選択＞商品をクリック＞商品からフィルターをクリア」をクリックすればいい。
表示させたい分類名を抽出する
行ラベルの▼をクリックする。
解除は行ラベルのじょうごをクリックして、「分類からフィルターをクリア」をクリックすればいい。
表示させたい商品名を抽出する
行ラベルの▼をクリックする。
解除は行ラベルのじょうごをクリックして、「商品からフィルターをクリア」をクリックすればいい。
特定の金額以上のものを抽出する
1千万円以上のものだけを抽出してみる。
行ラベルの▼をクリックする。
解除は行ラベルのじょうごをクリックして、「商品からフィルターをクリア」をクリックすればいい。
階層構造で商品が1千万円以下のものを抽出する
行ラベルの▼をクリックし、フィールドの選択で右の矢印をクリックして商品をクリックする。
解除は行ラベルのじょうごをクリックし、フィールドの選択で商品をクリックして、「商品からフィルターをクリア」をクリックする。
クロス集計表から1千万円以下の商品を抽出する
クロス集計表の場合、1千万円以下の商品かどうかを判定するのは、右端の列の総計からである。
行ラベルの▼をクリックして、フィールドの選択で右の矢印をクリックして商品を選択し、値フィルターをクリックする。
解除は行ラベルのじょうごをクリックして、フィールドの選択で商品をクリックし、「商品からフィルターをクリア」をクリックすればいい。
上位5位の商品名を抽出する
行ラベルの▼をクリックして、値フィルターをクリックして、トップテンをクリックする。
解除は行ラベルのじょうごをクリックして、「商品からフィルターをクリア」をクリックすればいい。
階層構造の下位1位を抽出する
行ラベルの▼をクリックして、フィールドの選択を右の矢印から商品にして、値フィルターをクリックし、トップテンをクリックする。
階層構造の場合、下位の商品の一番小さいものが抽出される。
階層構造のクロス集計表の上位2位の抽出
クロス集計表の場合、元表の一番右端の総計の列で判断される。
行ラベルの▼をクリックして、フィールドの選択で商品をクリックし、値フィルターをクリックして、トップテンをクリックする。
解除はいままでと同様な手順で行う。
抽出したデータに色をつける
任意の数値以上に色をつける
総計が邪魔になるので、総計をカットする。
ピボットテーブルのセルを選択＞デザイン＞総計＞行と列の集計を行わない をクリックする。
解除の方法は、表のセル全体を選択して、ホーム＞条件付き書式＞ルールのクリア＞選択したセルからルールをクリア をクリックする。
階層構造のセルの強調表示
総計が邪魔なので、任意の数値以上に色をつける、と同様な手順でカットする。
分類の小計を削除する。ピボットテーブルの1つのセルを選択する＞デザイン＞小計＞小計を表示しない をクリックする。
セル範囲のすべてを選択して、ホーム＞条件付き書式＞セルの強調表示ルール＞指定の値より大きい をクリックする。
解除の方法は、任意の数値以上に色をつける、と同様にすればいい。
階層構造付きクロス集計表に色をつける
解除の方法は、任意の数値以上に色をつける、と同様にすればいい。
1位から5位までの値に色をつける
解除の方法は、任意の数値以上に色をつける、と同様にすればいい。
階層構造つきクロス集計表
解除の方法は、任意の数値以上に色をつける、と同様にすればいい。
平均値を下回ったものに色をつける
解除の方法は、任意の数値以上に色をつける、と同様にすればいい。
任意のルールで色をつける
解除の方法は、任意の数値以上に色をつける、と同様にすればいい。
ピボットテーブルの簡易データベース
経営ダッシュボードでは、スライサーを用いて行うことを、フィルターを用いてデータ上で行う。このためにはピボットテーブルのフィールドのフィルターに項目を入れればよい。
フィルターは左右にも配置できるのだが、一列おいて配置されて見づらくなるので、上下に配置した方がよい。また、フィルターの数が増えてもピボットテーブルは自動的に一行あけて配置されるので、心配はない。
また、ここではAllとなっているが、本来は（すべて）と表示される。
いま、商品のAllの▼をクリックして、Allの＋をクリックし展開して一つの項目、ここではかつ丼を選択すると、ピボットテーブルはかつ丼のみの集計になる。
また、複数のアイテムを選択すれば、選択した項目の合計値がピボットテーブルに反映される。これは分類でも同様である。
ここでは一方を選択したときはもう一方はAllにしておかなければならないが、本来は各フィルターごとに細かく設定することができる。たとえば製造業では、製造ラインと商品名などというふうに設定できるのである。重要なことはAND検索ということで、ある製造ラインとある商品名の両方を満たすものが結果として表示されることである。OR検索ではないことを覚えておいてほしい。（実例集を参照）
ピボットテーブルのグラフ化
いままで行ってきたピボットテーブルからグラフの作成は可能であり、抽出などもグラフ化ができる。
ピボットグラフをクリックすれば、任意のグラフを作成でき、ピボットテーブルの横に配置すればいい。
ピボットグラフは変化する
このような使い方が自由にできるようになると、経営ダッシュボードで作成するグラフと、簡易データベースで作成するグラフを分けて使えるようになる。とくに時系列などは簡易データベースの方がいいだろう。
簡易グラフ・カラースケール
スパークライン 折れ線の設定
するとH5に折れ線ができるので、それをオートフィルで下にコピーすればいい。
解除の方法は、折れ線グラフをすべて選択し、スパークライン＞クリアを押す。
スパークライン 縦棒の設定
折れ線の設定と同様に、縦棒を選択する。
I5に縦棒ができるので、先ほどと同様に、それをオートフィルで下にコピーすればいい。
解除の方法は、縦棒グラフをすべて選択し、スパークライン＞クリアを押す。
カラースケールの設定
カラースケールは、データのすべての範囲を選択し「ホーム＞条件付き書式＞カラースケール」の順にクリックして好きなカラーを選択すればいい。
解除の方法は、色分け範囲をすべて選択し、ホーム＞条件付き書式＞ルールのクリア＞選択した範囲からルールをクリア、を押す。
これで、金額の大きさで色分けができ、折れ線と縦棒グラフで推移がわかる。
実例集
これは製造業のデータベースの実例１である。絞り込みは、2018年度の国内の関東地方で販売された徳間ラインで製造された商品の売上である。
これは、国内の中国・四国地方の徳間ラインで製造された小型機械Aの時系列売上を、3カ月移動平均を使ったグラフを描いたものである。
このように、フィルターを用いた絞り込みをデータを見ながら行えることと、それをグラフ化できることが、ピポットテーブルのデータベース化の利点である。これに合わないものを経営ダッシュボードとしてグラフ化すれば、実用としてさほど困ることはないはずである。
サイトご利用方法
次のページ・前のページを利用するよりも、グローバルメニュー（ヘッダー部分にある項目）をクリックしていただければ、その項目の全体像が一目でみることができ、クリックすればそのサイトへ飛びます。
google、yahoo、Bingなどで検索する場合、検索ワードは先頭に、孤立じじい、と入力しその後に、グローバルメニューのどれかひとつを入力すると、その検索サイトが上位表示されます。
