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

ピボットテーブルを作ってみた（更新や、用語の説明も）
元データ（データソース）の範囲を変える
データソースの範囲をデータの増加に応じて自動的に広げてみた（テーブルの設定）
ピボットテーブルを別のシートにコピーしてもデータソースは元のまま!?（レポートフィルタページの表示）

複数のシートからピボットテーブルを作ってみたら使い勝手が悪かった
複数のテーブルからピボットテーブルを作ってみた（リレーションシップ）/Excel2013
複数のシートを結合してデータチェックをしてみた

リレーションシップで出来なくなること（集計フィールドの追加とグループ化） / Excel2013
小計を非表示にする
列や行の総計を非表示にする
フィールド（項目）毎の集計順序を変更する
ピボットテーブルウィザードを起動してみた

フィルター（データの抽出）
ラベルにフィルタをかける（フィルタ）
値にフィルタをかける（フィルタ）
日付にフィルタをかける/前年同月とか（フィルタ）
フィルタを解除する（フィルタ）
レポートフィルタで特定のアイテムのデータを抽出し集計してみた
スライサーとタイムラインを使ってみた / Excel2013
スライサーで月締めの在庫を集計してみた

値を並び替えしてみた（表形式の時に並び替えが出来ない時は？）
複数の値（数量と金額とか）を縦と横に表示位置を変更する
ピボットテーブルのレイアウトを変えてみた

