关于INDIRECT函数的数组疑难问题剖析

ZhouFfett

先看附件 INDIRECT的恩怨.zip (4.03 KB, 下载次数: 81)

2018-3-26 15:10 上传

点击文件名下载附件

问题一
H6=ADDRESS(ROW($A$1)+INT((ROW()-ROW($A$6))/2),1,4)&":"&ADDRESS(ROW($A$1)+INT((ROW()-ROW($A$6))/2),3,4)

单元格H6返回文本"A1:C1"

然后，E7:G7输入区域数组公式=INDIRECT(H6)，E7:G7能够返回{1,5000,6000}

但是，直接在E6:G6输入区域数组公式
=INDIRECT(ADDRESS(ROW($A$1)+INT((ROW()-ROW($A$6))/2),1,4)&":"&ADDRESS(ROW($A$1)+INT((ROW()-ROW($A$6))/2),3,4))
却返回{#VALUE!,#VALUE!,#VALUE!}
直接把H6的公式代入为INDIRECT的参数，何解会返回错误值？？？

ZhouFfett

要理解问题一，需要首先明白两个道理：

第一，一个单元格只能返回一个单值

H6的公式，其实返回了一个数组——用功能键F9会发现返回数组{"A1:C1"}（虽然只有一个元素，但这确实是一个数组，不是单值），但是由于一个单元格只能返回一个单值，这时候单元格的值自动取该数组第一个元素的值，所以单元格返回单值——字符串"A1:C1"

第二，Excel公式不能处理超过二维的数组（这个道理，可能很多异议，但解释起来，已经偏离INDIRECT函数的范围了——可能涉及到OFFSET函数，还用所谓的三维引用；总之，这个道理是正确的）

INDIRECT函数返回的是一个引用，所有的单元格引用都是二维的。

E6:G6的区域数组公式相当于=INDIRECT({"A1:C1"})，而不是=INDIRECT("A1:C1")

——当INDIRECT的参数是一个数组时，INDIRECT的返回值将变成三维数组，所以返回错误值#VALUE!

这就是E6:G6区域数组公式与E7:G7区域数组公式的差异

ZhouFfett

问题二，为什么E1:E4的区域数组公式、F1:F4的数组公式及G1:G4的公式返回不同的结果？

先理解G1:G4，这得从绝对交集说起

在单元格输入的普通公式中，如果把一个只需要单值的数值，写成一个单行或单列的区域引用，Excel将这个引用解释为对公式所在的列或行，与该引用的行或列相交的单元格的引用，这叫做绝对交集，又叫做隐含交叉引用。例如：
在单元格C2输入公式=A1:A3*5，相当于=A2*5，因为单元格A2和C2位于同一行。
在单元格B5输入公式=A2:C2*5，相当于=B2*5，因为单元格B2和B5位于同一列。
绝对交集只适用于普通公式，不适用于数组公式

于是，G1相当于=INDIRECT(D1)，G2相当于=INDIRECT(D2)，等等

F1:F4的数组公式，其中的D$1:D$4返回数组{"a4";"a3";"a2";"a1"}给INDIRECT，INDIRECT由于只需要单值，所以只取其中的第一个元素a4的值。

E1:E4的区域数组公式，是由于超出维数，这已在问题一里解释了。

ZhouFfett

这里再对比一下以下三个公式的区别：
=INDIRECT({"A1:C1"})
=INDIRECT("A1:C1")
=INDIRECT({"a4";"a3";"a2";"a1"})

首先要记住，INDIRECT函数第一个参数需要的是一个文本值
于是，=INDIRECT("A1:C1")中的参数"A1:C1"，不要看成引用了，那只是一个字符串

至于INDIRECT({"a4";"a3";"a2";"a1"})与INDIRECT({"A1:C1"})的区别，我用公式数组来解释一下吧

数组中的元素，如果都是常量，通常叫数组常量（其实应该叫做常量数组）；如果元素是单元格引用，可以叫做引用数组（就是单元格区域引用）；如果数组的元素是公式，我们可以叫它做公式数组。

INDIRECT({"a4";"a3";"a2";"a1"})转换成公式数组，就是
={INDIRECT("a4");INDIRECT("a3");INDIRECT("a2");INDIRECT("a1")}
由于一个单元格只能返回一个单值，所以单元格F1:F4都只返回INDIRECT("a4")

而INDIRECT({"A1:C1"})转换成公式数组，就是
={INDIRECT("A1:C1")}
单元格自动取数组第一个元素的值——但是，INDIRECT("A1:C1")返回的是二维引用，不是单值，所以单元格返回错误值。

所以，I1:I4的区域数组公式=INDIRECT(D1)就不会返回错误值了。

r_zxf

请您对没有涉及过的项继续分析。

mmlzh

貌似有结果，只是结果没能正确显示而已

1. =INDEX(INDIRECT(ADDRESS(ROW($A$1)+INT((ROW()-ROW($A$6))/2),1,4)&":"&ADDRESS(ROW($A$1)+INT((ROW()-ROW($A$6))/2),3,4)),{1,2,3})

复制代码

mmlzh

1. =INDEX(INDIRECT(ADDRESS(ROW($A$1)+INT((ROW()-ROW($A$6))/2),1,4)&":"&ADDRESS(ROW($A$1)+INT((ROW()-ROW($A$6))/2),3,4)),COLUMN()-4)

复制代码

公式返回的结果应该是一样的，虽是数组公式，但也要通过正确的逻辑分到对应的单元格里才能正确显示。

mmlzh

1. =INDIRECT(ADDRESS(MAX(ROW($A$1)+INT((ROW()-ROW($A$6))/2)),1,4)&":"&ADDRESS(MAX(ROW($A$1)+INT((ROW()-ROW($A$6))/2)),3,4))

复制代码

应该说是ROW()返回的是数组，在公式中一直保持着数组的特性，所以INDIRECT最后出来的一个引用
通过MAX等降维了就一样的。
H6只不过是通过单元格进行了降维。

r_zxf

本质原因：
一、有些函数总是以数组形式返回值，比如ROW；大多数函数只有在数组公式中且参数为数组时，在这样做。
二、大多数函数返回的是“真正”值，有些函数返回的是“引用”值，比如INDIRECT、OFFSET等。
三、数组公式是依次计算后“集体”（以数组形式）返回值，在”区域=数组公式“中，区域只接受“集体”“真正”值，不接受“集体”“引用”值。
四、“区域（或者单元格）=区域引用”，只能在数组公式中才能计算它们的值。
五、结论：只有满足以上要求时，结果才正确。所以，用函数IF、N、T等只是把引用转换成了“真正”值，本人认为不存在“降维”的概念。
若有错误，请批评指正！

yimi8

老师这篇讲的很好，我这几天正好做公式在用INDIRECT和ADDRESS，公式报错，我感觉和INDIRECT和ADDRESS有关，但是一直找不出问题，老师能分析一下。

想做一个公式，向上查找同一列中与当前数相同的最近的相同数，然后取相同数下面的两个数。
原公式不灵活
=OFFSET(Sheet2!B1,LARGE(IF(LOOKUP(B6,Sheet2!B3:B31)=Sheet2!B3:B31,ROW(Sheet2!B3:B31),0),2),,2) 想对公式进行改进，适合动态增加数据的需要

新公式提示有错，光标停在COUNT(INDIRECT("Sheet2!B1:"&"B"&MATCH中的MATCH这里，找不出原因，求高人指点一下。
此新公式动态灵活，就是复杂了些，公式中Sheet2!B3:B31的首尾根据实际情况求出，新公式改进的重点在这。
=OFFSET(Sheet2!B1,LARGE(IF(LOOKUP(INDEX(B:B,MATCH(A6,A:A,0)),INDIRECT(ADDRESS(MATCH(A6,Sheet2!A:A,0)-COUNT(INDIRECT("Sheet2!B1:"&"B"&MATCH(A6,Sheet2!A:A,0)))+1,2,4,,"Sheet2")&":"&ADDRESS(MATCH(A6,Sheet2!A:A,0),2,4)))=INDIRECT(ADDRESS(MATCH(A6,Sheet2!A:A,0)-COUNT(INDIRECT("Sheet2!B1:"&"B"&MATCH(A6,Sheet2!A:A,0)))+1,2,4,,"Sheet2")&":"&ADDRESS(MATCH(A6,Sheet2!A:A,0),2,4)),ROW(INDIRECT(ADDRESS(MATCH(A6,Sheet2!A:A,0)-COUNT(INDIRECT("Sheet2!B1:"&"B"&MATCH(A6,Sheet2!A:A,0)))+1,2,4,,"Sheet2")&":"&ADDRESS(MATCH(A6,Sheet2!A:A,0),2,4))),0),2),,2)