エクセル マクロ ポイント100以上


質問が長くなりましたので、http://d.hatena.ne.jp/esecua/20100131 を御覧下さい。

回答の条件
  • 1人5回まで
  • 登録:2010/02/01 00:42:24
  • 終了:2010/02/06 12:35:50

ベストアンサー

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912010/02/01 02:03:16

ポイント100pt

一応マクロを作成してみましたが、

仕様の解釈に誤りがある場合はコメントください。

(特に結果が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
  • id:SALINGER
    これって数式だけでできますね。
    まあ、マクロではないので回答欄に書きませんが。
    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行になると処理が重くなるかもしれませんね。
  • id:Mook
    標準関数でも出来ますが、配列数式もSUMPRODUCT も計算パワーを要する関数ですから、
    行数が多く自動計算の場合は、ファイル自体の動作の歯切れが悪くなります。
    (Core2Duo の古いPCで 3000行に式を入れると、一つのセルの変更をするたびに1~2秒
    止まりました。)

    ですから 3000行ものデータならマクロという選択肢も有効な気がします。
  • id:SALINGER
    はい、最後に書いてあるように当然負荷はかかります。
    (私の環境では同じCore2Duoでは1秒も負荷はかかりませんでしたが、それはいいとして)
    質問はマクロでということですので、どちらがいいという意味で書いたわけではなく、
    別の方法として数式で行う方法を参考までにお知らせしただけですので誤解なきように。
    ただ、数式にもデータの変更にリアルタイムに反映されるという利点もあります。
    Excelで処理を実現する方法を考えた場合、大雑把に言うと
    1 Excel自体の機能で実現する
    2 数式で実現する
    3 マクロで実現する
    1で出きなければ2で、2でできなければ3でというように考えてみるのが
    最適な方法を実現するための指標になると思いますので、参考までに。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません