【マクロ変更希望】元データ(ピボッド)を開かずマクロで更新したい。

tatsu99様

先日も再度ご教授頂きありがとうございます。
度々で恐縮ではございますが、再度ご教授願います。

前回、下記にてお伺いした質問と同じように集計をしたいのですが
前回と大きく違う点は、個人別での集計ではない為、各ブックの「拠点計」へ一括で集計をしたいです。
また、前回のフォーム、集計項目と若干異なる為、再度ご教授頂ければと思いまして
質問させて頂きました。
↓
https://oshiete.goo.ne.jp/qa/9768603.html?pg=1&i …

拠点別のブックの中に「拠点計」というシートがあります。
添付したフォームになっており、今回実績の計欄へピボッドのデータで条件の合致した「粗利」の数値をを集計したいのです。
集計条件は下記の通りとなります。

部門C2、所属C3(ピボッドではD、E列を参照)の月別のA列の各数値の集計をしたいです。
ひと月、計画：数量、単価、計、実績：数量、単価、計の合計6行。
月の並びは10、11、12月、1Q、1、2、3月、2Q、上期、4、5、6月、3Q、7、8、9月、4Q、下期、年間
D列からDM列までです。
ちなみに、1～4Q、上期、下期、年間計は数式をもともと入れてありますのでそれ以外の実績の計を
マクロで集計したいです。

・3部
フォームのA列、1～8をピボッドの作業列F列を参照してJ列の粗利を集計。
・1部
フォームのA列、1.2(18行目、19行目)をピボッドのF列を参照してK列の粗利を集計。
フォームのA列、1(20行目)をピボッドのG列を参照してK列の粗利を集計
・2部
フォームのA列、4.5.6ををピボッドのB列を参照してH列の粗利を集計。

お手数をおかけいたしますが、ご教授願います。

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

• 

  ピボッドのサンプルを添付します。

  
    補足日時：2017/12/11 15:25

• > 質問④
  >3部の計（I列）へ加算するとき、合計の行（17行）へも加算しますか。（マクロで算出）
  >それとも、この箇所は、関数で求めますか。
  >又、全体計（26行）も、同様です。（合計が関数なら全体計も関数、合計がマクロなら全体計もマクロ）
  →17行目、21行目、25.26行目はすべて関数で数式を入力しているのでマクロで算出しなくても大丈夫です。

  No.3の回答に寄せられた補足コメントです。 補足日時：2017/12/13 10:39

• 失礼いたしました。
  お礼の文面にエラーを記載忘れてしまいましたのでこちらに記載いたします。
  ↓
  「1部ピボットのデータ不正　以下の情報をメモしてください」
  「261行G列」
  ※エラー発生場所を確認しましたところピボットの「総計」欄でした。

  No.7の回答に寄せられた補足コメントです。 補足日時：2017/12/15 13:59

• エラー発生対象の行の画像を添付します。

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

No.15 ベストアンサー 回答者： tatsu99 回答日時： 2017/12/18 19:12

下記URLへUPしました。

前回のは破棄してください。
https://ideone.com/35rsaN

３部はF列が空白のケースはエラーとします。
２部はB列が空白のケースはエラーとします。

この回答へのお礼

再度、修正頂きましてありがとうございます。
修正後のマクロ実行したのですが、やはり№12でご質問頂いたケース4がエラーになってしまいます。
> ケース4
>G列＝１　F列＝空白　対応：不明
→項目Kへ集計。

本当に何度も申し訳ございませんが、ご教授願います。

お礼日時：2017/12/20 10:39

No.19 回答者： tatsu99 回答日時： 2017/12/21 17:13

>'個人シートに値の設定を行う

>key = ws.Cells(2, "C").Value & "|" & ws.Cells(3, "C").Value & "|" & item
>この部分を修正しましたところ、無事エラー出すことなく集計完了することができました。

了解しました。

この回答へのお礼

お手数をおかけいたしました。
ありがとうございます。

お礼日時：2017/12/21 17:15

No.18 回答者： tatsu99 回答日時： 2017/12/21 16:26

>ただ、何度も申し訳ありません。

