ピボットテーブル手習い

作成日： 2010-09-17
最終更新日：

ピボットテーブルの謎

ピボットテーブルとは、Excel の機能の一つ。データを加工して表にすることができる機能であり、またその機能を使って作った表をいう。

それでどこが謎かというと、ピボットテーブルの作り方や使い方である。用語もよくわからないし、操作も一つ間違えば意図せざる結果になってしまう。また、できることが複雑かつ膨大であり、どこまでできるのかがわからない。これも私にとっての謎である。

ピボットテーブルとドリルダウン

最近困っていたのが、ピボットテーブルを使って集約データと詳細データを使い分ける方法である。たとえば、集約データが次の通りとする。

表1. 集約データ
営業員	売上(円)
株腰春	3,000
滝川法事	5,000
方鍋明菜	7,000
九後越巻	6,000

この詳細データが次の通りとする

表2. 詳細データ
営業員	得意先	売上(円)
株腰春	名尽商事	2,000
	法衣商会	500
	硫黄興業	500
滝川法事	名尽商事	4,000
	法衣商会	700
	硫黄興業	300
方鍋明菜	名尽商事	3,000
	法衣商会	1,000
	硫黄興業	3,000
九後越巻	名尽商事	1,000
	法衣商会	3,000
	硫黄興業	2,000

これを簡単な操作で切り替えられないか。 2つのシートを作り参照するというのは、資源がもったいない。おそらく、ドリルダウン、という機能が使えるに違いない。

［ピボットテーブル］ツールバー−［ピボットテーブル］−［オプション］をクリックする。
［ピボットテーブルオプション］ダイアログ−［データオプション］欄　−［詳細データの表示を有効にする］チェックボックスをOnにする。なお、バージョンによってはチェックボックス名がドリルダウンを有効にする表示になっていることがある。
［ピボットテーブルオプション］ダイアログ−［OK］ボタンをクリックする。

こうすると見だしの詳細データが見えるはずである。問題は、中身をみたいというとき、そして中身をまとめたい、というときに、どういう用語を思いつくかなのだ。下記はその対となる概念の一部である。

概要／詳細
集約／明細
まとめ／掘り下げ
概算／内訳
省略／展開
折り畳み／(折り畳みの)開き
グループ化／(グループ化)解除
サマリー／ディテール
ドリルアップ／ドリルダウン

「省略／展開」は、Notes/Domino などで用いられている。「折りたたむ／展開する」は、gmail などで使われている。

なお、ドリルダウンと似た概念に、ドリルスルーがある。これはその集計値の元になった集計値を見る機能で、 Excel では該当数値をダブルクリックすることで別シートが表示され、元データを見ることができる。

ピボットテーブルのグループ化

ピボットテーブルには、グループ化という概念がある。たとえば、果物フィールドに、ミカン、レモン、グレープフルーツ、などがあれば、これらに「柑橘類」というグループを付与することができる。

ピボットテーブルと正規化

ピボットテーブルの元になるリストは、少なくとも第1正規化までされなくてはならない。言い換えれば、一つのセルには一つの項目のみが入っている状態にしなければならない。この第1正規化を前提として、元のリストは、縦横ともにセルの値がが漏れなく埋まっていることが望ましい。セルが空のままでは、うまく集計できないことがある。だから、上の詳細データは、元リストとしては次の状態になっているべきである。

表3. 売上リスト(営業員氏名)
営業員	得意先	売上(円)
株腰春	名尽商事	2,000
株腰春	法衣商会	500
株腰春	硫黄興業	500
滝川法事	名尽商事	4,000
滝川法事	法衣商会	700
滝川法事	硫黄興業	300
方鍋明菜	名尽商事	3,000
方鍋明菜	法衣商会	1,000
方鍋明菜	硫黄興業	3,000
九後越巻	名尽商事	1,000
九後越巻	法衣商会	3,000
九後越巻	硫黄興業	2,000

さて、営業員が氏名でなく営業員コードで表されるとしよう。これはよくあることだ。もし氏名だけで管理したとすると、営業員氏名の登録が「株腰春」だけではなく、「株腰春」だったり、「株　腰春」だったりしてこれらは別人として扱われてしまう。したがって、氏名の異同を吸収して同一人として扱う、すなわち名寄せするために営業員コードを使う。では、次のように営業員コードが割り当てられたリストがあるとしよう。

