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

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

すみません。

操作を間違えたようです。No31のURLも期限切れになりました。
こちらからお願いします。パスワードは 0605 です。

http://firestorage.jp/download/6783d719aa18d9014 …

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

No30でダウンロードURLが有効期限切れになってしまいました。

こちらから、ダウンロードしてください。
http://firestorage.jp/download/f5ff4933d9f22a1c1 …

この回答へのお礼

マクロ作成、大変お手数をおかけしております。
また、作成頂きましてありがとうございます。

そして、ダウンロードが遅くなり、申し訳ありません。
先ほど、№32でダウンロードいたしました。
マクロ実行をしてみて、不明点、状況等を№32にて記載させて頂きます。
少しお時間を頂ければ幸いです。
何卒、よろしくお願いいたします。

お礼日時：2017/06/05 12:03

No.30 回答者： tatsu99 回答日時： 2017/06/05 06:41

climbiが使用できない為、

下記URLにアップロードしました。パスワードは 0605 です。ここからダウンロードしてください。
標準モジュールに格納してあります。
http://firestorage.jp/download/6e890aedcc98acd8d …

以下、使用時の注意事項です。
１）Pmax,Rmaxについて
マクロの先頭に
Const Pmax As Long = 100 '従業員人の最大数
Const Rmax As Long = 600 '1ピボットシート内の1人のひと月の最大行数

と定義されていますが、このマクロがサポートする最大数が定義されています。
補足回答では、
従業員数：５２名
ピボットの1人当たりのひと月の行数：３００行
との回答でしたので、52->100,300->600として定義しています。
将来、この設定を超えた場合は、この数字を大きくしてください。
（従業員数が100名を超えるか、ピボットの1人当たりのひと月の行数が600行を超えた場合）
この数字に従って、内部メモリを確保しますので、いたずらに大きくすると、Excelが動かなくなります。
（例 Pmax:10000,Rmax:100000等）
Pmax=1000 Rmax=1000程度までは、問題なく動くと思いますが、小さいほうがメモリの使用量が少なります。

運用時、この上限を超えた場合は、Pmaxオーバー又はRmaxオーバーの
エラーメッセージが表示され、マクロが停止します。
その場合は、この値を大きくしてください。
このエラーメッセージを実際に確認する場合は、いずれかの値を小さくすれば、その値のエラーが発生します。（例 Pmax＝10 にすると　Pmaxオーバーのエラーが発生します）
確認後は、この値を元に戻してください。

２）エラー発生後のマクロの実行
エラーが発生すると、マクロが停止しますが、その時、実績売上明細ブック、各支店ごとのブックが
開いたままの状態になっています。
その後マクロを実行する場合は、上記のブックを全て閉じてから、実行してください。

３）個人別シートのA列の値
関数式で運用時は、個人別シートのA列に、1,2,3,4等の数値を格納し、それを使用していますが、
マクロでは、上記の数値は、マクロ内部に組み込んでいます。
従って、A列の値を変えた場合は、関数式での運用には影響しますが、マクロの動作には影響しません。
逆に言えば、仕様変更により、A列の値を変えても、マクロの動作は変わりませんのでご注意ください。
その場合は、マクロの内容を直接修正する必要があります。（修正しやすいようにコメントを付加しています）

４）マクロ実行時の指定項目
マクロのあるブックに、シート名が"管理"であるシートの
A2に集計年（現在はこの入力値のチェックのみ行い、この項目は集計には使用していません）
B2に集計月
C2に集計項目（実績又は前年）
D2に実績フォルダ名
E2に実績売上明細のブック名（拡張子はxlsx又はxlsmであること）（拡張子を含めて指定すること）
F2に計画フォルダ名
を指定します。

実績売上明細のブック名は、拡張子がxlsx又はxlsmの何れかなら何でも構いません。
以下の名前は全て有効です。
【実績】売上明細データ(201705).xlsx
【実績】売上明細データ(20170501).xlsm
Book1.xlsx