下記エラーがでるのですが原因として何が考えられますでしょうか？
>集計元のデータに集計先である拠点の該当する項目がなかったということでしょうか？
>もしそうであれば、該当する項目がない場合もあります。

添付図のようなエラーについてのことでしょうか。
例として、
仙台支店｜第二課｜I
と表示された場合は、例えば10月のデータ集計を行った時にでたなら
ピボット１部の10月に仙台支店、第二課の項目Iが集計対象となるデータ（＝F列が１のデータ）があると
いう意味です。そして、拠点計のシートに仙台支店、第二課がないという意味です。

尚、あなたが提示された
「集計元のデータに集計先である拠点の該当する項目がなかったということでしょうか？」
このケースの場合は、集計結果が（該当データがないので）空白になります。
この場合はエラーメッセージは表示されません。

この回答へのお礼

早速、ご回答いただきありがとうございます。
また、エラーの記載、画像の添付が漏れておりまして本当に申し訳ございません。

仰る通りのエラーの件で質問させて頂きました。
わかりやすいご説明、ありがとうございます。

その上で、再度ご教授頂いたマクロを確認しましたところ
私が質問をさせていただいた際の「拠点計」シートの「支店」、「営業所・課」の記載行が
本番用のものと1行ズレており、その為集計されなかったようです。
私のミスのせいで、お忙し中お手数をおかけしてしまい、申し訳ありませんでした。
↓
'個人シートに値の設定を行う
key = ws.Cells(2, "C").Value & "|" & ws.Cells(3, "C").Value & "|" & item

この部分を修正しましたところ、無事エラー出すことなく集計完了することができました。

今回は、いつにも増してエラーの画像添付、説明記載が漏れていたり質問の質が悪く
本当に申し訳ありませんでした。

お忙しい中、ご教授頂き感謝いたします。ありがとうございました。
また、何かありました際は何卒ご教授願います。
年末も近づき、寒い日が続きますがお体ご自愛くださいませ＾＾

お礼日時：2017/12/21 17:07

No.17 回答者： tatsu99 回答日時： 2017/12/21 10:28

ケース4がエラーになる件ですが、

F列がエラーになっていますか？（G列ではなく）
もしそうであれば、空白の部分ですが、あなたの関数で、空白を「" "」にしていませんか。
"と"の間が1桁あいています。


以下は、あなたが提示された空白ですが
>自分なりにいろいろ検証の為に、元データを見てみたのですがG列同じく空白「””」になっている(元データ上は関数が入
>力されています。ピボットでは空白「””」です)なのですが、黄色で色付けした人の行だけエラーが出てしまいます。

これは、あなたが提示された空白で"と"の間になにもありません。
マクロでは、こちらのデータが設定されている前提です。

①「" "」・・・・エラーになる空白
②「""」・・・・エラーにならない空白（マクロではこちらの空白がある前提）

もし、①に状態になっているなら、②に変えることは可能でしょうか。
もし、変えられないなら、マクロで対応します。（①も②もエラーにしないようにする）

この回答へのお礼

お返事いただいておりましたのに遅くなり、申し訳ございません。

こちらで確認しましたところ、おっしゃる通り関数が入っている空白が”と”となっておりました。
こちらに関しては、私のほうで関数を修正しましたところエラーがでなくなりましたので解決いたしました。
ありがとうございました。
そして、お騒がせいたしましたm(__)m

ただ、何度も申し訳ありません。下記エラーがでるのですが原因として何が考えられますでしょうか？
集計元のデータに集計先である拠点の該当する項目がなかったということでしょうか？
もしそうであれば、該当する項目がない場合もあります。
※そもそもある項目の実績がない場合。

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

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

お礼日時：2017/12/21 14:25

No.16 回答者： tatsu99 回答日時： 2017/12/20 10:59

>修正後のマクロ実行したのですが、やはり№12でご質問頂いたケース4がエラーになってしまいます。

>> ケース4
>>G列＝１　F列＝空白　対応：不明
>→項目Kへ集計。

このケースは対応済みです。こちらではエラーになりません。F列は関数式で空白（=""）を設定して確認済みです。
間違いなく、https://ideone.com/35rsaN
のマクロを使用されていますか。
もし、使用していてれば、２０５行目に以下のプロシージャがあるばすです。

