2015年04月05日

GETPIVOTDATA関数でピボットテーブルのデータを引用(取得)してみた /Excel/ピボットテーブル

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

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

ちなみに、フィールドリストはこうなってます。
20150405_101-2.JPG

ここで、セル"B2"に"加藤 泰江"さんの"アメリカンクラッカー"の受注金額"\5,400"、つまりセル"C9"の値を引用してみます。
セル"B2"で"="(イコール)を入力して、セル"C9"を選択すると、自動的に"GETPIVOTDATA関数"が入力されます。
20150405_102.JPG
関数の引数は、ピボットテーブルの値"金額"(A6)の"氏名"が"加藤 泰江"さん"商品名"が"アメリカンクラッカー"と言う意味にまります。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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


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

2015年04月04日

データソースの範囲をデータの増加に応じて自動的に広げてみた(テーブルの設定)/Excel/ピボットテーブル

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

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

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

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

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

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

で、範囲を9行目までに編集します。
20150404_007.JPG

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

関連ページ
ピボットテーブルを作ってみた(更新や、用語の説目も)
テーブルを作ってみた
posted by haku1569 at 19:09| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2014年12月13日

ピボットテーブルのデザインを変更してみた/Excel/ピボットテーブル

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

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

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

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

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

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

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

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

で、次は「縞模様(列)」にもチェックを入れてみると、
20141207_10.JPG

スタイルの一覧が1列おきにも縞模様がはいりました。
20141207_11.JPG

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

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

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

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

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

別の色を設定してみました。
20141207_17.JPG

罫線も設定してみます。
20141207_18.JPG

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

セルの色を設定してみます。
20141207_20.JPG

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

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

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

すると、こんな具合にピボットテーブルにスタイルが反映されました。
20141207_24.JPG
これで、自分好みにピボットテーブルのスタイルを設定できます。


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

2014年11月03日

リレーションシップで出来なくなること(集計フィールドの追加とグループ化) / ピボットテーブル / Excel2013

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

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

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

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

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

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

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

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

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

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

関連ページ
新しい集計フィールドを追加する
日毎のデータを年月で集計してみた(グループ化)
VLOOKUP関数/列方向の検索
posted by haku1569 at 14:10| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2014年10月25日

スライサーとタイムラインを使ってみた/ピボットテーブル/Excel2013

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

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

では、さっそく。。

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

フィールドリストには“商品名”や“在籍支社”(隠れちゃってますけど。。)等もあります。
20141023-02.JPG

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

関連ページ
スライサーで月締めの在庫を集計してみた
ピボットテーブルを作ってみた
Excel2013 / 複数のテーブルからピボットテーブルを作ってみた(リレーションシップ)
posted by haku1569 at 15:07| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2014年10月18日

複数のテーブルからピボットテーブルを作ってみた(リレーションシップ)/Excel2013/ピボットテーブル

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

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

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

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

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

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

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

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

このような“テーブル”が出来ました。
20141017_103.JPG
同様に“商品テーブル”、“顧客テーブル”もテーブルの設定をしておきます。

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

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

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

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

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

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

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

んで、「作成」を選択します。
20141017_110.JPG

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

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

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

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

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

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

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

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

関連ページ
Excelでデータの作り方
テーブルを作ってみた/Excel2007
ピボットテーブルを作ってみた/Excel2007
ピボットテーブルでクロス集計を作ってみた/Excel2007
posted by haku1569 at 11:46| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2014年09月15日

作業日報から作業を行った日数を集計してみた(重複しないアイテムの集計)/Excel/ピボットテーブルと関数

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

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

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

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

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

次に、「Σ 値」に“作業日”設定します。
自動的に“データの個数”(件数)になります。
20140915_04.JPG

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

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

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

すると、こんなピボットテーブルになります。
“データ入力”は1日に2回やってる日が多いし、“伝票整理”は6月は毎日2回やっているようですね。。
20140915_09.JPG
で、“データ入力”は?121回!?
って、間違いの例の結果と同じ!

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

すると、こんな結果!
これが、正解です。
20140915_011.JPG

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


関連ページ
日毎の作業時間(経過時間)を集計してみた
posted by haku1569 at 21:49| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2014年09月14日

日々のデータからある年の商品(アイテム)と金額(値)を集計してみた/ピボットテーブル/Excel

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

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

普通に考えると受注日をグループ化してこんなピボットテーブルを作るかも、、
20140914_102.JPG
まぁ、これでも年毎に“商品”と“金額”の集計ができているのだけど、
“レポートフィルタ”に“年”を設定したい。。
行ラベルに“受注日”を設定しないで“受注日”を“年”にグループ化出来るかというと。。。
できないのですね。。。

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

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

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

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

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

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

するとこのように、2013年の商品(アイテム)と金額(値)の集計が完成しました。
20140914_109.JPG


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

ピボットテーブルでパレート図を画いてみた(パレート分析)/ピボットテーブル/Excel2010

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


関連ページ
第1弾! ABC分析(パレート分析)について
第2弾! Excelのグラフでパレート図の作り方
posted by haku1569 at 18:51| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2014年09月07日

日毎の作業時間(経過時間)を集計してみた(集計フィールドの追加)/Excel/ピボットテーブル

Excelのピボットテーブルで時刻の差から日毎の作業時間(経過時間)を集計してみます。
20140907_00時間計算.jpg

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

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

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

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

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

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

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

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

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

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

んで、一番下の“総計”を見てみると。。。
あれれ、なんか変?“総計”が17時間10分!?
20140907_11.JPG

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

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

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

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

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

関連ページ
Excelで元データ(データソース)の作り方
クロス集計表を作成する
新しい集計フィールドを追加する
時刻のシリアル値とは
posted by haku1569 at 18:29| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする