[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『毎月増えていくピボットデータを拾う数式』(おか)
こんにちは、いつもこちらでお勉強させていただいています。
ある明細データがあり、計画・実績・前年をピボットしました。
そこからさらに達成率などを追加したいため、一覧にするシートを追加し、
計画・実績・前年をピボットからもってきたいと思っています。
ただ、現時点で4月・5月は計画・実績・前年と横並びなのですが
6月以降はまだ実績が入っておらず、計画と前年のみとなります。
ピボットはこのようになっています。
4月 5月 6月 7月 …
計画 実績 前年 計画 実績 前年 計画 前年 計画 前年…
A社
商品1
商品2
B社
商品1
商品2
C社
商品1
商品2
・
・
・
このデータを一覧にもってくる際、毎回数式を変更せず、
ピボットを更新しても正しい数値をもってくるようにするには
どういった数式を使えばいいのでしょうか?
Vlookでは上手くいかず、ピボットのデータにキーを付けてSUMIFSでも試してみたのですが、その場合毎月数式を変更しないといけないため、もし変更せずにできる数式の組み合わせがあればご教示いただきたく、よろしくお願い致します。
< 使用 Excel:Excel2013、使用 OS:Windows10 >
あとは、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
= 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月の式となります。(合計範囲と参照範囲がピボシートのC〜E)
これを5月にする場合はC〜Eの部分をF〜Hにしないといけません。
6月にはI〜Kになるのですが、今はまだ6月の実績がないためIとJに6月の計画と前年があります。
これを毎月変更せずに条件指定でできれば・・・と思ったのですが
文章ではなかなかうまくかけず、申しわけありません。
(おか) 2017/06/20(火) 13:07
ピボットに表示されている計画と実績の数値をそのままつかっていいなら、ピボット内で達成率出るけど?
ピボット内のどっかのセルを選択、リボンタブの「ピボットテーブル ツール」の「分析」タブにある、 「フィールド/アイテム/セット」ボタンクリック。 その中にある「集計フィールド」を選択。
出てきた画面の中の数式という欄に「=実績/計画」と入力してOKする。 するとピボットに「合計/フィールド1」という欄が追加される。 この欄のタイトルを好きに直して(達成率とか?)、値フィールドの設定で表示形式をパーセントに直す。
これで終了。
そのまま使わず加工するならtataさんの方法でデータ出すといいよ。
(1111) 2017/06/20(火) 13:25
この方法はまた別の機会に使用させていただきます。
ありがとうございました。
GETPIVOTDATA関数というのも初めて聞いたので、今色々調べてみている最中です。。
(おか) 2017/06/20(火) 13:44
現在の案のままとすると、難しそうなのは、過去月ならば計画・実績・前年の3列セットですが、将来月は計画・前年の2列しかないので、単純に2で割ったり3で割ったりしては、目的の列が得られない、という部分ですよね?
例えば6行目に月が書かれているとして、「=INDEX(MATCH("4月",A6:ZZ6,FALSE),1)」のように式を書くと、「4月」が書かれた列番号が得られます。これを応用できませんか?
(???) 2017/06/20(火) 13:48
ありがとうございました。
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| | ↑Iと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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.