有没有使用数组参数调用的内置Excel函数的行为的文档？ [英] Is there any documentation of the behavior of built-in Excel functions called with array arguments?

问题描述

许多Excel的内置函数可以使用数组参数。有时结果记录在帮助中，有时不是。所以：

  = IF（{1,0,1}，42，99）
  

将返回{42，99，42}。 IF的帮助涵盖数组参数。但是：

  = INDEX（{2,3,5,7,11}，{2,4}）
  

将返回{3,7}。这是直观的，但是我找不到记录它的Microsoft源代码。和：

  = INDEX（{1,2,3,4; 5,6,7,8; 9,10， 11,12; 13,14,15,16}，{1,3}，{2,4}）
  

返回{2，12}，这根本不直观。

有没有涵盖这些不太常见的阵列用法的源代码？似乎有一个，但是我没有在网页搜索中找到它，因为它需要使用Excel，函数和数组...

$ b $编辑：

我很乐意使用数组公式，并一直使用它们，但我讨厌必须弄清楚尝试和错误会发生什么。这个特别的问题出现在我做（简化的等效的）时：

  = IF（ISNA（udf（）），{ 1,2,3,4}，{5,6,7,8}）
  

udf'返回，例如{1,2,3}，公式正在评估为{5,6,7，＃N / A}。这让我感到惊讶，即使我可以弄清楚发生了什么，并在其周围工作。它只是错了我，我找不到一个权威的来源，把它全部出来。

更多：

在这里的答案的帮助下，以及从超级用户的交叉发布的答案（http://superuser.com/questions/203177/is-there-any-documentation-of-the-behavior-of-内置的excel函数 - 机智），让我尝试更精确地描述数组公式如何工作。我的问题是要找出，如果存在类似的精确但官方的（或至少是权威的）描述某处，也填写了本描述中的缺失的部分。

所以这里。 （我复制并修改了从Neal在Superuser上的第一部分）：

当您使用一个或多个数组参数代替公式中的标量参数时，该公式将计算为具有与数组参数相同数量的元素的数组。结果数组的每个（标量）元素是公式将使用数组参数的相应（标量）元素进行评估。

您可以在功能计划中考虑这一点。 Excel基本上转换一个公式，如：

  = IF（{1,0,1}，42，99）
  

成功：

  f（x）= IF（[x]，42，99）
  

映射到标量x值的列表：

  map（f，{1,0,1}）
  

获取{42,69,42}。多个数组参数只是意味着概念函数有更多的参数：

  f（x，y）= INDEX（{1,2， 3,4; 5,6,7,8; 9,10,11,12; 13,14,15,16}，[x]，[y]）
  pre> 
 
 
然后：
  map（f，{1， 3}，{2,4}）
  
评估为{2，12}。
 
 
 
如果多个数组参数的长度不一样，Excel将尝试扩展它们。因此，
  = IF（{1,1,1}，{1,2,3,4}，{5， 6,7,8}）
  
评估为{1,2,3，＃N / A}因为{1,1,1}被扩展到{1,1,1，＃N / A}，IF（＃N / A，4，8）评估为＃N / A。
 
 
 
此描述涵盖了我可以想出的大多数示例。它还显示我，我不需要一个函数的功能描述如何处理数组参数。像SUM或AND这样的函数不像IF或INDEX那样工作，因为它们不一定要求标量参数。 （尽管如此，INDEX（{2,3,5,7,11}，{2,4}）如果从工作表中调用，则评估为{3,7}，但是如果通过VBA调用，则只有3使用Application.Evaluate，SUM（INDEX（{2,3,5,7,11}，{2,4}））评估为3，无论在哪里和如何调用。）
 
 
 
我只是不能相信微软或其中一个主要的Excel作者从未发表任何内容，而不是例子，这是有用的。希望Charles Williams在答案中提到的如何Microsoft Excel扩展阵列公式的讨论是正在寻找的。
解决方案
我非常怀疑任何关于Excel函数与数组的行为的这种文档都存在。
 
 
当Excel用于手册时，有一个有趣的部分叫做
 
Microsoft Excel如何扩展数组公式$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $在网络上。
 
还有一个部分授予特殊功能帮助您使用包含与数组一起使用的功能列表的数组（尽管我不相信这会给您您要查找的内容）。 
 
 EDIT 
 
