Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2013)
メッシュ状のデータの変換を高速化したい
投稿日時: 21/08/23 11:33:32
投稿者: mogpan

お世話になっています。
いま仕事でメッシュ状にあるデータを変換するVBAを組んでいるのですが、
これの処理が非常に遅く困っています。
遅い理由としては、セル入力が多いことだとはわかっています。
 
--やりたいこと--
1.5 1.6 2.0 2.1 ....
1.8 1.5 1.2 1.9 ....
2.0 2.3 2.4 1.5 ....
1.8 2.2 1.9 2.0
....
 
と縦横に数値のはいっているセルがたくさんあります。
こちらを、2*2のセルずつ平均をもとめ、隣のシートに入力したいです。
隣のシートのA1には、1.5、1.6、1.8、1.5の平均
A2には、2.0、2.1、1.2、1.9の平均
B1には、2.0、2.3、1.8、2.2の平均
というように入力したいです。
 
現状は、
        For i = 2 To r Step 2
            For j = 2 To c Step 2
 
                cnt = WorksheetFunction.Count(Range(Cells(i, j), Cells(i + 1, j + 1)))
                sum = WorksheetFunction.sum(Range(Cells(i, j), Cells(i + 1, j + 1)))
                 
                If cnt > 0 And sum > 0 Then
                    ave(i / 2, j / 2) = sum / cnt
                Else
                    ave(i / 2, j / 2) = 0
                End If
                 
            Next j
        Next i
 
で平均をもとめ、別シートに
        For i = 1 To r / 2
            For j = 1 To c / 2
                If ave(i, j) <> 0 Then
                    Cells(i + 1, j + 1) = ave(i, j)
                End If
            Next j
        Next i
 
で入力しています。
 
配列でいっきに入力できればいいのですが、
こういった場合にどのように配列に格納すればいいかわからず、
質問させていただきました。
ご教授いただけますと幸いです。
 
 
 

回答
投稿日時: 21/08/23 12:40:49
投稿者: WinArrow
投稿者のウェブサイトに移動

代入する側の話で
平均値をA1、A2、B1
という説明がありますが、なぜ、A2の次がB1なんですか?
 
 
常に4つのセルを取得し、4つのセルの平均を求める方法になりませんか?

投稿日時: 21/08/23 13:21:10
投稿者: mogpan

WinArrow さんの引用:
代入する側の話で
平均値をA1、A2、B1
という説明がありますが、なぜ、A2の次がB1なんですか?
 
 
常に4つのセルを取得し、4つのセルの平均を求める方法になりませんか?

 
説明がわかりにくく申し訳ありません。
2*2のセルを取得、その平均を求めたいですが、重複は省きたいといえば正しいでしょうか…?
もしくは、平均値を
A1、A2、A3、A4・・・・・・・・・・
B1、B2、B3、B4・・・・・・・・・・
C1、C2、C3、C4・・・・・・・・・・
と続けたいということです。
 
つまり、シート1のA1,A2、B1,B2を取得、平均値を→シート2のA1へ入力
シート1のA3,A4,B3,B4を取得、平均値を→シート2のA2とつづきますが、
シート1のB1、B2,C1,C2の平均値は不要となり、つぎは
シート1のC1,C2,D1,D2を取得、平均値を→シート2のB1へ入力したいです。
 
説明の足らない点はまたご説明させていただきたいので、
お聞かせください。

回答
投稿日時: 21/08/23 13:29:10
投稿者: Suzu

ave が 動的配列であり、余計な次元を持っていないのであれば
配列の要素それぞれに対し、個別に貼り付けを行う必要はありません。
 
Worksheets("Sheet2").Range("A1").Resize(UBound(ave, 1), UBound(ave, 2)) = ave()
 
の様に してしまえば良いです。
 
 
Offset を使えば、らくちんですね。
 
同様に、集計部分 についても 2x2 と決まっていますから
  WorksheetFunction.Count(Cells(i, j).Resize(2, 2))
 の様に 書けますね。 Sumも同様です。
 
 
Average関数を使わないのは、何かあるのですかね?

投稿日時: 21/08/23 13:41:28
投稿者: mogpan

Suzu さんの引用:
ave が 動的配列であり、余計な次元を持っていないのであれば
配列の要素それぞれに対し、個別に貼り付けを行う必要はありません。
 
Worksheets("Sheet2").Range("A1").Resize(UBound(ave, 1), UBound(ave, 2)) = ave()
 
の様に してしまえば良いです。
 
 
Offset を使えば、らくちんですね。
 
同様に、集計部分 についても 2x2 と決まっていますから
  WorksheetFunction.Count(Cells(i, j).Resize(2, 2))
 の様に 書けますね。 Sumも同様です。
 
 
Average関数を使わないのは、何かあるのですかね?

 
お返事ありがとうございます!
Average関数を使わなかった理由が、平均がマイナスの値になる場合は0にしたかったからのようです。
すみません、この部分を作ったのが別の人だったので疑問にも思わず転用していました。
 
不勉強で申し訳ないのですが、
「ave が 動的配列であり、余計な次元を持っていないのであれば
配列の要素それぞれに対し、個別に貼り付けを行う必要はありません。」
ということは、どういうことなのでしょうか…。
 
配列の理解が、
rng=Worksheets(1).Range("A1:B5")
MyAry=rng
Worksheets(2).Range("A1")=Myary
 
で一気に貼り付けられるというイメージです。
自分でAve(i,j)に格納した配列もまた、一気に貼り付けができるという意味でしょうか…

回答
投稿日時: 21/08/23 13:41:35
投稿者: Suzu

最初は
 

引用:
A1には、1.5、1.6、1.8、1.5の平均
A2には、2.0、2.1、1.2、1.9の平均
B1には、2.0、2.3、1.8、2.2の平均

 
これは、
 A1:B2 → A1
  C1:D2 → A2
 A3:B4 → B1
 ですよね?
 
 
mogpan さんの引用:

シート1のA1,A2、B1,B2を取得、平均値を→シート2のA1へ入力
シート1のA3,A4,B3,B4を取得、平均値を→シート2のA2とつづきますが、
シート1のB1、B2,C1,C2の平均値は不要となり、つぎは
シート1のC1,C2,D1,D2を取得、平均値を→シート2のB1へ入力したいです。

 
代入先の行列が逆になっていませんか?
コードを拝見する限りは、前者のコードになっていますよね?
 
行列 を逆ぐらいであれば
 
Worksheets("Sheet2").Range("A1").Resize(UBound(ave, 1), UBound(ave, 2)) = _
          Application.WorksheetFunction.Transpose(ave)
 
の様に、Transpose を使えば良いだけですがね。
 
 
 
また、コードを拝見する限り
引用:
For i = 2 To r Step 2
            For j = 2 To c Step 2
  
                cnt = WorksheetFunction.Count(Range(Cells(i, j), Cells(i + 1, j + 1)))

ですから、
 A1 がデータの開始点ではなく、B2 が開始点ですよね?
 説明を簡素化するために、A1 と記載しただけですよね?

回答
投稿日時: 21/08/23 14:21:49
投稿者: Suzu

引用:
Average関数を使わなかった理由が、平均がマイナスの値になる場合は0にしたかったからのようです。

 
であれば、配列に代入後 に 判定しても良いと思いますよ。
 
引用:
「ave が 動的配列であり、余計な次元を持っていないのであれば
配列の要素それぞれに対し、個別に貼り付けを行う必要はありません。」
ということは、どういうことなのでしょうか…。

 
配列の宣言として、
 
・固定配列 入りうる 最大次元を指定        Dim ave(1 to 100, 1 to 100) As Double
 
・動的配列 宣言時には、配列である事のみを指定   Dim ave() As Double
  → コード内にて、r と C の値を元に、ave の次元数を指定 ReDim ave(1 To r / 2, 1 To c / 2)
 
のどちらかを用いていると思います。
 
rng=Worksheets(1).Range("A1:B5")
MyAry=rng
Worksheets(2).Range("A1")=Myary

 
このコードの場合の MyAry は 宣言時には、次元を宣言しない動的配列になります。
 
Worksheets(2).Range("A1:B5")=Myary
としないと、A1 に、Myary(1,1) の値しか張り付きません。
 
 
固定配列の場合、
 ave(1 to 100, 1 to 100) だと、100行 100列分を準備されています。
 
 i と j を変数として、1こ1こ転記する分には問題ないですが
 
あ。。貼り付け先 も B2 からの貼り付けなのですね。。
 
 
一気に配列を はりつけようと
Worksheets("Sheet2").Range("B2").Resize(UBound(ave, 1), UBound(ave, 2)) = ave()
とすると、100行、100列分 張り付けられてしまいます。
 
 
今回は、rとc が判っていますから
Worksheets("Sheet2").Range("B2").Resize(r / 2, c / 2) = ave()
としてしまえば良いのですけどね。
 
 
 
テストコードを添付します。
参考にしてください。
 
Sub TEST()
  Dim i As Long, j As Long, r As Long, c As Long
' Dim cnt As Double, sum As Double
  Dim ave() As Double

  With Worksheets("Sheet1")
    r = .Range("B2").End(xlDown).Row
    c = .Range("B2").End(xlToRight).Column
    ReDim ave(1 To r / 2, 1 To c / 2)

    For i = 2 To r Step 2
      For j = 2 To c Step 2
'        cnt = WorksheetFunction.Count(.Cells(i, j).Resize(2, 2))
'        sum = WorksheetFunction.sum(.Cells(i, j).Resize(2, 2))
'        If cnt > 0 And sum > 0 Then
'          ave(i / 2, j / 2) = sum / cnt
'        Else
'          ave(i / 2, j / 2) = 0
'        End If
        ave(i / 2, j / 2) = WorksheetFunction.Average(.Cells(i, j).Resize(2, 2))
        If ave(i / 2, j / 2) < 0 Then ave(i / 2, j / 2) = 0
      Next j
    Next i
  End With

'  For i = 1 To r / 2
'    For j = 1 To c / 2
'      If ave(i, j) <> 0 Then
'        Worksheets("Sheet2").Cells(i + 1, j + 1) = ave(i, j)
'      End If
'    Next j
'  Next i
Stop

Worksheets("Sheet2").Range("B2").Resize(r / 2, c / 2) = ave()
'Worksheets("Sheet2").Range("B2").Resize(r / 2, c / 2) = Application.WorksheetFunction.Transpose(ave)

End Sub

回答
投稿日時: 21/08/23 14:49:25
投稿者: simple

質問者さんへの参考情報です。
# 皆さんのご指摘を読まれたあとでお読み下さい。
 
ご指摘のありましたとおり、高速化は、一括して書き込むところで効果が大きいわけですが、
元のデータを配列vに読み込んでから計算すると、この部分でも少し早くなると思います。
こちらのほうが約半分くらいになるようです。(1000行100列で計測)
データ量がさほど大きくなければ気にすることでも無いと思いますが。
 
計算式は、
Application.Average(v(j, k), v(j, k + 1), v(j + 1, k), v(j + 1, k + 1))
もし、平均がマイナスの時に0にするのであれば、
Application.Max(0,Application.Average(v(j, k), v(j, k + 1), v(j + 1, k), v(j + 1, k + 1)))
とすればよいと思います。
ご参考まで。

トピックに返信