元データ(ピボッド)を開かずマクロで更新したい(続き)

下記より質問させて頂いた内容の続きになります。(補足上限達した為)
https://oshiete.goo.ne.jp/qa/9768603.html

---------------------------------------------------------------------------------------------------------tatsu99さまよりご回答いただいた件、回答いたします。
↓
>>№37で実行しましたところ、こちらで記載頂いたエラーがでました。。。
>では、本当に、指定されたファイルがないということになります。
>拡張子も含めて、合ってますか。
>管理シートでAAA.xlsm を指定し実際のファイルはAAA.xlsxだったなどということはないでしょう>>か。
→拡張子は相違ありません。

>もし、間違いなければ、
>あなたが指定したファイルが間違いなく存在するというエビデンス（証拠）の提示をお願いします。
>添付図の①のように、管理シートで、指定したD2,E2の内容の画像
>添付図の②のように、上記のファイルが存在するディレクトリに移動し、
>コマンドプロンプトで、dir を実行し、指定したファイル名が表示されることの確認。
>（特に、赤字で囲んだ箇所が、間違いないか確認し、その箇所を含んだ画像を提示しください）
>鮮明な画像の提示をお願いします。
>第三者がみれば、間違いに気づく可能性があります。
> 操作で不明な点があれば、補足ください。
→指定したファイルが表示されませんでした。
画像添付いたしますので、ご確認願います。
---------------------------------------------------------------------------------------------------------

質問者からの補足コメント

• コマンドプロンプトの画像添付いたします。
  ※すみません、PC情報となる箇所は黒く塗りつぶしています。

  
    補足日時：2017/06/07 11:36

• №4で№5の質問をしております。
  文字数の兼ね合いでこちらに続きを記載いたします。
  
  ②の件
  実行時エラー’53’
  ファイルが見つかりません。
  
  '売上明細ブック名設定
  Uri_path = Jis_folder & "\" & Uri_book
  MsgBox ("ファイル名<" & Uri_book & ">") '追加①
  MsgBox ("フルパス名<" & Uri_path & ">") '追加②
  MsgBox (GetAttr(Uri_path)) '追加③

  No.5の回答に寄せられた補足コメントです。 補足日時：2017/06/08 17:16

• ※コマンドプロントの画像添付します。

  
  No.15の回答に寄せられた補足コメントです。 補足日時：2017/06/09 10:07

• サンプル画像添付します。

  
  No.24の回答に寄せられた補足コメントです。 補足日時：2017/06/14 15:54

No.25 ベストアンサー 回答者： tatsu99 回答日時： 2017/06/14 20:29

>①〇〇支店　〇〇課　やまだ

>元データ上、2016年7月～2017年3月まで実績あり
>シート追加した1部、2部、3部の行列番号確認しましたところA列に①の該当する行列番号のみ表示あり
この「A列に①の該当する行列番号のみ表示あり」とは、どういう意味でしょうか。
追加した1部のシートのA列に①の名前があり、その行のB列以降に行番号があったとという意味でしょうか。
そうであれば、その月のデータがピボットにあったということになります。

１点、気になったのですが、「〇〇支店　〇〇課　やまだ」と「〇〇支店　△△営業所　やまだ」のシートは、
前年のものを使い回ししていますか。（つまり、7月に新規のシートを作るとき、前のデータが残ったまま使ってますか）
もし、そうであれば、前年の残骸が残っている可能性があります。
「下記従業員は、1部,2部,3部の何れのピボットにも集計月のデータなし」このメッセージが表示されたとき、
〇〇支店　〇〇課　やまだ
〇〇支店　△△営業所　やまだ
の2人も表示されていると、その集計月のその2人のシートは更新されません。
（０も空白も設定されません）
従って、前の集計値が残った状態になります。

マクロを以下のように変えることにより、その2人のシートのその集計月を明示的に空白（0）を設定することができます。

-------------------------------------
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
--------------------------------------
Private Sub Update1Sheetの中の
Exit Subを上記のようにコメントアウトしてください。
そうすると、その集計月をデータがなくても打ち切らず、空白を設定します。

これで改善しない場合は、再度補足ください。

この回答へのお礼

> この「A列に①の該当する行列番号のみ表示あり」とは、どういう意味でしょうか。
> 追加した1部のシートのA列に①の名前があり、その行のB列以降に行番号があったとという意味でしょうか。
>そうであれば、その月のデータがピボットにあったということになります。
→わかりづらい説明で申し訳ありません。
　おっしゃる通り、A列に①の名前があり、その行のB列以降に行番号が出てきました。

> １点、気になったのですが、「〇〇支店　〇〇課　やまだ」と「〇〇支店　△△営業所　やまだ」のシートは、
> 前年のものを使い回ししていますか。（つまり、7月に新規のシートを作るとき、前のデータが残ったまま使ってますか）
>もし、そうであれば、前年の残骸が残っている可能性があります。
>「下記従業員は、1部,2部,3部の何れのピボットにも集計月のデータなし」このメッセージが表示されたとき、
> 〇〇支店　〇〇課　やまだ
>〇〇支店　△△営業所　やまだ
>の2人も表示されていると、その集計月のその2人のシートは更新されません。
>（０も空白も設定されません）
>従って、前の集計値が残った状態になります。
→月でシートを追加しているわけではないですし、使いまわしもしていませんが
　前の集計値が残ってしまっていたということですね。。
　再度、ご教授頂いたマクロに修正しましたところ正しく集計できました。
　何度もお手数をおかけして申し訳ありませんでした。
　また、2017年実績のほうも何度か検証をして、正しく集計できない原因を探っていき無事解決いたしました。
　本当に何度もやりとりにお時間を頂戴してしまい、申し訳ありませんでした。
　とても助かりました。ありがとうございます。

お礼日時：2017/06/15 10:38

No.26 回答者： tatsu99 回答日時： 2017/06/15 10:58

>再度、ご教授頂いたマクロに修正しましたところ正しく集計できました。

>　何度もお手数をおかけして申し訳ありませんでした。
>　また、2017年実績のほうも何度か検証をして、正しく集計できない原因を探っていき無事解決いたしました。

そうすると、これで、全て解決でしょうか。
管理シートのD2,E2セルから実績ファイルを読み込むようにしても、問題なく読み込みできますか。
その点が、問題なければ、全て完了になるかと思います。

この回答へのお礼

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

管理シートの内容を読み込んでエラーなく処理が今はできているので
以前の問題点は解消されたと認識しております。

本当にお手数をおかけしました。
感謝いたします。
ありがとうございました。

お礼日時：2017/06/15 11:05

No.24 回答者： tatsu99 回答日時： 2017/06/12 23:21

No23の続きですが、集計月の各担当者のデータが、ピボットの何行目にあるのか、その一覧を表示できるようにしました。

まず、マクロのあるブックに、1部、２部、３部のシートを追加してください。（添付図の赤まる部分参照）
（1,2,3は半角です）
次に、以下の行を追加してください。
Public Sub 売上月単位集計()の下の方に
'ピボットデータ読み込み
Call readPivot(HON1sheet, HON1dicT, HON1Rows)
Call readPivot(HON2sheet, HON2dicT, HON2Rows)
Call readPivot(HON3sheet, HON3dicT, HON3Rows)
Call OutPivot 'ピボット情報出力・・・・・追加①
'計画フォルダ内の全ブックを更新する
Call UpdateAllBooks(Kei_folder)

上記の追加①が追加部分になります。

更に、マクロの最後に、以下のマクロを追加します。
-----------------------------------------------------
'ピボット情報を出力する
Private Sub OutPivot()
Call OutPivotUnit("1部", HON1dicT, HON1Rows)
Call OutPivotUnit("2部", HON2dicT, HON2Rows)
Call OutPivotUnit("3部", HON3dicT, HON3Rows)
End Sub
'部単位でピボット情報を出力する
Private Sub OutPivotUnit(ByVal sheetName As String, ByVal dicT As Object, ByRef TRows() As Long)
Dim ws As Worksheet
Dim key As Variant
Dim row, col, ix, ctr, i, gno As Long
ThisWorkbook.Activate
Set ws = Worksheets(sheetName)
ws.Cells.Clear
row = 0
'全てのキーを処理する
For Each key In dicT
ix = dicT(key)
row = row + 1
'キー印字
ws.Cells(row, 1).Value = key
ctr = TRows(ix, 0)
'行番号印字
For i = 1 To ctr
gno = TRows(ix, i)
col = i + 1
ws.Cells(row, col).Value = gno
Next
Next
End Sub
-----------------------------------------------
上記を実行すると、１部、２部、３部にピボットの行番号が表示されます。添付図参照
１部には１部本年Pのシートの行番号が表示されます。（他も同様）
A列はキー（支店名｜課名｜担当者名）が表示されます。
B列及びそれ以降は、ピボットの行番号が表示されます。（青まる部分）
重複したデータ等を調査するときに活用できれば幸いです。