我已经将Excel的数组扩展规则的描述添加到我的数组公式页面
  http://www.decisionmodels.com/optspeedj.htm  
 
Many of Excel's built-in functions can take array arguments. Sometimes the result is documented in the help and sometimes not. So:
=IF({1,0,1}, 42, 99)
will return {42, 99, 42}. The help for 'IF' covers array arguments. But:
=INDEX({2,3,5,7,11}, {2,4})
will return {3, 7}. This is intuitive, but I can't find a Microsoft source that documents it. And:
=INDEX({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16}, {1,3},{2,4})
returns {2, 12}, which is not intuitive at all.

Is there any source that covers these less-common array usages? It seems like there has to be one, but I'm not finding it in a web search because it requires using "Excel", "function", and "array"...

EDIT:

I'm comfortable with using array formulas and use them all the time, but I hate having to figure out what will happen by trial and error. This particular question arose when I was doing the (simplified equivalent of):
=IF(ISNA(udf()), {1,2,3,4}, {5,6,7,8})
where 'udf' was returning, say, {1,2,3}, and the formula was evaluating to {5,6,7,#N/A}. This surprised me, even though I can figure out what is going on and work around it. It just bugs me that I can't find an authoritative source that lays it all out.

MORE:

With the help of the answers here, and also from the answer to my cross-posting on Superuser (http://superuser.com/questions/203177/is-there-any-documentation-of-the-behavior-of-built-in-excel-functions-called-wit), let me attempt a more precise description of how array formulas "work". The point of my question is to find out if there exists a similarly precise, but official (or at least authoritative), description somewhere that also fills in the missing pieces in this description.

So here goes. (I copied and modified the first part of this from Neal on Superuser):

When you use one or more array arguments in place of scalar argument(s) in a formula, the formula evaluates to an array with the same number of elements as the array argument(s). Each (scalar) element of the result array is what the formula would evaluate to using the corresponding (scalar) element(s) of the array argument(s).

You could think of this in functional progamming terms. Excel essentially turns a formula like:
=IF({1,0,1}, 42, 99)
into a function:
f(x) = IF([x], 42, 99)
which it then maps over a list of scalar x values:
map(f, {1,0,1})
to get {42,99,42}. Multiple array arguments just mean the conceptual function has more arguments:
f(x, y) = INDEX({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16}, [x], [y]) 
and then:
map(f, {1,3}, {2,4})
evaluates to {2, 12}.

If the multiple array arguments are not the same length, Excel will try to expand them. Thus,
=IF({1,1,1}, {1,2,3,4}, {5,6,7,8})
evalues to {1,2,3,#N/A} because {1,1,1} got expanded to {1,1,1,#N/A} and IF(#N/A, 4, 8) evaluates to #N/A.

This description covers most of the examples I can come up with. It also shows me that I don't need a function-by-function description of how array arguments are handled. Functions like SUM or AND don't work like IF or INDEX, because they don't necessarily expect scalar arguments. (There do remain loose ends, though: INDEX({2,3,5,7,11}, {2,4}) evaluates to {3,7} if called from the worksheet, but only to 3 if called through VBA using Application.Evaluate. And SUM(INDEX({2,3,5,7,11}, {2,4})) evaluates to 3 regardless of where and how it's called.)

I just can't believe Microsoft or one of the major Excel authors never published anything on this other than examples, helpful as they are. Hopefully the discussion of "How Microsoft Excel Expands Array Formulas" that Charles Williams mentioned in his answer is the "official" source I'm looking for.
解决方案
I very much doubt that any such documentation about the behaviour of Excel functions with arrays exists.


When Excel used to come with manuals there was an interesting section called 

"How Microsoft Excel Expands Array Formulas"

That explained the rules about handling combinations of missmatched array sizes and scalars etc, but I can't find that information in Excel help or on the web.

There was also a section entitles Special Functions Help You Work with arrays that included a list of functions that work with arrays (although I do not believe this gives you what you are looking for).

EDIT

I have added descriptions of Excel's array expansion rules to my array formulae page at
http://www.decisionmodels.com/optspeedj.htm

                        
这篇关于有没有使用数组参数调用的内置Excel函数的行为的文档？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！