优化多准则迭代函数系统 [英] Optimizing multiple-criteria IFs

问题描述

我正在执行需要很长时间才能完成的数组计算。我想再优化一下我的配方。所有公式都具有相同的性质-它们对一列值执行一些高级函数(Average、Slope、Min、Max)。但是，并非列中的所有单元格都包含在数组中。我使用多个If标准来选择包含哪些单元格。所有比较都与当前行进行。以下是数据的一个示例：

     A             B                C            D          E
1    Company       Generation       Date         Value      ToCalculate
2    Abc           1                1/1/2010     5.6          
3    ...           ...              ...          ...        ...

E应该是这样的

{=Average(If(A2=A2:A1000, If(B2=B2:B1000, If(C2 > C2:C1000, D2:D1000))))}

因此，一旦计算出E2，我就必须自动填写E列F、G、H、...使用相同的方法，要么选择不同的值进行运算，要么选择不同的函数执行。我的数据集非常大，只有几个数据集，电子表格需要一个多小时才能计算出来。我经常会添加第四个条件，所有其他条件都是相同的。

有效率吗？一些想法：

1. 我是否可以每列使用单个数组，而不是每列使用数千个数组？
2. 我是否可以压缩前三个条件，以便输出为行号？也许随后的公式将不必搜索多个条件，而只需执行该函数？
3. 还是以某种方式建立了一个标准？因此，新列将返回公司相同的所有行。另一列返回生成相同的第一列中的所有行...依此类推...

推荐答案

没有数组时的平均值：

 =AVERAGEIFS(D2:D$1000,A2:A$1000,A2,B2:B$1000,B2,C2:C$1000,"<="&C2)  

由于也有COUNTIF和SUMIF，我认为您的斜率可以用同样的方法计算。

对于其余的函数(max、min等)，我们应该逐个分析。

我做了一个轻微的性能测试，这显然更好，但当然，我的数据集只是被嘲笑了。

HTH！

注意：仅限Excel 2007及更高版本！

编辑-回复您的评论。

不知道问题的严重性很难给出建议，但无论如何我都会冒一个险：

您可以编写一个VBA函数，该函数：

1)为每个公司世代对生成新工作表
2)按日期对这些工作表中的数据进行排序
3)将公式添加到这些工作表中(在此上下文中不需要条件)
4)重新计算并从这些公式中获得结果并填充原始工作表
5)删除辅助单

这篇关于优化多准则迭代函数系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！