元データ(ピボッド)を開かずマクロで更新したい。

同一フォルダ内の複数ブックの中に同じフォーマットの複数シートがあります。
その複数シートにSUMIFS関数で集計用の数式を組んでおり、その参照先元データ(ピボッド)が別ブックにあります。

以前、SUMIFS関数が別ブック参照エラーになる関数だと知り、数式を他の関数で組むか元データのピボッドをシートごとコピーするか？と教えていただいたので、元データシートを複数ブックにそれぞれコピーする方法を選択しました。
その各ブックにコピーした元データシートを元データを更新すると開かずともすべて更新できるようなマクロを組みたいです。

もしくは、他に何か良い方法があればご教授頂ければ幸いです。
何卒、よろしくお願いいたします。

質問者からの補足コメント

• >３）参照先元データ(ピボッド)のブックのシートのフォーマット（セルの位置情報）
  >（SUMIFS関数で集計用の数式に関するセルの位置情報は必須です。）
  1部→A列：売上日付　B列：商品コードの一桁目　C列：担当者名　D列：支店名　E列：課or営業所名　F列：部門名　G列：商品1or2の条件作業列
  　H列：1or0の条件作業列　I列：新規(1)or既存の条件作業列　J列：数量　ｋ列：売上
  2部→A列：売上日付　B列：商品コードの一桁目　C列：担当者名　D列：支店名　E列：課or営業所名　F列：部門名　G列：新規(1)or既存の条件作業列
  　J列：数量　ｋ列：売上
  3部→A列：売上日付　B列：商品コードの一桁目　C列：担当者名　D列：支店名　E列：課or営業所名　F列：部門名　G列：1.2.3.4.5.6.7.8の条件作業列
  　H列：新規(1)or既存の条件作業列　I列：数量　J列：売上

  No.4の回答に寄せられた補足コメントです。 補足日時：2017/05/25 11:38

• >４）複数ブックの中に同じフォーマットの複数シートのブック名、シート名
  →計画のフォルダに下記ブックが入っています。
  「関東支店1課、2課、3課、西日本支店1課、2課、3課、中部支店1課、2課、3課、東北支店1課、2課、3課、九州支店、中国支店、本部、横浜営業所、栃木営業所」の17ブックあります。
  シートはそれぞれ担当者名になっており、支店によってシート数が異なります。
  それに加え、2）で記載した④各部の前年P、⑤各部本年Pのシートコピー分と下記①②のシートがあります。
  ①別途、質問投稿しご回答いただいた合計シート：「合計シート」
  ②それとは別の条件での合計シート：「それ以外シート」

    補足日時：2017/05/25 11:40

• >５）上記シートのセル情報、及び、そのセルのどこにSUMIFS関数の結果を格納したいのかの情報
  →3）で記載したピボッドの作業列を参照してそれぞれ、前年ピボッドは前年のセルへ、実績は実績のセルへ結果を表示させたいです。ちなみに、A7の「1」は新規だったら「1」とする作業列です。
  数式を表示しましたが、説明が足りないようでしたら申し訳ありません。
  >６）SUMIFS関数で集計している機能を業務的に説明したもの
  →月別で個人の特定商品の前年、本年の実績を年間で管理したい。それぞれ条件が多いので作業列を部別で作成しています。質問に対しての回答が違っておりましたら、申し訳ありません。

    補足日時：2017/05/25 11:41

• ※個人別のシートの項目欄を添付します。
  2つに並べてありますが、実際は縦になっています。

  
    補足日時：2017/05/25 11:42

• 個人別のシートに組んでいる数式の画像です。
  ピボッドのデータとA列の作業列を参照する式になっています。
  ※画像が見づらい等ありましたら、教えてください。

  
    補足日時：2017/05/25 11:49

