从分析OFFSET函数开始

ZhouFfett

一、分析公式1和公式2，主要分析公式返回什么类型的数值。
通过F9，我们发现公式1返回一个单值12，而公式2返回一个数组{12,0}。
原理1：一个单元格只能保存一个单值，如果给单元格返回一个数组，则单元格的值为数组的第一个元素。

然而，单元格C4直接输入={12,0}，却能返回12，为什么呢？
这是因为，OFFSET返回的只是单元格引用，而不是单元格的值，也就是说，OFFSET并不直接返回{12,0}！！！
原理2：函数INDIRECT和OFFSET返回的都只是单元格引用，而不是单元格的值。

公式2返回#VALUE!，就跟单元格D4直接输入=(A2,B2)的结果一样：一个单元格不能同时引用多个单元格的值。

对公式1的正确理解是：第一步，OFFSET($A$1,1,)返回单元格引用A2；第二步，=A2返回结果12。
对公式2的正确理解是：第一步，OFFSET($A$1:$B$1,1,)返回单元格引用(A2,B2)；第二步，=(A2,B2)返回#VALUE!。
offset-1.zip (7.52 KB, 下载次数: 28)

2018-1-3 11:29 上传

点击文件名下载附件

ZhouFfett

二、分析公式3和公式4，主要是分析公式有没有进行数组运算，OFFSET究竟返回了几个引用。
原理3：在单元格的普通公式中，当给某个要求是单值的操作数提供了数组常量，则只取数组常量中第一个元素进行计算，其余的元素一律忽略。
OFFSET函数的第二个参数，是指定一个行的偏移量，是一个单值。
公式3的数组常量{1;2;3;4;5}中，只有第一元素1是有效的；公式3相当于=OFFSET($A$1,1,)。
公式3返回1个单元格引用；
而公式4进行数组运算后，返回1个数组，数组中包含5个元素，分别是5个单元格引用：{A2;A3;A4;A5;A6}。
根据原理1，尽管公式4返回一个包含5个元素的数组，单元格只保留数组第一个元素的值。
这里需要注意两点：
1、公式4返回的数组，其中的元素并不是常量（或者说，并不是确定的值），而是单元格引用，这与数组常量是不同的。
2、公式4返回的是5个引用，而不是1个引用，所以，G2:G6与H2:H6返回不同的值。
offset-2.zip (8.46 KB, 下载次数: 19)

2018-1-3 11:37 上传

点击文件名下载附件

活在理想的世界

顶，支持，有希望了。

ZhouFfett

三、为什么同样的公式4，在J2:J6区域内输入数组公式（以下简称区域数组公式），结果会不同呢？
原理4：Excel公式是在工作表这样一个二维的空间里运行的，处理的数据一般不能超过二维。
区域数组公式与一般数组公式的区别在于：
1、在区域内，区域数组公式只有一条公式，而一般数组公式有多条公式；
2、在区域内，一条区域数组公式生成区域内所有单元格的值，而一般数组公式只生成一个单元格的值。
原理5：区域数组公式为了在区域内所有单元格都返回值，需要对公式中的数据进行数组扩展。
{=OFFSET($A$1,{1;2;3;4;5},)}进行数组扩展后，
变成{=OFFSET({$A$1,$A$1,$A$1,$A$1,$A$1},{{1;2;3;4;5},{1;2;3;4;5},{1;2;3;4;5},{1;2;3;4;5},{1;2;3;4;5}},)}
从而造成OFFSET函数的第二个参数超出二维，所以公式返回#VALUE!。
offset-3.zip (12.81 KB, 下载次数: 16)

2018-1-3 11:43 上传

点击文件名下载附件

1120933984

大佬，你发的东西我都看不懂。。。。我好笨

ZhouFfett

1120933984 发表于 2018-3-7 11:08
大佬，你发的东西我都看不懂。。。。我好笨

数组这PART确实有点高端，你还是从基础的学起吧

r_zxf

正确：公式2返回的是A2:B2。

r_zxf

ZhouFfett 发表于 2018-1-3 11:43
三、为什么同样的公式4，在J2:J6区域内输入数组公式（以下简称区域数组公式），结果会不同呢？
原理4：E ...

正确：{=OFFSET($A$1,{1;2;3;4;5},)}扩展后成为{=OFFSET({$A$1;$A$1;$A$1;$A$1;$A$1;},{1;2;3;4;5},)}
如果可以这样{引用}表示的话，省略的参数同样省略。

r_zxf

ZhouFfett 发表于 2018-1-3 11:43
三、为什么同样的公式4，在J2:J6区域内输入数组公式（以下简称区域数组公式），结果会不同呢？
原理4：E ...

正确：J2:J6数组公式出错是因为，系统不对OFFSET函数的多个结果进行数值转换。你换成INDEX函数试试，使其返回同样结果，看看出错否？

r_zxf

ZhouFfett 发表于 2018-3-29 14:58
数组这PART确实有点高端，你还是从基础的学起吧

怎么不回复？觉得我们够不上这种高度吗？很希望探讨。