５）集計年について
現在は、A2の集計年は、集計の為に使用していませんが、もし、今後、
【実績】売上明細データのピボットに複数年度のデータを格納したい場合は、
集計年と集計月の両方を条件として、データを抽出するようにすることもできます。
その為には、ピボットのA列を以下のいずれかの形式に変更する必要があります。
①表示上は、1月、2月になっているが、実際の内容は日付（2017/1/21等）が格納されている。
②表示も、実際の内容も日付になっている。（例 2017/1/21）
（このA列の表示は、あなたの回答から推測すると、表示も実際の内容も1月となっているように思われます）
上記のどちらかなら、A列が集計年と集計月に一致するものを抽出することは可能です。
この要望があれば、その旨、補足ください。
但し、複数年のデータを格納すると、ブックのサイズも大きくなり、処理にも影響するので、2～3年の範囲が限度かと思います。

６）処理時間について
参考までに、本番環境に近い状態で試験時、どのくらい処理時間がかかったか教えていただけますでしょうか。
（マクロ終了時、所要時間（秒）が表示されます。）

この回答へのお礼

お忙しい中、何度もお手数をおかけしております。
また、私の説明が拙いにも関わらずマクロを作成いただきありがとうございます。
大変感謝しております。

下記、追加で質問を記載させて頂いております。
ご回答頂けますと幸いです。

>climbiが使用できない為、
>下記URLにアップロードしました。パスワードは 0605 です。ここからダウンロードしてください。
>標準モジュールに格納してあります。
>http://firestorage.jp/download/6e890aedcc98acd8d …
→ダウンロードが遅くなり、申し訳ありませんでした。

>以下、使用時の注意事項です。
> １）Pmax,Rmaxについて
>２）エラー発生後のマクロの実行
>３）個人別シートのA列の値
→上記の件、承知いたしました。

>４）マクロ実行時の指定項目
>マクロのあるブックに、シート名が"管理"であるシートの
※中略させて頂きます。
→上記で実行しようとしたところ、早速エラーが出てしまい「ファイルが存在しません」と出てしまいました。
ちなみに、テストとして「 【実績】売上明細データ(201601).xlsx」というファイルを「実績」というフォルダに置き
実績フォルダ名はそのフォルダが格納されているアドレスを、実績ファイル名は上記名で、「計画」というフォルダには
集計先の支店のファイルを格納しているアドレスを「管理」シートへ例と同じように入力したのですがエラーが出てしまいます。
原因は何が考えられますでしょうか？

>５）集計年について
>現在は、A2の集計年は、集計の為に使用していませんが、もし、今後、
【実績】売上明細データのピボットに複数年度のデータを格納したい場合は、
集計年と集計月の両方を条件として、データを抽出するようにすることもできます。
※中略させていただきます。
→承知いたしまた。
ちなみに、表示の件ですが元データ上は2017/1/21になっておりますし、ピボッドも元は2017/1/21ですが
月だけで表示しているので2017/1/21という表示にしたほうが確実なのでしょうか？

お礼日時：2017/06/05 13:59

No.29 回答者： tatsu99 回答日時： 2017/06/04 00:51

>>A既存の前年 G列の値が３（H列は参照しない）・・・・・ここが確認したい箇所です。

>→H列を上記と同じように参照したいです。
回答：承知いたしました。前年の既存と実績の既存は、同じ方法で集計します。

>→ちなにに201601を読み込むと集計年月2016年6月の集計を行うということですね？
回答：はい、そうなります。
>そうすると集計年月を12回修正すれば処理可能ということですね？
>そちらの方法でできれば助かります。
回答：承知しました。

仕様がまとまりましたの、マクロの製造に入ります。


確認事項
【実績】売上明細データは、そのブック名をマクロ実行時に指定できるようにしましたので、
マクロ実行時に、年を指定する意味がなくなってしましました。
当初は、年を指定するのは、ブック名を決める為のものでした。
しかし、ブック名は、マクロ操作者が決めるため、マクロ実行時、指定年を使用しなくなりました。
現状では、指定年を使用しませんが、今後のこともあるかも知れませんので、使わないが残しておきたいと考えています。
マクロ実行時、A2列へ集計年を指定しますが、入力年のチェックのみ行うようにし、その指定年は使用しません。それでよろしいでしょうか。
（データの集計時、指定月に合致するデータを集計します。指定年月に合致するデータではありません。）

