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

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

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

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

何卒、ご教授願います。

「【マクロ】同一フォルダ内の複数ブックの複」の質問画像

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

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

    「【マクロ】同一フォルダ内の複数ブックの複」の補足画像1
      補足日時:2017/05/18 13:06
  • ※回答続きです。
    >4)集計対象ブック内の各シートのC2、C3の内容に従い、合計シートの支店名、課名が合致する箇所へ合計する。従って、1つのブック内で、支店名、課名が異なっていても、それに従い、集計する。
    例 仙台・営業.xlsxのブックがあった時
    そのブックの1枚目のシートがC2=仙台支店 C3=営業課、であれば、仙台支店の営業課へ合計する。
    そのブックの2枚目のシートがC2=大阪支店 C3=第一課、であれば、大阪支店の第一課へ合計する。
    →承知しました。

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

    ※補足コメント続きです。
    >5)集計対象ブックの○○支店、○○課のデータと合計シートの○○支店、○○課の関係は以下の通り
    各月ごとに、
    集計対象ブックの1番目の合計(D列の1番目に"合計"が設定されている行)の値(前年、計画、実績)を合計シートの①の前年、計画、実績へ加算する。
    集計対象ブックの2番目の合計(D列の2番目に"合計"が設定されている行)の値(前年、計画、実績)を合計シートの②の前年、計画、実績へ加算する。
    集計対象ブックの3番目の合計(D列の3番目に"合計"が設定されている行)の値(前年、計画、実績)を合計シートの③の前年、計画、実績へ加算する。
    →仰る通りです。

    また、こちらから追加で質問です。
    マクロ実行は各ブックそれぞれをひとつづつ更新する必要があるということでしょうか?
    私の質問が説明不足、私の理解不足でしたら申し訳ありません。
    何卒、よろしくお願いいたします。

      補足日時:2017/05/24 11:57
  • うーん・・・

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

    度々恐縮ではございますが、何卒ご教授頂けますと幸いです。

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

    No.2の回答に寄せられた補足コメントです。 補足日時:2017/05/24 13:23
  • 補足要求③の画像添付します。

    「【マクロ】同一フォルダ内の複数ブックの複」の補足画像5
    No.6の回答に寄せられた補足コメントです。 補足日時:2017/05/25 15:27
  • 補足要求④の画像添付します。

    「【マクロ】同一フォルダ内の複数ブックの複」の補足画像6
      補足日時:2017/05/25 15:35
  • 申し訳ありません。
    先ほどのお礼に書かせていただいた内容が質問の返答になっていなかった点がありました。
    大変失礼いたしました。

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

    No.11の回答に寄せられた補足コメントです。 補足日時:2017/05/26 10:14
  • ※個人シート画像添付します。

    「【マクロ】同一フォルダ内の複数ブックの複」の補足画像8
    No.13の回答に寄せられた補足コメントです。 補足日時:2017/05/26 13:08
  • ※それ以外シートの画像添付します。

    「【マクロ】同一フォルダ内の複数ブックの複」の補足画像9
      補足日時:2017/05/26 13:09
  • うーん・・・

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

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

A 回答 (19件中1~10件)

「それ以外」シートの方ですが下記URLへ格納しました。


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

条件は、
個人シートのD列 ”売上”の行が集計対象(2つ以上あること)(上から2つまでを集計)
上記を「それ以外」シートの①②へ集計する
で行っています。
この回答への補足あり
    • good
    • 1
この回答へのお礼

助かりました

お礼が遅くなり、申し訳ありません。
それ以外シートの件、ご教授頂きましてありがとうございました。
ご教授頂いたマクロで実行し、無事解決いたしました。
本件、何度もお手数をおかけして申し訳ありませんでした。
本当に助かりました。また、大変勉強になりました。
ありがとうございました。

お礼日時:2017/05/29 09:39

>今回、ご教授頂いた集計用マクロで集計したファイルをそれぞれのブックへ参照させるようにしますのでご対応不要です。


了解しました。
    • good
    • 1

>集計用マクロと同じように各支店の個人シートをすべて集計したものを


