[[20140827151553]]　『変数と変数の間を平均する』（kiwiii）　ページの最後に飛ぶ

『変数と変数の間を平均する』（kiwiii）

こんにちは。はじめまして。
目安とする値を探してその間を平均する方法を教えていただきたいです。

具体的に言いますと
A列は最初20行ほど空白で、そのあと2000行くらいが、100でそのデータ数を割った数ということで、0.00●から順に100まであります。
さらにB～Q列、AI～AQ列にはあと20行ほどデータがありますが、A列にはその最後の部分は空白です。

やりたいことは、A列の(●).5を超えたところ(以上)から、(●+1).5を超える一つ前のセル(未満)の行において
C～QについてはT～AFに、AI～AQに関してはAT～BBに順に平均値を出したいということです。
なのでT～AF、AI～AQの平均値というのは、101個の平均値が出るようになります。

20シートくらいあり、
そのうち左の2つを除く他のすべてのシートは同じことをさせます。

できる限りの知恵を振り絞って
下記のようなものを書いてみました…

まず
Macro4でデータのある一番上(2行目)からA列が0.5を超えるところの一個手前までを平均しています
Macro5では、0.5から99.5までを0.5ずつ探すのを繰り返し、
(●).5≦セル＜(●+1).5に該当するセルを平均します
Macro6では、A列の99.5からB列の最後の行までの行分のセルを平均します

これですと、Macro4はスムーズにできるのですが
Macro5を起動した途端、進むスピードが異常に遅くなり、全く進まなくなりました。
結局何分も待ってもほとんど進まず、最終的に応答なしになってしまいます。

説明が下手でややこしくてすみません
説明不足な点がたくさんあると思いますので、言ってくだされば情報を補足させていただきます。
ご教授のほどよろしくお願いします。。。

Sub Macro4()

Dim myStr As String, i

For i = 3 To Worksheets.Count

Dim k As Long, Target As Range

   For k = 2 To 3000
   If Worksheets(i).Cells(k, 1) >= 0.5 Then
      Set Target = Worksheets(i).Cells(k, 1)
      Exit For
   End If
Next k
   Worksheets(i).Range("T2") = Application.WorksheetFunction.Average(Worksheets(i).Range("C2:C" & k - 1))
   Worksheets(i).Range("AT2") = Application.WorksheetFunction.Average(Worksheets(i).Range("AI2:AI" & k - 1))

Next i
Call Macro5
End Sub

'- - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub Macro5()

Dim myStr As String, i

For i = 3 To Worksheets.Count

   Dim lRowT As Long
   lRowT = Worksheets(i).Range("T" & Rows.Count).End(xlUp).Row

    j = 1
    l = j - 1
    Do While j < 99
       Dim k, n As Long, Target As Range
       For k = 2 To 3000
       For n = 2 To 3000
       If Worksheets(i).Cells(k, 1) >= j + 0.5 Then
          Set Target = Worksheets(i).Cells(k, 1)
          Exit For
       End If
       If Worksheets(i).Cells(n, 1) >= l + 0.5 Then
          Set Target = Worksheets(i).Cells(n, 1)
          Exit For
       End If

    Next n
    Next k

   Worksheets(i).Cells(lRowT + 1, 20) = Application.WorksheetFunction.Average(Worksheets(i).Range("C" & n & ":C" & k - 1))
   Worksheets(i).Cells(lRowT + 1, 46) = Application.WorksheetFunction.Average(Worksheets(i).Range("AI" & n & ":AI" & k - 1))

    j = j + 1
    Loop

Next i
Call Macro6
End Sub

'- - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub Macro6()
Dim myStr As String, i

For i = 3 To Worksheets.Count

Dim k As Long, Target As Range
For k = 2 To 3000

   If Worksheets(i).Cells(k, 1) >= 99.5 Then
      Set Target = Worksheets(i).Cells(k, 1)
      Exit For
   End If
Next k
   Dim lRowB As Long
   lRowB = Worksheets(i).Range("B" & Rows.Count).End(xlUp).Row
    With Worksheets(i)
   Worksheets(i).Cells(lRowB + 1, 20) = Application.WorksheetFunction.Average(Worksheets(i).Range("Cells(k, 3):Cells(lRowB, 3)"))
   Worksheets(i).Cells(lRowB + 1, 46) = Application.WorksheetFunction.Average(Worksheets(i).Range("Cells(k, 35):Cells(lRowB, 35)"))
    End With

