如何克服Excel中公式的最大长度限制?excel的错误? [英] How to overcome the max length limit of a formula in Excel? a bug of excel?

问题描述

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

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

 类别value1 value21.0 ...2.01.03.0b 1.0b 5.0b 2.0 ...... 

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

这是我尝试过的:

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

  = INDIRECT(ADDRESS(ROW()，COLUMN()))-INDIRECT(ADDRESS(ROW()-1，COLUMN()))<1.0 * STDDEV.P(INDIRECT(ADDRESS(MATCH(INDIRECT("A"& ROW())，$ A:$ A，0)，COLUMN()"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()"A"& ROW())，$ A:$ A，1)，COLUMN())))，TRUE，FALSE) 

如果一个人将公式输入到单元格中，则可以使用此方法，但不适用于数据验证的引用公式.

要使用所有单元的所有行(无需手动跳过每个类别的第一行)，我编写了以下公式来进行数据验证.但是由于excel的最大长度限制，它给出了"FOrmula当前评估为错误...".

  = IF(MATCH(INDIRECT("A"& ROW())，$ A:$ A，0)= ROW()，TRUE，INDIRECT(地址(ROW()，COLUMN()))-INDIRECT(地址(ROW()-1，COLUMN()))<1.0 * STDDEV.P(INDIRECT(ADDRESS(MATCH(INDIRECT("A"& ROW())，$ A:$ A，0)，COLUMN()"A"& ROW())，$ A:$ A，1)，COLUMN())))) 

尽管Microsoft声称excel公式的长度限制为"8192"，但事实并非如此:

请输入相同的公式，但请按 Ctrl + Shift + Enter 进行确认.现在它是一个数组公式，结果不同(10).

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

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

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

There are at least two forms of formula in Excel. One is used for cells, and the other is used for data validation. The max length limit for the formula to do data validation is about only 210 chars.

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.

Here is what I tried:

The following formula works for cells of each category beginning from the second row to the last row of its 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())))

However, the following doesn't work because of the max length limit of formula in excel - just add 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)

This can work if one input the formula into a cell, but it doesn't work for the refer-to formula of Data Validation.

To work with all cells all rows (without need to manully skip the first row of each category), I wrote following formula for data validation. But it gives "The FOrmula currently evaluates to an error..." because of the max length limit of the excel.

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

Although Microsoft claims that the length limit of formula of excel is "8192", it seems not true: https://support.office.com/en-nz/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

Update:

Here is another try for the formula of "Data Validation":

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

It is strange that the True/False value of the formula above is correct when I input in a cell, but when I input it in the "Data Validation" ->Custom -> Formula. The result is completely wrong. It always gives False.

解决方案

In Excel 2010 you can enter max 255 chars in data validation formula.

You can simplify your formula to fit the limits.

Instead of using:

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

You can simply use cell relative address.

Select e.g. B2:B50 and enter validation formula:

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

This should be equivalent to your formula.

within one sigma deviation of its category

I think you may need to use ABS(B2-B1) instead of B2-B1.

EDIT:

Your screenshot to prove that my formula is not working as expected:

Please enter the same formula, but confirm it with Ctrl+Shift+Enter. Now it's an array formula, with different result (10).

You can't confirm formula entered in data validation field with Ctrl+Shift+Enter, but you don't need to do it. Excel parses every formula in data validation as an array formula.

Below is the screenshot of sample data with data validation applied:

On the right you can see a table with standard deviation of all cells, and each category. Circle invalid cells option highlights cells that do not meet criteria. As you can see different deviation is applied to each category.

这篇关于如何克服Excel中公式的最大长度限制?excel的错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！