|
Option Explicit
Dim Arr() '数据
Dim Brr() '结果
Dim NotRecord As Integer '非记录(指平均行或空白行)
Dim Record As Integer '记录
Const LineCount = 20 '行数 = 多行记录 + 1行平均值
'入口
Sub rk()
Dim i As Integer, j As Integer
Call init
Arr = Range("a1").CurrentRegion.Value
ReDim Brr(1 To UBound(Arr) * LineCount, 1 To UBound(Arr, 2))
NotRecord = 0
Record = 0
For i = 2 To UBound(Arr)
'如果本行和上行不同,则处理平均行
If i > 2 And Arr(i, 4) <> Arr(i - 1, 4) Then Call AverageLine(i)
'某个产品的累加记录
Record = Record + 1
If Record < LineCount Then
For j = 1 To UBound(Arr, 2)
'结果的行号 = 累计的记录数 + 累计的非记录行数
Brr(i + NotRecord, j) = Arr(i, j)
Next j
End If
Next i
'最后一个产品的平均行,单独处理
Call AverageLine(i)
Sheets(2).Activate '结果在第2个工作表
Cells.Clear
Range("F:R").NumberFormat = "0.0"
Range("Z:AB").NumberFormat = "0.00"
Range("a1").Resize(i + NotRecord, UBound(Brr, 2)) = Brr '结果
Range("a1").Resize(1, UBound(Arr, 2)) = Application.Index(Arr, 1, 0) '标题
Range("a1").Select
Call FillColor(LineCount, UBound(Arr, 2)) '可选
End Sub
'初始化
Private Sub init()
Dim j As Integer
Sheets(1).Select
'1)前5列调整为“统计号,2016年排号,2016年小区号,2016年品种名称,2016年单株号”
If [b1] = "2016年品种名称" Then
j = Rows(1).Find("品种名称").Column '查找品种名称的列号
Columns(j).Cut '剪切"2016年品种名称"
Columns("E:E").Insert Shift:=xlToRight '插入到D列
'排号
j = Rows(1).Find("排号").Column '查找排号的列号
Columns(j).Cut '剪切"2016年排号"
Columns("C:C").Insert Shift:=xlToRight '插入到B列
End If
'要在处理前5列后,再排序
Range("a1").CurrentRegion.Sort key1:=[d1], order1:=xlAscending, Header:=xlYes 'D列 2016年品种名称 升序
'2)P列有错误的,就删除整行
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, 16).Delete '清除 错误常量
On Error GoTo 0
End Sub
'处理平均行
Sub AverageLine(iData)
Dim dic As Object '字典,用于文本型产品的计数
Dim ProductAvg As Long '产品的平均行行号
Dim ProductSum As Double '数字型产品的和
Dim ProductCount As Integer '产品的累计次数
Dim ProductColumn As Integer '产品的参照列号
Dim Crr() '为求标准差,准备存放数据的数组
Dim i As Integer, j As Integer, startRow As Integer, endRow As Integer, temp
Set dic = CreateObject("scripting.dictionary")
NotRecord = NotRecord + (LineCount - Record) 'LineCount - Record 表示累加非记录数,即平均行和空白行
ProductAvg = iData + NotRecord - 1 '某产品的平均行行号 = 累计记录数 + 累计非记录数 - 1
startRow = ProductAvg - LineCount + 1
endRow = ProductAvg - 1
'从第6列(株高cm),到最后1列(经济系数),求各产品的平均值
For j = 6 To UBound(Arr, 2)
'1)清零
ProductSum = 0: ProductCount = 0: ProductColumn = 0: dic.RemoveAll
'2)求和
ReDim Crr(startRow To endRow)
For i = startRow To endRow
'如果产品的值非空
If Brr(i, j) <> "" Then
'如果产品的值是数字
If IsNumeric(Brr(i, j)) Then
'累计产品值的和
ProductSum = ProductSum + Brr(i, j)
Crr(i) = Brr(i, j)
End If
'更新终止排号
Brr(ProductAvg, 2) = Brr(i, 2)
End If
Next i
'3)求次数
'更新产品的参照列号
Select Case j
Case 10, 14
ProductColumn = 16
Case Else
ProductColumn = j
End Select
'求和的列与求次数的列,因为可能不一样,所以必须再循环
For i = startRow To endRow
'如果产品的值非空
If Brr(i, ProductColumn) <> "" Then
'如果产品的值是数字
If IsNumeric(Brr(i, ProductColumn)) Then
'数字型产品的累计次数
ProductCount = ProductCount + 1
Else
'文本型产品的累计次数
dic(Brr(i, ProductColumn)) = dic(Brr(i, ProductColumn)) + 1
End If
End If
Next i
'4)求平均值
'4-1 数字型 = 和 / 次数
If ProductCount Then
Brr(ProductAvg, j) = ProductSum / ProductCount
'>>>
'O列 和 Q列,是求标准差
If j = 15 Or j = 17 Then
If ProductCount <> 1 Then
temp = 0
For i = LBound(Crr) To UBound(Crr)
temp = temp + (Crr(i) - Brr(ProductAvg, j)) ^ 2
Next i
Brr(ProductAvg, j) = VBA.Sqr(temp / (ProductCount - 1))
Else
Brr(ProductAvg, j) = "分母不能是0"
End If
End If
'<<<
End If
'4-2 '文本型 = 最多次数的条目
If dic.Count Then Brr(ProductAvg, j) = getItem(dic.keys, dic.items)
Next j
'处理前5列
Brr(ProductAvg, 1) = "" 'A列,统计号
Brr(ProductAvg, 2) = " " & Brr(startRow, 2) & "-" & Brr(ProductAvg, 2) 'B列,排号
Brr(ProductAvg, 3) = "平均" 'C列,材料序号2014年
Brr(ProductAvg, 4) = Brr(startRow, 4) 'D列,材料名称
Brr(ProductAvg, 5) = "平均" 'E列,株号
Record = 0
End Sub
'自定义函数:最多次数的条目
Function getItem(k, t) As String
Dim i As Integer
Dim temp As Integer '最大次数
Dim Id As Integer '最大次数的下标
For i = 0 To UBound(k)
'k是条目的数组,t是条目次数的数组
If temp < t(i) Then
temp = t(i) '最大次数
Id = i '最大次数的下标
End If
Next i
getItem = k(Id) '最大次数下标对应的条目
End Function
'将结果表里每种材料名称的平均行填充颜色
Sub FillColor(LineCount, c)
Dim r As Integer
Dim i As Integer
Sheets(2).Activate
r = Range("b65536").End(xlUp).Row
For i = r To 2 Step -LineCount
Range(Cells(i, 1), Cells(i, c)).Interior.ColorIndex = 24
Next i
End Sub
鲜籽考种平均-测试7.rar
(141.08 KB, 下载次数: 2)
|
|