Next i
End Sub

＜使用 Excel：Excel2010、使用 OS：Windows8 ＞

 回答じゃないけど、とりあえずコードを整形。
・インデントの調整
・未使用変数「myStr」の宣言削除
・Macro5において足りない変数宣言を追加
　
Option Explicit
Sub Macro4()
    Dim i As Long, k As Long
    Dim Target As Range
    For i = 3 To Worksheets.Count
        For k = 2 To 3000
            If Worksheets(i).Cells(k, 1) >= 0.5 Then
                Set Target = Worksheets(i).Cells(k, 1)
                Exit For
            End If
        Next k
        Worksheets(i).Range("T2") = Application.WorksheetFunction.Average(Worksheets(i).Range("C2:C" & k - 1))
        Worksheets(i).Range("AT2") = Application.WorksheetFunction.Average(Worksheets(i).Range("AI2:AI" & k - 1))
    Next i
    Call Macro5
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub Macro5()
    Dim i As Long, j As Long, k As Long, l As Long, n As Long
    Dim lRowT As Long
    Dim Target As Range
    For i = 3 To Worksheets.Count
        lRowT = Worksheets(i).Range("T" & Rows.Count).End(xlUp).Row
        j = 1
        l = j - 1
        Do While j < 99
            For k = 2 To 3000
                For n = 2 To 3000
                    If Worksheets(i).Cells(k, 1) >= j + 0.5 Then
                        Set Target = Worksheets(i).Cells(k, 1)
                        Exit For
                    End If
                    If Worksheets(i).Cells(n, 1) >= l + 0.5 Then
                        Set Target = Worksheets(i).Cells(n, 1)
                        Exit For
                    End If
                Next n
            Next k
            Worksheets(i).Cells(lRowT + 1, 20) = Application.WorksheetFunction.Average(Worksheets(i).Range("C" & n & ":C" & k - 1))
            Worksheets(i).Cells(lRowT + 1, 46) = Application.WorksheetFunction.Average(Worksheets(i).Range("AI" & n & ":AI" & k - 1))
            j = j + 1
        Loop
    Next i
    Call Macro6
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub Macro6()
    Dim i As Long, k As Long
    Dim lRowB As Long
    Dim Target As Range
    For i = 3 To Worksheets.Count
        For k = 2 To 3000
            If Worksheets(i).Cells(k, 1) >= 99.5 Then
                Set Target = Worksheets(i).Cells(k, 1)
                Exit For
            End If
        Next k
        lRowB = Worksheets(i).Range("B" & Rows.Count).End(xlUp).Row
        With Worksheets(i)
            Worksheets(i).Cells(lRowB + 1, 20) = Application.WorksheetFunction.Average(Worksheets(i).Range("Cells(k, 3):Cells(lRowB, 3)"))
            Worksheets(i).Cells(lRowB + 1, 46) = Application.WorksheetFunction.Average(Worksheets(i).Range("Cells(k, 35):Cells(lRowB, 35)"))
        End With
    Next i
End Sub

 で、疑問点とか。
・TargetにSetしてるけど使用してないみたい？
・Macro5でのFor Nextのループって期待している通り？
　→kとnのところ。
・実際のデータがないとマクロが動かせない、
　そして実際のデータがどうなっているかの説明が良く分からなかった。
　→シートのイメージがあったほうが回答が付く気がする

（ご近所PG） 2014/08/27(水) 16:38

Macro5の、kとnのループは、別々に分けるのでは？現状だと3000*3000回もループしますよ？
（Exit Forは1段階しか抜けないので、途中で一致してもkは必ず最後まで回るし…）

あと、Targetとか l とか、意味不明の代入多し。これ本当に動きます？
また、Targetという変数は、プロシジャで使用される場合があるので、あまり宣言すべきではないです。
（???） 2014/08/27(水) 16:40

 細かい説明は省きますけれど、単純に実行ステップ数の違いだと思います。
 ループはまわさず、AVERAGEIFS 関数で求めたらどうでしょうか。
 それであれば条件を変えるだけで、範囲をかえる必要は無いと思います。
（Mook） 2014/08/27(水) 16:42

みなさま、早々にありがとうございます(>_<)
まだ少し理解できていないので
シートのざっくりしたイメージをのせさせていただきます(データとはすべて数字です)
　
　　A　　B　　　　C　～　Q　　　・・　T　～　AF
　空白データ　データ　データ　　　C～Qのデータの平均値
　　・　　　　・　　　・　　　・　　　　A列の～0.5、0.5-1.5
　0.048 　　　・　　　・　　　・　　　　・・・98.5-99.5、
　0.096 　　　・　　　・　　　・　　　　99.5～C～Q各データの最終行まで　
　　・　　　　・　　　・　　　・　　　　　（101行になる）
　　・　　　　・　　　・　　　・
　0.484　　　・　　　・　　　・
　0.532
　0.581
　・　
　　・
　・
　1.404
　1.452
　1.501
　　・
　　・
　　・
　　・
　99.467
　99.515
　　・
　　・
　99.903　　　・　　　・　　　・
　99.951　　データ　データ　データ
　100　　　データ　　データ　データ
　　空白　　データ　　データ　データ
　シートにもよりますがここまでで2200行ほどです　

ここでゆうと
上~0.484、0.532~1.404、1.501~…~99.467、99.515~最終行というふうな行を
それぞれ平均していきたいのです…

ループや代入のところを指摘してくださっているんですが
そこに関しては、いろいろ試しているうちに自分でもわけがわからなくなってしまって　
今に至ります。すみません。。

ちなみにですが
最初に書いたもので
ものすごい時間をかけて1シートのたった5行ほど進みました。。
動いているとは言いませんね(*_*)

（kiwiii） 2014/08/27(水) 17:29

 数式を使ったサンプルですけれど。
 Sub Sample()
    For i = 3 To Worksheets.Count
        With Worksheets(i)
            .Cells(2, "T").Resize(1, 15).FormulaR1C1 = "=AVERAGEIFS(C[-17]:C[-17],C1:C1,""<0.5"")"
            .Cells(2, "AT").Resize(1, 9).FormulaR1C1 = "=AVERAGEIFS(C[-11]:C[-11],C1:C1,""<0.5"")"
            For d = 1 To 99
                .Cells(d + 2, "T").Resize(1, 15).FormulaR1C1 = _
                     "=AVERAGEIFS(C[-17]:C[-17],C1:C1,"">=" & (CDbl(d) - 0.5) & """,C1:C1,""<" & (CDbl(d) + 0.5) & """)"
                .Cells(d + 2, "AT").Resize(1, 9).FormulaR1C1 = _
                     "=AVERAGEIFS(C[-11]:C[-11],C1:C1,"">=" & (CDbl(d) - 0.5) & """,C1:C1,""<" & (CDbl(d) + 0.5) & """)"
            Next
            .Cells(102, "T").Resize(1, 15).FormulaR1C1 = "=AVERAGEIFS(C[-17]:C[-17],C1:C1,"">=99.5"")"
            .Cells(102, "AT").Resize(1, 9).FormulaR1C1 = "=AVERAGEIFS(C[-11]:C[-11],C1:C1,"">=99.5"")"

            With .Range("T2:AH101")
                .Value = .Value
            End With

            With .Range("AT2:BB101")
                .Value = .Value
            End With
        End With
    Next
 End Sub

 # For の範囲等を修正　@ 2014/08/28 07:57

（Mook） 2014/08/27(水) 18:24

みなさま、詳しい内容も含め、
いろいろご指摘いただいてありがとうございました。

Mook 様のサンプルを参考にして
また少し自分なりに修正して
なんとか思った通りのことができそうです。

しっかり理解するためにも
もう少しの部分は自分でがんばってみます。

もしもどうしてもわからなくなってしまったら
またお聞きします。

（kiwiii） 2014/08/27(水) 20:31

コメント返信：

[ 一覧(最新更新順) ]

[[20140827151553]] 『変数と変数の間を平均する』（kiwiii） ページの最後に飛ぶ

[[20140827151553]]　『変数と変数の間を平均する』（kiwiii）　ページの最後に飛ぶ