データを扱う時のいろいろな操作 1

■ Excel豆知識30

　前回SUMPRODUCT関数で作った集計表と同じ形のピボットテーブルの結果だけをご紹介しまし
たが、その詳しい設定は次回の豆知識31にまわすことにして、今回はピボットテーブルの元に
なっている表に後からデータを追加した場合でも、それをピボットテーブルに反映できるよう
にするやり方などについて説明をしたいと思います。

　豆知識29のSUMPRODUCT関数を用いた集計表では、データの表の範囲を「3行目から1000行目」
としてあらかじめデータが入っていない分も含めて余裕をみて設定をしました。これはデータ
が追加されても集計に反映されるようにするためです。

では、ピボットテーブルではどうすればいいでしょうか。

ということで、ピボットテーブルでの「データの更新」と「データが追加される場合の対策」
について説明したいと思います。

　30-1 ピボットテーブルのデータの更新

ピボットテーブルは、元のデータに変更があった場合、「データの更新」ということをして変
更を反映させることが出来ます。

[図03001]

下のピボットテーブルは[図03001]のリストを元に作ったものです。

[図03002]

今ピボットテーブルの「飯島」さんの「東森地区」の数字は4950になっています。まず、上の
表の黄色のG17セルの数字を10,000,000などの大きい数字に変えてみてください。それでもピ
ボットテーブルの数字に変化は見られません。

ではピボットテーブルの中のセルを選択して、「データ」-「データの更新」をクリックして
ください。「ピボットテーブルツールバー」にある「！」マークをクリックしてもいいです。

[図03003]

このようにピボットテーブルではリスト範囲の中のデータに変更があった場合、「データの
更新」でそれを反映させることが出来ます。

でも、このリストの下の行に追加データがあった場合はどうでしょう？

試しにリストの下のピンク色にした部分にデータを入力して「データの更新」をしてみてく
ださい。どうでしょう。ピボットテーブルに変化は見られなかったと思います。

なぜでしょう？
それは、ピボットテーブルで認識しているリスト範囲が最初にデータがあった部分だけにな
っているからなのです。

これを確認するために、ピボットテーブルの中のセルを選択して、「ピボットテーブルのウ
ィザード」を表示させてください。（下図のようにウィザードの3/3が出ます。）
そして「戻る」をクリックしてください。

[図03004]

[図03004-2]

ここで、下図のようにウィザードの範囲のボックスの右の小さな四角をクリックしてデータ
範囲を広げてやることも出来ますが、データが追加されるたびにその操作をするのは面倒で
す。

[図03005]

また、データ範囲を最初から1000行くらいとることも可能ではありますが、空白データがあ
ると日付などのグループ化が出来なくなるなど困る点も出てきます。

そこで、下にデータが追加されても、自動的に範囲が変わっていくような設定をしよう
と思います。

　30-2 ピボットテーブルの参照範囲を可変にするには

この項は「Excel技道場」さんのページを参考にさせていただいております。

まず、名前定義をします。
「名前ボックス」を使って範囲に名前をつけるやり方は豆知識第9回でもご紹介しましたが、
今回は名前定義のダイアログボックスで設定をします。

（１）データのあるシート（ここでは「データ」シート）を選択して作業をします。
Ctrlキーを押しながらF3キーを押すと名前定義のダイアログボックスが出ます。
（これはメニューの「挿入」-「名前」-「定義」として出るものと同じです。）

[図03006]

（２）「名前」に例えば　リスト　と入力。（自分でわかりやすい名前にすればいいです。）
「参照範囲」に　=$A$2:INDEX($F:$F,COUNTA($A:$A)+1) 　と入力します。

（上の式中の「COUNTA($A:$A)+1」の「+1」は、[図03006]の項目行が2行目にあり、１行目が空白
であるために、その１行分を加えて最終行の行数を求めているものです。項目行が1行目にある場合
は「参照範囲」に　=$A$1:INDEX($F:$F,COUNTA($A:$A))　　と入力します。こちらを参考にして
ください。）

この時、参照範囲にキーボードからこの数式を入力してもいいですが、面倒なのでコピー貼
り付けをしたいと思います。名前定義のダイアログボックスを出してからコピーすることは
出来ませんので、作業を始める前にコピーだけしておきます。

[図03007]

（１）と（２）がちょっと後先になりますが、まず、数式をコピーだけしておいて、（１）
の操作に入ります。そして、（２）の操作です。

[図03008]

この操作で「データ」シートのデータ範囲が「リスト」という名前に定義されました。

[図03009]

（３）（２）の操作で定義された名前をピボットテーブルの「範囲」として使います。
ピボットテーブルウィザードを起動し、ウィザードの2/3で「範囲」に =リスト と入力し
「完了」とします。

[図03010]

（４）「行のフィールド」に「受付支店」をドラッグ、「列のフィールド」に「講座名」を
ドラッグ、「データフィールド」に「氏名」をドラッグします。下図のようなピボットテー
ブルが出来上がると思います。

[図03011]

（５）次にデータの最下行の下にデータを追加してみましょう。変化がわかるように新しい
受付支店名で講座名も新しいものにしてみます。その後ピボットテーブル内のセルを選択し
て、「データの更新」をクリックします。

[図03011-2]

（６）念のため、ピボットテーブルウィザードを再表示させて「戻る」としてウィザードの
2/3で範囲を確認してみましょう。

[図03012]

上記の状態から画面を下へスクロールして最終行の部分が見えるようにしてみます。ちゃんと
追加した最終行まで範囲が拡大されていることが確認できます。

[図03012-2]

このように、ピボットテーブルの参照範囲を可変に設定しておけば、安心してデータを追加す
ることができますね。(*^-^*)	
ちょっと面倒に思うかもしれませんが、日々増えるデータをピボットで集計するというような
場合には是非覚えておきたいテクニックだと思います。これ以外にも、データの追加に対応す
るやり方に「外部データの取込」を使った方法があります。（こちら）

なお、=$A$2:INDEX($F:$F,COUNTA($A:$A)+1)　この式を直接ピボットテーブルウィザー
ドに入れると自動的に　データ!$A$2:$F$110　というふうに変換されてしまいますので、
データの追加に対応することは出来ません。それで名前定義を用いて「リスト」という
名前にその数式を定義して、それを範囲として用いているという訳です。

上記の式がどういう理屈で範囲が可変になるのか、ということについては豆知識30のDLブック
の「INDEX関数・COUNTA関数」シートに書きましたので、興味があったら読んでみてください。
（なお、「COUNTA($A:$A)+1」の「+1」はデータが2行目から始まっていて１行目が空白であるために、その
１行分を加えて最終行の行数を求めているものです。項目行の上に空白行が３行あれば「+3」とします。）