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

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.8 回答者： tatsu99 回答日時： 2019/02/18 13:21

＞→質問：現状、99人となりますが今後人数が変動した場合は問題ございませんでしょうか？

今後、人数の変動があるようであれば、データが記述されている最終行を取得し、そこから人数を割り出すようにします。
最終行に必ずデータが格納されている列は、どの列でしょうか。（A列は全てデータがありますか。歯抜けの行でも、埋まっている列を求めています）
取得した最終行は
1人目　4行～34行
2人目　35行～65行
3人目　66行～96行
99人目 3042行～3072行
なので、34,65,96,・・・,3072,・・・以外はエラーとします。
(取得した最終行-3)÷３１で余りがあれば、エラーとします。

＞→対象については、歯抜けになっております。
歯抜けがあるということなので、最終行まで処理します。

この回答へのお礼

>＞→質問：現状、99人となりますが今後人数が変動した場合は問題ございませんでしょうか？
> 今後、人数の変動があるようであれば、データが記述されている最終行を取得し、そこ>から人数を割り出すようにします。
> 最終行に必ずデータが格納されている列は、どの列でしょうか。（A列は全てデータが>ありますか。歯抜けの行でも、埋まっている列を求めています）
> 取得した最終行は
>1人目　4行～34行
>2人目　35行～65行
>3人目　66行～96行
>99人目 3042行～3072行
>なので、34,65,96,・・・,3072,・・・以外はエラーとします。
>(取得した最終行-3)÷３１で余りがあれば、エラーとします。
→承知いたしました。では、そちらでお願いいたします。
　また、データが確実に入っております列はG列でお願いいたします。
　G列以降でしたらデータが入っておりますのでそちらでお願いできればさいわいです。

> ＞→対象については、歯抜けになっております。
> 歯抜けがあるということなので、最終行まで処理します。
→承知いたしました。よろしくお願いいたします。

お礼日時：2019/02/18 13:32

No.7 回答者： tatsu99 回答日時： 2019/02/18 12:19

＞抽出条件は、➁でお願いいたします。

この案の最終確認です。

1人目　4行～34行
2人目　35行～65行
3人目　66行～96行
なので
1人目は4行から34行まで、順に、
　「B列が空白でなく、C列が空白でなく、D列が空白でなく、E列が"合計"である」行を検索し、
　条件に合致すれば、抽出を行い、2人目へ遷移する。
　最終的に34行まで条件に合致する行がなければ、1人目は抽出しない。

上記の処理を同様に2人目から99人目まで繰り返す。
（2人目は35行～65行まで検索をします）


抽出対象となる各担当者は、上から順に詰まっているのでしょうか。
それとも、歯抜けがあるのでしょうか。（１人目、５人目が抽出対象であり、２，３，４人目、6人目～９９人目は対象外のようなケース）

上から順に詰まっているなら、抽出対象外の担当者が発生した時点で処理を打ち切ります。
歯抜けがあるなら、９９人目まで、無条件に検索をおこないます。

この回答へのお礼

tatsu99様

お世話になっております。
お忙しい中、早々にご返信頂きましてありがとうございます。

下記、質問と回答をさせて頂きます。

>1人目　4行～34行
>2人目　35行～65行
>3人目　66行～96行
>なので
>1人目は4行から34行まで、順に、
> 　「B列が空白でなく、C列が空白でなく、D列が空白でなく、E列が"合計"である」行を検索し、
>　条件に合致すれば、抽出を行い、2人目へ遷移する。
> 　最終的に34行まで条件に合致する行がなければ、1人目は抽出しない。
> 上記の処理を同様に2人目から99人目まで繰り返す。
> （2人目は35行～65行まで検索をします）
→質問：現状、99人となりますが今後人数が変動した場合は問題ございませんでしょうか？

