【再投稿】【マクロ】元データから別表のランキング表へデータを抽出したい

tatsu99様

いつもご教授頂きましてありがとうございます。

先日、投稿させて頂きました件ですが
条件がほぼほぼ決まりましたので、条件等記載させて頂きます。
↓
前回の投稿
https://oshiete.goo.ne.jp/qa/10962742.html

大幅な変更はないとは思いますが、若干内容が変更になる可能性があります。
その際はまた、ご相談させて頂きます。

お忙しいかとは存じますが、何卒ご教授頂ければ幸いです。
↓
↓
★順位基準はあくまで達成率が高い人で順位付けをする
➀➁→データ抽出先は2シートずつ作成
③→データ抽出先は1シート作成

➀最優秀拠点
■元データ　※画像添付いたします。
・元データ名：収支表
・シート名(複数あり)：大阪、兵庫、東京、仙台、名古屋、西日本　6拠点
※今後増減可能性あり

・列：C列からひと月あたり23列ずつ
　12か月+1、2、3、4Q、上下期、年間
・行：6行目から170行ずつ
　最終行：175行目

例）10月　
※画像添付いたします。
・抽出したい項目　
シートの支店名ごとの下記項目の金額、達成率
➀168行目　収支➀
➁173行目　収支➁

③H列：計画金額
④K列：実績金額
⑤X列：達成率対計画金額
⑥Y列：伸長額対計画金額

■データ抽出先(上記、168行目、173行目の項目を2シートで分けて表示)　
※画像添付いたします。
・抽出先名：順位表
・シート名：最優秀拠点➀、最優秀拠点➁

列：A列からひと月あたり6列ずつ
　12か月+1、2、3、4Q、上下期、年間
・行：5行目から10行目の6位まで

・抽出項目
＊収支➀シート
➀元データ：シート別拠点→抽出先：B列　エリア/支店へ
➁元データ：H列　粗利計画→抽出先：C列　粗利計画へ
③元データ：D列　粗利実績→抽出先：D列　粗利実績へ
④元データ：X列　達成率対計画金額→抽出先：E列　達成率対計画金額へ
⑤元データ：ｙ列　伸長額対計画金額→抽出先：F列　伸長額対計画金額へ

＊収支➁シート
➀元データ：シート別拠点→抽出先：B列　エリア/支店へ
➁元データ：H列　粗利計画→抽出先：C列　粗利計画へ
③元データ：D列　粗利実績→抽出先：D列　粗利実績へ
④元データ：X列　達成率対計画金額→抽出先：E列　達成率対計画金額へ
⑤元データ：ｙ列　伸長額対計画金額→抽出先：F列　伸長額対計画金額へ


➁最優秀部署
■元データ　※画像添付いたします。
・元データ名：収支表　
・シート名(複数あり)：A店~N店まで14店舗、A営業所～G営業所営業所7拠点、東京1、2、3課、西日本1、2、3課、仙台営業課、名古屋営業課
計　29拠点
※今後増減可能性あり

・列：C列からひと月あたり23列ずつ
　12か月+1、2、3、4Q、上下期、年間
・行：6行目から170行ずつ
　最終行：175行目

例）10月
※画像添付いたします。
・抽出したい項目　
シートの支店名ごとの下記項目の金額、達成率
➀168行目　収支➀
➁173行目　収支➁

③H列：計画金額
④K列：実績金額
⑤X列：達成率対計画金額
⑥Y列：伸長額対計画金額

■データ抽出先(上記、168行目、173行目の項目を2シートで分けて表示)
※画像添付いたします。
・抽出先名：順位表
・シート名：最優秀拠点➀、最優秀拠点➁

列：A列からひと月あたり6列ずつ
　12か月+1、2、3、4Q、上下期、年間
・行：5行目から10行目の6位まで

