我有一个电子表格，其中包含数十万行，分布在多个工作表中，每个工作表都使用多列 VLOOKUP，此外还有一些列公式，例如 COUNTIFS 和 SUMIFS，这些公式在计算时会查看工作表上的每一行。每次计算时，我的 CPU 都会激增，有时计算时间太长，我可能需要泡一杯咖啡才能完成。
我已经通过使用 MATCH("*",[column],-1) 查找最后一行并动态生成范围（而不是仅引用整个列），以及通过将 VLOOKUP 替换为 INDEX MATCHes 来使情况变得不那么糟糕了。只剩下两个公式我不知道如何改进。
以下是我正在处理的数据的近似值：
+--------+-------+----------+--------+
| Group | Item | Quantity | Round |
+--------+-------+----------+--------+
| Group1 | Item1 | 2 | 1 |
| Group1 | Item2 | 2 | 2 |
| Group2 | Item1 | 2 | 1 |
| Group2 | Item2 | 3 | 2 |
| Group2 | Item3 | 2 | unused |
+--------+-------+----------+--------+
对于第一个问题，我想查看组中的所有项目是否具有相同的数量。旧版本使用以下公式：
=COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,C:C,C2)
我可以不使用 COUNTIFS 完成同样的事情吗？使用数据透视表是否会比使用原始数据本身提高性能？或者我能做的最好的事情是用 MATCH 计算的动态范围替换整个列引用？
对于第二个问题，我试图找到该组的最小“舍入”值。旧公式：
=MINIFS(D:D,A:A,A2)
它曾经是一个数组公式 -
={MIN(IF(A:A,A2),IF(P:P>0,P:P))}
- 所以当 MINIFS 添加到 Excel 时我非常兴奋。但是有没有比 MINIFS 更好的东西可以用来得到这个答案呢？
答案1
您完全可以使用数据透视表来实现这一点。根据我的经验，数据透视表（一旦创建并缓存）比 sumif/countif 公式快得多，但这取决于数据集的大小。
按照如下方式创建数据透视表：
- 选择您的数据，单击“数据透视表”按钮，单击“确定”
- 将“组”拖到行框中
- 将“四舍五入”拖到列框中，单击它，单击“值字段设置”，选择“最小值”，然后单击“确定” - 这是每个组的最小值
- 将“数量”拖到列框中，单击它，单击“值字段设置”，选择“最小值”，然后单击确定
- 将“数量”拖到列框中，单击它，单击“值字段设置”，选择“最大”，然后单击确定
如果“最小数量”和“最大数量”相等，则说明该组中的所有商品数量相同。您可以在数据透视表旁边创建一个公式来评估这一点。