この回答へのお礼

お返事が遅くなり、申し訳ありません。

>確認事項
>【実績】売上明細データは、そのブック名をマクロ実行時に指定できるようにしましたので、
>マクロ実行時に、年を指定する意味がなくなってしましました。
>当初は、年を指定するのは、ブック名を決める為のものでした。
>しかし、ブック名は、マクロ操作者が決めるため、マクロ実行時、指定年を使用しなくなりました。
>現状では、指定年を使用しませんが、今後のこともあるかも知れませんので、使わないが残しておきたいと考えています。
>マクロ実行時、A2列へ集計年を指定しますが、入力年のチェックのみ行うようにし、その指定年は使用しません。それでよろしいでしょうか。
>（データの集計時、指定月に合致するデータを集計します。指定年月に合致するデータではありません。）
→承知いたしました。そちらの仕様で問題ございません。

お礼日時：2017/06/05 11:50

No.28 回答者： tatsu99 回答日時： 2017/06/02 11:26

No27の関連です。

もし、１つのブックをその都度リネームして運用する予定なら、ブック名を添付図のように指定する案もあります。
現状では、指定年月に合致する【実績】売上明細データ(201607).xlsmを読み込みますが、このファイル名を
予め、指定しておき、それを読み込むことは可能です。添付図のE列参照。

例えば、集計年月で2017年6月を指定し、実績ファイル名で、【実績】売上明細データ(201601).xlsm
を指定した場合は、
【実績】売上明細データ(201601).xlsm を読み込み、2017年5月の集計を行います。

そうすれば、前年の集計で毎回、ブック名を変えないで済みます。

この回答へのお礼

№27の返信と行き違いになってしまい申し訳ありません。
続けて、下記記載いたします。

>No27の関連です。
>もし、１つのブックをその都度リネームして運用する予定なら、ブック名を添付図のように指定する案もあります。
>現状では、指定年月に合致する【実績】売上明細データ(201607).xlsmを読み込みますが、このファイル名を
>予め、指定しておき、それを読み込むことは可能です。添付図のE列参照。

>例えば、集計年月で2017年6月を指定し、実績ファイル名で、【実績】売上明細データ(201601).xlsm
>を指定した場合は、
>【実績】売上明細データ(201601).xlsm を読み込み、2017年5月の集計を行います。
>そうすれば、前年の集計で毎回、ブック名を変えないで済みます。
→ご提案ありがとうございます。
>【実績】売上明細データ(201601).xlsm を読み込み、2017年5月の集計を行います。
→ちなにに201601を読み込むと集計年月2016年6月の集計を行うということですね？
そうすると集計年月を12回修正すれば処理可能ということですね？
そちらの方法でできれば助かります。

お礼日時：2017/06/02 13:09

No.27 回答者： tatsu99 回答日時： 2017/06/02 11:06

No24の補足への回答です。

>ちなみに5）とかかわることだと認識しておりますが5）ではフォルダだけの指定ですが、実績、前年ともにフォルダの中に入っているデータのブックには1部、2部、3部のピボッド以外のシートもありますがそれは問題ございませんでしょうか？(1部、3部の作業シート、ピボッドの元データがそれぞれ1部、2部、3部とあります)的外れな質問でしたら申し訳ありません。

回答：ピボット１，２，３部のシート名を直接指定して読み込みますので、他のシートはあってもなくても構いません。
ピボット１，２，３部のシート名は、今まで通り以下の内容で良いですか。
1部本年P
2部本年P
3部本年P
もし、シート名を変えたい場合は、その旨補足ください。
（現行は関数で運用しているので、そのまま使用したいなら、現状通りです。
今後は、格納しているデータに前年はないので、本年Pをとり、1部Pとすること等も考えられますが、あなたが運用しやすい名称にしてください）


>→2）と3）で、前年の数値を集計する場合、添付図の管理でマクロを実行する際、「指定された年月の【実績】売上明細データ(yyyymm)」の部分を都度
手修正するという認識でよろしいでしょうか？

回答：手修正の意味がよくわかりませんが、ブック名を変更するという意味でしょうか。
例えば、2017年7月の時点で、2016年7月から2017年6月までのデータを（前年を指定）集計する場合は、
実フォルダ内に
【実績】売上明細データ(201607).xlsm
【実績】売上明細データ(201608).xlsm
【実績】売上明細データ(201609).xlsm
【実績】売上明細データ(201610).xlsm
【実績】売上明細データ(201611).xlsm
【実績】売上明細データ(201612).xlsm
【実績】売上明細データ(201701).xlsm
【実績】売上明細データ(201702).xlsm
【実績】売上明細データ(201703).xlsm
【実績】売上明細データ(201704).xlsm
【実績】売上明細データ(201705).xlsm
【実績】売上明細データ(201706).xlsm
上記の12個のブックが既に格納されていると考えていたのですが、あってますか。
そうであれば、
＜＜「指定された年月の【実績】売上明細データ(yyyymm)」の部分を都度手修正する＞＞
する必要はなく、
2016年7月から2017年6月の指定でマクロを12回実行すれば良いだけです。

実フォルダ内に１つのブックだけしか格納しないなら、その都度、該当するブック名を修正等をしておく必要があります。
マクロが要求するのは、指定年月に合致するブック【実績】売上明細データ(yyyymm)が、実績フォルダ内に存在することです。
他のブックは、あってもなくても構いません。

運用上は１つのブックをその都度月を変更していく予定でしょうか。
例 現在が2017年５月場合
【実績】売上明細データ(201705)・・・・①
　2017年6月になった場合
【実績】売上明細データ(201706)・・・・①のファイル名をこの名称に変更
そうであれば、12か月分の前年データを処理する場合、ブック名もそれに合わせて変更が必要になります。

もし、ディスク容量が問題にならない、データの修正などが発生しないという前提でよいなら、
売上明細データ(201705)を売上明細データ(201706)にリネームする際、売上明細データ(201705)のバックアップをとり、それを残しておくことを推奨します。そうすれば、前年の項目を集計するときに楽になります。

この回答へのお礼

度々、恐縮です。
以下、記載いたします。

>No24の補足への回答です。
> 回答：ピボット１，２，３部のシート名を直接指定して読み込みますので、他のシートはあってもなくても構いません。
>ピボット１，２，３部のシート名は、今まで通り以下の内容で良いですか。
>1部本年P
> 2部本年P
> 3部本年P
>もし、シート名を変えたい場合は、その旨補足ください。
>（現行は関数で運用しているので、そのまま使用したいなら、現状通りです。
>今後は、格納しているデータに前年はないので、本年Pをとり、1部Pとすること等も考えられますが、あなたが運用しやすい名称にしてください）
→今まで通りで大丈夫です。前年は1部前年P、2部前年P、3部前年Pとし、本年は1部本年P、2部本年P、3部本年Pとします。

>>→2）と3）で、前年の数値を集計する場合、添付図の管理でマクロを実行する際、「指定された年月の【実績】売上明細データ(yyyymm)」の部分を都度
>手修正するという認識でよろしいでしょうか？
> 回答：手修正の意味がよくわかりませんが、ブック名を変更するという意味でしょうか。
>例えば、2017年7月の時点で、2016年7月から2017年6月までのデータを（前年を指定）集計する場合は、
> 実フォルダ内に～
※中略させていただきます。

>もし、ディスク容量が問題にならない、データの修正などが発生しないという前提でよいなら、
>売上明細データ(201705)を売上明細データ(201706)にリネームする際、売上明細データ(201705)のバックアップをとり、それを残しておくことを推奨しま>す。そうすれば、前年の項目を集計するときに楽になります。
→ご教授頂きましてありがとうございます。また、データのバックアップの件もご親切にご提案いただき感謝いたします。
仰る通り、実フォルダに12ブックはなく、フォルダ内に1つのブックが格納されているだけです。ですので、都度変更していくこととなりますのでバックアップをとるようにいたします。ありがとうございます。

お礼日時：2017/06/02 13:05

No.26 回答者： tatsu99 回答日時： 2017/06/02 09:10

確認＆質問事項

１）ピボットのA列は、1月、2月のような表示がされていますが、
①1月、2月の文字を格納している。
②日付を格納しているが、書式設定で月のみ表示するようにしている。
　例 データ内容は2017/7/4、表示は7月
上記のどちらででしょうか。
マクロで判定するときに①②のどちらかにより、マクロの内容が変わる可能性があるので質問しました。

２）前年の集計をする場合の集計条件
前年新規は、集計しない旨、了解しました。
例として田中の2016年の3部のA新規／既存が以下の値だったとします。
　　　7月　（2016年7月時点の田中の個人シート）
A新規 200
A既存 300

そうすると2017年になって、上記の値を前年として表示する場合は、500を表示すべきと考えますがあってますか。
　　　7月　（2017年7月時点の田中の個人シート）
A前年 500

つまり、前年の集計条件は、以下のようになると考えますが、あってますか。
（支店名、課名、個人名、月は一致している前提とすると）
A既存の実績 G列の値が３　かつ H列の値が空白
A新規の実績 G列の値が３　かつ H列の値が１
A既存の前年 G列の値が３（H列は参照しない）・・・・・ここが確認したい箇所です。

この回答へのお礼

確認、質問頂きましてありがとうございます。
下記、回答させて頂きます。

>確認＆質問事項
> １）ピボットのA列は、1月、2月のような表示がされていますが、
>①1月、2月の文字を格納している。
>②日付を格納しているが、書式設定で月のみ表示するようにしている。
>例 データ内容は2017/7/4、表示は7月
>上記のどちらででしょうか。
>マクロで判定するときに①②のどちらかにより、マクロの内容が変わる可能性があるので質問しました。
→②です。元データ自体は2017/7/4といった日付になっておりますがピボッドで表示する際グループ化で
月しか表示していない状況です。

>２）前年の集計をする場合の集計条件
>前年新規は、集計しない旨、了解しました。
>例として田中の2016年の3部のA新規／既存が以下の値だったとします。
>　　　7月　（2016年7月時点の田中の個人シート）
>A新規 200
>A既存 300
>そうすると2017年になって、上記の値を前年として表示する場合は、500を表示すべきと考えますがあってますか。
>7月　（2017年7月時点の田中の個人シート）
>A前年 500
→年度が替わった際も新規、既存そのままの数字を前年に表示させたいです。
前年に新規、既存があったかを分けたい為です。
>
>つまり、前年の集計条件は、以下のようになると考えますが、あってますか。
>（支店名、課名、個人名、月は一致している前提とすると）
>A既存の実績 G列の値が３　かつ H列の値が空白
>A新規の実績 G列の値が３　かつ H列の値が１

>A既存の前年 G列の値が３（H列は参照しない）・・・・・ここが確認したい箇所です。
→H列を上記と同じように参照したいです。

お礼日時：2017/06/02 11:52

No.25 回答者： tatsu99 回答日時： 2017/06/02 09:03

No24で、誤りがありました。

３）前年を12か月分設定したい場合の作業
⑫2017年3月指定 前年指定・・・・誤
⑫2017年6月指定 前年指定・・・・正
　　　　^^^
以上、訂正します。

尚、【実績】売上明細データ(yyyymm)のyyyyですが、これは会計年度ではなく、格納しているデータの年です。
例えば、2017年３月は会計年度は2016年になりますが、2017年3月1日から2017年3月31日までのデータを格納したブック名は、【実績】売上明細データ(201703)になります。この認識であってますか。
【実績】売上明細データ(201703)には、他の月のデータはあってもなくても構いません。
運用上は、2016年4月1日～2017年3月31日のデータを格納すると想定しています。
マクロは要求するのは、yyyy年mm月が指定された場合、
①【実績】売上明細データ(yyyymm)のブックが存在すること
②そのブックの中にyyyy年mm月1日～yyyy年mm月末日のデータが存在すること
以上の2点です。
（但し、当然ですが②は現在の年月が指定された年月と同じなら末日までのデータはなくても構いません）

この回答へのお礼

度々恐縮です。
以下、記載いたします。

>No24で、誤りがありました。
> ３）前年を12か月分設定したい場合の作業
>⑫2017年3月指定 前年指定・・・・誤
>⑫2017年6月指定 前年指定・・・・正
>　　　　^^^
>以上、訂正します。
→ご丁寧にありがとうございます。承知いたしました。

>尚、【実績】売上明細データ(yyyymm)のyyyyですが、これは会計年度ではなく、格納しているデータの年です。
>例えば、2017年３月は会計年度は2016年になりますが、2017年3月1日から2017年3月31日までのデータを格納したブック名は、【実績】売上明細データ(201703)になります。この認識であってますか。
→はい、合っています。

>【実績】売上明細データ(201703)には、他の月のデータはあってもなくても構いません。
>運用上は、2016年4月1日～2017年3月31日のデータを格納すると想定しています。
→おっしゃる通りです。

>マクロは要求するのは、yyyy年mm月が指定された場合、
>①【実績】売上明細データ(yyyymm)のブックが存在すること
>②そのブックの中にyyyy年mm月1日～yyyy年mm月末日のデータが存在すること
>以上の2点です。
>（但し、当然ですが②は現在の年月が指定された年月と同じなら末日までのデータはなくても構いません）
→承知いたしました。

お礼日時：2017/06/02 09:22

No.24 回答者： tatsu99 回答日時： 2017/06/02 07:04

すみません。

２０１７年度は2017年7月から2018年６月迄と思っておりました。
そうではなくて、2017年４月から2018年３月が2017年度ですね。
画面に表示上、7月から翌年6月までを表示しているだけであり、データは4月から翌年３月までを保持している、と理解しました。

そうすると以下のようにしては、いかがでしょうか。
１）データをあなたが提案された通り、「2016年実績」と「2017年実績」に分ける。
2016年実績は、2016年4月1日から翌年3月31日のデータを格納
2017年実績は、2017年4月1日から翌年3月31日のデータを格納とする。
（従って前年Pのピボットは持たない。本年Pのみもつ）

２）マクロ実行時は、以下の項目を指定する。
①年月
②実績又は前年
マクロは、指定された年月の【実績】売上明細データ(yyyymm)を参照する。
例　2016年4月なら、【実績】売上明細データ(201604)を参照
マクロは、指定された月に該当するフィールドへ設定する。
実績が指定された場合は、実績の列へ集計する。
前年が指定された場合は、前年の列へ集計する。
（No23で述べた12か月ぶんの指定はサポートしない）

３）前年を12か月分設定したい場合の作業
2017年７月になった時、2016年度の実績を7月から翌年６月に設定する場合は、以下の指定でマクロをを12回実行する。
①2016年7月指定 前年指定　
②2016年8月指定 前年指定　
・・・
⑫2017年3月指定 前年指定

４）以降の指定
前年を12か月分設定した後は、実績指定でマクロをその月毎に（複数回、必要なぶん）実行する。

５）マクロ実行時の指定方法
添付図のように、マクロのあるブックの”管理”シートに、実行時の条件を指定して、実行します。
A2:集計年
B2:集計月
C2:集計項目（実績又は前年）
D2:【実績】売上明細データ(yyyymm)を格納したフォルダ名
（このフォルダの呼び名は実績フォルダであってますか。正しい呼び名を教えてください）
E2:支店・課ごとの個人データを格納したフォルダ名
（このフォルダの呼び名は計画フォルダであってますか。正しい呼び名を教えてください）

この回答へのお礼

再度、ご教授頂きましてありがとうございます。
何度もお手数をおかけして申し訳ありません。。。
以下、記載いたします。

>すみません。
> ２０１７年度は2017年7月から2018年６月迄と思っておりました。
>そうではなくて、2017年４月から2018年３月が2017年度ですね。
>画面に表示上、7月から翌年6月までを表示しているだけであり、データは4月から翌年３月までを保持している、と理解しました。
→説明不足で申し訳ありません。仰る通りです。データ自体は2016.4～2017.3まで分です。