'1部の粗利取得
Private Function get_arari1(ByVal sheet_name As String, ByVal sh As Worksheet, ByVal row As Long, ByRef item As String, ByRef arari As Variant) As Boolean

修正前は、
Private Function get_arari1　ではなく
Private Sub get_arari1 になっています。
上記の箇所を確認ください。

No.14 回答者： tatsu99 回答日時： 2017/12/18 14:39

念のため、追加の確認です。

３部はF列を参照しますが、F列が空白のケースはありますか。その場合は、どうしますか。
２部はB列を参照しますが、B列が空白のケースはありますか。その場合は、どうしますか。

この回答へのお礼

ご確認頂きましてありがとうございます。
下記回答いたします。

>３部はF列を参照しますが、F列が空白のケースはありますか。その場合は、どうしますか。
→空白のケースは基本的にはありません。ただし、空白のケースが発生する場合がなきにしもあらずですので
　エラーがでるように設定頂ければ幸いです。
　空白になるケースとして、元データでF列の参照先が3部の「抽出対象リスト」を参照しており
　VLOOKUPで検索するという数式の為、新しくリストにないものが発生した場合が考えられます。
　ですので、エラーがでるようにしていただけるとこちらでチェック漏れがなくなると思っています。

>２部はB列を参照しますが、B列が空白のケースはありますか。その場合は、どうしますか。
→こちらに関しては空白のケースはありません。
　4.5.6のみとなります。


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

お礼日時：2017/12/18 16:41

No.13 回答者： tatsu99 回答日時： 2017/12/18 14:21

>→ケース1、ケース4は上記で記載した通りです。

了解しました。
データが正しくて、かつそのデータを集計しないケース（G列空白かつF列空白）は、想定外のケースなので
マクロに見直しが必要になります。これから出かけますので回答は、夕方以降になります。

＞ケース5、ケース6は不要となります。
もし、このケースを故意に発生させた場合は、どうなればよいですか？
①エラーにする。
②項目Kへ集計する。
の何れかが考えられます。

この回答へのお礼

何度もお手数をおかけして申し訳ありません。
可能でしたら、明日中にご回答頂ければ大丈夫ですのでお仕事優先にされてくださいませ。
本当にすみません。。。

>＞ケース5、ケース6は不要となります。
>もし、このケースを故意に発生させた場合は、どうなればよいですか？
>①エラーにする。
>②項目Kへ集計する。
>の何れかが考えられます。
→この場合は、②でお願いいたします。

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

お礼日時：2017/12/18 14:35

No.12 回答者： tatsu99 回答日時： 2017/12/18 13:50

>>F列の値が

>>１，２、空白の3つケースがありますが、
>>F列が空白の場合、それをエラーとしないケースはG列が1の場合でしょうか。
>→はい、おっしゃる通りです。
>>G列が空白、かつF列も空白の場合は、どうしますか？（エラーにする、エラーにはしないが集計もしないがあります）
>→エラーにしないが集計もしないとしたいです。

あなたの回答が矛盾しています。

F列が空白の場合、それをエラーとしないケースはG列が1の場合でしょうか。
→はい、おっしゃる通りです。
これで、YESなら

G列が空白、かつF列も空白の場合は、どうしますか？
>→エラーにしないが集計もしないとしたいです。
「エラーにする」の回答しかありません。

混乱されているようなので再度、質問します。
前提
G列は１、空白のみ正常とする。
F列は１、２、空白のみ正常とする。

ケース１
G列＝空白　F列＝空白　対応：不明
ケース２
G列＝空白　F列＝１　対応：項目Iへ集計
ケース３
G列＝空白　F列＝２　対応：項目Jへ集計
ケース４
G列＝１　F列＝空白　対応：不明
ケース５
G列＝１　F列＝１　対応：項目Kへ集計
ケース６
G列＝１　F列＝２　対応：項目Kへ集計

不明なのは、ケース１、ケース４です。
ケース１、ケース４の場合、どうしますか？

この回答へのお礼

お手数をおかけしております。
私の条件設定の説明がちゃんとできていなかったようで申し訳ありません。
下記回答させて頂きます。

> 前提
> G列は１、空白のみ正常とする。
> F列は１、２、空白のみ正常とする。

> ケース１
> G列＝空白　F列＝空白　対応：不明
→エラーにもせず、集計もしない
> ケース２
> G列＝空白　F列＝１　対応：項目Iへ集計
> ケース３
> G列＝空白　F列＝２　対応：項目Jへ集計
> ケース４
> G列＝１　F列＝空白　対応：不明
→項目Kへ集計。
> ケース５
> G列＝１　F列＝１　対応：項目Kへ集計
> ケース６
> G列＝１　F列＝２　対応：項目Kへ集計
→ケース5、ケース6はなし

> 不明なのは、ケース１、ケース４です。
>ケース１、ケース４の場合、どうしますか？
→ケース1、ケース4は上記で記載した通りです。
ケース5、ケース6は不要となります。

説明不足で申し訳ありませんでした。
何卒、よろしくお願いいたします。

お礼日時：2017/12/18 14:08

No.11 回答者： tatsu99 回答日時： 2017/12/18 10:26

>この部分をどう集計すればよいのでしょうか？

>Call checkvalue2(sheet_name, sh, row, "F", 1, 2, False)

どうするか以前の話になりますが、
まず、
F列の値が
１，２、空白の3つケースがありますが、
F列が空白の場合、それをエラーとしないケースはG列が1の場合でしょうか。
G列が空白、かつF列も空白の場合は、どうしますか？（エラーにする、エラーにはしないが集計もしないがあります）

この回答へのお礼

ご返信頂きましてありがとうございます。
理解不足で申し訳ありません。
お手数をおかけしました。。。

>どうするか以前の話になりますが、
>まず、
>F列の値が
>１，２、空白の3つケースがありますが、
>F列が空白の場合、それをエラーとしないケースはG列が1の場合でしょうか。
→はい、おっしゃる通りです。
>G列が空白、かつF列も空白の場合は、どうしますか？（エラーにする、エラーにはしないが集計もしないがあります）
→エラーにしないが集計もしないとしたいです。

度々恐縮ではございますが
何卒、よろしくお願いいたします。

お礼日時：2017/12/18 13:25

No.10 回答者： tatsu99 回答日時： 2017/12/16 10:26

修正版を下記URLにUPしました。

前回のは、破棄してください。
https://ideone.com/aP3IlD

空白セルの扱いですが
何も設定されていないセルの場合は、正しく動作しましたが、関数式で""が設定されたセルの場合
正しく動作しませんでした。その箇所を修正しました。

尚、前回まで総計行が問題になっていましたが、これは、修正せずに、最後の行まで処理するようにしています。
For row = 3 To maxrow
のままです。

理由は、マクロで、最終行を求めるとき、A列が空白でない最大の行が、最終行であると判断している為です。
その為、添付のような図の場合、実際の総計行が２７行であっても、２６行が最終行（＝総計行）と判断する為です。
もし、A列の最後の行に「総計」の文字があるなら、正しく２７行が最終行（＝総計行）と判断します。

従って、どちらの場合でも、正しく処理する為には、現状通り
For row = 3 To maxrow
のままにしておく必要があります。

この回答へのお礼

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

総計行の件、承知いたしました。
わかりやすいご説明ありがとうございます。

また、G列の空白””の件は無事解決いたしました。
ありがとうございます。

ただ、今度はF列でエラーが出てしまいました。
G列の件があったので、再度ご質問頂き回答した内容を調べましたところ
tatsu99様より№5の質問4で質問頂いた際、私の回答が誤っていたことに気づきました。
おそらくそれが原因でF列のエラーが出てしまったのかと認識しております。
＞質問４
＞ピボットの１部のデータ行のF列は、1～2の何れかが必ず設定されていますか。（空白もなし）
→はい。と答えましたが実際は1~2の何れかと空白””(元データには関数が入っている)が設定されています。

この部分をどう集計すればよいのでしょうか？
Call checkvalue2(sheet_name, sh, row, "F", 1, 2, False)

本当に何度も何度も申し訳ございませんが
再度その部分ご教授頂けますと幸いです<(_ _)>

お礼日時：2017/12/18 10:10