・抽出項目
＊収支➀シート
➀元データ：シート別拠点→抽出先：B列　エリア/支店へ
➁元データ：H列　粗利計画→抽出先：C列　粗利計画へ
③元データ：D列　粗利実績→抽出先：D列　粗利実績へ
④元データ：X列　達成率対計画金額→抽出先：E列　達成率対計画金額へ
⑤元データ：ｙ列　伸長額対計画金額→抽出先：F列　伸長額対計画金額へ

＊収支➁シート
➀元データ：シート別拠点→抽出先：B列　エリア/支店へ
➁元データ：H列　粗利計画→抽出先：C列　粗利計画へ
③元データ：D列　粗利実績→抽出先：D列　粗利実績へ
④元データ：X列　達成率対計画金額→抽出先：E列　達成率対計画金額へ
⑤元データ：ｙ列　伸長額対計画金額→抽出先：F列　伸長額対計画金額へ


③個人別順位表
■元データ　※画像添付いたします。
・元データ名：個人別実績
・シート名：2019年度

・列：G列からひと月あたり8列ずつ
　12か月+1、2、3、4Q、上下期、年間
・行：4行目から31行ずつ
　最終行：3,078行目

例）10月
※画像添付いたします。
・抽出したい項目
B列：拠点
C列：課
D列：担当者
Y列：粗利計画
L列：粗利実績
N列：粗利達成率
※B、C、Dが入力されていて且つE列に「合計」と記載のある行(34行目)を抽出したい
※順位の基準は達成率が高い人

■データ抽出先
※画像添付いたします。
・抽出先名：順位表
・シート名：個人別

列：A列からひと月あたり8列ずつ
　12か月+1、2、3、4Q、上下期、年間
・行：5行目から10行目の6位まで

・抽出項目
➀元データ：B列　拠点→抽出先：B列　支店へ
➁元データ：C列　課→抽出先：C列　課・営業所へ
③元データ：D列　担当者→抽出先：D列　担当者へ
④元データ：Y列　粗利計画→抽出先：E列　粗利計画へ
⑤元データ：L列　粗利実績→抽出先：F列　粗利実績へ
⑥元データ：N列　粗利達成率→抽出先：G列　粗利達成率へ
⑦元データにはデータなし→抽出先：H列　伸長額対計画金額には別途、数式を入力予定

※本投稿には個人集計の元データ添付いたします。

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

• 最優秀拠点、最優秀部署の元データサンプル画像添付いたします。

  
    補足日時：2019/02/15 16:52

• 個人集計の抽出先サンプル画像を添付いたします。

  
    補足日時：2019/02/15 16:53

• 最優秀拠点、最優秀部署の抽出先サンプル画像添付いたします。

  
    補足日時：2019/02/15 16:54

• 5.保留にしておりました件、回答させて頂きます。
  やはり、到来していない月は「0.0％」で表示されるように数式が入っております。
  
  よろしくお願いいたします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2019/02/18 09:59

• tatsu99様
  お世話になっております。
  下記について、確認いたしましたので回答させて頂きます。
  
  ＞3078行目で終わるのはおかしいです。
  まずは、先に2点お詫びがございます。
  ➀最終行は3,072行目でした。
  
  また、説明不足で申し訳ありませんでした。
  個人集計元のデータは下記の通りとなっております。
  
  (A列：支店)→今回は集計には関係ありません
  B列：拠点
  C列：課
  D列：担当者
  
  ピボットのように下記内容で下へ連なっています。
  ➀(A列)の合計
  ➁(A列)、B列の合計
  ③(A列)、B列、C列の合計
  ④(A列)、B列、C列、D列の合計
  
  その中で、今回は④のすべてに拠点、課、担当者名が入っている行の
  合計セルを抽出したいです。
  ですので、①②③分も行数に入っている為最終行が3,072行目となります。
  
  恐れ入りますが、よろしくお願いいたします。

  No.5の回答に寄せられた補足コメントです。 補足日時：2019/02/18 10:14

• 

  tatsu99様
  
  おはようございます。
  早速、動作確認をしておりましてエラーがでましたので
  ご報告いたします。
  
  ↓
  最優秀拠点・部署
  
  
  '順位シートの設定
  ~
  Set wk1 = Worksheets(wn1)→この部分でエラーが出てしまいます。
  
  恐れ入りますが、考えられる原因をご教授頂けますと幸いです。

  No.10の回答に寄せられた補足コメントです。 補足日時：2019/02/19 10:57

• 本件、回答させて頂きます。
  ↓
  >このGetRowNo = wrowの行をマクロが実行すると、そこで止まり、マクロが一旦停止した状態にな>ります。
  >このGetRowNo = wrowの行が実行されない場合は、そのまま、終了します。
  →そのまま終了しました。
  
  恐れ入りますが、よろしくお願いいたします。

  No.16の回答に寄せられた補足コメントです。 補足日時：2019/02/20 10:33

• お世話になっております。
  本件につきまして、モジュール2で設定しております。
  処理完了した画像を添付させて頂きます。

  
  No.19の回答に寄せられた補足コメントです。 補足日時：2019/02/20 13:37

• tatsu99様
  
  お世話になっております。
  
  検証完了したはずだったのですが、個人集計のほうで
  データが反映されないことが判明しました。
  
  到達月：12月まで
  10、11、12月、1Qまで反映。
  上半期、年間が反映されません。
  
  原因は何が考えられますでしょうか？
  
  度々恐縮ではございますが、再度ご教授願います。
  
  また、別件に関しては申し訳ありません
  本日中には投稿させて頂きます。
  
  何卒、よろしくお願いいたします。

  No.26の回答に寄せられた補足コメントです。 補足日時：2019/02/22 13:18

No.10 ベストアンサー 回答者： tatsu99 回答日時： 2019/02/18 17:49

下記に登録しました。

最優秀拠点部署
https://ideone.com/V31xSp
標準モジュール module1に登録してください

最優秀個人
https://ideone.com/lkg5yt
標準モジュール module2に登録してください

仕様上の注意
マクロのあるブック（抽出先）には
「管理」シート、「作業①」シート、「作業②」シート、を作成しておいてください。

作業①②は、空のシートです。
「管理」シートのレイアウトは添付図を参照ください。
黄色のセルが入力必須のセルです。

この回答へのお礼

お忙しい中、ご教示頂きましてありがとうございます。
早速、明日動作確認させていただきます。
また、何か不明点等あれば質問させて頂きます。
その際はご教授頂けますと幸いです。
よろしくお願いいたします。

お礼日時：2019/02/18 20:36

No.28 回答者： tatsu99 回答日時： 2019/02/22 17:40

>非表示列があり、その列が原因だったかと考えます。

>そちらを削除し、再度マクロ実行しましたところ無事データ反映されました。
了解しました。

No.27 回答者： tatsu99 回答日時： 2019/02/22 15:22

>到達月：12月まで

>10、11、12月、1Qまで反映。
>上半期、年間が反映されません。
上半期の BN,BO,BP列（支店、課、担当者）
年間の EP,EQ,ER列（支店、課、担当者）
も空白ですか？

もし、上記の支店、課、担当者が空白でなく、
粗利計画、粗利実績、粗利達成率だけが空白なら、
元データがそのようになっている可能性があります。

それとも、上半期、年間の支店、課、担当者も空白なのでしょうか？

この回答へのお礼

> 上半期の BN,BO,BP列（支店、課、担当者）
> 年間の EP,EQ,ER列（支店、課、担当者）
>も空白ですか？
→お忙しい中、ご教授頂きましてありがとうございます。
　 元データ、抽出先データを再度確認しましたところ
　 非表示列があり、その列が原因だったかと考えます。
　 そちらを削除し、再度マクロ実行しましたところ無事データ反映されました。

お忙しい中、お騒がせして申し訳ありませんでした。

お礼日時：2019/02/22 16:08

No.26 回答者： tatsu99 回答日時： 2019/02/20 17:58

>本件、最優秀拠点・部署、優秀個人ともに無事検証完了いたしました。

