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

tatsu99様

お世話になっております。
下記では、お忙しい中何度もお手数をおかけして申し訳ありませんでした。

補足がMAX使用してしまった為、改めて質問投稿させてください。

数量・売上と同様粗利も同じ項目で集計したいというのが要望です。

★拠点別、個人別粗利集計
https://oshiete.goo.ne.jp/qa/10981290.html

★拠点別、個人別数量・売上集計
https://oshiete.goo.ne.jp/qa/10992933.html

➀数量・売上と粗利の6シートずつでデータを抽出できるように変更をお願いしたいです。

➁もし可能でしたら添付した画像のように同じシートに数慮・売上と粗利のデータを抽出できるように
したいというのが要望ですが、　おそらく、お忙しいかと思いますので今からの変更は難しいかとは存じますが、何卒ご検討頂けますと幸いです。
難しい場合は、①でそれぞれ抽出したあとこちらで加工させて頂きます。
※こちらには個人集計のイメージ画像添付いたします。

よろしくお願いいたします。

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

• ※こちらには拠点別集計のイメージ画像添付いたします。

  
    補足日時：2019/02/28 17:19

• また、ランキングの基準となる項目ですが粗利の実績の降順で設定をお願いできれば幸いです。
  もし、私が要望いたしました②がご対応可能な場合も粗利の実績の降順で設定をお願いいたします。
  また、ランキングの基準となる項目を変更する場合どこを修正したら良いのかも、ご教授頂けますと幸いでございます。

    補足日時：2019/02/28 17:29

• tatsu99様
  
  お世話になっております。
  
  無事解決したと申しましたのに
  何度も申し訳ありません。
  追加でご教授をお願いしたく…。
  
  個人集計の数量売上金額なのですが
  G列、I列の27行目の項目の数字を
  26行目の数字をマイナスした金額で表示させたいのです。
  
  お忙しいかと存じますが、何卒ご教授頂けますと幸いです。
  
  よろしくお願いいたします。

    補足日時：2019/03/04 19:29

• 画像添付いたします。

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

• 検証結果画像添付いたします。
  上が元データ、下がマクロ実行後の集計結果です。
  恐れ入りますが、よろしくお願いいたします。

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

• 

  tatsu99様
  
  また、本件とは別件の質問している内容について
  求めたい条件が複雑すぎて、頭を悩ませております＞＜
  ↓
  https://oshiete.goo.ne.jp/qa/11014863.html
  
  関数で求めようと思っておりましたが、なかなか良い案が浮かばず。。
  tatsu99様には本来、関係ない質問をお尋ねすることはお門違いかと思ったのですが
  もし、何か良いアドバイスを頂けたら幸いです。
  
  こちらも来週以降で構いません。
  大変恐縮ではございますが、何卒よろしくお願いいたします。

  No.22の回答に寄せられた補足コメントです。 補足日時：2019/03/08 11:04

• tatsu99様
  
  お世話になっております。
  ご返信が遅くなり申し訳ありません。
  
  下記回答させて頂きます。
  
  >質問１
  >→ 元データの何列になりますか。(C列～Y列のどの列ですか)
  >質問２
  >>★module5(拠点別集計)
  →申し訳ありません、再度確認しましたところ粗利の金額と勘違いしておりました。
  こちらはマクロで算出する設定でお願いいたします。
  お手数をおかけいたしますが、よろしくお願いいたします。
  　
  >質問３
  >H列＝数量売上実績－数量売上計画＝抽出シートのF列－抽出シートのE列
  >となりますが、OKでしょうか。
  >質問４
  >H列＝粗利実績－粗利計画
  >＝抽出シートのF列－抽出シートのE列となりますが、OKでしょうか。
  →本件、仰る通りで問題ございません。
  
  お忙しい中、大変恐縮ではございますが
  何卒、よろしくお願いいたします。

  No.23の回答に寄せられた補足コメントです。 補足日時：2019/03/11 11:56

• tatsu99様
  
  お世話になっております。
  検証結果のご報告が遅くなり、申し訳ありませんでした。
  
  module1、2に関しては問題なく動作確認できました。
  こちらは解決いたしました。
  ありがとうございました。
  
  module3、4についてですが「達成額/未達額」が空白になってしまいます。。
  私のお伝えの仕方がわかりづらかったのかもしれません。
  申し訳ありません。
  
  ・module3→G列＝E列－D列
  ・module4→H列＝F列ーE列
  
  ↑どちらも元データにデータがない為、マクロで算出するように設定いただきたいです。
  もしくは、こちらで数式を入力しますので、数式が消えないようにしていただけると幸いです。
  
  何度もお手数をおかけして申し訳ありませんが、よろしくお願いいたします。

  No.25の回答に寄せられた補足コメントです。 補足日時：2019/03/13 13:48

• tatsu99様
  
  お世話になっております。
  検証しておりまして、うまくいかず再度ご教授願います。
  
  こちらには拠点別(module5)の検証結果の画像を添付いたします。
  
  10月は、達成額/未達額(本来H列)にB列エリアと同じ内容が反映されてしまいます。
  C列店舗に関しては正しく抽出されます。
  11月以降に関しては、B列エリアにはエリアと数字が混じって抽出されてしまいます。
  C列店舗に関しては上記同様、正しく抽出されます。
  
  今回はご教授頂いた修正後のマクロをそのまま貼り付けても
  追加の内容を修正前のマクロに追記してもどちらもこのような状態になってしまいます。
  
  これまでは正常に動作確認できていたのですが何が原因と考えられますでしょうか？
  何度もお手数をおかけして申し訳ありませんが、ご教授頂けますと幸いです。

  
  No.27の回答に寄せられた補足コメントです。 補足日時：2019/03/14 11:01

• №27のmodule6の検証結果画像添付いたします。
  
  module6個人集計について画像添付いたします。
  H列達成額/未達額に支店が入ってしまい、それ以外の対象列が空白になってしまいます。
  
  こちらもお手数をおかけいたしますが
  ご教授願います。

  
    補足日時：2019/03/14 11:07

No.43 ベストアンサー 回答者： tatsu99 回答日時： 2019/03/18 18:04

下記URLにアップしました。

module4
修正内容
①合計をF列で検索するように修正
②達成率をマクロで算出するように修正
https://ideone.com/p0kPPZ

module6
①合計をF列で検索するように修正
https://ideone.com/clPXG9

追加／修正の箇所に2018.03.18のコメントを付加してあります。

この回答へのお礼

tatsu99様

お世話になっております。
ご返信が遅くなり申し訳ありません。

ご教授頂きましたマクロでmodule4、６ともに無事に検証完了し
解決いたしました。
本当にありがとうございました。

今回に限らずですが、特に今回に関しましては
お忙しい中、何度もお手数をおかけして申し訳ありませんでした。

今後は、もっと簡潔な説明と、条件等を明確にし記載させて頂く様
心がけます。

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

お礼日時：2019/03/19 12:24

No.44 回答者： tatsu99 回答日時： 2019/03/19 13:16

>ご教授頂きましたマクロでmodule4、６ともに無事に検証完了し

>解決いたしました。
了解しました。

No.42 回答者： tatsu99 回答日時： 2019/03/18 13:21

＞個人別実績.xlsxのレイアウト確認しましたところ、恐らくそれが原因だということがわかりました。

＞当初、E列「合計」の抽出するマクロを作成頂いた際はE列で検索するようにとお願いしておりましたが
＞①合計~⑥合計に関してはF列に変更をお願いしておりました。
＞私のお伝えの仕方がわかりづらく、お手数をおかけして申し訳ありません。
＞恐らく今、①合計~⑥合計をE列で検索するようにして頂いているかと思います。
＞何度も申し訳ありませんが、F列を検索するように変更をお願いできますでしょうか？

現在、E列で①合計~⑥合計を検索しています。
F列にするとのことですが、これは、module6のみの話ですか。
それとも、module4、module6ともにF列で検索するということですか？

