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

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.19 回答者： tatsu99 回答日時： 2019/08/17 16:12

エラー処理について

ケース１．
元データのB列（支店区分）とC列（所属）の組み合わせが、全体シートのA列（支店）、B列（課）に登録されていない場合、
エラーとし、マクロを停止しますが、よろしいでしょうか。
通常、このケースが発生するのは、元データのB列、C列がおかしいか、全体シートのA列、B列がおかしいかのどちらかの場合です。
唯一、正常ケースで発生しうるのは、課が廃止になった場合です。
例
2019年度　東京支店　３課　が存在する。
2020年度　東京支店　３課　を廃止した。
2020年度になり集計を行うとき、全体シートから東京支店　３課を削除した場合、前年データに東京支店　３課があるため、このエラーが発生する。
従って、この場合は、全体シートから東京支店　３課を削除せず、運用する。
全体シートから東京支店　３課を削除するのは、２０２１年度からとする。
上記のような運用をすれば、このケースのエラーは回避できます。

ケース２．元データのF列（分類区分コード）が、全体シートに登録されていない場合、
エラーとし、マクロを停止しますが、よろしいでしょうか。
これも、通常は元データか全体シートのどちらかがおかしい場合です。
唯一、正常ケースで発生しうるのは、分類区分コードが廃止になった場合が、ケース１と同様の運用で回避できます。
例　分類区分コード＝２０を2020年度から廃止した場合、
全体シートから（分類区分コード＝２０）を削除するのは、２０２１年度からにすれば、エラーは発生しません。

この回答へのお礼

エラー処理について、わかりやすくご説明いただきましてありがとうございます。

元データ、もしくは全体シートどちらかに存在しない支店区分(支店)、所属(課)の組み合わせ、分類区分コードがあった場合
エラーになるということですね。

ケース１、ケース２とも回避方法含め、承知いたしました。
何らかの変更があった際は事前に確認をし、次年度まではそのまま元データ、全体シートに存在するようにいたします。

また、ケース2に関する分類区分コードの追加に関して№13にてご教授頂きましたが
ケース1に関することになりますが、逆に支店が追加になった場合は新たに支店・課のブロックを追加し
「XXX合計」に集計されるように、元データ、全体シートに存在する支店に所属をさせれば集計されるという認識でよろしいでしょうか？

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

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

No.18 回答者： tatsu99 回答日時： 2019/08/17 15:53

前回同様、「合計」「XXX合計」を集計するルールのままでお願いできましたら幸いです。

本件、了解です。
本部については
　支店　課
①本部　本部
②本部　本部　合計
のように２つのブロックが定義されていないと、エラーになりますが、よろしいでしょうか。

この回答へのお礼

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

こちらこそ、以前のルールについて把握しておりましたのにも関わらず
紛らわしいサンプルを提示してしまい、お手数をおかけしてしまいました。
申し訳ありません。

当初のルールのままでよろしくお願いいたします。

また、本部に関しましては本部合計のブロックを追加いたしますので
そちらで進めていただけますと幸いです。

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

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

No.17 回答者： tatsu99 回答日時： 2019/08/17 15:50

最終確認です。

No１４回答のお礼欄を反映しました。
No10との違いの個所を★を付けました。ご確認ください。

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

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


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


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

この回答へのお礼

最終的な内容をご提示いただきましてありがとうございました。
内容確認いたしました、こちらでお願いできましたら幸いです。

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

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

No.16 回答者： tatsu99 回答日時： 2019/08/17 13:26

全体シートですが、

以前は、支店＝本部、課＝本部　はなかったように思われます。
今回、新規追加でしょうか。
以前は、
支店　　課
A支店｜１課
A支店｜２課
A支店｜３課
A支店｜A支店　合計

のように並んでいて、
B列の課のセルが　XXX合計　のように最後の２文字が「合計」の場合、その支店の合計を算出する
ようになっていました。
従って、既存の考え方を踏襲すると、本部を追加した場合も　「本部　合計」がないとエラーになります。
本部｜本部
本部｜本部　合計
のようになっていないと、エラーになってしまいます。
これは、例外的に　支店名が「本部」の場合は、「本部　合計」がなくてもOKにするということでしょうか。

