平均値を出すVBAを教えて下さい！

質問

平均値を出すVBAを教えてください！！
下記の関数をCA2に入れて下に引っ張って使っているのですが、
データ量が多すぎて砂時計状態のまま動きません！！ データはA列～BVまでで50万行位あります。
一番上は見出しです。
VBAで関数を使わないで平均値を出す方法を教えてください！！
C列に日付が記入されており、シート11のセルで期間を以上と以下で設定しています。

答えが一緒であればどんな方法でも構いません。
ぜひよろしくお願いします。

=AVERAGEIFS(AC:AC,A:A,A10,C:C,Sheet11!$A$1,C:C,Sheet11!$B$1,AB:AB,2)

回答No.2

imogasi

配列に、エクセルシートデータを入れると処理が早いとのＷＥＢの記述がある。
それで下記をやってみた。
ーー
モジュール外に（Ｍｏｄｕｌｅ１など、全体コードの一番上に貼り付け。時間計測のためのＡＰＩを使うため）下記１行を置く（関数の利用宣言）
Public Declare Function timeGetTime Lib "winmm.dll" () As Long
標準モジュールに下記を貼り付け
Sub test06()
nStart = timeGetTime()
Dim a As Variant
a = Range("A1:C500000")
x = 0: s = 0
For i = 2 To 500000
If a(i, 2) >= #3/1/2016# And a(i, 3) = "男" Then
x = x + 1
s = s + Cells(i, 1)
'MsgBox a(i, 2)
End If
Next i
nEnd = timeGetTime
MsgBox (CDbl(nEnd) - CDbl(nStart)) / 1000 & "秒"
MsgBox s / x
End Sub
テストデータは、第２行から第５０万行、
Ａ列に平均を考える計数、Ｂ列に日付（シリアル値であること）、Ｃ列に性別の男か女を入力されている。
上記では、条件は３月以降のデータで、かつ男のデータ（の平均を計算）。
ーーー
実行時間は0.5秒ぐらいとでた。
ーー
＞VBAで関数を使わないで平均値を出す方法を教えてください
こんなのは、コンピュタープログラムの１番はじめにやる、例題のアルゴリズムだよ。
むつかしいことから入って、基礎学習を飛ばしているのかな。
ーー
ＶＢＡ処理でとか、コンピュターの処理速度を上げる方法を明確に説明できる人は
少ないだろうと推測する。コンピュターの基礎の基礎（メモリやアドレスやＯＳのプログラムを実行する仕組みなど）をわかってないと理由を示して説明できないが、そんな回答者をここに求めても無理だろう。他の同時実行プログラムも影響する。
コンピュターの「りソース」というものを勉強するのがよい。
データ（フィールド・列）の中身の多様性はあまり影響ないでしょう。上記の男女が多数の商品の１つに置き換わっても。
データのことは何も質問に説明もない。式などストレートに挙げず、文章で説明を望む。
ー
エクセル関数を使って、配列で処理を試みたが、条件がつくとうまく行かなかった（条件なしの（全データの）ＡｖｅｒａｇｅやＳＵＭ関数なら配列データを使える。小生の力不測？）
＝＝＝＝
方法転向のすすめ
（１）ピボットテーブルを使う、や
（２）ＡＤＯを使って、ＳＱＬ処理に持ちこめば、ＷＨＥＲＥやＨＡＶＩＮＧが使えるので
やりやすいかも。ＳＱＬには、ＡＶＧ関数がある。

回答No.1

chie65535

＞50万行位あります。

50万件あると、VBAでやっても「AVERAGEIFSよりも遅い」です。

こういうのは「条件に合う行の行数を数える」「１００分割するなどして、５０００件づつの小計を出す」「５０００件づつの小計を足して総合計を出す」「総合計を、条件に合う行の行数で割って、平均を出す」など、作業用セルを用いて、処理を小分けして下さい。

CA列、CB列、CC列、CD列が「未使用で空いている」と仮定します。

１．CA2に「=AND(C2>=Sheet11!$A$1,C2<=Sheet11!$B$1,AB2=2)*1」と入れます。

２．CB2に「=AC2*CA2」と入れます。

３．CA2、CB2を範囲選択して「Ctrl＋C」を押します。

４．CA3～CB500000を選択します。表の「データが最終行まで埋まっている列」で「Ctrl＋↓」を押せば表の最終行まで飛びますから、表の最終行に飛んだら、CB列に移動。CB列の最終行にカーソルを合わせ、「Shift＋Ctrl＋↑」「Shift＋↓」「Shift＋←」の順に押すと、CA3～CB500000が選択できます。

５．選択したら「Ctrl＋V」を押して貼り付けします。貼り付けに時間がかかりますが、辛抱強く待ちましょう。

６．CC2に「=SUM(OFFSET(CA$2,(ROW()-2)*5000,0,5000,1))」と入力します。

７．CC2をコピーしてCD2に貼り付けします。

８．CC2～CD3をコピーして、CC３～CD101に貼り付けます。CC列は「5000行ごとの、条件に一致する行数」に、CD列は「5000行ごとの、条件に一致する小計」が、100個づつ出来ます。

９．「=SUM(CD2:CD101)/SUM(CC2:CC101)」で平均を求めます。