Excel ピボットテーブルでのグループ化について

質問

Excelのピボトテーブルは結構愛用しているのですが
半期とグループ化が出来ず困っています
欧米では四半期での決算が主流なのでしょうか
四半期や月・年などではグループ化できるようなのですが…

上期/下期をワークスペースとして
新たに設けたセルで計算させて今は対応しているのですが

演算セルが増えるとデータ量が膨大になるにつれ
折角のピボットテーブルの軽快さが失われる懸念がします

何か良い方法はないでしょうか？

Excelは確か2004だったかを使っています

あと、
単純なダミーデータでは月でグループ化できるのに
下に示すような本ちゃんのデータをピボット化した場合では
日付を「グループ化できない」と
エラーを示してきます。

考えられる原因としてどんなものが挙げられるでしょうか？

以上２点
宜しくお願い致します。

勤務日　　シフト　型番　使用数　勤務日&シフト
５／１０　　Ａ　　５　　　２０　　５／１０Ａ
５／１０　　Ａ　　２　　　２０　　５／１０Ａ
５／１０　　Ａ　　４　　　２０　　５／１０Ａ
５／１０　　Ｂ　　５　　　２０　　５／１０Ａ
５／１１　　Ａ　　５　　　２０　　５／１１Ａ
５／１１　　Ｃ　　１　　　２０　　５／１１Ａ
５／１４　　Ａ　　３　　　２０　　５／１４Ａ
５／１４　　Ａ　　５　　　２０　　５／１４Ａ
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　↓

　　　　　　　　　　　　　型番
勤務日　　勤務日&シフト　　１　　　２　　　３　　　４　　　５
５／１０　５／１０Ａ　　　 ０　　２０　　　０　　２０　　２０
５／１０　５／１０Ｂ　　　 ０　　　０　　　０　　　０　　２０
５／１１　５／１１Ａ　　　 ０　　　０　　　０　　　０　　２０
５／１１　５／１１Ｃ　　 ２０　　　０　　　０　　　０　　　０
５／１４　５／１４Ａ　　　 ０　　　０　　２０　　２０　　２０
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・