この回答へのお礼

tatsu99様

お世話になっております。
下記、記載させて頂きます。

>現在、E列で①合計~⑥合計を検索しています。
>F列にするとのことですが、これは、module6のみの話ですか。
>それとも、module4、module6ともにF列で検索するということですか？
→申し訳ございません、こちらの抽出条件が二転三転した結果、説明不足でした。
お手数をおかけする結果となり、大変申し訳ございません。
module4、6ともに①合計~⑥合計を参照するのはF列になります。
何度も申し訳ございませんが、修正をお願いできましたら幸いです。
よろしくお願いいたします。

お礼日時：2019/03/18 15:04

No.41 回答者： tatsu99 回答日時： 2019/03/16 17:59

＞また、別件の達成率につきましてはおっしゃる通りです。

ということは、達成率＝実績値／計画値　と理解しました。
①計画値＝０　実績値＝０の時、達成率＝０　（これは、あなたから提示されています）
②計画値＝０　実績値＝1０の時、達成率＝？　
③計画値＝０　実績値＝20０の時、達成率＝？

②③のケースは達成率はいくらになりますか。（計画値が０なので　実績値／計画値の演算はできません）

又、計画値と実績値がマイナスになることはありますか。あるならば、
④計画値＝1０　実績値＝-10の時、達成率＝-1
⑤計画値＝-1０　実績値＝10の時、達成率＝-1
⑥計画値＝-1０　実績値＝-10の時、達成率＝1
になりますが、それでよろしいでしょうか。

この回答へのお礼

tatsu99様

おはようございます。
ご返信が遅くなり、申し訳ありません。
別件、マクロにつきましてはこれから検証させて頂きますので
改めてご報告させて頂きます。

お問合せ頂いておりました件、下記回答させて頂きます。
恐れ入りますが、ご確認の程よろしくお願いいたします。

>＞また、別件の達成率につきましてはおっしゃる通りです。
>ということは、達成率＝実績値／計画値　と理解しました。
>①計画値＝０　実績値＝０の時、達成率＝０　（これは、あなたから提示されています）
>②計画値＝０　実績値＝1０の時、達成率＝？　
>③計画値＝０　実績値＝20０の時、達成率＝？
>②③のケースは達成率はいくらになりますか。（計画値が０なので　実績値／計画値の演算はできません）
→➁③ともに0％です。

>又、計画値と実績値がマイナスになることはありますか。あるならば、
>④計画値＝1０　実績値＝-10の時、達成率＝-1
>⑤計画値＝-1０　実績値＝10の時、達成率＝-1
>⑥計画値＝-1０　実績値＝-10の時、達成率＝1
>になりますが、それでよろしいでしょうか。
→仰る通り、計画値、実績値ともにマイナスになることがあります。
④達成率=-100％
⑤達成率=300％
⑥達成率=100％
元データの数式は本来下記の数式が入っております。
=IFERROR(IF(計画<0,IF(実績<(計画),ABS(実績)/(計画),実績/ABS(計画)+2),実績/(計画)),"")

お礼日時：2019/03/18 09:41

No.40 回答者： tatsu99 回答日時： 2019/03/16 15:04

>お忙しい中、マクロ共有頂きましてありがとうございます。

>申し訳ありません、月曜日に確認させていただきます。
アップしたファイルの有効期間が3日間なので、月曜日は期限切れになっていると思いますので、
再度、アップします。
こちらからダウンロードしてください。

Book403.xlsm
http://firestorage.jp/download/daf8b223c1ff5aa04 …

個人別実績.xlsx
http://firestorage.jp/download/0026da488b819f478 …
有効期間は７日間にしましたので、22日午前中まではOKかと。

ボタン６をクリックするとmodule6が実行されます。

１．あなたの環境の個人別実績.xlsxを指定して、このマクロを実行した結果がOKなら、あなたの環境のマクロが正しくない。
２．NGなら、私が提示した個人別実績.xlsxを再度、このマクロを実行する。
その結果がOKなら、あなたの個人別実績.xlsxと私が提示した個人別実績.xlsxとのレイアウトが違っている可能性がある。
その結果がNGなら（これは考えにくいが・・・）、あなたのexcelの環境と私のexcelの環境の問題になります。