表4. 営業員コードつきリスト
営業員コード	営業員	得意先	売上(円)
000175	株腰春	名尽商事	2,000
000175	株腰春	法衣商会	500
000175	株腰春	硫黄興業	500
000131	滝川法事	名尽商事	4,000
000131	滝川法事	法衣商会	700
000131	滝川法事	硫黄興業	300
000235	方鍋明菜	名尽商事	3,000
000235	方鍋明菜	法衣商会	1,000
000235	方鍋明菜	硫黄興業	3,000
000207	九後越巻	名尽商事	1,000
000207	九後越巻	法衣商会	3,000
000207	九後越巻	硫黄興業	2,000

なんか、もったいないと感じないだろうか。そう、元リストと次の対応表があれば、上の表は作れてしまうからだ。

表5. 営業員マスター
営業員コード	営業員
000175	株腰春
000131	滝川法事
000235	方鍋明菜
000207	九後越巻

一般に固定された対応表を指して「マスター」という。上の場合は営業員マスターなどという。

なお、通常はリストにはコードのみがあって、コードと名称を対応付けるのはマスターに頼るのが一般的だ。したがって、一般的には次のリストが普通である。

表6. 売上リスト(営業員コード)
営業員コード	得意先	売上(円)
000175	名尽商事	2,000
000175	法衣商会	500
000175	硫黄興業	500
000131	名尽商事	4,000
000131	法衣商会	700
000131	硫黄興業	300
000235	名尽商事	3,000
000235	法衣商会	1,000
000235	硫黄興業	3,000
000207	名尽商事	1,000
000207	法衣商会	3,000
000207	硫黄興業	2,000

さて、表5. と表6. から表4. が作れないかを考えてみた。営業員コードと営業員(氏名)がピボットテーブルで同時表示するには、表4 を経由しないといけない。一度、複数のシートから作るピボットテーブルで、表5 と表6 から表4 が合成できる、と思ったがそれはできなかった。複数シートで作るピボットテーブルは、複数シートがすべて同じ一覧表でなければならない。シートにまたがる串刺し形式のピボットテーブル化、と認識するのがいい。

したがって、表5. と表6. から表4. を作るには、vlookup 関数(ワークシート関数)を使うのが普通だ。まず、表6. の営業員コードの右側に新しい列を設け、これを営業員の列とする。そして、営業員のセルに、次のように計算式を入れる。
=vlookup(A2, "営業員", 2, FALSE)
なお、営業員コードの列がA列、レコードが2行の場合である。また、表5. 全体の枠が「営業員」という名前が付与されているものとする。ここで、vlookup の使い方については他のページを見てほしい。

さて、もったいない、という直感はあたっているだろうか。それを検証する前に、節約できそうなアイディアが浮かんだのでそれを明らかにしよう。これは、元のリストを加工するのではなく、ピボットテーブルを加工するのである。具体的には表6. の「営業員コード」をグループ化する、という方法である。一つのコードで一つのグループがつけられれば、結果として表4. が得られると考えた。しかし、現実は甘くなかった。グループにする対象の複数のセルを選んで右クリック→グループ化とするのだが、セルを一つだけ選んで同様なことをする「このグループ化はできません」というエラーが出る。そこで、姑息かつ場当たり的で泥縄式の対応だが、ダミーの営業員コードを作ろう。ついでに得意先も「得意先ダミー」という名前にする。もちろん、売上はゼロ円である。

表6'. 売上リスト(営業員コード)ダミー付加
営業員コード	得意先	売上(円)
000175	名尽商事	2,000
000175	法衣商会	500
000175	硫黄興業	500
000131	名尽商事	4,000
000131	法衣商会	700
000131	硫黄興業	300
000235	名尽商事	3,000
000235	法衣商会	1,000
000235	硫黄興業	3,000
000207	名尽商事	1,000
000207	法衣商会	3,000
000207	硫黄興業	2,000
999999	ダミー得意先	0