了解しました。マクロ提示後に追加した行は、内容を確認する為のものなので、削除しても、問題ありません。
毎回、メッセージが表示されるのが煩わしいので削除したほうが良いかと。


＞当初の要望としては、粗利だけの抽出だったのですが
＞別途、数量のランキングも作成してほしいとの要望がありまして
＞度々のお願いで大変恐縮ではございますが、お願いできれば幸いです。
＞数量も粗利の元データということは同じなのですが
＞粗利よりも参照先が増えてしまいます。

１．抽出先は同じブックですか？（同じならmodule3,4に追加になるかと）
２．出力結果のシート名、レイアウトを提示してください。
３．元データの抽出元の項目も提示してください。
提示の際は、本スレッドが長くなり過ぎたので、これを閉じて、別スレッドでお願いいたします。
着手は、来週になるかも知れません。

この回答へのお礼

>>本件、最優秀拠点・部署、優秀個人ともに無事検証完了いたしました。
> 了解しました。マクロ提示後に追加した行は、内容を確認する為のものなので、削除しても、問題ありません。
> 毎回、メッセージが表示されるのが煩わしいので削除したほうが良いかと。
→ご丁寧に、アドバイス頂きましてありがとうございます。
　 ご教授頂きました追加のメッセージにつきましては、ご指示頂きました様に削除させて頂きます。
　 お手数をおかけいたしました。

>１．抽出先は同じブックですか？（同じならmodule3,4に追加になるかと）
→はい、ブックで問題ございません。

>２．出力結果のシート名、レイアウトを提示してください。
>３．元データの抽出元の項目も提示してください。
>提示の際は、本スレッドが長くなり過ぎたので、これを閉じて、別スレッドでお願いいたします。
→上記に関しては、本日中に別スレッドで再度質問を投稿させて頂きます。

>着手は、来週になるかも知れません。
→承知いたしました。
　 お忙しい中、度々お手数をおかけいたしますが、何卒、よろしくお願いいたします。

お礼日時：2019/02/21 09:27

No.25 回答者： tatsu99 回答日時： 2019/02/20 15:36

これから外出します。

戻りは6時以降になります。

この回答へのお礼

外出されるとのこと、承知いたしました。
お気をつけていってらっしゃいませ。

本件、最優秀拠点・部署、優秀個人ともに無事検証完了いたしました。
お忙しい中、何度もお手数をおかけして申し訳ありませんでした。
大変感謝しております。いつも本当にありがとうございました。

また、ご多忙のことと存じますが
別途、ご教授をお願いしたく。。

当初の要望としては、粗利だけの抽出だったのですが
別途、数量のランキングも作成してほしいとの要望がありまして
度々のお願いで大変恐縮ではございますが、お願いできれば幸いです。

数量も粗利の元データということは同じなのですが
粗利よりも参照先が増えてしまいます。

もし、可能な際は別途質問投稿させて頂きたいと思っております。

何卒、ご検討くださいます様お願いいたします。

お礼日時：2019/02/20 16:24

No.24 回答者： tatsu99 回答日時： 2019/02/20 15:32

ブレイクポイントで止まらない件ですが、他の行も同様なのか確認したいため、以下の作業を行っていただけますか。

Public Sub 最優秀個人()の最初の実行部分の行である
Set kj_ws = Worksheets("個人別")　
の行へブレイクポイントを設定し（添付図参照）、そこで止まるか、確認してみてください。
もし、止まらなければ、なにか環境設定の問題のような気がします。

この回答へのお礼

お世話になっております。
こちらについては、処理完了せず指定のところで
止まりました。

お礼日時：2019/02/20 15:53

No.23 回答者： tatsu99 回答日時： 2019/02/20 15:23

maxpsとmaxrowの件、了解しました。

127行目が集計抽出対象の行
なので、その内容を表示するようにします。
Private Function GetRowNoの
For wrow = str_row To (str_row + 30)の直後に以下の3行を追加します。
If wrow = 127 Then
MsgBox ("B=<" & src_ws.Cells(wrow, "B").Value & ">C=<" & src_ws.Cells(wrow, "C").Value & ">D=<" & src_ws.Cells(wrow, "D").Value & ">E=<" & src_ws.Cells(wrow, "E").Value & ">")
End If

以下のようになります。（追加と書かれているのが追加された行）
Private Function GetRowNo(ByVal ps As Long, ByVal src_ws As Worksheet)
Dim str_row As Long
Dim wrow As Long
str_row = (ps - 1) * 31 + 4
For wrow = str_row To (str_row + 30)
If wrow = 127 Then '追加
MsgBox ("B=<" & src_ws.Cells(wrow, "B").Value & ">C=<" & src_ws.Cells(wrow, "C").Value & ">D=<" & src_ws.Cells(wrow, "D").Value & ">E=<" & src_ws.Cells(wrow, "E").Value & ">") '追加
End If '追加
'支店／課／担当者／合計が空白でないなら処理する
If src_ws.Cells(wrow, "B").Value <> "" And src_ws.Cells(wrow, "C").Value <> "" And src_ws.Cells(wrow, "D").Value <> "" And src_ws.Cells(wrow, "E").Value = "合計" Then
GetRowNo = wrow
Exit Function
End If
Next
GetRowNo = 0
End Function

それで、１２７行目の内容がメッセージボックスに表示されます。
B列、C列、D列は空白でなく、E列は合計になってますでしょうか。

この回答へのお礼

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

ご教授頂いた内容に修正いたしました。
エラー内容が表示されるようにしていただき、ありがとうございます。

そのメッセージを確認しましたところ、根本のこちらの確認漏れが原因だと判明いたしました。
元データが”合(全角スペース)計”となっておりました。
お忙しい中、お手数をおかけしました大変申し訳ございませんでした。
全角スペースを削除し、”合計”に修正しましたところ、無事処理が完了いたしました。

本当に申し訳ありませんでした。

お礼日時：2019/02/20 16:16

No.22 回答者： tatsu99 回答日時： 2019/02/20 14:07

こちらで、期待している

maxpsは99
maxrowは3072
です。

この回答へのお礼

お世話になっております。
何度も申し訳ありません。

上記、やはりとまらず処理完了してしまう為
追加いたしました。

仰る通り、下記の通りとなりました。
>maxpsは99
> maxrowは3072
>です。

ただ、データは抽出されませんでした。

上記ふまえ、いろいろと私のほうでもお願いした内容を再確認しました。

B、C、D列すべて空白ではない、E列が”合計”の対象は68人分です。
それに加え、B列のみに入っていたり、B、C列のみ入っているものがあり、それを加えると
99人分となります。

その説明が不足しておりましたでしょうか？
もし、それが原因でしたら申し訳ありません。

お礼日時：2019/02/20 15:02

No.21 回答者： tatsu99 回答日時： 2019/02/20 14:01

＞本件につきまして、モジュール2で設定しております。

＞処理完了した画像を添付させて頂きます。
画像をみました。
たしかに、wk_row = 0にブレイクポイントが設定されていますね。
ならば、実行時、必ず、ここで止まるはずですが、止まらないのは不思議ですね。

もし、もう一度、やってみて、止まらなければ、以下のようにしてください。
wk_row = 0の前の行に、
MsgBox ("maxps=" & maxps & " maxrow=" & maxrow)
を追加してください。

結果的に以下のようになります。（'追加と書かれているのが追加した行）
maxps = (maxrow - 3) \ 31
MsgBox ("maxps=" & maxps & " maxrow=" & maxrow) '追加
wk_row = 0

それで、実行すると
maxpsとmaxrowが表示されます。その値を報告してください。
又、それは、あなたの認識と合致していますか。
maxpsは最大人数（最大枠の人数）
maxrowはG列の最大行
です。

No.20 回答者： tatsu99 回答日時： 2019/02/20 12:32

マクロの登録ですが

Public Sub 最優秀拠点部署()はModule1
Public Sub 最優秀個人()はModule2
に登録されていますか。
添付図参照（画像はModule2のマクロを表示しています）