|
本帖最后由 胡剑0227 于 2011-11-20 18:29 编辑
ROW、COLUMN,这几乎是最基本的函数了,不过我发现平时我们只看到了它的常态的一面,它有鲜为人知的特性,今天我们就来解开这层面纱。首先,我们做一个游戏,仿【移动火柴】游戏的,如下图所示,请移动一根火柴(修改一个字符),使以下等式成立(行4区域的多单元格数组公式返回行2对应区域的结果)。
其中行4的多单元格数组公式如下:
- =SUM(INDEX(ROW($1:$10),N(IF(1,COLUMN())),0))+RAND()*0
复制代码
如果你找到并成功修改了那根火柴,那么恭喜你!因为并不是每个人都能轻松地解出这个火柴题的,那么我想问一下,你能解释一下这个公式吗?我想对于大部分同学来说还是会咋舌的,当然有些可能是瞠目的,呵呵。看似聊斋事件的现象起初是hustnzj发现的,我感觉是触碰到了一些新的东西,于是做了一些大胆地猜想算是把他发现的问题给解释了。这个游戏是根据猜想进行实践的一个产品,算是进一步巩固了那个猜想。
下面我给大家看两个小东西,也许能触发大家的灵感,一下子明白了。
【小东西1】
RAND,在这里RAND只是一个摆设,在这个特定的公式中它的功能被INDEX取代了,但是在利用ROW、COLUMN新特性的那一类函数公式中RAND有特定的作用。(这一点随着后文的深入会慢慢清晰的)
看一个图吧:
在上图中我们发现使用F9对公式=if(row(1:8),rand())进行求值时返回8个相同的数值,而以多单元格数组公式进行录入时每个单元格的数值却是不同的,因此每个单元格中的值不是在同一个节拍生成的。在随后的实例中我们会发现它具有一个极其特殊的功能,强迫数组公式在每个单元格都进行重算!
【小东西2】
ROW,COLUMN,它们隐藏得很深,也许只有高频摄像机才能真正让它们显影,先抓拍几个片段,其中SUM函数是关键,让狐狸尾巴露了出来,看图。
从上图,结合一点想象力,我们会发现在具体的公式中ROW()变型为了ROW(n:m),其中n是数组公式所在区域的左上单元格的行号,而m表示公式所在区域的底下单元格的行号。COLUMN可谓兄妹同命了,将行的概念置换成列就行了。为了更加清晰的了解其中的情况我们打算进行高频抓拍,就是利用RAND函数让多单元格数组公式的所在区域的每个单元格都进行重新计算,如下图所示。
通过RAND来驱动高速抓拍,我们发现我们的猜想是靠谱的,RAND对发现ROW、COLUMN的特性真是功不可没。另外RAND的出手也让RAND自身的特性也充分体现了出来,这叫什么呢,“与之为取”吧,呵呵。这下,移动火柴 后的公式原理能解释了吧,这里我还真不好替为解释,一解释就漏风了,呵呵。到时留个附件,在附件中再解释一下吧,呵呵。好,我们再换包火柴,请看下面的公式,原理是一样的吧:
第5行的数组公式如下
- =SUM(LOOKUP(COLUMN(),COLUMN(A1:H1)))+RAND()*0
复制代码
在这里RAND再一次的证明了自身的价值,不过当顶楼第一个图公式那样,ROW、COLUMN落在INDEX函数这个火柴盒里面时是不需要RAND的,因为INDEX自身同样具有让单元格中的公式重新计算的功能。(上文RAND的功能被INDEX替代了在这里落)。如果你已经成功修改了那根火柴,那么你可以删除RAND函数试试,你会发现确实如此,呵呵。
再帖一下给力花絮吧,呵呵,这样能看得更加清晰:
花絮1,ROWS来拍摄:
花絮2,TRANSPOSE来拍摄:
我不知道这个发现会有多大的作用,但我觉得值得遐想...但要从技术上讲,原始顶楼第一个图(出错及出错的形态)蕴含的知识点更是深,里面涉及了太多的东西,先还是当它出错混乱吧,呵呵。
该贴已经同步到 胡剑0227的微博
|
评分
-
4
查看全部评分
-
|