2019年03月03日

ピボットテーブル / メニュー

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

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

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

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

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

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

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

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

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

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

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

ピボットテーブルの体裁を良くする
ピボットテーブルのデザインを変更してみた
更新しても列幅を固定する
集計フィールドの名前を変更する
セルを結合してラベルを中央に表示してみた(ピボットテーブルオプション)Excel2007
posted by haku1569 at 22:12| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2018年10月07日

スライサーで月締めの在庫を集計してみた / ピボットテーブル / Excel

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





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

2017年08月15日

複数のシートを結合してデータチェックをしてみた / Excel / ピボットテーブル

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

さらに、「戻る」します。
20170815_17.JPG

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

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

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

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

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

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



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


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

2016年03月21日

ピボットテーブルウィザードを起動してみた / ピボットテーブル / Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


関連ページ
複数のシートからピボットテーブルを作ってみたら使い勝手が悪かった
複数のピボットテーブルでグループ化の連動を解除してみた(キャッシュを共有しない)
posted by haku1569 at 14:16| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2016年03月06日

データの無い日付を表示させてみた(1日毎にグループ化) / ピボットテーブル / Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

「縦棒」を選択します。
20160228_08.JPG

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

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

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

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

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

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

次に、「月」、「年」も選択します。
20160228_14.JPG

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

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

「月」を"1月"にします。
20160228_17.JPG

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

んで、グラフはこうなりました。
20160228_19.JPG

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


関連ページ
日毎のデータを年月で集計してみた(グループ化)
日付でグループ化出来ない(グループフィールドボタンが押せない)
データの無いアイテムを表示してみた
無い(架空の)アイテムを表示してみた
空白セルに“0”表示させてみた
posted by haku1569 at 15:25| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2016年02月11日

複数のピボットテーブルでグループ化の連動を解除してみた(キャッシュを共有しない) / ピボットテーブル / Excel

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

どーしたらいいのでしょうか!?

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

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

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

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

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

「年」「月」を指定します。
20160211_05.JPG

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

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

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

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

すると、さっき作った月毎に集計したピボットテーブルも年毎に変わってしまいました。
20160211_11.JPG
これが、グループ化の連動です。

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

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

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

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

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

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

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

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

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

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

受注日と金額の集計をします。
20160211_18.JPG

受注日を年毎にグループ化します
20160211_19.JPG

と年毎に集計できました。
20160211_20.JPG

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

月毎にしてみます。
20160211_22.JPG

月毎の集計になりました。
20160211_23.JPG図23

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

めでたし、めでたし!!


関連ページ
日毎のデータを年月で集計してみた
posted by haku1569 at 18:15| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2015年04月26日

複数のシートからピボットテーブルを作ってみたら使い勝手が悪かった /Excel/ピボットテーブル

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

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

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

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

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

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

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

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

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

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

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

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

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

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

"列"はデータソースのフィールド名
20150426_15.JPG

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

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

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

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

こんなんなっちゃいました。
20150426_20.JPG

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

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

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

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

"得点"の中に埋もれてました。。。
20150426_25.JPG

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

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

関連ページ

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


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

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 ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2015年04月11日

データソースとピボットテーブルのセットをコピーしてもデータソースは元のまま!?(レポートフィルタページの表示)/Excel/ピボットテーブル

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

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

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

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

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

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

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

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

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

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

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

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

"日"と"月"を選択します。
20150411_111.JPG

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

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

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

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

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

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

すると、あら不思議!各月のシートが自動的に作成されて、例えば、4月のシートでは、ピボットテーブルのレポートフィルタが4月になっていて、4月のデータが表示されています。
20150411_118.JPG
ちなみに一番最初に作った3月のシートは右端にあるので、所定の場所に移動させておきましょう!


関連ページ
これは便利!異なる名前の複数のシートを自動で作ってみた
posted by haku1569 at 19:21| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

「データの無いアイテムを表示する」で注意しなければいけないこと(あり得ないアイテムが表示される)/ピボットテーブル/Excel

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

例えば、こんなデータソースがあります。
3月と4月の高級車?の販売台数のデータ?東京・千葉・神奈川・茨城のどこに何台売れたが入力されています。
20150411_001.JPG
3月は東京では杉並区で販売されましたが、世田谷区では販売されませんでした、他県でも同様にデータの無いアイテムが存在しています。

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

ところが、フツウにピボットテーブルを作ると、こんな感じになります。
データの無いアイテム、例えば、3月の東京都の世田谷区は表示されません(元々、データソースにそんなデータがないため)
20150411_003.JPG
ちなみに、フィールドリストはこうです。表形式で、小計は非表示にしています。
20150411_004.JPG

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

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

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

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

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

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

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

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

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

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

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

そして、ピボットテーブルには"住所"のフィールドだけにして、「データの無いアイテムを表示する」にします。
20150411_014.JPG
すると、こんな感じになりました。。。
20150411_013.JPG

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


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

関連ページ
市区町村を都府県でグループ化してみた(データの無いアイテムの表示が出来る)
posted by haku1569 at 12:58| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする