|
マクロで集計だけすれば善いなら
こんな、かな?
結果は、D列に書き込んでます
Public Sub TestCalc()
Dim i As Long
Dim lngTop As Long
Dim lngEnd As Long
Dim vntData As Variant
Dim vntResult As Variant
Dim vntSumA As Variant
Dim vntSumB As Variant
'データ先頭行
lngTop = 4
'データ最終行取得
lngEnd = Cells(65536, 1).End(xlUp).Row
'結果書き込み用配列の確保
ReDim vntResult(1 To (lngEnd - lngTop + 1), 1 To 1)
'データを配列に取得
vntData = Range(Cells(lngTop, 1), Cells(lngEnd, 3)).Value
'A列の合計とB列の"S"を除く合計の取得
For i = 1 To lngEnd - lngTop + 1
'A列の合計
vntSumA = vntSumA + vntData(i, 1)
'B列の"S"を除く合計
If vntData(i, 2) <> "S" Then
vntSumB = vntSumB + vntData(i, 2)
End If
Next i
'集計用配列に計算結果を代入
For i = 1 To lngEnd - lngTop + 1
vntResult(i, 1) = vntSumA * (vntSumB + vntData(i, 3))
Next i
'集計用配列をセルに書き込み
Range(Cells(lngTop, 4), Cells(lngEnd, 4)).Value = vntResult
End Sub
|
|