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

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.29 回答者： tatsu99 回答日時： 2019/08/28 10:28

>また、下記の件はまた別途質問させて頂きます。

>↓
>https://oshiete.goo.ne.jp/qa/11227677.html

了解しました。仕様が決定しましたら、連絡ください。

この回答へのお礼

かしこまりました。
引き続きよろしくお願いいたします。

お礼日時：2019/08/28 11:48

No.28 回答者： tatsu99 回答日時： 2019/08/27 21:33

＞無事、検証完了いたしましたのでご報告させて頂きます。

了解しました。マクロの勉強は引き続き行ってください。

この回答へのお礼

おはようございます。

はい、もちろん今後も勉強していく所存です。

ただ、参考書だけでは理解できない部分が多い為
tatsu様にご教授頂きました内容やネットで検索して知識を高めていければと思います。

また、下記の件はまた別途質問させて頂きます。
↓
https://oshiete.goo.ne.jp/qa/11227677.html

お手数をおかけして大変申し訳ありませんが
引き続き何卒ご教授頂けますと幸いです。

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

お礼日時：2019/08/28 09:41

No.27 回答者： tatsu99 回答日時： 2019/08/26 20:25

1か所訂正です。

val1 = get_val(nendo, ws, wrow, "H")
val2 = get_val(nendo, ws, wrow, "I")
val2 = get_val(nendo, ws, wrow, "J")
のように変えます。

を

val1 = get_val(nendo, ws, wrow, "H")
val2 = get_val(nendo, ws, wrow, "I")
val3 = get_val(nendo, ws, wrow, "J")・・・・・（val2をval3に訂正）
のように変えます。

に訂正します。

No.26 回答者： tatsu99 回答日時： 2019/08/26 18:10

変更は以下のようになります。

１．列数の変更
283行　 GetColNumber = 6 + 12 * ix
の12を17に変更する。

２．全体シートの各列の初期化
データを設定する相対列は、４，５、９，１０、１３、１４列なので、
269行　 ws.Cells(wrow, scol + 3).Value = ""
270行　 ws.Cells(wrow, scol + 4).Value = ""
271行　 ws.Cells(wrow, scol + 8).Value = ""
272行　 ws.Cells(wrow, scol + 9).Value = ""
を
ws.Cells(wrow, scol + 4).Value = ""
ws.Cells(wrow, scol + 5).Value = ""
ws.Cells(wrow, scol + 9).Value = ""
ws.Cells(wrow, scol + 10).Value = ""
ws.Cells(wrow, scol + 13).Value = ""
ws.Cells(wrow, scol + 14).Value = ""
に変更する。

３．データの読み込み
165行　 Dim val1 As Variant 'H列/I列の値
166行　 Dim val2 As Variant 'J列の値
を
Dim val1 As Variant 'H列の値
Dim val2 As Variant 'I列の値
Dim val3 As Variant 'J列の値
のように変えます。
val1が元データの数量
val2が元データの売上
val3が元データの粗利益
を格納します。

193行　 If is_uriage(bcd) = True Then
194行　 wcol = "I"
195行　 Else
196行　 wcol = "H"
197行　 End If
の行ですが、これは不要になりますので、コメントアウトするか削除するかしてください。
（分類区分コードが"82", "85", "86", "87", "88"か否かで取得対象データを変えるのをやめたため）
これにともない。
13行　 Dim UriArray As Variant '売上集計対象　分類コード（82、86、85、87、88）
39行　 UriArray = Array("82", "85", "86", "87", "88") '売上を集計する分類コード
223行　Private Function is_uriage(ByVal bcd As String) As Boolean
224行　 Dim i As Long
225行　 is_uriage = True
226行　 For i = 0 To UBound(UriArray)
227行　 If UriArray(i) = bcd Then Exit Function
228行　 Next
229行　 is_uriage = False
230行　End Function
も不要になります。コメントアウト、又は削除してもかまいませんが、残しておいても問題ありません。
但し、残す場合は使用しない旨をコメントで追加しておいたほうがよいでしょう。

198行　 val1 = get_val(nendo, ws, wrow, wcol)
199行　 val2 = get_val(nendo, ws, wrow, "J")
は
val1 = get_val(nendo, ws, wrow, "H")
val2 = get_val(nendo, ws, wrow, "I")
val2 = get_val(nendo, ws, wrow, "J")
のように変えます。

４．読み込んだデータの設定
204行　 '数量／価格の集計
205行　 Call add_val(nendo, rowA, scol + 3, val1)
206行　 Call add_val(nendo, rowY, scol + 3, val1)
207行　 Call add_val(nendo, rowZ, scol + 3, val1)
208行　 '粗利の集計
209行　 Call add_val(nendo, rowA, scol + 8, val2)
210行　 Call add_val(nendo, rowY, scol + 8, val2)
211行　 Call add_val(nendo, rowZ, scol + 8, val2)
を以下のように変更します。
'数量の集計
Call add_val(nendo, rowA, scol + 4, val1)
Call add_val(nendo, rowY, scol + 4, val1)
Call add_val(nendo, rowZ, scol + 4, val1)
'価格の集計
Call add_val(nendo, rowA, scol + 9, val2)
Call add_val(nendo, rowY, scol + 9, val2)
Call add_val(nendo, rowZ, scol + 9, val2)
'粗利の集計
Call add_val(nendo, rowA, scol + 13, val3)
Call add_val(nendo, rowY, scol + 13, val3)
Call add_val(nendo, rowZ, scol + 13, val3)

この回答へのお礼

tatsu99様

お世話になっております。
ご返信遅くなり、申し訳ありません。

ご教授頂きました修正マクロで検証させて頂きました。
※№27も反映いたしました。

無事、検証完了いたしましたのでご報告させて頂きます。

お忙しい中、お手数をおかけいたしました。

また、様々な修正例もご教授いただきましてありがとうございました。
とても勉強になりました。

お礼日時：2019/08/27 11:55

No.25 回答者： tatsu99 回答日時： 2019/08/26 14:21

画像を見たときの確認です。

以下のような前提条件でしょうか。
１．計画の売上（H列）は、マクロでは設定しない。
２．前年数量（J）、当年数量（K）は、元データの数量を設定する。
（分類区分コードが"82", "85", "86", "87", "88"であっても、元データの数量を設定する）
３．前年売上（O）、当年売上（P）は、元データの売上を設定する。
（分類区分コードが"82", "85", "86", "87", "88"以外でも、元データの売り上げを設定する）
結局、現状では、分類区分コードの値により、元データの採用項目が決定されていたが、このケースでは
分類コードに依存せず、設定を行う。
４．前年粗利、当年粗利は、元データの粗利を設定する。
５．マクロで設定する相対列は、４，５、９，１０、１３、１４とする。
６．1ブロックの列は１７列とする。

この回答へのお礼

tatsu99様

お世話になっております。
ご返信頂きましてありがとうございました。

仰る通り、ご提示いただきました前提条件で問題ございません。

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

お礼日時：2019/08/26 16:17

No.24 回答者： tatsu99 回答日時： 2019/08/26 11:12

>→今後可能性は低いとは思いますがあるとすれば、現在数量の項目にコードによって

>数量or売上の数字を集計するようにして頂いておりますが
>売上を前年売上、当年売上といったように分けて集計することが考えられます。
>なので、計画の部分にも売上といった項目が追加されることになるかもしれません。
すみません。このイメージがわきません。具体的に画像で提示していただければ、その画像に従い、
マクロのどの個所を修正するかを指摘することは可能です。（提示した画像の部分を修正した内容を提示してください）
但し、今回、あなたが、そこまでは望んでいないなら画像の提示は不要です。

この回答へのお礼

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

