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

フィルタを使って、ラベルや値の抽出を解除する方法です。
まず、フィルタがかかっている行ラベルの右のフィルタアイコンに三角の記号が追加されています。
行ラベルの隣のフィルタのアイコンをクリックし、ドロップダウンリストからフィルタに赤の×印のアイコンが表示されている「”得意先名”からフィルタをクリア」を選択するか、


ピボットテーブルの行ラベル上で任意のセルを選択し、右クリの「フィルタ」から「”得意先名”からフィルタをクリア」を選択すれば全てのフィルタが解除されます。

Excel のピボットテーブルで、既存のフィールドを使って計算した結果を新しい集計フィールドとしてフィールドリストに追加することが出来ます。

例えば、問屋さんの出荷データのピボットテーブルがあって、このフィールドから粗利の集計を行ってみたいと思います。
因みに、粗利＝売上−原価　で計算します。


まずカーソルをピボットテーブルの中に置いて、メニューのピボットテーブルツールの“オプション”から“数式”、”集計フィールド”を選択します。


すると、“集計フィールドの挿入”ウィンドウが開きます。


名前に「粗利」と入力して、数式の=の後にまず、“フィールド”で“売上”を選択し“フィールドの挿入”を押し、「-」を入力して、“フィールド”で“原価”を選択し“フィールドの挿入”をします。


すると、粗利のフィールドが作成されました。


数式に入力できるのはExcelで設定されている演算子（“+”、“-”、“*”、“/”等）と関数の一部も使用することができます。
また、新しく作る集計フィールドは「Σ値」専用のフィールドになります。行ラベル、列ラベルには使用できません。

さて、次は関数を使って新しい集計フィールドと追加してみましょう。

応用編
日毎の作業時間（経過時間）を集計してみた

日付フィルタを使うと日付データのフィールドに対して様々なフィルタをかけてレコードを抽出することができます。
ここでは、
●抽出する日付を範囲指定する方法
●「動的フィルタ」と言われる、今月や今週等、更新日によって自動的にデータが更新されるフィルタ
●前年同月比等が簡単に集計できる「期間内全日付」による抽出
●アイテムからの日付フィルタの設定
を紹介します。。

この様な、日付と売上金額のピボットテーブルがあります。


ではまず、指定の日付範囲のデータを抽出してみましょう。
行ラベルの右側の矢印アイコンをクリックし「日付フィルタ」を選択すると、予め用意された多くのフィルタが表示されます。そこから「指定の範囲内」を選択してみます。


すると、「日付フィルタ」の画面が開きます。
ここで、2つのボックスに日付を入力するとその間のレコードが抽出されます。


カレンダーのアイコンをクリックし、範囲の最初の日付を選択します。


同様に範囲の終わりの日付を入力したら「OK」します。


すると「2009/9/1」から「2009/10/10」までのデータが抽出されました。


次に動的フィルタをかけてみましょう
フィルタのメニューの中には“動的フィルタ”と呼ばれるフィルタが幾つか用意されています。
動的フィルタとは、更新日によって自動的に結果が変化していくフィルタで「今日」「今月」「今年」等のフィルタのことです。
今日のフィルタは明日になれば翌日（今日）のデータに更新されます。


次に前年同月のデータを抽出してみます。
「期間内の全日付」から「1月」を選択してみます。


すると、年に関係なく「1月」のデータが抽出されます。
これで、前年同期比等のデータが簡単に抽出できます。これって、かなり便利です。


アイテムからも日付フィルタを設定できます。
日付アイテム上で右クリし「フィルタ」から「日付フィルタ」を選択します。


すると比較演算子が表示され日付フィルタを設定できます。
が、レベルフィルタの様なフィルタは用意されていません。


関連ページ
値にフィルタをかける
日付にフィルタをかける
フィルタの解除
レポートフィルタで特定のアイテムのデータを抽出してみた

値フィルタを使ってピボットテーブルの値を抽出します。
注）Excel2007でラベルフィルタを使用する場合はファイルがExcel2007のファイル（xlsx）でないと使用できません。
2003のファイル（xls）では、使用できません（ラベルフィルタのアイコンがアクティブになりません）のでご注意ください。

仕入先毎に受注金額を集計したピボットテーブルで受注金額が50万円から100万円の間のデータを抽出してみましょう。
行ラベルの隣の矢印のアイコンをクリックします。


ドロップダウンリストの「値フィルタ」から「指定の範囲内」を選択します。
この一覧から様々な抽出条件を設定できます。


「値フィルタ」の画面で「“500000” と "1000000" の間」と入力し「OK」します。
すると、受注金額が50万円から100万円の間のレコードが抽出されました。


行ラベルからではなく、各アイテムからもフィルタをかけられます。
任意の仕入先の上にセルを置いて、右クリします。
「フィルタ」から「値フィルタ」を選択します。


あとは、「値フィルタ」の画面が開きラベルによる方法と同じです。

ちなみに、値フィルタで選択できるフィルタの種類は
�@指定の値に等しい
�A指定の値に等しくない
�B指定の値より大きい
�C指定の値以上
�D指定の値より小さい
�E指定の値以下
�F指定の範囲内
�G指定の範囲外
�Hトップテン
の9種類です。

