excel - Excel VBA 如何比较两个大型数据集中的值与质量缺陷/错误？

问题描述

我在实习期间活跃于分析化学领域，并希望比较大型数据集（两列，最多 15,000 行）。这样做的主要思想是，我有两列包含质量数据（带有 4 位小数），其中宏应该在第二列的第一列中查找每个质量，但存在质量缺陷/错误。这意味着该值不完全对应（由于测量中的仪器误差），但应落在下限和上限内。当宏循环通过 dataset2 检查每个值时，dataset1 的检查单元格将偏移到下一个 (.Offset(1,0)) 以重复搜索该特定值。上限和下限自动调整。

举个例子（使用的代码格式，否则表格将无法正确显示）：

Value to check from dataset1 is 101.1048, mass error is 5 ppm (parts-per-million, 0.000005%),  
so the lower limit is 101.1043 and the upper limit is 101.1053. So in the example shown below,  
the mass in dataset2 falls within the boundaries, after which the macro should sum the intensity
(linked to the mass column) of all mass values from dataset2 that fall within the dataset1 limits
for the checked cell. So SumIntensity=105+209 in the example, if no corresponding value is found,
the intensity of the dataset1 is used (so 100).

**Dataset1**             "   **Dataset2**   
Mass      ' Intensity    "   Mass      ' Intensity 
''''''''''''''''''''''''''''''''''''''''''''''''''''
101.1048  '    100       "   101.1045  ' 105
101.1272  '    300       "   101.1051  ' 209

然而，我对 VBA 的经验并不丰富（我只编写了一些基本的宏来比较同一数据集中的值与质量缺陷/错误），经过无数次尝试，我还没有能够让宏工作。我当前的代码如下，但不断崩溃（很可能是由于循环）：

Sub CompareColumnsTest2()
Dim wscalc, wsdata, wscontrol As Worksheet
 Set wscalc = Sheet2
 Set wsdata = Sheet1
 Set wscontrol = Sheet4

''-----------------------------------------------------------
''Compares datasets 1 and 2 in two steps:
''Looks up each Rounded Mass from dataset1 in dataset2 and substracting the relative intensity respectively
''Looks up each Rounded Mass from dataset 2 in dataset1 and if NOT present in dataset 1, copies Rounded Mass and (negative) Intensity
wscalc.Range("B3:B" & wscalc.Range("B" & Rows.Count).End(xlUp).Row).Copy
wscalc.Range("K3").PasteSpecial (xlPasteValues)

''Step one
 Dim refcl, refint, massdefect, lowerlimit, upperlimit As Range
 Set refcl = wscalc.Range("B3")
 Set refint = wscalc.Range("D3")
 Set pastecell = wscalc.Range("L3")
 Set massdefect = wscontrol.Range("D4")
 Set lowerlimit = wscalc.Range("Z2")
 Set upperlimit = wscalc.Range("Z4")
 Set checkcl = wscalc.Range("G3")
 Set checkint = wscalc.Range("I3")

Dim refclnext, refintnext, checkclnext, pastecellnext As Range, sumint As Long
Do While Not IsEmpty(refcl)
Set refclnext = refcl.Offset(1, 0)
Set refintnext = refint.Offset(1, 0)
Set pastecellnext = pastecell.Offset(1, 0)
Set checkclnext = checkcl.Offset(1, 0)
Set checkintnext = checkint.Offset(1, 0)

sumint = 0
lowerlimit.Value = refcl / (1 + (massdefect / 1000000))
upperlimit.Value = refcl * (1 + (massdefect / 1000000))

 Do While Not IsEmpty(checkcl)
 If checkcl <= upperlimit And checkcl >= lowerlimit Then
 sumint = sumint + checkint
 End If
 Set checkcl = checkclnext
 Set checkint = checkintnext
 Loop

Set pastecell.Value = refint - sumint
Set refcl = refclnext
Set refint = refintnext
Set pastecell = pastecellnext
Loop



End Sub

我希望我的描述足够清楚，能够帮助我。我不要求您完全重写我的代码，因为这当然会花费很多时间，但是任何提示/修改都将受到高度赞赏。

最好的，詹姆斯看起来

编辑 1：以下是一些屏幕截图，显示了一些数据以及工作表是如何组织单元格引用的。

数据和工作表布局概述，

用于上限和下限的单元格

标签： excelvbaloopscomparisonlarge-data

excel - Excel VBA 如何比较两个大型数据集中的值与质量缺陷/错误？

问题描述

解决方案

推荐阅读