2015年04月12日

市区町村を都府県でグループ化してみた(データの無いアイテムの表示が出来る)/Excel/ピボットテーブル

Excelのピボットテーブルで市区町村のアイテムを都府県で「グループ化」してみます。
"都府県"と"市区町村"等の上位・下位等の従属関係のあるフィールドで「データのないアイテムを表示する」を行う場合、グループ化を行うことで正しい組合せのアイテムを表示させることが出来ます。
つまり、複数のフィールドのアイテムの組合せを制約(ヒモ付き)にすることが出来ます。

例えばこんな、"月"と"区市町村"と"販売台数"のデータがあります。(高級車とか?)
20150412_001.JPG

こんなフィールドリストでピボットテーブルを作ります。
20150412_003.JPG

表形式で、小計は非表示にしています。
20150412_002.JPG

グループ化
ではグループ化をしていきます。
まず、神奈川県からグループ化していきましょう。
"横浜市"を選択し、「Ctrl」キーを押しながら離れている"川崎市"を選択します。
20150412_004.JPG

「ピボットテーブルツール」、「オプション」、「グループ」から「グループの選択」を選択します。
20150412_005.JPG

すると、"区市町村2"と言うフィールドが追加され、"横浜市"と"川崎市"が"グループ1"と表示されました。他の区・市はそのまま区・市名が表示されています。
20150412_006.JPG

今度は、千葉県をグループ化してみます。
"千葉市"を選択し、これも離れているので「Ctrl」キーを押しながら"松戸市"を選択します。
今度は、そこで右クリして「グループ化」を選択します。
メニューからでも、右クリからでもどちらからでもグループ化ができます。
20150412_007.JPG

"松戸市"と"千葉市"が"グループ2"と表示されました。
20150412_008.JPG

他の区市も同様にグループ化します。
20150412_009.JPG

グループの名前を変更していきます。
"グループ1"を選択して「F2」キーを押して編集します。
20150412_010.JPG

"神奈川県"と入力します。
20150412_011.JPG

他のグループも同様に編集します。
20150412_012.JPG

ついでに、フィールド名も同じように編集します。
"都県"にしてみました。
20150412_014.JPG

ちなみに、フィールドリストには"都県"というフィールドが追加されており、あたかもデータソースのフィールドリストと同様に扱われているのが分かります。
20150412_017.JPG

ここで、データソースに新たにデータが追加されました。
4月に"成田市"(千葉県)のデータが追加されました。
20150412_015.JPG

ピボットテーブルを更新すると
まだ千葉県にグループ化されていないので、"都県"は"成田市"で集計されています。
20150412_016.JPG

これも、千葉県にグループ化していきましょう。
"成田市"、"千葉市"、"松戸市"を選択してグループ化します。
20150412_019.JPG

一旦、"千葉県"と言うグループ名が"グループ2"とかに戻るので、改めて"千葉県"と編集します。
20150412_020.JPG

以上で、グループ化で都府県毎に区市町村が集計されるようになりました。

さて、わざわざグループ化で都府県のフィールドを作ったか?
そのメリットを検証したいとおもいます。
グループ化をすることで、"都府県"のフィールドと"区市町村"のフィールドには従属関係が設定されます。
つまり、"東京都"には"杉並区"と"世田谷区"しか含まれないと設定されたわけです。。。
ここが重要な点です。

ここで、"区市町村"のフィールドで「データの無いアイテムの表示」をしてみましょう。
"区市町村"のフィールドのアイテムを選択します(ここでは水戸市)
20150412_021.JPG

「ピボットテーブルツール」、「オプション」、「アクティブなフィールド」から「フィールドの設定」を選択します。
20150412_022.JPG

「レイアウトと印刷」から「データのないアイテムを表示する」にチェックをいれます。
20150412_023.JPG

また、データのないアイテムが表示された時に"値"(販売台数)には空白ではなく"0"を表示させます。
「ピボットテーブルツール」、「オプション」、「ピボットテーブル」から「オプション」を選択します。
20150412_024.JPG

「レイアウトと書式」から「空白セルに表示する値」に"0"を入力します。
20150412_025.JPG

すると、このように3月、4月に販売データがなかった区市町村のデータが"0"として表示されました。
20150412_026.JPG

これを、グループ化を使わずに、ソータソースに都府県のフィールドがあるとどうなるかというと。。。
こんな、予め"都道府県"のデータが入力されているデータソースがあります。
20150412_027.JPG

こんなフィールドリストでピボットテーブルを作ります。
グループ化で作った"都県"は使用しません。
20150412_029.JPG

ピボットテーブルはこうなります。
20150412_028.JPG

ここで、"区市町村"で「データのないアイテムを表示する」をおこなうとどうなるかというと。。。
こんなことになってしまうのです。
20150412_030.JPG
茨城県 横浜市!? ありえないデータが表示されてますねー。
"都道府県"の全てのアイテムに対して"区市町村"の全てのアイテムが表示されています。
もともとデータがないアイテムですから、集計結果に何も影響は及ぼしませんが、これはどうみても変でしょ!?
データソースのフィールドでは従属関係等は当然設定されませんから、こんな結果になってしまうわけです。

これで、わざわざグループ化した訳がおわかりでしょうか。。。


関連ページ
アイテムを選択してグループ化してみた
「データの無いアイテムを表示する」で注意しなければいけないこと(あり得ないアイテムが表示される)


posted by haku1569 at 14:05| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。