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

ZhouFfett

r_zxf 发表于 2018-3-27 09:21
请您对没有涉及过的项继续分析。

找原因.zip (4.13 KB, 下载次数: 14)

2018-3-27 12:01 上传

点击文件名下载附件

我们先分析E9:H9、E10:H10的区别

E10:H10为普通公式，参数$D1:$D4不是文本值，所以返回错误值，

而E9:H9为数组公式，用公式数组的方法来解释，就相当于
={INDIRECT(D1);INDIRECT(D2);INDIRECT(D3);INDIRECT(D4)}
然后D1到D4的值都是文本值，就是
={INDIRECT("A4");INDIRECT("A3");INDIRECT("A2");INDIRECT("A1")}
看到了吧，INDIRECT的参数是文本值了，该文本值代表一个单元格引用，于是，公式进一步返回
={7;5;3;1}
因为INDIRECT("A4")就是指向单元格A4的引用

因为一个单元格只能返回一个单值，所以公式最终返回7给单元格，其他的5、3、1被忽略

至于E8:E9的区域数组公式，比E9:H9的数组公式多了一维，超出两维了，所以出错。

所有的引用都是两维的，因为每个引用都由行号和列标构成。

ZhouFfett

yimi8 发表于 2018-3-27 10:49
老师这篇讲的很好，我这几天正好做公式在用INDIRECT和ADDRESS，公式报错，我感觉和INDIRECT和ADDRESS有关， ...

首行一般是固定的吧？即使不固定，也可以直接用=row(sheet!B3)来得到行号
另外，ADDRESS函数通常很少使用，直接用&生成字符串就行了
你的公式把问题复杂化了，不能突出重点
一边看你的公式，一边心里就在说：怎么这么写啊

wangg913

以前说过几次。
由数组（必须）和函数生成的引用，叫多维引用
直接跨表的引用，叫跨表多维引用，实际上直接跨表的多维引用就不是引用了。

=INDIRECT(D$1:D$4)
就是由数组和函数生成的多维引用.对于这个公式，D1：D4首先转为数组{"a4";"a3";"a2";"a1"}，再生成多维引用。
一般情况下，不使用特定函数，无法直接对多维引用进行运算，这些运算，也包括等号在内。

ZhouFfett

wangg913 发表于 2018-3-27 14:29
以前说过几次。
由数组（必须）和函数生成的引用，叫多维引用
直接跨表的引用，叫跨表多维引用，实际上直 ...

不赞同你的说法。
“由数组（必须）和函数生成的引用，叫多维引用”，有点费解，而且很多例子都证实这种说法是错误的

至于你所说的“=INDIRECT(D$1:D$4) …… D1：D4首先转为数组{"a4";"a3";"a2";"a1"}”，看看我附件里的例子吧

理解数组公式前应该先理解绝对交集。

在单元格输入的普通公式中，如果把一个只需要单值的数值，写成一个单行或单列的区域引用，Excel将这个引用解释为对公式所在的列或行，与该引用的行或列相交的单元格的引用，这叫做绝对交集，又叫做隐含交叉引用。
INDIRECT与绝对交集.rar (6.5 KB, 下载次数: 28)

2018-3-27 18:32 上传

点击文件名下载附件

如例子中的四个普通公式：
C2的公式=A1:A4*5，相当于=A2*5

E2的公式=INDIRECT("A1:A4")*5，由于INDIRECT("A1:A4")返回引用A1:A4，所以结果同C2公式

而F2公式=INDIRECT(A1:A4)*5  返回#REF!（无效的引用），是因为按照绝对交集，公式相当于=INDIRECT(A2)*5，而A2是一个数字值。

只有像G2公式=INDIRECT(B1:B4)*5，相当于=INDIRECT(B2)*5，而B2是文本值"A2"，所以等于=A2*5

wangg913

理解函数的方法，描述函数的定义和概念，不能自造名词，要依据函数的帮助才好。
如果每个人，都自己给函数使用方法，起个名字，不就乱套了？
如果帮助里没有，最好沿用前人（早期那些版主或者大咖）已经使用的名称为好。
这些名词，已经流传大约10年了，

