尝试提高数组公式的效率
我有一个 SUM 数组公式，其中有多个嵌套 IF 语句，使其效率非常低。 我的公式跨越 500 行，但这里是它的一个简单版本：
{=SUM(IF(IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17>0,
IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17,0))}
如您所见，公式的前半部分检查数组中的哪些位置大于零，如果大于零，则将数组第二部分中的值相加。公式。
您会注意到相同的 IF 语句在那里重复两次，这对我来说效率很低，但这是我获得正确答案的唯一方法。
我的示例数据如下：
电子表格中的示例数据 http://clients .estatemaster.net/SecureClientSite/Download/TempFiles/example.jpg 在本例中，使用我上面提到的公式，答案应该是 350。
如果我尝试在数组中放入 MAX 语句，因此删除测试以查找它大于零的位置，所以它是这样的：
{=SUM(MAX(IF(B2:B6>B8:B12,B2:B6,B8:B12)-B14:B18,0))}
但是，它似乎只计算每个范围中的第一行数据，并且它给了我错误的答案 70。
有谁知道我可以减少公式的大小或通过不需要在其中重复 IF 语句来提高公式的效率吗？
更新
吉米
您建议的 MAX 公式实际上并不适用于所有情况。
如果我将第 1 行至第 5 行中的示例数据更改如下（显示某些数字大于第 7 行至第 11 行中各自的单元格，而某些数字则较低）
电子表格中的示例数据 http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example2.jpg
我试图达到的正确答案是 310，但是您建议 MAX 公式给出的错误答案是 275。
我猜测该公式需要是一个数组函数才能给出正确答案。
还有其他建议吗？
I have a SUM array formula that has multiple nested IF statements, making it very inefficient. My formula spans over 500 rows, but here is a simple version of it:
{=SUM(IF(IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17>0,
IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17,0))}
As you can see, the first half of the formula checks where the array is greater than zero, and if they are, it sums those in the second part of the formula.
You will notice that the same IF statement is repeated in there twice, which to me is inefficient, but is the only way I could get the correct answer.
The example data I have is as follows:
Sample Data in spreadsheet http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example.jpg
The answer should be 350 in this instance using the formula I mentioned above.
If I tried to put in a MAX statement within the array, therefore removing the test to find where it was greater than zero, so it was like this:
{=SUM(MAX(IF(B2:B6>B8:B12,B2:B6,B8:B12)-B14:B18,0))}
However, it seems like it only calculates the first row of data in each range, and it gave me the wrong answer of 70.
Does anyone know a away that I can reduce the size of the formula or make it more efficient by not needing to repeat an IF statement in there?
UPDATE
Jimmy
The MAX formula you suggested didnt actually work for all scenarios.
If i changed my sample data in rows 1 to 5 as below (showing that some of the numbers are greater than their respective cells in rows 7 to 11, while some of the numbers are lower)
The correct answer im trying to achive is 310, however you suggested MAX formula gives an incorrect answer of 275.
Im guessing the formula needs to be an array function to give the correct answer.
Any other suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
一种计算效率更高（尤其是重新计算效率更高）的方法是使用辅助列而不是数组公式：
C1:
=MAX(A1,A7)-A13D1:
=IF(C1>0,C1,0)将这两个内容复制到 5 行
E1:
=SUM(D1:D5)然后 Excel 将仅重新计算依赖的公式任何更改的值，而不必在每次任何单个数字更改时计算数组公式隐含的所有虚拟列。 即使您更改所有数字，它的计算量也会减少。
A more calculation-efficient (and especially re-calculation efficient) way is to use helper columns instead of array formulae:
C1:
=MAX(A1,A7)-A13D1:
=IF(C1>0,C1,0)copy both these down 5 rows
E1:
=SUM(D1:D5)Excel will then only recalculate the formulae dependent on any changed value, rather than having to calculate all the virtual columns implied by the array formula every time any single number changes. And its doing less calculations even if you change all the numbers.
您可能想查看 VB 宏编辑器。 在“工具”菜单中，转到“宏”并选择“Visual basic 编辑器”。 这提供了一个完整的编程环境，您可以在其中编写自己的函数。
VB 是一种简单的编程语言，Google 上有您需要的所有指南。
在那里，您可以编写一个像 MySum() 这样的函数，并让它以您自己编写的清晰方式执行您真正需要的任何数学运算。
我从谷歌上找到了这个，它看起来像是一个很好的指南来设置这一切。
http://office.microsoft.com/en-us/excel/HA011117011033。 ASPX
You may want to look into the VB Macro editor. In the Tools Menu, go to Macros and select Visual basic Editor. This gives a whole programming environment where you can write your own function.
VB is a simple programming language and google has all the guidebooks you need.
There, you can write a function like MySum() and have it do whatever math you really need it to, in a clear way written by yourself.
I pulled this off google, and it looks like a good guide to setting this all up.
http://office.microsoft.com/en-us/excel/HA011117011033.aspx
这似乎有效：
编辑
- 不处理减法结果为负的情况
这可行 - 但它更有效吗？？？
This seems to work:
EDIT
- doesn't handle cases where subtraction ends up negative
This works - but is it more efficient???
那这个呢？
编辑：
糟糕 - 错过了扔掉底片的部分。 那这个呢？ 不确定它是否更快...
编辑 2：
这对您来说效果如何？
What about this?
Edit:
Woops - Missed the throwing out negatives part. What about this? Not sure it it's faster...
Edit 2:
How does this perform for you?