集計
データの個数(レコードの件数）を集計する
平均値、最小値、最大値を集計してみた
日々のデータからある年の商品（アイテム）と金額（値）を集計してみた
作業日報から作業を行った日数を集計してみた（重複しないアイテムの集計）
クロス集計表を作成する
クロス集計で複数の値の表示位置（配置）を変えてみた
クロス集計で構成比を求める
構成比を集計する（売上構成比とか）
累計を集計する

集計フィールドの追加
新しい集計フィールドを追加する
Excelの関数を使って新しい集計フィールドを追加してみた
日毎の作業時間（経過時間）を集計してみた

空白セルに“0”表示させてみた
データの無いアイテムを表示してみた
無い（架空の）アイテムを表示してみた
「データの無いアイテムを表示する」で注意しなければいけないこと（あり得ないアイテムが表示される）
値の書式を設定する（値フィールドの設定）
セルの書式を一括で設定してみた（アクション）

グループ化
日毎のデータを年月で集計してみた（グループ化）
日付でグループ化出来ない（グループフィールドボタンが押せない）時は
データの無い日付を表示させてみた（1日毎にグループ化）
○○年以前のデータをひとまとめにして集計してみた（グループ化）
前年同月比較で今年注文が無くなったところを調べてみた（グループ化）
テキストデータのアイテムを選択して手動でグループ化してみた（グループ化）
市区町村を都府県でグループ化してみた（データの無いアイテムの表示が出来る）
フィールドの展開と折りたたみ（ボタンの非表示とかも）
複数のピボットテーブルでグループ化の連動を解除してみた(キャッシュを共有しない)

元データから削除したデータをフィルタに表示させないようにしてみた（データソースから削除したアイテムの保持/ピボットテーブルオプション）

ピボットテーブルの応用
GETPIVOTDATA関数でピボットテーブルのデータを引用（取得）してみた
ピボットテーブルでヒストグラムを作ってみた
ピボットテーブルで層別のヒストグラム（度数分布グラフ）を作ってみた
ピボットテーブル・ピボットグラフでパレート図を画いてみた（パレート分析）

ピボットテーブルの体裁を良くする
ピボットテーブルのデザインを変更してみた
更新しても列幅を固定する
集計フィールドの名前を変更する
セルを結合してラベルを中央に表示してみた（ピボットテーブルオプション）Excel2007

ピボットテーブルのスライサーを使って月締めの在庫を集計してみました。過去の月末在庫も簡単に確認できます。
Excel2013以降は日時のスライサーはタイムラインと言う専用のスライサーであってより簡単に操作することが出来る様になっています。





続きを読む

Excelのピボットテーブルでは、複数のワークシートを結合したピボットテーブルを作ることができるのだけど、用途はデータ集計や分析と言うより予め集計が終わっているクロス集計表を複数のワークシートで結合する様な用途に使用するものなのだけど、応用例として、複数のワークシートであるデータの有無をチェックする（正しく言うと、データの個数を数える）ことができます。

こんな、ある塾の生徒の氏名とテストの得点のクロス集計表があります。このテストは毎月1回行っていて、シートが月毎に分かれています。
ここで、欠席してテストを受けなかった生徒を複数のワークシートの結合でピボットテーブルを作って調べてみようと思います。


1月のワークシートで、「ピボットテーブル/ピボットテーブルウィザード」のアイコンをクリックします。（このアイコンは予め表示させておいてくださいね）


「ピボットテーブルウィザード」画面が開きます。
「複数のワークシート範囲」にチェックを入れます。


“ページフィールドの作成方法”を「自動」にします。


“結合するワークシートの範囲”を選択します。
まず、シート“1月”のテーブルの範囲を選択して、「追加」を押します。
すると、「範囲一覧」に追加されます。


同じ様に2月のテーブルの範囲も追加します。


3つのシートのテーブルを結合するワークシート範囲として指定します。


ピボットテーブルの作成先を「新規」に指定します。


すると、この様なピボットテーブルができました。
フィールドは「行」、「列」、「値」、と自動で作成された「ページ1」と言うのが出来ています。
生徒の名前と1月から3月までの各テストの合計点数が表示されています。


「ページ1」の▼をクリックして展開してみると、“アイテム1〜3”と表示されています。
これでは何のことだか分かりませんが、結合したワークシートのことなので“1月”、“2月”、“3月”のことです。


で、“値”（得点）をフィールドリストにドラッグドロップで戻して代わりに“行”（氏名）を持ってきます。氏名は数値ではないので自動的に“データの個数”になっています。これでOKです。
また、“列”（学科）を“列ラベル”から“レポートフィルター”に移動します。


1回のテストでの個数を1個とカウントさせたいので、レポートフィルタの“列”（学科）を“国語”だけにフィルターをかけます。


するとこの様な集計になって、真木さんが1回、東出さんが2回しかテストを受けてないことが分かります。


んで、何月のテストを欠席したのか知りたいですよね。
「列ラベル」に“ページ1”を持ってきます。
すると、アイテム毎の回数が表示され、真木さんはアイテム1と2が欠席、東出さんはアイテム3が欠席しています。


でも、こんなんじゃいくらなんでも分かりずらいので、ちゃんと名前を付けましょう。
と、いうことで、ピボットテーブルにカーソルを置いて「ピボットテーブルウィザード」のアイコンをクリックします。


すると「ウィザード」画面が最後から開きます。
「戻る」します。


さらに、「戻る」します。


ここで、“ページフィールドの作成方法”を「指定」に変更します。
「次へ」します。


範囲指定の画面になります。
ここで、“ページフィールド数”を「1」にチェックします。
「範囲一覧」から、まず1月の範囲を選択して、
「フィールド1」に“1月”と手入力します。


同様に2月の範囲を選択して“2月”と入力


3月の範囲は“3月”と入力します。
これで「完了」します。


すると、この様に“ページ1”の中身に名前が付きました。


なので、“ページフィールドの作成方法”は最初から「指定」にした方がいいです！



関連ページ
複数のシートからピボットテーブルを作ってみたら使い勝手が悪かった
ピボットテーブルウィザードを起動してみた

Excel2003まではピボットテーブルを作る時はウィザードが起動してウィザード画面から対話形式で作っていったけど、Excel2007以降はウィザードはいちいち起動せず、「挿入」メニューから作れるようになって、より簡単にピボットテーブルを利用できるようになった。

のだけど、実はウィザードからじゃないと設定できない機能なんかがある。
ということでウィザードの起動方法をみてみましょう！

っと言ってもショートカットか「クイックアクセスツールバー」にアイコンを設定するだけなのでとても簡単！

ではまず、
ショートカットから起動する方法
データソース上にカーソルを置いて、「Alt」＋「D」を押します。
すると、こんなメッセージが表示されます。ちなみにこれはExcel2007です。


ここで「P」を押します。
すると「ピボットテーブルウィザード」が起動します。


例えば、「複数のシートをデータソースにしたピボットテーブルを作る」時はここで「複数のワークシート範囲」を選択します。

通常のピボットテーブルの場合はこのまま「次へ」を押します。
データソースの範囲を指定します。
ここでは範囲指定ではなくテーブル名を入力しています。


するとこんなメッセージが表示されます。ここも通常の「挿入」メニューからでは無いメッセージです。
「同じ元データから作成された既存のレポート***を基にする場合、新しいレポートではメモリを節約できます。既存のレポートと同じデータを基にして新しいレポートを作成しますか？
[はい]をクリックすると、メモリを節約し、ブックのサイズを小さくできます。
[いいえ]をクリックすると、独立した2つのレポートが作成されます」
・・・


「挿入」メニューから作られるピボットテーブルではここで「はい」が選択されたピボットテーブルが作られます。
[いいえ]を選択することでキャッシュを共有しないピボットテーブルを作る事ができて、この方が便利な場合があります。
詳しくはコチラをごらんあれ！

では次は予め、
ピボットテーブルウィザードのアイコンをクイックアクセスツールバーに表示させてみましょう。
「クイックアクセスツールバー」の「▼」をクリックして「その他のコマンド」を選択します。


「Excelのオプション」画面が開きます。
「コマンドの選択」で"リボンにないコマンド"を選択し「ピボットテーブル/ピボットグラフウィザード」を選択し「追加」します。


すると、右側に「ピボットテーブル/ピボットグラフウィザード」が追加されました。


さて、元に戻ると、クイックアクセスツールバーに追加したアイコンが表示されています。さっそくクリックします。


「ピボットテーブル/ピボットグラフウィザード」がひらきます。


というやりかたもあります。


関連ページ
複数のシートからピボットテーブルを作ってみたら使い勝手が悪かった
複数のピボットテーブルでグループ化の連動を解除してみた(キャッシュを共有しない)

実はこのタイトルは正しくはなくて、「無い日付を表示させてみた」あたりが妥当なのだろうと思う。。。

さて、どういうことかと言うと、、

日付毎の何かのデータが入力されたテーブルがあって、それをデータソースにしたピボットテーブルを作ると、もちろんデータが無い日付は表示されない。それをデータ"0"で表示させたい時にどうするか。。。と言うことなのだけど、、
ピボットテーブルには「データの無いアイテムを表示する」という機能があるのだけど、思わずそれを使えばデータの無い日付も表示されるのでは！？と思ってしまう。。。
けど、結論を言うとそれでは表示されないのです。なぜなら、データの無い日付は「データの無いアイテム」ではなく「無い（架空の)アイテム」なんです。。。

