エクセル2007からのピボットテーブル
| ■ Excel豆知識49 |  |
|---|
今回は、2007からのピボットテーブルで設定できるいろいろなことについてあれこれやってみた
いと思います。
集計の意図
ここで使うデータは、A支店からF支店までの６つの支店でのひと月の購入データです。
取引先からの複数の請求書があります。それを取引先ごとに集計して一括して振り込みをしたいと
思います。そのために、取引先１件１行のデータをピボットテーブルで作成します。
請求書の内容には取引の性格によりA・B・Cの区分があり、それも一緒に集計したいと思います。
また、別の視点から作成したデータと「取引先CD」をキーにして、この後チェックをする予定があ
るので「取引先名」だけでなく、そこに「取引先CD」をつけたデータにしたいと思います。
49-1 2007からのピボットテーブル（レポートのレイアウト）
<ピボットテーブル レポートのレイアウト>
データ範囲の中のセルをひとつ選択して、「挿入」-「ピボットテーブル」をクリックして、出
てきたダイアログボックスで「OK」とします。
（データがリスト形式になっていれば、自動的にデータ範囲全体が範囲として指定されますし、ピボ
ットテーブルの場所は通常は新規ワークシートに作成しほうがわかりやすいのでここでは設定された
状態のまま「OK」として良いと思います。
豆知識48でご紹介した参照範囲を可変にするのは、データに追加があって、それを次々にピボットテ
ーブルに反映させていきたい場合に用いる方法です。通常の集計をする場合は、「挿入」-「ピボッ
トテーブル」-「OK」として大丈夫です。）
[図04901]
フィールドリストから 「行ラベル」に「取引先CD」と「取引先名」を 「列ラベル」に「区分」を 「値」に「金額」を配置します。 すると、下図のように出来上がります。 「取引先CD」と「取引先名」のそれぞれの行に数字があって、見にくいですし、「取引先CD」を キーにしたチェックにも適しません。 このままでは使いにくいので、設定を工夫していこうと思います。
[図04902]
「取引先CD」の欄のどこかで右クリック。
出てくるメニューから「"取引先CD"の小計」のチェックをはずします。
（右クリックする場所によって、表示される項目名が変わります。）
この場合、「取引先CD」と「取引先名」は１対１ですので、上図のように必ず２行になり、それぞ
れ同じ数字になりますが、一応太字になっている方は、行ラベルの上位にある項目で、小計が表示さ
れている数字なので、こちらの小計をはずします。
「取引先名」のほうで右クリックして出てくる「"取引先名"の小計」をクリックしても表示は消えま
せん。これは小計が表示されているのではないためです。
[図04903]
すると下図のように重複した数字が消えてすっきりしました。 でも、まだ１件１行のデータにはなっていません。 なので、レポートのレイアウトを変更してみます。
[図04904]
「ピボットツール」-「デザイン」-「レポートのレイアウト」の「コンパクト形式で表示」 というのが上図の状態です。ボタンを見てわかるように、「コンパクト形式」と「アウト ライン形式」は小計の行が別になっています。「表形式で表示」にすると、小計と下位の データ行の頭が揃っているようです。
[図04905]
「アウトライン形式で表示」をクリックしたのが下図です。 これも、まだ１件１行のデータにはなっていません。
[図04906]
「表形式で表示」にします。
[図04907]
これで１件１行のデータになりました。 集計の最初の目的は達成しましたので、この後はあれこれいじってみたいと思います。^^
[図04908]
<ピボットテーブル 行ラベルエリアに項目を追加。アイテムのラベルの繰り返し。> 上で作成したピボットテーブルの「行ラベル エリア」に「支店名」を追加して、支店ごとの集 計を表示させます。「支店名」ごとにしたいので、「支店名」のフィールドを「行ラベル エリ ア」の一番上に置きます。 これは結構見やすい表示のような気がしますが、時々質問掲示板などで、A列の空白を全部支店 名で埋めたいという希望を見ます。 今までのバージョンでは、ピボットテーブルの中でそれをすることはできませんでしたが、エク セル2010ではその機能が追加されました。
[図04909]
「レポートのレイアウト」の点線から下の2つのボタンはエクセル2007にはありません。 この「アイテムのラベルをすべて繰り返す」をクリックしてみましょう。
[図04910]
下図のように空白だったところに支店名が表示されました。 見た目はちょっとうっとうしい感じがしますが、用途によってはこれがとても嬉しい場合もあ るかもしれません。
[図04911]
上記は「表形式で表示」の状態ですが、「アウトライン形式で表示」にすると下図のようにな ります。これはあまり出番はないかも。。^^；
[図04912]
「コンパクト形式で表示」では「行ラベル エリア」に複数の項目があっても、全部A列のセル に表示されますので、アイテムの繰り返しは出来ません。
[図04913]
<ピボットテーブル スタイル> ピボットテーブルのスタイルは、ピボットテーブルを選択した状態で、いろいろなスタイルの ボタンにマウスを合わせるとそれにした場合にどうなるかが、ピボットテーブル上で実際に見 ることが出来ますので、やってみてください。 初期設定のままだと、淡色の上から３段目の水色の状態のようです。 「ピボットテーブル スタイルのオプション」の「行見出し」「列見出し」「縞模様（行）」 「縞模様（列）」も下図の状態が初期設定です。ここも、チェックを入れたり外したり試して みると、状況がすぐにわかります。
[図04914]
上から２段目のボタンは下図のようになります。
[図04915]
「表形式で表示」の状態で左上隅の「なし」を選択すると、2003までのピボットテーブルと同 じ表示になります。
[図04916]
49-2 2007からのピボットテーブル（ピボットテーブル オプション）
「ピボットテーブルツール」-「オプション」-「オプション」の右の▼をクリックしたのが下 の状態です。ここには「オプション」と「レポートフィルターページの表示」「GetPivotData の生成」の3つのボタンがあります。随分とバラバラな取り合わせのような気もしますが。^^； 「ピボットテーブル オプション」のダイアログボックスを出すには赤丸のボタンをクリックす れば良いです。
[図04917]
出てきたダイアログボックスが下図です。下図はエクセル2010のものです。 詳しくはマイクロソフトサポートの「ピボットテーブル オプション」 （適用対象: Microsoft Office Excel 2007）をご覧ください。
[図04918]
[図04919]
「表示」タブにある「データのないアイテムを行（列）に表示する」には「この設定はOLAPデ ータソースでのみ使用できます。」と注釈がついていて、通常のデータから作った場合はグレ ーアウトしています。
[図04920]
2003までのピボットテーブルと同じ「データのないアイテムを表示する」は、下図のように
ピボットテーブルの「フィールドの設定」の「レイアウトと印刷」タブにその項目があります。
[図04920-1]
[図04921]
「データ」タブの「What-If分析」セクションは、2010から追加されました。
[図04922]
ピボットテーブルを作成した後にデータを変更した際、フィールドのドロップダウンリストに 変更前のアイテムが残ったままになることがあります。 「サイト内FAQ 4.」ではVBAを利用して古いアイテムを削除する方法をご紹介しましたが、2007 からのピボットテーブルのオプションのデータタブにある「データソースから削除されたアイ テムの保持」の「１フィールドに保持するアイテム数」で「なし」（初期値は「自動」)を選択 し、「OK」とした後、ピボットテーブルを更新すると古いアイテムが削除され、現在のデータ ソースにあるアイテムのみになります。
[図04922-1]
この「代替テキスト」タブは2010から追加されました。
[図04923]
今回はこれでおしまいです。