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

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

補足要求です。

１）本年Pのデータ件数（行数）は、およそ、何件ぐらいでしょうか。（処理速度等のパフォーマンスの考慮材料とします）
２）同様に、前年Pのデータ件数も提示ください。
３）個別シートのA7に１が設定され、「新規」の判定に使用するとのことですが、
このA7の値とH列の値が同じなら、「新規」と判定する。・・・という用途で使用しているのでしょうか？

この回答へのお礼

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

>補足要求です。
>１）本年Pのデータ件数（行数）は、およそ、何件ぐらいでしょうか。（処理速度等のパフォーマンスの考慮材料とします）
→本年は5月分のみで300件ほどですが今後毎月増えていくので前年を参考値とすると45,000件ほどです。
>２）同様に、前年Pのデータ件数も提示ください。→上記で記載しまし通り、3部合算すると約45,000件です。
> ３）個別シートのA7に１が設定され、「新規」の判定に使用するとのことですが、
>このA7の値とH列の値が同じなら、「新規」と判定する。・・・という用途で使用しているのでしょうか？
→仰る通りです。

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

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

以下、確認です。

新規と既存は、ピボットのH列（1or0）を参照していますが、
これは、H列の値が1なら新規、0なら既存へ集計ということですか。
あなたの提示された条件をみると、
既存の集計は、H列の値によらず、集計する。
新規の集計は、H列の値が1の場合、集計する。
ように読み取れます。
>・大分類A(既存)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
>・大分類A(新規)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で1だった時の数量


大分類（既存）は、
・大分類A(既存)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で0だった時の数量
にすべきではないでしょうか？
それとも（既存は新規も含めた値のことなのでしょうか）

この回答へのお礼

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

>以下、確認です。
>新規と既存は、ピボットのH列（1or0）を参照していますが、
>これは、H列の値が1なら新規、0なら既存へ集計ということですか。
>あなたの提示された条件をみると、
> 既存の集計は、H列の値によらず、集計する。
> 新規の集計は、H列の値が1の場合、集計する。
>ように読み取れます。
>・大分類A(既存)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だった時の数量
>・大分類A(新規)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で1だった時の数量
→わかりづらく申し訳ありません。新規、既存の分類ですが新規だったら「1」、既存だったら「空欄」でした。失礼いたしました。
　
>大分類（既存）は、
>・大分類A(既存)=G列の中で「3」だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったらC列の中で「たなか」だったら、H列の中で0だった時の数量
>にすべきではないでしょうか？
>それとも（既存は新規も含めた値のことなのでしょうか）
→仰る通り、H列の中で新規だったら「1」、既存だったら「空欄」の数量をもとめたいです。

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

No.10 回答者： tatsu99 回答日時： 2017/05/30 10:22

補足ありがとうございました。

更に、以下の補足をお願いします。
１）拡張子の件
【実績】売上明細データ(yyyymmdd)の拡張子について質問したのは、ブックをオープンする時、
拡張子まで含めて指定する必要があるからです。（拡張子"xlsm"とします）

２）【実績】売上明細データ(yyyymmdd)のyyyymmdd部分について
1案 指定年月に01を付加した日付のファイルを対象とする。
例 2017年5月の処理なら
【実績】売上明細データ(20170501).xlsm　のブックが対象となる。
括弧及び括弧内の数字は半角であること "(20170501)"は全て半角文字
（注意：上記以外の日付のブック（20170503等）が存在するのは構いません。但し、マクロは、その日付のファイルを参照しません）

2案 yyymmddでなくyyyymm形式にする。
例 2017年5月の処理なら
【実績】売上明細データ(201705).xlsm　のブックが対象となる。
（注意：1案と同様に、上記以外の日付のブック(20170501等)が存在するのは構いません。但し、マクロは、その日付のファイルを参照しません）

上記の何れかでお願いいたします。（運用上、問題あれば、他の案も可。）


３）本年Pのシートの画像を提示してください。
1部、2部、3部でフォーマットが異なる場合は、各部ごとに提示ください。

４）本年Pと前年Pは同じフォーマットと理解していますが、間違いないでしょうか。
違う場合は、画像の提示をお願いします。

５）本年Pと前年Pの正確なシート名をご提示ください。
1部、2部、3部を付加した名称です。
1部の1は半角数字ですか。
本年PのPは半角の大文字ですか。

この回答へのお礼

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

>補足ありがとうございました。更に、以下の補足をお願いします。
>１）拡張子の件
> 【実績】売上明細データ(yyyymmdd)の拡張子について質問したのは、ブックをオープンする時、
>拡張子まで含めて指定する必要があるからです。（拡張子"xlsm"とします）
→承知いたしました。

>２）【実績】売上明細データ(yyyymmdd)のyyyymmdd部分について
>1案 指定年月に01を付加した日付のファイルを対象とする。
>例 2017年5月の処理なら
>【実績】売上明細データ(20170501).xlsm　のブックが対象となる。
>括弧及び括弧内の数字は半角であること "(20170501)"は全て半角文字
>（注意：上記以外の日付のブック（20170503等）が存在するのは構いません。但し、マクロは、その日付のファイルを参照しません）
>2案 yyymmddでなくyyyymm形式にする。
>例 2017年5月の処理なら
>【実績】売上明細データ(201705).xlsm　のブックが対象となる。
>（注意：1案と同様に、上記以外の日付のブック(20170501等)が存在するのは構いません。但し、マクロは、その日付のファイルを参照しません）
>上記の何れかでお願いいたします。（運用上、問題あれば、他の案も可。）
→ご検討、ご提案ありがとうございます。2案でお願いいたします。

> ３）本年Pのシートの画像を提示してください。
>1部、2部、3部でフォーマットが異なる場合は、各部ごとに提示ください。
→承知いたしました。申し訳ありません、補足画像添付がこれで最後な為、各部まとめてピボッドの並び順を
画像添付させていただきます。ご了承ください。

> ４）本年Pと前年Pは同じフォーマットと理解していますが、間違いないでしょうか。
>違う場合は、画像の提示をお願いします。
→フォーマットは同一です。

> ５）本年Pと前年Pの正確なシート名をご提示ください。
>1部、2部、3部を付加した名称です。→1部本年P、2部本年P、3部本年Pです。
>1部の1は半角数字ですか。→すべて半角数字です。
>本年PのPは半角の大文字ですか。→すべて半角の大文字です。

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

No.9 回答者： tatsu99 回答日時： 2017/05/29 16:12

補足ありがとうございました。

詳細はこれから確認いたしますが、以下の前提条件で良いですか。
１）今回のマクロでは、一回の実行で一ケ月分のデータを処理する。
２）処理する年月（2017年5月等）は、実行時、マクロのあるブックの特定のシートに記入されているものとする。
３）処理対象となる支店は全支店とする。（特定の支店のみ指定して集計はしない）
４）集計の為に参照する元データは、ピボットである。
１つのブック内の以下のシート（①～⑤は1部、2部、3部毎にあるので3×５＝１５シート）
①管理（マクロ実行用）
②売上明細データ前年
③売上明細データ本年
④前年P（②のピボット）
⑤本年P（③のピボット）
⑥1部シート、3部シート
上記で、集計元データとして使用するのは、④⑤のみである。（これ以外にありますか？）

５）もし、処理する年月が2017年5月の場合、
参照するブックは　【実績】売上明細データ(yyyymmdd)　のyyyymmdd部分の日付は何になりますか。
yyyymmdd部分は処理する年月が決まれば、一義的にきまりますか。
例　処理年月＝2017年5月の場合　【実績】売上明細データ(20170501)
　　処理年月＝2017年7月の場合　【実績】売上明細データ(20170731)
等のようになっていると、どのブックを参照するのかが判りません。
yyyymmddのddの部分は、
1案　必ず1日(01)とする。例　20170701,20170501等
2案　必ず末日とする。　 例 20170701,20170531等
3案　必ず15日とする。　例 20170715,20170515等
のような案にできませんでしょうか。
尚、末日を選択した場合、うるう年の2月の末日は29日になります。

６）【実績】売上明細データ(yyyymmdd)の拡張子は何でしょうか。xlsxであってますか。

この回答へのお礼

早速ご返信頂きましてありがとうございます。
下記、記載させて頂きます。

>補足ありがとうございました。詳細はこれから確認いたしますが、以下の前提条件で良いですか。
>１）今回のマクロでは、一回の実行で一ケ月分のデータを処理する。
→個人別シートの「前年」データについては、2016.7.1～2017.4.30までのデータが入っている為すべての月の集計をしたいです。個人別シートの「計画」については、元データはなく各支店ごとの個人シートへ直接値で個人で入力している為集計は不要です。※別途、ご教授頂いた合計シート、それ以外シートへ集計できていれば問題ありません。わかりづらいのが個人別シートの「実績」についてなのですが、仰る通り1回の実行で1か月分のデータを処理する。5月中であれば5/1から5/31までの受注がある分のデータを処理することになります。私がお伝えしたいことと、tatsu99様の認識に相違ありましたら申し訳ありません。

>２）処理する年月（2017年5月等）は、実行時、マクロのあるブックの特定のシートに記入されているものとする。
→下記にある①管理(マクロ実行用)のシートに5月であれば5月実績というブックの参照先が入っており、データベース作成用のマクロを実行をすると
③売上明細データ本年につみあがっていくようになっています。ですので、処理する年月が特定シートに記入されているわけではないのですが、マクロ作成するにあたり、変更したほうが良い等指示がありましたらご教示願います。

>３）処理対象となる支店は全支店とする。（特定の支店のみ指定して集計はしない）
→おっしゃる通りです。

>４）集計の為に参照する元データは、ピボットである。
>１つのブック内の以下のシート（①～⑤は1部、2部、3部毎にあるので3×５＝１５シート）
>①管理（マクロ実行用）
>②売上明細データ前年
>③売上明細データ本年
>④前年P（②のピボット）
>⑤本年P（③のピボット）
>⑥1部シート、3部シート
>上記で、集計元データとして使用するのは、④⑤のみである。（これ以外にありますか？）
→おっしゃる通り、参照元となるピボッドは④⑤×3部=計6シートです。

お礼日時：2017/05/29 17:19

No.8 回答者： tatsu99 回答日時： 2017/05/26 23:56

補足要求で各ブック、各シートの情報の提示をセルの説明、画像等でお願い指定していますが、

もし、出来るようであれば、簡単なテスト用のExcelファイル一式を下記のサイトに格納していただけるとありがたいです。
http://firestorage.jp/
このサイトは、ユーザー登録不要で、無料で利用可能です。
その場合は、ファイルを格納したＵＲＬ及びパスワードを提示してください。
当然、そのExcelファイルは第3者にみられても全く問題ないように、個人及び会社に関する部分は匿名にしてください。
又、Excelファイルを右クリックすると、プロパティが表示され、詳細タブを表示すると「プロパティや個人情報を削除」の機能が
ありますので、個人情報も削除してください。
又、その際は、集計対象のセル及び集計する為に参照するセルに色をつけておいていただけると助かります。
こちらでは、そのファイルを使用してテストを行います。
それによって大分、行き違いもなくなり、お互いの労力も半減すると思います。

上記につきましては、会社のセキュリティにかかわりますので、セキュリティ上、問題なしと判断された場合で結構です。

この回答へのお礼

ご返信遅くなり、申し訳ありません。
私もこちらですと、文字数制限があったり不便であり、かつtatsu99様に大変なお手間をおかけしていることも
心苦しい限りなのですが。。。会社のセキュリティー上、共有することが難しい状況です。
大変申し訳ありません。
ですので、できるだけtatsu99様に内容を理解頂けるよう心がけますので引き続きこちらでよろしくお願いいたします。

