質問が長くなりましたので、http://d.hatena.ne.jp/esecua/20100131 を御覧下さい。
一応マクロを作成してみましたが、
仕様の解釈に誤りがある場合はコメントください。
(特に結果がE列ではなくF列の場合は、コメントがある行の "E" を "F" に変更ください。)
一連の商品IDは連続していることを想定しています。
Option Explicit Sub EstimationCalc() Dim startRow As Long Dim endRow As Long Dim lastRow As Long lastRow = Range("A" & Rows.Count).End(xlUp).Row Dim avCount As Double Dim avSum As Double Dim isSold As Boolean Dim r As Long startRow = 2 Do While startRow <= lastRow endRow = startRow Do While Cells(startRow, "A") = Cells(endRow + 1, "A") endRow = endRow + 1 Loop avSum = 0 avCount = 0 isSold = False For r = startRow To endRow If Cells(r, "C").Value > 0 And Cells(r, "D").Value > 0 Then If Cells(r, "E").Value = 0 Then Cells(r, "E").Value = Cells(r, "D").Value / Cells(r, "C").Value End If avCount = avCount + 1 avSum = avSum + Cells(r, "E").Value isSold = True End If Next If isSold = False Then Range("E" & startRow).Resize(endRow - startRow + 1, 1).Interior.ColorIndex = 3 Else Range("E" & startRow).Resize(endRow - startRow + 1, 1).Interior.ColorIndex = 0 For r = startRow To endRow If Cells(r, "D").Value = 0 And avCount > 0 Then Cells(r, "E").Value = avSum / avCount ' 結果の出力がF列なら、EをFに変更してください。 End If Next End If startRow = endRow + 1 Loop End Sub
コメント(3件)
まあ、マクロではないので回答欄に書きませんが。
E2でもF2でもいいので次の数式を入れて、Ctrl+Shift+Enterで配列数式にして下にコピーしてください。
>>
=IF(D2<>"",D2/C2,IF(SUM(IF(A$2:A$3000=A2,D$2:D$3000,""))=0,0,AVERAGE(IF((A$2:A$3000=A2)*(D$2:D$3000<>""),D$2:D$3000/C$2:C$3000,""))))
<<
※数式中の3000は最終行の意味なので実際以上の数値にしてください。
更に、すべてのレコードに売上が計上されていない場合は0になるので、条件付き書式で0のときに赤にすればいいです。
また数式を使うと商品IDが連続である必要はありませんが、3000行になると処理が重くなるかもしれませんね。
行数が多く自動計算の場合は、ファイル自体の動作の歯切れが悪くなります。
(Core2Duo の古いPCで 3000行に式を入れると、一つのセルの変更をするたびに1~2秒
止まりました。)
ですから 3000行ものデータならマクロという選択肢も有効な気がします。
(私の環境では同じCore2Duoでは1秒も負荷はかかりませんでしたが、それはいいとして)
質問はマクロでということですので、どちらがいいという意味で書いたわけではなく、
別の方法として数式で行う方法を参考までにお知らせしただけですので誤解なきように。
ただ、数式にもデータの変更にリアルタイムに反映されるという利点もあります。
Excelで処理を実現する方法を考えた場合、大雑把に言うと
1 Excel自体の機能で実現する
2 数式で実現する
3 マクロで実現する
1で出きなければ2で、2でできなければ3でというように考えてみるのが
最適な方法を実現するための指標になると思いますので、参考までに。