この回答へのお礼

tatsu99様

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

ファイルアップの件、ご配慮頂きましてありがとうございます。お手数おかけいましました。

検証させていただき次第、ご報告させていただきます。

引き続きよろしくお願いいたします。

お礼日時：2019/03/16 15:54

No.39 回答者： tatsu99 回答日時： 2019/03/15 11:06

＞→申し訳ありません、module6は達成率は当初のままで大丈夫です。

＞module4が数量と売上が混在しております為、以前お願いした下記内容になります。
＞ですが、やはりmoduke4は達成率が合計④を抽出しているように思えます。
＞例)
＞抽出元：27行目の数字として表示したい数字
＞①計画：G27-G26の数字(0)　➁実績：I27-I26の数字(0)　③達成率：①/➁
＞この式に当てはめると本来は0％となるようにしたいのですが
＞抽出先はE列：計画　0　F列：実績　0　G列：達成率161％　のようになってしまいます。。
＞私の説明の仕方、私の理解の仕方が違っているのかもしれません。申し訳ありません。

module6がきちんとかたずいてから、確認したいと思います。
ところで達成率は、①／②ですか？　②／①のような気がします。
一度、要件をきちんと整理してからにしたいと思います。

この回答へのお礼

tatsu99様

お世話になっております。
下記、検証結果ご報告させて頂きます。

>１．あなたの環境の個人別実績.xlsxを指定して、このマクロを実行した結果がOKなら、あなたの環境のマクロが正しくない。
>２．NGなら、私が提示した個人別実績.xlsxを再度、このマクロを実行する。
>その結果がOKなら、あなたの個人別実績.xlsxと私が提示した個人別実績.xlsxとのレイアウトが違っている可能性がある。
>その結果がNGなら（これは考えにくいが・・・）、あなたのexcelの環境と私のexcelの環境の問題になります。
→共有頂きましたファイル確認しました。ありがとうございます。
マクロの結果ですがNGでした。。。
個人別実績.xlsxのレイアウト確認しましたところ、恐らくそれが原因だということがわかりました。
当初、E列「合計」の抽出するマクロを作成頂いた際はE列で検索するようにとお願いしておりましたが
①合計~⑥合計に関してはF列に変更をお願いしておりました。
私のお伝えの仕方がわかりづらく、お手数をおかけして申し訳ありません。
恐らく今、①合計~⑥合計をE列で検索するようにして頂いているかと思います。
何度も申し訳ありませんが、F列を検索するように変更をお願いできますでしょうか？

お忙しい中、お手数をおかけして申し訳ありませんが
何卒、よろしくお願いいたします。

お礼日時：2019/03/18 13:10

No.38 回答者： tatsu99 回答日時： 2019/03/15 11:01

当方のexcelファイルを下記URLにアップしました。

①Book403.xlsm(マクロのあるファイル)
http://firestorage.jp/download/f97b2b1be944ecbe6 …

②個人別実績.xlsx
http://firestorage.jp/download/58a941fd0ac10981d …


Book403.xlsmの管理シートにあなたの環境の個人別実績.xlsxを指定して、マクロを実行してください。
尚、ボタンは以下のように割り当てられています。
ボタン1：module1
ボタン2：module2
ボタン3：module3
ボタン4：module4
ボタン5：module5
ボタン6：module6

module6の確認なのでボタン６をクリックしてください。
私の環境の個人別実績.xlsxは、不要かと思いますが念のためにUPしました。

この回答へのお礼

tatsu99様

お世話になっております。
お忙しい中、マクロ共有頂きましてありがとうございます。
申し訳ありません、月曜日に確認させていただきます。

また、別件の達成率につきましてはおっしゃる通りです。
大変失礼いたしました。

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

お礼日時：2019/03/16 07:06