>補足要求の続きです。
>集計対象の確認です。
>１）集計対象のブックは特定フォルダ内の全ブック(拡張子 xlsx)とする。
→承知しました。
>２）集計対象のシートは、個人別のシートのみとする。
>（ブック内に集計対象外のシートがあり、"合計"シート以降は集計しない）
→承知しました。
>３）個人別の集計項目がよくわかりません。
>あなたが提示された個人シートの添付図で見た場合、何行目になりますか。
>例
>8行の③部①
>9行の③部②
>のように回答するか、集計対象とする項目を色付けして、再度、提示していただけますでしょうか。
→色分けして、3部と1.2部分けて画像添付いたします。
★3部集計項目→項目：①(8行目)、項目：②(9行目)大分類：Aの既存(10行目)、Aの新規(11行目)、大分類：Bの既存(13行目)、Bの新規(14行目)、大分類：Cの既存(16行目)、Cの新規(17行目)、大分類：D(19行目)、E(２０行目)、F(21行目)
★1部集計項目→大分類：Gの既存(23行目)、Gの新規(24行目)、大分類：Hの既存(26行目)、Hの新規(27行目)、大分類：I(２９行目)
★2部集計項目→大分類：J(31行目)、K(32行目)、L(33行目)

>４）上記の項目について、集計方法を（関数式の表ではなく）文章で定義したものを提示して
v いただけますでしょうか。
>どのブックのどのシートのどの項目（行、列）を参照するのか、また、どのような条件の時、集計するのかしないのか等がわかるものがほしいです。
>（関数式は当面こちらでは使いませんが、最終的な齟齬の確認のために、再度、提示をお願いするかも知れません。
>ちなみに、今、提示されている関数式は、残念ながら、小さすぎて良くわかりません。）
→記載させていただきました。わかりづらい点等ありました、補足要求願います。

お礼日時：2017/05/29 13:28

No.7 回答者： tatsu99 回答日時： 2017/05/26 15:35

補足要求の続きです。

集計対象の確認です。
１）集計対象のブックは特定フォルダ内の全ブック(拡張子 xlsx)とする。
２）集計対象のシートは、個人別のシートのみとする。
（ブック内に集計対象外のシートがあり、"合計"シート以降は集計しない）
３）個人別の集計項目がよくわかりません。
あなたが提示された個人シートの添付図で見た場合、何行目になりますか。
例
8行の③部①
9行の③部②
のように回答するか、集計対象とする項目を色付けして、再度、提示していただけますでしょうか。

４）上記の項目について、集計方法を（関数式の表ではなく）文章で定義したものを提示して
いただけますでしょうか。
どのブックのどのシートのどの項目（行、列）を参照するのか、また、どのような条件の時、集計するのかしないのか等がわかるものがほしいです。
（関数式は当面こちらでは使いませんが、最終的な齟齬の確認のために、再度、提示をお願いするかも知れません。
ちなみに、今、提示されている関数式は、残念ながら、小さすぎて良くわかりません。）

追伸：これから出かけますので、夜、7時以降でないと、返信できません。ご了承ください。

この回答へのお礼

個人別シートのA列は作業列になっています。A7セルに「1」が入っています。新規かどうか判断する際の作業セルです。ちなみに、前年、計画にはこの条件は含まず、実績のみに新規だったら1という条件を入れたいです。「B2：部門、C2：○○支店、B3：所属、C3：一課、B4：担当者、C4：たなか」の条件は3部とも必須条件です。
★1部集計項目詳細
1部→A列：売上日付、B列(集計条件不要)、C列：担当者名、D列：支店名、E列：所属名、F列(集計条件不要列)、G列：作業列①(1か2)、H列：作業列②(1だったら)、I列：作業列③(新規だったら1)、J列：数量、K列：売上
集計条件　
・大分類G(既存)=G列の中で1だったらD列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったら,C列の中で「たなか」だった時の数量
・大分類G(新規)=G列の中で1だったらD列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったら,C列の中で「たなか」だったら、I列で1(新規)だった時の数量)
・大分類H(既存)=G列の中で1だったらD列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったら,C列の中で「たなか」だった時の数量
・大分類H(新規)=G列の中で1だったらD列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったら,C列の中で「たなか」だったら、I列で1(新規)だった時の数量)
・大分類I=H列の中で1だったら、D列で○○支店だったら、E列で1課だったら、A列で7月だったら、C列で田中だった時の売上

★2部集計項目詳細
2部→A列：売上日付、B列：コード(J、K、L)、C列：担当者名、D列：支店名、E列：所属名、F列(集計条件不要列)、G列：作業列①(新規だったら1)、H列：数量、I列：売上
・大分類J=B列の中で4だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったら、C列の中で「たなか」だった時の数量
・大分類K=B列の中で5だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったら、C列の中で「たなか」だった時の数量
・大分類L=B列の中で6だったら、D列の中で○○支店だったら、E列の中で1課だったら、A列の中で7月だったら、C列の中で「たなか」だった時の売上

お礼日時：2017/05/29 14:27

No.6 回答者： tatsu99 回答日時： 2017/05/26 15:31

補足回答ありがとうございました。

以下、気になる点のみ、返信します。（下記以外は問題ありません）
>→当月の１日から月末までの受注データとなりますのでひと月単位となります。

その場合、そのブック名は、どうなりますか。
例えば、2017年7月の集計を行う時に、マクロはそのブック名をどうやって決めるのかが判りません。
【実績】売上明細データ(20150701~20160630)の1つです。の
【実績】売上明細データ(20150701）なのか
【実績】売上明細データ(20150731）なのか、それとも以外なのかが判りません。
提示例では、０１日の例もあるし、月末日の例もあるので、質問した次第です。

>>２）本件で作るマクロは運用時、1月（ひとつき）に1回実施と考えてますが、あってますか。
>>それとも、１日（いちにち）に１回実施ですか。
>→更新頻度は、１日（いちにち）に１回実施)(毎日更新)もしくは2週間に1度になるのかまだ未定となっております。
>それによってマクロが変わってきてしまうということでしょうか？

月単位で集計を行う為、マクロ実行時は、集計対象の年月（例 2017/7）が指定されることを、想定しています。
この認識で、あっているなら、月末か、翌月の頭で、マクロを１回実施すればよいと考えています。
1日１回おこなっても、毎回、指定された月のデータを最初からみて作成しますので、余分な作業になると思った為です。
但し、会社の営業戦略的な理由で、「今月の状態を途中までで構わないから、今すぐ、集計結果がみたい」という要件があるなら、
毎日、実行する意味はあります。

この回答へのお礼

№9のお返事の続きをこちらに記載させていたただきます。
こちらの質問に気づかずに大変失礼いたしました。
また、補足があと1回しか記載することができずこちらに記載させていただくことご了承ください。

>５）もし、処理する年月が2017年5月の場合、
>参照するブックは　【実績】売上明細データ(yyyymmdd)　のyyyymmdd部分の日付は何になりますか。
>yyyymmdd部分は処理する年月が決まれば、一義的にきまりますか。
>例　処理年月＝2017年5月の場合　【実績】売上明細データ(20170501)
>処理年月＝2017年7月の場合　【実績】売上明細データ(20170731)
>等のようになっていると、どのブックを参照するのかが判りません。
>yyyymmddのddの部分は、
>1案　必ず1日(01)とする。例　20170701,20170501等
>2案　必ず末日とする。　 例 20170701,20170531等
>3案　必ず15日とする。　例 20170715,20170515等
>のような案にできませんでしょうか。
>尚、末日を選択した場合、うるう年の2月の末日は29日になります。
→yyyymmdd部分は、現状（20160701～20180630）と1年としております。2）で記載しましたように、今後データベース化していくので。。と思っている為です。ただ、マクロを作成するにあたりtatsu99さまのほうで、ご指示があるようでしたらご教示頂ければ幸いです。

>６）【実績】売上明細データ(yyyymmdd)の拡張子は何でしょうか。xlsであってますか。
→現在、2）で記載しましたようにマクロを作成している為、xlsmとなっておりますがマクロ実行時、エクセルで。。ということであれば処理用に変換するようにしますがその処理でよろしいでしょうか？

№6の回答
マクロ実行頻度ですが、おっしゃる通り毎日、もしくは月途中の売上推移を見たいとの要望がある為月1回ではなく前者で述べた頻度になる可能性が高いのです。

恐れ入りますが、よろしくお願いいたします。

お礼日時：2017/05/29 17:26

No.5 回答者： tatsu99 回答日時： 2017/05/26 14:44

本件、かなり複雑そうなので、何回か補足要求が続きそうです。

ご了承ください。
まず、前提条件として、以下の補足要求です。

１）【実績】売上明細データの存在単位はどうなってますか。
1月（ひとつき）に１ブックですか。
1日（いちにち）に１ブックですか。

２）本件で作るマクロは運用時、1月（ひとつき）に1回実施と考えてますが、あってますか。
それとも、１日（いちにち）に１回実施ですか。

３）現状は関数式で実装されており、その場合は、元データを変えると、リアルタイムで変更分が反映されます。
今回、マクロでの実装になると、元データが変わっても、リアルタイムでは変更分が反映されませんがよろしいでしょうか。
変更分が反映されるのは、マクロを実施した直後になります。
（リアルタイムで反映されなくなることによる障害はないでしょうか）

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

この回答へのお礼

>本件、かなり複雑そうなので、何回か補足要求が続きそうです。ご了承ください。
→こちらこそ、毎回大変なお手間をおかけしており申し訳なく思っております。
補足要求の返答をさせていただきながら、かなり複雑なことを質問しているなと思っておりました。
ですが、やりとりをさせて頂く中で、私もマクロについて勉強させてただきたいと思っております。
恐れ入りますが、何卒よろしくお願いいたします。

>１）【実績】売上明細データの存在単位はどうなってますか。
>1月（ひとつき）に１ブックですか。
>1日（いちにち）に１ブックですか。
→当月の１日から月末までの受注データとなりますのでひと月単位となります。
前年の実績については、昨年の7月から今年の4月までのデータがデータベースとして1シートに存在しています。
それをピボッドしています。本年の実績もどんどん積み重ねてデータベースにしていく予定です。

>２）本件で作るマクロは運用時、1月（ひとつき）に1回実施と考えてますが、あってますか。
>それとも、１日（いちにち）に１回実施ですか。
→更新頻度は、１日（いちにち）に１回実施)(毎日更新)もしくは2週間に1度になるのかまだ未定となっております。
それによってマクロが変わってきてしまうということでしょうか？

