提高此Excel公式性能的最快方法是什么? [英] What is the fastest way to boost the performance of this Excel formula?

问题描述

这是从后面的帖子走的更远.

This is to go further from the following post.

这是我要解决的问题.给定具有类别和值的数据集

Here is a problem I am trying to solve. Given a data set with categories and values

category   value1   value2
a           1.0     ...
a           2.0
a           1.0
a           3.0
b           1.0
b           5.0
b           2.0     ...
...

我想通过检查上一行的值更改是否在其类别的一个西格玛偏差内来验证这些值.这意味着我们需要跳过每个类别的第一行.

I want to validate these values by checking if the value change from the row above is within one sigma deviation of its category. That means we need to skip the first row of each category.

这是我尝试的方法:

以下公式适用于从每个类别的第二行到最后一行的每个类别的单元格.

The following formula works for cells of each category beginning from the second row to the last row of each category.

=INDIRECT(ADDRESS(ROW(), COLUMN())) - INDIRECT(ADDRESS(ROW()-1, COLUMN())) < 
1.0*STDDEV.P(INDIRECT(ADDRESS(MATCH(INDIRECT("A" & ROW()), $A:$A, 0), COLUMN()) & ":" &ADDRESS(MATCH(INDIRECT("A"&ROW()),$A:$A, 1), COLUMN())))

它的运行速度非常快，但是我们需要清除每个类别第一行的数据验证.

It works pretty fast, but we need to clear the data validation for the first row of each category.

这是@ user3964075提供的解决方案

{=IF($A2<>$A1,TRUE,B2-B1<STDEV.P(IF($A:$A=$A2,B:B)))}

问题在于性能.200Kb数据集需要十多分钟的时间.

The problem is the performance. It need more than ten minutes for 200Kb data set.

最快的公式是什么?

推荐答案

除了 STDDEV.P (

Beyond the STDDEV.P (STDEV.P function...?) in the original question, the flagrant use of volatile¹ functions in underlying processes like data validation has got to be killing your calculation cycles. Substituting for array formulas² with full column references is not helping.

以下方法将创建一些动态命名范围.源自A1的连续数据块"island"将有一个，而其他数据块将在您使用它们的任何行上引用A列，以及将在其使用的任何单元格上方的单元格以及 STDEV.P函数其返回的结果.

The following method is going to create some dynamic named ranges. There will be one for the contiguous data block 'island' originating in A1 and others that will reference column A on whatever row you use them as well as cells above whatever cell it is being used in and the range from which the STDEV.P function will be getting its returned result.

虽然在预期的工作表之外使用这些功能没有多大意义，但将为它们提供工作簿(而不是工作表)范围.我在任何地方都看不到工作表的实际名称，因此我将使用 Sheet2 .如有必要，请调整工作表名称.请注意，您将在单元格范围引用中使用绝对和相对列和行的各种组合.

While there is not much point in using these beyond the worksheet for which they are intended, they will be given Workbook (as opposed to worksheet) scope. I cannot see anywhere that the worksheet was actually named so I will work with Sheet2. Adjust the worksheet name if necessary. Note that you will being using varying combinations of absolute and relative columns and rows in the cell range references.

1. 在Sheet2上选择A2.这是重要！
2. 转到公式"►定义的名称"►名称管理器".当名称管理器对话框打开时，单击新建以创建每个命名范围.
3. stdCAT -将引用A列中与正在使用的任何行相对应的单元格.
   • 名称:stdCAT
   • 范围:工作簿
   • 引用至: = Sheet2！$ A2

1. Select A2 on Sheet2. This is IMPORTANT!
2. Go to Formulas ► Defined Names ► Name Manager. When the Name Manager dialog opens, click New to create each of these named ranges.
3. stdCAT - Will reference the cell in column A that corresponds to whatever row it is being used on.
   • Name: stdCAT
   • Scope: Workbook
   • Refers to: =Sheet2!$A2

• 名称:stdVALa
• 范围:工作簿
• 指的是: = Sheet2！A2

• 名称:stdVALb
• 范围:工作簿
• 参考: = Sheet2！A1

• 名称:stdDATA
• 范围:工作簿
• 引用至: = Sheet2！$ A $ 1:INDEX(Sheet2！$ A:$ Z，MATCH("zzz"，Sheet2！$ A:$ A)，MATCH("zzz"，Sheet2！$ 1:$ 1))

• 名称:stdRNG
• 范围:工作簿
• 引用: = INDEX(stdDATA，MATCH(Sheet2！$ A2，Sheet2！$ A:$ A，0)，COLUMN()):INDEX(stdDATA，MATCH(Sheet2！$ A2，Sheet2！$ A:$ A，1)，COLUMN())

(从样本数据中选择 Sheet2！B3:B8 )，然后选择数据"►数据工具"►数据验证".选择允许:自定义，并为来源:，

Select Sheet2!B3:B8 (from your sample data) and choose Data ► Data Tools ► Data Validation. Opt for Allow: Custom and supply the following for the Source:,

=(stdVALa-stdVALb)<STDEV.P(stdRNG)

单击确定创建数据验证规则.选择其他范围，并根据需要创建其他数据验证规则.

Click OK to create the Data Validation Rule. Select other ranges and create other Data Validation Rules as necessary.

您所要做的是完全绕过了无引用运算符数据验证限制，而您无需使用具有全列引用的volatile¹函数或数组公式²就可以做到这一点.在公式中使用时，每个命名范围及其后续的计算负荷都只是绝对必须的大小. MATCH函数中使用的完整列引用不会对计算负荷产生负面影响，因为必须找到 lookup_value .

What you've accomplished is a complete bypass of the No reference operators data validation restriction and you've done this without resorting to volatile¹ functions or array formulas² with full column references. Each named range and its subsequent calculation load when used in a formula is only as large as it absolutely has to be. The full column references used in the MATCH functions will not negatively impact the calculation load as by their very nature the lookup_value has to be found.

您应该能够识别出与所得公式和原始方法的相似之处，这是设计使然.我无意提供一个新公式而是将工作模型重新设计为更有效的版本.

You should be able to recognize close similarities with the resulting formula and your original method and that is by design. It was not my intention to provide a new formula so much as to redesign the working model to a more efficient version.

¹ _{Volatile函数会在整个工作簿中的任何内容发生更改时重新计算，而不仅仅是影响其结果的某些内容发生更改时. volatile 函数的示例为地址，今天和偏移量.}

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, ADDRESS, TODAY and OFFSET.}

²_{数组公式随着它们引用的单元格范围的扩大，以对数形式增加了计算周期.在数组公式中使用时，请始终尝试将所有单元格范围引用保持在绝对最小值.}

这篇关于提高此Excel公式性能的最快方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！