16203 第四课时小结
先说一下这个课程的总体感受,首先,这个课程学的offset函数,真的很有用。
之前小土豆的那道练习题,要用数据有效性生成下拉菜单的,学了这个课时之后,发现原来用offset可以那么容易实现。
生成的辅助列有许多非法值,下拉菜单里面,如果直接使用数据有效性,会有许多非法值,这件事情一直困扰了几天,取得不是非法值的个数之后,就不知道怎么样将这些值转化成数组的形式,设置到下拉菜单。学过这个课程之后,终于知道要怎么实现这件事情了
最开始做作业的时候,其实已经用过offset函数实现,然后内心小鹿乱跳地以为自己很强大,看过这个课程中关于offset的介绍知道,才知道不是自己强大,而是offset函数比我想象的强大多了。
其次是INDIRECT函数,之前经常看到各种排序中用到它,然后我的理解就是,看不懂。
跑到群里面问,小土豆也很热心地解答了我的问题,通过看一些实例,以及解释,大致明白这个函数的意思,但是,通过木兰老师的讲解,才真的能够用这个函数了。
不得不说,也是个非常有用的函数。
下面开始这个课时的具体小结
第一节,MOD INT ROUNDUP
说实在的,这几个函数放在我面前,我会觉得很简单,没啥特别的
就是Mod是求余,INT是向下取整,Roundup是向下取整
生成各种序列,这也是件非常简单的事情,没啥特别的。但是,知道了之后的indirect函数知道,我再回来看这三个函数,就觉得特别有意思了。
通过各种数学组合,生成indirect函数的参数,可以实现各种功能。
再结合row和column函数,这个功效就非常大了。
大部分有规律的数字组合,都可以通过使用这些函数生成。
第二节之offset
这真的是一个很神奇的函数,尤其是可以直接取一个大数组,这件事情让我觉得有点小high。
不知道为什么,现在只要扯上数组之后,我就会觉得有点high。
用offset来构造下拉菜单,可以把一个序列排在后面的空值,非法值,通过用counta统计一下有效值的数目,把后面的值排除掉。
一个offset和mod一起的一个应用,职工排班表
一周七天,一共有七个人排班,只需要将七个人第一天排好,然后之后轮班即可,这时候可以用offset进行移位,用mod算出应该怎么按顺序轮班
星期一 | 星期二 | 星期三 | 星期四 | 星期五 | 星期六 | 星期日 | |
第1周 | 周一 | 王二 | 吴三 | 郑四 | 丁五 | 胡六 | 唐日 |
第2周 | 王二 | 吴三 | 郑四 | 丁五 | 胡六 | 唐日 | 周一 |
第3周 | 吴三 | 郑四 | 丁五 | 胡六 | 唐日 | 周一 | 王二 |
第4周 | 郑四 | 丁五 | 胡六 | 唐日 | 周一 | 王二 | 吴三 |
第5周 | 丁五 | 胡六 | 唐日 | 周一 | 王二 | 吴三 | 郑四 |
第6周 | 胡六 | 唐日 | 周一 | 王二 | 吴三 | 郑四 | 丁五 |
第7周 | 唐日 | 周一 | 王二 | 吴三 | 郑四 | 丁五 | 胡六 |
第8周 | 周一 | 王二 | 吴三 | 郑四 | 丁五 | 胡六 | 唐日 |
第9周 | 王二 | 吴三 | 郑四 | 丁五 | 胡六 | 唐日 | 周一 |
效果图如上
放在A1的位置开始,公式是:=OFFSET($B$2,0,MOD(ROW()-MOD((4-COLUMN()),7),7))
不过以上只是偏移的一个应用,offset让我激动的是它的偏移数组,但是目前,除了那个下拉菜单是一个比较典型的应用以外,想不到其它的了,就不另外在这里写了。
第二节之indirect
关于这个函数,让我知道了怎么用之后,真的让人觉得很激动,最近扯上数组就比较激动,大家不要见怪。
比较常见到它,是在排序的时候,用来计算应该显示哪个值。
之前就是在看排序的时候,看不是很懂,然后一个头两个大,很郁闷。现在,对排序的公式理解起来,就轻松很多了。
比如说说源数据data={1;2;1;2;”a”;”a”;”b”}
生成data个数的序列Rw =ROW(INDIRECT(“1:”&ROWS(data)))
生成的序列
newdata=LOOKUP(SMALL(IF(MATCH(data,data,)=rw,rw),ROW(INDIRECT("1:"&SUM(N(MATCH(data,data,)=rw))))),rw,data)
然后用数组函数,便可以生成一个排好序的序列了。
Indirect会提到一列数组,然后用row函数可以取得1到N的数字序列
最后生成的序列里,用match函数不是重复的项,然后如果和数字序列相同,则会返回这个数字。然后用small进行排序,再用lookup函数可以查找到各个序列然后显示出来。
之前应该indirect函数看不是很懂,这个函数一直都看不明白,但是,自从木兰老师的讲解之后,看到这个公式,就能大概看出来用意,即使不知道这是干什么的,也能够知道是怎么实现这件事情,做了一件什么意思。
第三节,实例运用
第三节主要是讲之前这些函数的应用,真的,整体整理一下,也对这个课时所介绍的内容有一个整体的认识,课程要求我们将match,vlookup,offset,indirect函数的实例应用整理一下。这个会在成果物展示里面体现出来。这里就不详述啦
总而言之这个课时,是非常有用的一个课时,而且也比之前的课程都更加难了,课程已经到了最后时刻了,下一个课时的内容,真的让人有点紧张哎。
最后附上excelhome的链接:club.excelhome.net