ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 159276|回复: 377

[分享] OFFSET数组化 撞出 array 级别的参数数组化

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2010-9-10 14:53 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:OFFSET
            用数组基本知识浅析OFFSET数组化应用

导语:

        OFFSET函数的返回值是引用区域,当其参数为数组时得到的返回值也是数组,数组的每个元素即是对应的引用区域-----参数驱动

        数组的各引用区域元素是分时隔离的-----分时特性

        使"array"形式的参数数组化成为可能-----参数驱动

时间紧的看导语就够了...
========================================================================================


1.参数驱动

        当函数的参数,或者运算符的操作数由单值升级为对应的数组时,就得到对应函数或运算的数组用法。

         这是一个指导性思想,有例行自然也会有例外,重要的是我们根据这个原理可以直接发开很多函数的数组用法。以lookup为例,对lookup第一参数进行数组化的结果如下图所示。
                        1-lookup数组化.jpg


                以上作为参数驱动的基础知识,下面我们就来探究OFFSET的数组化应用。先上一个表,作为讲述材料,大家可以直接下载附件以更好的理解。
                        讲述材料.jpg
OFFSET基本用法

                        offset普通用法.jpg
数组用法(对行偏、列偏组成一个数组)
                        OFFSET数组用法.png

             上图中,通过对OFFSET第2、第3参数的数组化得到一个数组,在单元格中显示的值为#VALUE,那么究竟这个#VALUE是否就代表了所代表的引用区域呢?
            通过SUM函数验证,在数值上是完全匹配的,也即证实了各#VALUE就是代表了不同的引用区域。因此,整个数组返回值的逻辑关系如下所示。
                        数组化应用的逻辑层次.png

我们不妨称之为 引用数组的双平面结构 现在想想用“大颗粒数组”应该更为合适

由于平时我们习惯了返回值对应一个单元格颗粒的数据,因此思维上受到禁锢,我们要挣脱这个束缚,让思想自由的飞翔!
                  

2.分时特性

        从上面的SUM验证我们还得到一个信息,数组的各个引用区域元素是分时独立的,也即他们并不是同时存在的。如果同时存在,那么依照SUM函数的机制,我们有理由相信它会将所有的数据进行汇总,而不会像现在这样进行隔离汇总。
            这个特性也不是OFFSET所独有的,在INDEX函数的数组特性用法中就有出现,如下图所示。当行参数是数组,列参数为0时就能分时取第一参数的多行,外套一个SUM函数时就成了按行汇总。                           
                      INDEX的分时特性.png      
                     直接按F9只能得到第一行的数据,但公式赋值到单元格的过程远比按F9要复杂。我们可以简单认为一个F9就是得到了一次计算,在落入多个单元格中时进行了多次计算。       

           我们通过下面的例子可以看的更加清楚一些。
                      分时特性.png
                      打开附件,在“分时特性”工作表,我们可以直接用F9来测试返回值,返回值为{1,2;3,4},正好对应OFFSET第一组参数说指向的引用区域,即公式 =IF(1,OFFSET($A$11,0,0,2,2)) 所指向的区域。我们可以理解为OFFSET的第一节拍返回值;虽然我无法通过F9等手段在时间上切片查看第二节拍、第三节拍、第四节拍的状态,但是通过整个IF函数的返回值状态我们有理由相信它确实是分了四个节拍来返回的。因为我们可以很合理的进行如下解释:

第一节拍:C14单元格
                       时间切片上,OFFSET返回值为{1,2;3,4}

                                        空间对应上,C14处于第1行、第1列位置

                       于是返回:1
第二节拍:D14单元格
                       时间切片上,OFFSET返回值为{10,20;30,40}

                                        空间对应上,D14处于第1行、第2列位置

                       于是返回:20

第三节拍:C15单元格
                       时间切片上,OFFSET返回值为{100,200;300,400}

                                        空间对应上,C15处于第2行、第1列位置

                       于是返回:300

第四节拍:D15单元格
                       时间切片上,OFFSET返回值为{1000,2000;3000,4000}

                                        空间对应上,D15处于第2行、第2列位置

                       于是返回:4000


在数组班第一课中我讲过这样一句话:数组公式,它在时域上是一个遍历循环,在空间上是一个并行输出。

常规的函数,这些时间上的节拍是觉察不到的,而像OFFSET数组化的情况,时间上的节拍是以公式宿主单元格为分割的。这一点可能是因为EXCEL的工作表是二维的,因此直接把多个引用区域结成一片并没有多大意义的关系。        

OFFSET函数参数有五个,各个参数都可以进行数组话,上述例子只是将行偏、列偏进行了数组化处理,但本质是一样的。即得到一个返回值数组,其元素都是引用区域!

分时隔离的猜想更接近本质,我们要从本质入手才能更清晰地理解某些特性。


     

3.array数组化

根据参数驱动原理,当参数升级为对应的数组时就得到了数组用法。OFFSET函数的数组化返回值使得array级别的参数数组化成为可能。我们简要给一个例子。
               array数组化.png

这是一个将VLOOKUP函数的第二参数数组化的例子,大家可以自己去尝试更多的应用。

为了让大家更接受 引用区域数组 的概念,我们再演练一个数组运算中常用的十字交叉法。
               十字交叉运算.png


OFFSET数组化应用产生的数组返回值就是论坛中被称为的多维引用,在空间排布上这样称呼非常合适,它在时间上是分时隔离的,当然它的本质应该是一个数组,其元素是引用区域。只有这样在观念上改变过来,结合参数驱动的思想才能为数组公式打开一块新的天地。

[ 本帖最后由 胡剑0227 于 2010-12-14 11:21 编辑 ]

OFFSET函数数组化.rar

24.93 KB, 下载次数: 4555

评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-9-10 15:10 | 显示全部楼层
胡版又要出精品了,还没看,先下载,顶下先。

TA的精华主题

TA的得分主题

发表于 2010-9-10 15:42 | 显示全部楼层
胡版是个勤奋多产的热心版主,讲授浅显易懂,切合实际。是我们初学者的福分。

TA的精华主题

TA的得分主题

发表于 2010-9-10 15:51 | 显示全部楼层
多谢版主!!
能听懂一小部分,大部分因自身水平低下没有听懂

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-9-10 15:58 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
原帖由 MicrosoftEH 于 2010-9-10 15:51 发表
多谢版主!!
能听懂一小部分,大部分因自身水平低下没有听懂


有些东西自己去测试一下就会深刻很多。
对于上过数组特辑班的同学只要将上课讲到的 参数驱动,十字交叉,进行活用就可以了。根据这两个数组最基本的法则自动的去推演就是了。不仅理解OFFSET可以这样,开发其他函数的数组用法也是相同的。比如数组第三课(飞跃)就是把这些简单的原理用到了IF、CHOOSE函数而已。

TA的精华主题

TA的得分主题

发表于 2010-9-10 20:26 | 显示全部楼层
谢谢胡版,好好学习一下。

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-9-10 20:52 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
看这个帖子需要一定的数组公式基础,看的时候把握总体思路...

[ 本帖最后由 胡剑0227 于 2010-9-10 20:56 编辑 ]

TA的精华主题

TA的得分主题

发表于 2010-9-10 20:53 | 显示全部楼层
谢谢胡版~

刚刚接触数组,很是头疼

慢慢学吧。

TA的精华主题

TA的得分主题

发表于 2010-9-10 21:27 | 显示全部楼层
offset这个函数在上次课程中有同学用过,很有用,要仔细学学.
教师节学生没有礼物可送,反到是老师又给学生送了一份大礼.谢谢老师,

TA的精华主题

TA的得分主题

发表于 2010-9-10 21:49 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
嗯,OFFSET确实是个很好用的函数~~郑重学习一下!
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-26 21:31 , Processed in 0.042617 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表