haku1569　Excel でらくらく データ分析！

Excelのピボットテーブルで日付でグループ化しようとした時、「グループフィールド」のボタンが押せない（アクティブ）にならず、グループ化が出来ない時があります。その原因と対策を紹介します。


例えば受注データがあって、受注日でグループ化しようとしましたが、「グループフィールド」ボタンがアクティブ（黒色）にならず、クリックできません。


よく見るとピボットテーブルの一番下に（空白）があり、元データのレコードに受注日が空白のものが含まれていることが分かります。


元データを調べてみたら、元データには空白セルは無かったのですが、ピボットテーブルの対象範囲である“データソース”に元データの行数よりも余分に空白行も含んでいることが分かりました。これを削除して元データの範囲と、“データソース”の範囲を一致させたら、無事にグループ化できるようになりました。。。

でも、いつも“データソース”の範囲設定は元データが増えて行ってもいちいち「データソースの変更」をしなくてもいいように、余分な行をいれてたはずだけど、と別なファイルを開いてみたら、、
空白セルがあるのに、問題無くグループ化ができるじゃありませんか!?


んで、いろいろ調べてみたら、空白セルによりグループ化が出来なくなるのは。。
Excel2003以前のバージョン（拡張子“.xls”）で作られたピボットテーブルの場合グループ化が出来なくなることがわかりました。
2003以前で作られたピボットテーブルを2007で開いて、2007のバージョン（拡張子“.xlsx”）で保存してもダメです。
また、2007で作ったピボットテーブルを“97-2003互換”でファイル（拡張子“.xls”）してもグループ化は問題なくできることが分かりました。
つまり、ファイルのバージョンのせいではなく、あくまでもピボットテーブルを2003以前で作られた場合だけグループ化が出来なくなります。
もし、2007で使っていてファイルの拡張子が“.xlsx”でも、グループ化が出来ない場合は、2003以前で作られたピボットテーブルで操作している可能性があるので、空白セルがあるかどうかの確認が必要です。
また、その場合“データソース”の範囲を元データの範囲より余分に大きめに設定しておくことが出来なくなるって、元データが増える度に“データソース”も変更しなければならなくなって面倒なので、いっそピボットテーブルを作り直す必要もあると思います。。。

また、グループ化が出来なくなるのは空白セルだけでなく、日付のフィールドに文字が入力されているとグループ化ができません。
これは2007でも同じです。「受注日」のフィールドのフィルターを解除して（全てを表示させて）日付以外の文字などのデータが間違って入力されていないか、故意に「不明」等と入力されていないか、チェックが必要です。


関連ページ
日毎のデータを年月で集計してみた（グループ化）

Excelのピボットテーブルのラベルのフィルターには元データ（データソース）からはとっくの昔に削除したはずのデータのアイテムが依然と表示されてしまう。。。
幽霊か！？バグか！？。。。と思ったりするのだけど、、、
実はこれは「データソースから削除したアイテムの保持」という機能が働いているからのなのです。。。なんで！？
MicroSoftによると、「ブックと共に一時的にキャッシュする、各フィールドのアイテム数を指定する」とある。。。。。。m(__)m
さっそく、解除っ！


受注日と受注金額を集計したピボットテーブルがあります。


元データ（データソース）には“2013年の6月12日”からのデータしかない。
ここで、受注日のフィルタボタンを押してみる


すると、なななんと!? “1996年1月8日”からズラッとあるはずのない受注日が表示されている!!


じつはこの元データは元々1996年からのデータだったのだけど、それを2012からに書き換えたものだったんです。それがこんな所に表示されてしまうなんて。。。恥ずかしっ
んで、ピボットテーブルにカーソルを置いて。「ピボットテーブルツール」「オプション」「（ピボットテーブル）オプション」を選択します。


「ピボットテーブルオプション」画面が開きます。
「データ」タブから「データソースから削除されるアイテムの保持」を見ると「フィールドに保持するアイテム数」が「自動」になっています。


ここを「無し」にします。


それから、ピボットテーブルを更新します。
（更新しないと、いつまでも昔の名前が出てきます）


すると、やっと過去の記憶を消すことができました。。。


関連ページ

Excelのピボットテーブルを表形式で表示したときに複数の行ラベルをセルを結合して見やすくしてみます。ちなみにExcel2007です。


