excel - 如何克服 Excel 中公式的最大长度限制？ excel的错误？

Question

Excel 中至少有两种形式的公式。一个用于单元格，另一个用于数据验证。用于数据验证的公式的最大长度限制大约只有 210 个字符。

这是我要解决的问题。给定一个包含类别和值的数据集

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

我想通过检查上一行的值变化是否在其类别的一个 sigma 偏差范围内来验证这些值。这意味着我们需要跳过每个类别的第一行。

这是我尝试过的:

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

=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())))

但是，由于 excel 中公式的最大长度限制，以下内容不起作用 - 只需添加 IF(formula_above, True, False):

=IF(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()))), TRUE, FALSE)

如果将公式输入到单元格中，这可以工作，但它不适用于数据验证的引用公式。

为了处理所有单元格的所有行(无需手动跳过每个类别的第一行)，我编写了以下公式进行数据验证。但由于 excel 的最大长度限制，它给出了“当前的 FOrmula 计算结果为错误......”。

=IF(MATCH(INDIRECT("A" & ROW()), $A:$A, 0) = ROW(), TRUE,
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()))))

虽然微软声称excel公式的长度限制是“8192”，但好像不是这样的: https://support.office.com/en-nz/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

更新:

下面是“数据验证”公式的另一种尝试:

=IF($A2<>$A1, TRUE, abs(B2-B1)<Stdev.P(offset(indirect(address(match($A2,$A:$A,0), column())), 0,0,countif($A:$A, $A2,1)))

奇怪的是，当我在单元格中输入时，上面公式的 True/False 值是正确的，但是当我在“数据验证”->自定义-> 公式中输入时。结果完全错误。它总是给出 False。

Answer 1

在 Excel 2010 中，您可以在数据验证公式中输入最多 255 个字符。

您可以简化公式以适应限制。

而不是使用:

INDIRECT(ADDRESS(ROW(), COLUMN()))

您可以简单地使用单元格相对地址。

选择例如B2:B50 并输入验证公式:

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

这应该等同于您的公式。

within one sigma deviation of its category

我认为您可能需要使用 ABS(B2-B1) 而不是 B2-B1。

编辑:

你的截图证明我的公式没有按预期工作:

请输入相同的公式，但使用 Ctrl+Shift+Enter 确认。现在它是一个数组公式，具有不同的结果 (10)。

您无法使用 Ctrl+Shift+Enter 确认在数据验证字段中输入的公式，但您不需要这样做它。 Excel 将数据验证中的每个公式解析为数组公式。

下面是应用了数据验证的示例数据的屏幕截图:

在右侧，您可以看到一个表格，其中包含所有单元格和每个类别的标准差。 Circle invalid cells 选项突出显示不符合条件的单元格。如您所见，每个类别都应用了不同的偏差。