|
|
楼主 |
发表于 2009-12-24 22:03
|
显示全部楼层
本帖最后由 胡剑0227 于 2012-8-21 11:43 编辑
栖息地
“谁人不识君”，这个特性可能算是“百姓日用而不知”之类，掌握这一技巧可以解决很多传递的问题，扩大很多基本功能的作用范围。今天要介绍的“栖息地、预处理”的功能，依然会让你有所收获。
“栖息地”听上去有点自然保护区的味道。是的，“名称”是宏表函数的“栖息地”，宏表函数是早期版本的产物，但非常好用，能弥补常规工作表函数的很多不足，因此在新版本中都进行了保留。
提取颜色
比如，有时候我们对单元格的颜色比较感兴趣，心血来潮时还想使用颜色进行排序。当然在07版中这一切都没有问题啦，群众的需求是要考虑的，所以在07版中可以直接对颜色排序、对颜色筛选。但考虑到有部分战友还战斗在03版这块阵地上，所以可能就需要使用宏表函数GET.CELL函数来助助阵。
插图：
上图中通过辅助列，使用名称“纸色”提取了对应的颜色数值。这样就提取了颜色的信息量，以后就可以按照这个辅助列进行排序、筛选，迂回方式却也达到了目的。感叹，还是用07好呀，呵呵。
到这里大家可能还体会不到名称的作用在哪里？当你失去的时候才知道要好好珍惜。我们看一下，如果不用名称：
插图
上图，在编辑完公式“=get.cell(38,$E2)”按回车后，Excel立即报错，说什么什么无效？根据归一化查错技巧，我们发现不同之处就在于一个使用了“名称”进行了封装，一个没有，所以名称的作用就很明显了。
GET.CELL是一个宏表函数，在工作表中不能直接使用，而在“名称”中却可以，估计就涉及到“名称”的出生了，前面猜想过“名称”可能直属于工作簿的，所以它可以直接调用工作簿的资源。
这里，GET.CELL函数的第一参数 “38” 指定了该函数的功能，即提取填充色，第二参数引用方式上有点小技巧，使用了行变列不变的方式。如果我告诉大家 提取 字体颜色 只要将 38 换成 24 就可以了，那么请大家尝试完成对 笔色 的提取。
对应提取颜色，38，24，也许今天大家能记住，但个人感觉还是比较容易忘的，我分享一个不太容易忘得技巧，大家听了一定会觉得好笑，呵呵。3*8=24，都是颜色，所以我觉得38，24两个数值就不太会忘了。
那哪个对应填充色，哪个又对应字体色呢？天对地，雨对风，大路对长空。平面、线条、填充色、字体色，这四个项目让我配对，我会 线条 配 字体色，平面 配 填充色，感觉中平面 是 大于线条的，所以 填充色 大于 字体色。讲得很复杂，大家就感觉一下吧 字体 和 单元格背景 哪个比较纤细哪个比较壮实吧，应该不难感觉 背景 大于 字体吧，所以38是填充色，24是字体色。
从公式文本到结果
在论坛上我也经常看到这样的需求：一个单元格中是一个文本型的公式，要求在另一个单元格中返回该公式的运算值，如下图所示。
这类问题只要借助宏表函数EVALUATE就比较容易解决，难点在于这个函数名比较难拼：）。求值么，所以这个词的主干是 VALUE，我想第一个 E 是使能（ENABLE)的感觉，词尾ATE，应该是动词变形后缀吧。当拼错时我就用这种方式把他给抠出来，希望必要时也能给你帮上忙，呵呵。
同样的，EVALUATE虽好，但不能直接使用，依然要定义一个名称，如下图所示。
当然以上应该是“EVALUATE”最简单的应用了，把握一点：其函数的参数是一个字符串的公式文本，就是说这里可以发挥想象力，充分发挥 & 和各种文本处理函数的功能，只要最后的结果是一个合法的公式字符串就可以了。下面精彩一下，呵呵。
累加单元格中的数字
比如要达到上图的效果，C12="1,2,3",要求汇总数字？当然用数组公式是可以的，这里讲一个使用EVALUATE以比较巧妙的方式来实现。先想一个思路，当然这靠积累的，目标公式设定为“=SUM({1,2,3})”，那么分段切片，把C12的值揉入进来，具体就是：="sum({" & $C$12 & "})"，最后将这个公式作为EVALUATE的参数即可。
这里要灵活应用EVALUATE,关键就是使用各种方式来构造“字符串公式文本”这个参数。
另外，如果把公式设定为 =1+2+3，那么使用SUBSTITUTE函数把C12单元格中的“，”换成“+”也行，大家可以自己尝试。
串接常量数组
比如定义数组1="{1;2;3}",数组2="{4;5;6}",求数组3，将数组1和数组2串接起来。目标公式：
={1;2;3;4;5;6},于是可以定义名称数组串接：=EVALUATE(SUBSTITUTE(数组1&数组2,"}{",";"))，我觉得这个用法实际意义可能不是很大，但还是能让大家回味一下想象力的魅力，呵呵。（这个是当时写一个数组串接时一位网友提供的，呵呵）
引用公式
我们的需求总是多种多样的，上面刚讲已知“字符串公式文本”想得到公式值，但有时我们有想显示目标单元格的具体公式。怎么办?依然是GET.CELL，把参数值设为6就可以了。如下图所示。
提取工作表名
另一个经常会问到的问题是如何提取所有工作表的名称。这个问题有好几个成熟的名称，尽管如此我也常常要思索一下才能写出来，定义名称 shts:=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1),1)+1,100),这里的MID第三参数100一般情况下够用，如果工作表名称实在长那么可以调整为更大的数。
不过，对于这个名称忘了也不要紧，只要我们能记住GET.WORKBOOK(1),就行了。这个应该还好记的。最后根据GET.WORKBOOK(1)在进行MID处理即可。如下图所示。
上图
名称就讲到这里吧，关键大家要多用。有关宏表函数的更多参考请下载附件。
栖息地预处理.rar
(7.13 KB, 下载次数: 302)
有关宏表函数的更多内容请搜索相关帖子，这里给个现成的，很不错的，czzqb版主给大家的礼物http://club.excelhome.net/thread-214231-1-1.html
如果您有函数公式方面的任何想法、疑问那么请您直接跟帖，如果有小巧的例子以更透析的说明问题，那么还可以Email至sword0227@189.cn，胡剑需要您的帮助。邮件不一定回复，但胡剑感谢您的支持！永久有效
[ 本帖最后由 胡剑0227 于 2011-1-11 12:22 编辑 ]
|
|
楼主: 胡剑0227
[复制链接]
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-12-24 20:32