No.37 回答者： tatsu99 回答日時： 2019/03/14 22:57

＞module4

＞https://ideone.com/JRtx04
>達成率も同じように④合計ではなく④合計-③合計の達成率としたいです。

module4は既に実施済みです。修正するのはmodule6と理解しました。

下記URLに修正後のmodule6をアップしました。
https://ideone.com/8xCSwr
これをまるごとコピーして、実行した時、期待した結果にならないならその旨連絡ください。
その時は、こちらの（マクロが組み込まれた）excelファイル自体をアップします。(ideoneではありません。他のサイトです)

この回答へのお礼

tatsu99様

おはようございます。

私が知識不足、説明不足の結果、何度もお手数をおかけしておりまして
本当に申し訳ありません。

下記、回答させて頂きます。

＞module4
＞https://ideone.com/JRtx04
>>達成率も同じように④合計ではなく④合計-③合計の達成率としたいです。
＞module4は既に実施済みです。修正するのはmodule6と理解しました。
→申し訳ありません、module6は達成率は当初のままで大丈夫です。
module4が数量と売上が混在しております為、以前お願いした下記内容になります。
ですが、やはりmoduke4は達成率が合計④を抽出しているように思えます。
例)
抽出元：27行目の数字として表示したい数字
①計画：G27-G26の数字(0)　➁実績：I27-I26の数字(0)　③達成率：①/➁
この式に当てはめると本来は0％となるようにしたいのですが
抽出先はE列：計画　0　F列：実績　0　G列：達成率161％　のようになってしまいます。。
私の説明の仕方、私の理解の仕方が違っているのかもしれません。申し訳ありません。

>下記URLに修正後のmodule6をアップしました。
>https://ideone.com/8xCSwr
>これをまるごとコピーして、実行した時、期待した結果にならないならその旨連絡ください。
>その時は、こちらの（マクロが組み込まれた）excelファイル自体をアップします。(ideoneではありません。他のサイトです)
→お忙しい中、何度もお手数をおかけして申し訳ありません。
やはり期待した結果になりませんでした。。。

お礼日時：2019/03/15 10:20

No.36 回答者： tatsu99 回答日時： 2019/03/14 16:18

＞また、module5なのですが前回合計④の数字から合計③の数字になるように

＞修正頂きましたが、達成率が合計④の数字を抽出するような設定になっていることに
＞本日気が付きました。こちらの確認漏れで申し訳ございません。
＞こちらも修正頂くことは可能でしょうか？

すみません。なんのことか良く判りません。
③合計、④合計であれば、module4,6の話ですが、module5なので、その件ではないですよね。
もっと、具体的に説明していただけませんでしょうか。

この回答へのお礼

>すみません。なんのことか良く判りません。
>③合計、④合計であれば、module4,6の話ですが、module5なので、その件ではないですよね。
>もっと、具体的に説明していただけませんでしょうか。
→すみません、いろいろと混乱しておりました( ﾉД`)
また、説明不足で申し訳ございません。
仰る通り下記module4についてでした。
↓
module4
https://ideone.com/JRtx04

達成率も同じように④合計ではなく④合計-③合計の達成率としたいです。

お礼日時：2019/03/14 16:40

No.35 回答者： tatsu99 回答日時： 2019/03/14 16:07

>For i = 0 To UBound(month_flg)→この部分です。

module6の先頭は以下のようになっていますか。
Dim month_flg As Variant '到来月対応編集フラグ・・・・この行があるか確認してください。



Option Explicit

Dim month_flg As Variant '到来月対応編集フラグ

Public Sub 最優秀個人数量売上2()

この回答へのお礼

こちらに関しては仰る通り、上記行が漏れておりました。
大変失礼いたしました。

module6についてですが、修正版そのままコピーしましたが
やはり、改善されません。。。

何度もお手数をおかけするのも申し訳ないので
改善しないようなら、修正前のまま実績順で一旦集計し、並べ替えをしようかと考えております。

本当にお手数をおかけして申し訳ありません。

お礼日時：2019/03/14 16:32