因此,我对使用excel很陌生,很可能错过了一个已经回答过的问题,但是我想做的是测量大型excel工作表中各组点之间的距离。
我已经尝试了编写函数并将其向下拖动的标准方法,但是当我向下移动函数时更改该函数的方式不是我所需要的,而且我似乎无法弄清楚如何解决此问题,因此我认为可能是解决方案。
数据是8个测量值的组,每个组具有一个蓝色,红色,黄色,Csome,重叠,绿色,青色和完整对象。每个对象的XYZ坐标在前三列中列出,我试图使用一个简单的距离公式来测量每个对象与同一组中所有其他对象之间的距离。例如。蓝色到红色,黄色,Csome ...等。然后再次将Blue2到Red2,Yellow2,Csome2 ...和Blue3到Red3,Yellow3,Csome3 ...等。
但是,当我将以下公式放在所需的输出位置并尝试将其向下拖动时,显然会遇到一些问题。对于第一个与它成对的Blue,我以= SQRT((A $ 2-A2)^ 2 +(B $ 2-B2)^ 2 +(B $ 2-B2)^ 2)开头并将其向下拖动,以便循环第2行与第3、4、5、6、7、8和9行。当我到达包含Blue2的第10行时,就会出现问题。这不应与Blue1进行比较,而应开始一个新的小组。然后如何获得从第10行重新开始的循环,以便在该组中比较以Blue 2开头的新组,例如,第10行中的XYZ与11、12、13 ...中的XYZ:
Position X Position Y Position Z Unit Category Collection Surpass Object
16.38 41.20 4436.18 um Surface Position Blue Selection
17.35 40.83 4436.17 um Surface Position Red Selection
15.93 40.62 4435.93 um Surface Position Yellow Selection
16.22 40.80 4436.03 um Surface Position Csome1
17.97 42.23 4435.46 um Surface Position Overlap
15.37 40.87 4436.03 um Surface Position Green Selection
15.44 40.04 4436.39 um Surface Position Cyan Selection
17.97 42.23 4435.46 um Surface Position Full 1
15.57 36.20 4435.75 um Surface Position Blue Selection 2
12.83 35.19 4435.33 um Surface Position Red Selection 2
15.76 37.25 4435.74 um Surface Position Yellow Selection 2
14.94 36.17 4435.65 um Surface Position Csome2
15.99 36.78 4436.34 um Surface Position Overlap 2
14.23 36.26 4435.57 um Surface Position Green Selection 2
13.61 36.96 4436.09 um Surface Position Cyan Selection 2
14.64 36.42 4435.80 um Surface Position Full 2
42.83 36.04 4435.47 um Surface Position Blue Selection 3
42.34 36.56 4435.63 um Surface Position Red Selection 3
42.25 36.87 4435.75 um Surface Position Yellow Selection 3
42.48 36.50 4435.62 um Surface Position Csome3
41.77 36.02 4435.67 um Surface Position Overlap 3
42.64 36.89 4435.73 um Surface Position Green 3
42.05 36.90 4436.21 um Surface Position Cyan Selection 3
42.34 36.53 4435.71 um Surface Position Full 3
33.75 33.13 4436.32 um Surface Position Blue Selection 4
34.99 33.12 4436.38 um Surface Position Red Selection 4
35.40 31.97 4436.38 um Surface Position Yellow Selection 4
34.75 32.58 4436.36 um Surface Position Csome4
39.03 35.24 4436.04 um Surface Position Overlap Selection 4
35.49 32.19 4436.49 um Surface Position Green Selection 4
36.14 32.35 4436.42 um Surface Position Cyan Selection 4
36.16 33.20 4436.30 um Surface Position Full 4
应用一些VBA ,以通配符在完全匹配中循环进行匹配,并为每次迭代重置公式的起始行,应该是最好的方法。
Sub Group_Formula()
Dim frmla As String, rw As Long, rws As Long, n As Long, cnt As Long
frmla = "=SQRT((A$×-A×)^2+(B$×-B×)^2+(B$×-B×)^2)"
With ActiveSheet '<-set to worksheet name properly!
For cnt = 1 To Application.CountIf(.Columns("G"), "Full*")
rw = Application.Max(2, rw)
rws = Application.Match("Full*", .Cells(rw, "G").Resize(Rows.Count - rw, 1), 0)
.Cells(rw, "H").Resize(rws, 1).Formula = Replace(frmla, Chr(215), rw)
rw = rw + rws
Next cnt
End With
End Sub
结果应类似于以下内容。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句