データを扱う時のいろいろな操作 1
| ■ Excel豆知識31 |  |
|---|
豆知識29でSUMPRODUCT関数を使って下の受付データを受付支店別・講座別・月別に集計しま した。今回はピボットテーブルで同じ形式の集計表を作る場合のいろいろな工夫についてご紹 介します。（別に同じ形にする必要は全然ないのですが、それをすることでいろいろな操作を ご紹介できますので。）
[図03101]
31-1 ピボットテーブル（いろいろな設定）
関数での集計表と同じ形にするために、いろいろな操作を用いています。 ・グループ化 ・ページフィールドの設定 ・表示する項目の選択 ・フィールド名の表示の変更 ・データのないアイテムの表示 ・空白セルに表示させるものを設定 ・項目の表示位置の移動 ・書式の保持の設定 書式に関連して「ピボットテーブルの集計行のみに色をつけるには？」はこちら。 日付のグループ化が出来ない場合はこちら。
（１）リスト中のどこかのセルを選択して、ピボットテーブルウィザードを出し、「完了」を クリック。
[図03102]
豆知識第27回で説明したように、 ピボットテーブルウィザードは3画面で構成されますが、データがリスト形式になっている場合、 2/3では表中のセルを選択してあれば自動的に表全体が指定されますし、 3/3で通常は新規ワークシートに作成したほうがわかりやすいので、 1/3の 画面で「完了」をクリックして、すぐにピボットテーブルの枠を表示させてしまって 良いと思います。 豆知識第30回でご紹介した参照範囲を可変にするのは、データに追加があって、それを次々にピボットテーブル に反映させていきたい場合に用いる方法です。 普段は上記（１）のようにしてウィザードの1/3の画面で「完了」をクリックして大丈夫です。
（２）フィールドリストから「受付日」を行のエリアへドラッグ。
[図03103]
（３）日付のグループ化 日付を「年」「月」でグループ化します。同じ年だけのデータなら「月」でグループ化するだ けで大丈夫です。でも、「月」だけにすると2005年の1月も2006年の1月も同じになってしまい ますので、ここでは一応「年」と「月」でグループ化しようと思います。 また、グループ化したあと、それをページエリアへ移動させます。 はじめからページエリアへ持っていくと、なぜかグループ化の作業が出来ませんので、まず行 のエリアに置いてグループ化をして、その後ページエリアへ移します。 （日付のグループ化が出来ない場合はこちらをご覧ください。） （Excel2000では行エリアに項目をドラッグしただけでは日付が表示されません。データエリアにも何か項目 をドラッグしてピボットテーブルを一旦完成させてから、グループ化の作業をしてください。）
[図03104]
[図03105]
（４）「年」と「受付日」をページエリアへ移動 （３）の操作で下図のように年・月でグループ化されました。この「年」と「受付日」（「受 付月」の方がいいですね。直してみてください。）を上のページエリアへドラッグします。 はじめに「年」をドラッグし、
[図03106]
その下に「受付日」をドラッグします。
[図03106-2]
（５）行のエリアへ「受付支店」をドラッグします。
[図03107]
（６）列のエリアへ「講座名」をドラッグします。
[図03107-2]
（７）データエリアへ「氏名」をドラッグします。 これは受付件数を数えたいだけなので、別に「氏名」でも「講座名」でも「会員番号」でも何 でもかまいません。ただ、「会員番号」にして「合計」なんてされてしまうと意味がありませ んので、どの項目を用いた場合でも「データの個数」にする必要があります。 項目が「会員番号」のように数値だと、「合計」が最初に選択されますが、「氏名」などのような文 字データの場合は最初から「データの個数」が選択されます。
[図03108]
（８）項目の表示の変更 ピボットテーブルのフィールド名などは数式バーで変更することが出来ます。ただ、既に項目 名として使われているものと同じものを入力することは出来ません。どうしても同じものを使 いたい場合は、数式バーで頭にスペースを入れてから入力すれば、別のものと認識されま すので、使うことが出来ます。例えば、「受付支店」というのをこの「データの個数/氏名」 の部分に入れたい場合は、「 受付支店」というふうに頭にスペースを入れます。
[図03109]
（９）表示する項目の選択 それぞれのフィールド名の右の▼をクリックすると出てくるリストで、表示させたい項目を選 択することが出来ます。「年」で「2005年」を、「月」で「9月」を選択してみましょう。
[図03110]
すると、下図のように9月の受付データだけの集計が表示されます。 受付支店や講座名の右の▼をクリックして、表示するものを指定することができます。 試してみてください。
[図03110-2]
ここで、SUMPRODUCT関数を使った集計表を見てみましょう。 受付支店と講座名は全部表示されていて、受付のないデータはゼロが表示されています。 上記のピボットテーブルの9月分にはゼロの分は表示されていません。
[図03111]
（１０）データのないアイテムの表示 データがない分も表示させるように設定をします。フィールド名のセルで右クリックし、「フィ ールドの設定」をクリックします。
[図03112]
ピボットテーブルフィールドのダイアログボックスの「データのないアイテムを表示する」に チェックを入れ「OK」とします。
[図03112-2]
（１１）空白セルに表示させる値の設定 上記操作でデータのない項目も表示できました。でも、関数で作った集計表にはデータのない 部分にはゼロが入っていました。なので、ピボットテーブルの空白部分にもゼロを表示させて みましょう。 データフィールドの項目の部分で右クリックし、「オプション」を選択します。
[図03113]
ピボットテーブル オプションのダイアログボックスの「空白セルに表示する値」の右の四角に
「0」など を入力し「OK」とします。
「*」など好みの設定が出来ます。ゼロを表示するには「空白セルに表示する値」のチェックをオフにしても
出来ます。（ヘルプには「ゼロ（0）を表示するには、このチェックボックスをオフにします。」とありまし
た。）
[図03114]
（１２）項目の表示位置の移動 さて、今度は「講座名」や「受付支店」の並び順も関数で作った集計表と同じにしたいと思い ます。
[図03115]
移動させたい項目のセルの枠の部分でマウスを動かすとマウスポインタが4方向へ広がる矢印 のマークになりますので、それを掴んで場所を移動させます。
[図03116]
[図03116]上の項目の移動のところで、Excel2000ではマウスポインタが４方向への矢印にはなりませんが、
セルを選択してから枠線付近をマウスで掴むとドラッグして移動できると思います。
（１３）書式の保持の設定 次に、受付支店と講座名のセルを薄い水色に、総計の欄を薄い緑にします。 また、表全体に罫線をつけ、列幅を調整します。 これでだいたい関数で作った集計表と同じような集計表が出来上がりました。
[図03117]
でも、ピボットテーブルで「データの更新」をすると、書式が戻ってしまいます。 なので、書式が保持されるように設定をします。 ピボットテーブルの中のセルを選択した状態で右クリック。「オプション」を選択します。 ピボットテーブルオプションのダイアログボックスで、「表のオートフォーマット」のチェ ックをはずし、「書式の保持」にチェックを入れて、「OK」をクリックします。
[図03118]
これで、「データの更新」をしても、罫線以外の書式（列幅とか色とかセルの表示形式とか） が保持されます。 「表のオートフォーマット」のチェックをはずすことによって保持されるのは「列幅」です。 レイアウトの変更をした場合も列幅の自動調整が行なわれませんので、自動調整の方が良い 場合はそのチェックを入れてください。
[図03119]
いろいろな設定や操作のご紹介をしました。 いつもこれらを使うわけではありませんが、こんなことも出来ると知っていると、何かの時に役 に立つかもしれませんので。 なお、上記の方法ではどうしても罫線の保持をすることが出来なかったのですが、アイテム毎に 個別に範囲を選択して設定すると、縦罫線や横罫線が保持されます。 ほんとに個別に設定しなければなりませんので、面倒ではありますが、豆知識44に罫線の維持も 含めたピボットテーブルの書式設定についてまとめましたのでご覧いただければと思います。