アイテムというのは、
例えば、この商品名で言うと「生うどん」、「ラーメン」、「パスタ」、「生蕎麦」、「ソース焼きそば」の5つのことだ。
んで、ピボットテーブルで1月の販売された商品と数量を集計するとこうなる。
「生蕎麦」、「ソース焼きそば」は1月には販売されてない（データが無い）ので表示されない。


ここで、「データの無いアイテムを表示」させるとこうなる。
「ソース焼きそば」と「生蕎麦」の販売数は空白になっている。


これが、「データの無いアイテムを表示する」ってことだ。
実はこの店はこの5品目以外にも沢山の商品を扱っている。が、、そんなデータソースに存在していない商品名が「データの無いアイテムを表示する」で表示されるはずがない。
「データの無い日付」ってのはつまりそう言うことだ。

したら、どうすればいいかと言うと、グループ化で「日」毎にグループ化すればいいのでした！

なぜかというとExcelの日付と言うのはシリアル値（数値）をカレンダーの様に○○年○月○日と表示させているだけなので、最初の値（開始日）から最後の値（終了日）を設定した区間（日）でグループ化すれば途中の値（シリアル値）が飛んでいても（データの無い日付があっても）全ての日付がアイテムとして作り上げられる。そこで、「データの無いアイテムを表示する」を設定すれば「データの無い日付」が表示されることになるわけです。

では、早速やってみましょう！
こんな食品問屋の受注データのデータソースがあります。


ピボットテーブルで日付と受注数を集計します。


フィールドリストはこうなります。


ここで、2014年1月のデータを抽出してみます。
日付にカーソルを乗せて右クリして「日付フィルタ」の「指定の範囲内」を選択します。


