引用不同表格数据的公式怎样编辑及其跨工作薄复制粘贴.doc

引用不同工作表数据的公式编辑及其跨工作薄复制粘贴庆云县项目办二一二年五月二十日于济南微山湖大酒店11目 录0、引子 11、首先熟悉第一套工作薄里各个工作表 11.1第一套工作薄里各个工作表标签 11.2第一灌区农田建设内容汇总表 11.3第一灌区农田建设各个项目的单价汇总表 21.4第一灌区高标准农田建设投资汇总表 21.5第一灌区高标准农田建设规划投资估算表 32、第一灌区农田水利规划计算公式编辑举例 42.1求和公式编辑举例 42.2跨工作表引用数据的公式编辑举例 43、曾经走过的路 73.1第二灌区规划内容 73.2直接复制粘贴公式会出现错误结果 74、跨工作薄复制粘贴的新方法 84.1选定单元格 84.2选定公式并复制 94.3粘贴公式 95、关于水库和塘坝的投资估算的提示 110、引子我出差在济南做一个规划，时间很紧。昨天晚上，需要处理完四套格式和计算一致的工作表，每套一个工作薄。由于跨工作表引用数据，公式复杂而且很长，不便于检查，数据又多，仅一套工作表，就干到今晨2点。照此速度，剩余的3套工作表，加上白天都完成不了。我想，既然四套工作表格式一致，就复制公式吧。试了几次，都不成功。原因是跨工作薄复制粘贴公式，照正常的方法行不通。后来，想出来了一个办法，仅用了4个小时，就把剩余的3个工作薄完成了。觉得很有必要与大家分享，遇到这样的情况，你就可以少走弯路了。为便于说明，我把农田水利规划汇总表简化后做例子。例子中的农田水利规划，设定为2个灌区，每个灌区1个工作薄，其格式和计算一致；每个工作薄由4个工作表组成：规划建设内容表、各项目的单价汇总表、投资汇总表和投资估算表。工作目标是通过规划建设内容的项目数量和各项目的单价来计算投资，并进行投资分类汇总。在阅读理解本文时，注意各个图中公式编辑栏中的公式。1、首先熟悉第一套工作薄里各个工作表1.1第一套工作薄里各个工作表标签为叙述方便，规划建设内容表、各项目的单价表、投资汇总表和投资估算表，以下简称依次为表1、表2、表3和表4.图1 第一套工作薄里各个工作表标签1.2第一灌区农田建设内容汇总表第一灌区农田建设内容汇总表，标签为“规划”，简称为表1，内容如下图：图2 第一灌区农田建设内容汇总表1.3第一灌区农田建设各个项目的单价汇总表第一灌区农田建设各个项目的单价汇总表（由典型设计得出,略），简称为表2，内容如下图：图3 第一灌区农田建设各个项目的单价汇总表内容1.4第一灌区高标准农田建设投资汇总表已经输入公式的第一灌区高标准农田建设投资汇总表内容，简称为表3，如下图：图4 第一灌区高标准农田建设投资汇总表内容1.5第一灌区高标准农田建设规划投资估算表已经输入公式的第一灌区高标准农田建设规划投资估算表，简称为表4，内容如下图：图5第一灌区高标准农田建设规划投资估算表2、第一灌区农田水利规划计算公式编辑举例2.1求和公式编辑举例分时间段（2011-2015是前5年，2016-2020是后5年）汇总和分工程类别（水源工程、排水工程）汇总，可以编辑公式（如：在表4的G9单元格输入“=G10+G11+G12+G13”，见图6），或者直接用函数（如：在表4的G14单元格输入“=SUM(G5,G9)”，见图7）。一般连续的单元格求和，用函数方法，不连续的单元格求和，用公式法。这里应用了相反的方法，只是在说明，两种方法都可以。图6 表4的G9单元格公式输入图7在表4的G14单元格公式输入2.2跨工作表引用数据的公式编辑举例我们看表3的F10单元格，它是水源工程的前5年总投资，包括水库、塘坝、泵站的投资，根据表1规划的数量和表2各个项目的单价计算，那么，可以输入“=规划!D10*单价!$C$8+规划!F10*单价!$C$9+规划!G10*单价!$C$6”，回车，可以见到结算结果，如下图：图8 表3的F10单元格输入公式及结算结果下一步，只要选中F10，拖拽复制至F11。其过程，见图9。图9 表3 F10单元格公式复制到F11用同样的方法，编辑G10到G11的公式。然后，选中F9：G11，拖拽复制到表底。就完成了表3的计算过程。其过程如图10到图11，结果如图4。表4也用同样的办法计算完成。计算结果如图5。提示：公式编辑过程中，可以直接输入单元格名称，也可以点击所引用单元格。图10 选中表3的F9：G11图11 将选中的 F9：G11，拖拽复制到表底3、曾经走过的路第一灌区的表格计算汇总完成以后，会发现表3和表4的汇总结果一致，说明计算正确。进行第二灌区的计算之前，应该了解一下其规划内容。3.1第二灌区规划内容以上说过，第一灌区和第二灌区的规划汇总表，格式和计算方法一致。再补充一点，其单价也一样。所以，只要了解第二灌区的规划内容，利用第一灌区规划汇总表的格式、计算方法和单价，就能汇总出结果。第二灌区的规划内容如图12.图12 第二灌区高标准农田水利的规划内容3.2直接复制粘贴公式会出现错误结果首先，我建立了第二灌区资金汇总表的格式，并初步完成了汇总部分和第一个网格的汇总公式输入，见下图13。图13 第二灌区资金汇总表然后，我直接复制粘贴了第一灌区表3中F10单元格中的公式，并将其粘贴到第二灌区表3中F10单元格中。结果见下图14：图14第一灌区表3中F10单元格中的公式直接复制粘贴到第二灌区表3中F10单元格中的结果很明显，粘贴后公式中引用的数据，仍是原来第一灌区中的数据，计算结果也是原来的结果。这与我们的目的完全不一致。怎么办？一一重新输入吧，太麻烦；一一把公式中的工作薄名称去掉，也不容易。我想到了另一种方法，请继续往下看。4、跨工作薄复制粘贴的新方法跨工作薄复制粘贴的新方法概括来说就是，复制单元格中的公式（不是单元格），再粘贴到另一工作薄相应的单元格中。布置如下：4.1选定单元格选定第一灌区表3中的F10单元格，就会发现公式编辑栏中出现了相应的公式。如下图15。图15 选定第一灌区表3中F10单元格4.2选定公式并复制再重新选定公式编辑栏中的公式。这时，公式编辑栏内都变成黑色。复制公式。特别注意：在复制完成以后，必须加按Enter键予以确认！图16 选定公式编辑栏中的公式4.3粘贴公式选定第二灌区表3中的F10单元格，粘贴公式。如下图17。图17 在第二灌区表3中的F10单元格粘贴公式再来看看公式引用的数据和计算结果。引用的数据来自本工作薄，计算结果也是我们想要的结果。接下来的工作就变得非常简单了，复制-粘贴-拖拽，不予赘述，我只提供一下第二灌区表3和表4的最后计算结果，如图18和图19。从图中还可以看出，第二灌区表3和表4的总投资都是12077.89万元，说明我们的汇总计算前后一致。图18第二灌区表3的最后计算结