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

Question

tatsu99様

ご無沙汰しております。
いつも大変お世話になっております。
その節は何度もご教授頂きましてありがとうございました。

 以前ご教授頂いたマクロを修正しなければいけなくなってしまい
 お忙しいとは存じますが、再度ご教授頂きたく投稿いたしました。
↓
 ★元データのピボットから各課・営業所の個人シートへ集計
Public Sub 売上月単位集計()
https://oshiete.goo.ne.jp/qa/9963340.html

★各課・営業所の個人シートを別ファイルの全体集計表へ集計(チェック用)
Public Sub 全体数量集計()
https://oshiete.goo.ne.jp/qa/9902092.html

★「拠点計」シートへ粗利を集計
Public Sub 拠点別粗利集計()
https://oshiete.goo.ne.jp/qa/10129886.html

各課・営業所の個人シートと別ファイルの集計表のフォーマットを今期から変更することになり
修正が必要となりそうです。

◆現在確定していること◆
①3部と1部の集計項目(行)を増やしたい
現時点、このアバウトな要望しか決まっておらず
具体的にどのくらい項目数を増やすのかまでは未確定です。

②これまで新規、既存を設けておりましたがこれをすべて既存として集計したい。
※元データのピボットの作業列「新規」の項目を削除予定です。

 行、列の増減があった場合、マクロのどの部分を変更したら良いか？等含め
 ご教授頂ければ幸いです。

 今、確定していること以外の詳細については今月から来月中に確定し、できれば7月中後半から8月にかけてテストをして運用したいと考えております。

確定次第、再度質問投稿させて頂こうと思っております。
お時間的にご対応頂くことが可能でしたら是非お願いしたいと思っております。
tatsu99様のご都合お伺いしたく。。

いつもお手数おおかけして大変恐縮ではございますが
何卒、ご検討くださいます様お願いいたします。

tatsu99 · Accepted Answer

以前、下記URLの質問で
https://oshiete.goo.ne.jp/qa/10379569.html
ダミー社員を登録した時、添付図のようなエラーが出るはずだが、それがでないという現象がありました。
その原因がわかりました。
結論からいいますと、あなたが、マクロをエラーがでないように改造されていたためです。
Public Sub 売上月単位集計()
の146行目からが以下のようになっています。
-------------------------------
'１つのシートを更新する
Private Sub Update1Sheet(ByVal bookname As String, ByVal ws As Worksheet)
    '支店名＋課名＋担当者名
    trg_key = ws.Cells(2, "C").Value & "|" & ws.Cells(3, "C").Value & "|" & ws.Cells(4, "C").Value
    '1部,2部,3部のピボットシート内に担当者の集計月の行が１件もない場合は、警告メッセージを出力し、その担当者をスキップする
    If HON1dicT.exists(trg_key) = False And HON2dicT.exists(trg_key) = False And HON3dicT.exists(trg_key) = False Then
        'warnP = warnP & trg_key & vbLf'①
        'warnP = warnP & bookname & "(" & ws.name & ")" & trg_key & vbLf '②
        'Exit Sub コメントアウト
    End If
    '集計月に対応するカラム位置を取得
    trg_col = GetColNumber(trg_mm)
    '個人シートに値を設定する
    Call set_value(bookname, ws, trg_mode)
End Sub
-------------------------------
上記の②の部分がコメントになっています。
以下のようにしてください。（②の行を生かします）
        warnP = warnP & bookname & "(" & ws.name & ")" & trg_key & vbLf '②

そうすれば、エラーメッセージが表示されるようになります。

tatsu99 · Answer

>ご提示いただいた確認事項、すべて成立しております(;O;)

確認、ありがとうございました。とりあえず、この件は、これで終わりにします。

tatsu99 · Answer

>今回、ご教授頂いた内容は下記へ変更ということですよね？
>下記へマクロ変更後、ダミー社員のシートがあるブックでtestしましたが
>画像添付頂いたようなエラーがでませんでした。
>何度も申し訳ありません、私のほうでの認識が違っておりましたらご指摘頂ければ幸いです。