この回答へのお礼

お返事が遅くなり申し訳ございません。
ご教授頂いたマクロ追加の上、再度検証してみました。
↓
①〇〇支店　〇〇課　やまだ
元データ上、2016年7月～2017年3月まで実績あり
シート追加した1部、2部、3部の行列番号確認しましたところA列に①の該当する行列番号のみ表示あり

②〇〇支店　△△営業所　やまだ
元データ上、2016年4月～2016年6月まで実績あり
シート追加した1b、2部、3部の行列番号確認しましたところA列に②の該当する行列番号のみ表示あり

上記より、ピボッドの参照先は正しいと思います。

ただ、その集計先の支店別ブックの中身が3部は1つの月(赤字)を除き、数字が重複しており
1部に関しては重複しているような数字ではなく、いろいろと調べてみたのですがどの数字を参照しているのかわかりかねている状況です。。。

画像添付いたします。
黄色の部分は、元データのピボッドにあるので正しい数値です。
グレーの部分は、元データのピボッドにないが支店別のブックには表示されている数値です。
※2部に関しては実績なし。

何度も申し訳ございませんが、他に原因が考えられるようでしたら
ご教授頂けますと幸いです。

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

お礼日時：2017/06/14 15:41

No.23 回答者： tatsu99 回答日時： 2017/06/12 18:16

>〇〇支店　一課　やまだ

>〇〇支店　△△営業所　やまだ
>理由としては、年途中で異動になった為データ上、それぞれの課で売上が
>発生している状況です。
>集計後のデータを確認したのですが、その人の分が重複しているように見受けられます。
>ただ、私がお伝えした条件で作成いただいたマクロを拝見する限りピボッドは支店、課名、担当者で抽出できるようになっていると認識しております。
>元データのピボッドは何もいじっていないので、他に原因は何が考えられますでしょうか？
回答：支店、課名、担当者で抽出します。従って、上記のケースの「やまだ」は、別々に集計します。

確認ですが、上記のようなケースの場合、「やまだ」は別々のブックに格納されていますか。
①○○支店・一課のブックの「やまだ」のシートの内容
C2＝○○支店
C3＝一課
C4＝やまだ

②○○支店・△△営業所のブックの「やまだ」のシートの内容
C2＝○○支店
C3＝△△営業所
C4＝やまだ
上記のようになっていることが前提です。


又、「やまだ」が２０１６年３月末まで、①に所属
２０１６年４月１日以降は②に所属とすると
ピボットは２０１６年３月末までは、○○支店、一課、やまだ（①の状態）
２０１６年４月１日以降は、○○支店、△△営業所、やまだ（②の状態）
となっていますか？

この回答へのお礼

>確認ですが、上記のようなケースの場合、「やまだ」は別々のブックに格納されていますか。
>①○○支店・一課のブックの「やまだ」のシートの内容
>C2＝○○支店
>C3＝一課
>C4＝やまだ

>②○○支店・△△営業所のブックの「やまだ」のシートの内容
>C2＝○○支店
>C3＝△△営業所
>C4＝やまだ
>上記のようになっていることが前提です。
→おっしゃる通り、別ブックの中にそれぞれシートがありますので
　別々に集計されるという認識でした。


>又、「やまだ」が２０１６年３月末まで、①に所属
>２０１６年４月１日以降は②に所属とすると
> ピボットは２０１６年３月末までは、○○支店、一課、やまだ（①の状態）
>２０１６年４月１日以降は、○○支店、△△営業所、やまだ（②の状態）
>となっていますか？
→実際、ピボッドの元データを見てみると①の所属の時に実績がないはずなのに
数値が入っている月がありました。№24でご教授いただいたマクロに修正し、再検証させていただきます。
お手数をおかけして申し訳ありませんでした。

お礼日時：2017/06/14 10:49

No.22 回答者： tatsu99 回答日時： 2017/06/09 12:24

