|
|
楼主 |
发表于 2009-12-16 14:28
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 胡剑0227 于 2011-8-19 15:46 编辑
玉不琢不成器
“玉不琢不成器”，这里是指复杂完美的公式不是一蹴而就的，而是不断修改、雕琢出来的。
初学函数公式时我也感叹：那些函数公式的高手真是太了不起了，这么长的公式可以直接写出来，佩服佩服！但现在我想，除了个别已经成型的函数组合应用外，碰到新问题一般都是一步一步雕琢出来的。这个过程需要活跃的思维、熟练的公式编辑技能，是公式综合编辑能力的体现。
纯语言描述非常空洞，我们就那一个具体的实例吧，所要完成的任务如下图
所示：
具体讲就是要把左图转换成三列，第一列为X值，第二列为Y值，第三列X、Y值所对应的数据。要求在H2单元格写入公式，然后向左向下拖拉单元格公式进行填充完成三列。
写公式，尤其只是为了更好更快的解决问题，随时定义名称是一个很好的习惯，增加可读性便于维护。下面我们来定义几个名称，顺便让大家接触一下名称，不至于后文中提高时过于突兀。
X:引用地址为$B$1:$F$1; Y:引用地址为$A$2:$A$10; DATA:引用地址为$B$2:$F$10；
具体过程如下图所示：
当然，定位名称的方式有多种，这只是其中最通用的一种。如果是03版的，原理也是一样的，菜单入口为“插入”--》“名称”---》“定义命名”。如此定义名称后，就可以直接使用X、Y、DATA来直接指代那些单元格了，编辑公式时更是可以利用F3功能键来快捷准确的录入了。顺便补一个利用F3编辑公式的贴图吧，呵呵。
图中停顿处是按了 F3 ，调出了“粘贴名称”对话框
好了，回到正题。我们的任务是要完成三列，那么先分解一下，先写第一列
。
步骤1 在H2单元格：=INDEX(X,,ROW()-1)，值为A，满足要求。然后往下拖拉复制，这个时候马上会发现，H3单元格的值为B，不符合。这个时候很容易想到INDEX函数的第三参数必须重复9次（就是Y数组的个数，ROWS(Y））
。
步骤2 修改，H2单元格：=INDEX(X,,(ROW()-1)/ROWS(Y))，这个时候返回显然是错误值，此时马上会发现第三参数小于1（修改时直接添加1），并且也马上会想到不是整数（修改时外套INT)，于是再修改。
步骤3 修改，H2单元格：=INDEX(X,,INT(1+(ROW()-1)/ROWS(Y)))，然后再往下拖拉公式，开始都正常，但是在H10单元格时返回值变成了“B”而不是预想的“A”。
此时，仔细分析，第三参数INT(1+(ROW()-1)/ROWS(Y))=int(1+(10-1)/9)=2,这个时候就是希望在ROW（）=10时能等于1，因此也不难想到再修改（将ROW()替换为ROW()-0.5)。
步骤4 修改，H2单元格公式：=INDEX(X,,INT(1+(ROW()-1-0.5)/ROWS(Y)))
整个编辑过程可以如下图所示，当然啦，实际编辑时没有那么快速的，会有点停顿思考的，呵呵
其实，大家也看到这里的关键就是抠出INDEX的第三参数，就是表格中DATA对于那个的列。
这样第一列就算完成了。
第二列，大家可以根据这个思路尝试一步一步雕琢，预祝大家成功，呵呵。
最后I2的公式为：
=INDEX(Y,IF(MOD(ROW()-1,ROWS(Y)),MOD(ROW()-1,ROWS(Y)),ROWS(Y)))
关键1：他是循环的，于是不难想到用MOD，由于MOD会 出现0，我们把这个出现0时换成ROWS(Y)就行了。
其实，这里的关键是抠出INDEX的第二个参数，就是表格中DATA对应的那个行。
第三列，行列都扣出来了，第三列就最简单了。
最后J2:=INDEX(DATA,IF(MOD(ROW()-1,ROWS(Y)),MOD(ROW()-1,ROWS(Y)),ROWS(Y)),INT(1+(ROW()-1-0.5)/ROWS(Y)))
其中INDEX的第二参数使用了第二列公式中INDEX的第二参数，第三参数使用了第一列公式中INDEX的第三参数。
完成了吗？也许有同学会问，不是最后要合成一个公式吗？那还不简单，用IF判断所在列，然后填写对应的公式就可以了。这里我再秀一下IF函数，顺便大家再熟悉一下名称定义，引用地址是公式，呵呵。
将H2中的公式定义为“X公式”。
步骤1 选中H2单元格，进入编辑状态，复制整个公式。
步骤2 开启“定义名称”对话框，或者直接进入“名称管理器”对话框，新建名称，名称名称，如“X公式”，然后在“引用地址”文本框中粘贴刚才H2中的公式。
按以上介绍方法依次定义“Y公式”、“DATA公式”。
然后在H2单元格中输入=IF(COLUMN()=8,X公式,IF(COLUMN()=9,Y公式,DATA公式))
看图
============================================================================================
大家不要太看重这个例子本身，这里主要讲函数公式不是很一蹴而就的，是不断修改雕琢的。当然里面有些东西你没怎么接触的话正好熟悉熟悉（比如定义名称什么），这倒是很有用的，呵呵。
如果您有函数公式方面的任何想法、疑问那么请您直接跟帖，如果有小巧的例子以更透析的说明问题，那么还可以Email至sword0227@189.cn，胡剑需要您的帮助。邮件不一定回复，但胡剑感谢您的支持！永久有效
[ 本帖最后由 胡剑0227 于 2011-1-11 12:15 编辑 ]
|
|
楼主: 胡剑0227
[复制链接]
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-12-15 19:23
学习一下，支持楼主