マクロの変更箇所はあっています。
そうであれば、必ず添付画像のようなエラー（実際は警告です）が出るはずですが・・・・。
前回のときと同じような状態に戻ってしまったような気がします。
念の為、確認ですが
①修正したマクロを実行した。（Public Sub 売上月単位集計()）
②ピボットデータ（入力データ）にダミー社員は存在しない。
③ダミー社員が存在するのは、計画フォルダ（F2で指定したフォルダ）内のブックである。
④ダミー社員のシートは、”合計”、”拠点計”のシートより左側にある。
⑤ダミー社員のシートのC2,C3,C4は、支店名、課名、社員名が設定されている。（添付図の赤線で囲んだ箇所）
⑥マクロを実行した結果、ダミー社員のあるブックは更新されている。（エクスプローラに表示される「更新時刻」が新しくなる）
上記の全てが成立していますか？
何れかがNGなら、それが原因です。
もし、全てが成立するなら原因不明で、結局前回と同じになりますが、
これ以上深追いせず、とりあえず打ち切り、様子を見たいと思います。

tatsu99 · Answer

原因がわかりました。
結論からいうと、変更前でも、変更後でも同じ結果になります。
変更前のマクロは個人シートの１０月のデータ（一番左側）を７月のデータと認識して、そのデータを取得し、
全体集計表の10月のデータ（一番左側）へ7月のデータとして設定しています。

変更前のマクロは個人シートの１０月のデータ（一番左側）を１０月のデータと正しく認識して、そのデータを取得し、
全体集計表の10月のデータ（一番左側）へ１０月のデータとして設定しています。

従って、変更前のマクロでも月によるシートの位置が入力側と出力側が共に変わるので、正しい結果が得られます。

しかしながら、マクロは正しく修正しておいた方が良いので、Ver1修正を行っておいてください。
こちらも、Ver1修正を行い、それを原本とします。

tatsu99 · Answer

>比較してみましたが、一部の課・営業所の数値を比較いたしましたが
>各月の数値に変動がありませんでした。

こちらでも、Private Function GetColNumber(ByVal mm As Long)を変える前と変えた後で
実行した結果を比較しましたが、差異が発生しませんでいた。

この件、こちらで再度詳細に再確認しますので、そのままにしておいてください。
進展があれば、連絡いたします。

tatsu99 · Answer

>ちなみに、この箇所を変更しておりませんでしたが
>問題なく集計できていたと認識をしておりましたが
>正しく集計できていなかったということでしょうか？

すみません。あなたがアップした Public Sub 全体数量集計()ですが、
間違えて修正前の古いマクロをアップしてしまったということはありませんか。（念のための確認です）

tatsu99 · Answer

>ちなみに、この箇所を変更しておりませんでしたが
>問題なく集計できていたと認識をしておりましたが
>正しく集計できていなかったということでしょうか？
はい。おそらく7月開始の前提で動作しますので、１０月の表示箇所（一番左側の月）に7月の結果が格納されるかと思います。
Public Sub 全体数量集計()
https://oshiete.goo.ne.jp/qa/9902092.html
で、このURLをみると開始月が7月になっています。

そのあと、開始月を10月にするにはどうするかの質問を受けて回答したような気もしますが失念しました。

念のため、現行のまま実行した結果の10月分の内容と修正を行って実行した結果の10月分の内容を比較したほうが
間違いがないかと思います。

tatsu99 · Answer

比較が完了しました。
こちらのマクロと比較して、変更がそこそこ行われておりますので、
あなたから提供されたマクロを原本として、そちらをベースに改造を行います。
（但し、Public Sub 全体数量集計()については、インデントが乱れていますのでインデントの修正はこちらで行います）

一点、下記の確認をさせてください。
年度の開始月を7月から10月に変更し、その対応を行っていますが、
Public Sub 売上月単位集計()と
Public Sub 拠点別粗利集計()は正しく10月から開始となっています。
しかしながら、Public Sub 全体数量集計()については、7月開始のままになっています。

以下の箇所です。（Public Sub 全体数量集計()の195行より）
---------------------------------------
 '指定月から指定月対応のカラム位置（前年データ）を計算する
'カラム位置は1からの連番
'7月=6 ... 6月=81
 Private Function GetColNumber(ByVal mm As Long)
 Dim x, ix As Long
 If mm < 7 Then mm = mm + 12
 x = mm - 7
 ix = x + x \ 3 + x \ 6
 GetColNumber = 6 + 5 * ix
 End Function