> 抽出対象となる各担当者は、上から順に詰まっているのでしょうか。
>それとも、歯抜けがあるのでしょうか。（１人目、５人目が抽出対象であり、２，３，４人目、6人目～９９人目は対象外のようなケース）
> 上から順に詰まっているなら、抽出対象外の担当者が発生した時点で処理を打ち切ります。
> 歯抜けがあるなら、９９人目まで、無条件に検索をおこないます。
→対象については、歯抜けになっております。
　
恐れ入りますが、よろしくお願いいたします。

お礼日時：2019/02/18 13:02

No.6 回答者： tatsu99 回答日時： 2019/02/18 10:46

＞やはり、到来していない月は「0.0％」で表示されるように数式が入っております。

了解しました。３案なので、どの月にデータがあっても問題ありません。

＞最終行は3,072行目でした。
ということは、９９人分のデータがあると理解しました。
それで、間違いないでしょうか。

＞その中で、今回は④のすべてに拠点、課、担当者名が入っている行の
＞合計セルを抽出したいです。
この意味は、1人目のデータは、３４行目からデータを抽出しますが、
34行目のB列が空白でない（拠点）
34行目のC列が空白でない（課）
34行目のD列が空白でない（担当者）

ということでしょうか。

抽出条件は、
①B,C,D列が全て空白でない行(F列はチェックしない)
従って、１人目について言えば３４行とは限らない。
（結果的にそうなるかも知れませんがマクロは34行目にこだわらない）

②B,C,D列が全て空白でない行でかつF列に"合計"の文字がある行
従って、１人目について言えば３４行とは限らない。
（結果的にそうなるかも知れませんがマクロは34行目にこだわらない）

③１人目の最後の行が"合計"なので、
34行目のB,C,D列が全て空白でなく、かつF列に"合計"の文字があれば、抽出対象となる。
（常に、各個人の最終行のみ注目し、最終行の上記条件が満たされれば、抽出する）

①②③のどれになるのでしょうか？

この回答へのお礼

tatsu99様

お世話になっております。
お忙しい中、ご確認頂きましてありがとうございました。
ご質問頂きました件、下記回答させて頂きます。

>＞やはり、到来していない月は「0.0％」で表示されるように数式が入っております。
> 了解しました。３案なので、どの月にデータがあっても問題ありません。
→よろしくお願いいたします。

>＞最終行は3,072行目でした。
>ということは、９９人分のデータがあると理解しました。
>それで、間違いないでしょうか。
→仰るとおり、99人分となります。

> ＞その中で、今回は④のすべてに拠点、課、担当者名が入っている行の
>＞合計セルを抽出したいです。
>この意味は、1人目のデータは、３４行目からデータを抽出しますが、
>34行目のB列が空白でない（拠点）
>34行目のC列が空白でない（課）
>34行目のD列が空白でない（担当者）
>ということでしょうか。
→仰る通りです。

抽出条件は、➁でお願いいたします。
また、F列に”合計”がある行とお伝えしておりましたが
正しくは、E列とF列を結合したセルとなります。
ですので、E列参照となります。
大変失礼いたしました。

恐れ入りますが、何卒、よろしくお願いいたします。

お礼日時：2019/02/18 12:00

No.5 回答者： tatsu99 回答日時： 2019/02/17 00:34

個人別順位表の元データですが、

・行：4行目から31行ずつ
　最終行：3,078行目
とありますが、
4行目　1人目の最終行＝34行目
　　　 2人目の最終行＝65行目
　　　99人目の最終行＝3072行目
　　 100人目の最終行＝3103行目
となり、
3078行目で終わるのはおかしいです。
ご確認ください。

この回答へのお礼

ご返信が遅くなり、申し訳ありません。
また、ご指摘ありがとうございます。
明日確認の上、回答させていただきます。
宜しくお願いいたします。

お礼日時：2019/02/17 09:41

No.4 回答者： tatsu99 回答日時： 2019/02/16 23:56

個人集計の元データについてです。