>そうすると以下のようにしては、いかがでしょうか。
> １）データをあなたが提案された通り、「2016年実績」と「2017年実績」に分ける。
※以下、省略させて頂きます。
→ご提案ありがとうございます。そちらで問題ございません。
ちなみに5）とかかわることだと認識しておりますが5）ではフォルダだけの指定ですが、実績、前年ともにフォルダの中に入っているデータのブックには1部、2部、3部のピボッド以外のシートもありますがそれは問題ございませんでしょうか？(1部、3部の作業シート、ピボッドの元データがそれぞれ1部、2部、3部とあります)的外れな質問でしたら申し訳ありません。

>２）マクロ実行時は、以下の項目を指定する。
※以下、省略させて頂きます。
>３）前年を12か月分設定したい場合の作業
※以下、省略させて頂きます。
→2）と3）で、前年の数値を集計する場合、添付図の管理でマクロを実行する際、「指定された年月の【実績】売上明細データ(yyyymm)」の部分を都度
手修正するという認識でよろしいでしょうか？

> ４）以降の指定
>前年を12か月分設定した後は、実績指定でマクロをその月毎に（複数回、必要なぶん）実行する。
→承知いたしました。

>５）マクロ実行時の指定方法
※以下、省略させて頂きます。
→承知いたしました。D2、E2はそれぞれ実績フォルダ、計画フォルダを作成しましたのでご教示頂いたフォルダ名で
問題ございません。

お礼日時：2017/06/02 09:18

No.23 回答者： tatsu99 回答日時： 2017/06/01 12:52

すみません。

No22で、データを「2016年実績」と「2017年実績」2つのブックに分けないでください。
と回答しましたが、少し、考えさせてください。
というのは、前年の設定に関する件があるためです。

前提条件として、
１）前年は、年度切り替え時、新規シートを用意した時に、7月前年～6月前年の１２か月ぶんを一気に値を設定したい。
２）前年は、一度、設定すれば、原則として、更新しない。

とうことであれば、データを「2016年実績」と「2017年実績」にわけた方がやりやすくなります。
その場合のマクロ実行時の指定項目は、
①集計項目は実績か前年のどちらかを指定
②集計項目で実績が指定された場合は、年月を指定。
②集計項目で前年が指定された場合は、12か月分か１か月分かを指定。
　12か月分の指定なら、7月から6月のフィールドに値を設定（１つのブック内に１年分のデータがあること）
　１か月分の指定なら、指定月のフィールドに値を設定
のようになるかと思います。
上記のようにすることは可能です。

この回答へのお礼

度々、ご提示いただきありがとうございます。
以下、回答いたします。

> 前提条件として、
>１）前年は、年度切り替え時、新規シートを用意した時に、7月前年～6月前年の１２か月ぶんを一気に値を設定したい。
>２）前年は、一度、設定すれば、原則として、更新しない。
→1）ですが、申し訳ありません、理解不足な為再度ご教授願います。
　年度切り替え時。。という部分なのですが、現状前年データには2016.7月から2017.6月までのデータがあるとします。
　2016.7月から2016.12月までと2017.1月から2017.6月までを切り分け。2017.1月以降のシートを作成した場合ということでしょうか？

>とうことであれば、データを「2016年実績」と「2017年実績」にわけた方がやりやすくなります。
>その場合のマクロ実行時の指定項目は、
>①集計項目は実績か前年のどちらかを指定
>②集計項目で実績が指定された場合は、年月を指定。
→実績は2017年実績に入っているデータを参照するので、中身のデータは2017.2018年が入り混じることになります。(2017.7～2018.6分)
その場合、ブックorシートを分ける必要があるということでしょうか？
>②集計項目で前年が指定された場合は、12か月分か１か月分かを指定。
　 >12か月分の指定なら、7月から6月のフィールドに値を設定（１つのブック内に１年分のデータがあること）
　 >１か月分の指定なら、指定月のフィールドに値を設定
>のようになるかと思います。
>上記のようにすることは可能です。
→すみません、前年を指定した場合の12か月分か1か月分かを指定。。という部分ですが違いについてもう少しご説明頂けませんでしょうか？
　ちなみに、前年も2016.2017が入り混じっていることになります。(2016.7～2017.6分)

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