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

以前、複数シートを集計したい(別シートへ集計させたい)という質問をさせていただき
ご回答いただきました。
https://oshiete.goo.ne.jp/qa/9705836.html

これに似たような質問なのですが、今度は複数シートの集計だけでなく、同一フォルダにある複数のブックの複数シート(同じフォーマット)の
特定のセルを集計して、「合計」というシートに集計した数値を表示させるマクロを作成したいです。
※ブックは開かず閉じたまま実行したいです。

複数ブックはそれぞれ支店＆課名になっており、シート名はその支店の担当者名になっております。
全ブックの個人シートの特定セルを合計すると「合計」というシートの各支店、各課の前年、計画、実績に数値を合計させるといった
ことができたらなと思っております。

個人別シートのそれぞれA、B、Cの黄色のセルを合計シートの月別の前年、計画、実績へ表示させたいです。

何卒、ご教授願います。

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

• 合計シートサンプル画像。

  
    補足日時：2017/05/18 13:06

• ※回答続きです。
  >４）集計対象ブック内の各シートのC2、C3の内容に従い、合計シートの支店名、課名が合致する箇所へ合計する。従って、１つのブック内で、支店名、課名が異なっていても、それに従い、集計する。
  例　仙台・営業.xlsxのブックがあった時
  そのブックの1枚目のシートがC2=仙台支店 C3=営業課、であれば、仙台支店の営業課へ合計する。
  そのブックの2枚目のシートがC2=大阪支店 C3=第一課、であれば、大阪支店の第一課へ合計する。
  →承知しました。

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

• 

  ※補足コメント続きです。
  >５）集計対象ブックの○○支店、○○課のデータと合計シートの○○支店、○○課の関係は以下の通り
  各月ごとに、
  集計対象ブックの1番目の合計（D列の１番目に"合計"が設定されている行）の値（前年、計画、実績）を合計シートの①の前年、計画、実績へ加算する。
  集計対象ブックの2番目の合計（D列の２番目に"合計"が設定されている行）の値（前年、計画、実績）を合計シートの②の前年、計画、実績へ加算する。
  集計対象ブックの3番目の合計（D列の３番目に"合計"が設定されている行）の値（前年、計画、実績）を合計シートの③の前年、計画、実績へ加算する。
  →仰る通りです。
  
  また、こちらから追加で質問です。
  マクロ実行は各ブックそれぞれをひとつづつ更新する必要があるということでしょうか？
  私の質問が説明不足、私の理解不足でしたら申し訳ありません。
  何卒、よろしくお願いいたします。

    補足日時：2017/05/24 11:57

• 

  また、こちらからも追加で再度質問させてください。
  集計対象すべてのブックに各シートを集計するにあたる元データが別シートとしてあります。
  ※他のブックにあるピボッドのシートをコピーしたものです。それをSUMIFS関数で集計してます。
  このピボッドを各ブック開かずに更新するようなマクロも追加したいのですが
  そういったことも可能でしょうか？
  
  度々恐縮ではございますが、何卒ご教授頂けますと幸いです。
  
  よろしくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2017/05/24 13:23

• 補足要求③の画像添付します。

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

• 補足要求④の画像添付します。

  
    補足日時：2017/05/25 15:35

• 申し訳ありません。
  先ほどのお礼に書かせていただいた内容が質問の返答になっていなかった点がありました。
  大変失礼いたしました。
  
  ＞この事象で考えられることは以下のどちらかです。
  ＞①テストデータとして合計を作ってしまった。
  ＞②集計対象のブックには、集計対象外のシートが（今回の合計等）があるのだが、その旨の説明が漏＞れていた。
  →結果から申し上げますと、①②どちらにも該当いたします。
  マクロのブックに合計シートがありましたし、集計対象ブックには集計対象シート以外も含まれておりました。失礼いたしました。追加での質問を先ほど記載いたしましたので恐れ入りますが、ご回答いただけますと幸いです。

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

• ※個人シート画像添付します。

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

• ※それ以外シートの画像添付します。

  
    補足日時：2017/05/26 13:09

• 

  解決したといいながら申し訳ありません。
  集計用マクロの合計、それ以外シートは正しく集計されて表示されているのですが
  その集計元の各支店別の複数ブックの中にある「合計シート」、「それ以外シート」には処理完了とはなりますが＃REFエラーや何も反映されず空白になってしまっています。
  原因は何が考えられますでしょうか？
  本当に何度も申し訳ありませんが、ご教授願います。

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

No.9 回答者： tatsu99 回答日時： 2017/05/25 15:54

仕様が確定しましたので、以下の仕様で作業を進めます。

１）支店計の行は、マクロで合計しない。
２）全体合計は、①②③のみ合計する。（計の行は合計しない）
３）○○支店の□□営業所は、①②③のみ合計する。（計の行は合計しない）（当初の仕様の部分）
上記で問題があれば、補足してください。（なければ補足不要です）

この回答へのお礼

何度もお手数をおかけして申し訳ありません。
上記で問題ございません。
何卒、よろしくお願いいたします。

お礼日時：2017/05/25 15:59

No.8 回答者： tatsu99 回答日時： 2017/05/25 15:39

No7の質問は取り消します。

マクロで合計算出を追加するのは、
全体合計①
全体合計②
全体合計③
でよろしいでしょうか。

この回答へのお礼

申し訳ありません。
行き違いで№8に回答してしまいました。。
上記で問題ございません。

お礼日時：2017/05/25 15:46

No.7 回答者： tatsu99 回答日時： 2017/05/25 15:14

念のため確認ですが、マクロで、算出するのは、

一番下の行の「全体合計」のみで良いでしょうか。
それとも、支店合計（29行目、42行目、55行目、76行目）も、マクロで算出するのでしょうか。

この回答へのお礼

再度、ご確認ありがとうございます。
支店合計もマクロで算出できればなお助かりますが。。
それは可能でしょうか？
もしくは各支店の課ごとの計、支店合計、全体合計のたて計とQ合計、上期下期、年間のよこ計は数式が入っているので
そこを活かすということも可能でしたら①②③の内訳のみ集計するマクロでも問題ございません。

お礼日時：2017/05/25 15:44

No.6 回答者： tatsu99 回答日時： 2017/05/25 14:24

補足要求①②③④です。

>その行がある為、「不正な行」というエラーが出てしまうのではないか？と思ったのですが。
以下のどのメッセージですか。（補足要求①）
合計シート 支店名の行番号不正
合計シート 課名の行番号不正

現行では、合計シートのＢ列が空白でない行（＝課名がある行）を①としています。②が①＋1の行、③が②+１の行です。
課名が出現可能な行は、５，９、１３なので、４の余りが１の行としています。（支店名の出現可能な行も同様です）

>①合計シートですが、B列の各課、営業所の合計が支店合計になるように「支店合計」という行がありますがそれをお伝えしておりませんでした。
申し訳ありません。
>その行がある為、「不正な行」というエラーが出てしまうのではないか？と思ったのですが。。
>ちなみに支店合計は29行目、42行目、55行目、76行目にあります。

１つの支店（○○支店）は、必ず１つの支店合計の行をもつということでしょうか。（補足要求②）
支店合計は29行目、42行目、55行目、76行目ということですが、
どれか、１つ（例えば２９行目）で、よいので、サンプル画像を提示してしただけますか。（補足要求③）

>②一番下に全体合計として各支店の①②③をそれぞれ合計した数値を表示させるセルがあります。
>この全体合計を表示する内容を追加することは可能でしょうか？

この行の画像を提示していただけますでしょうか。（補足要求④）

この回答へのお礼

再度、補足要求ありがとうございます。
下記、回答いたします。

補足要求①
合計シート 支店名の行番号不正とエラーがでます。

補足要求②
仰る通り、支店ごとに支店合計の行があります。

補足要求③④
画像添付します。

お礼日時：2017/05/25 15:25

No.5 回答者： tatsu99 回答日時： 2017/05/24 17:01

下記ＵＲＬに記述しました。

http://climbi.com/b/10215/0

Const Folder As String = "d:\goo\excel\goo191" '支店＆課名ブックの格納フォルダ
は、集計対象となるブックを格納するフォルダです。あなたの環境に合わせて、適切に設定してください。
不明点があれば補足して下さい。

この回答へのお礼

昨日はご教授頂きましてありがとうございます。
ご教授頂いたマクロを実行してみたところエラーが出てしまい、原因は私がお伝えし漏れていた下記の件が原因ではないかと思い、再度ご教授頂ければ幸いです。

①合計シートですが、B列の各課、営業所の合計が支店合計になるように「支店合計」という行がありますがそれをお伝えしておりませんでした。
申し訳ありません。その行がある為、「不正な行」というエラーが出てしまうのではないか？と思ったのですが。。
ちなみに支店合計は29行目、42行目、55行目、76行目にあります。
②一番下に全体合計として各支店の①②③をそれぞれ合計した数値を表示させるセルがあります。
この全体合計を表示する内容を追加することは可能でしょうか？

お礼日時：2017/05/25 13:26

No.4 回答者： tatsu99 回答日時： 2017/05/24 14:58

>>「ピボッドを各ブック開かずに更新するようなマクロ」とは、

>>各ブックを人間が予め開いておくのではなく、今回のように、マクロで、一つずつ、開いて処理するマクロ
>>という意味でしょうか。
>>それであれば可能かと思いますが、実際には、業務の要件を確認してからになるかと思います。

>→はい、おっしゃる通りマクロで処理できたらと思っております。・・・・・①
>　追加するにあたり、情報が必要なようでしたらご質問頂ければ幸いです。

念のため確認ですが、今回作成するマクロとは、別に、①のマクロを作成するという意味で良いでしょうか。
今回作成するマクロに①の機能を組み込んで実装するということではありません。
従って、①のマクロについては、今回の質問とは別の質問として、あなたが投稿され、それを回答者が回答することになります。

この回答へのお礼

>→はい、おっしゃる通りマクロで処理できたらと思っております。・・・・・①
>　追加するにあたり、情報が必要なようでしたらご質問頂ければ幸いです。

念のため確認ですが、今回作成するマクロとは、別に、①のマクロを作成するという意味で良いでしょうか。
今回作成するマクロに①の機能を組み込んで実装するということではありません。
従って、①のマクロについては、今回の質問とは別の質問として、あなたが投稿され、それを回答者が回答することになります。
→承知いたしました。また、大変失礼いたしました。
　また、別途質問を投稿させて頂きます。

お礼日時：2017/05/24 15:14

No.3 回答者： tatsu99 回答日時： 2017/05/24 14:28

>ただ、大変申し訳ありません、そもそもわたしが提示した内容が違っておりましたので訂正させてください。

>　集計対象ブックの各シートも合計シートもすべて前年、計画、実績、伸長率、達成率の5列。
>　そしてクォーター1、2、3、4、上期、下期、年間合計となっております。
>　7月から9月：D列からR列、1Q：S列からW列、10月から12月：X列からAL列、2Q：AM列からAQ列、上期：AR列からAV列
>　1月から3月：AW列からBK列、3Q：BL列からBP列、4月から6月：BQ列からCE列、4Q：CF列からCJ列、下期：CK列からCO列
>　年間：CP列からCT列

そうすると集計対象となる月（もしくは期間）は、
7月から翌年６月（１２件）
上期、下期（２件）
クオーター１，２，３，４（４件）
年間（１件）
の計１９件でしょうか。
列の並びは提示された通りです。
処理の順序は、７月、８月、９月、１Ｑ、10月、11月、・・・・下期、年間
のようになります。列の左側から順に右側へ処理します。



>また、こちらからも追加で再度質問させてください。
>集計対象すべてのブックに各シートを集計するにあたる元データが別シートとしてあります。
>※他のブックにあるピボッドのシートをコピーしたものです。それをSUMIFS関数で集計してます。
>このピボッドを各ブック開かずに更新するようなマクロも追加したいのですが
>そういったことも可能でしょうか？

「ピボッドを各ブック開かずに更新するようなマクロ」とは、
各ブックを人間が予め開いておくのではなく、今回のように、マクロで、一つずつ、開いて処理するマクロ
という意味でしょうか。
それであれば可能かと思いますが、実際には、業務の要件を確認してからになるかと思います。

この回答へのお礼

>>ただ、大変申し訳ありません、そもそもわたしが提示した内容が違っておりましたので訂正させてください。
>>　集計対象ブックの各シートも合計シートもすべて前年、計画、実績、伸長率、達成率の5列。
>>　そしてクォーター1、2、3、4、上期、下期、年間合計となっております。
>>　7月から9月：D列からR列、1Q：S列からW列、10月から12月：X列からAL列、2Q：AM列からAQ列、上期：AR列からAV列
>>　1月から3月：AW列からBK列、3Q：BL列からBP列、4月から6月：BQ列からCE列、4Q：CF列からCJ列、下期：CK列からCO列
>>　年間：CP列からCT列

>そうすると集計対象となる月（もしくは期間）は、
>7月から翌年６月（１２件）
>上期、下期（２件）
>クオーター１，２，３，４（４件）
>年間（１件）
>の計１９件でしょうか。
>列の並びは提示された通りです。
>処理の順序は、７月、８月、９月、１Ｑ、10月、11月、・・・・下期、年間>
のようになります。列の左側から順に右側へ処理します。
→はい、おっしゃる通り19件です。
　処理の手順は承知いたしました。

>>また、こちらからも追加で再度質問させてください。
>>集計対象すべてのブックに各シートを集計するにあたる元データが別シートとしてあります。
>>※他のブックにあるピボッドのシートをコピーしたものです。それをSUMIFS関数で集計してます。
>>このピボッドを各ブック開かずに更新するようなマクロも追加したいのですが
>>そういったことも可能でしょうか？

>「ピボッドを各ブック開かずに更新するようなマクロ」とは、
>各ブックを人間が予め開いておくのではなく、今回のように、マクロで、一つずつ、開いて処理するマクロ
>という意味でしょうか。
>それであれば可能かと思いますが、実際には、業務の要件を確認してからになるかと思います。
→はい、おっしゃる通りマクロで処理できたらと思っております。
　追加するにあたり、情報が必要なようでしたらご質問頂ければ幸いです。

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

お礼日時：2017/05/24 14:41

No.2 回答者： tatsu99 回答日時： 2017/05/24 12:16

>また、こちらから追加で質問です。

>マクロ実行は各ブックそれぞれをひとつづつ更新する必要があるということでしょうか？
各ブックの更新はしませんが、
①各ブックを１つオープンする。
②１つのシートについて、合計シートに加算を行う。・・・これを全シートについて行う。
③オプーンしたブックをクローズする。
上記①～③を集計対象の全てのブックについておこないます。
従って、マクロ実行は各ブックそれぞれをひとつづつ（更新はしないが）処理する必要があるということになります。

私が提示した、前提条件に関しては下記の１点のみの相違と理解しました。
集計対象となる月は7月から翌6月までと、7月から12月の列に上期、1月から6月の次の列に下期がある。というこですが、
これについて、以下の前提で良いでしょうか。
①上期、下期のフォーマットは、他の月と同じ（前年、計画、実績、伸長率、達成率の５列）
②上期、下期についても他の月と同じように処理して良い。（集計元の前年、計画、実績を合計シートの前年、計画、実績に加算）

この回答へのお礼

>>また、こちらから追加で質問です。
>>マクロ実行は各ブックそれぞれをひとつづつ更新する必要があるということでしょうか？
>各ブックの更新はしませんが、
>①各ブックを１つオープンする。
>②１つのシートについて、合計シートに加算を行う。・・・これを全シートについて行う。
>③オプーンしたブックをクローズする。
>上記①～③を集計対象の全てのブックについておこないます。
>従って、マクロ実行は各ブックそれぞれをひとつづつ（更新はしないが）処理する必要があるということになります。
→ご回答ありがとうございます。
　 ひとつづつ開かなくても全ブックをマクロで集計できるということですね？とても助かります。ありがとうございます。

>私が提示した、前提条件に関しては下記の１点のみの相違と理解しました。
>集計対象となる月は7月から翌6月までと、7月から12月の列に上期、1月から6月の次の列に下期がある。というこですが、
>これについて、以下の前提で良いでしょうか。
>①上期、下期のフォーマットは、他の月と同じ（前年、計画、実績、伸長率、達成率の５列）
>②上期、下期についても他の月と同じように処理して良い。（集計元の前年、計画、実績を合計シートの前年、計画、実績に加算）
→①②については仰る通りで問題ございません。
　ただ、大変申し訳ありません、そもそもわたしが提示した内容が違っておりましたので訂正させてください。
　集計対象ブックの各シートも合計シートもすべて前年、計画、実績、伸長率、達成率の5列。
　そしてクォーター1、2、3、4、上期、下期、年間合計となっております。
　7月から9月：D列からR列、1Q：S列からW列、10月から12月：X列からAL列、2Q：AM列からAQ列、上期：AR列からAV列
　1月から3月：AW列からBK列、3Q：BL列からBP列、4月から6月：BQ列からCE列、4Q：CF列からCJ列、下期：CK列からCO列
　年間：CP列からCT列

お礼日時：2017/05/24 13:21

No.1 回答者： tatsu99 回答日時： 2017/05/23 20:53

以下の前提でマクロを作成しますが、宜しいでしょうか。

１）合計シートは、作成したマクロのあるブック内に格納されていることが前提。
２）合計シートの集計対象となる月は、７月から翌年の６月までの１２か月であることが前提。（下期計、上期計等はない）
３）特定フォルダ内の拡張子がxlsxであるブックを全て集計対象とする。
　　拡張子がxlsmのブックは集計対象としない。
　　もし、拡張子がxlsxで集計対象外のブックがある場合は、そのブックを他に移動してからマクロを実行すること。
４）集計対象ブック内の各シートのC2、C3の内容に従い、合計シートの支店名、課名が合致する箇所へ合計する。
　　従って、１つのブック内で、支店名、課名が異なっていても、それに従い、集計する。
　例　仙台・営業.xlsxのブックがあった時
　そのブックの1枚目のシートがC2=仙台支店 C3=営業課、であれば、仙台支店の営業課へ合計する。
　そのブックの2枚目のシートがC2=大阪支店 C3=第一課、であれば、大阪支店の第一課へ合計する。
５）集計対象ブックの○○支店、○○課のデータと合計シートの○○支店、○○課の関係は以下の通り
　各月ごとに、
　集計対象ブックの1番目の合計（D列の１番目に"合計"が設定されている行）の値（前年、計画、実績）を合計シートの①の前年、計画、実績へ加算する。
　集計対象ブックの2番目の合計（D列の２番目に"合計"が設定されている行）の値（前年、計画、実績）を合計シートの②の前年、計画、実績へ加算する。
　集計対象ブックの3番目の合計（D列の３番目に"合計"が設定されている行）の値（前年、計画、実績）を合計シートの③の前年、計画、実績へ加算する。

この回答へのお礼

tatsu99さま
いつもご教授頂きましてありがとうございます。
今回もご教授頂けるとのこと、感謝いたします。
下記、回答いたしました。
お手数をおかけいたしますが、何卒、よろしくお願いいたします。

----------------------------------------------------------------------------------------------------------------------------------
>以下の前提でマクロを作成しますが、宜しいでしょうか。
１）合計シートは、作成したマクロのあるブック内に格納されていることが前提。
→承知しました。

>２）合計シートの集計対象となる月は、７月から翌年の６月までの１２か月であることが前提。（下期計、上期計等はない）
→集計対象となる月は7月から翌6月までと、7月から12月の列に上期、1月から6月の次の列に下期があります。

>３）特定フォルダ内の拡張子がxlsxであるブックを全て集計対象とする。
　　拡張子がxlsmのブックは集計対象としない。
　　もし、拡張子がxlsxで集計対象外のブックがある場合は、そのブックを他に移動してからマクロを実行すること。
→承知しました。

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

お礼日時：2017/05/24 11:53