１．抽出項目で
④元データ：Y列　粗利計画→抽出先：E列　粗利計画へ
とあるが、
④元データ：H列　粗利計画→抽出先：E列　粗利計画へ
の誤りではないでしょうか？

２．「合計」と記載されている列
　　例　10月
「※B、C、Dが入力されていて且つE列に「合計」と記載のある行(34行目)を抽出したい」
ということですが、F列に「合計」と記載のある行(34行目)を抽出ではないでしょうか。（画像はF列に合計）

この回答へのお礼

度々、申し訳ありません。
仰る通り、元データはH列です。
また、合計はF列で間違いありません。

お手数おかけして申し訳ありません。

お礼日時：2019/02/17 09:46

No.3 回答者： tatsu99 回答日時： 2019/02/16 13:45

追加の確認です。

最優秀拠点の
抽出項目
＊収支➀シート
③元データ：D列　粗利実績→抽出先：D列　粗利実績へ

＊収支➁シート
③元データ：D列　粗利実績→抽出先：D列　粗利実績へ

となっていますが、元データはＫ列の誤りではないでしょうか？

下記が正しいと思いますが、いかがでしょうか。（最優秀部署も同様）
＊収支➀シート
③元データ：Ｋ列　粗利実績（実績金額）→抽出先：D列　粗利実績へ

＊収支➁シート
③元データ：Ｋ列　粗利実績（実績金額）→抽出先：D列　粗利実績へ

この回答へのお礼

こちらも仰る通り、元データK列です。
記載ミスが多く、お手数をおかけして申し訳ありません。
恐れ入りますが、何卒よろしくお願いいたします。

お礼日時：2019/02/17 10:07

No.2 回答者： tatsu99 回答日時： 2019/02/16 10:43

５の補足ですが、

>→空白かどうか、月曜日再度元データを確認いたします。
>ただ、恐らく0%になっていた気がします。
>また、到来していない月については空欄ではなく0%と表示させたいです。Q毎、上下期、年間も元データはデータが入っている為、そちらも反映したいのです>が可能でしょうか？
>※単月だけではなく、その時点のQ、上下期、年間も合わせて1表で見たいためです。

0%であれば、それでも問題ありませんが、例えば、個人別順位表で順位を決める場合、未到来の月のデータが全て０％なので、
全ての個人が結果になるため、表示された上位６人の個人は意味のない個人名になってしまいます。（最優秀拠点、最優秀部署も同様）

順位表は未到来の月であれば、全て空欄で表示するのが良いと考え、質問した次第です。
このままでは、未到来の月の順位表は、全て％で表示され、意味のない上位６人の個人名が表示されてしまいます。
恐らく、それは望んだ結果ではないと思います。
これを回避する為、未到来の月か、到来済みの月（データがきちんとある月）をどのようにして区別するかが問題になります。

1案
０％なら未到来として処理する。
この案は、到来済みの月で、実際に０％のデータがある場合は、採用できない。

2案
未到来の月のデータは空欄にする。
このようになっていれば、未到来の月と到来済みの月の切り分けができるため、質問した次第です。
補足を見た限りでは、この案は、難しそうなので、とりあえず、採用対象外とします。

3案
管理シートに到来済みの月を入力し、その翌月以降は、未到来の月なので処理しないようにする。（添付図参照）
例　到来済みの月＝１１月の場合、
順位表の10月、11月を設定する。
12月~9月の月は、全て空欄で表示する。
１1月は１Ｑ、上半期、年間計の対象になるので、１Ｑ、上半期、年間計を設定し、２Ｑ、３Ｑ、４Ｑ、下半期は空欄で表示する。

4案
未到来の月は、意味のない順位が表示されるが、それで良しとする。（たぶん、望んでないと思いますが）


1案か３案になりそうですが、どの案にするか提示してください。

この回答へのお礼

ご提案頂きましてありがとうございます。
仰る通り、到来月以外は無意味になってしまいますよね。
ご提案頂きました、3案でお願いできましたらと思います。
宜しくお願いいたします。

お礼日時：2019/02/17 09:58

No.1 回答者： tatsu99 回答日時： 2019/02/16 07:23

１．マクロは「順位表.xlsm」に格納しますが、よろしいでしょうか。

２．最優秀部署のデータ抽出先のシート名が、最優秀拠点①、最優秀拠点②となっていますが、
　　最優秀部署①、最優秀部署②の誤りではないでしょうか。
　　（最優秀拠点のデータ抽出先のシート名が、最優秀拠点①、最優秀拠点②です。）

３．順位表.xlsmに管理シート（シート名：管理）を作成し、
　　そのシートに元データのフォルダ名、ファイル名、シート名を指定するようにしたいと
　　考えてますがいかがでしょうか。（添付図参照）
　　黄色のセルが指定必須の箇所です。
　　Ｂ２：最優秀拠点・部署用の元データの格納フォルダ名
　　Ｃ２：最優秀拠点・部署用の元データのファイル名（拡張子まで含めて指定すること）
　　Ｂ３：個人別順位表の元データの格納フォルダ名
　　Ｃ３：個人別順位表の元データのファイル名（拡張子まで含めて指定すること）
　　Ｄ３：個人別順位表の元データの処理対象となるシート名
　　（シート名は、個人別順位表のみ指定必須）

４．順位表.xlsmに作業用シートを予め作成しておくことが必要になりますが、よろしいでしょうか。
　　シートは空のシート。シート名は未定。（作業①、作業②等の予定。正式名称はマクロ完了時、提示します）

５．元データのシートで処理対象の月が到来していない月は、達成率は空白であると理解してよろしいでしょうか。
　　例えば、2019年年度の12月のデータは、2019年11月1日時点では、集計されていないので、達成率は空欄になっている。
　　（0％ではなく、空白です）
　　空欄の月（１Ｑ、２Ｑなども含む）は、データの集計をスキップします。

６．最優秀拠点と最優秀部署の元データは、同じファイルになり、シート名が違うだけですが、
　　マクロでは、そのシートが拠点用なのか、部署用なのかの判断ができません。
　　（人間はシート名で判断ができますが、マクロはできません）
　　その為、各シートのある特定のセルに以下の情報を格納しておくようにはできませんでしょうか。
　　Ａ１：区分（1：最優秀拠点用、2:最優秀部署用、左記以外：集計対象外）（１，２は半角で指定）
　　Ｂ１：拠点名又は部署名（データ抽出先の「エリア／支店」の設定する名称）
　　セル、Ａ１，Ｂ１は例です。具体的なセルの位置はあなたの方で提示してください。

この回答へのお礼

tatsu99様

おはようございます。
お忙しい中、早々にご教授いただきましてありがとうございます。

文字数制限ある為、番号のみに回答させていただきます。
また、5.につきましては月曜日に再度確認の上、ご連絡させて頂ければ幸いです。

恐れ入りますが、よろしくお願いいたします。

１．
→問題ございません。

２．
→失礼いたしました。仰る通りでございます。
最優秀拠点も最優秀部署も各収支①、収支②２つのシートにわけて集計したいです。

３．
→管理シート作成につきましては問題ございません。
そちらでお願いいたします。

４．
→はい、問題ございません。

５．
→空白かどうか、月曜日再度元データを確認いたします。
ただ、恐らく0%になっていた気がします。
また、到来していない月については空欄ではなく0%と表示させたいです。Q毎、上下期、年間も元データはデータが入っている為、そちらも反映したいのですが可能でしょうか？
※単月だけではなく、その時点のQ、上下期、年間も合わせて1表で見たいためです。

６．
→こちらついては、特定セルに入力することは可能です。
ご教示頂いたA1、B1でお願いいたします。

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