>各支店ごとのブックにある合計シート、それ以外シートへ反映させたいのです。
>各支店ごとにブックが分かれているので、各支店ごとにブックを開けば全支店の数値を把握できるようにしたいのです。

ということは、仙台が3課まであるとすると
①仙台一課.xlsx内の山田、田中のデータをそのブック内の合計シートに集計
②同上データを仙台二課、仙台三課、及び他の全支店の合計シートにも集計
のように、他のブック(②)に集計することも要求されていますか。
もし、そうであれば、大幅な、変更になりますので、今回は対応できません。(①だけなら修正量はすくなく対応可能ですが)

又、要件が上記の①②のことだとすると、
マクロのあるブックの合計シートと、各集計元ブックの合計シートは、全く同じ内容が設定されることになりますが、
そのようなことを望まれていたのでしょうか。
    • good
    • 1
この回答へのお礼

助かりました

>>集計用マクロと同じように各支店の個人シートをすべて集計したものを
>>各支店ごとのブックにある合計シート、それ以外シートへ反映させたいのです。
>>各支店ごとにブックが分かれているので、各支店ごとにブックを開けば全支店の数値を把握できるようにしたいのです。

>ということは、仙台が3課まであるとすると
>①仙台一課.xlsx内の山田、田中のデータをそのブック内の合計シートに集計
②同上データを仙台二課、仙台三課、及び他の全支店の合計シートにも集計
>のように、他のブック(②)に集計することも要求されていますか。
>もし、そうであれば、大幅な、変更になりますので、今回は対応できません。(①だけなら修正量はすくなく対応可能ですが)
>又、要件が上記の①②のことだとすると、
>マクロのあるブックの合計シートと、各集計元ブックの合計シートは、全く同じ内容が設定されることになりますが、
>そのようなことを望まれていたのでしょうか。
→はい、おっしゃる通りで①②どちらも希望しておりました。
 ですので、当初私が作成した表ですと①の作業をSTART、ENDというシートで個人シートを挟んで合計シートに集計したものを
 それぞれ各支店ブックの合計シートへ参照させておりました。
 今回、ご教授頂いた集計用マクロで集計したファイルをそれぞれのブックへ参照させるようにしますのでご対応不要です。
 本当に何度も無理難題なことを申し上げ、お手数をおかけして申し訳ありませんでした。
 大変助かりました。ありがとうございます。

お礼日時:2017/05/29 17:01

>→私の説明不足で正確な情報を伝えられていなかったのだと思います。

申し訳ありません。
>各支店の集計用ブックにそれぞれ個人シートと合計、それ以外シートがあり、マクロ用で集計後
>各支店分の合計、それ以外シートにもすべて反映できるようにとお伝えしたつもりでした。

要件の追加は、
①各支店の集計用ブックの合計シート
②各支店の集計用ブックのそれ以外シート
上記シートへも集計したい(上記シートへ合計を加算したい)。
ということでしょうか。

現行のマクロへの影響が少ないようであれば、検討してみます。
上記①②のシートの画像及び集計したいセルの情報を提示していただけますか。
又、1つのブック内の個人シートのデータを同じブック内の合計シート(又はそれ以外シート)に集計(加算)と理解していますが、あってますか。
(他の各支店のブックの合計シート(又はそれ以外シート)に集計することはない。)
    • good
    • 1
この回答へのお礼

>>→私の説明不足で正確な情報を伝えられていなかったのだと思います。申し訳ありません。
>>各支店の集計用ブックにそれぞれ個人シートと合計、それ以外シートがあり、マクロ用で集計後
>>各支店分の合計、それ以外シートにもすべて反映できるようにとお伝えしたつもりでした。

要件の追加は、
①各支店の集計用ブックの合計シート
②各支店の集計用ブックのそれ以外シート
上記シートへも集計したい(上記シートへ合計を加算したい)。
ということでしょうか。
→説明が下手でしたイメージがつきずらく申し訳ありません。。。。
集計用マクロと同じように各支店の個人シートをすべて集計したものを
各支店ごとのブックにある合計シート、それ以外シートへ反映させたいのです。
各支店ごとにブックが分かれているので、各支店ごとにブックを開けば全支店の数値を把握できるようにしたいのです。


>現行のマクロへの影響が少ないようであれば、検討してみます。
>上記①②のシートの画像及び集計したいセルの情報を提示していただけますか。
>又、1つのブック内の個人シートのデータを同じブック内の合計シート(又はそれ以外シート)に集計(加算)と理解していますが、あってますか。
>(他の各支店のブックの合計シート(又はそれ以外シート)に集計することはない。)
→上記でも記載させていただいておりますが、他の各支店のブックの合計シート、それ以外シートにも集計させて全体の数値を把握したです。
画像は、集計用マクロの際補足で添付させていただいたものと同じものになります。
もし、現行のマクロにに影響があるようでしたら本件につきましては割愛いただいて結構です。
お手数をおかけして申し訳ありません。

お礼日時:2017/05/29 16:07

>集計用マクロの合計、それ以外シートは正しく集計されて表示されているのですが


回答:今回のマクロでは、
①集計用マクロのある合計シート
②集計用マクロのあるそれ以外シート
のみが集計対象です。

>その集計元の各支店別の複数ブックの中にある「合計シート」、「それ以外シート」には処理完了とはなりますが#REFエラーや何も反映されず空白になってしまっています。
>原因は何が考えられますでしょうか?
回答:これは、そもそも最初の要件に含まれていないと考えています。
集計元の各支店別の1つのブックを○○支店.xlsxとすると、そのブック内に左から順に
①田中
②山田
③合計
④任意の名称のシート、以降同様
とあるとすると、
①、②のセルを読み込み、マクロのある合計シート(若しくはそれ以外シート)に加算します。
③の合計シートは、そのシート名が”合計”であることを検知した時点で、処理を打ち切ります。
    • good
    • 1
この回答へのお礼

助かりました

早々にご回答いただきましてありがとうございます。

>>集計用マクロの合計、それ以外シートは正しく集計されて表示されているのですが
>回答:今回のマクロでは、
>①集計用マクロのある合計シート
>②集計用マクロのあるそれ以外シート
>のみが集計対象です。
→私の説明不足で正確な情報を伝えられていなかったのだと思います。申し訳ありません。
各支店の集計用ブックにそれぞれ個人シートと合計、それ以外シートがあり、マクロ用で集計後
各支店分の合計、それ以外シートにもすべて反映できるようにとお伝えしたつもりでした。
大変失礼いたしました。
ただ、集計用マクロではご教授頂いたマクロで無事集計完了できております為
こちらで完了とさせて頂きます。ありがとうございました。
そして、いろいろとお手数をおかけしてしまい、申し訳ありませんでした。

お礼日時:2017/05/29 11:14

>また、今回の合計シートと同じように個人シートのD列に「売上」となっているものを


>集計して別シートとして「それ以外シート」に集計結果を表示させたいのですが
>どこの部分のモジュールを書き換えたらよいでしょうか?
>ちなみにそれぞれのブックの個人シートの②と③の売上とつくセルの数値を
>それ以外シートの①(個人シートの②)、②(個人シートの③)へ集計結果を表示させたいです。
>※個人シートの合計にはこの部分は含まれていない為、別シートで集計したいです。
>画像を添付いたしましたので、再度で恐縮ではございますがご教授願います。

別マクロでの提供になります。
修正&こちらでの動作確認は終了していますがclimbiが不調なため、そちらに投稿できません。
このgooに書こうとすると文字数オーバーで書けません。
このgooのサイトは、マクロの空白のインデントがめちゃくちゃになるので、できればclimbiにしようかと思っています。
来週明けまでに、climbiが復旧しない場合は、来週明けにこのサイトに複数回に分けて、マクロを提示します。
それで、良いでしょうか。

また、「それ以外シート」を除いて、本件は完了と考えて良いでしょうか。
(他に追加・変更の要件はないと考えて良いでしょうか)
    • good
    • 1
この回答へのお礼

助かりました

ご返信ありがとうございます。
別マクロ、climbiの不具合の件承知いたしました。
何度もお手数をおかけして申し訳ありません。
週明けでかまいませんので、何卒よろしくお願いいたします。

>また、「それ以外シート」を除いて、本件は完了と考えて良いでしょうか。
(他に追加・変更の要件はないと考えて良いでしょうか)
→はい、完了という認識で問題ありません。
 ご丁寧にご教授頂き感謝いたします。ありがとうございました。

お礼日時:2017/05/26 14:30

いま、climbiがアクセス不能の為、下記URLの内容を修正したものを、climbiに格納できません。


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

前回、提示したマクロの83行目付近のRead1Bookを修正します。

'1つのブックを処理する
Private Sub Read1Book(ByVal bookname As String)
Dim ws As Worksheet
Dim i As Long
Workbooks.Open Folder & "\" & bookname
For i = 1 To Worksheets.Count
Set ws = Worksheets(i)
If ws.Name = "合計" Then Exit For '追加①
Call Read1Sheet(bookname, ws)
Next
Workbooks(bookname).Close
End Sub

追加①の行を追加してください。
シート名が”合計”の時、繰り返しを打ち切ります。(合計の左側のシートのみ処理します)
この回答への補足あり
    • good
    • 1
この回答へのお礼

お忙しい中、大変お手数をおかけいたしました。
テストとして2ブックでマクロ実行しましたが、無事解決いたしました。
また、二重に開くと~というメッセージでしたが今回はでませんでしたが
こちらは仰る通り、様子を見たいと思います。
ありがとうございます。

また、今回の合計シートと同じように個人シートのD列に「売上」となっているものを
集計して別シートとして「それ以外シート」に集計結果を表示させたいのですが
どこの部分のモジュールを書き換えたらよいでしょうか?
ちなみにそれぞれのブックの個人シートの②と③の売上とつくセルの数値を
それ以外シートの①(個人シートの②)、②(個人シートの③)へ集計結果を表示させたいです。
※個人シートの合計にはこの部分は含まれていない為、別シートで集計したいです。
画像を添付いたしましたので、再度で恐縮ではございますがご教授願います。

お礼日時:2017/05/26 13:08

>マクロと集計対象ブックの仕様は下記の認識で合ってますでしょうか?


>認識が相違しておりましたら申し訳ありません。
>↓
>①マクロのあるブックには合計シートのみ。
回答:ほかにあっても構いません。マクロのあるブックは合計シートのみ参照/更新します。
>②集計対象となるブックはxlsxのもののみ。
回答:あっています。

>また、集計対象となるブックには、個人のシート(複数)と合計シート、参照元データのピボッドの3部門の
>コピーシートがある。(前年、本年とあるので計6シートあり)
>集計対象シートは個人のシートのみです。
>私の説明不足で申し訳ありません。
>対象外のシートも含まれている為、合計シートより左側に置いたもののみ集計等、条件が必要でしょうか?
回答:はい、必要になります。
方法1:対象外シートの名称に規則性があれば、シート名でそれをスキップすることは可能です。(今後他のシートが追加されることがないことが条件になりますが)
方法2:”合計”のシートが全てのブックに必ず存在するなら、合計シートより左側に置いたもののみ集計し、合計シート以降のシートをスキップすることは可能です。
どちらか、あなたが運用しやすい方を、選択してください。

また、
>営業部.xlsx(テストブック)はすでに開いています。のメッセージについてですが
>他に開いている人はおらず、私だけなのですが。。なぜか出てしまいます。
回答:現状では、なぜメッセージが表示されるのか判りません。今後、同じフォルダ内の他のブックも同じ現象が起こるのか等の確認を行いながら
様子を見たいと考えます。(とりあえず試験は出来そうですので)
    • good
    • 1
この回答へのお礼

早速のご回答ありがとうございます。
インラインにて追記いたします。

>>マクロと集計対象ブックの仕様は下記の認識で合ってますでしょうか?
>>認識が相違しておりましたら申し訳ありません。
>>↓
>>①マクロのあるブックには合計シートのみ。
回答:ほかにあっても構いません。マクロのあるブックは合計シートのみ参照/更新します。
→承知いたしました。
>>②集計対象となるブックはxlsxのもののみ。
回答:あっています。
→承知いたしました。

>>また、集計対象となるブックには、個人のシート(複数)と合計シート、参照元データのピボッドの3部門の
>>コピーシートがある。(前年、本年とあるので計6シートあり)
>>集計対象シートは個人のシートのみです。
>>私の説明不足で申し訳ありません。
>>対象外のシートも含まれている為、合計シートより左側に置いたもののみ集計等、条件が必要でしょうか?
回答:はい、必要になります。
方法1:対象外シートの名称に規則性があれば、シート名でそれをスキップすることは可能です。(今後他のシートが追加されることがないことが条件になりますが)
方法2:”合計”のシートが全てのブックに必ず存在するなら、合計シートより左側に置いたもののみ集計し、合計シート以降のシートをスキップすることは可能です。
どちらか、あなたが運用しやすい方を、選択してください。
→対象シートに規則はなく、今後シートが増える場合もある為、方法2で運用したいと考えております。

また、
>>営業部.xlsx(テストブック)はすでに開いています。のメッセージについてですが
>>他に開いている人はおらず、私だけなのですが。。なぜか出てしまいます。
回答:現状では、なぜメッセージが表示されるのか判りません。今後、同じフォルダ内の他のブックも同じ現象が起こるのか等の確認を行いながら
様子を見たいと考えます。(とりあえず試験は出来そうですので)
→承知いたしました。いろいろとありがとうございます。

以上、よろしくお願いいたします。

お礼日時:2017/05/26 10:40

>ブック名=営業部.xlsx シート名=合計


営業部.xlsxには全て、個人の名のシートが入っている前提です。
そのC2、C3が空白の為、エラーが発生しています。
そもそも、集計対象のブックには、”合計”のようなシートがないことが前提です。

この事象で考えられることは以下のどちらかです。
①テストデータとして合計を作ってしまった。
②集計対象のブックには、集計対象外のシートが(今回の合計等)があるのだが、その旨の説明が漏れていた。

①の場合は、そのシートを削除して試験続行してください。
②の場合は、仕様変更となる。その場合は、集計対象外となるシートの条件を明記してください。

>営業部.xlsx(テストブック)はすでに開いています。
このメッセージの原因は、不明です。営業部.xlsxを事前に必ず、閉じてから行ってますでしょうか。
また、あなた以外のだれかが、開いているということはないですか。
この回答への補足あり
    • good
    • 1
この回答へのお礼

うーん・・・

再度、ご教授頂きましてありがとうございます。

マクロと集計対象ブックの仕様は下記の認識で合ってますでしょうか?
認識が相違しておりましたら申し訳ありません。

①マクロのあるブックには合計シートのみ。
②集計対象となるブックはxlsxのもののみ。

また、集計対象となるブックには、個人のシート(複数)と合計シート、参照元データのピボッドの3部門の
コピーシートがある。(前年、本年とあるので計6シートあり)
集計対象シートは個人のシートのみです。
私の説明不足で申し訳ありません。
対象外のシートも含まれている為、合計シートより左側に置いたもののみ集計等、条件が必要でしょうか?

また、
>営業部.xlsx(テストブック)はすでに開いています。のメッセージについてですが
他に開いている人はおらず、私だけなのですが。。なぜか出てしまいます。

本当に何度も申し訳ありませんが、再度ご教授頂ければ幸いです。

何卒、よろしくお願いいたします。

お礼日時:2017/05/26 09:51

下記URLへ保存しました。


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

最後は、画像のように 支店名が「全体合計」になっていることが必須です。
また、この名称は変更しないでください。(変更すると正しく動作しません)
    • good
    • 1
この回答へのお礼

うーん・・・

何度もご教授頂きましてありがとうございます。
新たに頂いたマクロを下記手順で実行しましたところ、下記のような表示がされます。
私の実行過程もしくはシート等にエラーが出る原因があるのだと思いますが、どこが原因か恐れ入りますがご教授願います。

★テストとしてマクロが登録されている【マクロ】というテストブックでマクロ実行→「営業部.xlsx(テストブック)はすでに開いています。二重に開くと、これまでの変更内容は破棄されます。開きますか?」のメッセージ→「はい」→ファイル更新(ピボッド)→「ブック名=営業部.xlsx シート名=合計の支店名・課名(l)が合計シートに未登録と出ます。」のメッセージが表示されます。

お礼日時:2017/05/25 17:23

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報