>先ほど質問させていただいたエラーがでなくなりました。

>実際先ほどエラーが出ていた担当者ですが元データに実績がありましたのでエラーが消えたのでしょうか？
回答：
はい、そうなります。
大阪支店|○○支店|△支店
のようなエラーがでるのは、
担当者名が、△支店 はダメだと言っているのではありません。
マクロは、担当者が　△支店 さんだと思って、その担当者（△支店）をピボットから探しにいきます。
そうすると、当然ないので、なかったというエラーを表示しています。
マクロは、課名、担当者名が適切な名称なのかの判断は、できません。
C2を支店名、C3を課名、C4を担当者名と思って処理します。

＞別件になりますが、２０１７実績の元データを2016実績と同じように処理をしたのですが
＞実績欄へ反映されません。
＞処理は完了と出ます。管理シートの前年、実績選択する欄もちゃんと実績にしております。
＞原因は何が考えられますでしょうか？
回答：エラー表示がでないなら、ピボットに該当月のデータがあるが、集計する為の条件にマッチしたデータがなかったことが原因です。（3部ならG列、H列の値が集計条件にマッチしない）

この回答へのお礼

ご返信が遅くなり申し訳ありません。
エラーの件、理解いたしました。
ご教授いただきありがとうございます。
すみません。。まだ、2017年実績の件は検証中なのですが。。。

別件なのですが、ご教授願います。
支店名は同じなのですが課名が違うファイルそれぞれに同じ担当者シートが存在しています。

例）
〇〇支店　一課　やまだ
〇〇支店　△△営業所　やまだ

理由としては、年途中で異動になった為データ上、それぞれの課で売上が
発生している状況です。

集計後のデータを確認したのですが、その人の分が重複しているように見受けられます。
ただ、私がお伝えした条件で作成いただいたマクロを拝見する限りピボッドは支店、課名、担当者で抽出できるようになっていると認識しております。
元データのピボッドは何もいじっていないので、他に原因は何が考えられますでしょうか？

お礼日時：2017/06/12 16:52

No.21 回答者： tatsu99 回答日時： 2017/06/09 11:46

>②の部分については個人シートのC2,C3,C4にありますが、何が理由で表示されるのかを知りたいです。

実際にそのように内容が格納されているシートがあるということです。
添付図のように
C3に○○支店
C4に△支店
と設定されていれば
大阪支店|○○支店|△支店
と表示されます。
つまり、課名を設定すべきところに、支店名が記入されています。
又、担当者を設定すべきところに、支店名が記入されています。

このシートがどのブックのどのシートかの特定が難しい場合は、No20のようにしてください。
ブック名とシート名が左側に表示されます。

この回答へのお礼

再度ご教授いただきましてありがとうございます。
ちなみにですが、№２１でご教授いただいた内容を下記のように反映させましたところ
先ほど質問させていただいたエラーがでなくなりました。
実際先ほどエラーが出ていた担当者ですが元データに実績がありましたのでエラーが消えたのでしょうか？
↓
'１つのシートを更新する
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

別件になりますが、２０１７実績の元データを2016実績と同じように処理をしたのですが
実績欄へ反映されません。
処理は完了と出ます。管理シートの前年、実績選択する欄もちゃんと実績にしております。
原因は何が考えられますでしょうか？

お礼日時：2017/06/09 12:08

No.20 回答者： tatsu99 回答日時： 2017/06/09 10:56

このエラーが出たときに、エラー箇所のブック名とシート名も表示したいなら、

以下のようにします。

'１つのシートを更新する
Private Sub Update1Sheet(ByVal bookname As String, ByVal ws As Worksheet)のなかの
'warnP = warnP & trg_key & vbLf '①
warnP = warnP & bookname & "(" & ws.name & ")" & trg_key & vbLf '②


Update1Sheetの中の①をコメントにして②の行を追加します。
そうすると、添付画像のように、ブック名とそのブックのシート名が左側に表示されます。

No.19 回答者： tatsu99 回答日時： 2017/06/09 10:42

以下の意味は

>②○○支店　○○支店　○○支店
>　担当者名　山田　　　田中
>　担当者名　佐々木　　小川