次は、日付にフィルタをかけてみましょう。
日付もラベルになるのですが、ラベルに日付が設定されると日付独特のフィルタが選択可能になります。

関連ページ
ラベルにフィルタをかける
日付にフィルタをかける
フィルタを解除する
レポートフィルタで特定のアイテムのデータを抽出してみた

ラベルフィルタを使ってピボットテーブルのラベルを抽出してみます。
注）Excel2007でラベルフィルタを使用する場合はファイルがＥｘｃｅｌ2007のファイル（xlsx）でないと使用できません。
2003のファイル（xls）では、使用できません（ラベルフィルタのアイコンがアクティブになりません）のでご注意ください。

仕入先毎に受注金額を集計したピボットテーブルで仕入れ先名に“レストラン”と言う文字が含まれる仕入先だけを抽出してみしょう。
行ラベルの隣の三角のアイコンをクリックします。


ドロップダウンリストの「ラベルフィルタ」から「指定の値を含む」を選択します。
この一覧から様々な抽出条件を選択することが出来ます。


「ラベルフィルタ」の画面で“レストラン”と入力し右側のドロップダウンリストから「を含む」を選択し「OK」します。


すると、“レストラン”を含む仕入先が抽出されました。


ちなみに、ラベルフィルタで選択できるフィルタは
�@指定の値に等しい
�A指定の値に等しくない
�B指定の値で始まる
�C指定の値で始まらない
�D指定の値で終わる
�E指定の値で終わらない
�F指定の値を含む
�G指定の値を含まない
�H指定の値より大きい
�I指定の値以上
�J指定の値より小さい
�K指定の値以下
�L指定の範囲内
�M指定の範囲外
の14種類があります。

さて、次は“値”にフィルタをかけてみましょう。

関連ページ
値にフィルタをかける
日付にフィルタをかける
フィルタの解除
レポートフィルタで特定のアイテムのデータを抽出してみた

エクセルのピボットテーブルで、例えば、表示させる値が「金額」と「数量」等のように2つあった場合、その表示レイアウトを列方向に横に並べて表示させるか、行方向に縦に並べるかを簡単に変更できます。
クロス集計等で、集計表の体裁を整える時に大変重宝します。

「商品名」毎に「金額」と「数量」を集計するピボットテーブルがあった時、
フィールドリストで2つ以上の値のフィールドを値のボックスに入れると、「Σ値」と言う架空のフィールドが列か、行のボックスに表示されます。
列方向に並べて表示させたい場合は「Σ値」を列ラベルに移動させます。


すると、このように、商品名毎に「金額」の右側に「数量」が表示されました。


次に、「Σ値」を行ラベルのボックスに移動させます。


すると、金額の下の行に数量が表示されます。


ここで、「金額」と「数量」のどちらを左（または上）表示させるかは、値ボックスの入っている「金額」と「数量」の順番を入れ替えればOKです。
また、フィールドリストの行ラベルのボックスには「商品名」の下に「Σ値」が入っていますが、この順番を入れ替えるとどうなるでしょうか？やってみましょう。
「Σ値」をドラッグして「商品名」の上に持ってきます。


すると、「商品名」の前に「金額」・「数量」の値のフィールドが表示され、まず「金額」の「商品名」毎の集計、その下に「数量」の「商品名」毎の集計が表示されます。


関連ページ
クロス集計で複数の値の表示位置（配置）を変えてみた

Excelのピボットテーブルで「金額」や「数量」などのような数値データではなく、行ラベルに設定された“商品名”などのフィールドのアイテム（商品名の“データ”）が登場した個数（レコードの件数）を集計してみます。
普通、元データの列フィールド（項目）には登場する商品名や担当者の「件数」を表示する数値を入力したフィールドはありませんので、件数を集計するにはちょっとしたコツがあります。


では、受注データから受注したそれぞれの商品が登場する“データの個数”（レコードの件数）を集計してみます。
ピボットテーブルの「行ラベル」に“商品名”を設定します。で、「Σ値」にも同じように“商品名”を設定します。この“商品名”は数値データではないので自動的に集計方法は“データの個数”となります。


すると、このように、商品名毎の“データの個数”（レコードの件数）が表示されました。


で、この“データの個数”という表現がピボットテーブルに慣れていないとひっじょーに（非常にです。。）誤解しやすい表現なんですよね！？
“データの個数”ってことはある商品の受注した数量（個数）だと勘違いしちゃうわけです。。。
でもそーでなくて、“データの個数”というのは元データに登場したある商品名の件数、つまり元データのレコード（行）の件数という意味なんです。。。

例えば、「Σ値」に“数量”も設定します。
“数量”とは商品を受注した時の数のことで、1件のレコードに“50”個とか“100”個とか入力してある数値データです。
なので、集計方法は自動的に“合計”になります。


すると、このような集計表ができあがります。
“あおのりあじさい”の受注数量の合計は“630”個、“あおのりあじさい”が受注した件数は“9件”（9回）ということがわかります。


