[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBA：各列80以上の場合の平均値を出力したい』（あんこ）
初めまして。
題記件、思い通りの処理結果とならず苦戦しております。
(マクロ初心者でこの夏にマスター出来るようになりたいと思い、本で勉強している最中ですが、中々思うようにいかず…お力お貸しください）
＜やりたい事＞
各列ごとに3～12行目の値が80以上のものだけで、1行目に平均値を出力したい。
A B C D E
1 86.6 93.2 90 90 96 2 3 100 88 100 100 90 4 80 99 80 80 90 5 20 20 20 20 20 6 40 40 35 20 40 7 70 70 35 40 70 8 50 50 20 70 50 9 70 100 40 50 99 10 80 80 70 100 99 11 20 20 50 80 99 12 40 99 35 20 99
Sub test()
Dim Lrow As Integer
Dim Ave(5) As Integer
Dim con As Integer
Lrow = Range("A3").End(xlDown).Row
For n = 1 To 5
For J = 3 To Lrow
If Cells(J, n).Value >= 80 Then
con = con + 1
Ave(n) = Ave(n) + Cells(J, n).Value
End If
Next J
Next n
For n = 1 To 5
Cells(2, n).Value = Ave(n) / con
Next n
End Sub
これでやると、変数「con」の値が各列ごとではなく、A列からE列の合計値となってしまい、Cells(2, n)に平均値を出力すると、平均が小さく計算されてしまいます。
conを1列ずつリセットするような処理が必要なのだと思いますが、その辺の考え方や、変数J, n の順番をどうするべきか…等がいまいち分かっていません。
考え方を教えて頂ければ幸いです。宜しくお願いいたします。
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
ちらみなので ＾＾； 外していましたらお許しを conも五個いるのでは。。。＾＾； m(__)m （隠居Ｚ） 2021/08/14(土) 14:40
私もVBA学習中の身ですので偉そうな事は申せませんが、 モジュールの先頭に Option Explicit を記述し、変数の宣言を強制する事をお勧めします。 VBEのツール→オプション→編集タブにある「変数の宣言を強制する」にチェックを入れると自動で記述されます。
Sub test1()
Dim Lrow As Integer
Dim Ave(5) As Integer
Dim con As Integer
Dim n As Long, j As Long
Lrow = Range("A3").End(xlDown).Row
For n = 1 To 5
con = 0 '←列が変わる度、conを0にする
For j = 3 To Lrow
If Cells(j, n).Value >= 80 Then
con = con + 1
Ave(n) = Ave(n) + Cells(j, n).Value
End If
Next j
Cells(2, n).Value = Ave(n) / con '←列単位で処理するよう、nのループ内で計算する
Next n
End Sub
以下私個人の主観です。 ループが多重になると複雑になるので、 ステップ実行して、ローカルウインドウ等を確認するのが習得の近道かと思います。 変数の順番は詰まるところ個人の自由だと思います。 社内ルール等が無ければ、自分の分かりやすい決まりで良いでしょう。 最初は変に格好とか気にしない方がいいです。
ひとつ加えるなら、今回のケースだと、Ave()は必ずしも配列である必要は無いと思います。 あとは諸先輩方がアドバイスを下さるのを期待して下さい。 -- 衝突してました。 隠居Zさんのおっしゃる通り、con()を5つ用意するのもアリです。 様々なアプローチがあるので、まずは色々試すのが良い時期だと思います。
（名無し） 2021/08/14(土) 14:51
名無しさん
ご親切に考え方まで記載して頂き、ありがとうございます。大変勉強になります。
教えて頂いた方法で、正しく出力できました。
con = 0を設定すればいいのでは…という所までは自分でもたどり着いたのですが、そうすると今度は最後の列のconに代入された値で計算されてしまい行き詰っていたところでした。
なるほど、一列ずつnのループ内で結果を処理してしまえばよいのですね。。大変勉強になりました。
まだループの組み合わせが自分の中で理解しきれていないので、今後習得していけるように頑張りたいと思います。
いかに、シンプルに構造を組み立てて考えられるか…が大切そうですね。
ありがとうございました。
（あんこ） 2021/08/14(土) 15:05
名無しさんからご提示頂いたコードでは、 Dim n As Long, j As Long を設定していて、
一方私が最初に提示したコードでは、n , j については特にDim～で変数を宣言していません。
全て変数は宣言した方がいいのでしょうか。
本を見ていると、変数を宣言しているものと、していないものと混在していて、
その分け方の違いがいまいちわかりませんでした。
下記アドバイス頂きましたが、変数の宣言を強制する事で、処理速度が速くなったり、エラーを見つけやすくなったり…等のメリットがあるからでしょうか。
＞モジュールの先頭に Option Explicit を記述し、変数の宣言を強制する事をお勧めします。
宜しくお願いいたします。
（あんこ） 2021/08/14(土) 15:12
たとえば今回、Lrowという変数がありますが、 これをタイプミスしてLrwoと入力したとします。 変数の宣言を強制していないと、新たにLrwoという変数を定義した事になります。 短いコードなら気付き易いですが、長かったり複雑だったりすると気付かない事もあります。 自分の意図しない変数に値が入力され、本来の変数に入ってないけど何故だ、みたいな事を防げます。
宣言をしない変数は全てVariant型として定義されます。 超極論すれば全部の変数Variant型でも動くといえば動きます。 ただ、変数宣言の必要性については、Web上で多くの方が語られているので、 「変数宣言の必要性」とかで検索した方が理解が深まると思います。 （正直、私がここで書いたところで、それらのサイトの受け売りになるからです） いずれ変数宣言の必要性に気付くのであれば、学習初期に習得しておいた方が良いのではとのお節介でした。
（名無し） 2021/08/14(土) 15:45
重ね重ね、ありがとうございました。
そのようなミスも防げるのですね。
また、宣言しないとvariant型になってしまうという事も理解しました。
変数宣言の必要性、ネットで勉強してみます。
お忙しい中、ご丁寧なご回答に感謝いたします。
（あんこ） 2021/08/14(土) 16:03
その他で気になった点を書きます。 「Option Explicit」と並んで、「Option Base」という宣言があります。 https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/option-base-statement これによって、配列の添え字下限を「0」にするか「1」にするか、設定できます。 「Option Base」を省略した場合は、配列の添え字下限は「0」になります。
今回のケースで問題になることはありませんが、提示された 「Dim Ave(5) As Integer」は「Option Base」宣言されていない場合、添字が0～5の6要素だという点にもご注意を。 （mmm） 2021/08/14(土) 16:22
また、変数宣言時に、Dim Ave(1To 5) As integerとしてしまうのも手でしょうか？
色々ありがとうございます。お陰で理解が深まりました。
（あんこ） 2021/08/14(土) 17:09
なかなか、勘のよい方のようで。 >また、変数宣言時に、Dim Ave(1 To 5) As integerとしてしまうのも手でしょうか？ 逆に、そのように宣言する方が良いと思います。
VBAの場合は添え字の下限が「0」にも「1」にも設定できる点が逆にややこしくしていて、 私は他言語を主に扱うので、VBAで書く場合は、それに合わせて添え字下限「0」で統一しています。 但し、Split 関数のようにOption Base ステートメントの影響を受けずに添字の最小値が常に「0」になるような ものもあるので、下限「1」と思ってコーディングしている場合は注意が必要です。 https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/split-function
学習を始めたばかりのようなので、これから徐々に覚えていけばいいと思います。 私も、今、関数の勉強を一生懸命しております。
＞5列分の処理だけでいいものを6つ変数を宣言してしまった事で処理時間がそれだけ増えた 処理時間は関係ないです。 1要素分、メモリを余分に使っているだけなので、特にこの程度、気にすることはないです。 （mmm） 2021/08/14(土) 17:40
情報量が多すぎて迷惑かもですが....配列について追記 VBAでセル範囲をVariant型変数に代入した場合は2次元配列になりますが、 その場合は常に下限「1」となります。
下限が「0」とか「1」とか色々なパターンがでてきますので、 配列の上限、下限を返す関数が用意されています。 上限：UBound 関数 https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/ubound-function 下限：LBound 関数 https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/lbound-function 今回のケースだと↓のような感じで確認できます。
MsgBox "下限：" & LBound(Ave) MsgBox "上限：" & UBound(Ave) （mmm） 2021/08/14(土) 19:10
意外とVBAで関数も使えますので参考出品です。 たくさん引き出しを持てるよう、お互い頑張りましょうp(^^)q
Sub Sample()
Dim i As Long
Dim Rng As Range, iArg As Variant: iArg = ">=80"
For i = 1 To 5
With Cells(3, i)
Set Rng = .Resize(.End(xlDown))
.Offset(-2).Value = WorksheetFunction.SumIf(Rng, iArg) / WorksheetFunction.CountIf(Rng, iArg)
End With
Next i
End Sub （名無し） 2021/08/14(土) 20:51
ちんたら書いている間に、ほかの回答者さんと内容被りがありますがご容赦を。
■１
VBAのお勉強ということで、あえてそうしてるのかもしれませんが、問題自体は数式でも解決できますよね。
ということで数式をVBAで書き込むとしたらこんな感じじゃないですかね。
Sub 数式で解決してみる()
Range("A1:E1").Formula = "=ROUNDDOWN(SUMIF(A$3:A$12,"">=80"",A$3:A$12)/COUNTIF(A$3:A$12,"">=80""),1)"
End Sub
数式が残っちゃうのは困るということであれば↓のようにすればokです。
Sub 数式で解決してみる改()
With Range("A1:E1")
.Formula = "=ROUNDDOWN(SUMIF(A$3:A$12,"">=80"",A$3:A$12)/COUNTIF(A$3:A$12,"">=80""),1)"
.Value = .Value
End With
End Sub
■２
次に↑をVBAで１つずつ処理することを考えてみましょう。
行と列があるから二重ループになるのお気づきの通りだとおもいます。
そして条件を満たすセルの合計を条件を満たすセルの個数で割ればいいわけですから、コードにするとこんな感じですよね。
Sub 二重ループ処理で解決してみる壱()
Dim 行 As Long, 列 As Long
Dim 合計 As Long, 個数 As Long
For 列 = 1 To 5
合計 = 0
個数 = 0
For 行 = 3 To 12
If Cells(行, 列).Value >= 80 Then
合計 = 合計 + Cells(行, 列).Value
個数 = 個数 + 1
End If
Next 行
Cells(1, 列).Value = Int(合計 / 個数 * 10) / 10
Next 列
End Sub
■３
さて、↑では「合計」「個数」を列が変わるたびにリセットしているわけですが、お気づきのようにそれぞれ5個用意してもいいですね。
これをコードにしてみるとこんな感じです。
Sub 二重ループ処理で解決してみる弐()
Dim 行 As Long, 列 As Long
Dim 合計(5) As Long, 個数(5) As Long
For 列 = 1 To 5
For 行 = 3 To 12
If Cells(行, 列).Value >= 80 Then
合計(列) = 合計(列) + Cells(行, 列).Value
個数(列) = 個数(列) + 1
End If
Next 行
Cells(1, 列) = Int(合計(列) / 個数(列) * 10) / 10
Next 列
End Sub
■４
上記までで使っているのは静的配列と呼ばれるものであらかじめ個数を決めています。
しかし、80以上であるセルがいくつあるかあらかじめわかるわけではありません。
このようなときは動的配列というものを使うというアプローチもあります。
これをコードにするとこんな感じです。
Sub 動的配列を使ってみる()
Dim 行 As Long, 列 As Long
Dim 動的配列 As Variant
For 列 = 1 To 5
ReDim 動的配列(0) As Long
For 行 = 3 To 12
If Cells(行, 列).Value >= 80 Then
ReDim Preserve 動的配列(UBound(動的配列) + 1)
動的配列(UBound(動的配列)) = Cells(行, 列).Value
End If
Next 行
Cells(1, 列) = Int(WorksheetFunction.Sum(動的配列) / UBound(動的配列) * 10) / 10
Next 列
End Sub
■５
上記では、動的配列を使い１列ごとに計算して出力しました。
実はExcel君にとって、セルへの書き込みは割と大変な処理です。
一度で済ませることができるなら、そちらのほうは早く処理できる可能性があります。
今回のケースではA1～E1までの5つのセルだとわかっているので、5つがセットになった入れ物を用意して一気に出力すればよさそうですね。
決まった数がセットになった入れ物といえば「静的配列」です。
Sub 動的配列と静的配列を使ってみる()
Dim 行 As Long, 列 As Long
Dim 動的配列 As Variant
Dim 静的配列(1 To 5) As Double
For 列 = 1 To 5
ReDim 動的配列(0) As Long
For 行 = 3 To 12
If Cells(行, 列).Value >= 80 Then
ReDim Preserve 動的配列(UBound(動的配列) + 1)
動的配列(UBound(動的配列)) = Cells(行, 列).Value
End If
Next 行
静的配列(列) = Int(WorksheetFunction.Sum(動的配列) / UBound(動的配列) * 10) / 10
Next 列
Range("A1:E1").Value = 静的配列
End Sub
というようなことをステップ実行して研究してみると、参考になるかもしれません。
（もこな２） 2021/08/14(土) 20:54
沢山の引き出しを教えて頂き、ほんとありがとうございます。
VBAは作る人によって答えが１つではない…と聞きますが、ほんと色々出来るのですね！
自由自在に出来る様になると楽しくなりそうですね。
その境地に達せるようになる事を目指して、まずは教えて頂いたことを、ステップ実行してみます。
ありがとうございました！！
（あんこ） 2021/08/14(土) 21:41
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.