优化多标准 IF
我正在执行需要很长时间才能完成的数组计算。我想进一步优化我的公式。所有公式都具有相同的性质 - 它们对一列值执行一些高级函数（平均值、斜率、最小值、最大值）。但是，并非列中的所有单元格都包含在数组中。我使用多个 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 列...使用相同的方法，选择不同的值进行操作或执行不同的功能。我的数据集非常大，仅包含其中的几个，电子表格就需要一个多小时来计算。我经常会添加第四个标准，所有其他标准都相同。
有效率吗？一些想法：
- 我可以每列使用一个数组而不是每列数千个数组吗？
- 我可以压缩前三个标准以便输出为行号吗？也许后续公式就不必搜索多个条件而只需执行该功能？
- 或者以某种方式建立标准？因此，新列将返回公司相同的所有行。另一列返回第一列中生成相同的所有行...依此类推...
I'm performing array calculations that are taking a long time to complete. I'd like to optimize my formulas some more. All of the formulas are of the same nature - they perform some high-level function (Average, Slope, Min, Max) across a column of values. However, not all cells in a column are included in the array. I use multiple IF criteria to choose which cells get included. All comparisons are made to the current row. Here's an example of the data:
A B C D E
1 Company Generation Date Value ToCalculate
2 Abc 1 1/1/2010 5.6
3 ... ... ... ... ...
E would look something like this
{=Average(If(A2=A2:A1000, If(B2=B2:B1000, If(C2 > C2:C1000, D2:D1000))))}
So once E2 is calculated then I have to autofill down column E. Column F, G, H, ... Uses the same approach, either selects different values to operate on or a different function to perform. My dataset is quite large, and with only a few of these the spreadsheet is taking an hour plus to compute. Every so often I'll add a fourth criteria, all other criteria being the same.
Is there an efficiency? Some thoughts:
- Can I use a single array per column instead of thousands per column?
- Can I condense the first three criteria so that the output is row numbers? Perhaps then subsequent formulas won't have to search for multiple criteria but can just perform the function?
- or somehow build the crtieria up? So a new column returns all rows where the company is the same. another column returns all rows from the first column where generation is the same...and so on...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
对于平均值，您可以不使用数组：
由于还有 COUNTIFS 和 SUMIFS，我认为您的斜率可以用相同的方式计算。
对于其余的函数（max、min 等），我们应该具体情况具体分析。
我做了一个轻微的性能测试，这显然更好，但当然我的数据集只是被嘲笑。
哈！
注意：仅限 Excel 2007 及更高版本！
编辑 - 回答您的评论。
在不知道问题的严重程度的情况下很难提供建议，但无论如何我都会冒险：
您可以编写一个 VBA 函数来：
1）为每个公司生成对生成一个新表
2) 按日期对这些工作表中的数据进行排序
3) 将公式添加到这些工作表中（在此上下文中不需要条件）
4) 重新计算并获取这些公式的结果并填充原始工作表
5）删除辅助表
For the Average you can do without arrays:
As there is also a COUNTIFS and a SUMIFS, I think your slopes could be calculated the same way.
For the rest of the functions (max, min, etc), we should analyze case by case.
I did a slight performance test, and this is apparently better, but of course my datasets are just mocked.
HTH!
Note: Excel 2007 and up only!
Edit - Answering your comment.
Without knowing the dimensions of the problem is difficult to give advice, but I'll risk one anyway:
You could write a VBA function that:
1) Generates a new sheet for each company-generation pair
2) Sorts the data in those sheets by date
3) Adds the formulas to those sheets (no conditionals needed in this context)
4) Recalculates and Gets the results from those formulas and populates the original sheet
5) Deletes the auxiliary sheets
要捕获行并重新使用，请尝试以下方法：
按公司和公司对数据进行排序一代。
制作一份独特的公司和公司列表世代（使用高级过滤器、仅唯一、复制）
对于列表中的每个公司世代对，构建 2 列公式。
第一列给出该对数据中的行数（使用 COUNTIFS），第二列给出该对数据中的第一行（=first前一对的行+前一对的行数）。
然后，您可以使用像 OFFSET 这样的函数来仅返回 Company-Generation 对的数据行，并将其嵌入最终函数/数组公式（AVERAGEIFS 等） )
如果您愿意，您可以扩展这种排序和计数方法以包括日期。
有一个缺点，如果城市和代的列表发生变化，您必须更改唯一值和相关公式的列表。
我的网站上有这种方法的示例，网址为
http://www. Decisionmodels.com/optspeedk.htm
http://www.decisionmodels.com/optspeedj.htm
To capture the rows and re-use try this approach:
Sort the data by Company & Generation.
Make a unique list of Companies & generations (use Advanced Filter, Unique Only, Copy)
For each Company generation pair in the list build 2 columns of formulae.
First column gives the count of rows in the data for this pair (use COUNTIFS), second column gives the first row in the data for this pair (=first row for previous pair+count of rows for previous pair).
Then you can use a function like OFFSET to return only the rows of data for the Company-Generation pair and embed this inside the final function/array formula (AVERAGEIFS etc)
You could extend this sort and count approach to include dates if you wanted.
There is a drawback that if the list of cities and generations change you have to change the list of uniques and associated formulas.
There are examples of this approach on my website at
http://www.decisionmodels.com/optspeedk.htm
http://www.decisionmodels.com/optspeedj.htm