１）表示が
○○支店|○○支店|○○支店
のように並んでいるのはなぜか？という質問ですか。
２）それとも
○○支店の山田は、集計月のデータがあるのに、何故、このエラーがでるのか？　という質問ですか。

１）の場合の回答
各個人シートのC2,C3,C4内容を
C2|C3|C4
のように表示しています。
個人シートのなかにそのように表示される箇所があるので、それを確認してください。

２）の場合の回答
○○支店の山田は、集計月に該当するデータ1部,2部,3部の何れにもありません。再確認してください。
その場合、
○○｜△△｜山田
のように表示されるので、○○、△△も含めて確認してください。

この回答へのお礼

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

１）の質問でした。
理解不足で申し訳ありません。
確かに個人のシートのC2,C3,C4に支店名、課名、担当者名がありますが
①のエラーと何が違うのでしょうか？
エラーですが、私が記載しましたとおり①と②が連続で出てきます。
①については該当データがないのでその該当者が表示されるのはわかりますが
②の部分については個人シートのC2,C3,C4にありますが、何が理由で表示されるのかを知りたいです。

お礼日時：2017/06/09 11:33

No.18 回答者： tatsu99 回答日時： 2017/06/09 10:14

>マクロで読み込ませるとそれは.xlsxと認識される為、エラーが出てしまっていたと考えます。

>念の為、画像添付させて頂きます。
回答：画像を見ましたが、良くわかりませんでした。
でも、拡張子が2重になっていたことが、判明したので、それで解決とします。

＞また、2017(本年)実績データを読み込ませると2016(前年)実績データでは現れない表示が出てきました。
＞この表示内容はどのような意味でしょうか？
＞上の部分のエラーはその月に実績がないのでと理解できるのですが、下の部分のエラーについては上の内容と表示内容が異なるので恐れ入りますが、ご教授頂ければ幸いです。
回答：どのようなエラーでしょうか。具体的な説明をお願いします。
正確なエラーメッセージの提示をお願いします。

この回答へのお礼

度々、恐縮です。
下記、記載いたしました。
恐れ入りますが、ご教授頂けますと幸いです。

>>マクロで読み込ませるとそれは.xlsxと認識される為、エラーが出てしまっていたと考えます。
>>念の為、画像添付させて頂きます。
>回答：画像を見ましたが、良くわかりませんでした。
>でも、拡張子が2重になっていたことが、判明したので、それで解決とします。
→№17にてご教授頂き、無事解決いたしました。本当にありがとうございます。

＞＞また、2017(本年)実績データを読み込ませると2016(前年)実績データでは現れない表示が出てきました。
＞＞この表示内容はどのような意味でしょうか？
＞＞上の部分のエラーはその月に実績がないのでと理解できるのですが、下の部分のエラーについては上の内容と表示内容が異なるので恐れ入りますが、ご教授頂ければ幸いです。
>回答：どのようなエラーでしょうか。具体的な説明をお願いします。
>正確なエラーメッセージの提示をお願いします。
→画像を添付しょうとしたのですが、どうしても小さく不鮮明になってしまう為、下記記載いたしました。
ご確認願います。
----------------------------------------------------------------------------
下記従業員は、1部、2部、3部の何れのピボットにも集計月のデータなし。
①○○支店　一課　田中
　△△営業所　二課　山田
・
・
l l
l l
②○○支店　○○支店　○○支店
　担当者名　山田　　　田中
　担当者名　佐々木　　小川

①は該当月にデータがないので集計できなかったということは理解しているのですが
②に関してはわかりかねてしまった為、ご教授頂ければ幸いです。

お礼日時：2017/06/09 10:23

No.17 回答者： tatsu99 回答日時： 2017/06/09 10:07

＞なぜか、エクセルファイルで保存すると拡張子が付いたり付かなかったりするのですが、もちろん保存するときは

＞.xlsxで保存しているのですが表示上拡張子が現れず、私が手入力しておりました。

あなたのPCの設定が拡張子を表示しない設定になっています。
拡張子を表示する設定にしてください。
「拡張子を表示」で検索すればいろいろ出てきます。
下記URLを参考にしてください。
https://support.microsoft.com/ja-jp/help/978449
http://qa.nifty.com/cs/catalog/faq_nqa/qid_15385 …

この回答へのお礼

何から何までご教授頂きありがとうございます。
無事、解決いたしました。

お礼日時：2017/06/09 10:18