对数组使用 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屋！