----------------------------------------
上記で問題ないのでしょうか？

もし10月開始であれば、以下のようになるはずです。(Ver1が修正部分）
------------------------------------------------
'指定月から指定月対応のカラム位置（前年データ）を計算する
'カラム位置は1からの連番
'7月=6 ... 6月=81
'10月=6 ... 9月=81                  'Ver1修正
Private Function GetColNumber(ByVal mm As Long)
    Dim x, ix As Long
    'If mm < 7 Then mm = mm + 12
    If mm < 10 Then mm = mm + 12    'Ver1修正
    'x = mm - 7
    x = mm - 10                     'Ver1修正
    ix = x + x \ 3 + x \ 6
    GetColNumber = 6 + 5 * ix
End Function
ｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰ
本件、現状のままで良いかどうか確認をお願いします。

tatsu99 · Answer

>ご教授頂きましたマクロ添付の手順に沿ってアップいたしました。
>下記URLとなります為、ご確認の程よろしくお願いいたします。

アップされたマクロのダウンロードが完了しました。
これから比較を行います。結果は、後程連絡いたします。

tatsu99 · Answer

こちらでマクロを提示した後に、あなたのほうでマクロの修正を行われていますので、
作業に着手する前にお互いに現在保持しているマクロの最新版を比較したいと考えています。
（原則として、今回はあなたが保持しているマクロに対して修正を加えたほうがミスが最小限に防げると考えています。
但し、ソースのインデントが正しくない場合は、私の方のマクロに対して修正を加えます。）

こちらで保持しているマクロの最新版は、以下のURLです。
Public Sub 売上月単位集計()
https://ideone.com/ADipKz

Public Sub 全体数量集計()
https://ideone.com/d3W5Bm
(マクロ名はとりあえず「全体集計」になっています)

Public Sub 拠点別粗利集計()
https://ideone.com/UIwzHy

あなたが保持しているマクロの最新版を同じようにURLにアップしていただけますでしょうか。
以下の方法で行ってください。

-----------------------------------------
１．下記URLを表示する。
https://ideone.com/

２．言語を選択する
下の部分の一番左のボタンををクリックして、言語を選択します。
Textを選択してください。（添付図の青線で囲んだ箇所を参照）
（青線のボタンをクリックして赤線の箇所のTextを選択します）

３．余分な文字を削除
添付図の黄色の部分に余分な文字があるので削除します。
（your text goes here等）

４．マクロの貼り付け
Public Sub 売上月単位集計()
Public Sub 全体数量集計()
Public Sub 拠点別粗利集計()
excelから上記の何れかのマクロをコピーし、添付図の黄色の箇所へ張り付けます。

５．マクロの保存
下の一番右側のRunボタンをクリックします。
（添付図の右側の青線の部分を参照）

６．URLのメモ
マクロが保存され、新規のURLが表示されるのでそれをメモします。
-------------------------------------------
上記の１～６を３つのマクロ分繰り返します。（但し２．については言語がTextになっていれば毎回行う必要はありません）

もし、マクロ中に公開すると都合の悪い情報があるならXXXX等の文字に変更してください。
但し、変更可能なものは以下の２つに限られます。（下記以外に公開すると都合の悪い情報があるなら連絡ください）
１．コメント内の文字
２．フォルダの定義等の文字（以下の例を参照）
    Const KeiFolder As String = "d:\goo\excel\goo191"   '計画フォルダ名（支店別＆課別（個人データ）ブックの格納フォルダ）

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

以前、下記URLの質問で

>ご提示いただいた確認事項、すべて成立しております(;O;)

>今回、ご教授頂いた内容は下記へ変更ということですよね？

原因がわかりました。

>比較してみましたが、一部の課・営業所の数値を比較いたしましたが

>ちなみに、この箇所を変更しておりませんでしたが

>ちなみに、この箇所を変更しておりませんでしたが

比較が完了しました。

>ご教授頂きましたマクロ添付の手順に沿ってアップいたしました。

こちらでマクロを提示した後に、あなたのほうでマクロの修正を行われていますので、

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

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

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

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

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