我创建了一个 Excel 2007 电子表格，其中包含一个包含源数据的较大表格（约 500,000 行和 10 列）。我需要从这个大表中提取数据进行分析。为了提取和汇总数据，我通常使用 sumif、vlookup/hlookup 和 index+match 函数。
我最近了解到 getpivotdata 函数的存在，该函数可以从数据透视表中提取数据。为了能够使用它，我首先需要将我的大型源表转换为数据透视表，然后我可以使用 getpivotdata 函数提取数据。
如果我使用 getpivotdata 来提取和聚合数据，您是否期望性能得到改善？我希望在底层 Pivot 对象中预先计算聚合值，因此性能会更好。
如果性能会更好，有什么理由不采用这种方法呢？要明确的是，没有必要刷新数据透视表，因为它包含源数据（位于计算链的开头）。
答案1
我对装有 Excel 2007 的双核 2.33 GHz 2 GB RAM 台式电脑进行了一些性能测试。
查找是在包含 241,000 条记录的表上进行的。结果如下（最快的排在最前面，最慢的排在最后）：
随着索引匹配函数排序列表每秒的查找次数为：18万!!（基于 8 秒内 1,440,000 次查找）。有关如何在 Excel 中实现排序查找的更多信息，请参见这里并向下滚动到部分INDEX-MATCH 在一个公式中，排序数据
随着获取枢轴数据函数每秒的查找次数为：6,000（基于 40 秒内 250,000 次查询）
随着获取枢轴数据功能使用非常灵活单参数字符串语法（看这里）每秒的查找次数为：2,000（基于 145 秒内的 250,000 次查找）
随着索引匹配函数未分类列表每秒的查找次数为：500（基于 35 秒内的 20,000 次查找）
当查找函数引用数据表而不是命名范围。
那么，回答这个问题吧。通过 getpivotdata 进行查找的速度大约是常规索引匹配查找的 10 倍，但通过对源数据进行排序可以实现最佳性能改进。对源数据进行排序可以使您的查找速度提高 400 倍。
答案2
使用 VBA（使用字典）进行查找是迄今为止最快的方法。请参见：https://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup
答案3
使用 GetPivotData 只能让您访问数据透视表中可见的内容。如果您是此电子表格的唯一用户，那么这可能是一种可行的方法。
如果您可以设计 Pivot 来为您完成大部分聚合，那么使用 GetPivotData 将会更快。
我还没有测试过 GetPivotData 的性能，但我预计它会比对排序数据进行二分搜索查找/匹配慢。
答案4
我每天都会遇到同样的问题。Excel 中的多个数据表中有大量行。
目前，使超大表可用的唯一解决方案是将它们导出到数据库服务器并执行/写入SQL 查询进行 Sumif、Vlookups 和聚合
您可以使用 Excel 创建SQL 查询
多年来，我已将工作表/表格导出至“MySQL“ 和 ”MS SQL 服务器快递“然后使用 excel 连接它们并写入SQL 查询
服务器处理速度比 Excel 更快和如果数据库位于不同的服务器上，则性能会提高，因为它不使用您电脑的资源来进行计算。
该解决方案还有其他好处。
喜欢ETL 自动化并共享连接字符串而不是“BIG”电子表格。