[[20170620103741]]　『毎月増えていくピボットデータを拾う数式』（おか）　ページの最後に飛ぶ

『毎月増えていくピボットデータを拾う数式』（おか）

こんにちは、いつもこちらでお勉強させていただいています。

ある明細データがあり、計画・実績・前年をピボットしました。
そこからさらに達成率などを追加したいため、一覧にするシートを追加し、
計画・実績・前年をピボットからもってきたいと思っています。
ただ、現時点で4月・5月は計画・実績・前年と横並びなのですが
6月以降はまだ実績が入っておらず、計画と前年のみとなります。

ピボットはこのようになっています。

　　　　4月　　　　　　　5月　　　　　　　　6月　　　　 7月　　　 …
　　　　計画　実績　前年　計画　実績　前年　計画　前年　計画　前年…
Ａ社
　商品１
　商品２
Ｂ社
　商品１
　商品２
Ｃ社
　商品１
　商品２
　　・
　　・
　　・
このデータを一覧にもってくる際、毎回数式を変更せず、
ピボットを更新しても正しい数値をもってくるようにするには
どういった数式を使えばいいのでしょうか？
Vlookでは上手くいかず、ピボットのデータにキーを付けてSUMIFSでも試してみたのですが、その場合毎月数式を変更しないといけないため、もし変更せずにできる数式の組み合わせがあればご教示いただきたく、よろしくお願い致します。

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

SUMIFSを使ってうまく値を取り出せた、毎月数式を変更しないといけない、と言っている数式を書いてください。

あとは、COLUMN関数で列を数字化し、2で割って、1行目の文字列（日付型の数字を、書式設定で月表示しているとベスト）を得るような式に変えれば、SUMIFSの入ったセルをドラッグコピーするだけで済んだりしませんか？
（???） 2017/06/20(火) 11:14

　>ある明細データ
　の並びも、

　>一覧にするシートを追加し、計画・実績・前年をピボットからもってきたい
　の並びもあまり良くわからないため、一般論な回答になりますが、

ピボットテーブルの値を　もってくる　だけなら、
GETPIVOTDATA 関数を用いてはいかがでしょうか

=GETPIVOTDATA("値",$A$3,"月","4月","社","A社","商品","商品1","区分","計画")
のような形になりますが、
必要な部分を、"もってきた後の様式"に合わせ、
=GETPIVOTDATA("値",$A$3,"月",P2,"社","A社","商品","商品1","区分","計画")
セル指定(↑はP2セルに「4月」と書いてある場合の例)にしてやればできるのでは？と。
（tata） 2017/06/20(火) 11:24

???様
ごめんなさい、SUMIFSではなくSUMPRODUCTでした。
ピボットのシートから、数値をもってくるシートを「一覧」という名前と仮定し
一覧シートのD4セルに下記数式が入っています。

= SUMPRODUCT((ピボ!$A$9:$A$29=$A4)*(ピボ!$C$8:$E$8=D$1),ピボ!$C$9:$E$29)

