我在 Excel 文档中有 2 个工作表。我想在一个工作表中计算某个值是否位于第二个工作表的列中。第二个工作表的列是禁止呼叫列表中的电话号码列表。目标是找出第一个工作表中的哪些电话号码位于禁止呼叫列表中。
我的公式使用以下MATCH()函数：
=MATCH(A2, 'DNC Worksheet'!$C$2:$C$100000, 0)
我遇到的问题是公式查看的数据列非常大（约 100,000 行）。我计算了这个公式大约 25,000 次。结果是 Excel 打开、保存和滚动文件的速度非常慢。
有没有更高效的方式来进行这种搜索？也许有一种方法可以将“请勿致电”号码缓存在数据结构中？
答案1
有一种方法可以显著加快速度（因子 3,000，见下文说明）：如果对工作表 C 列中的数据进行排序DNC Worksheet，则可以在末尾MATCH不带 的情况下运行 ，即。 （旁注：Excel 2007 及更高版本在使用整列时非常智能，因此无需在此处指定 100000！）。0=MATCH(A2,'DNC Worksheet'!$C:$C)
这种方法的初始缺点是，即使 A2 不包含在 C 列中，您也会得到匹配。但是，可以使用以下公式来处理这个问题：
=IF(INDEX('DNC 工作表'!$C:$C,MATCH(A2,'DNC 工作表'!$C:$C))=A2,MATCH(A2,'DNC 工作表'!$C:$C),"无匹配")
当然，你可以将计算时间减半，通过在一个单元格（例如 B2）中进行匹配，然后在下一个单元格中使用它=IF(INDEX('DNC Worksheet'!$C:$C,B2)=B2,B2,"no match")。
背景信息：
如果您在末尾提供MATCH（或VLOOKUP）参数，Excel 将执行精确搜索，即从 C2 中的第一个单元格开始，检查是否匹配。如果不匹配，则继续下一个单元格，直到找到值 - 或产生错误。因此，如果您有 100,000 个数据集，则平均需要进行 50,000 次比较才能找到值（=n/2）- 因此，在您的情况下，总计算次数为 1.25B！0/FALSEN/A!
如果FALSE省略该参数，Excel 会假定该范围已排序并应用二进制搜索算法：它从范围的中间开始，即单元格 50,000，并检查其值是更大还是更小。假设它更大，则您尝试匹配的值必须介于单元格 1 和 50,000 之间。现在它再次检查此范围的中间，等等。因此，它将进行 log2(n) 比较，在您的情况下为 ~17。因此，总计算次数仅为 425K，即计算速度将提高 3,000 倍！:-)
如需进一步阅读/性能调整，我推荐此文网站！