ดู Code VBA คำนวนหา Mean, S.D. ของข้อมูลแจกแจงความถี่ให้หน่อยครับ

[4ko6yon61]

Code: Select all

Sub MeanCal()
For r = 2 To 6
    Sheets("Sheet1").Cells(r, "J").Value = Application.WorksheetFunction.SumProduct(Range("B1:I1"), Range(Cells(r, "B"), Cells(r, "I"))) / Cells(r, "A").Value
Next
End Sub

Sub SDCal()
For r = 2 To 6
    Sheets("Sheet1").Cells(r, "K").Value = Application.WorksheetFunction.**********
Next
End Sub

สูตร = SQRT(SUMPRODUCT(((B1:I1)-J2)^2,B2:I2)/(A2-1))

SumProduct(((Range("B1:I1")) - Cells(r, "A").Value) ^ 2, Range(Cells(r, "B"), Cells(r, "I"))) / (Cells(r, "A").Value -1)

[4ko6yon61]

หลังจากนั่งคิดอยู่พักใหญ่ ได้โค้ดประมาณนี้ครับ

Code: Select all

Sub MeanCal()

Dim ArrayA As Variant
Dim ArrayC As Variant

ArrayA = Worksheets("Sheet1").Range("B1:I1").Value
ArrayC = ArrayA

Application.ScreenUpdating = False

If Sheets("Sheet1").Cells(2, 1).Value = "" Then
MsgBox "Please import data"
Else

For r = 2 To 6
    Sheets("Sheet1").Cells(r, "J").Value = Application.WorksheetFunction.SumProduct(Range("B1:I1"), Range(Cells(r, "B"), Cells(r, "I"))) / Cells(r, "A").Value

                If Sheets("Sheet1").Cells(r, 1).Value <> "" Then
                    For i = 1 To 8
                        ArrayC(1, i) = (ArrayA(1, i) - Cells(r, "J").Value) ^ 2
                    Next i
                    Sheets("Sheet1").Cells(r, "K").Value = Application.WorksheetFunction.SumProduct(ArrayC, Range(Cells(r, "B"), Cells(r, "I"))) / Cells(r, "A").Value
                End If
Next r

End If
Application.ScreenUpdating = True
End Sub