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

Excelのピボットテーブルで集計されたデータをピボットテーブルの外から"GETPIVOTDATA関数"を使って引用（取得）してみます。

例えばピボットテーブルがあります。
営業の担当者名と商品毎の受注金額のクロス集計表になっています。


ちなみに、フィールドリストはこうなってます。


ここで、セル"B2"に"加藤 泰江"さんの"アメリカンクラッカー"の受注金額"\5,400"、つまりセル"C9"の値を引用してみます。
セル"B2"で"="（イコール）を入力して、セル"C9"を選択すると、自動的に"GETPIVOTDATA関数"が入力されます。

関数の引数は、ピボットテーブルの値"金額"（A6）の"氏名"が"加藤 泰江"さん"商品名"が"アメリカンクラッカー"と言う意味にまります。

んで、結果は"5400"になりました。
つまり、セル"C9"を引用したのではなく、"加藤 泰江"さんの"アメリカンクラッカー"を引用したわけです。


なので、行ラベルの並び順を変えてみても、、、


加藤さんのデータは"C25"に移動してしまいましたが、ちゃんと追っかけています。


また、今度はピボットテーブルの集計を変えてみます。
行ラベルに"氏名"と"商品名"をレイアウトしてみます。


すると、このようなピボットテーブルになりました。


が、これもきっちり"B916"の値を表示しています。


今度は、ピボットテーブルから"商品名"を削除して、"氏名"と全ての商品の受注金額の"合計"だけにしてみます。


すると、引用すべきデータがピボットテーブルに表示されてないため、エラー"#REF"が表示されました。


引数にセルを指定する
また、"氏名"や"商品名"の引数は"加藤 泰江"や"アメリカンクラッカー"等のテキストデータではなく、通常の関数の様にテキストが入力されたセルを指定することももちろんできます。
こんな一覧表を作って、"氏名"と"商品名"の引数をセルにすると、、


"加藤 泰江"さんと"森上 偉久馬"さんのの"アメリカンクラッカー"と"インドカレーパン"の金額が引用できました。


GETPIVOTDATA関数の解除
このように、ピボットテーブルの外から"="でピボットテーブルのセルを選択すると自動的に"GETPIVOTDATA関数"が入力されてしまうのですが、通常通りセルを引用することもできます。
「ピボットテーブルツール」、「オプション」、「ピボットテーブル」から「GetPivotDataの生成」のチェックを外します。


セル"D2"で"="でセル"C9"を選択すると、通常通り"=C9"と入力されました。


GETPIVOTDATA関数の応用
さて、この"GETPIVOTDATA関数"でピボットテーブルの値を引用することによって、ピボットテーブルの中で計算出来ないような式や関数、つまり"集計フィールドの挿入"が出来ないような計算を、ピボットテーブルの外から行うことが出来、データソースのが更新されて、ピボットテーブルの値が更新されても、常に最新の計算結果を求めることができます。
例えば、加藤さんと森上さんのデータにそれぞれ異なる係数を掛けて合計するとか、、、


ピボットテーブルの"集計フィールドの追加"で金額に係数を掛けることは可能ですが、氏名毎に異なる係数を指定することはできません。


また、ピボットテーブルのレイアウトでは適当ではないような場合に、独自の表を作っておいてそこにピボットテーブルのデータを引用するなどの応用が考えられます。例えば、ピボットテーブルの結果を1カ月のカレンダー形式で表示させるとか？
色々応用がありそうですね。。


関連ページ
新しい集計フィールドを追加する

前回は、Excelのピボットテーブルでデータソース（元データ）の範囲を変更してする方法を説明しましたが、今回はデータソースを"テーブル"に設定することで、データが増えても、いちいち範囲を広げなくてもいい楽チンな方法を説明します！


例えば、ピボットテーブルのデータソースとしてこんな日付毎のデータがあります。


テーブルに設定しない時の問題点
普通やりかたで、データソースを設定したこんなピボットテーブルが出来ています。


「データソースの変更」を選択してみると、この様な範囲指定になっています。


ここで、データソースの9行目に4月6日のデータが追加されました。


もう一度、「データソースの変更」を選択してみると、、、
追加した4月6日のデータはデータソースの範囲から外れています。