>３）現状は関数式で実装されており、その場合は、元データを変えると、リアルタイムで変更分が反映されます。
>今回、マクロでの実装になると、元データが変わっても、リアルタイムでは変更分が反映されませんがよろしいでしょうか。
>変更分が反映されるのは、マクロを実施した直後になります。
>（リアルタイムで反映されなくなることによる障害はないでしょうか）
→恐れ入りますが、先に質問させていただいた合計シートの更新の前にこの処理をするということですよね？
元データの更新→3）の処理マクロを実行→合計シートのマクロ実行→完了であればリアルタイムに反映されなくても問題ございません。
認識が違っておりましたら申し訳ありません。

※文字数オーバーの為、4）については補足に記載させて頂きます。

お礼日時：2017/05/26 15:10

No.4 回答者： tatsu99 回答日時： 2017/05/24 19:15

>その複数シートにSUMIFS関数で集計用の数式を組んでおり、その参照先元データ(ピボッド)が別ブックにあります。

要件としては、以下の内容であってますか。
①その複数シートにSUMIFS関数で集計用の数式を組んでいる箇所を、関数ではなくマクロで実装したい。
②その場合、同一フォルダ内の複数ブックも、参照先元データ(ピボッド)の別ブックも、マクロでブックを開き、処理が終われば、マクロで閉じること。（予め人間が開いておく必要はないようにしたい）

