エクセル2007からのピボットテーブル
| ■ Excel豆知識51 |  |
|---|
今回は前回と同じデータで、2007からの「テーブル」機能を利用してピボットテーブルを作成 してみます。豆知識48で「データ範囲を可変にするには」ということで、可変範囲の名前を利 用する方法を説明しましたが、今回の「テーブル」も同じように使えます。
51-1 2007からのピボットテーブル（範囲に「テーブル」を利用）
エクセル2003で、データ範囲のセルを選択して、「データ」-「リスト」-「リストの作成」と すると、下図のような「リストの作成」ダイアログボックスが出て、いろいろ便利に使えるら しい「リスト」が作成されました。（個人的には使ったことがありませんでしたが。）
[図05101]
2007からは、これと似た機能の「テーブル」というものが出来ました。ピボットテーブルを作 成する時に利用するボタンの右となりにそのボタンがあります。これを利用すると、データに 追加がある場合でも自動的にピボットテーブルのデータ範囲が広がりますので、やってみたい と思います。
[図05102]
データ範囲の中のセルを1つ選択して「挿入」-「テーブル」をクリックします。すると、下図 のような「テーブルの作成」ダイアログボックスが出て、データ範囲が示されますので、「OK」 をクリック。
[図05103]
すると、下図のようにデータ範囲が「テーブル」となって、テーブルツールの「デザイン」タ ブが出てきます。テーブルには「テーブルスタイル」の赤丸のスタイルが初期設定で適用され ます。デザインタブにはいろいろなボタンが配置されていますので、興味があったらいろいろ やってみてください。
[図05104]
今回はこれをピボットテーブルのデータ範囲として使おうと思います。まず、わかりやすいよ うに、作成したテーブルの名前を「テーブル1」から「受付データ」に変更しました。（そのま までもかまいません。）その後、「ピボットテーブルで集計」ボタンをクリックします。 （「挿入」-「ピボットテーブル」として、範囲にその名前を入力してもかまいません。）
[図05105]
すると、下図のように「ピボットテーブル作成」の見慣れたダイアログボックスが出てきます。 「テーブル/範囲」に先ほどつけた「受付データ」というテーブル名が自動的に入っています。
[図05106]
「OK」とすると、新しいシートにピボットテーブルの枠とフィールドリストが出ますので、と りあえず、下図のように「行ラベル エリア」に「受付支店」を、「列ラベル エリア」に「講 座名」を、「値 エリア」に「氏名」を配置します。
[図05107]
ここで「テーブル」を利用した一番の理由の「データの追加があった場合」にどうなるかを見 てみましょう。1件のデータを追加しました。すると、テーブルのスタイルも追加データまで のびました。 ピボットテーブルに戻って、「更新」してみます。
[図05108]
ちゃんと「世田谷支店」の「茶道」のデータが1件増えました。 「テーブル」を利用するのは、追加データがある場合には便利だと思います。
[図05109]
51-2 2007からのピボットテーブル（いろいろな操作）
ここからは、豆知識44で作成した2003のピボットテーブル（下図）と似たような配置の表にな るようにしてみたいと思います。別に同じ形にする必要は全然ないのですが、それをすること でいろいろな操作をやってみることができますので。
[図05110]
同じにするために、次のような操作をします。 ・「データの個数/氏名」となっているフィールド名を「受付件数」に変更 ・「レポートフィルタ エリア」に「年」「月」を配置するための日付のグループ化の工夫 ・「列ラベル」「行ラベル」という表示から「フィールド名」への変更 ・ 列幅の変更が戻らないような設定 ・「受付支店」や「講座名」のアイテムの移動 ・ピボットテーブルの「要素」を「選択」して書式を設定 <「データの個数/氏名」となっているフィールド名を「受付件数」に変更> 氏名のフィールドを選択し、「アクティブなフィールド」の名前を「受付件数」に変更。
[図05111]
<「レポートフィルタ エリア」に「年」「月」を配置するための日付のグループ化の工夫 > レポートフィルタエリアに最初から配置すると、日付のグループ化は出来ませんので、まず「行 ラベル エリア」に配置します。
[図05112]
次に日付のフィールドを選択して「右クリック」。出てくるメニューから「グループ化」を選択。 「ピボットテーブルツール」-「オプション」-「グループの選択」のボタンでも良いです。
[図05113]
すると、下図の「グループ化」のダイアログボックスが出ます。 この「月」だけでは、複数年あった場合は、年を無視して月で合算されてしまいますので、
[図05114]
「年」「月」を選択。としたいところですが、
[図05115]
そうすると、フィールド名が「年」「受付日」となり、せっかく「月」でグループ化している のに、「受付日」という表示は何となく面白くありません。
[図05116]
なので、「年」「月」「日」の3つでグループ化します。
[図05117]
すると、下図のように、「年」「月」「受付日」のフィールド名が出来ます。
[図05118]
「年」「月」のフィールドだけを「レポートフィルタ エリア」へ移動して、「受付日」は削除します。
[図05119]
下図のようになります。こうなれば、「年」「月」を素直に選べるようになると思います。
[図05120]
<「列ラベル」「行ラベル」という表示から「フィールド名」への変更> 上図で「受付支店」「講座名」のフィールド名が表示される部分が「行ラベル」「列ラベル」 という表示になっています。これは2007からのピボットテーブルを作成した時の初期設定のレ イアウトである「コンパクト形式」の表示の場合になるようです。
[図05121]
「コンパクト形式」では、複数の行ラベルがあった場合でも全部A列にインデントをつけて表示 されるので、個別のフィールド名は表示できないからだと思います。
[図05122]
「アウトライン形式」にすると、下図のように「受付支店」「講座名」のフィールド名が表示 されます。
[図05123]
「表形式」にすると、下図のようになります。枠線も表示されるので、このあとはこれを使っ て次の操作をしていこうと思います。
[図05124]
<列幅の変更が戻らないような設定> 上図では列の幅がまちまちなので、A列からG列まで同じ列幅に変更しました。 でも、このままではピボットテーブルに変更を加えた時に、元の列幅に戻ってしまいますので 変更したままになるようにします。
[図05125]
「ピボットテーブルツール」-「オプション」-「オプション」で出てくるダイアログボックス の「レイアウトと書式」タブの「更新時に列幅を自動調整する」のチェックを外して、「OK」 とします。
[図05126]
<アイテムの移動> 「受付支店」「講座名」のアイテム名を、下表と同じにしたいと思います。
[図05110]
何らかの基準で並べ替えが出来る場合は並べ替えの操作を使えますが、今回は勝手な配置なの で、手でアイテム名を移動します。 まず、「パソコン教室」を「英会話」の右へ移動します。 「パソコン教室」のセルを選択し、セルの枠線あたりでマウスポインタが下図のような4方向の 矢印になるところで掴みます。
[図05127]
掴んだまま「英会話」の右へ移動します。すると、下図のように太線が「ここね？」という感じ に出ますので、そこでマウスをはなします。
[図05128]
下図のように場所が入れ替わりました。
[図05129]
「陶芸」も同じように掴んで、
[図05130]
「彫金」の左へ移動して、マウスをはなします。
[図05131]
これで列のアイテム名が、しようと思った配置になりました。
[図05132]
次は行のアイテム名の移動です。 「大田支店」を一番下に移動したいと思いますので、大田支店をマウスで掴みます。
[図05133]
墨田支店の下に太線が来たら、マウスをはなします。
[図05134]
これで行のアイテム名も、しようと思った配置になりました。
[図05135]
<ピボットテーブルの「要素」を「選択」して書式を設定> ピボットテーブル レポートに対して書式の設定、データのコピー、または変更を行う時に、コ マンドやマウスを使用して個々のセルまたはデータ領域を選択します。 マイクロソフトサポートの「ピボットテーブル レポートのデータを選択する」に詳しい解説が あります。 たとえば「講座名」を「選択」したい時は、「講座名」のセルの上枠あたりにマウスを持って いくと、マウスポインタが下図のような太い矢印になります。
[図05136]
ここでクリックすると、下図のように「講座名」のアイテムが全部「選択」されます。
[図05137]
もし、太い矢印にならない場合には、下図の「ピボットテーブルツール」-「オプション」-「選 択」の下の▼をクリックして「選択範囲を有効にする」が選択されているかどうか確認してくだ さい。（初期設定では選択されている状態になっています。）
[図05138]
せっかく「選択」したので、「パソコン教室」の右が切れているのを、直したいと思います。 「選択」した状態で「セルの書式設定」のダイアログボックスを出して、「縮小して全体を表示 する」のチェックを入れて「OK」とします。
[図05139]
これでちゃんと見えるようになりました。
[図05140]
他の書式は、いろいろやってみてください。 今回はこれでおしまいです。