で、範囲を9行目までに編集します。


と、ピボットテーブルが更新されて4月6日のデータも集計されました。


毎日データが増える度にいちいちこんなことをするのは面倒なので、予め範囲を広く設定したりすることもあります。


すると、ピボットテーブルに"（空白）"というアイテムが表示されてしまいます。
また、この方法でもいずれ範囲を広げてやる必要がありますし、範囲から外れているのを忘れてしまうこともあります。


テーブルを設定する方法
そこで、そんな問題を解決するのが"テーブル"を使用する方法です。

まず、データソースの中にカーソルを置いて「挿入」から「テーブル」を選択します。


「テーブルの作成」画面が開きます。
このまま「OK」します。


すると、テーブルに設定されたデータは予め用意されていたデザインが設定されました。


ここで、「テーブルツール」の「デザイン」を開いて、左端の"テーブル名"を確認しておきます。（ここで、変えることも出来ます）


んで、ピボットテーブルに戻って、「データソースの変更」で、範囲の欄に"テーブル1"と入力します。


データソースの10行目に4月7日のデータを追加して、


ピボットテーブルを「更新」すると、


4月7日のデータ集計されました。


また、D列に新たなフィールドを追加しても、


ピボットテーブルを「更新」すると、"フィールドリスト"に追加されて、


こんな、クロス集計が出来るようになります。


これで、データソースのデータが増えていっても、データソースの範囲設定を気にしないでピボットテーブルを更新していくことが出来るようになりました！！


今回は、範囲指定で作られたピボットテーブルのデータソースを後から「テーブル」に変更しましたが、ピボットテーブルを作る前にデータソースにするデータをテーブルに設定しておくと、ピボットテーブルを作る時のデータソースは自動的にテーブル名が入ります。

バックナンバー
データソース（元データ）の範囲を変える

関連ページ
ピボットテーブルを作ってみた（更新や、用語の説目も）
テーブルを作ってみた

Excelのピボットテーブルのデザインを変更して見栄えの良いものにしてみます。
たまには、イメチェンが必要だよ！？

例えばこんなピボットテーブルがあります。


用意されているスタイルの適用
「ピボットテーブルツール」、「デザイン」から「ピボットテーブルスタイル」を選択します。


すると、予め用意されているスタイルの一覧が表示されます。
ここから、好みのスタイルを選択します。


すると、ピボットテーブルに選択したスタイルが適用されました。


また、「ピボットテーブルツール」、「デザイン」の「ピボットテーブルスタイルのオプション」で、「縞模様（行）」にチェックを入れてみます。


すると、スタイルの一覧が1行おきに縞模様のついたものに変更されました。


で、さっきと同じスタイルを選択してみると、縞模様のスタイルが適用されました。


で、次は「縞模様（列）」にもチェックを入れてみると、


スタイルの一覧が１列おきにも縞模様がはいりました。


すると、ピボットテーブルの列にも縞模様が適用されました。


新しいピボットテーブルスタイルの適用
次はオリジナルのスタイルを設定してみましょう。
「ピボットテーブルスタイル」の一覧の下に表示されている「新しいピボットテーブルスタイル」をクリックします。


「新しいピボットテーブルクイックスタイル」画面が開きます。
“テーブル要素”で、「最初の列」を選択し「書式」をクリックします。


見慣れた「セルの書式設定」画面が開きます。
「塗りつぶし」でセルの色を設定してみます。


すると、「新しいピボットテーブルクイックスタイル」画面の“プレビュー”に設定した内容が反映されました。
次に「見出し行」を選択してみます。


別の色を設定してみました。


罫線も設定してみます。


プレビューに反映されました。
今度は「2番目の行のストライプ」を選択してみます。


セルの色を設定してみます。


プレビューに反映されました。
最後に「このドキュメントを既定のピボットテーブルクイックスタイルに設定」にチェックして「OK」します。


「ピボットテーブルツール」、「デザイン」から「ピボットテーブルスタイル」を選択してみると、真っ先にさっき設定したスタイルが表示されました。


一覧を表示させてみると「ユーザー設定」にさっきのスタイルが表示されています。
これを選択してみます。


すると、こんな具合にピボットテーブルにスタイルが反映されました。