もし、その要件であっていれば、以下の情報をご提示ください。
１）参照先元データ(ピボッド)のブック名（複数か１つかも含めて）
２）参照先元データ(ピボッド)のブックのシート名（複数か１つかも含めて）
３）参照先元データ(ピボッド)のブックのシートのフォーマット（セルの位置情報）
（SUMIFS関数で集計用の数式に関するセルの位置情報は必須です。）

４）複数ブックの中に同じフォーマットの複数シートのブック名、シート名
５）上記シートのセル情報、及び、そのセルのどこにSUMIFS関数の結果を格納したいのかの情報
６）SUMIFS関数で集計している機能を業務的に説明したもの

この回答へのお礼

いつもありがとうございます。
また、返事が遅くなってしまい申し訳ありません。
ご質問頂いた内容に回答させていただきます。
私の回答がずれていたり、回答不足な点ありましたらご指摘頂ければ幸いです。何卒よろしくお願いいたします。

>>その複数シートにSUMIFS関数で集計用の数式を組んでおり、その参照先元データ(ピボッド)が別ブックにあります。

>要件としては、以下の内容であってますか。
>①その複数シートにSUMIFS関数で集計用の数式を組んでいる箇所を、関数ではなくマクロで実装したい。
→おっしゃる通りです。
>②その場合、同一フォルダ内の複数ブックも、参照先元データ(ピボッド)の別ブックも、マクロでブックを開き、処理が終われば、マクロで閉じること。（予め人間が開いておく必要はないようにしたい）
→おっしゃる通りです。

>もし、その要件であっていれば、以下の情報をご提示ください。
>１）参照先元データ(ピボッド)のブック名（複数か１つかも含めて）
→ブック名：【実績】売上明細データ(20150701~20160630)の1つです。

>２）参照先元データ(ピボッド)のブックのシート名（複数か１つかも含めて）
→複数あります。
　①管理(1部)→本年度実績データを日々積み上げてデータベースにする為の　マクロ実行の際、参照するファイル名を入れてあります。
　②売上明細データ(1部)前年→前年実績の元データ
　③売上明細データ(1部)実績→本年度実績の元データ
　④１部前年P：②のピボッド
　⑤1部本年P：③のピボッド
　3部門に分かれている為、上記①から⑤のシートが1部、2部、3部のそれぞ　れありますので計：15シートあります。
　その他に売上明細データ前年、本年分の中で抽出したい条件がある為、そ　れ用の作業リストが1部と3部に1シートずつあり
　それぞれ「1部リスト」、「3部シート」というシート名です。

※文字数多い為、補足に続きを記載いたします。

お礼日時：2017/05/25 11:35

No.3 回答者： fujillin 回答日時： 2017/05/24 17:29

こんにちは

＞元データを更新すると開かずともすべて更新できるようなマクロを組みたいです
ブック間で値を参照しようとする場合、参照式でも、マクロでもブックを開いて行うのが最も一般的な方法と思います。
これを『開かないで～』と条件付けなさっている意味が分かりかねます。
　・速度の問題（直接参照した方が早い場合がある）
　・見た目の問題（他のブックが開いているのが見えるのが嫌）
といったことが考えられますが…

ブックを開かずに値を参照する方法は、あることはあります。
旧いExecuteExcel4Macroの時代からある方法で、現在でも使えるようです。
http://excel-ubara.com/excelvba5/EXCELVBA242.html
http://officetanaka.net/excel/vba/tips/tips28.htm
時間的なことから言えば、この方が若干速い可能性はありそうです。

一方で、見た目だけの話であれば、
新しいExcel.Applicationオブジェクトからブックを開けば、Visible = Falseの設定（デフォルトはfalse)にしておくことで、非表示のままで他のブックを操作することが可能です。
（↑のサイトにも方法の紹介がありますので、そちらを見てください）