又、合計のブロックは最後の２文字が「合計」なら、合計のブロックとみなす約束でしたが、
今回は、最後の１文字が　「計」なら合計のブロックとみなすことにしたのでしょうか。

この回答へのお礼

お世話になっております。
以下、回答させていただきます。

>全体シートですが、
> 以前は、支店＝本部、課＝本部　はなかったように思われます。
>> 今回、新規追加でしょうか。
> 以前は、
> 支店　　課
>A支店｜１課
>A支店｜２課
>A支店｜３課
>A支店｜A支店　合計
>のように並んでいて、
>B列の課のセルが　XXX合計　のように最後の２文字が「合計」の場合、その支店の合計を算出する
> ようになっていました。
> 従って、既存の考え方を踏襲すると、本部を追加した場合も　「本部　合計」がないとエラーになります。
> 本部｜本部
> 本部｜本部　合計
>のようになっていないと、エラーになってしまいます。
>これは、例外的に　支店名が「本部」の場合は、「本部　合計」がなくてもOKにするということでしょうか。
>又、合計のブロックは最後の２文字が「合計」なら、合計のブロックとみなす約束でしたが、
>今回は、最後の１文字が　「計」なら合計のブロックとみなすことにしたのでしょうか。

→本部についてですが、以前はA支店の課：本部としていた為、今回は分けて集計することとなりました。
ですので、「本部　合計」といたします。大変失礼いたしました。
また、ご指摘の通り以前は「XXX合計」というルールになっておりましたのにサンプルでは「XXX計」として
共有しておりました。大変申し訳ありません。
前回同様、「合計」「XXX合計」を集計するルールのままでお願いできましたら幸いです。

お忙しい中、何度もご確認いただきまして大変お手数をおかけして申し訳ございません。

お礼日時：2019/08/17 15:09

No.15 回答者： tatsu99 回答日時： 2019/08/17 07:54

元データのデータの開始行について、

以前の元データは３行目からデータが開始していましたが、
今回の元データも３行目からデータが開始するという前提で良いでしょうか。（添付図は以前の元データです。黄色部分がデータの開始行）

この回答へのお礼

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

元データの開始行は仰る通り、以前同様3行目開始で問題ございません。

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

お礼日時：2019/08/17 14:52

No.14 回答者： tatsu99 回答日時： 2019/08/17 07:42

>元データ：H列(数量)→J列(実績・当年数量)へ加算　※82、86、85、87、88以外

>元データ：I列(売上)→J列(実績・当年数量)へ加算　※82、86、85、87、88
>上記で提示しております通り、集計先は同一ですがコードによって元データの参照列が異なります。
>集計先が「数量」と表示されておりますが、実際は「数量/売上」というようなニュアンスです。

再度、質問し直します。
当年データが添付図のデータ１件のみの場合です。（前年のデータはデータなしとします。）
10月　支店=本部　課＝本部　分類区分コード＝８６なので、全体シートの２８行目が集計先になります。
元データのH列（2）　　　→J列（実績・当年数量）へ加算　J2８へ加算
元データのI列（45000）　→J列（実績・当年数量）へ加算　J2８へ加算
なのでJ28＝２＋４５０００＝４５００２
となりますが、あってますか。
という質問です。
これで、あっているとすると
分類コード＝８６の場合のJ28の意味は、当年の売上なので、４５０００のはずですが、何故４５００２になるのでしょうか。
この場合、元データのH列（＝２）をJ28へ加算してはいけないと思うのですが、いかがでしょうか。

この回答へのお礼

tatsu99様

こんにちわ。

ご返信が遅くなってしまい申し訳ありません。
以下、回答させていただきます。

>再度、質問し直します。
> 当年データが添付図のデータ１件のみの場合です。（前年のデータはデータなしとします。）
>10月　支店=本部　課＝本部　分類区分コード＝８６なので、全体シートの２８行目が集計先になります。
> 元データのH列（2）　　　→J列（実績・当年数量）へ加算　J2８へ加算
> 元データのI列（45000）　→J列（実績・当年数量）へ加算　J2８へ加算
>なのでJ28＝２＋４５０００＝４５００２
>となりますが、あってますか。
>という質問です。
→私の説明が混乱を招いてしまい申し訳ありません。
上記の例で言いますと分類区分コードは86はデータI列(売上)のみ参照先としたい為
H列は参照しない、としたいです。
> 元データのH列（2）　　　→J列（実績・当年数量）へ加算　J2８へ加算
→加算しない
> 元データのI列（45000）　→J列（実績・当年数量）へ加算　J2８へ加算
→加算する


>これで、あっているとすると
>分類コード＝８６の場合のJ28の意味は、当年の売上なので、４５０００のはずですが、何故４５００２になるのでしょうか。
>この場合、元データのH列（＝２）をJ28へ加算してはいけないと思うのですが、いかがでしょうか。
→私の説明がわかりづらく申し訳ありません。

・分類区分コード82、86、85、87、88は元データ(I列：売上)を参照→J列(実績・当年数量)へ
※元データH列は集計しない

・分類区分コード82、86、85、87、88以外は元データ(H列：数量)を参照→J列(実績・当年数量)へ
※元データI列は集計しない

何度も申し訳ありませんが、何卒よろしくお願いいたします。

お礼日時：2019/08/17 14:50

No.13 回答者： tatsu99 回答日時： 2019/08/16 23:45

>→ご提示いただいた内容の場合、今回こちらから提示したコードが今後発生した場合

>追加のコードをD列に追加し、行数が増えたとしても影響はないという認識でしょうか？
>私がご提示いただいた内容について理解不足でしたら申し訳ありません。
はい。問題ありません。（あなたがマクロを以下のように修正して対応します）
現在、考えているのは、以下のような形です。（正式にはリリース時説明しますが・・・）
①Const XXXX As Long = 28 '1ブロックの行数
②dim YYYY as variant
YYYY = array("82","86","85","87","88")

上記の２つの値を（XXXX,YYYY）を用意しておき（リリース時、XXXX,YYYYは他の具体的な名前に変わりますが）
ケース1
分類区分コードXXを追加して1行増える場合（XXが82、86、85、87、88と同じ扱いの場合）は、
Const XXXX As Long = 29
YYYY = array("82","86","85","87","88","XX")
のようにします。

ケース２
分類区分コードXXを追加して1行増える場合（XXが82、86、85、87、88以外と同じ扱いの場合）は、
Const XXXX As Long = 29
YYYY = array("82","86","85","87","88")・・・これは変更しません
のようにします。

ケース3（これはないかもしれませんが）
分類区分コードXXを追加して２行増える場合（XXが82、86、85、87、88と同じ扱いの場合）は、
Const XXXX As Long = 30
YYYY = array("82","86","85","87","88","XX")
のようにします。

ケース３の場合は、XXが記述された行について、集計が行われます。
もう１つの行は、空白になるので、その行は、集計は行われません。（関数式が埋め込まれるはずなのでマクロはその行を一切変更しません）

この回答へのお礼

tatsu99様

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

分類区分コードが増えた際について
わかりやすくご説明頂きましてありがとうございます。
とても勉強になります。

仰る通り、ケース3以外が発生する可能性があります。
その際はご教授頂いた内容で修正できるようにしたいと思います。

お礼日時：2019/08/17 10:02

No.12 回答者： tatsu99 回答日時： 2019/08/16 21:57

私が理解できないための質問ですが、

元データが、当年の分類コード＝８２の場合、
元データのH列→J列（実績・当年数量）へ加算
元データのI列→J列（実績・当年数量）へ加算
ですが、その理由は、８２の場合は、売上で集計をしているからということでした。
そうすると、
元データのH列は売上金額であることは理解できるのですが、
元データのI列も売上金額なのでしょうか。もし、そうなら、I列の売上とH列の売上の違いはなんでしょうか。
元データのI列が数量なら、J（実績・当年数量）へ加算してはいけないような気がします。

この回答へのお礼

