新浪博客

角度整理

2022-05-27 15:02阅读：

http://blog.sina.cn/dpool/blog/u/1221912365

Sub 提取整理()
Dim i, n, j, m, x, y, k, h, f, sum, a, c, d, p
'填写序号，以便将逆时针测的角度按照顺时针的编号顺序走，好提取数据
k = 1
For h = 1 To Range('a65536').End(xlUp).Row
Cells(h, 'f') = k
k = k + 1
Next h
c = 5 '目标数目,每次改动此数据
d = 4 '测回数目,每次改动此数据

'根据序号列，将特定区间的数值倒序排列，以便将逆时针测得的角度按照顺时针排列，方便后续提取
For f = c + 1 To Range('a65536').End(xlUp).Row Step 2 * c
Range('A' & f & ':f' & (f + c - 1)).Select
ActiveWorkbook.Worksheets('Sheet1').Sort.SortFields.Clear
ActiveWorkbook.Worksheets('Sheet1').Sort.SortFields.Add Key:=Range('f' & f), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets('Sheet1').Sort
.SetRange Range('A' & f & ':f'
& (f + c - 1))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next f
'把增加的辅助序号列删除
Columns('F:F').EntireColumn.Select
Selection.Delete Shift:=xlToLeft
'每隔几行写数
For i = 0 To 17 '18*5行,此18行为任意数,根据观测数目和测回数改
For n = 1 To c
If Int(i / 2) * 2 = i Then
Sheet1.Cells(i * c + n, 6) = '盘左'
Else
Sheet1.Cells(i * c + n, 6) = '盘右'
End If
Next n
Next i
x = 1
For j = 1 To Range('a65536').End(xlUp).Row
If Cells(j, 'F') = '盘左' Then
Cells(x, 'G') = Cells(j, 'C')
Cells(x, 'H') = Cells(j, 'D')
Cells(x, 'I') = Cells(j, 'E')
x = x + 1
End If
Next j
y = 1
For m = 1 To Range('a65536').End(xlUp).Row
If Cells(m, 'f') = '盘右' Then
Cells(y, 'j') = Cells(m, 'c')
Cells(y, 'k') = Cells(m, 'd')
Cells(y, 'l') = Cells(m, 'e')
y = y + 1
End If
Next m
For p = 1 To c - 2
sum = 0
For a = p + 1 To Range('a65536').End(xlUp).Row Step c
sum = sum + Cells(a, 'b')
Next a
Cells(p, 'm') = sum / (2 * d)

Next p
End Sub

我的更多文章

下载客户端阅读体验更佳

APP专享