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

同一フォルダ内の複数ブックの中に同じフォーマットの複数シートがあります。
その複数シートに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.2 回答者： chonami 回答日時： 2017/05/24 16:46

各ブックとの関係性やどういったデータでピボットテーブルを作成しているかどうかがわからないので、こういう案もあるというので書いただけです。

お書きになっている内容だけで、どうこうしたらうまくいきますよ、とまではちょっと書けません。
活用できなさそうなら他の方の回答をお待ちください。
使い方は、検索すればいくらでも出てきます。

この回答へのお礼

大変失礼いたしました。
アドバイス頂きありがとうございました。

お礼日時：2017/05/24 16:49

No.1 回答者： chonami 回答日時： 2017/05/24 16:15

細かいレイアウトがわからないので何とも言えませんが、元データを閉じたまま参照してきたいならMicrosoft Queryなどは如何でしょうか？

http://www4.synapse.ne.jp/yone/excel2010/excel20 …

この回答へのお礼

ご教授頂きましてありがとうございます。
ただ、Microsoft Queryについて初めて知ったもので、添付頂いたURLを見ただけでは理解できず。。申し訳ありません。
もしお分かりでしたらご教授願います。
Microsoft Queryでデータベースを作成したとしても、結果各ブックをひとつひとつ開かないと更新できないのでしょうか？
私が希望している動作としては、元データを更新すると複数ブックにあるピボッドデータも開かず更新したいのですが
Microsoft Queryでそのような動作ができるということでしょうか？

お礼日時：2017/05/24 16:42