允许任何Excel公式中的范围 [英] Allow ranges in any Excel formula

问题描述

  = SUMPRODUCT（MONTH（A1：A4）...）<  - 没问题
  

其他功能不

  = SUMPRODUCT（EOMONTH（A1： A4，0）...）<  - 错误
  

有没有办法强制这样的功能 EOMONTH 可以使用范围？

解决方案

$ c>Analysis ToolPak像 EOMONTH ， WORKDAY ， NETWORKDAYS ， WEEKNUM 等将在 Excel 2007 或更高版本中接受数组您可以使用像+0或* 1这样的co-ercer将数组更改为一个范围，例如

= SUMPRODUCT（ - （WEEKNUM（A1：A10 + 0）= 1））

这将给你一个 A1：A10 在第1周（由 W定义） EEKNUM ） - 没有+0它不起作用。

还有其他功能，如 VLOOKUP 或 INDEX 不能轻易地输出数组 - 那些不能以同样的方式被共享

In Excel, some functions accept ranges, which can be used with SUMPRODUCT and other array functions:

=SUMPRODUCT(MONTH(A1:A4) ...)       <-- no problem

Other functions do not:

=SUMPRODUCT(EOMONTH(A1:A4, 0) ...)  <-- ERROR

Is there a way to force functions like EOMONTH to work with ranges?

解决方案

Typically the ex "Analysis ToolPak" functions like EOMONTH, WORKDAY, NETWORKDAYS, WEEKNUM etc. will accept arrays in Excel 2007 or later - so you can use a "co-ercer" like +0 or *1 to change an array to a range, e.g.

=SUMPRODUCT(--(WEEKNUM(A1:A10+0)=1))

That will give you a count of how many of the dates in A1:A10 are in week 1 (as defined by WEEKNUM) - without the +0 it doesn't work.

There are other functions - like VLOOKUP or INDEX which can't easily output arrays - those can't be co-erced in the same way

这篇关于允许任何Excel公式中的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！