有时候,我们需要将公式复制到其他单元格中。这时,如事先没有掌握相关知识,就会浪费一些不必要的时间。例如,下图是不同地区的分公司的销售额一览表,其中,处理“结构比率”一栏时,请输入正确的公式。

Excel复制带公式单元格时的陷阱-Excel22

各分公司的“结构比率”,是将各个分公司销售额除以全公司的销售额计算得出的。因此,首先请在 C2中输入“=B2/B11”。

在单元格 C2输入=B2/B11

※选择单元格 C2➛输入等号(=)➛点击单元格 B2➛输入斜线(/)➛点击单元格 B11

Excel复制带公式单元格时的陷阱-Excel22

详细的内容我会在第七章介绍,这里只稍微提一下。在“设置单元格格式”中,可以预先将 C 列的表示形式设为百分比,那么就可以知道北海道分公司在整个公司的销售额中所占的比例。

接下来,同样在 C3~C11中输入计算占比的公式,就可以得出所有分公司的销售额在整体中所占的比例。当然,如果你在单元格中逐个输入相同的公式,做完的时候太阳都下山了。

而且如果你这么做……还会出现这样的乱码:

表格中显示“#DIV、0!”

Excel复制带公式单元格时的陷阱-Excel22

单元格中出现“#DIV/0!”,似乎计算进行得并不顺利。

那么到底出了什么问题?我们选中单元格 C3,按下F2 键。

F2 键的功能】

  • 使活动单元格处于可编辑状态。
  • 选中的活动单元格内容引用自其他单元格时,用有色框线显示被引用的单元格。

于是,所选单元格的引用单元格如下图。

选中单元格 C3,按下F2 键

Excel复制带公式单元格时的陷阱-Excel22

除数引用了正确的单元格(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!”这样的乱码。

像这样,在复制包含公式的单元格作为引用时,结果有所偏差的状态叫作“相对引用”。