当不可用时恢复Excel操作

Question

我在excel上有一个数据框架，我需要为一个朋友在excel上做一个操作。所以不能使用python或R。

​

​

我想创建一个新列，在其中执行此操作：

ABS(A2-B2)+ABS(A2-C2)+ABS(A2-D2)+ABS(A2-E2)+ABS(A2-F2)+ABS(A2-G2)+ABS(A2-H2)+ABS(A2-I2)

问题是有些行有NA，因此excel结果是错误的。理想情况下，我希望这样: BB行

ABS(A2-B2)+ABS(A2-C2)+ABS(A2-D2)+ABS(A2-E2)+ABS(A2-F2)不包括+A2(A2-G2)+A2(A2-H2)+A2(A2-I2)，因为这最后三行得到NA值。

ABS(6-7)+ABS(6-7)+ABS(6-12)+ABS(6-20)+ABS(6-2) = 26

如果有人能帮上忙，我将不胜感激！谢谢

Answer 1

所以试试吧：

=Iferror(ABS(A2-B2),0)+iferror(ABS(A2-C2),0)+iferror(ABS(A2-D2),0)+iferror(ABS(A2-E2),0)+iferror(ABS(A2-F2),0)+iferror(ABS(A2-G2),0)+iferror(ABS(A2-H2),0)+iferror(ABS(A2-I2),0)

Answer 2

如果您的NAs始终位于行的末尾，则可以使用以下命令

=SUMPRODUCT(ABS(A1-B1:INDEX(B1:I1,COUNT(B1:I1))))

COUNT只计算数字，所以它只包含数字终止的单元格。在您的示例中，INDEX(B2:I2,COUNT(B2:I2))将返回对F2的引用。计算B2:INDEX部件如下所示

=SUMPRODUCT(ABS(A2-{7,7,12,20,2}))

然后

=SUMPRODUCT(ABS({-1,-1,-6,-14,4}))
=SUMPRODUCT({1,1,6,14,4})

SUMPRODUCT的计算方式类似于数组公式，因此可以避免单独键入每个ABS。