>すみません。このイメージがわきません。具体的に画像で提示していただければ、その画像に従い、
>マクロのどの個所を修正するかを指摘することは可能です。（提示した画像の部分を修正した内容を>提示してください）
>但し、今回、あなたが、そこまでは望んでいないなら画像の提示は不要です。
→補足に画像添付いたしました。
　黄色で色付けした部分が追加になった場合の「売上」の列です。
　何度も申し訳ございませんが、ご教授頂けますと幸いです。

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

お礼日時：2019/08/26 13:24

No.23 回答者： tatsu99 回答日時： 2019/08/23 20:26

>>nendoは前年／当年の意味で、ZENNEN,TOUNENのどちらかが設定されていて、もし、TOUNENの場合は、add_valが１列右側にデータを設定します。

>>val3これは、あらたにこの変数を宣言し、このval3に値を設定しておく必要があります。（ここは、具体的に決まってないのでこれ以上は書けません）
>→上記部分に関して、私の認識で合ってますでしょうか？
>2列追加したとして、例えばval3をnendoで「KEIKAKU」というように設定すれば、1列右側にデータを設定できるということでしょうか?
>　 理解不足でしたら申し訳ありません。

基本的に誤解しています。
まず、nendoについてですが、
前年度のデータを処理するときに、
124行　 Call Sum1Book(zen_folder, zen_file, ZENNEN)
により、ZENNENが設定され、これが、最終的に、Private Sub Sum1Sheet(ByVal nendo As Long)のnendoにまで引き継がれます。
当年度の場合は、
126行 Call Sum1Book(tou_folder, tou_file, TOUNEN)でTOUNENが指定され、
これが最終的に、Private Sub Sum1Sheet(ByVal nendo As Long)のnendoにまで引き継がれます。
従って、nendoとは今、前年度のデータを処理しているのか、当年度のデータを処理しているのかを示す変数です。
これが、前年度ならば、
208行　Call add_val(nendo, rowA, scol + 8, val2)
は、相対列8列目に値を設定しなさいということを示しています。（先頭の相対列は０）
それで、当年度ならば、9列になりますが、１つのロジックで、処理するようにしているため、
add_valの中で、もし、当年度なら１列右側に設定するようにしています。
これにより
Call add_val(nendo, rowA, scol + 9, val2)
のような文を記述する必要がなくなります。
言い換えれば
208行　Call add_val(nendo, rowA, scol + 8, val2)
は、前年度なら相対列８列目に設定し、当年度なら、相対列９列目に設定する機能を持っています。


従って、nendoにはKEIKAKUの概念自体がありませんので、そのような設定は意味がありません。
具体的に、２列追加して、どのような値を設定することを想定していますか？
そのあたりの事情がわかれば、もっと、具体的に変更箇所を提示できます。

この回答へのお礼

tatsu99様

こんにちわ。
ご返信が遅くなり、申し訳ありません。

>基本的に誤解しています。
>まず、nendoについてですが、
>前年度のデータを処理するときに、
>124行　 Call Sum1Book(zen_folder, zen_file, ZENNEN)
～以下、文字数制限の為省略させて頂きます。
→理解不足で大変申し訳ございません。
　また、詳細ご丁寧に説明頂きましてありがとうございます。
　少しずつではありますが、ご教授頂いた説明を理解しながら自分で修正等できるように
　勉強していきたいと思います。

>従って、nendoにはKEIKAKUの概念自体がありませんので、そのような設定は意味がありません。
>具体的に、２列追加して、どのような値を設定することを想定していますか？
>そのあたりの事情がわかれば、もっと、具体的に変更箇所を提示できます。
→今後可能性は低いとは思いますがあるとすれば、現在数量の項目にコードによって
数量or売上の数字を集計するようにして頂いておりますが
売上を前年売上、当年売上といったように分けて集計することが考えられます。
なので、計画の部分にも売上といった項目が追加されることになるかもしれません。

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

お礼日時：2019/08/26 10:36

No.22 回答者： tatsu99 回答日時： 2019/08/23 11:51

282行　　GetColNumber = 6 + 12 * ix　　　12を14に変更

（279行の12は変更してはいけません。これは、月が10月未満の場合、12月を足して補正するためのものです）

276行　　9月=186→9月=210へ修正・・・これで、あってますが、これはコメントなので、特に動作内容には影響しません。
しかしながら、コメントが誤っていると、後で見たときに、混乱のもとになるので、これも直したほうがよいでしょう。

他の修正個所です。もし、２列を最後に追加し、そこに何かのデータを書き込むなら、

268行 ws.Cells(wrow, scol + 3).Value = ""
269行 ws.Cells(wrow, scol + 4).Value = ""
270行 ws.Cells(wrow, scol + 8).Value = ""
271行 ws.Cells(wrow, scol + 9).Value = ""
の後に
272行 ws.Cells(wrow, scol + 12).Value = ""
273行 ws.Cells(wrow, scol + 13).Value = ""
のように追加します。
この３、４、８、９、の数値ですが、１ブロックの左側からの相対位置です。先頭が０になります。
添付図の緑の数値です。
ここは、最初にシートをクリアする処理です。
実績の前年数量、当年数量、前年粗利、当年粗利のみを狙い撃ちでクリアしています。ほかの列は関数式があるのでクリアしてはいけません。
ここに、クリアする処理を追記します。（272,273行の追加）
但し、追加した２列をクリアする必要がないなら、272,273行の追加は不要です。



次にデータを設定する処理です。
203行 '数量／価格の集計
204行 Call add_val(nendo, rowA, scol + 3, val1)
205行 Call add_val(nendo, rowY, scol + 3, val1)
206行 Call add_val(nendo, rowZ, scol + 3, val1)
207行 '粗利の集計
208行 Call add_val(nendo, rowA, scol + 8, val2)
209行 Call add_val(nendo, rowY, scol + 8, val2)
210行 Call add_val(nendo, rowZ, scol + 8, val2)

へ
211行 'ＸＸの集計
212行 Call add_val(nendo, rowA, scol + 12, val3)
213行 Call add_val(nendo, rowY, scol + 12, val3)
214行 Call add_val(nendo, rowZ, scol + 12, val3)

のように追加します。
変数の意味は以下のようになっています。
nendoは前年／当年の意味で、ZENNEN,TOUNENのどちらかが設定されていて、もし、TOUNENの場合は、add_valが１列右側にデータを設定します。
rowAは、データを設定する支店・課の行
rowYは、データを設定する支店合計の行
rowZは、データを設定する全体合計の行　になっています。
scol+12ですが、scolがデータを設定するブロックの先頭列になっています。さらに12を加算すると今回追加された列（前年分）の位置になります。
（当年のデータを処理するときは、nendoにTOUNENが設定されるので、その為、指定より更に１列右側に設定されます）
val3これは、あらたにこの変数を宣言し、このval3に値を設定しておく必要があります。（ここは、具体的に決まってないのでこれ以上は書けません）

尚、データの設定が行われない（関数式で設定する場合等）なら、この追加（211行～214行）は不要です。

この回答へのお礼

tatsu99様

お世話になっております。
早速、ご教授頂きましてありがとうございます。
また、画像も添付頂き、わかりやすくご説明頂き感謝いたします。

>282行　　GetColNumber = 6 + 12 * ix　　　12を14に変更
>（279行の12は変更してはいけません。これは、月が10月未満の場合、12月を足して補正するためのものです）
→承知いたしました。

>276行　　9月=186→9月=210へ修正・・・これで、あってますが、これはコメントなので、特に動作内容には影響しません。
>しかしながら、コメントが誤っていると、後で見たときに、混乱のもとになるので、これも直したほうがよいでしょう。
→承知いたしました。もし、今後列追加が発生した際は備忘録として修正するようにいたします。

>他の修正個所です。もし、２列を最後に追加し、そこに何かのデータを書き込むなら、
>268行 ws.Cells(wrow, scol + 3).Value = ""
～文字数の為、以下省略させて頂きます。
→とても勉強になります。
　追加したい列が数式が入っている列だったらクリアする必要はないので追加をせず
　実績を集計したい列であればクリアする必要がある為、追加をするという認識ですね。

>次にデータを設定する処理です。
>203行 '数量／価格の集計
～文字数制限の為、以下省略させて頂きます。
↓
>nendoは前年／当年の意味で、ZENNEN,TOUNENのどちらかが設定されていて、もし、TOUNENの場合は、add_valが１列右側にデータを設定します。
>val3これは、あらたにこの変数を宣言し、このval3に値を設定しておく必要があります。（ここは、具体的に決まってないのでこれ以上は書けません）
→上記部分に関して、私の認識で合ってますでしょうか？
2列追加したとして、例えばval3をnendoで「KEIKAKU」というように設定すれば、1列右側にデータを設定できるということでしょうか?
　 理解不足でしたら申し訳ありません。


何度も申し訳ありまんが、何卒ご教授頂けますと幸いです。

お礼日時：2019/08/23 15:55

No.21 ベストアンサー 回答者： tatsu99 回答日時： 2019/08/19 07:58

https://ideone.com/0Jb16U
に登録しました。
使用時の注意事項です。
１．分類区分コードの追加を行う場合の手順（分類区分コード＝XXを追加）
①全体シートにXXを追加
②マクロの４行目
Const ROW_COUNT_OF_1BLOCK As Long = 28 '1ブロックの行数
の内容を変更する。現在１ブロックにつき28行なので、追加した結果29になるなら
Const ROW_COUNT_OF_1BLOCK As Long = 29 '1ブロックの行数
と記述する。
③マクロの38行目
UriArray = Array("82", "85", "86", "87", "88") '売上を集計する分類コード
追加したXXが、82、86、85、87、88と同じ処理をするなら、この行にXXを追加する。
UriArray = Array("82", "85", "86", "87", "88","XX") '売上を集計する分類コード
と記述する。82、86、85、87、88と同じ処理をしないなら、XXは追加しない。

２．支店を追加する場合の手順（X支店を追加する）
①全体シートにX支店を追加する。（B列の課に"X支店　合計"も含めること）
②マクロは更新不要

こちらでは、分類区分コードの追加／削除時の動作確認、及び、支店の追加／削除時の動作確認は
行っておりません。あなたのほうで、一通りの確認が終わりましたら、これらの動作確認試験も
行ってください。

この回答へのお礼

こんにちわ。

お忙しい中、ご教授頂きましてありがとうございます。

注意点も承知いたしました。

早速、動作確認させていただきます。
ただ、今週前半確認が難しい為、木曜日以降確認させて頂き、何かあればご連絡させていただきます。

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

お礼日時：2019/08/19 15:43

No.20 回答者： tatsu99 回答日時： 2019/08/17 17:57

>ケース1に関することになりますが、逆に支店が追加になった場合は新たに支店・課のブロックを追加し

>「XXX合計」に集計されるように、元データ、全体シートに存在する支店に所属をさせれば集計されるという認識でよろしいでしょうか？

はい。それでOKです。
XXX支店　1課、２課　を追加の場合
全体シートに以下のように追加すればOKです。
支店　　　　課
XXX支店｜１課
XXX支店｜２課
XXX支店｜XXX支店　合計

元データは
売上日付　支店区分　所属　
10月　　 XXX支店　１課　・・・・以降省略・・・
10月 　　XXX支店　２課　・・・・以降省略・・・
11月 　　XXX支店　１課　・・・・以降省略・・・
のような形になります。

この回答へのお礼

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

承知いたしました。

そういった場合が発生した際は、ご教授頂きましたように対応させていただきます。

お礼日時：2019/08/17 18:04