例えば受注データがあって、支社ごのと得意先別の受注金額を集計したピボットテーブルがあります。
普通、ピボットテーブルを作るとアウトライン形式で表示されます。


これを、表形式に変更すると、見慣れた感じになるのですが、
支社名は得意先でくくった行の一番上にしか表示されません。


ここで、ピボットテーブルにカーソルを置いて、「ピボットテーブルツール」「オプション」から「（ピボットテーブル）オプション」を選択します。


「ピボットテーブルオプション画面」が開きます。
「レイアウトと書式」タブから「セルとラベルを結合して中央揃えにする」をチェックします。


「OK」でいつも見慣れた（？）表になりました。


また、複数の行ラベルの場合ではなく、グループ化で日付を「年」と「月」に表示させた場合も同様にできます。


実は、このグループ化で行ラベルは「年」が新たに追加されるので、結局複数の行ラベルになっちゃってるんですけど。。。


たぶん、この手の機能はヴァージョンが上がると色々もっと便利なのが増えてるんだろうなと思います。2013とか、、


関連ページ
ピボットテーブルのデザインを変更してみた
更新しても列幅を固定する
集計フィールドの名前を変更する

Excelのピボットテーブルである時期以前のデータをひとまとめににして集計してみます。


「日毎のデータを年月で集計してみた」で年月集計されたピボットテーブルできました。
ここで、2012年以前のデータを年や月に関係なくひとまとめにしちゃおうと思います。
時系列のデータ集計の場合、ある時期以前をまとめてしまうってのはよくあることだと思います。


受注日にセルにカーソルを置いて。「ピボットテーブルツール」「オプション」「グループ」から「グループフィールド」を選択します。
「グループ化」画面が開きます。
ここで、「開始日」を“2013/1/1”と入力します。（つまり、それ以前は「年」「月」の集計対象外となります）
「単位」は「月」と「年」そのままにしておきます。


「OK」すると、「<2013/1/1」というラベルが表示され2012年以前の受注金額が全て集計されました。


では、ここまで出来たところでもう少し見栄えする表になるように一工夫してみましょう。
�@「年」に表示されているグループの折りたたみボタンを非表示にして、、
�A年のラベルが1月の行にしか表示されていないので、1月〜12月までのセルを結合して年を表示させてみます。
�Bそれと、自動的に名前が付けられた集計フィールドのラベル等をそれなりに変えて。。。


と、こんな素敵な表に、、、ってゆーか表として違和感のない（ピボットテーブル風）ではない表に仕上がりました。


関連ページ
日毎のデータを年月で集計する
フィールドの展開と折りたたみ（ボタンの非表示とかも）
セルとラベルを結合する
集計フィールドの名前を変更する

Excelのピボットテーブルで日毎のデータを「グループ化」を使用して年月で集計してみます。


例えば受注データがあって、一日毎の受注金額がピボットテーブルで集計されています。
もちろん、元データは受注日毎のレコードになっています。


受注日にセルにカーソルを置いて。「ピボットテーブルツール」「オプション」「グループ」から「グループフィールド」を選択します。
この時、「グループフィールド」がアクティブにならず（灰色のまま）クリックできない時があります。
そんな時はこちらを見て下さい。


「グループ化」画面が開きます。
ここで、「単位」で「月」と「年」両方を選択します。
「開始日」「終了日」は特に指定が無い時はそのままにしておきます。


すると、このように受注した年とその月毎に受注金額が集計されました。


個人的に、表形式が好みなので変更します。(^_^;)


フィールドリストをよく見ると「年」というフィールドが一番下に自動的に作られて、行ラベルに「年」と「受注日」の2つのフィールドが設定されています。
あっ、でも2012年6月の上に1996年1月8日以前のアイテムがあります。
こんなデータ元データには無いはずなのに。。。


ピボットテーブルでは、元データを更新してもそれまで存在したアイテムがフィルターの一覧に残ってしまいます。
バグ？と思いきや、なんと!わざわざ「削除したアイテムを保持」する機能があるんです。。。なんで！？
なので、それを解除してやれば、、と、スッキリ!!


で、このようにグループ化すると、「年」でフィルターをかけたり、


月でフィルターをかけたり、


自由に集計データを抽出することができます。
前年同月なんかも簡単に比較出来ますね。
また、グループの折りたたみを使うと、「年」だけの集計も一発で表示できます。

次に、この月毎のデータで“2012年以前”をひとくくりにして集計させてみたいと思います。
時系列のデータ集計の場合、ある時期以前をまとめてしまうってのはよくあるやり方です。。

関連ページ
日付でグループ化出来ない（グループフィールドボタンが押せない）時は
元データから削除したアイテムをフィルターに表示させない
アイテムを選択してグループ化してみた
フィールドの展開と折りたたみ（ボタンの非表示とかも）
「○○年以前」をひとくくりにして集計する
セルとラベルを結合する

Excelのピボットテーブルで“合計 / 金額”等とかってに表示される集計フィールドの名前を変更してみましょう。


例えばこんなように表示されている
“合計 / 金額”を“金額計”
“合計 / 数量”を“数量計”
“列ラベル”を“得意先名”
“行ラベル”を“受注年月”
に変更してみます。
“全体の　合計　/　金額”、“全体の　合計　/　数量”も変えたいのですが、小計や総計の名前は変更できません（少なくとも2007では）


まず、ピボットテーブルの上にカーソルを合わせます。
そして、「ピボットテーブルツール」「オプション」から「（アクティブなフィールド）フィールドの設定」を選択します。


「値フィールドの設定」画面が開きます。
「名前の指定」が“合計 / 金額”になっています。


ここを、“金額計”に変更します。
ホントは“金額”にしたかったのですが、“金額”はフィールド名として既にあるので、使用できません。


「OK」すると、ピボットテーブルの“合計 / 金額”が“金額計”に変更されました。


同じように、“合計 / 数量”を“数量計”に変更します。


と、だいぶ見やすい表になってきました。
小計の“全体の 金額計”はどうでしょう


“合計金額”あたりに変えようとすると、、、


と、変更できません。。。（Excel2007では）
しかたないので、“列ラベル”と“行ラベル”を変えてみます。
これは、「フィールドの設定」画面からではなく、通常のセルの編集で変更できます。
“列ラベル”のセルの上にカーソルを置いて、、


セルの編集で“得意先名”に変更します


同様に“行ラベル”も“受注年月”に変更します


これで、だいぶ見やすくなりました。。。よね！？


関連ページ
ピボットテーブルのデザインを変更してみた
更新しても列幅を固定する
セルを結合してラベルを中央に表示してみた（ピボットテーブルオプション）Excel2007

Excelのピボットテーブルでデータの無い空白セルに“0”表示をさせてみます。

例えば受注データがあって、5月と6月の仕入先毎の受注金額を集計したところ、5月に受注があったけど6月には無かったところ、またはその逆のケースでは元データに金額“\0”というデータ（レコード）が存在しないためセルが空白のまま表示されしまいます。


でここで、受注金額の集計表なので“0”と表示させてみます。
まず、ピボットテーブルの上にカーソルを合わせます。
そして、「ピボットテーブルツール」「オプション」から「（ピボットテーブル）オプション」を選択します。


「ピボットテーブルオプション」画面が開きます。
「レイアウトと集計」タブから「空白セルに表示する値」に“0”を入力します


「OK」すると、空白セルだったところに“\0”が表示されました。


ちなみに、元の空白セルに戻す方法ですが、設定した時と逆のことをするのですが、
「空白セルに表示する値」のチェックを外しても消えません。
チェックはそのままで、“0”削除してくださいね


関連ページ
データの無いアイテムを表示してみた
ピボットテーブルでヒストグラムを作ってみた

Excelのピボットテーブルでデータの無いアイテムを表示してみます。

例えば受注データがあって、ピボットテーブルである月の得意先毎の受注金額を集計してみます。
ここでは、2014年4月の得意先毎の受注金額を集計しました。
得意先名を表示させると表が大きくなってしまうので代わりに得意先コードで表示させています。
また、得意先コードも“1”〜“10”までに限定しました。


4月は得意先コードが“1”、“2”、“8”、“9”の4つの得意先だけ受注がありました。
では、5月をみてみましょう


5月は得意先コードが“2”、“8”、“9”、“10”の4つの得意先だけ受注がありました。
このように、ピボットテーブルではデータが無い（データを入力したレコードがない）と当然集計されませんので、受注があった得意先以外はなにも表示されません。

ですが、この月毎にピボットテーブルで集計したデータを他のシートにコピーして集計表をつくるような場合は、データの有無に関わらず、常に“1”〜“10”の得意先が表示されていた方が、貼り付けがとても楽になります。

そこで、「データの無いアイテムを表示させる」わけです！（やっと本醍。。。）

まず、ピボットテーブルの“商品コード”の上にカーソルを合わせます。
そして、「ピボットテーブルツール」「オプション」から「（アクティブなフィールド）フィールドの設定」を選択します。


「フィールドの設定」画面が開きます。
ここで、「レイアウトと印刷」タブで「データのないアイテムを表示する」にチェックを入れます。


「OK」すると、得意先コード“1”〜“10”までが表示されました。


で、データの無い得意先が空白になっていますが、金額の集計なので“\0”と表示させたいですよね。
もう一度“商品コード”にカーソルを合わせたら「ピボットツール」「オプション」から「（ピボットテーブル名）オプション」を選択します。


「ピボットテーブルオプション」画面がひらきます。
「レイアウトと書式」タブで「空白セルに表示する値」にチェックを入れ“0”を入力します。


「OK」します。
4月の得意先コード“1”〜“10”の金額の集計はこのようになりました。


5月の集計はこのようになりました。


このように、ピボットテーブルでデータが無くてもアイテムをすべて表示させたい時にこの機能は大変便利です。
ピボットテーブルから直接度数分布表やヒストグラムを作る場合にも応用できます。

さて、次回はこの「データの無いアイテムを表示する」を使う場合注意しなければならないことをまとめてみました。



関連ページ
ピボットテーブルでヒストグラムを作ってみた
無い（架空の）アイテムを表示してみた

Excelのピボットテーブルで無い(架空の)アイテムを表示してみます。

「データの無いアイテムの表示」では、たまたまデータが無くピボットテーブルの集計から漏れてしまったアイテム（項目）を値“0”で表示させてみましたが、
今度は、アイテム（項目）自体が元のテーブル（データ）に存在しないものをピボットテーブル上に架空に表示させてみます。

例えば受注データがあって、ピボットテーブルで4月の得意先毎の受注金額を集計したところ、“イルカランド”と“パラーえんとつ”の受注金額が集計されました。


でここで、元のテーブル（データ）にも存在しない得意先“博友堂”の受注金額“\1,000,000”も追加してみます。
まず、ピボットテーブルの“得意先名”の上にカーソルを合わせます。
そして、「ピボットテーブルツール」「オプション」「数式」から「集計アイテム」を選択します。


「“得意先名”への集計アイテムの挿入」画面が開きます。
ここで、
名前：“博友堂”
数式：“=1000000”
と入力します。


「OK」すると、ピボットテーブルの集計に“博友堂”の“\1,000,000”も追加されました。


ちなみに、“得意先名”のフィルターのボタンで中を確認すると一番下に“博友堂”が表示されています。


削除の方法は、“得意先名”の上にカーソルを合わせ、「ピボットテーブルツール」「オプション」「数式」から「集計アイテム」を選択します。


「“得意先名”への集計アイテムの挿入」画面が開きます。
ここで、「名前」のドロップダウンリストから“博友堂”を選択し


「削除」を押して「OK」します。


と、“博友堂”が削除されました。


この「集計アイテムの挿入」を使うと、ピボットテーブルの縦横の行ラベル、列ラベルに自由にアイテムを追加表示できます。

ただこの例のような場合は、「集計アイテムの挿入」を行わなくても、元のテーブルに“博友堂”のレコードを追加してしまえば良いことなのですが。。。
元データを弄らずにピボットテーブル上でも出来るということを知っておくと何かの時に（？）役立つのかも。

「無いアイテムを追加表示」させるか「データの無いアイテムの表示」でいけるのか、それとも元データに必要なレコードを追加してしまうのか、上手な使い分けが必要だと思います。。。よ


関連ページ
データの無いアイテムを表示してみた

Excelのピボットテーブルでクロス集計の構成比を求めてみます。

売上データがあって、得意先と商品名で売上金額を集計したクロス集計表があります。
（クロス集計表の作り方はこちらをごらん下さい）


フィールドリストの値の「合計/金額」を左クリックし、「値フィールドの設定」を選択します。


「値フィールドの設定」画面で、「計算の種類」タブを開き「計算の種類」で“全体に対する比率”を選択します。


「OK」すると金額のクロス集計が全体に対する比率（構成比）のクロス集計に変わりました。


関連ページ
構成比を集計する（売上構成比とか）
クロス集計表を簡単に作成する