ZhouFfett

r_zxf 发表于 2018-3-27 10:38
本质原因：
一、有些函数总是以数组形式返回值，比如ROW；大多数函数只有在数组公式中且参数为数组时，在 ...

对于你所说的第一、第二点
“一、有些函数总是以数组形式返回值，比如ROW；大多数函数只有在数组公式中且参数为数组时，在这样做。”
“二、大多数函数返回的是“真正”值，有些函数返回的是“引用”值，比如INDIRECT、OFFSET等。”

我的意见是，学习Excel要注重数据类型（学习每一种编程语言都很重视这一点）

在学习每一个函数的时候，既要注意分辨函数第一个参数需要什么类型的数据，又要注意函数返回值是什么类型。

引用只是数组的一种。数组也是一种数据类型。

数据类型分为单值和多值。

单值（也可以叫做基础值、JavaScript中叫原始值）包括数字值、文本值、逻辑值、错误值。

多值包括数组和对象。

对象好像少接触，其实不知不觉已经接触了，比如，公式可以返回图片，但返回的值保存在图片层中，图片并不属于任何一种原始值，而是对象

数组主要包括常量数组、引用数组、公式数组三种

常量数组就是数组常量，数组中的元素都是常量

引用数组就是单元格区域引用，数组中的元素都是单元格引用

公式数组是指数组元素是一个表达式，比如{INDIRECT("A4");INDIRECT("A3");INDIRECT("A2");INDIRECT("A1")}

ZhouFfett

wangg913 发表于 2018-3-27 18:41
理解函数的方法，描述函数的定义和概念，不能自造名词，要依据函数的帮助才好。
如果每个人，都自己给函数 ...

帮助里本来就有不少瑕疵

日心说流行才三百年，而之前的地心说已流行几千年

理论是用来指导实践的；新的理论如果能更好地发挥这种指导作用，就是更好的理论。

一个新名字，如果更容易使人理解，就是好名字。

ZhouFfett

r_zxf 发表于 2018-3-27 10:38
本质原因：
一、有些函数总是以数组形式返回值，比如ROW；大多数函数只有在数组公式中且参数为数组时，在 ...

你所说的第三、四点，是关于区域数组公式的

我举一个简单的例子，说明区域数组公式的为什么比普通公式、普通数组公式多了一维
区域数组公式多一维的简单例子.rar (6.23 KB, 下载次数: 18)

2018-3-27 19:31 上传

点击文件名下载附件

比如，D1输入普通公式=A1+1，向下拉到D3，这是普通的公式，没有用到数组，是零维

然后，选择E1:E3区域，输入=A1+1后按Ctrl+Shift+Enter三键，生成区域数组公式

你会发现：第一，公式中并没有用到数组，第二，区域数组公式已经括在{}里了

我们可以把这个区域数组公式看成以下的公式数组：
{A1+1;A1+1;A1+1}
数组中每个元素对应E1:E3中的每个单元格。

可见区域数组公式比普通公式总是增加一维
把数组公式输入成区域数组公式，也是这种情况，也是增加了一维

r_zxf

一般地，数组公式应用就是这样的形式：“区域=数组公式”，数组公式“集体”返回一个“真正”值的数组，给区域“集体”赋值的时候可以扩展。至于非要提出“维”的说法，不敢苟同。您的第一个例子是普通公式复制，第二个例子是数组公式应用，之所以称之为数组公式，就是指表达式的参数是数组，计算结果也是数组。

ZhouFfett

wangg913 发表于 2018-3-27 14:29
以前说过几次。
由数组（必须）和函数生成的引用，叫多维引用
直接跨表的引用，叫跨表多维引用，实际上直 ...

“由数组（必须）和函数生成的引用，叫多维引用”
是谁说的？

至于多少维，只能从分析最终生成的维数来确定，不能由“数组（必须）和函数生成”来确定
你说的这一点，从基本的逻辑思维就已经可以否定，不再需要什么例子了