「日付フィルタ」で"2014/1/1"から"2014/1/31"を範囲指定します。


するとこのように2014年1月のデータが集計されます。


例えばそれをグラフにしてみましょう。
「オプション」から「ピボットグラフ」を選択します。


「縦棒」を選択します。


するとこんなグラフになって、横軸の日付は連続してなく受注のある日付しか表示されません。
これでは、日毎の受注データの山積みの状況を見て取ることはできません。


例えば、ここで「データの無いアイテムを表示」させてみましょう。
日付にカーソルを置いて右クリして「フィールドの設定」を選択します。


「フィールドの設定」の「レイアウトと印刷」で「データの無いアイテムを表示する」にチェックを入れます。


でも、何も変わりません。。

ではここで「グループ化」をしてみましょう。
「オプション」、「グループ」から「グループフィールド」を選択します。


「グループ化」でまず「日」だけを選択して「日数」を"1"にし、「日」毎にグループ化させます。


次に、「月」、「年」も選択します。


「OK」すると「グループ化」した「開始日」と「最終日」の期間内が1日毎にグループ化されて、全ての日付が表示されました。
先ほど「データの無いアイテムを表示する」にチェックを入れていたので受注数量の無い日付も表示されたわけです。


行ラベルの「手動フィルター」で「年」を"2014年"に


「月」を"1月"にします。


すると、2014年1月1日から31日までがすべて行ラベルに表示されました。


んで、グラフはこうなりました。


不具合の話とか。。。
たまに、行ラベルにできた日付がテキストとして扱われる場合があって、並び替えをしても、1月1日が1月19日の後に並んでしまったり「手動フィルタ」でも「日付フィルタ」が使えなかったりします。原因は不明ですが、最悪の場合ピボットテーブルを作り直さないと直らない場合があります。
それからもう一つ、行ラベルに表示された日付はあくまでもシリアル値をグループ化してExcelが付けた範囲（1日）の名前です。なので、書式設定で"2014/1/1"のように表示をかえることはできません。。。ので、悪しからず。


関連ページ
日毎のデータを年月で集計してみた（グループ化）
日付でグループ化出来ない（グループフィールドボタンが押せない）
データの無いアイテムを表示してみた
無い（架空の）アイテムを表示してみた
空白セルに“0”表示させてみた

１つのデータソースから複数のピボットテーブルを作成していて、１つのピボットテーブルで例えば日付をグループ化すると、他のピボットテーブルでも同じように日付がグループ化されてしまいます。

どーしたらいいのでしょうか！？

まずは、連動している実例から、、、

こんな、データソースがあります。“データベース”という名前のテーブルに設定してあります。


ピボットテーブルを作って“受注日”と“金額”の合計を集計します。


フィールドリストはこんな感じです。


“受注日”をグループ化してみます。


「年」「月」を指定します。


すると、このように月毎の集計ができました。


次に、年毎の集計用のピボットテーブルを新たに作ってみました。
行ラベルに“受注日”を設定すると、既に月毎になってしまっています。


“受注日”を年毎にグループ化します。


すると、年毎の集計が出来ました。


すると、さっき作った月毎に集計したピボットテーブルも年毎に変わってしまいました。

これが、グループ化の連動です。

では、なんでこーなるの！？っていうと、、、
同じデータソースから複数のピボットテーブルを作ると、パソコンのキャッシュメモリ（良く使用するデータを保存する高速メモリ）を節約する為に共有化をします。
なので、その結果クループ化等が共有されて連動してしまうわけです。

では、どうやって連動を解除するかと言うと。。。
一旦出来上がってしまったピボットテーブルの連動は残念ながら解除できません！

ので、新たにキャッシュを共有しない独立したピボットテーブルを作るわけです。
但し、通常の作り方ではなく「ピボットテーブルウィザード」を使って作ります。
では、さっそく

ピボットテーブルウィザードを使ってキャッシュを共有しない独立したピボットテーブルを作成する

データソース上にカーソルを置いて、「Alt」＋「D」を押します。
すると、こんなメッセージが表示されます。ちなみにこれはExcel2007です。


ここで「P」を押します。
すると「ピボットテーブルウィザード」が起動します。
ショートカットキーではなく、「クイックアクセスツールバー」にアイコンを追加することも出来ます。


「次へ」を押します。
データソースの範囲を指定します。
ここでは範囲指定ではなくテーブル名を入力します。


するとこんなメッセージが表示されます。ここが肝です！
「同じ元データから作成された既存のレポート***を基にする場合、新しいレポートではメモリを節約できます。既存のレポートと同じデータを基にして新しいレポートを作成しますか？
[はい]をクリックすると、メモリを節約し、ブックのサイズを小さくできます。
[いいえ]をクリックすると、独立した2つのレポートが作成されます」
・・・なるほど。。。
で、[いいえ]をクリックします。


ピボットテーブルの作成場所を指定して「完了」します。


すると、新しい独立したピボットテーブルが出来ました。


受注日と金額の集計をします。


受注日を年毎にグループ化します


と年毎に集計できました。


で、最初に作って年毎に連動してしまったピボットテーブルの受注日のグループ化を変更してしみます。


月毎にしてみます。


月毎の集計になりました。
図23

で、独立して作ったピボットテーブルを見てみると、ちゃんと年毎になってます。


めでたし、めでたし!!


関連ページ
日毎のデータを年月で集計してみた

Excelのピボットテーブルで複数のシートをデータソースにして集計してみます。


例えばこんな毎月行っている氏名ごとの国語と数学の試験結果の表があります。これが、シート毎に1月、2月、3月のシートに分かれています。
各シートの表は全く同じで、点数だけが異なっています。


ピボットテーブルウィザードをメニューに登録
この3つのシートからピボットテーブルを作ります。
作るには通常のピボットテーブルの様にメニューの「挿入」からは出来ません。
「ピボットテーブルウィザード」を起動しなければならないのですが、メニューのリボンにはありませんので、まずメニューに登録することろから始めなくてはなりません。
「クイックアクセスツールバー」の「▼」をクリックして「その他のコマンド」を選択します。


「Excelのオプション」画面が開きます。
「コマンドの選択」で"リボンにないコマンド"を選択し「ピボットテーブル/ピボットグラフウィザード」を選択し「追加」します。


すると、右側に「ピボットテーブル/ピボットグラフウィザード」が追加されました。


例1
さて、元に戻ると、クイックアクセスツールバーに追加したアイコンが表示されています。さっそくクリックします。


「ピボットテーブル/ピボットグラフウィザード」がひらきます。
「複数のワークシート範囲」をクリックします。


次の画面で、ページフィールドの作成方法を「自動」にします。


次の画面で、統合するワークシートの範囲指定をしていきます。
まず、"1月"のシートで、表の範囲を選択し「追加」を押します。
すると、下の"範囲一覧"に1月の範囲が入力されました。


そのまま、2月のシートの表も選択して、「追加」します。


同じように3月も追加し、「次へ」を押します。


次の画面で、ピボットテーブルの作成先を指定します。
ここでは、"新規のワークシート"を選択します。


すると、こんなピボットテーブルができました。
元のデータソースと同じスタイルで、得点が3カ月の合計になっています。
但し、フィールドリストはデータソースのフィールド名ではなく、
"行"、"列"、"値"、"ページ1"となっています。


"行"のアイテムは"氏名"が表示されています。


"列"はデータソースのフィールド名


"ページ1"は"アイテム1〜3"とありますが、内容は3つのシートのことです。


ここで、"列"を行ラベルに、"ページ1"を列ラベルに持っていくと、、


こんなピボットテーブルになります。

このサンプルを見ると、なるほど3つのシートが統合されたピボットテーブルが出来たと納得なのですが。。。

例2
こんな、"氏名"の左側に"学生番号"のフィールドがあるデータソースだとどうなるかというと。。。


こんなんなっちゃいました。


"氏名"が"数学"や"国語"の得点と同じ扱いになってしまってます。


例3
今までの例はデータソースが既にクロス集計になっていましたが、本来のデータソースはこうでなくてはなりません。