>私が理解できないための質問ですが、
> 元データが、当年の分類コード＝８２の場合、
> 元データのH列→J列（実績・当年数量）へ加算
> 元データのI列→J列（実績・当年数量）へ加算
>ですが、その理由は、８２の場合は、売上で集計をしているからということでした。
>そうすると、
> 元データのH列は売上金額であることは理解できるのですが、
> 元データのI列も売上金額なのでしょうか。もし、そうなら、I列の売上とH列の売上の違いはなんでしょうか。
> 元データのI列が数量なら、J（実績・当年数量）へ加算してはいけないような気がします。
→私の説明がわかりづらく申し訳ありません。
元データ：H列(数量)→J列(実績・当年数量)へ加算　※82、86、85、87、88以外
元データ：I列(売上)→J列(実績・当年数量)へ加算　※82、86、85、87、88
上記で提示しております通り、集計先は同一ですがコードによって元データの参照列が異なります。
集計先が「数量」と表示されておりますが、実際は「数量/売上」というようなニュアンスです。

私が質問に対して、的を得ない回答をしてしまっておりましたら
大変申し訳ありません。

宜しくお願いいたします。

お礼日時：2019/08/16 23:20

No.11 回答者： tatsu99 回答日時： 2019/08/16 21:42

分類区分コードの追加があった場合の、集計先のあつかい。

今後、分類区分コード＝XX　が追加された場合の扱いですが、
以下のようにしますが、よろしいでしょうか。
１．元データのJ列については、分類区分コードに依存しないので、特に分類区分コードの追加を意識しない。
前年J列→N列（実績・前年粗利）
当年J列→O列（実績・当年粗利）とする。
２．元データのH列、J列については、以下のように対応する。
ケース１．XXが82、86、85、87、88と同様の扱いの場合、
マクロで、82、86、85、87、88のコードを格納した配列を用意しておくので、その配列にXXを追加する。
全体シートのD列にXXを追加する。
上記により、82、86、85、87、88と同様の動作が保障される。
ケース2．XXが82、86、85、87、88以外と同様の扱いの場合、
マクロ中の82、86、85、87、88のコードを格納した配列にXXを追加しない。
追加するのは、全体シートのD列のみである。
上記により、82、86、85、87、88以外と同様の動作が保障される。

この回答へのお礼

tatsu99様

遅くまで何度も申し訳ありません。

以下、回答させていただきます。
ご確認の程、宜しくお願いいたします。

>分類区分コードの追加があった場合の、集計先のあつかい。
> 今後、分類区分コード＝XX　が追加された場合の扱いですが、
> 以下のようにしますが、よろしいでしょうか。
> １．元データのJ列については、分類区分コードに依存しないので、特に分類区分コードの追加を意識しない。
> 前年J列→N列（実績・前年粗利）
> 当年J列→O列（実績・当年粗利）とする。
→仰る通りです。

> ２．元データのH列、J列については、以下のように対応する。
>ケース１．XXが82、86、85、87、88と同様の扱いの場合、
>マクロで、82、86、85、87、88のコードを格納した配列を用意しておくので、その配列にXXを追加する。
> 全体シートのD列にXXを追加する。
> 上記により、82、86、85、87、88と同様の動作が保障される。
>ケース2．XXが82、86、85、87、88以外と同様の扱いの場合、
>マクロ中の82、86、85、87、88のコードを格納した配列にXXを追加しない。
> 追加するのは、全体シートのD列のみである。
> 上記により、82、86、85、87、88以外と同様の動作が保障される。
→ご提示いただいた内容の場合、今回こちらから提示したコードが今後発生した場合
追加のコードをD列に追加し、行数が増えたとしても影響はないという認識でしょうか？
私がご提示いただいた内容について理解不足でしたら申し訳ありません。

お礼日時：2019/08/16 23:19

No.10 回答者： tatsu99 回答日時： 2019/08/16 21:29

10月へ集計する場合の最終確認です。

以下で、間違いないでしょうか。（前年はNo9と同じです）
前年　売上集計対象コード　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列 →　　J列（実績・当年数量）
I列 →　　J列（実績・当年数量）
J列 →　　O列（実績・当年粗利）

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

この回答へのお礼

tatsu99様

最終確認いただきましてありがとうございます。

ご提示いただいた内容で問題ございません。

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

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