[[20170613105015]]　『ピボットテーブル・グラフの操作法計算式総計÷』（tata）　ページの最後に飛ぶ

『ピボットテーブル・グラフの操作法計算式総計÷目標×100』（tata）

以下のようなデータ(テーブルとして書式設定)が有り、

     |[A]     |[B]   |[C]         |[D]     |[E]     
 [1] |年月日  |担当者|設定売上目標|算入区分|売上金額
 [2] |2017/6/1|田中  |        5000|空白    |空白    
 [3] |2017/6/1|田中  |空白        |ぶどう  |     100
 [4] |2017/6/2|田中  |空白        |みかん  |    1000
 [5] |2017/6/2|田中  |空白        |いちご  |     200
 [6] |2017/6/3|田中  |空白        |みかん  |     200
 [7] |2017/6/3|田中  |空白        |みかん  |     300
 [8] |2017/6/1|鈴木  |        5000|空白    |空白    
 [9] |2017/6/1|鈴木  |空白        |ぶどう  |     100
 [10]|2017/6/2|鈴木  |空白        |みかん  |    1000
 [11]|2017/6/2|鈴木  |空白        |いちご  |    2000
 [12]|2017/6/3|鈴木  |空白        |みかん  |     200
 [13]|2017/6/3|鈴木  |空白        |みかん  |     300
 [14]|2017/5/1|田中  |        5000|空白    |空白    
 [15]|2017/5/1|田中  |空白        |ぶどう  |    1000
 [16]|2017/5/2|田中  |空白        |みかん  |     101
 [17]|2017/5/2|田中  |空白        |いちご  |     466
 [18]|2017/5/3|田中  |空白        |みかん  |     211
 [19]|2017/5/3|田中  |空白        |みかん  |     307
 [20]|2017/5/1|鈴木  |        5000|空白    |空白    
 [21]|2017/5/1|鈴木  |空白        |ぶどう  |     100
 [22]|2017/5/2|鈴木  |空白        |みかん  |      10
 [23]|2017/5/2|鈴木  |空白        |いちご  |     200
 [24]|2017/5/3|鈴木  |空白        |みかん  |      20
 [25]|2017/5/3|鈴木  |空白        |みかん  |     300

ピボットテーブル・ピボットグラフ機能で以下のR列まで作成しています。

    |[K]            |[L]   |[M]   |[N]     |[O]   |[P]   |[Q] |[R] |[S]          
 [1]|合計 / 売上金額|      |      |算入区分|      |      |    |    |             
 [2]|担当者         |年    |年月日|いちご  |ぶどう|みかん|空白|総計|目標達成率(%)
 [3]|田中           |2017年|5月   |     466|  1000|   619|   0|2085|           42
 [4]|               |      |6月   |     200|   100|  1500|   0|1800|           36
 [5]|田中 集計      |      |      |     666|  1100|  2119|   0|3885|             
 [6]|鈴木           |2017年|5月   |     200|   100|   330|   0| 630|           13
 [7]|               |      |6月   |    2000|   100|  1500|   0|3600|           72
 [8]|鈴木 集計      |      |      |    2200|   200|  1830|   0|4230|             
 [9]|総計           |      |      |    2866|  1300|  3949|   0|8115|

この時、集計フィールドとして、
S列のような値[目標の達成率(売上金額(のいちごぶどうみかん合計)÷設定売上目標×100)]を作成し、
積み上げ(いちご・ぶどう・みかん　が、月ごとに積み上げ)グラフの上部に
目標の達成率を表示したいです。
(達成率のフィールドを作り、第2軸表示を想定している)

が、いちごぶどうみかん総計を集計フィールドの計算式として扱うやり方がわかりません。

のですが、なにかうまいやり方はありますでしょうか。
データの件数が半年で1500件程度ですので、元データ側に計算量の多い作業列を用いるとだんだん重くなる可能性を危惧しており、あまりいい案が浮かびません。

基本的には一般操作系の質問(ピボットグラフの使い方)ですが、
vbaでこんな実現法がある、元データの書き方を変えれば簡単、等有れば、そういったお話もありがたいです。

よろしくお願いします。

2017/06/13 11:39補足
C列目標金額は、毎月1日以外にも、例えば15日等にも追加で入る可能性がある。
　2017/06/01設定目標：5000
　2017/06/15設定目標：3000
　6月の目標は 8000

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 > が、いちごぶどうみかん総計を集計フィールドの計算式として扱うやり方がわかりません。

 集計フィールドで以下の式を入力。

 =売上金額/設定売上目標

 フィールドの書式設定をパーセントにする。

 でいいんじゃ？
 空白の列非表示したらアウトだし、各列（みかんの後とか）にエラー表示の列出るけど。
 （エラーがまずければオプションで「エラー値に表示する値」にチェック入れて）

 それよりピボットグラフは総計をグラフに表示できないんじゃなかったかなー？と思う。

（1111） 2017/06/13(火) 14:44

　ありがとうございます。夜に試してみて、詳細書きますが、取り急ぎお礼まで。

　>ピボットグラフは総計をグラフに表示できない
　やっぱりそうですかー…
（tata） 2017/06/13(火) 15:21

1111さん、試してみましたがやはり、
総計はグラフ上に表示できないようです。

目標の何%に達しているか表示させたかったのですが…

%表示は諦めて、当面は、単純に目標時間をプロットし、その点より上か下かのみ分かる状態にしておきます。

ありがとうございました。
（tata） 2017/06/13(火) 19:35

 参考までに荒っぽい力技を。

 ピボットテーブルをまず１つ作る。
 tataさんが作ったｓ列のないピボットを作ってグラフを作っておく。
 このグラフの第一縦軸の文字色を白にしておく。

 ピボットテーブルをもう一つ作る。
 集計フィールドで「=売上金額/設定売上目標」を作り、
 行に「担当者」「年月日」
 値に「売上金額」「フィールド1（さっき作った集計フィールド）」を入れる。
 列とフィルターには何もいれない（列には勝手に「Σ値」が入る）
 フィールド1の書式設定をパーセントに。
 これでもう一つグラフを作る。
 こちらには総計とパーセンテージのグラフになるので、複合グラフにして
総計を棒グラフ、パーセンテージを折れ線にする。
 ついでに折れ線にデータラベル出しておく。

 凡例を削除。
 第一横軸の表示を削除

 そして最初に作ったグラフの上に重ねる。
 最初のグラフと同じ大きさにし、グラフエリアの塗りを透明に。
 プロットエリアを選択（しにくい場合はピボットグラフツールタブの書式タブにある「現在の選択範囲」のドロップダウンから選択）し、
最初のグラフのプロットエリアと同じ大きさに調整。

 こうすると最初のグラフの上に折れ線グラフが重なる。
 ぱっと見た目は１つのグラフみたいに見えるので、ごまかせるかも？

（1111） 2017/06/14(水) 09:42

グラフエリアの塗りを透明にして、もう一つグラフを重ねる！

なるほど、柔軟な発想に脱帽です。

面白い案、ありがとうございました。
（tata） 2017/06/14(水) 20:14

コメント返信：

[ 一覧(最新更新順) ]

[[20170613105015]] 『ピボットテーブル・グラフの操作法 計算式 総計÷』（tata） ページの最後に飛ぶ

[[20170613105015]]　『ピボットテーブル・グラフの操作法計算式総計÷』（tata）　ページの最後に飛ぶ