Excel公式优化 [英] Excel Formula Optimisation

问题描述

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

这是我的公式：

  {= IF（ROWS （$ E $ 1：$ E $ 2500，List1！$ E $ 1：$ E $ 2500,0）））
索引（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

在这个缩小的版本上，它的效率更高，但在我的实际工作表上有更多的数据，它变慢了。

解决方案

嗯，我在玩耍一点，我认为这是一样的，没有第一个IF语句：

  = IFERROR（INDEX（A $ 1：A $ 2500，SMALL（IF（ISNA（MATCH（$ A $ 1：$ A $ 2500安培; $ B $ 1：$ B $ 2500，列表1 $ A $ 1：$ A $ 2500安培;列表1！$ B $ 1：$ B $ 2500,0）），ROW（$ B $ 1：$ B $ 2500）-ROW（$ B $ 1）+1），ROWS（F $ 2：F2））），）
  

您的示例数据中的那部分：

  ROWS（F $ 2：F2）< =（！SUMPRODUCT（ -  ISNA（MATCH（$ A $ 1：$ A $ 2500，列表1 $ A $ 1：$ A $ 2500,0）））） 
  

据了解，它只能看到输入公式的e行号低​​于新项目的数量，但它不提供任何用途，因为当您拖动公式超过所需的数量时，仍然会收到错误而不是预期的空白。所以我以为它可以完全删除（试图用 COUNTA（）代替它），并使用一个 IFERROR（）部分直接提取细节。

编辑：抓住了。看到barry houdini对这些部分的重要性的评论。

接下来，你有这样的：

  ROW（$ B $ 1：$ B $ 2500）-ROW（$ B $ 1）+1 
  

-ROW（$ B $ 1）+1 总是返回 0 ，所以我没有找到任何使用它，并完全删除它。

它还很长，需要一些时间我猜，但我相信应该是比以前更高一点：）

I am no excel expert and after some research have come up with this formula to look at two sets of the same data from different times. It then displays new entries that are in the latest list of data but not in the old list.

This is my formula:

  {=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))),"")}

Are there any optimisation techniques I could employ to speed up the calculation?

As requested Some example data(link to a spreadsheet): https://docs.google.com/file/d/0B186C84TADzrMlpmelJoRHN2TVU/edit?usp=sharing

On this scaled down version its more efficent but on my actual sheet with a lot more data it is slowed.

解决方案

Well, I was playing around a bit and I think that this works the same, and without the first IF statement:

=IFERROR(INDEX(A$1:A$2500,SMALL(IF(ISNA(MATCH($A$1:$A$2500&$B$1:$B$2500,List1!$A$1:$A$2500&List1!$B$1:$B$2500,0)),ROW($B$1:$B$2500)-ROW($B$1)+1),ROWS(F$2:F2))),"")

That part in your sample data:

ROWS(F$2:F2)<=(SUMPRODUCT(--ISNA(MATCH($A$1:$A$2500,List1!$A$1:$A$2500,0))))

As I understood it, it only sees to it that the row number in which the formula is entered is lower than the number of 'new' items, but it doesn't serve any purpose because when you drag the formula more than required, you still get errors instead of the expected blank. So I thought it could be removed altogether (after trying to substitute it with COUNTA() instead) and use an IFERROR() on the part directly fetching the details.

EDIT: Scratched that out. See barry houdini's comment for the importance of those parts.

Next, you had this:

ROW($B$1:$B$2500)-ROW($B$1)+1

-ROW($B$1)+1 always returns 0, so I didn't find any use to it and removed it altogether.

It's still quite long and takes some time I guess, but I believe it should be faster than previously by a notch :)

这篇关于Excel公式优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！