((ピボ!$A$9:$A$29=$A4)・・・一覧シートのA列には一番最初に質問した際に記載したピボットの商品?@、商品?Aという項目が入っています。
(ピボ!$C$8:$E$8=D$1)・・・一覧シートのD行には計画・実績・前年という言葉が入っています。

上記は4月の式となります。(合計範囲と参照範囲がピボシートのＣ～Ｅ)
これを5月にする場合はＣ～Ｅの部分をＦ～Ｈにしないといけません。
6月にはＩ～Ｋになるのですが、今はまだ6月の実績がないためＩとJに6月の計画と前年があります。
これを毎月変更せずに条件指定でできれば・・・と思ったのですが
文章ではなかなかうまくかけず、申しわけありません。

（おか） 2017/06/20(火) 13:07

 ピボットに表示されている計画と実績の数値をそのままつかっていいなら、ピボット内で達成率出るけど？

 ピボット内のどっかのセルを選択、リボンタブの「ピボットテーブル ツール」の「分析」タブにある、
「フィールド/アイテム/セット」ボタンクリック。
 その中にある「集計フィールド」を選択。

 出てきた画面の中の数式という欄に「=実績/計画」と入力してOKする。
 するとピボットに「合計/フィールド１」という欄が追加される。
 この欄のタイトルを好きに直して（達成率とか？）、値フィールドの設定で表示形式をパーセントに直す。

 これで終了。

 そのまま使わず加工するならtataさんの方法でデータ出すといいよ。

（1111） 2017/06/20(火) 13:25

1111様
ありがとうございます。
こんな事ができるの、初めて知りました！ありがとうございます。
ただ今回の場合は、ピボっとの元になっている明細に実績、計画という項目あるわけではなく
明細の中に「予実績区分」という列があり、その中に計画や実績などがついていて
それを列フィールドにしている形なので、こちらの方法では難しそうです。

この方法はまた別の機会に使用させていただきます。
ありがとうございました。

GETPIVOTDATA関数というのも初めて聞いたので、今色々調べてみている最中です。。

（おか） 2017/06/20(火) 13:44

ピボット内に達成率等を追加するのが良さそうに思いますが、見た目を自由にしたいのであれば、普通の数式を使うのもアリですね。（それならばピボットを使わずに集計しておいた方が楽だったような？）

現在の案のままとすると、難しそうなのは、過去月ならば計画・実績・前年の3列セットですが、将来月は計画・前年の2列しかないので、単純に2で割ったり3で割ったりしては、目的の列が得られない、という部分ですよね？

例えば6行目に月が書かれているとして、「=INDEX(MATCH("4月",A6:ZZ6,FALSE),1)」のように式を書くと、「4月」が書かれた列番号が得られます。これを応用できませんか？
（???） 2017/06/20(火) 13:48

tata様

ありがとうございました。
GETPIVOTDATAという数式は初めて知りました。
何回かやってみたのですが、私の理解不足のせいかうまくいきませんでした・・・
ピボット自体、いくつかフィルタをかけているので、それが原因かもしれません。
単純にピボットから数値を持ってくる場合にはとても活用できそうです！
ご教示いただきありがとうございました。

???様
＞過去月ならば計画・実績・前年の3列セットですが、将来月は計画・前年の2列しかないので
そうなのです、まさにここが難しく。。
アドバイスいただいたようにINDEXとMACH関数でうまくできないか考えてみます。
ご教示いただきありがとうございました。
（おか） 2017/06/20(火) 15:18

おかさんが　何がしたいのか、元のデータがどうなのかも、
書かれている日本語からは私はあまり読み取れてないので、
下に、多分こうだろう、みたいな予想の上で。

 明細　シート
     |[A]|[B] |[C]   |[D]       |[E] 
 [1] |月 |社名|商品名|予実績区分|金額
 [2] |4月|A社 |商品1 |計画      | 100
 [3] |4月|A社 |商品1 |実績      |  90
 [4] |4月|A社 |商品1 |前年      |  80
 [5] |4月|A社 |商品2 |計画      | 200
 [6] |4月|A社 |商品2 |実績      | 220
 [7] |4月|A社 |商品2 |前年      | 210
 [8] |4月|B社 |商品1 |計画      | 101
 [9] |4月|B社 |商品1 |実績      |  91
 [10]|4月|B社 |商品1 |前年      |  81
 [11]|5月|A社 |商品1 |計画      | 102
 [12]|5月|A社 |商品1 |実績      |  92
 [13]|5月|A社 |商品1 |前年      |  82
 [14]|5月|A社 |商品2 |計画      | 202
 [15]|5月|A社 |商品2 |実績      | 222
 [16]|5月|A社 |商品2 |前年      | 212
 [17]|5月|B社 |商品2 |計画      | 203
 [18]|5月|B社 |商品2 |実績      | 223
 [19]|5月|B社 |商品2 |前年      | 213
 [20]|6月|A社 |商品1 |計画      | 102
 [21]|6月|A社 |商品1 |前年      |  82
 [22]|6月|A社 |商品2 |計画      | 202
 [23]|6月|A社 |商品2 |前年      | 212
↑今はまだ6月の実績がない

 ピボ　シート
    |[A]        |[B]   |[C] |[D]       |[E] |[F] |[G] |[H] |[I] |[J] 
 [3]|合計 / 金額|      |月  |予実績区分|    |    |    |    |    |    
 [4]|           |      |4月 |          |    |5月 |    |    |6月 |    
 [5]|社名       |商品名|計画|実績      |前年|計画|実績|前年|計画|前年
 [6]|A社        |商品1 | 100|        90|  80| 102|  92|  82| 102|  82
 [7]|           |商品2 | 200|       220| 210| 202| 222| 212| 202| 212
 [8]|B社        |商品1 | 101|        91|  81|    |    |    |    |    
 [9]|           |商品2 |    |          |    | 203| 223| 213|    |    
↑ＩとJに6月の計画と前年

 一覧　シート
     |[A]  |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] 
 [3] |A社  |4月 |4月 |4月 |5月 |5月 |5月 |6月 |6月 |6月 
 [4] |     |計画|実績|前年|計画|実績|前年|計画|実績|前年
 [5] |商品1|    |    |    |    |    |    |    |    |    
 [6] |商品2|    |    | 210|    |    |    |    |    |    
 [7] |商品3|    |    |    |    |    |    |    |    |    
 [8] |商品4|    |    |    |    |    |    |    |    |    
 [9] |商品5|    |    |    |    |    |    |    |    |    
 [10]|商品6|    |    |    |    |    |    |    |    |    
↑なのかな？
どうしたいのかあまり投稿された日本語だと良くわからない、のですが、

例えば、一覧　シートの D6セルを選んで、 = を入力した状態で、
マウスでピボ　シートの E7セルをクリックすると、
自動的にGETPIVOT関数が入力されるので、

そこから自分の好きなように書き換えれば良いんじゃないでしょうか。

GETPIVOT関数では、関数入力後にピボットテーブルの方を例えば社名集計項目を外すとか、
ピボットの集計項目が減少すると、#REFエラーになったりするかも？

場合によっては???さんがおっしゃるように、

【目的】一覧　シートに好きなレイアウトで集計したい

ということであれば、
ピボットテーブルは特に経由したりせずに、
明細　シートから直接
sumifs　関数　等を用いて、
○社　かつ　○月　かつ　実績　の　金額　を合計

等で集計しても良いかもしれません。

（tata） 2017/06/20(火) 18:02

コメント返信：

[ 一覧(最新更新順) ]

[[20170620103741]] 『毎月増えていくピボットデータを拾う数式』（おか） ページの最後に飛ぶ

[[20170620103741]]　『毎月増えていくピボットデータを拾う数式』（おか）　ページの最後に飛ぶ