【マクロ改修希望】同一フォルダ内の複数ブックの複数シートの特定セルの集計を出したい

tatsu99様

お世話になっております。

先日、事前に質問させていただきました件ですが
まだ条件確定しておらず、また改めて提示させていただきます。
申し訳ありません。
↓
https://oshiete.goo.ne.jp/qa/11227677.html

本質問では、以前ご教授頂きましたマクロの修正をご教授頂きたく
投稿させていただきました。
↓
同一フォルダ内の複数ブックの複数シートの特定セルの集計を出したい
https://oshiete.goo.ne.jp/qa/10618547.html

これまで元データから「個人集計」マクロにて個人ファイルへデータを一旦集計→「全体集計」マクロで支店・課ごとのデータを集計しておりましたが
来期より個人集計はせず、元データから直接全体集計へ条件ごとに項目を集計することになりました。

データ集計先のフォーマットが変更になりましたので、共有させて頂きます。
下記へ、ブックを格納させて頂きましたのでご確認頂けますと幸いです。
↓
http://xfs.jp/FdG1L
w4c218we

①全体(変更後)シートが新しくデータ集計先のフォーマットとなります。
また、D列に新たに項目列を追加いたしました。

②参照元データですが、以前は1部～3部でシートを分けておりましたが
来期より1つのシートで管理することになりました。
格納ブックの「参照元データ(変更後)」へ元データのサンプルを作成しております。
参照先の月、分類区分コードに合致した支店&所属へデータを集計したいです。

何卒、ご教授頂けますと幸いです。
よろしくお願いいたします。

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

• tatsu99様
  
  お世話になっております。
  お忙しい中、マクロ修正頂きましてありがとうございます。
  また、検証実施のご報告が遅くなり、大変申し訳ありません。
  
  ご提示いただきました注意点に則り、分類区分コード、支店の追加/削除につきまして検証いたしました。問題なく、検証完了いたしました。本当にありがとうございました。
  
  また、恐れ入りますが追加で質問させてください。

  No.21の回答に寄せられた補足コメントです。 補足日時：2019/08/23 10:46

• 追加での質問です。
  
  今後、行数が変動(支店数増減、分類コードの増減)する場合については
  ご教授頂いたのですが、今後列数の変動(1か月のブロック項目の増減、抽出先の項目の並びの変動)があった場合はどの部分を修正すればよろしいでしょうか？
  1か月のブロックの列数が変動した場合は下記を修正するというのはなんとなく理解しているのですが。。。
  他に修正が必要な箇所がございましたら、ご教授頂けますと幸いです。
  
  ★1か月のブロックの列数が変動した場合★
  指定月から指定月対応のカラム位置（計画数量）を計算する
  
  ・276行目　9月=186→9月=210へ修正
  ・279行目　12→14へ修正
  ・282行目　12→14へ修正
  
  また、まだ条件等未確定の案件修正に関しては追って質問させて頂ければ幸いです。
  ↓
  https://oshiete.goo.ne.jp/qa/11227677.html

  No.19の回答に寄せられた補足コメントです。 補足日時：2019/08/23 10:51

• サンプル画像添付いたします。
  よろしくお願いいたします。

  
  No.24の回答に寄せられた補足コメントです。 補足日時：2019/08/26 13:25

No.9 回答者： tatsu99 回答日時： 2019/08/16 19:47

返信ありがとうございました。

あなたの回答をまとめると、以下のようになりますが、間違いないでしょうか。

前年　売上集計対象コード　82、86、85、87、88の場合
元データ　設定先（加算先）
H列 →　　I列（実績・前年数量）
I列 →　　I列（実績・前年数量）
J列 →　　N列（実績・前年粗利）

前年　売上集計対象コード　82、86、85、87、88以外の場合
元データ　設定先（加算先）
H列 →　　I列（実績・前年数量）
I列 →　　集計しない
J列 →　　N列（実績・前年粗利）

当年　売上集計対象コード　82、86、85、87、88の場合
元データ　設定先（加算先）
H列 →　　I列（実績・前年数量）
I列 →　　I列（実績・前年数量）
J列 →　　O列（実績・当年粗利）

当年　売上集計対象コード　82、86、85、87、88以外の場合
元データ　設定先（加算先）
H列 →　　I列（実績・前年数量）
I列 →　　集計しない
J列 →　　O列（実績・当年粗利）

-----------------------------------------------------------
質問１：
合計シートの集計対象の列は、10月分については
I列（実績・前年数量）、N列（実績・前年粗利）、O列（実績・当年粗利）の３列のみで間違いないですか。
（なぜ、J列 実績・当年数量になにも設定されないのかがよくわかりません）

質問２：元データのH列、I列については、元データが当年であっても、前年であっても、何故同じI列（実績・前年数量）に加算される
のかがよくわかりませんが、そのような仕様で作成して間違いないでしょうか。

この回答へのお礼

tatsu99様

こんばんわ。

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

>あなたの回答をまとめると、以下のようになりますが、間違いないでしょうか。
→前年実績集計に関してはこちらで問題ございません。

>質問１：
> 合計シートの集計対象の列は、10月分については
>I列（実績・前年数量）、N列（実績・前年粗利）、O列（実績・当年粗利）の３列のみで間違いないですか。
> （なぜ、J列 実績・当年数量になにも設定されないのかがよくわかりません）
→ご質問頂きましてありがとうございます。
また、tatsu99様に質問頂くまで前年実績のことのみしか提示していないことに気づきました。
大変失礼いたしました。
ご提示頂きました当年実績集計につきまして、修正箇所を下記記載させていただきました。
恐れ入りますが、こちらで修正頂けますと幸いです。

> 当年　売上集計対象コード　82、86、85、87、88の場合
> 元データ　設定先（加算先）
>H列 →　　I列（実績・前年数量）
→設定先：J列(実績：当年数量)
>I列 →　　I列（実績・前年数量）
→設定先：J列(実績：当年数量)
>J列 →　　O列（実績・当年粗利）

> 当年　売上集計対象コード　82、86、85、87、88以外の場合
> 元データ　設定先（加算先）
>H列 →　　I列（実績・前年数量）
→設定先：J列(実績：当年数量)
>I列 →　　集計しない
>J列 →　　O列（実績・当年粗利）


> 質問２：元データのH列、I列については、元データが当年であっても、前年であっても、何故同じI列（実績・前年数量）に加算される
> のかがよくわかりませんが、そのような仕様で作成して間違いないでしょうか。
→説明不足で申し訳ありません。
設定先の項目に「数量」と表示しておりますが、敢えて売上という項目を設けず、「集計対象コード」82、86、85、87、88の場合は売上で集計
それ以外は数量で集計をして表示するようなフォーマットになっている為、ご心配をおかけしておりますがそのようなお願いをさせていただいております。

何度もお手数をおかけして大変申し訳ございませんが
何卒よろしくお願いいたします。

お礼日時：2019/08/16 21:13

No.8 回答者： tatsu99 回答日時： 2019/08/16 13:33

追加質問です。

元データの下記項目の設定先ですが、全体シートの１０月へ設定する場合、どの列になりますか。
??の個所がわかりません。ご提示ください。又、元データのJ列は、これであってますか？
前年の実績ファイルの場合
H列（合計／数量）　　　→？？

I列（合計／売上金額）　→？？

J列（合計／粗利益）　　→N列（前年粗利）

当年の実績ファイルの場合
H列（合計／数量）　　　→？？

I列（合計／売上金額）　→？？

J列（合計／粗利益）　　→O列（当年粗利）

この回答へのお礼

tatsu99様

こんにちわ。

追加でのご質問、以下回答させて頂きます。

>元データの下記項目の設定先ですが、全体シートの１０月へ設定する場合、どの列になりますか。
>??の個所がわかりません。ご提示ください。又、元データのJ列は、これであってますか？
→大変申し訳ありません。肝心なことをお伝えし忘れておりました。
前年、当年ともに元データ：I列売上金額については下記「分類区分コード」のみ設定先I列へ集計。
それ以外のコードにつきましては元データ：H列数量を設定先I列へ集計ができるようにお願いできれば幸いです。

●売上集計対象コード
82、86、85、87、88

>前年の実績ファイルの場合
>H列（合計／数量）　　　→？？
→設定先：I列
>I列（合計／売上金額）　→？？
→設定先：I列(売上集計対象コード含め)
>J列（合計／粗利益）　　→N列（前年粗利）

>当年の実績ファイルの場合
>H列（合計／数量）　　　→？？
→設定先：I列
>I列（合計／売上金額）　→？？
→設定先：I列(売上集計対象コード含め)
>J列（合計／粗利益）　　→O列（当年粗利）

何度も御手数をおかけして申し訳ございませんが
よろしくお願いいたします。

お礼日時：2019/08/16 15:04

No.7 回答者： tatsu99 回答日時： 2019/08/16 01:01

追加の質問です。

集計元データの集計対象のシート名は何でしょうか。（前回の"1部本年P"、"2部本年P"、"3部本年P"に相当）
集計先のシート名はなんでしょうか。前回の”全体”に相当します。

この回答へのお礼

tatsu99様

おはようございます。
ご返信が遅くなり申し訳ありません。

以下、回答させて頂きます。

>追加の質問です。
>集計元データの集計対象のシート名は何でしょうか。（前回の"1部本年P"、"2部本年P"、"3部本年P"に相当）
→「実績」です。
>集計先のシート名はなんでしょうか。前回の”全体”に相当します。
→前回同様、「全体」です。

よろしくお願いいたします。

お礼日時：2019/08/16 09:07

No.6 回答者： tatsu99 回答日時： 2019/08/15 19:29

ケース３で行いますが、その前提で、いくつか追記の質問です。

１．元データのファイル名は、添付の画像のように、管理シートから指定させる（赤線で囲んだセル）のと、
直接マクロにConst ZenFile as string = "d:\zennen\売上明細データ(2018).xlsx"
のように記述し、実行時に、あなたがZenFileの内容を書き換える方法があります。
どちらにしますか。
２．データの集計は区分CD（＝分類区分コード）のみ参照します。分類名は参照しません。
元データに分類名が、X-999のようなでたらめなものがあっても、区分CDが正しければOKとしますがよろしいでしょうか。
尚、このコードの名称は「全体（変更後）」シートでは”区分CD”が使われ、
「参照元データ（変更後）」シートでは、"分類区分コード"が使われています。
どちらも正しい名称でしょうか。できればどちらかに統一していただきたく。
３．区分CDの並びが今後変動する可能性のあること、区分CDの数が増えて、行数が増える可能性があること
については、了解しました。ただし、一切マクロを修正なしに、対応するようにするとかなり複雑になるので、
マクロの修正をすれば、区分CDの追加にも対応できるようにします。
その場合は、どのように対応するかは、マクロリリース時に説明します。それで良いでしょうか。
４．区分CDの並びは、最初のブロック（本部）の並びを基準とします。
以降のブロックは、最初のブロック（本部）の並びと同じであるという前提で、データ設定をおこないます。
たとえ、以降のブロックの並びが、おかしくても、エラーにせず、最初のブロックと同じように並んでいる前提で
データを設定します。それでよろしいでしょうか。

この回答へのお礼

tatsu99様

おはようございます。
ご返信が遅くなり申し訳ありません。

以下、回答させて頂きます。

>ケース３で行いますが、その前提で、いくつか追記の質問です。
>１．元データのファイル名は、添付の画像のように、管理シートから指定させる（赤線で囲んだセル）のと、
>直接マクロにConst ZenFile as string = "d:\zennen\売上明細データ(2018).xlsx"
>のように記述し、実行時に、あなたがZenFileの内容を書き換える方法があります。
>どちらにしますか。
→管理シートから指定させる方法でお願いいたします。

>２．データの集計は区分CD（＝分類区分コード）のみ参照します。分類名は参照しません。
>元データに分類名が、X-999のようなでたらめなものがあっても、区分CDが正しければOKとしますがよろしいでしょうか。
→はい、、そちらで問題ございません。

>尚、このコードの名称は「全体（変更後）」シートでは”区分CD”が使われ、
>「参照元データ（変更後）」シートでは、"分類区分コード"が使われています。
>どちらも正しい名称でしょうか。できればどちらかに統一していただきたく。
→どちらも正しい名称ですが、元データの名称「分類区分コード」に統一頂いて問題ございません。

>３．区分CDの並びが今後変動する可能性のあること、区分CDの数が増えて、行数が増える可能性があること
>については、了解しました。ただし、一切マクロを修正なしに、対応するようにするとかなり複雑になるので、
>マクロの修正をすれば、区分CDの追加にも対応できるようにします。
>その場合は、どのように対応するかは、マクロリリース時に説明します。それで良いでしょうか。
→承知いたしました。恐れ入りますが、よろしくお願いいたします。

>４．区分CDの並びは、最初のブロック（本部）の並びを基準とします。
>以降のブロックは、最初のブロック（本部）の並びと同じであるという前提で、データ設定をおこないます。
>たとえ、以降のブロックの並びが、おかしくても、エラーにせず、最初のブロックと同じように並んでいる前提で
>データを設定します。それでよろしいでしょうか。
→はい、問題ございません。

諸々、お手数をおかけいたしますが何卒よろしくお願いいたします。

お礼日時：2019/08/16 09:08

No.5 回答者： tatsu99 回答日時： 2019/08/15 11:58

とりあえず、現時点での質問です。

追加の質問があれば、逐次、追記します。
質問１
マクロ実行の手順は、現行の場合、以下のようになっています。
１．個人集計マクロで
①集計月、②集計項目（実績又は前年）を指定し、個人データ作成
２．全体集計マクロで、支店・課毎の集計

今回は、以下のようになるかと思います。
ケース1．新マクロで下記を指定し、指定された個所（月、前年／当年）の集計を行う。
①集計月、②集計項目（実績又は前年）

ケース２．新マクロで下記を指定し、指定された月の集計を行う。
①集計月のみ指定、データは前年用、当年用の２つを一度に処理する。

ケース３．新マクロを実行。
すべての月を処理する。データは前年用、当年用の２つを処理する。

どのケースを想定されてますか。
私としては、ケース２、ケース３のどちらかのような気がします。

質問２
元データは新しいフォーマットに変わりますが、
前年用のデータも新しいフォーマットを作成すると理解して良いでしょうか。

質問３
区分CDと分類名についてです。
①区分CD＝10と分類名＝Aは、ペアになっていますが、
　区分CD＝20と分類名＝Aのような組み合わせもありなのでしょうか。
②全体シートの各支店は、
区分コードの並びは、10,20,25,30,40,・・省略・・,87,88
となっていますが、全ての支店で、同じ並びであるという前提で良いでしょうか。
（B支店のみ、70,71,88・・・のようになるということはない）
③全体シートの区分コードの並びは、今後、変えることも想定されていますか。

この回答へのお礼

tatsu99様

こんにちわ。
いつもお世話になっております。

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

>質問１
>今回は、以下のようになるかと思います。
>どのケースを想定されてますか。
>私としては、ケース２、ケース３のどちらかのような気がします。
→ご提示いただきましてありがとうございます。
　 ケース3でお願いできればと考えております。

>質問２
>元データは新しいフォーマットに変わりますが、
>前年用のデータも新しいフォーマットを作成すると理解して良いでしょうか。
→はい、仰る通りです。

>質問３
>区分CDと分類名についてです。
>①区分CD＝10と分類名＝Aは、ペアになっていますが、
>区分CD＝20と分類名＝Aのような組み合わせもありなのでしょうか。
→私の説明が分かりづらく申し訳ありません。
　 区分CDと分類名の組合せで集計というわけではなく、区分CDで集計が可能であれば支障はございません。

>②全体シートの各支店は、
>区分コードの並びは、10,20,25,30,40,・・省略・・,87,88
>となっていますが、全ての支店で、同じ並びであるという前提で良いでしょうか。
>(B支店のみ、70,71,88・・・のようになるということはない）
→はい、仰る通り並びは各支店同一並びです。

>③全体シートの区分コードの並びは、今後、変えることも想定されていますか。
→現状はありませんが、今後並びが変更になることも考えられます。
　 また、区分が増える可能性もある為、行数が変動する場合も想定されます。

お忙しい中、お手数をおかけいたしますが
何卒、よろしくお願いいたします。

お礼日時：2019/08/15 14:07

No.4 回答者： tatsu99 回答日時： 2019/08/14 11:30

資料がそろいましたので、これから検討します。

この回答へのお礼

お忙しい中、お手数をおかけいたしますが
何卒よろしくお願いいたします。

お礼日時：2019/08/14 11:31

No.3 回答者： tatsu99 回答日時： 2019/08/14 11:19

>先程、貼付いたしました。

そのURLを提示してください。

この回答へのお礼

失礼いたしました。
大変失礼いたしました。
↓
https://ideone.com/tcVi3x

お礼日時：2019/08/14 11:21

No.2 回答者： tatsu99 回答日時： 2019/08/14 10:38

追伸、マクロをシートに貼り付けると、こちらで参照しずらいので、

https://ideone.com/
に貼り付けていただけませんでしょうか。（言語はTextを選択してください）
①全体集計マクロ
②個人集計マクロ
についてお願いします。

この回答へのお礼

先程、貼付いたしました。

お礼日時：2019/08/14 11:16

No.1 回答者： tatsu99 回答日時： 2019/08/14 10:33

tatsu99です。

現在、確認中ですが、提示された
②全体集計.xlsxの①全体集計マクロのシートは空の状態です。
このマクロの内容を提示してください。

この回答へのお礼

おはようございます。

ご返信頂きましてありがとうございます。

>②全体集計.xlsxの①全体集計マクロのシートは空の状態です。
>このマクロの内容を提示してください。
→シートに記載したのですが、うまく貼付ができなかったようで
　大変失礼いたしました。№２で提示頂きましたところへ貼付させて頂きます。

よろしくお願いいたします。

お礼日時：2019/08/14 11:09