[求助]定义公式期待高手的指点！（这个问题困扰我很久了！）

yaozhaoze

   定义公式，平时这个公式，写全部返回假，所以得出想要的结果。但到了自定义公式里，却全部返回空。虽然可以通过改变公式的方法也让自定义公式能用，但为什么用F9键得到是真，却返回假呢？
期待高手的指点！（这个问题困扰我很久了！） 9nBm8qhr.rar (16.95 KB, 下载次数: 172)

2008-2-23 10:29 上传

点击文件名下载附件
[求助]定义公式期待高手的指点！（这个问题困扰我很久了！）

山菊花

要弄清这个问题，必须了解几个知识：

1、隐含交叉

在汇总工作表输入公式：

F3=SHL月汇总!A:A

G3=SHL月汇总!A3

可以看到，两个单元格公式不同，而结果相同。其中F3单元格公式相当于：

=SHL月汇总!A:A SHL月汇总!3:3

注意，中间是一个空格。

这个公式称为交叉引用，A列与第3行交叉，结果是SHL月汇总!A3。类似F3单元格公式，引用了一列多个单元格，在这种情况下，系统默认它是一个交叉引用，行号取当前单元格的行号(3)，把F3公式往下复制，可以发现，会出现不相同的结果，这是列相同而行不相同的原因。这种交叉称为“隐含交叉”。

再作个练习，以加深对“隐含交叉”的理解。在H3单元格输入公式：

=SHL月汇总!1:1

这是引用SHL月汇总工作表第1行，这种情况下，系统自动以当前列号取交叉值，结果与下面公式相同：

H3=SHL月汇总!H1

H3=SHL月汇总!H:H SHL月汇总!1:1

H3=SHL月汇总!1:1 SHL月汇总!H:H

在普通公式中，对整行或整列的引用，系统都默认以当前行列号取交叉值，这就是隐含交叉。

理解了这一点，原公式可以改为：

C3=IF(ISERR(INDIRECT(汇总!C$2&"月汇总!A1")),"",SUMIF(INDIRECT(汇总!C$2&"月汇总!A:A"),汇总!$A3,INDIRECT(汇总!C$2&"月汇总!D:D")))

2、在自定义名称中，数组公式优先

在工作表的普通公式中引用整行或整列，会以隐含交叉返回引用。但在自定义名称中，引用多个单元格时，只要函数支持，都会默认为数组参数，返回数组结果。

如“ISERR(INDIRECT(汇总!C$2&"月汇总!A:A"))”，前面讨论过，在普通公式中，它相当于“ISERR(INDIRECT(汇总!C$2&"月汇总!A3"))”，但在自定义名称中，它表示的结果是一个数组。

3、数组公式的限制

Excel2003及以下版本中，数组公式不允许引用整列，正因为如此，在“ISERR(INDIRECT(汇总!C$2&"月汇总!A:A"))”中，公式“INDIRECT(汇总!C$2&"月汇总!A:A")”返回错误。

这个限制在Excel2007中被取消。

因此，定义名称HZ时要注意，ISERR()与SUMIF()的第2个参数不要引用整列。

光标定位B3单元格，插入—名称—定义：

HZ=IF(ISERR(INDIRECT(汇总!B$2&"月汇总!A1")),"",SUMIF(INDIRECT(汇总!B$2&"月汇总!A:A"),汇总!$A3,INDIRECT(汇总!B$2&"月汇总!D:D")))

ISERR()的目的，仅判断工作表是否存在，所以参数中可以使用固定单元格A1。

返回数组结果

题外话，如果需要根据A3:A11的值返回一个数组，得到汇总结果，它的公式是：

=IF(ISERR(INDIRECT(汇总!$C$2&"月汇总!A3:A11")),"",SUMIF(INDIRECT(汇总!$C$2&"月汇总!A:A"),汇总!$A$3:$A$11,INDIRECT(汇总!$C$2&"月汇总!D:D")))

为什么公式中可以引用整列？

前面郑重其事说过，数组公式中不能引用整列，而上面公式中还是存在对整列的引用，如何说？

这里有区别。与数组行数相关的参数，不能引用整列，如上面彩色部分，它决定了结果中数组的行数，它不能引用整列，SUMIF()的第1、3个参数，与结果的行数无关，所以它不受“整列”的限制。

yaozhaoze

山菊花老师指点,我明白