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

Question

tatsu99様

いつもお世話になっております。
連日大変恐縮ではございますが、以前ご教授頂きました下記の件
再度ご教授頂けますと幸いです。

【マクロ変更希望】同一フォルダ内の複数ブックの複数シートの特定セルの集計を出したい 
№10にてご回答頂きました件、1点修正したいのですがご教授頂けますと幸いです。
Public Sub 売上月単位集計()
↓
https://oshiete.goo.ne.jp/qa/10618547.html

①参照元データピボット
C列の「営業所」の列が追加
※画像添付いたします。

②集計先
上記に伴い、B列「営業所」の列を追加したいです。

その他は変更ございません。

よろしくお願いいたします。

tatsu99 · Accepted Answer

下記にアップしました。
https://ideone.com/c99p6N
簡単なテストは行いました。
十分な検証は行っていませんので、不明点、不具合などありましたら連絡ください。

tatsu99 · Answer

>85、91、97、109行目にあるような
>「 key = "||||合計"」という表示が今回の支店、課、営業所の「××合計」をひろう
>ものになりますでしょうか？

はい。キーの構成は以下のようになります。
①全体合計用のキー
　　key = "||||合計"・・・・・・・・"||||合計"（固定文字）

②支店合計用のキー
　　key = siten & "|||合計"・・・・・支店名＋"|||合計"

③営業所合計用のキー
　　key = siten & "|" & eigyosho & "||合計"・・・・支店名＋"|"＋営業所名＋"||合計"

④通常の課のキー
　　key = siten & "|" & eigyosho & "|" & ka・・・支店名＋"|"＋営業所名＋"|"＋課名

①は常に存在する前提なので、そのキーから、行数を取得し、srowAllSumにその行数を格納しています。
（全体合計の始まる行数）

④は、当年実績データの支店（B列）、営業所（C列）、課（D列）をもとにして
　　　「支店＋"｜"＋営業所＋"｜"＋課」のキーを作成し、そのキーからdicTを参照し、
　　　該当の支店、営業所、課の行数を取得しています。

②は、「支店＋"|||合計"」のキーを作成し、そのキーからdicTを参照し、
　　　その支店合計の行数を取得しています。但し、ない場合もあるので（営業部の場合等）
　　　その場合は、取得した行数を0に設定します。行数が0の場合は、
　　　Private Sub add_valの中で、加算せず（なにもしないで）、終了します。

③は、「支店＋"|"＋営業所＋"||合計"」のキーを作成し、そのキーからdicTを参照し、
　　　その営業所合計の行数を取得しています。但し、ない場合もあるので（富山営業所の場合等）
　　　その場合は、取得した行数を0に設定します。行数が0の場合は、
　　　Private Sub add_valの中で、加算せず（なにもしないで）、終了します。

tatsu99 · Answer

>Ｂ案でお願いいたします。
了解しました。

＞　そうなると、一つしかない課についてはＣ列に「××合計」は付けず、合計としては算出しない。
＞　複数課がある場合は、Ｃ列に「××合計」があれば算出する。(営業所合計)
＞　Ｂ列+Ｃ列結合セルは「××合計」があれば算出する。(支店合計)
＞　上記の認識で合ってますでしょうか？
合っています。

この案で作業に着手します。
そこそこの修正量が発生しましたので、マクロの提示は、早くて、本日の夜になるかと思います。

tatsu99 · Answer

＞>東京支店　合計（黄色）は、B列の項目の合計と考えられるので、B列＋C列を結合セルにして表示すべきと考えますがいかがでしょうか。
＞→仰る通り、結合したほうがより見やすくなりますね。ご提案いただきましてありがとうございます。
＞そちらで修正をお円買いできましたら幸いです。

上記の前提で、修正します。又、B列＋C列の結合セルになっていないと、B列の合計は算出されませんのでご注意ください、

更に、修正に当たり、以下の点を確認したく。
１．営業部については、B列の＋C列が結合セルになった合計は存在しない。（添付図左側）
この合計が存在するのは、東京支店、仙台支店、名古屋支店、大阪支店についてである。
本件、本来であれば、（例外をみとめないということであれば）、営業部についてもB列の＋C列が結合セルになった合計が
存在するほうが、やりやすいのですが、そうすると、営業部については、
①営業部、②営業部　合計　③B列＋C列が結合セルの合計
が存在し、①②③は全て同じ値になってしまい、非常に冗長的になります。
よって、「営業部については、B列の＋C列が結合セルになった合計がなくても良い。」こととします。
但し、マクロ上は、B列の内容が営業部だから、そのようにするのではなく、
B列に最後の2文字が「合計」であるセルがなければ、合計を算出しないこととします。
従って、東京支店にB列の＋C列が結合セルになった合計がなくてもエラーとせず、合計を算出しないだけとなります。

２．C列の合計で１件しか存在しない場合の扱いについて
添付図の左側の青のセルを参照ください。
営業部と富山営業所には１つの課しか存在しません。この場合、青のセルの個所は、上と同じ値になります。
A案：これで、良いのであれば、C列にも必ず、最後の２文字が合計であるセルが存在しない場合はエラーとします。
B案：しかしながら、添付図の右側のようにしたいのであれば、そのようにすることも可能です。
その場合は、C列に、最後の２文字が合計であるセルが存在した場合のみ、その合計を算出するようになります。
どちらかを選択してください。

tatsu99 · Answer

>C列に東京支店、仙台支店、名古屋支店、大阪支店の合計が2つ存在します。
>××支店の1課、2課、3課の合計と、その他各営業所の全体の合計と表示した為です。
>その場合、××支店の1課、2課、3課の小計、その他営業所の小計＝××支店　合計としたほうがよろしいのでしょうか？

東京支店を例にとった場合、「東京支店　合計」が２つ存在する形で良いと考えます。
私の添付図の例でいえば、
上側の東京支店　合計（白色）＝東京支店（１課＋２課＋３課）
下側の東京支店　合計（黄色）＝東京支店（１課＋２課＋３課）＋富山営業所（1課）＋栃木営業所（1課＋2課）＋横浜営業所（1課＋２課）
を意味するものと理解しました。

添付図の左側があなたの提示したセルのレイアウトです。
添付図の右側が私の提案するセルのレイアウトです。
東京支店　合計（黄色）は、B列の項目の合計と考えられるので、B列＋C列を結合セルにして表示すべきと考えますがいかがでしょうか。
あなたが提示されたレイアウトの東京支店　合計（黄色）は、横浜営業所の合計のように見えてしまいます。

tatsu99 · Answer

＞ご提案頂きました、②案でお願いできましたら幸いです。
②案で修正します。

＞その際、これまではB列(今回からC列に移動)に「××支店　合計」の行だけでしたが
＞今回からB列に「××営業所　合計」の行を作成、C列に「××支店　合計」の行を作成したいのです。
＞可能でしょうか？

可能です。営業所名の下2桁が「合計」なら、「××支店　合計」の行であると解釈します。
全体シートのレイアウトの齟齬がないようにしたいため、
シート名「全体」のファイルをダウンロード可能な個所にアップしていただけませんでしょうか。
実績ファイルは、アップ不要です。

tatsu99 · Answer

１．変更のフォーマットの確認ですが
①．元データのC列以降が右へ１列ずつシフトし、C列に営業所が追加
②．集計先のB列以降が、右へ１列ずつシフトし、B列に営業所が追加
であってますか。

２．今回、営業所が追加とのことですが、これは、集計時、キーとして使用するのでしょうか。
現行は、元データの支店（B列）＋課（C列）が、集計先の支店（A列）＋課（B列）に一致する箇所へ集計しています。
変更後は
①案（営業所がきーにならない）
元データの支店（B列）＋課（D列）が、集計先の支店（A列）＋課（C列）に一致する箇所へ集計
②案（営業所もキーに含める）
元データの支店（B列）＋営業所（C列）＋課（D列）が、集計先の支店（A列）＋営業所（B列）＋課（C列）に一致する箇所へ集計
のどちらでしょうか。

tatsu99 · Answer

本件は
１．★元データのピボットから各課・営業所の個人シートへ集計
Public Sub 売上月単位集計()
https://ideone.com/ScYsiA

のことですね。
集計元のデータの各シートは、
"1部本年P"
"2部本年P"
"3部本年P"
で、各シートにC列の「営業所」の列が追加になるのでしょうか？
フォーマットがよくわかりません。
https://ideone.com/ScYsiA　の
Public Sub 売上月単位集計()
ではないような気がしますが、本当にこのマクロが修正対象ですか。

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

下記にアップしました。

>85、91、97、109行目にあるような

>Ｂ案でお願いいたします。

＞>東京支店 合計（黄色）は、B列の項目の合計と考えられるので、B列＋C列を結合セルにして表示すべきと考えますがいかがでしょうか。

>C列に東京支店、仙台支店、名古屋支店、大阪支店の合計が2つ存在します。

＞ご提案頂きました、②案でお願いできましたら幸いです。

１．変更のフォーマットの確認ですが

本件は

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

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

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

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

＞>東京支店　合計（黄色）は、B列の項目の合計と考えられるので、B列＋C列を結合セルにして表示すべきと考えますがいかがでしょうか。