我在解决这个问题上遇到了麻烦。我有一个Excel工作表。sheet1的A列包含大约300,000个令牌,而sheet2的A列包含大约15,000个令牌,可能的词性标签分布在每个令牌的同一行上。格式示例:
A in sheet1 (A B C D E) in sheet2
now may NN MD
earth but CC CJS
between can MD NN
been think VB VBP VBZ NN
think now NN JJ RB IN
may between IN CC CJS
red ...
between
now
think
may
now
...
我的任务是将A(表格2)中的每个单词放在A(表格1)中进行搜索。如果找到,则在其行上复制标签,并将标签粘贴到与A(sheet1)中所有出现的单词相邻的单元格中。就是说,如果认为之间存在等,那么我将在sheet1中得到以下输出:
A B C D E
now
earth
between IN CC CJS
been
think VB VBP VBZ NN
may
red
between IN CC CJS
now
think VB VBP VBZ NN
may
now
...
我有这段代码,仅执行A(sheet1)和A(sheet2)中非空单元格的搜索,检查A(sheet2)中的每个单词是否在A(sheet1)中。如果为true,则将复制单词(而不是关联的标签)并将其粘贴到与A(sheet1)中的结果相邻的C列的单元格中。结果看起来像下面而不是上面。
A B C D E
now
earth
between between
been
think think
may
red
between between
now
think think
may
now
...
这是代码:
Sub CopyDataToPlan()
Dim LData As String
Dim LData2 As String
Dim iVal As Integer
Dim iVal2 As Integer
iVal = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
iVal2 = Worksheets("Sheet2").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
For LData_counter = 1 To iVal
LData = Sheets("Sheet1").Cells(LData_counter, 1).Value
For LData2_counter = 1 To iVal
LData2 = Sheets("Sheet2").Cells(LData2_counter, 1).Value
If (LData2 = LData) Then
Sheets("Sheet1").Cells(LData_counter, 3) = LData2
'Selection.Copy
End If
Next LData2_counter
Next LData_counter
End Sub
另外,当我尝试在50,000个单词以上使用此代码时,该代码要么运行并中途挂起,要么导致溢出。我的目标是:我准备了A(sheet1&2)中的数据,以便将我开发的POS标签标记为A(sheet1)中的单词。A(sheet2)中的单词是唯一的单词,带有最可能的标记。我为此任务雇用的许多用户对MS Office更为熟悉。因此,我决定为他们简化工作。该系统将向他们提供一个单词以及该单词的所有可能标签,而不是它们遍历大约100个标签并在A(sheet1)中查找一个单词的标签。他们现在要做的就是在A(sheet1)中说一个字,浏览提供的标签,然后选择一个用于它的标签。拜托!我需要帮助。谢谢你们!
您也可以使用工作表公式来执行此操作。例如
Sheet1!B1: =IF(LEN(IFERROR(VLOOKUP($A1,Sheet2!$A$1:$E$15000,COLUMNS($A:B),FALSE),""))=0,"",VLOOKUP($A1,Sheet2!$A$1:$E$15000,COLUMNS($A:B),FALSE))
填写E1的权利;然后选择B1:E1并向下填充至300000行
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句