[[20160524142230]] 『VBA オートフィルタ後核当の番号を探しデータを潤x(もずく) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『VBA オートフィルタ後核当の番号を探しデータを条件によって抽出したい』(もずく)

初心者です。宜しくお願いします。

■Book1(50行あります)
A列B列 C列     D列       E列      F列      G列      H列
1 No. 開始(予定) 終了(予定) 開始(実績) 終了(実績)  ステータス   進捗率
2 1  1/1     1/2       1/1     1/2      完了     100
3 2  1/1     1/2                      着手
4 2  1/2     1/5                      着手
5 2  1/3     1/6                      着手
6 2  1/4     1/6       1/4     1/6      完了     100

■Book2
A列 B列     
1  達成率
2  100
3  34
4  100
5  0 
6  100

【手順】
1.「Book1」のステータスを完了以外にする(フィルターをかける)
2.「Book1」のNo.+1した数を「Book2」のA列と同じ数字を参照し「Book1」に記載する
・「Book2」の達成率が100の場合、「Book1」の開始(予定)終了(予定)を開始(実績)終了(実績)にコピー。進捗率に100を記載する
・「Book2」の達成率が0の場合、特になし。
・「Book2」の達成率がそれ例外の場合、「Book1」の開始(予定)を開始(実績)が空白であればコピー。

【結果】
A列B列 C列     D列       E列      F列      G列      H列
1  No. 開始(予定) 終了(予定) 開始(実績) 終了(実績) ステータス  進捗率
3  2  1/1     1/2       1/1             着手
4  2  1/2     1/5       1/2     1/5      着手    100
5  2  1/3     1/6                       着手

上記の手順をマクロで実行したいです。
可能でしたらコメントでプログラムの意味も教えて頂きたいです。
お手数お掛けしますが宜しくお願いします。

< 使用 Excel:Excel2013、使用 OS:Windows7 >


よくわからないところがあります。まずフィルターをかけた結果は

3  2  1/1     1/2                      着手
4  2  1/2     1/5                      着手
5  2  1/3     1/6                      着手

ですよね。
で、No + 1 ということですから、これらすべての行に対して 3 で Book2 を参照?
そうすると 全行、34 ですよね。 
それなのに、そちらが提示した、これら3行の結果は、ことなっていますよね?
最初の行は 開始(実績)が転記されている。
次の行では 開始(実績)と終了(実績)が転記され、しかも、なぜか進捗率が 100% ?
最後の行では、何も 変化がない。

それと、進捗率が 100% なら ステータスを "完了" にしなくてもいいのですか?

(β) 2016/05/24(火) 21:49


 それと、もしかして・・・・・

 アップされたレイアウト、Book1もBook2 もともに、一番左が、先頭行から 1,2,3,・・・・になっていて
 それが A列 となっていますけど、実際は、これは、エクセルシートの行番号で、シート自体には、この部分がないのでは?

 たとえば Book2 でいうと、列が A,B ではなく 列は A だけで、そこに上から

 達成率 
 100 
 34 
 100 
 0  
 100 

 このようになっているのでは?

(β) 2016/05/25(水) 08:43


 もう1つ確認です。

 ブックの構成ですけど

 マクロブック、Book1.xlsx、Book2.xlsx の3ブック構成を考えていますか?

 それとも マクロブックが Book1 そのもの、つまり Book1.xlsm 、それと Book2.xlsx の2本立て構成を考えていますか?

(β) 2016/05/25(水) 08:46


大変申し訳ございません。「No.」の番号を間違えてしまいました。
ご指摘の通り進捗率が100なら結果のステータスも完了に変更になります。
ステータスの更新は「終了(実績)」が入力されると自動で変更されるの設定されています。
「Book1」「Book2」の一番左は行の番号でシート自体にこの番号は載っていません。
「Book2」の進捗率はA1のセルに記載されています。
ブックの構成ですが、マクロブック、Book1.xlsx、Book2.xlsxの3ブック構成を考えています。

いろいろな不備がありましたので下記に修正した「Book1」「Book2」「結果」を載せます。
※修正箇所
 Book1の行の番号削除、No.の修正
 Book2の行の番号削除、No.の修正、ステータスの更新
 結果の行の番号削除、No.の修正、ステータスの更新

 ■Book1(50行あります)
A列 B列     C列       D列      E列    F列      G列
No. 開始(予定) 終了(予定) 開始(実績) 終了(実績)  ステータス    進捗率
1  1/1     1/2     1/1     1/2    完了     100
2  1/1     1/2                 着手
3  1/2     1/5                 着手
4  1/3     1/6                 着手
5  1/4     1/6     1/4     1/6    完了     100

■Book2
A列     
達成率 (A1のセルに表示している)
100   (A2のセルに表示している)
34   (A3のセルに表示している)
100   (A4のセルに表示している)
0    (A5のセルに表示している)
100   (A6のセルに表示している)

【結果】(Book1)
A列 B列     C列       D列      E列      F列      G列
No. 開始(予定) 終了(予定) 開始(実績) 終了(実績) ステータス  進捗率
2  1/1     1/2       1/1             着手
3  1/2     1/5       1/2     1/5      完了    100
4  1/3     1/6                       着手

不備が多く申し訳ありません。
宜しくお願いします。

(もずく) 2016/05/25(水) 10:01


 いろいろ思うところはありますが、とりあえず、そちらの提示仕様にあわせたコードです。
 (思うところは、レスをわけて後ほどアップします)

 Sub Sample()
    Dim shM As Worksheet
    Dim shL As Worksheet
    Dim c As Range
    Dim dic As Object
    Dim n As Long
    Dim ratio As Long

    Application.ScreenUpdating = False              '処理中の画面の動きを隠す

    Set shM = Workbooks("Book1.xlsx").Sheets(1)     '元ブックのシート
    Set shL = Workbooks("Book2.xlsx").Sheets(1)     '達成率ブックのシート
    Set dic = CreateObject("Scripting.Dictionary")  '達成率格納辞書

    '達成率を辞書に格納
    For Each c In shL.Range("A2", shL.Range("A" & Rows.Count).End(xlUp))
        n = n + 1
        dic(n) = c.Value
    Next

    'オートフィルターと達成率反映処理
    shM.AutoFilterMode = False '念のためオートフィルター解除
    shM.Range("A1").CurrentRegion.AutoFilter Field:=6, Criteria1:="<>完了"
    'リスト領域のA列のセルを取り出す
    For Each c In shM.AutoFilter.Range.Columns(1).Cells
        If Not c.EntireRow.Hidden Then      '表示(抽出行)なら
            '達成率
            ratio = dic(c.Value)
            If ratio > 0 Then   '未登録あるいは0%なら何もしない
                With c.EntireRow    '当該行
                    .Range("G1").Value = ratio                  '達成率
                    .Range("D1").Value = .Range("B1").Value     '開始(実績)
                    If ratio = 100 Then
                        .Range("E1").Value = .Range("C1").Value '終了(実績)
                        .Range("F1").Value = "完了"
                    End If
                End With
            End If
        End If
    Next

 End Sub

(β) 2016/05/25(水) 13:29


 一応、コードはアップしましたが、感想です。

 1.まず、Book2のリストですけど、これって、Book1 の No に対応して2行目から記入されているんですよね。
  これが、どこかから取り込んだデータなのか、手打ちのデータなのかはわかりませんけど、手打ちだとして
  Book1 のNo(あるいは行そのもの?)にあわせながら数値をいれていくというのは、入力しやすいですか?
  Book1 のどのデータの進捗率なのか、目を皿のように(?)Book1を見ながら順番を間違わないように入力・・・
  できあがったリストの値をみても、34%・・・どのデータが34%なんだろう・・・?
  Book1を見て、行を数えて(あるいはNo を見て)あぁ、このデータに対する進捗率なんだなと。

 2.わざわざ Book2 にわけてリスト登録する意味は?
  最初から Book1 の G列に記述したほうが、どのデータに対する進捗率なのか、明快にわかりますよね?
  どこかから取り込むデータだとしても、Book1 の G列に取り込めばいいわけです。

 3.完了以外をオートフィルターで抽出する、その操作に意味があるなら、当方としてどうこういうことではありませんが
  目的が進捗率に合わせて、開始(実績)、終了(実績)、ステータス を適切にセットするなら、オートフィルターも不要。
  かつ、これらは、すべて 数式をいれておけばマクロも不要。
  G列に(どうしてもBook2のA列だというなら、Book2のA列に)進捗率をいれたら、それに見合ったセットを
  数式で行えばいいのでは?

(β) 2016/05/25(水) 13:42


コメントも記載して頂きありがとうございます。

1.⇒Book2は取り込みのデータです。
毎度目を皿のように見比べるのは大変ですが、リスト数も少ないのでこのまま使用しようと思います。

2.⇒Book1とBook2は異なる人からそれぞれ提出して頂いているので別ブックになっています。

3.結果報告に抽出件数などを記載するのでこのまま使用させて頂きます。
元データを変更せず手短に処理をしたかったので今回はマクロを希望しましたが、関数などで処理もできるよう考えたいと思います。

βさんご協力ありがとうございます!
(もずく) 2016/05/25(水) 17:17


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.