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

Question

以前、複数シートを集計したい(別シートへ集計させたい)という質問をさせていただき
ご回答いただきました。
https://oshiete.goo.ne.jp/qa/9705836.html

これに似たような質問なのですが、今度は複数シートの集計だけでなく、同一フォルダにある複数のブックの複数シート(同じフォーマット)の
特定のセルを集計して、「合計」というシートに集計した数値を表示させるマクロを作成したいです。
※ブックは開かず閉じたまま実行したいです。

複数ブックはそれぞれ支店＆課名になっており、シート名はその支店の担当者名になっております。
全ブックの個人シートの特定セルを合計すると「合計」というシートの各支店、各課の前年、計画、実績に数値を合計させるといった
ことができたらなと思っております。

個人別シートのそれぞれA、B、Cの黄色のセルを合計シートの月別の前年、計画、実績へ表示させたいです。

何卒、ご教授願います。

tatsu99 · Accepted Answer

「それ以外」シートの方ですが下記URLへ格納しました。
http://climbi.com/b/10229/0

条件は、
個人シートのD列　”売上”の行が集計対象（２つ以上あること）（上から２つまでを集計）
上記を「それ以外」シートの①②へ集計する
で行っています。

tatsu99 · Answer

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

tatsu99 · Answer

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

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

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

tatsu99 · Answer

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

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

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

tatsu99 · Answer

>集計用マクロの合計、それ以外シートは正しく集計されて表示されているのですが
回答：今回のマクロでは、
①集計用マクロのある合計シート
②集計用マクロのあるそれ以外シート
のみが集計対象です。

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

tatsu99 · Answer

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

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

また、「それ以外シート」を除いて、本件は完了と考えて良いでしょうか。
（他に追加・変更の要件はないと考えて良いでしょうか）

tatsu99 · Answer

いま、climbiがアクセス不能の為、下記ＵＲＬの内容を修正したものを、climbiに格納できません。
http://climbi.com/b/10215/1

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

'１つのブックを処理する
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

追加①の行を追加してください。
シート名が”合計”の時、繰り返しを打ち切ります。（合計の左側のシートのみ処理します）

tatsu99 · Answer

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

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

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

tatsu99 · Answer

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

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

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

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

tatsu99 · Answer

下記ＵＲＬへ保存しました。
http://climbi.com/b/10215/1

最後は、画像のように　支店名が「全体合計」になっていることが必須です。
また、この名称は変更しないでください。（変更すると正しく動作しません）

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

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

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

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

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

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

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

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

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

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

下記ＵＲＬへ保存しました。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング