[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『変数と変数の間を平均する』(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
あと、Targetとか l とか、意味不明の代入多し。 これ本当に動きます?
また、Targetという変数は、プロシジャで使用される場合があるので、あまり宣言すべきではないです。
(???) 2014/08/27(水) 16:40
細かい説明は省きますけれど、単純に実行ステップ数の違いだと思います。 ループはまわさず、AVERAGEIFS 関数で求めたらどうでしょうか。 それであれば条件を変えるだけで、範囲をかえる必要は無いと思います。 (Mook) 2014/08/27(水) 16:42
ここでゆうと
上~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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.