有时候,我们需要将公式复制到其他单元格中。这时,如事先没有掌握相关知识,就会浪费一些不必要的时间。例如,下图是不同地区的分公司的销售额一览表,其中,处理“结构比率”一栏时,请输入正确的公式。
各分公司的“结构比率”,是将各个分公司销售额除以全公司的销售额计算得出的。因此,首先请在 C2中输入“=B2/B11”。在单元格 C2输入=B2/B11
※选择单元格 C2➛输入等号(=)➛点击单元格 B2➛输入斜线(/)➛点击单元格 B11
详细的内容我会在第七章介绍,这里只稍微提一下。在“设置单元格格式”中,可以预先将 C 列的表示形式设为百分比,那么就可以知道北海道分公司在整个公司的销售额中所占的比例。
接下来,同样在 C3~C11中输入计算占比的公式,就可以得出所有分公司的销售额在整体中所占的比例。当然,如果你在单元格中逐个输入相同的公式,做完的时候太阳都下山了。而且如果你这么做……还会出现这样的乱码:表格中显示“#DIV、0!”
单元格中出现“#DIV/0!”,似乎计算进行得并不顺利。
那么到底出了什么问题?我们选中单元格 C3,按下 F2 键。
【 F2 键的功能】
使活动单元格处于可编辑状态。
选中的活动单元格内容引用自其他单元格时,用有色框线显示被引用的单元格。
于是,所选单元格的引用单元格如下图。
选中单元格 C3,按下 F2 键
除数引用了正确的单元格(B3),被除数本应引用 B11中的数值,但却引用了单元格 B12的数值。就是说,指定被除数时出现了偏差。
为什么会发生这种情况?
原来,将最初输入的公式向下复制的同时,所引用的单元格也一同被“拖拽”向下移动。
一开始在 C2中输入“=B2/B11”,其实是引用了 B2和 B11数值。这是因为从单元格 C2的位置关系来看,系统将 B2和 B11这两个单元格分别当作为“用于计算的分子与分母的单元格”。从含有公式的单元格 C2来看,与单元格 B2和 B11的位置关系如下:
B2➛自己所在处向左1格的单元格
B11➛自己所在处向左1格、向下9格所到达的单元格
而且,这种位置关系在被复制的单元格里也是同样。直接拖动复制,向下1格的 C3如先前画面所示,会自动变为“=B3/B12”。
作为除数的 B3,在含有公式的单元格 C3看来,就是“向左1格的单元格”,选中时会保持这种识别也没有问题。但是,关于被除数的话,在 C3看来引用的是“向左1格、向下9格的单元格”,也就是 B12。而 B12是一个空白单元格,那么这个算式就是 B3数值除以一个空白单元格数值……换句话说,被除数其实是0。
数学中最基本的常识就是被除数不能为0。因此,单元格 C3最终表示的结果就会是“#DIV/0!”这样的乱码。
像这样,在复制包含公式的单元格作为引用时,结果有所偏差的状态叫作“相对引用”。利用“F4”与“$”高效运用“绝对引用”那么,应该怎么操作才能在向下拖拽复制公式的时候保持被除数固定不变呢?答案就是“绝对引用”。请试着用以下方式输入公式。
➊ 在单元格 C2输入公式=B2/B11。
➋ 点击单元格 B11,按 F4 。可以看到,以 B11为引用单元格后,出现了符号$。
➌ 从单元格 B2开始拖拽至第11行,这次并没有出现错误,能够正常计算。
如果不知道这个方法,就需要手动输入每一个被除数,这样就会浪费很多时间。
顺带一提,指定引用单元格后,多次按下 F4 键,$符号的所在位置也会发生变化。
$A$1➛固定列和行
A$1➛固定行
$A1➛固定列
A1➛不固定位置
即使知道“$符号为绝对引用”,还是有很多人不清楚按 F4可以输入$这一操作方法。请大家一定要善用F4 键。
如需纵向、横向复制含有公式的单元格时,一般会有两种需求:只固定行、只固定列。这时,可用上述方法切换。
无需记住错误值的种类与意义
除了前文中提到的“#DIV/0!”,还有“#NAME?”、“#N/A”等在单元格里输入函数后出现的各种难以理解的内容。这些是“错误值”,表示你当前输入的函数中出现了问题或偏差。
错误值的种类有许多,但是不需要特意记住它们所表示的含义,只要会判断以下内容就足够了。
#N/A➛(VLOOKUP 函数的)检索值不存在
#DIV/0!➛以0位被除数
#REF!➛引用单元格已被删除
在错误值的处理问题上,最重要的是掌握设定不显示错误值的方法。