• >４）ピボットのコピーを行っているとのことですが、これは、関数で参照するときに、エラーを発生させない為と理解しました。
  →おっしゃる通りです。
  >マクロであれば、元データのピボットの参照が可能なので、ピボットのコピーは不要と考えますがいかがでしょうか。
  > (ピボットをコピーするなという意味ではありません。マクロは、コピーされたピボットを参照しないで、コピー元のピボットを参照するという意味です）
  →はい、コピー不要で元データピボッド参照ということが可能であればそちらで処理ができたら幸いです。

  No.5の回答に寄せられた補足コメントです。 補足日時：2017/05/26 15:10

• ※個人シート3部の画像添付します。
  ★3部集計項目詳細
  3部→A列：売上日付、B列(集計条件不要)、C列：担当者名、D列：支店名、E列：所属名、F列(集計条件不要列)、G列：作業列①(1～8のどれかだったら)、H列：作業列②(新規だったら1)、I列：数量、J列：売上
  ・項目①=G列の中で「1」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・項目②=G列の中で「2」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・大分類A(既存)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量

  
    補足日時：2017/05/29 14:33

• ※個人シートの1.2部の画像添付します。
  ・大分類A(新規)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で1だった時の数量
  ・大分類B(既存)=G列の中で「4」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・大分類B(新規)=G列の中で「4」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で1だった時の数量
  ・大分類C(既存)=G列の中で「5」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量

  
    補足日時：2017/05/29 14:35

• 

  ※文字数の関係で補足が分けての投稿になってしまい、わかりづらくて申し訳ありません。
  ・大分類C(新規)=G列の中で「5」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で1だった時の数量
  ・大分類D=G列の中で「6」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・大分類E=G列の中で「7」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
  ・大分類F=G列の中で「8」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量

    補足日時：2017/05/29 14:36

• 3）の画像ですが、3部まとめて項目並び順を添付します。

  
  No.10の回答に寄せられた補足コメントです。 補足日時：2017/05/31 11:18

No.22 回答者： tatsu99 回答日時： 2017/06/01 12:07

>>ブックを分けることを希望します。

>→承知いたしました。
>そうしますと、データを「2016年実績」と「2017年実績」2つのブックに分けようと思います。下記で問題ございませんでしょうか？
>2016年実績は、1部、2部、3部の元データとピボッド、3部、1部の作業列用のリスト計8シート。集計に使用するのはピボッドのみ。
>2017年実績は、1部、2部、3部の元データとピボッド、3部、1部の作業列用のリストともう一つ作業用シート作成予定ですので計9シート。集計に使用するのはピボッドのみ。

いいえ、そのようなことは期待していません。
「2016年実績」であれば、
2016年度の前年P（2015年度の実績に相当）（1部、2部、3部）と2016年度の本年P（1部、2部、3部）
「2017年実績」であれば、
2017年度の前年P（2016年度の実績に相当）（1部、2部、3部）と2017年度の本年P（1部、2部、3部）
のように分けてくださいということです。

「2017年実績」の中に、
前年Pに201５年度以前のデータを含めないでください
本年Pに2016年度以前のデータを含めないでください
ということです。

No.21 回答者： tatsu99 回答日時： 2017/06/01 11:47

確認＆質問事項です。

１）1Q,2Q,3Q,4Q,上期、下期、年間の列は更新しませんがよろしいでしょうか。（関数式で算出することが前提）

２）前年で参照シートは下記で間違いないでしょうか。
　　参照するシートは1部前年P,2部前年P,3部前年Pである。

３）実績、計画で参照シートは下記で間違いないでしょうか。
　　参照するシートは1部本年P,2部本年P,3部本年Pである。

４）新規、既存の判定は、実績のみ行い、前年、計画については行わない件ですが、これは1部、3部について適用であってますか。
　（2部は新規、既存がない為、該当せず）
　　又、前年、計画については、新規と既存は同じ値が設定されることになりますが、それであってますか。
　　例　3部の場合
　　　①新規の前年と①既存の前年は同じ値が設定される
　　　①新規の計画と①既存の計画は同じ値が設定される

　　3部の前年、計画の抽出条件
H列が空白のものを条件とするのでしょうか。それとも、H列自体を参照しないのでしょうか。

　　1部の前年、計画の抽出条件
I列が空白のものを条件とするのでしょうか。それとも、I列自体を参照しないのでしょうか。

５）1部　大分類（H）について
>・大分類H(既存)=G列の中で1だったら（以降省略）
>・大分類H(新規)=G列の中で1だったら（以降省略）
と提示されていますが、G列の中で2だったら・・・の誤りではないでしょうか。

この回答へのお礼

以下、回答いたします。

>確認＆質問事項です。
> １）1Q,2Q,3Q,4Q,上期、下期、年間の列は更新しませんがよろしいでしょうか。（関数式で算出することが前提）
→承知いたしました。

>２）前年で参照シートは下記で間違いないでしょうか。
　　>参照するシートは1部前年P,2部前年P,3部前年Pである。
→おっしゃる通りです。

>３）実績、計画で参照シートは下記で間違いないでしょうか。
　　>参照するシートは1部本年P,2部本年P,3部本年Pである。
→おっしゃる通りです。

> ４）新規、既存の判定は、実績のみ行い、前年、計画については行わない件ですが、これは1部、3部について適用であってますか。
>　（2部は新規、既存がない為、該当せず）
→はい、合ってます。
　　>又、前年、計画については、新規と既存は同じ値が設定されることになりますが、それであってますか。
　　>例　3部の場合
　　>①新規の前年と①既存の前年は同じ値が設定される
　　>①新規の計画と①既存の計画は同じ値が設定される
→前年は既存にだけ数値を表示したいです。前年には新規はありません。理由といたしましては、前年データと本年データを比較して、前年データにないものを
　新規とするという条件だからです。計画については直接入力ですので、集計不要です。

　　>3部の前年、計画の抽出条件
>H列が空白のものを条件とするのでしょうか。それとも、H列自体を参照しないのでしょうか。
　　>1部の前年、計画の抽出条件
>I列が空白のものを条件とするのでしょうか。それとも、I列自体を参照しないのでしょうか。
→説明不足で申し訳ありません。4）でも記載いたしましたが、前年に新規はありません。前年と本年の比較で前年になければ新規という条件だからです。
　計画は直接入力ですので、集計不要です。

>５）1部　大分類（H）について
>>・大分類H(既存)=G列の中で1だったら（以降省略）
>>・大分類H(新規)=G列の中で1だったら（以降省略）
>と提示されていますが、G列の中で2だったら・・・の誤りではないでしょうか。
→大変失礼いたしました。仰る通り大分類Hは2だったらです。

お礼日時：2017/06/01 13:22

No.20 回答者： tatsu99 回答日時： 2017/06/01 11:43

>→理解不足で申し訳ありません。

再度ご教授願います。私としては、前年度の内容はその月が到来するしない関わらず常に表示させておきたいのです。
>実績に関してはもちろん、到来するまでは空欄でOKとしたいのですが可能でしょうか？

すみません。前の補足回答を勘違いしました。
現在、2017年５月とすると、
個人のシートの8月分の実績、計画は、空白でよい。
個人のシートの8月分の前年、値を設定しておきたい。
ということでしょうか。

それであれば、今考えている仕様では、年度の切り替え時の新規個人シート作成時に、
2017年7月～2018年6月を指定して、マクロを実行することが必要になります。（12回）
（元データも(201707～201806まで)１２ブック必要）

この件は、他に、簡単にできる方法があれば、検討しますが、現状では、この形になるかもしれません。
多分、別マクロとして（１年間（7月～6月）の前年の列のみの設定を行うマクロ）の提供になるかも知れませんが、現状では期待しないでください。

この回答へのお礼

>>→理解不足で申し訳ありません。再度ご教授願います。私としては、前年度の内容はその月が到来するしない関わらず常に表示させておきたいのです。
>>実績に関してはもちろん、到来するまでは空欄でOKとしたいのですが可能でしょうか？

>すみません。前の補足回答を勘違いしました。
>現在、2017年５月とすると、
>個人のシートの8月分の実績、計画は、空白でよい。
>個人のシートの8月分の前年、値を設定しておきたい。
>ということでしょうか。
→実績は空白、前年は値を設定したい。計画については、個人で直接入力している為集計不要です。

>それであれば、今考えている仕様では、年度の切り替え時の新規個人シート作成時に、
>2017年7月～2018年6月を指定して、マクロを実行することが必要になります。（12回）
>（元データも(201707～201806まで)１２ブック必要）
>この件は、他に、簡単にできる方法があれば、検討しますが、現状では、この形になるかもしれません。
>多分、別マクロとして（１年間（7月～6月）の前年の列のみの設定を行うマクロ）の提供になるかも知れませんが、現状では期待しないでください。
→承知しました。

お礼日時：2017/06/01 13:06

No.19 回答者： tatsu99 回答日時： 2017/06/01 11:18

>前年、本年データを分ける必要がありますか？とお尋ねしたのは元データのピボッドの件です。

>現在、同じブックに前年、本年のピボッドが存在している為2016と2017といったようにブックを分けることは可能です。

回答：元データのピボッドは、
A列が1月、2月など月だけの表示であることと、ブックに年月の情報があるため、当然、年度毎に分かれていると考えていました。
もし、分けないで運用すると（前年、前前年、前前前年も含めると）、データ量が増え続ける為、今後、パフォーマンスの低下等が考えられます。
ブックを分けることを希望します。

>また、ピボッドのA列である売上日付を月だけではなく年度も追加することは可能です。
>申し訳ありませんが、個別シートを2016やまだ、2017やまだと分けることは考えておりません。
>理由としては、1個人1シートの中の月別の前年、計画、実績で管理したいからです。
回答：支店別のブック内に個人シートが、「本年度のやまだ」と「前年度のやまだ」の２つはないと理解しました。
私が、懸念して申し上げたのは、2017年５月指定で、マクロを実行すると、やまだの5月のフィールドに
前年、計画、実績の値が設定されますが（元データは2017年5月のピボット）、
201６年５月指定で、マクロを実行しても、同じ5月のフィールドに前年、計画、実績の値される（元データは2016年5月のピボット）ということです。
つまり、年度を間違えて指定すると、同じシートの同じ個所にその年度のデータを設定してしまうということです。（運用上注意することが必要ですが、年度の指定間違いをマクロではガードできません）
この仕様で問題なければ、特にシート名の変更は必要ありません。

＞ちなみに、前年には2016年と2017年が混じっており、期間指定(例えば、2016.4～2017.3を前年、2＞017.4～2018.3を実績へ)をして個人シートの前年、実績へ集計することは可能でしょうか？
回答：現状では、期間指定は考えていません。期間指定も考慮すると、前提条件を、最初から詰めなおす必要がありますので、年月指定のみとさせてください。

この回答へのお礼

度々、ご返信頂きましてありがとうございます。
下記、記載いたします。

>回答：元データのピボッドは、
>A列が1月、2月など月だけの表示であることと、ブックに年月の情報があるため、当然、年度毎に分かれていると考えていました。
>もし、分けないで運用すると（前年、前前年、前前前年も含めると）、データ量が増え続ける為、今後、パフォーマンスの低下等が考えられます。
>ブックを分けることを希望します。
→承知いたしました。
そうしますと、データを「2016年実績」と「2017年実績」2つのブックに分けようと思います。下記で問題ございませんでしょうか？
2016年実績は、1部、2部、3部の元データとピボッド、3部、1部の作業列用のリスト計8シート。集計に使用するのはピボッドのみ。
2017年実績は、1部、2部、3部の元データとピボッド、3部、1部の作業列用のリストともう一つ作業用シート作成予定ですので計9シート。集計に使用するのはピボッドのみ。

>回答：支店別のブック内に個人シートが、「本年度のやまだ」と「前年度のやまだ」の２つはないと理解しました。
>私が、懸念して申し上げたのは、2017年５月指定で、マクロを実行すると、やまだの5月のフィールドに
>前年、計画、実績の値が設定されますが（元データは2017年5月のピボット）、
>201６年５月指定で、マクロを実行しても、同じ5月のフィールドに前年、計画、実績の値される（元データは2016年5月のピボット）ということです。～
※以下中略
→いろいろとご検討、ご提案いただきましてありがとうございます。2016年5月、2017年5月どちらにせよ5月に数値が反映されてしまうということだったのですが条件として、2016年5月だったら「2016年実績」を参照して5月の「前年」へ、2017年5月だったら「2017年実績」を参照して5月の「実績」へ反映するといったことは不可ということでしょうか？ちなみに、「計画」に関しては、直接入力しているので個人シート上は集計不要です。

>回答：現状では、期間指定は考えていません。期間指定も考慮すると、前提条件を、最初から詰めなおす必要がありますので、年月指定のみとさせてください。
→承知いたしました。

お礼日時：2017/06/01 11:55

No.18 回答者： tatsu99 回答日時： 2017/05/31 18:52

>３）もし、１）の質問で、関数による集計式を設定しておく予定であるなら、その理由は２）の理由からでしょうか。

もし、そうであれば、新しい年度になった時点で、７>月から翌年6月までを指定して、マクロを１２回実行すれば、前年の値を設定することは可能です。ブックはその年度の１２か月ぶんを作っていく必要がありますが。

>→2）の前年を値で表示したい場合、こちらは不要になりますか？
回答：
新しい年度になった時、７月から翌年6月までを指定して、マクロを実行する必要はありません。
（前年の内容がその月が到来するまでは空白で良いということなので）

この回答へのお礼

お返事が遅くなり申し訳ありません。
下記、記載いたしました。ご確認願います。

>→2）の前年を値で表示したい場合、こちらは不要になりますか？
回答：
新しい年度になった時、７月から翌年6月までを指定して、マクロを実行する必要はありません。
（前年の内容がその月が到来するまでは空白で良いということなので）
→理解不足で申し訳ありません。再度ご教授願います。私としては、前年度の内容はその月が到来するしない関わらず常に表示させておきたいのです。
実績に関してはもちろん、到来するまでは空欄でOKとしたいのですが可能でしょうか？

お礼日時：2017/06/01 10:34

No.17 回答者： tatsu99 回答日時： 2017/05/31 18:39

>→的外れな質問をしていたら申し訳ありません。

個別のシートが何年度の処理をしているかがわかる必要ということは今、前年度のデータと本年度のデータを同じブック内においておりますが、これを分ける必要があるということでしょうか？

はい、（今の考えでは）結果的にそうなります。現在、以下のように考えています。
例えば、仙台一課.xlsxのブック内に左から順に
①田中
②山田
③合計
④その他何でも良い（以降同様）
というシートがあれば、
左から順番に処理し、①田中、②山田を処理し、”合計”のシート検出時、処理を終了します。
”前年山田”のようなシートがあっても、③合計の右側にあれば、処理しません。

もし、前年度のデータを同一ブック内において、前値度のデータを集計し直したい場合は、
以下の何れかになります。

1案：前年の山田を”合計”より、左側へ配置し、今年の山田を”合計”より右側へ配置して、マクロを実行すれば、前年の山田が処理される。（今年の山田は処理されない）

2案：”営業”より、左側のシートを処理するというルールを破棄し、処理対象となるルールを決め直す。
1例 個人シートは、必ず、2017XXX（XXXが個人名）という名称にする。
従って、個人シートは2016山田、2017山田のようなシートになる。
2017年7月～2018年6月が指定された場合は、2017XXXのシートを参照する。
2016年7月～2017年6月が指定された場合は、2016XXXのシートを参照する。
この場合、”営業”の右側にあっても、処理をおこなうので、どの位置にあるかは、関係しない。
（但し、別件のマクロに関連するので、今年度以外の個人シートは”営業”の右側におくことになると思います）

この回答へのお礼

お返事が遅くなり、申し訳ありません。
マクロを作成いただくにあたっての注意点、年度に関しての条件等について理解不足で申し訳ありません。
恐れ入りますが、一旦整理をさせてください。

私のしたいこととして。。
個人シートの集計方法ですが、支店別のブックに個人シートがそれぞれあり、私が別途お伝えしている各部の条件に該当する個人の数値を
前年、計画、実績に分けて集計したい。
その集計元のデータは別ブックであり、前年、本年シート(ピボッド)に分かれており同じブックに存在している。
また、そのコピーが今は支店別のブックに存在している。(SUMIFS関数で処理している為。ただし、マクロ実行になると不要になると伺いました)
そこで今回の年度の判別ができないので。。というお話でしたので質問をしたのですが
前年、本年データを分ける必要がありますか？とお尋ねしたのは元データのピボッドの件です。
現在、同じブックに前年、本年のピボッドが存在している為2016と2017といったようにブックを分けることは可能です。
また、ピボッドのA列である売上日付を月だけではなく年度も追加することは可能です。
申し訳ありませんが、個別シートを2016やまだ、2017やまだと分けることは考えておりません。
理由としては、1個人1シートの中の月別の前年、計画、実績で管理したいからです。

それ以外で私のほうで修正、検討すべきことはありますでしょうか？
ちなみに、前年には2016年と2017年が混じっており、期間指定(例えば、2016.4～2017.3を前年、2017.4～2018.3を実績へ)をして
個人シートの前年、実績へ集計することは可能でしょうか？

何度もお手数をおかけして申し訳ありませんが、何卒、よろしくお願いいたします。

お礼日時：2017/06/01 10:36

No.16 回答者： tatsu99 回答日時： 2017/05/31 16:36

基本的なことの確認になりますが、

１）個人別シートの各月の前年、実績のフィールドは、関数による集計式を設定しておく予定でしょうか。（個人的は設定しないでおくことを望みます。）
２）個人別シートの各月の前年は、シート作成時、既に値が設定されていることが、会社からの要件として求められているのでしょうか。例　2017年8月は、先なので、実績は空白で良いが、前年は判っているので前年8月の値を（8月前でも）表示してほしい。
３）もし、１）の質問で、関数による集計式を設定しておく予定であるなら、その理由は２）の理由からでしょうか。もし、そうであれば、新しい年度になった時点で、７月から翌年6月までを指定して、マクロを１２回実行すれば、前年の値を設定することは可能です。ブックはその年度の１２か月ぶんを作っていく必要がありますが。

この回答へのお礼

ご確認いただきありがとうございます。
こちら下記、変更させて頂きます。
↓
>基本的なことの確認になりますが、
> １）個人別シートの各月の前年、実績のフィールドは、関数による集計式を設定しておく予定でしょうか。（個人的は設定しないでおくことを望みます。）
→設定しておく予定はありません。
> ２）個人別シートの各月の前年は、シート作成時、既に値が設定されていることが、会社からの要件として求められているのでしょうか。例　2017年8月は、先なので、>実績は空白で良いが、前年は判っているので前年8月の値を（8月前でも）表示してほしい。
→おっしゃる通り、前年分を表示しておきたいです。(前年比を出したいので数値が必要)、現時点到来していない月に関しては集計前なので空白で問題ないです。
>３）もし、１）の質問で、関数による集計式を設定しておく予定であるなら、その理由は２）の理由からでしょうか。もし、そうであれば、新しい年度になった時点で、７>月から翌年6月までを指定して、マクロを１２回実行すれば、前年の値を設定することは可能です。ブックはその年度の１２か月ぶんを作っていく必要がありますが。
→2）の前年を値で表示したい場合、こちらは不要になりますか？

お礼日時：2017/05/31 16:50

No.15 回答者： tatsu99 回答日時： 2017/05/31 16:02

>例えば、3月のデータは4月のデータを処理する時はもう確定しているので数値が動くことはないという認識でよろしいでしょうか？

回答：その認識で、構いません。但し、3月の月を指定してマクロを実行すれば、3月のデータを再度集計し直します。（もし、3月の集計結果にあやまりがあったことが判明した場合は、3月のブックのピボットを正しく作成し直してからマクロを実行すれば、正しい3月の集計結果が得られます）
又、現在が４月だとして4月10日のマクロを実行すると、その日までの集計結果が、４月の箇所に集計されます。そして、４月15日になり、再度、４月を指定してマクロを実行すると、4月15日までの集計結果で、4月の箇所を更新し直します。

　>4月分より以前の月についてはその月で集計が完了していれば、値での表示になるという認識でよろしいでしょうか？
回答：値での表示の意味が不明ですが、今回集計しようとするフィールドには、関数式を設定しておくと
いう意味でしょうか。その場合、マクロを実行して、その箇所が更新されると、値が設定されます。
従って、現在が４月の場合で、4月の集計が完了していなくでも、マクロを実行しない限りは、更新されることはありません。（1度でもマクロを実行するとその月の関数式の設定が無効になります。）

又、非常に問題になるかもしれませんが、
マクロでは、現在、集計中のデータが何年度かを知る術がありません。
①もし、2017年４月の指定を行いマクロを実行すると、【実績】売上明細データ(201704)のデータを
参照し、各個人データの４月の列にデータを集計します。
②その後、2016年４月の指定を行いマクロを実行すると、実績】売上明細データ(201604)のデータを
参照し、各個人データの４月の列にデータを集計します。
つまり、①と②で更新されるシートは全く同じシートになります。
おそらく、これは②の動作を望んでいないかも知れませんが、マクロにとってみれば、どちらが正しいかの判断がつかないからです。②を実行させない為には、実績】売上明細データ(201604)のデータを当該ディレクトリに置かないことが必要になります。
このような仕様になりますが、宜しいでしょうか。もし、上記以外に、②の指定時マクロの実行をガードできる方法があれば、その旨ご提示ください。（個別のシートが何年度の処理をしているかがわかる情報があることが必要です）

この回答へのお礼

>>例えば、3月のデータは4月のデータを処理する時はもう確定しているので数値が動くことはないという認識でよろしいでしょうか？
(※文字数制限の為、回答省略させていただきます)
→ご回答ありがとうございます。承知いたしました。

>>4月分より以前の月についてはその月で集計が完了していれば、値での表示になるという認識でよろしいでしょうか？
(※文字数制限の為、回答省略させていただきます)
→私の質問の仕方が悪く、申し訳ございません。関数式を設定するということではなく、上記の質問にご回答頂いた内容と同じ質問をしたつもりでした。
　大変失礼いたしました。

>又、非常に問題になるかもしれませんが、
>マクロでは、現在、集計中のデータが何年度かを知る術がありません。～
(※文字数制限の為中略させていただきます)
。②を実行させない為には、実績】売上明細データ(201604)のデータを当該ディレクトリに置かないことが必要になります。
このような仕様になりますが、宜しいでしょうか。もし、上記以外に、②の指定時マクロの実行をガードできる方法があれば、その旨ご提示ください。（個別のシートが何年度の処理をしているかがわかる情報があることが必要です）
→的外れな質問をしていたら申し訳ありません。個別のシートが何年度の処理をしているかがわかる必要ということは今、前年度のデータと本年度のデータを同じブック内においておりますが、これを分ける必要があるということでしょうか？

お礼日時：2017/05/31 16:40

No.14 回答者： tatsu99 回答日時： 2017/05/31 12:56

ピボットの画像の提供ありがとうございました。

これについて、いくつか補足要求です。

１）実際のデータは何行目から始まりますか。
提示例では１部本年Pの場合は、３行目になっていますが、実シートでのこの行に該当する行番号です。
又、これは、他のシートも、同じ行番号であると考えて良いですか。

２）1部本年Pの例では、「たなか」の1月の例がありますが、このシート内に
「たなか」の1月のデータは、他の行にもたくさんある。と考えて良いですか。

３）１つのシート内の１個人がもつ最大行数
補足の回答で、
>→数名3,000件を超える(10,000件以上)の人がいる為、遅くなりますよね。。。
数名、1万件を超える人がいるということですが、仮にそのひとを「たなか」とすると、
１シート内で「たなか」の人の行が１万件を超えますか？（全シートの合計で超えるのは問題ありません。問題になるのは１シート内です）
実際には、A列の月も含めて絞り込みますので、（例えば2017年4月の場合はA列が4月のみの行を記憶します）
正確には、
1シート内でC列＝「たなか」でA列＝4月であるデータの件数になります。（4月が指定された場合）

1シート内でC列＝○○でA列＝XX月であるデータが最大発生しうる件数です。
（前年の1部、2部、3部、本年の1部、2部、3部で、最も多いデータを持つシートについてです）
（○○は、最も多いデータを持つ人、XXがその人が最も多いデータを持つ月）
この件数は、何件程度でしょうか。

４）データの最終行について
マクロでは、A列で、データの最終行を決定する予定です。
A列の内容が空白になることは、想定していません。それで、よろしいでしょうか。
もし、A列の内容が空白になるケースがあるなら、絶対に空白にならない列（例えばC列の担当者名等）を提示ください。

５）指定月について
念のため、確認ですが、2017年4月が、指定された場合、A列が4月のデータだけが集計対象であると、考えていますが、間違いないでしょうか。
（4月の4は半角の数字）
（yyyy年mm月が指定された場合、A列がmmの月が対象となる）

この回答へのお礼

>１）実際のデータは何行目から始まりますか。
>提示例では１部本年Pの場合は、３行目になっていますが、実シートでのこの行に該当する行番号です。又、これは、他のシートも、同じ行番号であると考えて良いですか。
→はい、すべてのシートが同じ3行目から始まります。

>２）1部本年Pの例では、「たなか」の1月の例がありますが、このシート内に
>「たなか」の1月のデータは、他の行にもたくさんある。と考えて良いですか。
→はい、仰る通りです。

>３）１つのシート内の１個人がもつ最大行数
> 補足の回答で、
>>→数名3,000件を超える(10,000件以上)の人がいる為、遅くなりますよね。。。
>数名、1万件を超える人がいるということですが～
(文字数オーバーの為、以下省略)
>1シート内でC列＝○○でA列＝XX月であるデータが最大発生しうる件数です。
>（前年の1部、2部、3部、本年の1部、2部、3部で、最も多いデータを持つシートについてです）
>（○○は、最も多いデータを持つ人、XXがその人が最も多いデータを持つ月）
>この件数は、何件程度でしょうか。
→だいたい多い人である月で300件程度です。

>４）データの最終行について
> マクロでは、A列で、データの最終行を決定する予定です。
>A列の内容が空白になることは、想定していません。それで、よろしいでしょうか。
>もし、A列の内容が空白になるケースがあるなら、絶対に空白にならない列（例えばC列の担当者名等）を提示ください。
→A列で問題ございません。

> ５）指定月について
>念のため、確認ですが、2017年4月が、指定された場合、A列が4月のデータだけが集計対象であると、考えていますが、間違いないでしょうか。
>（4月の4は半角の数字）
>（yyyy年mm月が指定された場合、A列がmmの月が対象となる）
→はい、問題ございません。
　ちなみに、基本的な質問かもしれませんがご教授願います。
　4月分より以前の月についてはその月で集計が完了していれば、値での表示になるという認識でよろしいでしょうか？
　例えば、3月のデータは4月のデータを処理する時はもう確定しているので数値が動くことはないという認識でよろしいでしょうか？

お礼日時：2017/05/31 13:38

No.13 回答者： tatsu99 回答日時： 2017/05/30 21:11

補足要求です。

１）集計フォルダ内の全ブックの個人シートの数は何個ぐらいでしょうか。
（全社で集計対象となる個人の数はおよそ何名でしょうか。）
　処理時間の高速化の為の判断材料とする為の質問です。
　ピボットのデータ件数÷全ブックの個人シートの数＝１個人の平均件数　となりますが
　１個人の平均件数が３０００件以内であれば、問題ありません。
　３０００件を超える場合は、現在考えている方法では遅くなるため、他の方法を検討します。

この回答へのお礼

ご返信が遅くなり、申し訳ありません。
下記、記載させていただきます。

>補足要求です。
>１）集計フォルダ内の全ブックの個人シートの数は何個ぐらいでしょうか。
>（全社で集計対象となる個人の数はおよそ何名でしょうか。）
→現時点で52シートです。(52名です)今後、増える可能性があります。
>　処理時間の高速化の為の判断材料とする為の質問です。
　 >ピボットのデータ件数÷全ブックの個人シートの数＝１個人の平均件数　となりますが
　 >１個人の平均件数が３０００件以内であれば、問題ありません。
>　３０００件を超える場合は、現在考えている方法では遅くなるため、他の方法を検討します。
→数名3,000件を超える(10,000件以上)の人がいる為、遅くなりますよね。。。
　お手数ですが、他の方法をご検討頂ければ幸いです。

お礼日時：2017/05/31 11:15