☰

从 Excel 数组中删除非数字数据

tag-icon • tag-icon microsoft-excel worksheet-function
从 Excel 数组中删除非数字数据

我无法使用该方法LINEST处理包含间隙的数据。手动排序和删除间隙不是一个选项。

我发现了这一点：
=LINEST(ISNUMBER(X_4)*Y_4,IF(X_4<>0,ISNUMBER(Y_4)*X_4^{0,1,2},0),0)
来自http://newtonexcelbach.wordpress.com/2011/05/14/using-linest-on-data-with-gaps/
但它不处理#N/A或""（空白）只处理空单元格

我的数据可以包含数字、文本#N/A和空白。我需要某种方法来仅获取数字，因此我可以使用LINEST。

我也发现了这个可能的解决方案，但我无法使其工作，我认为公式中存在一些错误：
=LINEST(IF(ISNUMBER(C1:C9),C1:C9,),IF(ISNUMBER(C1:C9),CHOOSE({1,2,3},1,A1:A9,B1:B9),),) 来自：https://stackoverflow.com/questions/21736549/ignore-n-as-in-excel-linest-function-with-multiple-independent-variables-known

数据样本：

X   Y
1   16,0
2   18,0
""  #N/A //Here I mean a blank cell, but non null, like an empty string ""
4   41,0
5   48,0
6   61,0
    #DIV/0!

公式必须忽略任何非数字行，结果必须相同

X   Y
1   16
2   18
4   41
5   48
6   61

LINEST结果：a = 9,279069767 b = 3,395348837 [ y(x) = ax+b ]

答案1

尝试这个数组公式1：

=LINEST(INDEX(A1:B6,N(IF(1,SMALL(IF(MMULT(0+(ISNUMBER(A1:B6)),{1;1})=2,ROW(A1:B6)-MIN(ROW(A1:B6))+1),ROW(INDIRECT("1:"&COUNT(1/(MMULT(0+(ISNUMBER(A1:B6)),{1;1})=2))))))),2),INDEX(A1:B6,N(IF(1,SMALL(IF(MMULT(0+(ISNUMBER(A1:B6)),{1;1})=2,ROW(A1:B6)-MIN(ROW(A1:B6))+1),ROW(INDIRECT("1:"&COUNT(1/(MMULT(0+(ISNUMBER(A1:B6)),{1;1})=2)))))))),1))

1 数组公式的输入方式与“标准”公式不同。ENTER您不必只按 ，而是先按住CTRL和SHIFT，然后再按ENTER。如果您操作正确，您会注意到 Excel 会{}在公式周围加上花括号（但不要尝试自己手动插入这些括号）。

答案2

我是问题中链接的博客的作者。

我已经修改了 LinestGap UDF，因此它将忽略任何带有错误、“”或任何列中为空单元格的行。修改后的电子表格可以从以下位置下载：http://interactiveds.com.au/software/Linest-poly.xls

相关内容