データを扱う時のいろいろな操作 1
| ■ Excel豆知識28 |  |
|---|
28-1 ピボットテーブル（行と列の制限、集計方法）
前回はピボットテーブルの基本的なことについて説明をしました。ピボットテーブルの動き に慣れましたでしょうか？ ピボットテーブルのいろいろな機能についてどういう順序で説明するのが良いか考えましたが、 結局、思いつくままに書いてみることにしました。(^^ゞ
<Excelの１枚のシートの行と列の数>
（１）Excelのシートは何行あるかご存知でしょうか？ Ctrlキーを押しながら下向き矢印↓キーを押してみてください。下図のようにシートの最終行 へセルが移動したと思います。65536行。これがExcelの1枚のシートの行数です。
[図02801]
（戻る時は、Ctrlキーを押しながら上向き矢印↑キーを押してください。） （２）では、1枚のシートに何列あるでしょう。 今度はCtrlキーを押しながら右向き矢印→キーを押してみてください。IV列。これが1枚のシ ートの最終列です。でも、これでは何列あるのかわかりませんので、2行目に =COLUMN() と いう列番号を返す関数を入れてみました。256列。これがシートの列数です。
[図02802]
横に256列、縦に65536行。これがExcelの1枚のシートの大きさです。
列に比べて行の数がずっと多いのがわかると思います。
豆知識20で「リスト」について リストとは、「各列に見出しがあり、それぞれの列見出し
の下に同じ形式のデータが並んだ表」のことです。とご紹介しましたが、項目行を除けば
65535件のデータまで1枚のシートで扱うことが出来るというわけです。
[図02803]
<ピボットテーブルの列方向の制限> ピボットテーブルでは項目を行フィールドにも列フィールドにも自由に設定することが出来ま すが、シートの大きさを超えて表示することは出来ません。 行方向には殆ど制限を感じないと思いますが、列方向ではそうはいきません。 例えば毎日のデータを1年分日付ごとに集計しようと思ったら、365の枠が必要ですが、Excel の列は256列までしかありませんから、あふれてしまいます。項目を表示する列が最低1列は必 要ですから、列方向には255までしか表示することができません。 私が仕事で扱っている会計ファイルからとったデータを例にとると、日付を列フィールドに設 定しようとすると次のようなメッセージが出ます。
[図02804]
なので、もし255を超えるものを表示させようと思ったら、行のフィールドへその項目を設定す るようにします。 或いは、上記メッセージが出て、「ドロップを続ける」としてとりあえず255だけ表示させてお いて、月ごとにグループ化してしまえば項目列と合計列をあわせても14列に縮めることが出来 ますので、そういう使い方もあります。 すると、下記のようなメッセージが出ますから「OK」として、そのあとでグループ化という作 業をします。
[図02805]
[図02806]
下図のように日付が月ごとにグループ化されましたので、列方向に日付の項目をおいても大丈 夫になりました。（グループ化についてはこちらをご覧ください。） でも、普通は沢山の枠が必要な項目については行フィールドの方に設定するのがいいと思います。
[図02806-2]
「4月」から「3月」という順に並び替えするやり方はこちらをご覧ください。
<ピボットテーブルの行方向の制限>行は65536行あって、項目行を除けば65535件までのデータを1枚のシートで扱うことが出来る ということはおわかりいただけたと思います。 6万5千もあれば絶対十分かというと、そうでもありません。 また私の会計ファイルを例にとると、1年分のデータは3万行を越します。それを何年分も一 度に集計したい、となると1枚のシートには入りきりません。 じゃあ出来ないか、というとそうでもありません。 ピボットテーブルのウィザードの1/3に「分析するデータのある場所を選択」するところが ありますが、そこに ・Excelのリスト/データベース ・外部データソース ・複数のワークシート範囲 とあります。
[図02807]
「複数のワークシート範囲」というのがあるので、これで出来るのでは、と最初は誰でも考 えると思います。ところがこれはちょっと想像とは違うものなのです。 複数のシート上にある既にクロス表の形になったものを統合させる、というような場合にし か使えないようなのです。 （「複数のワークシート範囲」ついては Excel豆知識40:ピボットテーブル（複数のワークシート範囲から） を参照してください。） なので、1枚のシートに収まりきれないデータを扱うためには使えません。 「外部データソース」、これを使うと出来るのです。 Excelでは1枚のシートで65535件までしか扱えませんが、データベースソフトではそれ以上で も全然平気です。Officeのソフトで言えばAccessがデータベースソフトです。そのAccessの テーブルに何年分の会計ファイルのデータも一度に入れられます。それを「外部データ」と してデータソースにして、ピボットテーブルだけをExcelに作ることが出来ます。増えたデー タはAccessのテーブルに追加していって、ピボットテーブルは「データの更新」をクリック するだけで大丈夫なのです。^^ この「外部データソース」としては、他のExcelブックを選択することも出来ます。別にデー タが多い場合だけでなく、データとピボットを別のExcelファイルにしておいたほうが便利な 場合などはこれを使います。 （「外部データソース」ついては Excel豆知識33:ピボットテーブル（外部データソースから） を参照してください。） なお、Microsoftサポートの記事 [XL2000]Excelのピボットテーブルの制限について [XL2002] Excel 2002 のピボットテーブルの制限 [XL2003] Microsoft Excel 2003 のピボットテーブル レポートの制限 にピボットテーブルの仕様および制限についての説明があります。
<集計の方法について>
例えば下図のように「売上額」の列に空白があったり、文字があったりする場合、この表から ピボットテーブルを作成すると・・・。
[図02808]
下図のように「売上額」の数字が妙な具合になります。
ここを見るとわかるように、「合計」ではなく「データの個数」になってしまっているからです。 これを合計にするためには 下図の「データの個数/売上額」という部分で右クリックし、「フィールドの設定」をクリック します。
[図02809]
ピボットテーブル フィールドの「集計の方法」が「データの個数」になっていますので
[図02810]
「合計」を選択して「OK」とします。
[図02810-2]
これで下図のように欲しい形になります。
[図02811]
データが数字で埋まっている場合以外は最初「データの個数」になると思いますので、上記の 手順で「合計」を選択してください。それ以外は必要に応じて選択してください。
[図02812]
この「集計の方法」は11種類あり、豆知識26回で紹介した「集計」の場合の集計方法と同じです。
[図02812-2]
「データの個数/売上額」のところで右クリックして出したメニューですが、ピボットテーブル ツールバーの「ピボットテーブル」という部分の▼をクリックしても出てきます。
[図02813]