この状態で000207と999999の複数セルを同時に選べばグループ化が可能で、グループ列が新たに左側にできる。このときのタイトルは「グループ1」となっている (グループ表示は半角カナ)。同時に、2つのセルを合体させたグループのセルができる。このグループのセルに「九後越巻」と入れればよい。他のグループのセルもできているので、残りは表5.とにらめっこして個別に手で埋めていく。表5. が大きい場合には、手で埋めるのは非効率だからピボットテーブルの助けを借りることになる。この方法は省略する。

できあがった表を見て、グループの位置を営業員コードの右側に移動すれば、表4. とほぼ同等の表が得られるだろう。唯一違うのはダミーが出ていることだが、実際には何ら影響はない。見た目を気にするのであれば、タイトル行の右にある▽ボタンをクリックし、ダミーを表す 999999 のみチェックをはずせば全く同等になる。

さて、どれほどの領域が節約できただろうか。 1万行ほどのリストを作って計測したところ、 vlookup 方式では 221.0KB あったところが、グループ化方式では 216.5KB だった。 5KB 減ったが、全体に比べれば誤差の範囲だろう。苦労したわりにはかいがないので、翻訳にグループ化を使う方法はおすすめしない。

まず名前の整理から

ピボットテーブルを使う上ので問題は、名前がわかりにくいことである。まずは名前を理解しよう。

表: ピボットテーブルを作るもとになる表。横の並びを行、縦の並びを列という。以下、m 行 n 列からなる表を、表(m, n)という。 (m >= 2, n >= 1) 表(m, n) の第１行めには見出しが必ずある。
見出し: 表の第１行にある語。２行目以下のデータの属性や性質を表す。表(m, n) の見出しは全部で n 個ある。
ピボットテーブル: 表を加工して、データを整理したテーブル。
フィールド: 見出しの部分集合。
列フィールド: ピボットテーブルで、縦に並んだフィールド。
行フィールド: ピボットテーブルで、横に並んだフィールド。
ページフィールド: ピボットテーブルで、最も上にあるフィールド。すべてのフィールドか、一つのフィールドのみが選べる。
値フィールド: ピボットテーブルで、行フィールドと列フィールドが交わるフィールド。
列エリア: ピボットテーブルで、縦に並んだ範囲。ドラッグアンドドロップする個所でもある。
行エリア: ピボットテーブルで、横に並んだ範囲。ドラッグアンドドロップする個所でもある。
ページエリア: ピボットテーブルで、最も上にある範囲。ドラッグアンドドロップする個所でもある。
値エリア: ピボットテーブルで、行フィールドと列フィールドが交わる範囲。Excel 2000/2002/2003 では、この範囲に「ここにデータアイテムをドラッグします」と表示される。

フィールド／アイテム／セット

ピボットテーブルを作ると、分析ツールにフィールド／アイテム／セットという項が表示される。これらの使い方を調べてみよう。

集計フィールド

名前：　　　　　[追加]
数式： = 0　　　[削除]

フィールド
営業員
得意先
売上（円）
月

[フィールドの挿入]
　　　　　　　　　　　　　　　　[ＯＫ]　[閉じる]

これは独自の集計列に使える。

集計アイテム

名前：　　　　　[追加]
数式： = 0　　　[削除]

フィールド　　　　　　　　　　　アイテム
営業員　　　　　　　　　　　　　株 腰春
得意先                          滝川 法事
売上（円）                      方鍋 明菜
月                              九後 越巻

[フィールドの挿入]　　　　　　　[アイテムの挿入]

　　　　　　　　　　　　　　　　[ＯＫ]　[閉じる]

マクロで楽をする

ピボットテーブルにマクロを入れるのは邪道だと思うが、便利だから入れてみよう。まずはよく使うマクロとして、値フィールドに 3 桁区切りのコンマを入れる方法を掲げる。

Sub Macro1()
ActiveSheet.PivotTables(1).PivotFields("合計 : 値段").NumberFormat = "#,##0_ "
End Sub

もっともこれはフィールドが"合計 : 値段" に合致しないと意味がない。フィールドの名称によらずに実現する手段は、追って考えてみる。

まりんきょ学問所＞コンピュータの部屋＞ピボットテーブル手習い

MARUYAMA Satosi

ピボットテーブル 手習い