これで、自分好みにピボットテーブルのスタイルを設定できます。


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

前回はExcel2013の新しい機能の一つである、ピボットテーブルのリレーションシップを紹介しました。複数のテーブルのフィールドをピボットテーブルで組み合わせて集計できる画期的なものなのですが、、
出来なくなる機能もあるんです。。。なんでー！！？

その１ 新しい集計フィールドの追加が出来ない
前回のリレーションシップを使ったピボットテーブルで、“商品テーブル”から“商品名”と商品の“単価”、“受注テーブル”から、“数量”を設定して集計しました。


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


で、何をしたいかというと、商品毎の受注金額を求めたいと思います。
受注金額は“単価”x “数量”で求められます。
“単価”は商品毎に定められた属性（情報）なので、“商品テーブル”に設定してあります。“受注テーブル”には“商品コード”と受注“数量”が設定してあるので、リレーションシップで、受注テーブルの“数量”と“商品テーブル”の“単価”を使って新しい集計フィールドを追加すればいいわけなのです。
「ピボットテーブルツール」、「分析」、「計算方法」から「フィールド/アイテム/セット」を選択します。


。。。ぅがーーーー!!
「集計フィールド」が選択できません！！


その2　グループ化が出来ない
リレーションシップが設定されている状態で、“受注テーブル”の“受注日”と“数量”をピボットテーブルで集計します。


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


んで、“受注日”をグループ化して月毎の数量を集計したいとおもいます。。。
ぅがーーーー!!
「フィールドのグループ化」が選択できません！！


という訳で。。。
せっかく複数のテーブルのリレーションシップが出来るようになったのに。。
これでは、使えないですよねー。
“VLOOKUP関数”の代用にしかなりません。
しかも、集計フィールドの追加や、グループ化ができないとなると、おちおちリレーションシップを設定できなくいなってしまいます。。

バックナンバー
複数のテーブルからピボットテーブルを作ってみた（リレーションシップ）

関連ページ
新しい集計フィールドを追加する
日毎のデータを年月で集計してみた（グループ化）
VLOOKUP関数/列方向の検索

Excel2013ではピボットテーブルのフィルター機能としてExcel2010からある“スライサー”に加え“タイムライン”と言うフィルタが追加されました。


Excel2007でも、行・列のラベルでのフィルターで同様のことは出来るので、新しいフィルタという訳ではないのだけど、使い勝手がなかないいのでデータを分析している途中、試行錯誤している時にラフに使うとかなり活用できると思いますよ。。。

では、さっそく。。

スライサーを使ってみた！
こんな受注データがあって、得意先ごとの受注金額がピボットテーブルで集計されています。


フィールドリストには“商品名”や“在籍支社”（隠れちゃってますけど。。）等もあります。


「ピボットテーブルツール」、「分析」、「フィルター」から「スライサー」を選択します。


すると、「スライサーの挿入」画面がひらきます。
ピボットテーブルのフィールドの一覧が表示されています。
ここで、フィルターをかけたいフィールドをチェックします。
ここでは、“在籍支社”と“商品名”をチェックします。


すると、「商品名」と「在籍支社」のアイテムが表示されたボックス（スライサー）が現れました。
今は、なにもフィルターをかけていないので、すべてのアイテムが選択された状態になっています。


ここで、「商品名」のスライサーで“あおのりあじさい”を選択してみます。
すると、得意先と受注金額を集計してあるピボットテーブルは商品名“あおのりあじさい”でフィルターがかかった集計にかわりました。


「Ctrl」キーを押しながら選択すると、複数のアイテムを選択してフィルターをかけることもできます。


フィルターをクリアするには右上の「フィルタのクリア」のボタンを押します。


次に、「在籍支社」のスライサーでもフィルターをかけます。
「商品名」と「在籍支社」、2つのフィールドでのフィルターがかけられます。


スライサーを削除するには、スライサーを選択して「Delete」キーを押します。


では次に、
タイムラインを使ってみた！
日時が入力されているフィールドには「タイムライン」を使用します。
「ピボットテーブルツール」、「分析」、「フィルター」から「タイムラインの挿入」を選択します。


「タイムラインの挿入」画面が開きます。
ここでは、日時のフィールド“受注日”が表示されています。


すると、「受注日」の「タイムライン」が現れました。
今は、なにもフィルターをかけていないので、横バーがすべて選択されてブルーになっています。


2013年の9月を選択すると、その1か月の得意先と受注金額の集計がピボットテーブルで表示されます。


ブルーのバーをドラッグするとフィルターの範囲が変わります。


右端の“月”の右側の▼をクリックすると予め設定されている日付フィルターが表示されます。
「四半期」を選択してみます。


すると、こんな感じに四半期ごとにフィルターがかけられます。


と、、
「スライサー」も「タイムライン」も従来通り、行ラベルにフィルターをかけたり、レポートフィルタを設定したり、あるいは日付にフィルターをかけたりとかで同じようにフィルタをかけることができますが、「スライサー」、「タイムライン」を使うと、より自由に簡単にフィルターをかけることができます。
定型的な集計で使うより、分析を試行錯誤している時にラフに使うのにもってこいの機能だと思います。
そして、集計方法が決まったら、従来のやり方でフィールドリストに所定のフィールドを設定して集計するのが、上手な使い方だと思います。。。

Excel2013の新機能
複数のテーブルからピボットテーブルを作ってみた（リレーションシップ）

関連ページ
スライサーで月締めの在庫を集計してみた
ピボットテーブルを作ってみた
Excel2013 / 複数のテーブルからピボットテーブルを作ってみた（リレーションシップ）

Excel2013ではデータベース機能が大幅に向上されています。ACCESSとの使い分けに悩みそうになるかもしれませんが、ACCESSを使うスキルと、ソフトを使う環境を整えるコストを考えれば、Excelである程度データベースをカバーできるようになればありがたいことだとでしょう。
別の見方をすれば、Excelに対して科学技術計算より統計分析へのニーズが求められているということ、つまり時代の要求ということなのかもしれないですね。。。

んで、Excel2013のピボットテーブルに関していえば、テーブル間のリレーションシップが出来るようになったのが画期的なことだと思います。
“リレーションシップ”というのは“関連付け”のことで、複数のテーブルにある共通のフィールドに関連付けを行って、あたかも2つのテーブルを1つのテーブルとして集計させることです。。


では早速、やってみますね。。
食料品の商社（問屋）の受注データがあります。ワークシートは“受注テーブル”、“商品テーブル”、“顧客テーブル”の3つのシートに分かれています。
データベースの場合“テーブル”というのはデータが入力（格納）された表（データ）のことをいいます（つまりExcelのワークシートに当たります）。ところがExcelの場合、“テーブル”というのは、“テーブル”として定義された（設定された）表のことをいいます。このへんが紛らわしいところです。

んで、シート“受注テーブル”は受注した日と受注した顧客（得意先）と商品、その受注した数が入力されています。得意先と商品は其々のコード番号で入力されていて、いちいち顧客名や商品名を入力する手間を省いています。


シート“商品テーブル”には、“商品コード”に対応した“商品名“、その商品の“単価”が入力されています。


シート“顧客テーブル”には“得意先コード”に対応した“得意先名”が入力されています。


まず、それぞれのワークシートの表を“テーブル”に設定します。
表の中にカーソルを置いて「挿入」から「テーブル」を選択します。


「テーブルツール」、「プロパティ」、「テーブル名」で、テーブルの名前を設定します。
ここでは、ワークシートの名前と同じにしています。


このような“テーブル”が出来ました。

同様に“商品テーブル”、“顧客テーブル”もテーブルの設定をしておきます。

次に、受注テーブルでピボットテーブルを作ります。
テーブルのセルを選択しておいて、
「挿入」、「テーブル」から「ピボットテーブル」を選択します。


「ピボットテーブルの作成」画面が開きます。
ここで、他のテーブルとリレーションシップにする時は、
「このデータをデータモデルに追加する」をチェックします。


と、こんなピボットテーブルが出来上がりました。


フィールドリストにある「すべてのフィールド」を選択します。


すると、テーブルに設定した“顧客テーブル”、“商品テーブル”もフィールドリストに追加されました。


▼をクリックするとテーブルのフィールドが表示されるので、必要なフィールドを下のリストにドラッグします。


まず、“商品テーブル”から“商品名”を行ラベルに、“受注テーブル”から“数量”を値に設定します。
すると、上の方に黄色のボックスでリレーションシップを催促するメッセージが表示されます。
“商品テーブル”の“商品名”と“受注テーブル”の“数量”の間には何の関係もないのでこのままでは、“商品名”に対する“数量”の集計ができません！と言ってるわけです。
では、何を基準に2つのテーブルを関連付ければいいかというと。。。
2つのテーブルで共通に持っているフィールドの“商品コード”になるわけです。

んで、「作成」を選択します。


「リレーションシップの作成」画面が開きます。
「テーブル」に“受注テーブル”、「列」に“商品コード”
「関連テーブル」に“商品テーブル”、「関連列」に“商品コード”
を設定します。
これで、2つのテーブルの“商品コード”が関連付けられました。


すると、ピボットテーブルは“商品テーブル”の“商品名”に対して“受注テーブル”の“数量”がそれぞれの“商品コード”を基準に集計されました。


次に同じように“顧客テーブル”もリレーションシップしてみます。
“顧客テーブル”から“得意先名”を列ラベルに設定します。
すると、リレーションシップの作成を催促されます。


「リレーションシップの作成」画面で
テーブル」に“受注テーブル”、「列」に“得意先コード”
「関連テーブル」に“顧客テーブル”、「関連列」に“得意先コード”
を設定します。
これで、今度は“受注テーブル”と“顧客テーブル”の“得意先コード”が関連付けられました。


すると、“数量”の“商品名”と“得意先名”のクロス集計が出来上がりました。


Excel2010までだと、この様な集計では“VLOOKUP関数”を使って“商品コード”から“商品名”を検索して表示させる方法しかありませんでしたが、Excel2013のリレーションシップを使うと、ピボットテーブルだけで、この様な集計が可能になります。
それぞれのテーブルはとてもシンプルになり、余計なデータを省くことができますね。。

では、次はリレーションシップを設定した時に出来なくなる(制限される)機能についてまとめてみました。
重要ですので必見です。

Excel2013の新機能
スライサーとタイムラインを使ってみた

関連ページ
Excelでデータの作り方
テーブルを作ってみた/Excel2007
ピボットテーブルを作ってみた/Excel2007
ピボットテーブルでクロス集計を作ってみた/Excel2007

Excelのピボットテーブルで業務日報から業務を行った日数を集計してみます。
以前、業務日報から業務を行った時間（経過時間の合計）を集計してみましたが、今回はその業務を何日行ったかを集計してみます。

例えばこんな業務日報があって、一日毎に何時から何時までどんな業務をしたかが入力されています。
家庭だったら、何日勉強したか、トレーニングを何日やったかとかを集計ができると思います。

業務日報は日付と業務を行った開始時刻、終了時刻が記入されています。
1日のうちに同じ業務を午前と午後に行うとか複数回行うこともありますよね。。


最初はありがちな間違った例です。
ピボットテーブルを作って「行ラベル」に“作業種別”を設定します。


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


次に、「Σ　値」に“作業日”設定します。
自動的に“データの個数”（件数）になります。


ピボットテーブルはこうなります。
作業毎の作業日のデータの個数（件数）が集計されました。

一見、これが作業を行った日数だと思っちゃいます！
ちなみにこのデータは6月〜8月までの日報になっていますので、毎日やったとしても92日。それを超えることはありえないはずなんだけど、“データ入力”は121日間やったことになってます。
つまり、これは作業日のデータの個数を数えていますが、つまりこれは作業種別に“データ入力”と記入されている“作業日”の数（件数）を数えています。つまりこれは（再）“データ入力”と記入した数（件数）を数えてるのと一緒なんです。。。よ！
1日に2回データ入力の作業をしてたら、2回でカウントされます。

だは、正しい方法です。
というか、ピボットテーブルでは集計したアイテムの重複のない数（アイテムの種類の数）を数えることが出来ないのです。。。
なんで、この問題！？じゃなくて、この課題はピボットテーブルでは解決できません。。
なので、ピボットテーブルで集計した結果を関数を使ってみます。。。

まず、行ラベルに“作業日”、列ラベルに“作業種別”、値に“作業日のデータの個数”を設定します。