元データ（データソース）を見て確認してみましょう。
“商品名”で並べ替えしておいて、“あおのりあじさい”を囲んでみると、下に“データの個数：9”と表示されています。


“データの個数”という表現はピボットテーブルに用いられる用語ではなくExcelで使われる表現なんですね。もっと分かりやすい用語にすればよかったのに、、

次に、“数量”を囲んでみると“合計：630”と表示され、ピボットテーブルの集計と合っているのがわかります。（当たり前だけど、、、）


で、“商品名”のデータの個数を集計するには、必ずしも“商品名”の“データの個数”である必要はありません。
“商品名”の各レコードに漏れなく入力されているデータであればその件数を数えることで商品名のデータの個数（件数）が分かるわけです。
例えば、「Σ値」に“受注日”を設定します。
すると、「Σ値」で“商品名”のデータの個数を数えた時とまったく同じ件数になりました。


ここまで、理解したところで最後の問題です！？
こんなピボットテーブルがありました。
「行ラベル」に“在籍支社”、「列ラベル」に“部署名”、「Σ値」に“氏名”のデータの個数が設定してあります。
さて、この集計表は何を意味していますか、、、？

なんか、一見それぞれの支社・部署に所属する人員の人数のように見えます。合計1,981名！？
でも、そうではありませんよね。
これは、元データに登場した氏名の件数を支社と部署でクロス集計したものです。
つまり、ここから言えるのは、「東京本社の第一営業が最も受注件数が多かった」ということで、“氏名”（人員）とは全く関係のない集計なんです。
“氏名”はデータの個数を数えるために利用されただけです。この“氏名”を“受注日”に変えても“商品名”に変えても全くおなじ受注件数の集計結果が得られます。

ということで、ここまで分かれば“データの個数”の集計も自由自在です!!

Excelのピボットテーブルでヒストグラム（度数分布図）を作ってみます。
グループ化で値を区間（階級）でまとめてヒストグラムを作ります。
「グループ化」「データの無いアイテムの表示」「空白セルに表示する値」の機能を使います。
ヒストグラムについてはコチラの解説をご覧ください！

ピボットテーブルの作成
まず、こんな製品の検査データがあります。


まず、検査データとデータの件数（度数）のピボットテーブルを作成します。
値は製品�ｂﾌデータの個数にします。
検査データのデータの個数でも同じピボットテーブルを作成できますが、後でグループ化する時に検査データを個数の集計にも使用しているとグループ化出来なくなってしまいます。


するとこんなピボットテーブルが出来上がります。


グループ化
ここから、度数分布表にするためにグループ化を使って検査データをある区間毎に分けてそこに含まれる製品の件数を集計します。
「ピボットテーブルツール」「オプション」から「グループフィールド」を選択します。


グループ化の画面が開きます。
先頭と、末尾は検査データの最小値と最大値が設定されています。


これでは、都合が悪いので
先頭の値：最小値より小さい“40”
末尾の値：最大値より大きい“61”
単位：区間の幅。後から変えられるので取り敢えずこのまま“1”


「OK」で区間間隔“1”の度数分布表が出来上がりました。


さてここでよく見ると、検査データの区間の“42-46”と“57-58”の部分が抜けてしまっています。
抜けいていると言うか、始めからその検査データが存在していなかったため、ピボットテーブルで集計されなかったのです。
ただ、ヒストグラムでは横軸の値の区間は連続していなければならないので、データが無かったのなら
“42-43”・・・“45-46”も“57-58”も“0”のデータが存在しないとヒストグラムは描けないのです。

んで、どうしたらいいかと言うと、「フィールドの設定」で “データの無いアイテムの表示” を行ったあと、「ピボットフィールドオプション」で “空白セルに表示する値” に “0” を指定します。

データの無いアイテムの表示
行ラベルの適当な所にカーソルを合わせて「ピボットテーブルツール」「オプション」から「フィールドの設定」を選択します。


「フィールドの設定」画面が開きます。
「レイアウトと印刷」タブから「データの無いアイテムを表示する」をチェックします。


「OK」すると、今まで抜けていたデータと “<40”、“>61” も表示されました。
が、データの個数は “0”


空白セルに“0”を表示する
次に、追加されたアイテムの値に “0” を表示させます。
行ラベルの適当な所にカーソルを合わせて「ピボットテーブルツール」「オプション」から「オプション」を選択します。


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


「OK」でデータの個数“0”を含む度数分布表が作成されました。


ここから、ピボットグラフを作成するとヒストグラムの完成です。


ピボットテーブルを使うとグループ化の時に「データの区間」を変えて、分布の状況を確認できるためデータ分析ツールでヒストグラムを作るよりも簡単に適切なヒストグラムを作成できます。

次はヒストグラムのバラつきを分析するために層別のヒストグラムをつくってみます。



バックナンバー

関連ページ
統計分析の基本中の基本、度数分布表についてまとめてみた
数値データの分布をみるヒストグラムについてまとめてみた
ヒストグラムを層別にしてみると！？
データの無いアイテムを表示してみた
空白セルに“0”表示させてみた
データ分析ツールでヒストグラムを作ってみた