读书频道 > 系统 > 其他综合 > 让Excel飞!职场Office效率提升秘籍
2.5 引用!引用!
2012-08-11 16:13:27     我来说两句 
收藏    我要投稿   
掌握Excel精髓,让我们随心所欲 借助Access,让Excel飞! 这是一本关于如何有效利用Excel和Access提高职场效率的书。书中凝聚了作者多年来关于如何利用Excel和Access提升个人生产力的独特思考。本书共分两部分,...  立即去当当网订购

Excel中的相对引用、绝对引用和混合引用是Excel中最终要的概念,必须要透彻地理解。

让我们先从一个例子说起,如图2-22所示的一个Excel表格,我们需要在D4:D13单元格中计算出每种产品的销售金额,即销售数量×单价。我们需要这样做:首先在D4单元格中输入公式“=B4*C4”,得到第一个产品“A”的销售金额(D4单元格中的数值)。
计算完D4单元格中A产品的销售金额后,其他单元格中的公式怎么输入呢?当然不需要在D列的其他单元格中分别输入=B5*C5、=B6*C6……那样太低估了Excel的能力!

图2-22
我们只需把光标移动到D4单元格的右下角,直到光标变成黑色的十字形状,向下拖动鼠标,一直拖动鼠标到D13单元格(或干脆在鼠标光标变成黑色十字形状时双击鼠标),Excel会自动把D4单元格的公式复制到D5:D13单元格中。
且慢!Excel真的把D4单元格中的公式“原封不动”地复制到了D5:D13单元格中了吗?
Excel并非把D4单元格中的公式“原封不动”复制到D5:D13单元格中,而是把D4单元格中公式所确定的“计算逻辑”复制到了D5:D13。我们发现,D5单元格中的公式为“=B5*C5”;D6单元格中的公式为“=B6*C6”;D7单元格中的公式为“=B7*C7”……
这个公式所确定的“计算逻辑”是:D4:D13每个单元格中的公式都是与其所处单元格同一行左侧的两个单元格的乘积,而这正是我们所需要的效果。
如果我们想查看一下工作表中公式而不是计算出来的数值,可以选择“公式→公式审核→显示公式”命令,把工作表设置成“显示公式”模式(对应的快捷键是“CTRL+` ”),设置完成后的显示效果如图2-23所示。
Excel能够自动对公式进行调整,这个能力看起来够酷!但是,我们不免会产生疑问:Excel是真的这么聪明,能够了解我们的“计算逻辑”;还是仅仅是巧合而已,这里面还存在我们尚未了解的规律?因此,我们必须做进一步的研究。
下面我们来看一看E4单元格里关于佣金数额的公式。在E4单元格中输入公式“=D4*B1”(B1单元格里存储的是提取佣金的比率),公式输入完成后,在E4单元格里可以看到佣金数额的计算结果(D4单元格中的数值),如图2-24所示。




图2-23      

                          图2-24
把光标移动到E4单元格的右下角,直到光标变成黑色的十字形状,向下拖动鼠标直到E13单元格,Excel会自动把E4单元格的公式复制到E5:E13单元格中。
和前面讲的“销售金额”公式情况一样,Excel把E4单元格中公式所决定的“计算逻辑”复制到了E5:E13单元格中。
这个公式所确定的“计算逻辑”解释如下:以公式所在的单元格为参考点,将向左移动3个单元格,向上移动3个单元格所对应的那个单元格中的数值,乘以公式单元格左边第一个单元格中的数值的乘积,如图2-25所示。
现在的问题是:这个逻辑只对E4单元格适用,如果E5:E13单元格中的公式也使用这个逻辑的话,那就大错特错了!
看来Excel不能够猜出我们的心思。事实上,我们必须明确地告诉Excel:E4单元格中的公式在向下复制的时候,提取佣金的比例必须一直使用B1单元格中的数值!
为了告诉Excel在公式复制时的调整逻辑,我们必须把E4单元格中的公式修改成“=D4*$B$1”,然后把光标移动到E4单元格的右下角,直到光标变成黑色的十字形状,向下拖动鼠标直到E13单元格,如此这般,Excel会就可以把E4单元格中“公式所确定的计算逻辑”按照我们的期望正确地复制到E5:E13单元格中了。
为什么把E4单元格中的公式“=D4*B1”修改成“=D4*$B$1”就能在公式复制时正确地执行我们的“计算逻辑”呢?原来,修改后的公式“=D4*$B$1”中所引用的单元格地址$B$1中的行号(“B”)和列标(“1”)前的“$”符号的作用类似一颗钉子,把对B1单元格的引用位置“牢牢”地固定住,表示$B$1所引用的单元格地址在公式复制的时候不再进行自动调整,即:无论公式复制到哪里,永远引用B1单元格中的数值。
修改后的公式自动复制的效果如图2-26所示,E4单元格中的公式为:=D4*$B$1,当E4单元格中的公式复制到E13单元格中时,公式调整为=D13*$B$1,结果正确,如图2-26所示。







图2-25 
                                图2-26
现在,我们介绍一下Excel中单元格地址的几种引用形式区别。
我们把类似$B$1形式的单元格地址引用,即在单元格地址的“行号”和“列标”前面都有一个$符号的引用,称做绝对引用。如果公式中含有这种形式的地址引用,那么无论公式复制到哪里,这种引用形式的单元格地址都不会自动调整,就像被钉子固定住了一样。
我们把类似B1形式的单元格地址引用,称做相对引用。如果公式中含有这种形式的单元格地址引用,那么公式中的该类型的地址引用会随着公式的复制而自动调整并和使用它的公式一直保持固定的“相对位置”,像影子一样跟随着引用了它的公式,无论公式被复制到了工作表的哪个位置。
有的时候,我们需要在公式复制时只有“行号”或者“列标”之一保持不变,这时,只需要在希望保持不变的单元格地址的“行号”或者“列标”之前加上一个$符号即可,即类似B$1,$B1形式的引用。我们把类似B$1,$B1形式的引用称做混合引用。如果公式中含有这种形式的单元格地址引用,那么该引用地址只会在行或列的“一个方向上追随”引用了它的公式,而在另一个方向上不随着引用了它的公式位置的变动而变动。
Excel公式中单元格地址的引用类型分为三种:
—    类似$B$1形式的引用称做绝对引用;
—    类似B1形式的引用称做相对引用;
—    类似$B1或者B$1形式的引用称做混合引用。
如果我们把公式比喻为“母亲”,把公式中引用的单元格地址称为“孩子”,那么:
(1)如果母亲公式中引用的单元格地址为绝对引用(类似$B$1形式的引用)时,那么,当公式母亲被复制到了其他单元格,孩子就像被绑住了双腿,一直停留在原来的为位置,动弹不得,不会跟随公式母亲一起移动,我们可以称之为“坚定不移”。
(2)如果母亲公式中引用的单元格地址为相对引用(类似B1形式的引用),那么,我们把母亲公式复制到其他单元格时,孩子会像母亲的影子一样,跟随母亲一起移动并一直和母亲保持固定的“相对位置”,可以说是“如影相随”。
(3)最后一种情况,如果母亲公式中引用的单元格地址为混合引用(类似$B1或者B$1形式的引用),那么,当公式母亲复制到其他单元格时,孩子只能在一个方向上(横向或列向)跟随母亲移动,而另一个方向上一直停留在原来的位置,相当于只在一个方向上没有了自由,而在另一个方向上保持自由!我们可以称之为“半身不随”。
有趣的是:那个限制孩子的自由,能够让孩子老老实实听话的符号竟然是$(美元符号)!真不知道Excel开发者当初是怎么想的!
我们在设计Excel表格或者模型时,在开始设计公式的时候就要根据公式将来复制的方向(横向复制、纵向复制还是两者都有),精确地调整好公式中的单元格地址的引用形式,这可以为我们后续的工作带来很大便利,并且能够避免一些可能出现的错误。
Excel小技巧:
公式中单元格地址引用形式的快速切换方法:把光标放置在单元格地址中间,连续按F4键,单元格地址会在相对引用、绝对引用、混合引用之间快速切换,例如:B1→$B$1→B$1→$B1→B1。
 
点击复制链接 与好友分享!回本站首页
分享到: 更多
您对本文章有什么意见或着疑问吗?请到论坛讨论您的关注和建议是我们前行的参考和动力  
上一篇:2.4.3 DATEDIF函数
下一篇:2.6 识别活动单元格
相关文章
图文推荐
2.7.12 使用仿真器查
2.7.11 栈和寄存器组
2.7.8 出栈
2.7.7 压栈
排行
热门
文章
下载
读书

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做最好的IT技术学习网站