我正在寻找一种方法来计算同一物料代码(数字)不同时间段的总和和标准差。它与excel中的小计功能非常相似,但是,与其将数字分组,创建新行并在同一列中插入小计-我想使用VBA自动执行此功能,并将小计放置在相邻的列或工作表中。我已经为小计记录了一个宏,但是,我需要在下一列中添加stdDev而不进行分组或破坏电子表格中的数据。我将需要此数据用于其他代码。
任何建议将不胜感激。谢谢
Sub stdDeviation(RN)
Dim FirstOccurrence As Long
Dim LastOccurrence As Long
Dim i As Integer
RN2 = RN
C = Sheets("CONETRAN").Cells(RN2, 2)
Do Until Sheets("CONETRAN").Cells(RN2, 2) <> C
RN2 = RN2 + 1
Loop
RN2 = RN2 - 1
FirstOccurrence = RN
LastOccurrence = RN2
For i = 1 To LastOccurrence
Sheets("conedetail").Cells(RN, 16).Value = Application.WorksheetFunction.StDev(Range("J" & FirstOccurrence & ":J" & LastOccurrence))
Next
End Sub
假设行B中有一个固定的数据块,$B$1:$B44931
并且假设列B中的所有值都已排序,则可以使用以下方法来实现所需的功能:
查找第一次出现的物料编号的行号(编号)
Dim FirstOccurrence As Long
FirstOccurrence = Range("B:B").Find(What:="47-901-049W2", After:=[B1], SearchDirection:=xlNext).Row
查找最后一次出现的物料编号的行号(编号)
Dim LastOccurrence As Long
LastOccurrence = Range("B:B").Find(What:="47-901-049W2", After:=[B1], SearchDirection:=xlPrevious).Row
在使用前两点分配的范围上执行StDev操作,并写入所需的任何单元格
Cells(1, 17).Value = Application.WorksheetFunction.StDev(Range("J" & FirstOccurrence & ":J" & LastOccurrence))
您可能需要将所有这些内容嵌套在一个循环中,以便继续在最后计算的StDev下面进行编写,并循环浏览所有项目代码。
为了使您的生活更轻松,我建议添加一个新列,复制B列中的所有值。选择所有新复制的单元格,单击“数据”选项卡->“删除重复项”。现在,在中使用此单元格范围For Loop
来运行搜索。
更新:
好的,因此您的代码是一次不错的尝试,但是有一些错误。主要两件事是:
您需要创建一个处理If Statement
第一个数据点的条件处理程序()。由于您是从“ B1”开始的,因此首先FirstOccurrence
需要为B2-1 = B1
您需要创建一个条件处理程序(If Statement
),以处理只有一个数据点的实例(即,当起点和终点都引用同一行时)。
试试下面的代码:
Sub stdDeviation()
Dim FirstOccurrence As Long
Dim LastOccurence As Long
Dim RN As Range
Dim workingRange As Range
Dim UniqueRange As Range
Dim i As Long
Set workingRange = Sheets("conedetail").Range("B1:B49999")
Set UniqueRange = Sheets("conedetail").Range("G1:G5") 'Insert the location of the extra column you created with all unique item codes
i = 1 'This sets up your writing position
'This loops throug all the unique item numbers and retrieves and calculates the necessary data
For Each RN In UniqueRange
'Need to place a control factor in for the very first set of data
If RN.Row = 1 Then
FirstOccurrence = workingRange.Find(What:=RN.Text, After:=[B1], SearchDirection:=xlNext).Row - 1
Else
FirstOccurrence = workingRange.Find(What:=RN.Text, After:=[B1], SearchDirection:=xlNext).Row
End If
LastOccurence = workingRange.Find(What:=RN.Text, After:=[B1], SearchDirection:=xlPrevious).Row
'Tests to see if only one occurrence, if no calculates stDev normally
If LastOccurence - FirstOccurrence = 0 Then
'Insert whatever you want it to do here if there is only one data point
Sheets("conedetail").Cells(i, 16).Value = 0
Else
Sheets("conedetail").Cells(i, 16).Value = Application.WorksheetFunction.StDev(Range("J" & FirstOccurrence & ":J" & LastOccurence))
End If
i = i + 1
Next RN
Set RN = Nothing
Set workingRange = Nothing
Set UniqueRange = Nothing
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句