使用Excel N函数与数组 [英] Using Excel N function with arrays

问题描述

一个相当基本的问题，但是我一直无法找到以前的答案 - 也许部分原因是因为难以搜索N功能，而不会发生大量的错误匹配。

A fairly basic question, but I have been unable to find a previous answer - perhaps partly because it is difficult to search on 'N function' without getting a lot of false matches.

如果单元格包含数字，则N函数是返回数字的简洁方法，如果它包含文本，则为零。它比使用ISNUMBER函数更短，在数组公式中可能有用。

The N function is a concise way of returning a number if a cell contains a number or zero if it contains text. It is shorter than using the ISNUMBER function and potentially useful in array formulae.

为什么我可以写

=SUM(N({1,2,3}))

并得到答案6，但如果我写

and get the answer 6, but if I write

=SUM(N(A1:A3))

和A1：A3包含一些数字，我只收到第一个数字？

and A1:A3 contains some numbers, I just get the first number?

如果我去评估公式，它表明它不是将A1：A3视为数组，即使我以数组公式输入它。

If I go to Evaluate Formula, it shows that it is not treating A1:A3 as an array, even if I enter it as an array formula.

有没有办法强制N函数在数组公式中工作？

Is there a way of coercing the N function to work in an array formula?

推荐答案

尝试：

= SUM（N（+ A1：A3））

运算符足以强制从传递的范围中返回数组。

The unary plus operator is here sufficient to coerce an array return from the range being passed.

将一元运算符应用于范围中的每个值也很方便这样，当我们把他们传给N时，如果我们简单地将N函数应用于每个函数，我们将获得的值将相当于。

It is also convenient that the results of applying the unary operator to each of the values in the range are such that, when we then pass them to N, the resulting values will be equivalent to those we would have obtained had we simply applied the N function to each.

关于

这篇关于使用Excel N函数与数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！