下载掌阅APP，畅读海量书库
立即打开
在使用公式对工作表进行计算时，基本都需要引用单元格数据参与计算，在引用单元格时可以进行相对引用、绝对引用或混合引用，不同的引用方式可以达到不同的计算结果，有时候为了进行一些特定的计算还需要引用其他工作表或工作簿中的数据。
关键点： 了解什么是相对引用
操作要点： 直接单击引用单元格
应用场景： 相对数据源引用是指把单元格中的公式复制到新的位置时，公式中的单元格地址会随之改变。对多行或多列进行数据统计时，利用相对数据源引用十分方便和快捷，Excel中默认的计算方法也是使用相对数据源引用。
在本例的工作表中统计了超市各产品的进货价格和销售价格，并且用公式计算出了每种产品的利润率，即利润率=（销售价格-进货价格）/进货价格，具体操作如下。
① 将光标定位在单元格D2中，输入公式： =(C2-B2)/B2 ，如图3-32所示。按Enter键，得到利润率。
图 3-32
② 选中D2单元格，向下填充公式至D7单元格，一次性得到其他商品的利润率，如图3-33所示。
图 3-33
② 选中D3单元格，在编辑栏显示该单元格的公式为：=(C3-B3)/B3，如图3-34所示。
图 3-34
通过对比D2、D3单元格的公式可以发现，当复制D2单元格的公式到D3单元格时，采用相对引用的数据源也自动变成了B3、C3，自动发生了相应的变化，这正是计算其他产品利润率时所需要的正确公式（复制公式是批量建立公式求值的一个最常见办法，有效避免了逐一输入公式的烦琐程序）。在这种情况下，用户需要使用相对引用的数据源。
在产品报价表中，需要对市场行情和本公式报价进行比较，如图3-35所示。公式中需要采用相对引用方式，随着公式复制依次对其他产品报价进行比较。
图 3-35
关键点： 了解什么是绝对引用及应用场合
操作要点： 单元格地址前要加上 “$” 符号
应用场景： 绝对数据源引用是指将公式复制或者填入新位置时，公式中对单元格的引用保持不变。要对数据源采用绝对引用方式，需要使用“$”符号来标注，其显示形式为$A$1、$B$2:$B$2等。
先来看下面的例子，表格中统计了某公司各销售员每个月的销售业绩，需要统计各销售员的销售额占总销售额的比值。
将光标定位在单元格C2中，输入公式： =B2/SUM(B2:B6) ，按Enter键，如图3-36所示。
图 3-36
得出的第一位销售员的销售额占总销售额的比值，当前公式的计算结果是没有什么错误的。
当我们向下填充公式至C3单元格时，得到的就是错误的结果（因为用于计算总和的数值区域发生了变化，已经不是整个数据区域），如图3-37所示。
继续向下复制公式，可以看到返回的值都是错的，因为除数在不断发生变化，如图3-38所示。
这种情况下用于求总和的除数不能发生变化，必须对其绝对引用。因此将公式更改为=B2/SUM($B$2:$B$6)，然后向下复制公式，即可得到正确的结果，如图3-39所示。
图 3-37
图 3-38
图 3-39
定位任意单元格，可以看到只有相对引用的单元格发生了变化，绝对引用的单元格不发生任何变化，如图3-40所示。
图 3-40
在进行成绩排名时，其第二个参数表示用于在其中判断名次的数据序列，这个序列不能发生变化，因此在公式中应用绝对引用方式，如图3-41所示。
图 3-41
关键点： 了解引用其他工作表中单元格的方法
操作要点： 先切换到目标工作表再选择单元格区域
应用场景： 在进行公式运算时，很多时候都需要使用其他工作表的数据源参与计算。在引用其他工作表的数据进行计算时，需要添加的格式为： “=函数‘工作表名’！数据源地址” 。
在本例的工作簿有3个工作表分别统计了公司第二季度每位销售员各月的销售量及销售额，如图3-42所示，需要在 “2季度销售统计” 工作表统计每月的总销量及销售额，具体操作如下。
图 3-42
① 切换至 “2季度销售统计” 工作表，选中B2单元格，在编辑栏中输入等号及函数等，如此处输入：=SUM(，如图3-43所示。
图 3-43
② 单击 “4月销售统计” 工作表标签，切换到 “4月销售统计” 工作表，选中参与计算的单元格（引用单元格区域的前面添加了工作表名称标识），如图3-44所示。
③ 输入其他运算符，如果还需引用其他工作表中数据来运算，则按第 ② 步方法再次切换到目标工作表中选择参与运算的单元格区域，完成后按Enter键即可计算出4月的销售量，如图3-45所示。
图 3-44
图 3-45
④ 按照相同的办法，可以在C2单元格中引用 “4月销售统计” 工作表中 “销售额” 列的数据，即可计算出4月的总销售额。
关键点： 了解跨工作簿引用数据源计算的方法
操作要点： ①把要引用其中数据的工作簿都打开
②先切换到工作簿，然后切换到工作表，再选择目标数据
应用场景： 在公式中还可以引用其他工作簿的数据来进行数据计算。要实现对其他工作簿单元格的引用，首先必须确保两个工作簿同时都打开。其引用的格式为： “=[工作簿名]工作表名!单元格” 。
在本例的两个工作簿中分别统计了超市商品上期的库存量（见图3-46）和本期的入库量（见图3-47），需要统计出累计的库存量，具体操作如下。
① 在 “本期库存” 工作簿的 “库存累计” 工作表，将光标定位在C2单元格中，输入：=B2+，如图3-48所示。
② 切换至 “上期库存” 工作簿，并选中B2单元格，此时可以看到公式为：=B2+[上期库存.xlsx] Sheet1!$B$2，如图3-49所示。
图 3-46
图 3-47
图 3-48
图 3-49
③ 切换到 “本期库存” 工作簿，按Enter键即可得出结果，可以在编辑栏中看到单元格前添加了工作簿名称与工作表名称，如图3-50所示。
图 3-50
④ 若要向下复制公式，可以把默认的绝对引用方式更改为相对引用方式，然后再向下复制公式，得出如图3-51所示的结果。
图 3-51
关键点： 了解如何引用多个工作表中的同一单元格进行计算
操作要点： 引用多张工作表的同一单元格
应用场景： 引用多张工作表中的数据源是指在两张或者两张以上的工作表中引用相同地址的数据源进行公式计算。多张工作表中特定数据源的引用的格式为 “=函数(‘工作表名1:工作表名2:工作表名3: …… :工作表名n’!数据源地址）” 。
在本例的工作簿中包含了多张工作表，分别统计了如图3-52所示的 “国购店” 和如图3-53所示的 “乐城店” 1～6月份的销售额。现在需要建立一张统计表，对各个分店各个月份的销售额进行汇总。此时在计算中需要引用多个工作表中的同一单元格进行计算，具体操作如下。
图 3-52
图 3-53
① 切换至 “销售统计表” 工作表，将光标定位在B2单元格，输入：=SUM(，如图3-54所示。
图 3-54
② 单击 “国购店” 工作表标签（见图3-55），按住Shift键不放，再单击 “乐城店” 工作表标签，（此时选中的工作表组成一个工作组，如果这两个工作表中间还有其他工作表也一起将选中），然后单击B2单元格，如图3-56所示。此时公式为：=SUM('国购店:乐城店'!B2。
图 3-55
图 3-56
③ 然后再输入公式后面的右括号 “）” ，按Enter键即可进行数据计算，并自动返回 “销售统计表” 工作表的B2单元格，如图3-57所示。
图 3-57
④ 向下填充B2单元格的公式至B7单元格，即可依次得到其他月份的总销售业绩。选中B3单元格可以对比公式，如图3-58所示。
图 3-58
这种计算方式前提要保证用于计算的多表格的结构要一样。因为它们是将同位置的数据用于计算。
关键点： 了解快速切换单元格引用方式的方法
操作要点： F4键切换引用方式
应用场景： 在Excel中可以通过按F4键快速地在绝对引用、相对引用、行/列的绝对/相对引用之间切换。
下面以计算占比公式=B2/SUM($B$2: $B$6)为例介绍通过按F4键切换单元格引用类型的方法。
① 单击C2单元格，将光标定位到编辑栏中，选中公式中的B2:B6单元格区域（原先为相对引用方式），如图3-59所示。
图 3-59
② 按F4键一次，即可发现相对引用变成了绝对引用（$B$2:$B$6），如图3-60所示。
图 3-60
③ 再次按F4键，变为行相对引用、列绝对引用（B$2:B$6），如图3-61所示。
④ 再次按F4键，变为列相对引用、行绝对引用（$B2:$B6），如图3-62所示。
⑤ 再次按F4键，即可恢复单元格数据的初始引用状态。
图 3-61
图 3-62
1．定义名称引用其他工作表数据
如图3-63所示的表格是一个产品的“单价一览表”，而在如图3-64所示的表格中计算金额时需要先使用VLOOKUP函数返回指定产品编号的单价（用返回的单价乘数量才是最终金额），因此设置公式时需要引用 “单价一览表!A1:B13” 这样一个数据区域。
图 3-63
① 首先在 “单价一览表” 中选中数据区域，在左上角的名称框中输入一个名称，如此处定义为 “单价表” ，如图3-65所示，按Enter键即可定义名称。
图 3-64
图 3-65
② 定义名称后，则可以使用公式 “=VLOOKUP (B2,单价表，2,FALSE)*C2” ，如图3-66所示。即在公式中使用 “单价表” 名称来替代 “单价一览表!A1:B13” 这个区域。
图 3-66
2．跨工作表引用统计平均分
当前的工作簿中有两张表格，如图3-67所示的表格为 “员工培训成绩表” ，用于对成绩数据的记录与计算总成绩；如图3-68所示的表格为 “平均分统计表” ，用于对成绩按分数求平均值。显然求平均值的运算需要引用“员工培训成绩表”中的数据。
图 3-67
图 3-68
① 在 “平均分统计表” 中选中目标单元格，在公式编辑栏中应用函数=AVERAGE()，将光标定位到括号中，如图3-69所示。
② 单击 “员工培训成绩表” 工作表标签，切换到 “员工培训成绩表” 中，选中要参与计算的数据，选择时可以看到编辑栏中同步显示，如图3-70所示。
③ 如果此时公式输入完成，则按Enter键结束输入（如图3-71所示已得出计算值）；如果公式还未建立完成，则该手工输入的手工输入。当要引用单元格区域时，就先单击单元格目标工作表的标签，切换到目标工作表中并选择目标区域即可。
图 3-69
图 3-70
图 3-71