だけど、これで統合してしまうと、、
こんなんなっちゃいました。。。(*_*;


"国語"と"数学"はフィールドに現れてきません。


"得点"の中に埋もれてました。。。


まとめ
つまり、まとめると。。。
複数のシートを統合したピボットテーブルでは、、、
データソースの一番左側のフィールドが1つの"行"というフィールドになり、それ以外は全て"列"というフィールドになってしまいます。
なので、、、例1のようなデータソースの場合はいいのですが、例2や例3のようなデータソースの場合は、期待するようなピボットテーブルはできません。
そこんところ良く理解して使わないといけないですね。
っていうか、、あまり使い勝手はよくありません。

やっぱ、データソースは1つに集約されていないとピボットテーブルの威力は発揮できないですね。

関連ページ

最近、CDより高音質と言われる"ハイレゾ"音源が流行ってるけど、サンプリングとか決まっているデジタルよりも結局アナログの方が高音質になりえるんじゃないかと思う今日この頃。。。だったらいっそ真空管アンプなんかを自作してみようかな？と思ったりします。。。

【初回ご購入者様全員に400円OFFクーポン配布中♪】★【ELEKIT正規販売店】PCL86 シングルステ... 価格：25,704円（税込、送料別）

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

例えばこんな、"月"と"区市町村"と"販売台数"のデータがあります。（高級車とか？）


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


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


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


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


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


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


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


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


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


"神奈川県"と入力します。


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


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


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


ここで、データソースに新たにデータが追加されました。
4月に"成田市"（千葉県）のデータが追加されました。


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


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


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


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

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

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


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


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


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


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


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


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


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


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


ここで、"区市町村"で「データのないアイテムを表示する」をおこなうとどうなるかというと。。。
こんなことになってしまうのです。

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

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


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

Excelのピボットテーブルでデータソースとピボットテーブルが同じシートに表示されているシートをコピーして新しいシートを作ってもデータソースは元のシートのままになっているのです。
なので、例えば月毎にシートを分けてそれぞれのシートのピボットテーブルで月のデータを集計したい時は、いちいちデータソースを変更していかなければならないのですが、、、
レポートフィルタを設定すれば、あっと言う間に月ごとのシートとピボットテーブルが出来ちゃいます。


データソースとピボットテーブルをセットでコピーしてもデータソースは元のまま
こんな、3月のデータソースとピボットテーブルが1つのシートに出来ています。
シートの名前も"3月"と編集しています。


このシートをコピーして各月のシートを作って、4月のデータは4月のシートに入力してピボットテーブルで集計したいと思います。
まず、シートのタブ上で右クリして「移動またはコピー」を選択します。


「コピーを作成する」にチェックを入れます。


コピーされたシートの名前を"4月"に編集して、データソースに4月のデータを入力して、ピボットテーブルを「更新」したのですが。。。
何故か、3月の集計結果が表示されているだけで、4月のデータになりません。


ピボットテーブルのデータソースを確認してみると、、何と！
3月のシートのデータソースになってるじゃないですか！？


つまり、ピボットテーブルをコピーしてもデータソースは元のままになっているのです。
データソースとピボットテーブルが１つのシートにあろうがなかろうが関係ないのです。

ので、4月のシートのピボットテーブルを4月のシートのデータソースに変更するには、いちいちデータソースの変更をしていかなければならないってことになります。


そうすれば、4月のシートのピボットテーブルに4月のデータが表示されます。
これを1月から12月までやるのも、ちと手間がかかります。


レポートフィルタのページ表示で一発作成
そこで、どうするかというと。。。
まず、月が異なるデータも日付順に1つのデータソースにどんどん入力していきます。


そこで、別のシートのピボットテーブルを作ります。


月毎に集計しなければならないので、日付をグループ化します。
日付の上にカーソルを置いて、「ピボットテーブルツール」、「オプション」、「グループ」から「グループフィールド」を選択します。


"日"と"月"を選択します。


フィールドリストに"月"が追加されました。
「レポートフィルタ」に"月"を追加します。


するとこのような一番上にレポートフィルタが追加されたピボットテーブルになりました。


レポートフィルタの▼ボタンを押して"3月"を選択してみると、、


3月のデータのみが集計されました。


さて、このピボットテーブルを月毎のシートに展開していきます。
「ピボットテーブルツール」、「オプション」、「オプション」から「レポートフィルタページの表示」を選択します。


レポートフィルタに"月"しか設定していないため、選択の余地はないのでこのまま「OK」します。


すると、あら不思議！各月のシートが自動的に作成されて、例えば、4月のシートでは、ピボットテーブルのレポートフィルタが4月になっていて、4月のデータが表示されています。

ちなみに一番最初に作った3月のシートは右端にあるので、所定の場所に移動させておきましょう！


関連ページ
これは便利！異なる名前の複数のシートを自動で作ってみた

便利な「データの無いアイテムを表示する」なのだけど、複数のフィールドを表示していてそれらのフィールドで上位・下位や所属、カテゴリ等の従属な関係がある場合、下位のフィールドで「データの無いアイテムを表示する」を実行させると、上位のフィールドのアイテムとは無関係に全てのアイテムが表示されてしまうのです。
つまり、実際のデータではあり得ないアイテムの組合せが表示されてしまうのです！


例えば、こんなデータソースがあります。
3月と4月の高級車？の販売台数のデータ？東京・千葉・神奈川・茨城のどこに何台売れたが入力されています。

3月は東京では杉並区で販売されましたが、世田谷区では販売されませんでした、他県でも同様にデータの無いアイテムが存在しています。

で、作りたいのはこんなピボットテーブルです。
都道府県ごとに販売がなかった区市町村も表示しています。


ところが、フツウにピボットテーブルを作ると、こんな感じになります。
データの無いアイテム、例えば、3月の東京都の世田谷区は表示されません（元々、データソースにそんなデータがないため）

ちなみに、フィールドリストはこうです。表形式で、小計は非表示にしています。


そこで、「ピボットテーブルツール」、「オプション」から「フィールドの設定」を選択し、


「データの無いアイテムを表示する」にチャックを入れます。


すると、市区町村に全てのアイテムが表示されます。
つまり、"都道府県"のフィールドのアイテムとは無関係に全て表示されてしまうのです。
東京都　横浜市　とか。。。


つまり、「データの無いアイテムを表示する」は一つのフィールド、ここでは"区市町村"に対する操作なので、"都府県"のフィールドとは全く関係がないのです。
この例のような、"都府県"と"区市町村"の組合せの様に一目であり得ない組合せかどうか分かる場合はまだいいのですが、分かりにくい場合や元々2つのフィールドで従属的な関係があるかどうかも分からないような場合もありでしょう。そのような時「データの無いアイテムを表示する」をする場合は、他のフィールドのアイテムとは一切無関係に全て表示されているんだっていう理解をしておくことが大切だって訳です。

んで、この場合の対策は。。。

１．グループ化で"都府県"の仮のフィールドを作って"区市町村"と関連付ける
データソースの"都府県"のフィールドはピボットテーブルには使用せず、ピボットテーブルの"区市町村"のアイテムをグループ化して"都府県"の仮のフィールドを作ります。グループ化により出来た"都府県"のアイテムと"区市町村"のアイテムには当然従属関係がありますし、データソースからは"区市町村"のフィールドしか使用していないため、「データのないアイテムを表示する」を実施してもあり得ないアイテムの組合せが表示されるようなことはありません。
詳しくはコチラをごらんください。

２．「データの無いアイテムを表示する」を使わない方法
元データ（データソース）に各月のデータに実在する"都府県"と"区市町村"のデータを値は空欄でダミーで入力してしまいます。


で、「ピボットテーブルツール」、「オプション」から「オプション」を選択し、


「空白セルに表示する値」にチェックを入れ"0"を入力します。


すると、表示させたいピボットテーブルになりました。


３．表示させるフィールドを1つにして「データの無いアイテムを表示」させる
もう一つのやり方は、邪道ではありますが、表示させるフィールドを無理やり１つにしてしまうことです。
データソースにフィールドを追加して、そこに"都府県"と"区市町村"の組合せを表示させてしまいます。
"住所"と言うフィールドを追加します。演算子"＆"を使うとセルのテキストをつなげることができます。見やすいように間に" "（半角スペース）をいれています。


そして、ピボットテーブルには"住所"のフィールドだけにして、「データの無いアイテムを表示する」にします。

すると、こんな感じになりました。。。


と、いろいろ手はありますが、一番まともそうなのは"1"のグループ化でしょうか。。。


バックデータ
データの無いアイテムを表示してみた

関連ページ
市区町村を都府県でグループ化してみた（データの無いアイテムの表示が出来る）