この最初の項目の勤務日を表示名「月」に変えて(此処までは出来ます)
月毎表示にグループ化したいです
他のピボットでは出来るのですが…　(;^_^A アセアセ…

※(数字は適当に書いたので計算が合わないかも？です)

回答No.2

OtenkiAme · ベストアンサー

こんにちは。

数式に名前定義して利用する方法があります。

１）データリストのシートを表示させます。
２）挿入＞名前＞定義を選択します。（[Ctrl]+[F3]）
３）名前テキストボックスに "Database" と入力します。
４）参照範囲に次の内容に則った式を入力します。
　　=シート名!参照データの左上セル:INDEX(シート名!参照データの最終列,COUNTA(シート名!空白セルのない列)）
　　例えば、"PvtData"ワークシートにデータリストがA1からE10001まであり、空白セルを作らない列がB列だったら、
　　=PvtData!$A$1:INDEX(PvtData!$E:$E,COUNTA(PvtData!$B:$B))
のように式を作成します。
　　なお、データリストがA列でなかったり1行目から始まっていなかったら、左上のセル番地を変更し、COUNTA関数の部分を COUNTA関数 - 上部の空白行数 に変更してください。
５）作成が終わったら［追加］＞［OK］をクリックします。
６）範囲の設定をしたいピボットテーブルを選択します。
７）ピボットテーブルツールバーの
　　ピボットテーブル＞ウィザード＞戻る（…ウィザード - 2/3）
８）「名前の貼り付け」リストボックスを表示（[F3]）させ、
　　"Database"を選択して[OK]します。
９）ウィザード2/3を[完了]をクリックします。

以後、データを入力して更新すれば、データを入力した範囲までピボットテーブルの範囲として認識されるようになります。

お礼

ホントに感服します
凄いですね
ありがとうございます

補足

厚かましいかも知れませんが
出来れば後３点
ご質問させて頂ければと思います。

１点目は
ピボットテーブルにおいての
日付データの半期でのグループ化です。
やはり2004では無理なのでしょうか？

もう１点ですが、
日付を月毎でグループ化した場合に生じる事についてです。
例えば
日付データが2007年12月28日から
2008年1月7日の範囲を超えてたとします。

どうなのでしょう？
2007年12月28日を12と
2008年1月7日を1と
解釈しているのでしょうか(？)

昇順ソートの結果が
2008年1月7日の方が値が小さいと判断されているのか
先になってしまうようです。
この症状はこの期間に限らず
年を跨ぐ場合にすべからず見られるように思います。

日付データの写像セルを別に設けて
それをデータ範囲に盛り込んだ上
ソートのキーと指定しても改善されないようで
データが何年にもに渡った場合、
今年の1月と過去の1月を同一視されたら…
と思うと甚だ気が重くなります。

これは
「避けられないことでピボットを作成後に
手動で並べ替えざるを得ない」
ものなのでしょうか？

月でのグループ化を諦めて
黒矢印で列(又は行)全体を指定して
表示形式をm"月"と設定することにより
対処すべきなのでしょうか？

恐らく自動では
同一値を示すセルが結合されないので
出来れば避けたい対応なのですが…

最後の1点ですが、
今回御指南頂いた
名前フィールドを用いる
ピボットテーブルのデータ領域指定法についてです。

この手法の要となるポイントは
絶対参照で普通は行うデータ領域の範囲指定を
ワークシート関数(？)を用いて
相対参照どころか
動的に領域を指定してしまえている
と言う解釈で良いのでしょうか？

お示し頂いた
「=$A$1:INDEX($E:$E,COUNTA($B:$B))」
と言う構文に理解がついて行かず
少々混乱しております

お示し頂いたものは
例えば
「=offset($A$1,0,0,COUNTA($B:$B),5)」
として領域名を確保しても同じ働きになるのでしょうか？

少々不躾な物言いになってるかも知れませんが、
出来れば「混乱しているが故」とご理解頂いて
再度そのお心の温かさに縋りたく思います。

どうぞ解説の程を宜しくお願い致します。

回答No.4

OtenkiAme

こんにちは。
日付のグループ化ですが、以下のようにしてください。

> 状況としては、全く同じ日付データ内容の項目を２つ
> 「月」?「日付」という項目名で盛り込み
> ピボットレポートを先ず作ります。
レイアウト枠内には「日付」だけ置いてください。
２つ置く必要はありません。

> フィールド設定で
> 項目名「日付」を昇順指定し
昇順なら指定する必要はないと思います。

「日付」フィールドを選択して
グループと詳細の表示＞グループ化を選びます。
（Macのコマンド名は分かりません）

グループ化ダイアログボックスから、
「日付」と「月」を選択し、OKをクリックします。
「日付」の親フィールドに「月」フィールドが表示されます。

お礼

またまた素早い対応有り難う御座います

それがですね
過去からの慣習とかで
日付の表示形式が
「m/d」に指定されているのです。

私も一度「1項目で良いのでは！！」
と、ひらめき試してみたのですが

表示形式の書式指定より
グループ化の結果が優先されるらしく
指定が反映されず困りました

結局さんざん試した後に
最後の試しにグループ化を解いてみたのですが
途端に書式設定が反映されました

それ以来日付データの本体「日付」と
その写像データの「月」と
日付データから算出した上下期欄「期」を用い、
このうちの「月」項目をグループ化するようにしています

しかし月をグループ化した途端
何度やっても1月が先頭に来るのです
(;^_^A アセアセ…

表示形式が無視されるのを見た後で
年データが無視されているのを見付けたので
落胆してしまいました

でも今回がレアケースだと知り
少し勇気づけられました

有り難う御座います

回答No.3

OtenkiAme

こんにちは。

> 出来れば後３点
> ご質問させて頂ければと思います。
回答できるものとできないものがあります。<(_ _)>

> １点目は
> ピボットテーブルにおいての
> 日付データの半期でのグループ化です。
> やはり2004では無理なのでしょうか？
2004に限らず、Windows版のエクセルでも"半期"のグループ化はないです。
"半期"が必要な場合は、元のリストに"半期"のデータを作って対処しています。
締日による月のグループ化や会計年度による四半期のグループ化、等、ピボットテーブルが用意した日付のグループ化ではできない場合もありますから、リストに望みの結果となるフィールドを追加して対処させればよろしいかと思います。

> もう１点ですが、
> 日付を月毎でグループ化した場合に生じる事についてです。

> 昇順ソートの結果が
> 2008年1月7日の方が値が小さいと判断されているのか
> 先になってしまうようです。
> この症状はこの期間に限らず
> 年を跨ぐ場合にすべからず見られるように思います。
長年、Windows版エクセルのピボットテーブルを使っていますが、
このような現象は見たことがありません。

Macは使ったことがないので分かりませんが、
全てのブックで同じ現象が起きるのでしょうか？
それとも、特定のブックだけで起きるのでしょうか？
或いは、
他のフィールドの「フィールドの設定」の［詳細］で並べ替えの順序を指定していてそちらが優先されている、ということはないでしょうか？

例えば、上の"半期"の件ですが、元のリストに1月～6月を"上半期"、7月～12月を"下半期"と出るように計算させておいて、行エリアに年、半期、月を置くと、半期は、何も指定していなと下半期=>上半期の順に並ぶので、月は、7月から表示されるようになります。
つまり、月の規定値は、昇順に並ぶかもしれませんが、他のフィールドの並べ替えの影響を受けることがあるということです。
月が1月から表示されるようにするには、半期のフィールドの設定の詳細で降順並べ替えの指定をするんですね。
他の（日付以外の）フィールドの「フィールドの設定」の［詳細］で並べ替えの設定がされていないか確認されては如何でしょうか？　それ以外に考えられる原因は、ご質問の内容からは分かりません。<(_ _)>

> 最後の1点ですが、
> 今回御指南頂いた
> 名前フィールドを用いる
> ピボットテーブルのデータ領域指定法についてです。

> 例えば
>「=offset($A$1,0,0,COUNTA($B:$B),5)」
>として領域名を確保しても同じ働きになるのでしょうか？
OFFSET関数では、A1セルを基準として高さと幅を変化させているのに対して、INDEX関数を使った方法では、最後のセル範囲のみ変化させています。
結果として、A1セルから対角の末端セル範囲を返して同じ範囲を参照するので、同じ働きをしていると解釈していいのではないでしょうか？

ちなみに、OFFSET関数を使うなら
=OFFSET($A$1,0,0,COUNTA($B:$B),COUNTA($1:$1))
のように幅もCOUNTA関数で求めるようにするとフィールド数が増えても対処できるようになります。

お礼

有難う御座います、
重ねて御礼申します。

月でのグループ化ですが
状況としては、全く同じ日付データ内容の項目を２つ
「月」?「日付」という項目名で盛り込み
ピボットレポートを先ず作ります。

このままでも既にちゃんと順番に並んでますが
フィールド設定で
項目名「日付」を昇順指定し
これ以外の項目をソート無しに指定します
この段階でも正しく並んでいます。

ここで、日付データの項目名「月」を月毎にグループ化すると
途端に並びが変わってしまいます。

こんな感じです。

回答No.1

OtenkiAme

こんにちは。

日付のグループ化では、日付フィールドの範囲に空白セルが含まれているとグループ化できません。
また、集計アイテムを使っている場合は、併用することができません。

まず日付フィールドの元データの範囲に
空白セルがないか、
日付と認識されていないデータがないか、
を確認されては如何でしょうか？

また、データソースの範囲指定を列指定にしたり、データの入っていない範囲も含めて指定している場合もダメです。
正しい範囲が指定されているか、確認されては如何でしょうか？

お礼

早速の的確なご回答有り難う御座います
痛み入ります

図星です
流石ですね

新しいデータを入力しても
更新のみで対応できるように
空白領域をかなり含めてあります

取り敢えずは
この空白領域を取り除けば
幾重にもグループ化できると言うことですね

感謝します

補足

所で
そうなってくると又新たな疑問がわいてきます

暫時新たなデータが追加されるのですが

その度に
ピボットテーブルのデータ領域を
タイトな状態で指定し直さないと
やはり駄目ってことになるのでしょうか？

だとしたらグループ化やその他の設定もリセットされて(？)
一から設定を全てやり直しせざるを得なくなるのでしょうか？

何かこれらの手間を回避する良い手だてはないかと思うのですが…
如何でしょうか？

本来、追加質問は別スレッドを立てるべきかも知れませんが
そこを枉げて
是非OtenkiAme様のお知恵を
更にお借りしたく思いますので
御指南どうぞ宜しくお願い致します