1234

ExcelHome技术论坛

用户名  找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

RAND函数让ROW和COLUMN神秘亮相

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2011-11-20 17:55 | 显示全部楼层 |阅读模式
本帖最后由 胡剑0227 于 2011-11-20 18:29 编辑

          ROW、COLUMN,这几乎是最基本的函数了,不过我发现平时我们只看到了它的常态的一面,它有鲜为人知的特性,今天我们就来解开这层面纱。首先,我们做一个游戏,仿【移动火柴】游戏的,如下图所示,请移动一根火柴(修改一个字符),使以下等式成立(行4区域的多单元格数组公式返回行2对应区域的结果)。
                                                            
移动一根火柴.jpg

        其中行4的多单元格数组公式如下:
  1. =SUM(INDEX(ROW($1:$10),N(IF(1,COLUMN())),0))+RAND()*0
复制代码

         如果你找到并成功修改了那根火柴,那么恭喜你!因为并不是每个人都能轻松地解出这个火柴题的,那么我想问一下,你能解释一下这个公式吗?我想对于大部分同学来说还是会咋舌的,当然有些可能是瞠目的,呵呵。看似聊斋事件的现象起初是hustnzj发现的,我感觉是触碰到了一些新的东西,于是做了一些大胆地猜想算是把他发现的问题给解释了。这个游戏是根据猜想进行实践的一个产品,算是进一步巩固了那个猜想。

          下面我给大家看两个小东西,也许能触发大家的灵感,一下子明白了。
【小东西1】

          RAND,在这里RAND只是一个摆设,在这个特定的公式中它的功能被INDEX取代了,但是在利用ROW、COLUMN新特性的那一类函数公式中RAND有特定的作用。(这一点随着后文的深入会慢慢清晰的)

看一个图吧:

RAND强迫在每一个单元格中进行重新计算.gif

         在上图中我们发现使用F9对公式=if(row(1:8),rand())进行求值时返回8个相同的数值,而以多单元格数组公式进行录入时每个单元格的数值却是不同的,因此每个单元格中的值不是在同一个节拍生成的。在随后的实例中我们会发现它具有一个极其特殊的功能,强迫数组公式在每个单元格都进行重算!

【小东西2】

         ROW,COLUMN,它们隐藏得很深,也许只有高频摄像机才能真正让它们显影,先抓拍几个片段,其中SUM函数是关键,让狐狸尾巴露了出来,看图。

小东西2.jpg

          从上图,结合一点想象力,我们会发现在具体的公式中ROW()变型为了ROW(n:m),其中n是数组公式所在区域的左上单元格的行号,而m表示公式所在区域的底下单元格的行号。COLUMN可谓兄妹同命了,将行的概念置换成列就行了。为了更加清晰的了解其中的情况我们打算进行高频抓拍,就是利用RAND函数让多单元格数组公式的所在区域的每个单元格都进行重新计算,如下图所示。

小东西2的高速抓拍.jpg


         通过RAND来驱动高速抓拍,我们发现我们的猜想是靠谱的,RAND对发现ROW、COLUMN的特性真是功不可没。另外RAND的出手也让RAND自身的特性也充分体现了出来,这叫什么呢,“与之为取”吧,呵呵。这下,移动火柴 后的公式原理能解释了吧,这里我还真不好替为解释,一解释就漏风了,呵呵。到时留个附件,在附件中再解释一下吧,呵呵。好,我们再换包火柴,请看下面的公式,原理是一样的吧:

换一包火柴.jpg

第5行的数组公式如下



  1. =SUM(LOOKUP(COLUMN(),COLUMN(A1:H1)))+RAND()*0
复制代码


          在这里RAND再一次的证明了自身的价值,不过当顶楼第一个图公式那样,ROW、COLUMN落在INDEX函数这个火柴盒里面时是不需要RAND的,因为INDEX自身同样具有让单元格中的公式重新计算的功能。(上文RAND的功能被INDEX替代了在这里落)。如果你已经成功修改了那根火柴,那么你可以删除RAND函数试试,你会发现确实如此,呵呵。

          再帖一下给力花絮吧,呵呵,这样能看得更加清晰:
          花絮1,ROWS来拍摄:
        
给力花絮1.jpg
           花絮2,TRANSPOSE来拍摄:

给力花絮2.jpg

          我不知道这个发现会有多大的作用,但我觉得值得遐想...但要从技术上讲,原始顶楼第一个图(出错及出错的形态)蕴含的知识点更是深,里面涉及了太多的东西,先还是当它出错混乱吧,呵呵。



该贴已经同步到 胡剑0227的微博

移动一根火柴.rar

5.06 KB, 下载次数: 640

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-11-21 09:30 | 显示全部楼层
太高深了  先下载  有空再学习  辛苦了版主。{:soso_e104:}

TA的精华主题

TA的得分主题

发表于 2011-11-21 09:12 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-11-22 14:32 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-11-20 18:42 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 bluexuemei 于 2011-11-20 18:45 编辑
  1. =SUM(INDEX(ROW(1:10),N(IF(1,COLUMN())),1))+RAND()*0
复制代码
去掉后面的RAND()*0,结果一样!

点评

哇,这个发现好,估计是INDEX、RAND同时导致两次重算造成的,如果用NOW没有触发重算,那么就是INDEX一次重算了,因此刷新快。使用NOW,重算只是由INDEX生成的,没有RAND的功能性  发表于 2011-11-22 08:43
不如改成now()*0,刷新更快.  发表于 2011-11-21 22:47

TA的精华主题

TA的得分主题

发表于 2011-11-20 17:56 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-11-20 18:10 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
占位学习,胡版又有大作!

TA的精华主题

TA的得分主题

发表于 2011-11-20 18:10 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-11-20 18:46 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-11-20 18:47 | 显示全部楼层
bluexuemei 发表于 2011-11-20 18:42
去掉后面的RAND()*0,结果一样!

呵呵,看下去就明白用意了,呵呵
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

1234

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

GMT+8, 2025-2-20 16:11 , Processed in 0.030715 second(s), 16 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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