Excel公式优化

Question

我不是一个优秀的专家，经过一些研究后，我想出了这个公式来查看来自不同时间的两组相同的数据。然后显示最新数据列表中的新条目，但不显示旧列表中的新条目。

这是我的公式：

  {=IF(ROWS(L$4:L8)<=(SUMPRODUCT(--ISNA(MATCH($E$1:$E$2500,List1!$E$1:$E$2500,0)))),
    INDEX(E$1:E$2500,
    SMALL(IF(ISNA(MATCH($E$1:$E$2500&$F$1:$F$2500,List1!$E$1:$E$2500&List1!$F$1:$F$2500,0)),
    ROW($F$1:$F$2500)-ROW($F$1)+1),ROWS(L$4:L8))),"")}

我是否可以采用任何优化技术来加速计算？

应要求提供一些示例数据(链接到电子表格)：https://docs.google.com/file/d/0B186C84TADzrMlpmelJoRHN2TVU/edit?usp=sharing

在这个缩小的版本上，它更有效，但是在我的实际表格上，有了更多的数据，它就变慢了。

Answer 1

一个相对快速的解决方案是在列表2旁边的列中添加一个多单元数组公式。

{=MATCH($A$1:$A$16,List1!$A$1:$A$11,0)}

并对#N/A的输出进行过滤。

(或者我的商业解决方案见Compare.Lists对VLOOKUP )

Answer 2

数组公式很慢。当你有成千上万的数组公式，它将使速度非常慢。因此，关键是避免任何数组公式。

以下是我实现它的方法，只使用简单的公式。如果你只有2500行，它应该足够快。

• 列F和H是“键”，通过连接2列(原公式中的E和F)创建。
• 假设第一行数据位于第3行。

数据：

|   A   |      B      |    |  D |       E       |     F     |      |     H     |
| index | final value |    | ID | exist in Old? | Key (New) |      | Key (Old) |
--------------------------------------------------------------------------------
|   1   |    XXX-33   |    |  0 |      3        | OOD-06    |      | OOC-01    |
|   2   |    ZZZ-66   |    |  0 |      1        | OOC-01    |      | OOC-02    |
|   3   |    ZZZ-77   |    |  1 |     N/A       | XXX-33    |      | OOD-06    |
|   4   |             |    |  1 |      4        | OOE-01    |      | OOE-01    |
|   5   |             |    |  1 |      2        | OOC-02    |      | OOF-03    |
|   6   |             |    |  2 |     N/A       | ZZZ-66    |      |           |
|   7   |             |    |  3 |     N/A       | ZZZ-77    |      |           |

列E“存在于旧？”：测试旧列表(H列)中是否存在新键(F列)

=MATCH(F3, $H$3:$H$2500, 0)

列D "ID"：每当找到一个新项时，都要增加一个

=IF(ISNA(E3), 1, 0)+IF(ISNUMBER(D2), D2, 0)  

ISNUMBER的第二部分仅用于第一行，仅使用D2就会导致错误。

列A "index"：从1开始(直到新列表列F的长度)开始的普通序列

列B“最终值”：通过将列A与列D匹配来查找新密钥

=IF(A3>MAX($D$3:$D$2500), "", INDEX($F$3:$F$2500, MATCH(A3, $D$3:$D$2500, 0))

这一列B将是您想要的列表。

如果它仍然太慢，就会有一些小技巧来加快计算速度，例如，使用带有MATCH( , , 1)的排序列表而不是MATCH( , , 0)。