1.3 Excel中的单元格引用
单元格引用是指对工作表中的单元格或单元格区域的引用,它可以在公式中使用,以便Microsoft Excel可以找到需要公式计算的值或数据。
在一个或多个公式中,可以使用单元格引用来引用:
•工作表中一个或多个相邻单元格内的数据;
•工作表中不同区域包含的数据;
•同一工作簿的其他工作表中的数据。
单元格引用示例如表1-8所示。
表1-8 单元格引用示例
单元格的引用方式包括相对引用、绝对引用和混合引用,不同的引用方式通过使用“$”进行区分,使用F4进行切换。默认情况下,单元格引用是相对的。
1.相对引用
相对引用是指公式所在的单元格与公式中引用的单元格之间的位置是相对的,如果公式所在的单元格位置发生了变化,那么所引用的单元格也会随之发生变化。
所以在相对引用中,通过复制或其他操作移动函数后,Excel会自动调整移动后函数的相对引用,使之能够引用相对于当前函数所在单元格位置的其他单元格。
例如,若把包含相对单元格引用的公式从单元格A2复制到单元格C2,那么实际上公式里所有的相对引用单元格都会保持行号不变,而列号向右移动两列。也就是说,包含相对单元格引用的公式会因为将它从一个单元格复制到另一个单元格而改变。
继续例1.1,将鼠标指针移动到单元格D1的右下角,鼠标指针变成黑色十字形(如图1-20所示),按住鼠标左键向下拖拽,那么单元格D1内的公式就会被复制到单元格D2～D6内(如图1-21所示),值得注意的是,如果将单元格D1中的公式“=SUM(A1:C1)”复制到单元格D2,D2中的公式将向下调整一行,成为“=SUM(A2:C2)”。如果将公式复制到单元格D3,D3中的公式将向下调整一行,成为“=SUM(A3:C3)”(如图1-22所示)。
图1-20 求和公式
图1-21 相对引用
图1-22 各行公式中的相对引用
2.绝对引用
绝对引用是指被引用的单元格与公式所在单元格的位置是绝对的,也就是不管公式被复制到什么位置,公式中所引用的单元格位置都不会发生变化,其书写形式为$A$1。一个“$”就是一把锁,锁住行或者列。绝对引用中有两把锁,锁“$”在谁的前面就是锁住谁。例如,$A$1中就有两把锁$,一把锁行,一把锁列。
例1.1中,如果希望在复制时保留此示例中的原始单元格引用,需要在列(A和B)和行(1)之前加上符号$来使单元格引用变为绝对。然后,当从D1复制公式“=SUM($A$1:$C$1)”到D2时,该公式会保持完全相同,如图1-23所示。
图1-23 绝对引用
3.混合引用
混合引用是一种介于相对引用和绝对引用之间的引用,即在引用的单元格的行和列之中一个是相对的,一个是绝对的,如$A1、A$1,其中$在哪个字符之前,哪个就是绝对的。
在不频繁的情况下,如果希望使单元格引用变为“混合”,在列或行前加“$”符号以“锁定” 列或行(例如,$A2或B$3)。可用以下步骤更改单元格引用的类型。
(1)选择包含公式的单元格。
(2)在“编辑栏”按钮图像中,选择要更改的引用。
(3)按F4键在引用类型之间切换。
表1-9总结了当将包含引用的公式向下和向右复制两个单元格时引用类型的更新方式。
表1-9 相对引用、绝对引用与混合引用
例1.2 某公司有A～F共6类产品,利润率均为20%,单价和销售量分布如表1-10所示,试求各类别以及汇总的销售额与利润。
表1-10 产品的单价和销售量
【实验步骤】
(1)销售额等于单价乘以销售量,首先计算A类别的销售额,在单元格F4中输入“=D4*E4”,并回车。
(2)拖动单元格F4填充柄,将公式复制到单元格F5到F10。
(3)利润等于销售额乘以利润率,在单元格G4中输入“=F4*$G$2”,并回车,如图1-24所示。
(4)按照上述操作复制G4公式到G5至G10单元格。
图1-24 绝对引用的应用
注意:步骤(3)中如在单元格G4中输入“=F4*G2”,拖动G4的填充柄,复制公式后,就会发现G5:G10单元格出现如图1-25所示的错误。查看单元格G5,发现单元格E5书写的公式为“=F5*G3”,而单元格E3并不是需要的利润率所在单元格,因此就会出现错误。错误原因是利润率应该是绝对引用,无论哪个公式对利润率的引用都是G2单元格,这个引用不能随着公式位置的变化而发生变化。
图1-25 相对引用的错误使用