すると、こんなピボットテーブルになります。
“データ入力”は1日に2回やってる日が多いし、“伝票整理”は6月は毎日2回やっているようですね。。

で、“データ入力”は？121回！？
って、間違いの例の結果と同じ！

んで、ここで使う関数は！？
この日毎の作業回数のピボットテーブルから、作業の回数に関わらずにその回数を数えればいいわけでうよね。
ってことで、“COUNT関数”を使いましょう。。


すると、こんな結果！
これが、正解です。


んでも、最初の間違いの例、ピボットテーブルで集計してるからと思って過信してると、ありがちな間違いです。
検証は絶対必要です!!


関連ページ
日毎の作業時間（経過時間）を集計してみた

ピボットテーブルで日々のデータからある年の“商品”（アイテム）と“金額”（値）を集計してみます。

食料品を扱う問屋（卸売業者）の受注データから、2013年の1年間に受注した商品（アイテム）と受注金額（値）を集計してみます。
元データ（データソース）はこんなデータです。


普通に考えると受注日をグループ化してこんなピボットテーブルを作るかも、、

まぁ、これでも年毎に“商品”と“金額”の集計ができているのだけど、
“レポートフィルタ”に“年”を設定したい。。
行ラベルに“受注日”を設定しないで“受注日”を“年”にグループ化出来るかというと。。。
できないのですね。。。

んで、特に変わったことはしないんですが、、、
まず、行ラベルに“受注日”を設定します。


んで、ここで日毎の“受注日”をグループ化します。
「ピボットテーブルツール」、「オプション」、「グループ」から「グループフィールド」を選択します。


「グループ化」画面が開きます。
「単位」に「年」を選択します。


すると、ピボットテーブルの行ラベルの“受注日”は年単位で表示されました。


そうしたら、「レポートフィルタ」に“受注日”、「行ラベル」に“商品名”「値」に“金額”を設定します。


ピボットテーブルのフィールドリストのレポートフィルタの“受注日”で“2013”を選択します。


するとこのように、2013年の商品（アイテム）と金額（値）の集計が完成しました。



関連ページ

第1弾“ABC分析（パレート分析）”と第2弾“Excelのグラフでパレート図の作り方”を紹介しましたが、第3弾！はピボットテーブル（ピボットグラフ）だけでパレート図を作ってみます。
また、第2弾のExcelのグラフでパレート図を作る場合でも、グラフの元データになるアイテムと値の集計表を作るにはピボットテーブルを使うのが一番簡単な方法になります。（そこまでの集計ならExcel2007で可能です！）
その先の、累積構成比をピボットテーブルで集計するのは残念ながらExcel2010以上が必要です。

食料品を扱う問屋（卸売業者）の受注データから、2013年の1年間に受注した商品（アイテム）と受注金額（値）でパレート図を画いてみます。
元データ（データソース）はこんなデータです。


アイテムと値の集計
まずパーレート図の元データになる“商品名”（アイテム）と“金額”（値）の集計を行います。
これは、Excel2007でも可能です。

まずピボットテーブルを作ります。
“商品名”（アイテム）と“金額”（値）で集計するのですが、集計対象は“2013年”の1年間だけなので、2013年のデータだけを抽出しなければなりません。
このままでは、それができないのでまず“年”でグループ化してあげなければいけません。
そのために、まず行ラベルに“受注日”を設定します。


行ラベルの受注日の上にカーソルを置いて「ピボットテーブルツール」、「オプション」、「グループ」から「グループフィールド」を選択します。


「グループ化」画面が開きます。
「単位」に「年」を選択します。


すると、ピボットテーブルの行ラベルの“受注日”は年単位で表示されました。


そうしたら、「レポートフィルタ」に“受注日”、「行ラベル」に“商品名”「値」に“金額”を設定します。


ピボットテーブルのフィールドリストのレポートフィルタの“受注日”で“2013”を選択します。


値は降順で並べ替えします。
するとこのように、2013年の商品（アイテム）と金額（値）の集計が完成しました。


Excel2007ではここまでです。
正確には“累積”と“構成比”は集計できますが、“累積構成比”は2007では集計できません。
なので、2007の場合はこの後の作業はピボットテーブルを用いないで通常のワークシート上でグラフのデータを作っていきます。

累積構成比の集計
ではここからはExcel2010で累積構成比を集計していきましょう。
「値」に“金額”をもう1つ設定します。


追加した「金額2」をクリックして「値フィールドの設定」を選択します。


「値フィールドの設定」画面が開きます。
「計算の種類」タブで、「計算の種類」を“比率の累計”、「基準フィールド」を“商品名”に設定します。


すると、アッと言う間に累積構成比の集計ができました。
簡単でしょ？


ピボットグラフでパレート図の作成
では集計表が出来上がったところで、ピボットグラフを使ってパレート図を作って行きましょう。

ピボットテーブルの中にカーソルを置いて、
「ピボットテーブルツール」、「オプション」、「ツール」から「ピボットグラフ」を選択します。


「グラフの挿入」画面が開きます。
「縦棒」から「集合縦棒」を選択します。


すると、このような“ほぼパレート図”が出来上がりました。


ここからは、見易いグラフにする仕上げ作業になります。
第2弾“Excelのグラフでパレート図の作り方”の後半部分をごらん下さい。
全く同じ作業をするだけです。
と、このようなパレート図が出来上がりました!!



関連ページ
第1弾! ABC分析（パレート分析）について
第2弾! Excelのグラフでパレート図の作り方

Excelのピボットテーブルで時刻の差から日毎の作業時間（経過時間）を集計してみます。


例えばこんな作業日報があって、一日毎に何時から何時までどんな業務をしたかが入力されています。
家庭だったら、勉強時間だったり、トレーニング時間だったりの集計ができると思います。
この元データ（データソース）には時間の計算はされていません。
それは、ピボットテーブルの集計フィールドで計算できるので、出来るだけ元データはシンプルにしておくためです。


では、最初は間違った例をみてみます。
まずは、元データからピボットテーブルを作ります。作業時間の集計フィールドはまだ作っていないので、値は空欄です。
“作業日”と“作業種別”のクロス集計にします。


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


では、“値”である“作業時間”を求める集計フィールドを追加しましょう。
「ピボットテーブルツール」、「オプション」、「計算」から「集計フィールド」を選択します。


「集計フィールドの挿入」画面が開きます。
名前：“作業時間”
数式：“終了時間-開始時間”
と入力します。


すると、このように“値”に“作業時間”が表示されました。
小数で表示された値は時刻のシリアル値です。


このシリアル値を時間表示にしたいので、値にカーソルを置いて右クリックして「値フィールドの設定」を選択します。


「値フィールドの設定」画面が開きます。
「表示形式」を選択します。


「セルの書式設定」画面が開きます。
「時刻」で「13:30」を選択してみました。


「OK」すると、このように作業時間が“時間・分”で表示されました。
日毎の“総計”も正しく表示されています。
で、完了！？


んで、一番下の“総計”を見てみると。。。
あれれ、なんか変？“総計”が17時間10分！？


試しに、日毎の“総計”を全部範囲選択してみると、右下には“合計：449:10:00”と表示が。
つまり総計は“449時間10分”なはずなんだけど。。。
それに、“データ入力”作業の総計も30分なんてありえない!!


この間違いは、計算されたシリアル値をただセルの書式で“時刻表示”にしてしまったからなんです。
書式の“時刻表示”は“0:00”から“23:59”までの表示なので、24時間以上は1日と時刻表示になって、いわゆる経過時間の“時間表示”にはならないんですね。

では、正しい方法です。
「集計フィールドの挿入」で「数式」には
“=(終了時間-開始時間)*24”
と入力します。
これは、“終了時間（終了時刻）と開始時間（開始時刻）の差”は小数の時刻のシリアル値になっているのですが、
時刻のシリアル値“1”は“24時間”になりますので、このシリアル値に“24”をかけると直接“時間（経過時間）”が数値で求められるのです。


で、セルの表示形式は“数値”にします。
（標準でも構いませんが、少数が表示されるので数値にして小数点以下を“1”にしておきます。）


すると、今度は総計“449.2時間”と正しく表示されました!!


関連ページ
Excelで元データ（データソース）の作り方
クロス集計表を作成する
新しい集計フィールドを追加する
時刻のシリアル値とは