《打造财务Excel达人》第五章 第4节第5节

注：本文摘自《偷懒的技术：打造财务Excel达人》第一版。本书2017年荣获当当网Excel类年度畅销榜第一名第四节 费用分析报表――日期函数的多功能应用示例在财务管理及分析工作中，对费用管理和分析是一个重要的组成部分。费用看似简单，但是却涉及到费用项目、归集部门、列支期间等多个分析维度。一般情况下，决策者至少需要了解各部门各个费用项目的当年累计发生额和当期发生额和一些通过查询方式获取的对比信息。本节内容，我们就以逸凡公司2014年一季度管理费用为例，讨论如何用Excel来设计一套高效实用的《费用报表》（参见示例文件《表5－2 费用报表》）。一、基本框架与功能展示《费用报表》由管理费用明细清单（以下简称“明细清单”如图5－16）、管理费用累计报表（以下简称“累计报表”，如图5－17）、管理费用本月报表（以下简称“本月报表”，如图5－18）、管理费用期间查询报表（以下简称“期间查询报表”，如图5－19）和管理费用部门查询报表（以下简称“部门查询报表”，如图5－20）组成。此外，为了提高《费用报表》的拓展性，我们还需要一个基础设置表，稍后我们再让它出场。

图5－16 明细清单明细清单是典型的清单式报表，它实际上就是一个逐笔记录费用的流水账，该表A至I列的信息一般可以来源于财务记账系统中相关明细表的导入。J列及以后的信息属于设置公式后自动生成的项目，它们将服务于报表的生成。由于不同财务记账系统的格式差异，该表的设计也是因人而异的，但是应尽量避免录入字段与公式字段的混搭，以方便录入信息时一次性到位，提高信息录入的效率。

图5－17 累计报表（自动生成）

图5－18 本月报表（自动生成）

图5－19 期间查询报表（输入查询参数后自动生成）

图5－20 部门查询报表（输入查询参数后自动生成）我们的目标是，只需要在明细清单中手工录入期间、部门、核算科目代码、核算费用科目以及金额（单位：元）后，就可以自动实现以下功能。（一）主要信息功能自动计算生成本年累计费用及统计当期项目与归集部门组合二维费用报表。（二）辅助信息功能1、用户录入待查询起始期间后，自动计算生成该期间累计费用项目与归集部门组合二维费用报表。2、用户录入待查询部门后，自动计算生成该部门累计费用项目与月度组合二维费用报表。如果查询部门参数为空值，则将统计整个公司的费用信息。（三）逻辑校验功能1、自动校验各类报表与费用明细清单的数据是否匹配。2、自动提示无效费用科目及无效归集部门。二、基本前提及假设（一）报表费用科目（以下简称“报表科目”）以二级科目进行列报且以万元为单位。一般情况下，财务核算的费用科目体系会根据核算管理需要设立多级明细科目，并以元为单位进行金额核算。但是报表作一个具有高度概括功能的经营管理工具，不可能也没有必要进行过于微观的反映，所以，本案例中的《费用报表》仅列示费用的二级科目，且金额以万元为单位。（二）费用按自然月度归集。费用的统计、查询均仅支持自然月度汇总，如果想统计或查询诸如2014年1月20日至2014年2月19日的费用情况，需对公式进行升级处理，由于其不具备广泛应用型，故不属于本节涉及的内容。三、注意事项（一）拿来主义的兼容性前面已经提到，《费用报表》的明细清单表的主要信息都来源于财务记账系统导出的信息，但是我们在享受拿来主义的便捷时，也要注意其兼容性，很多系统导出的Excel表，格式都不那么规范，诸如数据都是文本格式之类不胜枚举。所以，在将系统中导出的信息粘贴到明细清单表之前，应首先检查其格式是否达标，对于不合格的格式，应及时予以纠正。否则，一旦出现各种不兼容的情况，再完美的公式设计也会失效。（二）多条件求和公式与数据透视表的共通性本案例中的核心元素是多条件求和。而在需要多条件求和时，除了使用SUMIFS等公式外，还可以通过数据透视表来实现。本节应用SUMIFS函数来实现相关功能。四、知识点装备在阅读本节下面的内容前，请各位读者朋友首先确认大脑中是否已经基本装备了图5－21中的相关知识点。总类别分类别知识点函数逻辑类IF函数日期与时间类MONTH函数查找与引用类LEFT函数、HLOOKUP函数、VLOOKUP函数数学与三角函数类SUM函数、SUMIF函数、SUMIFS函数、RONUD函数应用查找与替换查找与替换插入表格选择性粘贴转置数据有效性序列自动求和自动求和其他&&的应用单元格引用混合引用和绝对引用图5－21 本节知识点五、主要信息的公式设计方法在对《费用报表》的框架、功能和相关注意事项有了大致的认识后，我们就将通过逸凡公司2014年一季度管理费用的案例来讨论《费用报表》的设计了。【案例5－2】逸凡公司管理费用（科目代码6602）核算按明细科目及部门核算项目进行归集，此外，技术部发生的期间费用通过研发支出（费用化）（科目代码5301.01）科目进行核算，且在月末将研发支出（费用化）科目的所有余额全部结转至管理费用。逸凡公司管理费用及研发支出（费用化）的科目体系见图5－22。此外，纳入管理费用核算的部门有：总经办、财务部、人力资源部、行政部、技术部和采购部。逸凡公司2014年一季度发生的费用流水账（简表）如图5－16中A3:I26单元格区域所示。科目名称管理费用科目代码研发支出（费用化）代码职工福利薪酬6602.015301.01.01工资及社保6602.01.015301.01.01.01福利费6602.01.025301.01.01.02职工教育经费6602.01.035301.01.01.03水电费6602.025301.01.02办公费6602.035301.01.03通讯费6602.045301.01.04差旅费6602.055301.01.05国内差旅费6602.05.015301.01.05.01国际差旅费6602.05.025301.01.05.02折旧摊销费6602.065301.01.06业务招待费6602.075301.01.07图5－22费用科目表（简表）结合《费用报表》结构，相关设计方法如下：（一）明细清单表的公式设计方法1、报表科目代码（J4:J27单元格区域）和报表科目（K4:K27单元格区域）由于费用核算是归集到费用科目的末级，而《费用报表》仅以管理费用的二级科目列示，所以我们首先要将核算科目转换为对应的二级科目。根据图5－22的费用科目表可以看出，管理费用的二级科目的代码长度为7，而对于研发支出（费用化）来说，对应到管理费用二级科目的实际上是其三级科目，其代码长度为10。这里就出现了一个条件选择的问题：当科目代码第一位为5时。取左边10位是取二级科目的代码，否则，则只需要取左边7位。J4单元格的公式为：=IF(LEFT(G4,1)="5",LEFT(G4,10),LEFT(G4,7))执行列填充后，报表科目代码就能从核算科目代码顺利过渡了（如图5－23）。

图5－23 报表科目代码的公式确定了报表科目代码后，我们就可以用VLOOKUP函数来匹配对应的科目名称了。到这里，我们就会发现，准备一张基础设置表是很有必要的了。对于《费用报表》来说，我们可以预先设计一个报表科目代码与报表科目的对照表（如图5－24），这样，我们就可以根据该对照表，轻松实现明细清单中报表科目代码与报表科目的联姻了。

图5－24 基础设置表基础设置表里的部门信息又是干什么用的呢？先不着急。稍后会有介绍。现在我们先将明细清单表中报表科目的公式完成。K4单元格的公式为：=IF(J4="","",VLOOKUP(J4,基础设置!$B$4:$C$23,2,0))执行列填充后，即可获得与费用科目代码匹配的报表科目（如图5－25）。

图5－25 报表科目代码的公式2、以万元表示金额（L4:L27单元格区域）L4单元格的公式为：=I4/10000执行列填充后，即可将费用金额转换为万元。注意，为避免大量四舍五入积少成多引起合计金额的重大偏差，此处一般不使用ROUND函数进行小数点限定。3、累计费用合计（J2单元格）不是说清单式报表不应该有求和项吗？那是说的不要在清单表的主体框架（A3:N27单元格区域）中出现求和，我们把求和项放在清单表主体框架之外的顶部，就没任何不良影响了。J2单元格公式为：=SUM(L4:L27)4、本月费用合计（L2单元格）这个就是只针对当前会计期间（F2单元格）的费用进行求和了。一般情况下，《费用报表》的最大累计期间为一个自然年度，所以我们就可以直接根据期间来进行识别了（如图5－26）。L2单元格的公式为：=SUMIF($B$4:$B$27,MONTH(F2),L4:L27)

图5－26 本月费用合计的公式为使报表数据更具有宏观性，我们假定《费用报表》中所有统计数据均以万元为单位，如果需要以元为单位，只需把相关公式中的L替换为I即可。（二）累计报表的公式设计方法累计报表的功能是统计各部门各项费用的本年累计发生额。这显然是一个交给SUMIFS函数来解决的多条件求和的问题。需要注意的是，本案例中的四个报表都属于二维报表，这就意味着，在设置公式时，我们就需要考虑相对复杂一点的区域填充（先行填充，再列填充）了。C4单元格的公式为：=SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,$B4,明细清单!$E$4:$E$27,C$3)执行区域填充后，累计报表的统计数据就将全部生成（如图5－27）。

图5－27 累计报表的区域填充公式最后，我们来完成费用科目及部门费用的合计公式设置。还记得第一章中那个自动求和的案例吗？现在可以练练手了，此处不再赘述。在进入下一张报表的公式设计方法的分析之前，我们先来继续讨论一下本节注意事项部分中提到的拓展性问题。前面已经提及，本案例中的部门和费用科目都存在着拓展性的需求，如果每次调整我们都一个一个的在每张表中进行增加或删减，就比较费事。所以，我们可以考虑为相关信息建立一个基础设置表进行统一维护，同时使用“=”自动关联到各个报表对应的位置上，这样，只要对现有信息进行了变更，报表就能立即同步完成调整。该方法不仅能提高工作效率，更有助于在整套报表中规避同物不同名的情况。而且在操作上也极其简单，所以非常值得你使用。在基础设置表中，我们编制的报表科目代码与报表科目对照表，不仅仅只是为VLOOKUP函数服务的，同样，也可以为报表同步关联费用科目和服务。同理，部门信息为 在基础信息表中出现，也是为了拓展性的需要。此外，我们还可以将基础信息表中的期间参数与明细清单和各报表进行同步关联，以实现期间变更的批量化操作。现在回到《费用报表》的问题上来，在报表中，费用项目的同步填充非常顺手。E4单元格的公式为：=IF(基础设置!C4="","",基础设置!C4)执行列填充后，即可实现费用科目的同步（如图5－28）。

图5－28 通过基础设置表同步费用科目信息但是部门的关联就比较麻烦了，它在报表中的列示是横向的，与其在基础设置中的纵向列示在方向性上不匹配，所以肯定是无法直接实现快捷的关联填充的。怎么办？难道需要一个一个的去关联？当然不用，下面我们就来看看如何利用第三章第一节提到的替换功能和选择性转帖中的转置功能来攻克这个别扭的堡垒。1、我们先在累计报表中找一块空地，先按纵向方式关联填列（如图5－29）。

图5－29 纵向同步部门2、将上述单元格中的“=”替换为一个在公式中未出现过的字符（比如“X”）。此时，由于构成公式的必要元素“=”被篡改，导致其公式失效，在单元格中只能以文本风格显示（如图5－30）。

图5－30 将公式中的“=”替换为“X”3、复制上述单元格，并将其以【选择性粘贴】→【转置】的方式粘贴到报表中的C3:K3单元格区域（如图5－31）。

图5－31 将被破坏的公式选择性粘贴到部门区域5、将部门区域单元格中的“X”替换为“=”。公式又重新恢复。关联设置顺利完成（如图5－32）。

图5－32 将被破坏的公式还原完成部门同步公式设置上述步骤又是一个灵活运用“替换”结合公式对“=”的依赖性特点实施“偷梁换柱”出奇招的案例。它再一次告诉我们，对于Excel的一些应用或函数功能，我们除了知道其基本应用，还需要发挥想象力挖掘其潜在的组合运用价值。最后再啰嗦一句，我们还应该考虑到相关信息未来的增加需求，所以在每个关联信息的维护区域留出一定的待定席位，为以后的拓展留好余地。（三）本月报表的公式设计方法本月报表的功能是统计各部门各项费用在报表日当月的发生额。和刚刚讨论完的累计报表相比，其实就是在多条件求和时。再加上了一个期间的控制。即，除了累计报表公式中限定的条件外，还得要求费用发生的期间必须是报表日所在的期间。于是，我们就可以很容易得出相关公式了。C4单元格的公式为：=SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,$B4,明细清单!$E$4:$E$27,C$3,明细清单!$B$4:$B$27,MONTH($G$2))执行区域填充后，本月报表的统计数据就将全部生成（如图5－33）。

图5－33 本月报表的区域填充公式六、辅助信息的公式设计方法前面我们已经完成了《费用报表》中累计报表以及本月报表的公式设计，但是这两个报表仅仅提供了累计和本月两种条件下的信息。明显不能满足费用管理中查询及分析需求。所以。我们还需要编制几个常用的辅助性查询报表，以提升报表的使用价值。（一）期间查询表的公式设计方法当我们需要查询一个任意连续期间（本年累计除外）的费用累计情况时就有必要在本月报表的基础上进行升级，即把仅能统计单月费用升级为可统计任意连续月份费用。功能强大了，但是公式却只需要做少量的补充即可。假设我们将期间查询表的F2单元格设为查询起始期间，H2单元格设为查询终止期间，则升级前后公式的差异，无非就将升级前的必须等于指定的某个（报表日）月份，调整为升级后介于查询起始期间与查询终止期间之间。C4单元格的公式为：=SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,$B4,明细清单!$E$4:$E$27,C$3,明细清单!$B$4:$B$27,">="&MONTH($F$2),明细清单!$B$4:$B$27,"<="&month($h$2))< span="">执行区域填充后，用户只需输入查询的起止期间，即可自动生成该期间累计费用报表（如图5－34）。

图5－34 期间查询表的区域填充公式（二）部门查询表的公式设计方法针对整个公司或者某个部门的费用进行月度对比分析也是费用管理及分析工作中的一个重要方式。所以，我们也有必要设计一个可以对查询主体的各月费用进行直观显示的报表。相对于前面三张报表，部门查询表看似模版变了脸，但是公式逻辑依然是万变不离其宗的多条件求和。只是需要注意的是，由于我们要求当查询部门为空值时，自动计算生成所有部门（公司整体）各月费用汇总数据。所以，在多条件求和前，先得进行一个两条件判断。我们就分两步来攻克这个公式。第一步：H2单元格为空值（所有部门费用查询）如果H2单元格为空值，则应该统计所有部门费用，此时的多条件就只需要考虑费用项目与期间的匹配。C4单元格的第一步公式为：=IF($I$2="",SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,$B4,明细清单!$B$4:$B$27,MONTH(C$3)),进入第二步)第二步：H2单元格不为空值（单个部门费用查询）进入第二步，说明是对具体的某个部门进行查询，所以除了第一步中应满足的条件外，还需要增加一个部门匹配的条件。C4单元格的第二步公式为：= SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,$B4,明细清单!$E$4:$E$27,$I$2,明细清单!$B$4:$B$27,MONTH(C$3))将上述两个步骤合并。C4单元格的公式为：=IF($I$2="",SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,$B4,明细清单!$B$4:$B$27,MONTH(C$3)),SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,$B4,明细清单!$E$4:$E$27,$I$2,明细清单!$B$4:$B$27,MONTH(C$3)))执行区域填充后，用户只需输入查询的部门，即可自动生成该部门各月费用报表（如图5－35。图a为查询财务部费用汇总，图b为查询所有部门汇总费用）。

图5－35（a） 部门查询表的区域填充公式

图5－35（） 部门查询表的区域填充公式需要友情提醒的是，为了确保输入查询部门的准确性，建议对I2单元格作数据有效性的序列设置。此外，用户还可以根据具体的管理需求，建立预算报表、并衍生出各类预算与实际发生额对比报表等。思路都大同小异，有兴趣的读者朋友可以自行研究。七、逻辑校验信息的公式设计方法（一）各类报表与明细清单的逻辑校验1、累计报表与明细清单的逻辑校验累计报表与明细清单校验的逻辑是：累计报表中的各部门（及所有部门）费用的合计数=明细清单上记录的该部门费用（及所有部门）的合计数（1）、各部门费用校验假设我们规定在校验通过时显示“OK”，校验出错时显示“偏差X（X为偏差的金额）”，下同，C15单元格的公式为：=IF(ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$E$4:$E$27,C3)-C14,2)=0,"OK","偏差"&ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$E$4:$E$27,C3)-C14,2))执行行填充至K15单元格即可完成上述校验公式的设置（如图5－36）。

图5－36 累计报表的各部门校验公式（2）、费用合计校验L15单元格的公式为：=IF(ROUND(SUM(明细清单!L4:L27)-L14,2)=0,"OK","偏差"&ROUND(SUM(明细清单!L4:L27)-L14,2))2、本月报表与明细清单的逻辑校验本月报表与明细清单校验的逻辑与累计报表基本一致，只是在明细清单中校验的期间仅限于本月。（1）、各部门费用校验C15单元格的公式为：=IF(ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$E$4:$E$27,C3,明细清单!$B$4:$B$27,MONTH($G2))-C14,2)=0,"OK","偏差"&ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$E$4:$E$27,C3,明细清单!$B$4:$B$27,MONTH($G2))-C14,2))执行行填充至K15单元格即可完成上述校验公式的设置（如图5－37）。

图5－37 本月报表的各部门校验公式（2）、费用合计校验L15单元格的公式为：=IF(ROUND(SUM(明细清单!L4:L27)-L14,2)=0,"OK","偏差"&ROUND(SUM(明细清单!L4:L27)-L14,2))3、期间查询报表与明细清单的逻辑校验本月报表与明细清单校验的逻辑与累计报表基本一致，只是在明细清单中校验的期间仅限于查询期间内。（1）、各部门费用校验C15单元格的公式为：=IF(ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$E$4:$E$27,C3,明细清单!$B$4:$B$27,">="&MONTH($F2),明细清单!$B$4:$B$27,"<="&month($h2))-c14,2)=0,"ok","偏差"&round(sumifs(明细清单!$l$4:$l$27,明细清单!$e$4:$e$27,c3,明细清单!$b$4:$b$27,">="&MONTH($F2),明细清单!$B$4:$B$27,"<="&month($h2))-c14,2))< span="">执行行填充至K15单元格即可完成上述校验公式的设置（如图5－38）。

图5－38 期间查询报表的各部门校验公式（2）、费用合计校验L15单元格的公式为：=IF(ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,">="&MONTH($F2),明细清单!$B$4:$B$27,"<="&month($h2))-l14,2)=0,"ok","偏差"&round(sumifs(明细清单!$l$4:$l$27,明细清单!$b$4:$b$27,">="&MONTH($F2),明细清单!$B$4:$B$27,"<="&month($h2))-l14,2))< span="">4、部门查询表与明细清单的逻辑校验部门查询表与明细清单校验的逻辑是：部门查询表中所查询的部门（或所有部门）的各月（及累计）费用合计数=明细清单上记录的属于该查询部门（或所有部门）的对应期间（及累计）费用合计数（1）、各期间费用校验第一步：H2单元格为空值（所有部门费用查询）如果H2单元格为空值，说明是统计所有部门费用，此时，只需要对月份进行匹配，为了公式格式统一，我们这次杀鸡用牛刀，让SUMIFS来执行这个单条件求和的运算。C15单元格的第一步公式为：=IF($I$2="",IF(ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,MONTH(C3))-C14,2)=0,"OK","偏差"&ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,MONTH(C3))-C14,2)),进入第二步)第二步：H2单元格不为空值（单个部门费用查询）进入第二步，说明是对具体的某个部门进行查询，所以除了第一步中应满足的条件外，还需要增加一个部门（H2单元格）匹配的条件。C15单元格的第二步公式为：=IF(ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,MONTH(C3),明细清单!$E$4:$E$27,$I$2)-C14,2)=0,"OK","偏差"&ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,MONTH(C3),明细清单!$E$4:$E$27,$I$2)-C14,2))将上述两个步骤合并。C15单元格的完整公式为：=IF($I$2="",IF(ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,MONTH(C3))-C14,2)=0,"OK","偏差"&ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,MONTH(C3))-C14,2)), IF(ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,MONTH(C3),明细清单!$E$4:$E$27,$I$2)-C14,2)=0,"OK","偏差"&ROUND(SUMIFS(明细清单!$L$4:$L$27,明细清单!$B$4:$B$27,MONTH(C3),明细清单!$E$4:$E$27,$I$2)-C14,2)))执行行填充至N15单元格即可完成上述校验公式的设置（如图5－39）。

图5－39 部门查询报表的各月校验公式（2）、费用合计校验费用合计校验我们仍然可以采用多条件求和公式处理。这个对读者朋友来说已经是有些腻歪了。所以这次我们换个思路，来个新口味的。费用合计校验其实就是检查部门查询表的合计金额是否与累计报表中对应部门的合计金额相等。这里多出来的一个新问题是，如果在累计报表中查找与查询部门对应部门的合计数？看到这类查询问题，首先闪过眼前的，就是我们已经劳驾过好几次的VLOOKUP函数了。不过本案例中有个问题比较特殊，那就是查找的备选区域不再是列，而是行。于是，HLOOKUP函数粉墨登场。第一步：H2单元格为空值（所有部门费用查询）O15单元格的第一步公式为：=IF(I2="",IF(ROUND(明细清单!I2-O14,2)=0,"OK","偏差"&ROUND(明细清单!I2-O14,2)),进入第二步)第二步：H2单元格不为空值（单个部门费用查询）O15单元格的第二步公式为：= IF(ROUND(HLOOKUP(I2,累计报表! C3:K14,12,0)-O14,2)=0,"OK","偏差"&ROUND(HLOOKUP(I2,累计报表!C3:K14,12,0)-O14,2))将上述两个步骤合并。O15单元格的完整公式为（如图5－40）：=IF(I2="",IF(ROUND(明细清单!I2-O14,2)=0,"OK","偏差"&ROUND(明细清单!I2-O14,2)), IF(ROUND(HLOOKUP(I2,累计报表! C3:K14,12,0)-O14,2)=0,"OK","偏差"&ROUND(HLOOKUP(I2,累计报表!C3:K14,12,0)-O14,2)))

图5－40 部门查询表的合计校验公式上述各表仅仅是对部门数据或各期间数据进行了校验，如果还需要对各报表中各项费用进行校验，也可参照上述逻辑设计相关公式，本文不再赘述。（二）费用科目及归集部门有效性及同步性校验上面我们完成了各类报表数据与费用明细清单数据的逻辑校验，校验通过当然皆大欢喜，但是一旦校验出现偏差，如果慌不择路不讲方法去乱找，就会很悲催了。所以纠错也是需要先分析原因。如果校验出现总数与明细数据误差，首先基本可以断定明细清单的金额是正确的，因为明细清单的数据是所有报表的源头。而各报表是根据明细清单中的报表科目、部门等信息进行若干条件匹配后自动计算生成数据的。就算明细清单数据有误，也只是导致各类报表数据连带犯错，但并不能导致明细清单数据与报表数据出现偏差。所以，出现偏差的最大可能性是明细清单中的报表科目额关键字段和报表产生了脱节。例如，某个报表科目（或部门）在明细清单里有，但是在报表中没有，这样报表自然无法统计该报表科目（或部门）的金额，于是偏差就出现了。找到了症结就好对症下药了。除了金额外，明细清单中，参与报表计算的关键字段包括期间、部门和费用科目。期间通过筛选很容易排查错误。所以，我们重点需要对明细清单中的报表科目和部门进行有效性校验。由于各报表中的费用项目和部门信息都来自基础设置表，所以，我们的校验逻辑就是，看看明细清单中使用的报表科目和部门，是否在基础设置中出现。假设数据有效时显示“OK”，无效时显示“无效”。M4单元格的公式为（科目有效性校验）：=IF(K4="","",IF(ISERROR(VLOOKUP(K4,基础设置!$C$3:$C$23,1,0)),"无效","OK"))执行列填充后，即可完成科目的有效性校验（如图5－41）。

图5－41 科目有效应的校验公式N4单元格的公式为（部门有效性校验）：=IF(E4="","",IF(ISERROR(VLOOKUP(E4,基础设置!$A$3:$A$23,1,0)),"无效","OK"))执行列填充后，即可完成部门的有效性校验（如图5－42）。

图5－42 部门有效应的校验公式如果上述校验全部通过，但是报表数据仍然存在偏差，就可能存在各报表的费用科目或部门没有同步的情况。例如某费用科目在基础信息表和明细清单均存在（此时科目校验显示“OK”），但是报表中却没有同步该费用科目，导致报表数据缺失。为防止此类情况，我们就需要在基础信息表中进行同步性校验。这样，就实现了基础信息表、明细清单和各报表的全方位无死角校验。假设通过验证显示“OK”，未通过验证显示“未同步”，以累计报表为例。D4单元格的公式为（科目同步性校验）：=IF(C4="","",IF(ISERROR(VLOOKUP(C4,累计报表!$B$4:$B$13,1,0)),"未同步","OK"))执行列填充后即可完成科目同步性校验公式的设置（如图5－43）。

图5－43 科目同步性的校验公式E4单元格的公式为（部门同步性校验）：=IF(A4="","",IF(ISERROR(HLOOKUP(A4,累计报表!$C$3:$K$3,1,0)),"未同步","OK"))执行列填充后即可完成科目同步性校验公式的设置（如图5－44）。

图5－44 部门同步性的校验公式第五节 营运周报表――集团快速汇总下属公司报表示例在本章前四节的内容中，我们通过案例详细展示了四套不同用途的财务工作表的设计方法，不过这四个工作表都属于不需要编制者之外的其他人参与填写的独立编制模式。而在实际工作中，我们经常会遇到这种情况：先设计好表格分发给其他部门或下属公司，其他部门或下属公司填好后传回，然后进行汇总统计，有些表格是需要定期填报汇总的，这些表格如果设计不好，既不方便下属公司填报，也不方便集团公司汇总。那怎么设计表格才能让集团公司可以快速合并汇总数据、下属公司又能填报方便，且尽可能不重复输入数据呢？。本节我们就以逸凡公司《营运周报表》为例，对此类分发后又收回汇总的工作表进行介绍，主要介绍此类表格的设计思路。这一节是第二章第三节理念和方法的具体应用，通过本示例你可看到，原来集团数据汇总可以如此简单而快速。一、工作需求【案例5－5】逸凡公司为集团公司，下辖矿井十余个（为便于展示，本示例只考虑四个），公司领导每周五需要了解下属矿井本周（上周六到本周五）的营运数据：进尺、产量、销量、回款情况、煤炭发热量等指标情况及当月和年度预算完成进度。逸凡公司财务人员收集下属矿井的上述指标后统一汇总，呈报给公司领导，报送的表格既要有总体汇总数，也要有各公司当期数据。二、设计思路及应实现的功能设计总体要求：作为集团的财务人员一定要有服务的理念，在设计表格时，除了要便于集团公司汇总，还要考虑下属矿井填列数据是否方便。汇总表格要使用公式根据下属公司填报的数据自动汇总，不必手工填列、不重复抄填数据。（一）表格布局1、下属公司的上报表格各矿井上报表格的列分别列示本月/本年实际数、本月/本年预算数、预算完成进度等，表格的行分别列示各项营运指标：开拓进尺、生产进尺、产量、销量、含税销售收入、销售回款、平均售价、平均发热量。各公司上报表格的布局参见图5－105表格的格式（本案例的示例文件请参见营运周报文件夹）。

图5－105各矿井上报表格考虑到数据是按周填报，并且还需要填报本月累计和本年累计数据，如果手工填列本月累计和本年累计，需要手工将本期数据加到上期累计数上去，填写很麻烦且容易出错。为避免手工填列这些数据，各矿井还应该增加一个台账表格，使用求和公式根据台账数据自动统计本月累计和本年累计数据。《每周台账》表格格式如图5－106：

图5－106 每周台账在台账表格填列每周数据，台账的列就是各项营运指标。为了提高填写效率，上报表格的本周完成数直接用查找引用公式从台账表格取数，上报表格的本月累计和本年累计数据使用求和公式自动计算台账数据生成。2、集团公司的汇总表格集团公司的汇总表格有二种选择：方案1：所有矿井的所有指标在一张表格列示，某一指标下每个公司的数据在不同行展示，然后用一汇总行列示本指标的汇总数据。具体格式如图5－107所示：

图5－107集团公司汇总表格（方案1）缺点：不方便查看整体汇总数据，也不方便查看每个矿井的各项数据方案2：所有数据在同一工作簿列示，每个公司使用一个工作表，各公司表格结构一致，集团整体情况使用汇总表列示。

图5－108 集团公司汇总表格（方案2）缺点：不方便对比查看某一指标各公司的情况；两种方案各有优缺点，但是第一种方案的缺点可以使用Excel的分组、筛选功能来克服，使用分组的分级显示功能可以很方便的查看汇总数据，使用筛选功能可以很方便地查看某一矿井的各项指标。因而，第一种方案更能满足工作需求，我们采用第一种方案。（二）表格的公式各矿井上报的表格应该实现全自动取数，上报表格只需填列周数或日期，表格自动从台账表格取数：本周数使用VLOOKUP函数进行查找引用、本月和本年累计数使用SUMIF函数进行统计。集团公司的汇总表格应该能使用公式从各矿井上报的表格中链接取数。（三）报表的后续使用由于营运周报是定期报表，汇总报表应能自动更新或方便批量翻新，即：如果上报的表格名称是固定不定的，每周当下属公司报表上报后，将数据放入指定的文件夹，汇总表格应能自动更新。如果上报的表格名称是按周数或日期命名，比如类似“甲公司营运周报（5/17-5/23）”的格式，那么上报表格的名称应规范，要方便使用查找替换实现批量翻新（报表翻新的技术请参阅第二章第三节）。三、知识点装备在阅读本节下面的内容前，请各位读者朋友首先确认大脑中是否已经基本装备了图5－109中的相关知识点总类别分类别知识点函数日期与时间类MONTH、EOMONTH、WEEKDAY数学与三角函数类FIND、LEFT、RIGHT、LEN、TEXT、其他IF、VLOOKUP、SUMIF、IFERROR、ROW应用自定义格式、数据有效性、创建组、筛选图5－109 营运周报知识点四、主要信息的公式设计(一)建立表格框架根据前文所述表格设计思路，首先建立表格整体框架，各矿井表格的结构应该完全一致（一致性原则）。具体格式如前文图5－105所示。图5－105是各矿井上报表格的样式，也是集团公司汇总表格的雏形。集团公司的汇总表由于需要在各指标行下列示各矿井的数据，故还需在各指标行下都插入四行。插入后表格样式如图5－107所示。在插入行后，表格由原来的12行变成了44行，很不方便查看整体数据，这个问题可以使用分组的分级显示功能来解决：选定集团汇总表格的第6至9行，点击【数据】选项卡的“分级显示”组中的“创建组”按钮，将6－9行组合在一起。然后分别选定11至14行、16至19行……41至44行，重复以上步骤，分别创建组。创建组后，表格如图5－106所示，在表格行号的左侧，增加了“分级显示”的按钮，如果点击“1”，表示显示第一级，则表格将显示如图5－110：

图5－110仅显示汇总行的集团汇总表格如果要显示某一指标的明细数据，则点击该指标前的“+”号即可。到此为止，集团汇总表格的框架和下属矿井上报表格的格式已经确定，下面我们来确定下属矿井每周数据的台账。简单地讲，台账表格将各项指标分别列示在各列，同时将第一列增加一个本周日期区间的字段即可，如前文图5－106所示。(二)编制计算公式我们先来看下属矿井报表的公式设置：1、每周起止日期的自动计算如果每周的起止天数用手工去录入的话，由于每年有51周左右，周数较多，费时费力且容易出错，故应该考虑用公式来实现，用公式自动计算出起止日期，生成类似“1/1-1/3”起止日期的字符串。由于每周的天数都是固定的七天，如果知道每周起始日期是每周的第几天就可推算出每周结束日期的具体日期，具体公式如下：＝每周起始日+（7－每周起始日在本周的第几天）计算日期在本周的第几天可用WEEKDAY函数，此函数是返回某日期为星期几，当使用不同的参数，当参数为2时，返回数字 1（星期一）到数字 7（星期日）；当参数为16时， 返回数字 1（星期六）到数字 7（星期五）。下面以“2014－1－1”为例简单介绍此函数二种常用参数的使用：如果每周起止日期从周一到周日，那么用函数公式“=WEEKDAY("2014/1/1",2) ”可计算出它是本周的第三天；如果每周起止日期从周六到周五，那么用函数公式“=WEEKDAY("2014/1/1",16)”可计算出它是本周的第五天。关于此函数的参数及详细使用请参阅Excel帮助。如前所述，逸凡公司周报统计日期是从上周六到本周五，为简化计算，同时也是为方便统计本月和本年数据，故计算起止日期增加二个辅助列（辅助列化繁为简的思想，请参见第二章第六节），第一列为每周起始日期，第二列为每周截止日期，每年第一周的起始日期为固定的1月1日，即在A2单元格直接输入“2014－1－1”。第一周的截止日期可以用公式计算，B2单元格的公式为：＝A2+7-WEEKDAY(A2,16)但是此公式计算周截止日期有点小问题：当周报遇到月底时应该以最后一天为截止日期，而不是以周自然天数来计算，故第一周结束日期（B2单元格）的公式应该用IF函数来进行逻辑判断：当使用上述公式计算出的日期跨月时，应该采用最后一天，否则才能使用上述公式。故此公式完善为：=IF(MONTH(A2+7-WEEKDAY(A2,16))<>MONTH(A2),EOMONTH(A2,0),A2+7-WEEKDAY(A2,16))然后下拉填充至本年度最后一天即可（EOMONTH函数的用法请参见第四章相关内容）。第二周的起始日期为第一周截止日期的第二天，由于日期型数据可以用简单的加减法，故A3单元格公式为：＝B2+1然后拖动下拉填充公式至本年度最后一周即可。增加C列为每周的序号列，增加D列为起止日期列，D2公式为：=TEXT(A2,"m/d")&"-"&TEXT(B2,"m/d")然后拖动填充柄下拉填充。公式解释：＝TEXT(A2,"m/d")是将A2单元格的日期按“月/日”格式显示，如“1/1”，如果要显示为“01/01”，则使用公式“＝TEXT(A2,"mm/dd")”增加后每周台账表格的结构如图5－111所示：

图5－111增加辅助列后的《每周台账》2、“本周完成”的公式如前面设计思路所述，各矿井上报表格的数据都是从每周台账表格取数计算生成。“本周完成数”可使用VLOOKUP函数来查找引用。为了能直接使用VLOOKUP函数，我们应该直接在C3单元格直接输入每周的起止日期，如“5/17-5/23”，但此日期不能作为表格的列标题，表格的列标题应该为“本周完成(5/17-5/23) ”类似格式。为同时满足二种要求，我们可以将C3单元格设置为自定义格式“"本周完成("@)”，将“5/17-5/23”直接显示为“本周完成(5/17-5/23) ”。这样即可直接使用VLOOKUP查找引用，也可满足作为列标题的需要。通过以上设置后，上报表格C5单元格的公式可以直接使用VLOOKUP函数：=VLOOKUP($C$3,每周台账!$D$2:$L$62,2,0)此公式有点小问题，不能直接下拉填充至C6:C12单元格区域。C6和C7单元格的公式为：=VLOOKUP($C$3,每周台账!$D$2:$L$62,3,0)=VLOOKUP($C$3,每周台账!$D$2:$L$62,4,0)通过观察可以发现A5:A12各项指标的顺序与每周台账表格各项指标的顺序完全一致，C6:C12单元格区域的公式只是引用的列不同，故为了增加公式的可拓展性（可扩展性原则），可以将C5单元格的公式修改完善为：=VLOOKUP($C$3,每周台账!$D$2:$L$62,ROW()-3,0)然后直接拖动填充柄下拉填充即可。在“上报表格”C3单元格手工输入每周的起止日期容易录错，一旦录入错误，“本周完成、本月达成、累计达成”将无法计算出正确值。为避免录入错误，应该使用数据有效性来限制录入的值，并且数据有效性还提供下拉列表，不必通过键盘录入，直接用鼠标点击选取下拉值还可提高工作效率。设置数据有效性的方法：点击【数据】选项卡→点击“数据工具”组中的“数据有效性”按钮→在弹出的“数据有效性”对话框按图5－112进行设置：

图5－112设置数据有效性3、“本月达成”的公式我们知道，要计算“本月达成”可以使用SUMIF来进行条件求和。那如何来构架这个求和的条件呢？我们观察“上报表格”C3单元格和每周台账表格D列，可以发现，直接提取起始日期的月份作为模糊求和的条件即可，即从“5/17-5/23”提取出“5/”，然后对所有以“5/”开头的记录进行求和的结果，即是5月达成数。即求和条件为：LEFT($C$3,FIND("/",$C$3))&"*"因而，上报表格D5单元格公式为：=SUMIF(每周台账!$D$2:$D$62,LEFT($C$3,FIND("/",$C$3))&"*",每周台账!$E$2:$E$62)当然，也可以使用此公式：=SUMPRODUCT((MONTH(每周明细!A2:A62)=--LEFT(C3,FIND("/",C3)-1))*每周明细!E2:E62)C6:C10单元格区域的公式使用以上公式类推即可。计算平均发热量时不能简单的求和，而是应该对当月发热量进行算术平均，故C12单元格的公式为：=SUMIF(每周台账!$D$2:$D$62,LEFT($C$3,FIND("/",$C$3))&"*",每周台账!$L$2:$L$62)/SUMPRODUCT((MONTH(每周台账!A2:A62)=(--LEFT($C$3,FIND("/",$C$3)-1)))*(每周台账!L2:L62<>""))4、“累计达成”的公式要计算累计达成也是要使用SUMIF函数来求和，要统计累计达成肯定是要统计小于等于截止日期指标之和，因而，其条件应该为："<="&--right($c$3,len($c$3)-find("-",$c$3))< span="">公式解释：RIGHT函数即截取C3单元格的截止日期，即“5/17-5/23”中的“5/23”， 截取出来“5/23”为文本数据，不能进行运算，故还需使用二个负号将其转化为日期数据（乘以负壹，再乘以负壹，负负得正。类以于使用“选择性粘贴－运算”将文本数值转换为数字型）。G5单元格累计达成的公式为：=SUMIF(每周台账!$A$2:$A$62,"<="&--right($c$3,len($c$3)-find("-",$c$3)),每周台账!$e$2:$e$62)< span="">G6:G10单元格区域的公式类推，不详述。平均发热量G12单元格的公式为：=SUMIF(每周台账!$A$2:$A$62,"<="&--RIGHT($C$3,LEN($C$3)-FIND("-",$C$3)),每周台账!$L$2:$L$62)/COUNTIF(每周台账!$A$2:$A$62,"<="&--right($c$3,len($c$3)-find("-",$c$3)))< span="">各项指标的完成进度使用类似F5单元格的公式：=IFERROR(D5/E5,"")5、集团公司汇总表格的公式如果下属矿井每月上报的表格名称固定不变，均为“营运周报（甲公司）”这种格式，那么，集团公司汇总表格中各矿井的各项指标可以使用简单的单元格链接，如：D6单元格甲公司开拓进尺指标“本周完成”的公式为：='[营运周报（甲公司）.xlsx]上报'!C5D7单元格乙公司开拓进尺指标“本周完成”的公式为：='[营运周报（乙公司）.xlsx]上报'!C5其他公司其他指标类推。以后每月只需将旧表格替换成最新的表格即可。但为了保证数据的正确引用，需确保以下事项：ü各公司上报表格工作簿名称和工作表名称保持不变，“营运周报（甲公司）”不能改成“营运周报[甲公司]” 等，否则将出现链接错误，无法引用数据。ü各公司上报表格布局不能改变，否则将出现数据引用错位。为了防范上述事项的发生，在将上报表格分发给各矿井之前，应对工作表和工作簿加密码保护，防止下属矿井修改表格的名称和结构（安全性原则）。操作方法：先选定需要录入数据的单元格→点击右键－设置单元格格式→“保护”选项卡→将“锁定”选项前的勾去掉→点击“确定”退出（如图5－113）。

图5－113 锁定单元格然后点击【审阅】选项卡下的“保护工作表”按钮→在弹出的“保护工作表”对话框输入密码，然后确定退出。

图5－114 保护工作表保护好各工作表后，还可设置保护工作簿，操作方法：然后点击【审阅】选项卡下的“保护工作簿”按钮→在弹出的“保护结构和窗口”对话框输入密码，然后确定退出。

图5－115 保护表格的结构和窗口当勾选保护“结构”选项后，将不能进行以下操作：查看已隐藏的工作表；移动、删除、隐藏或更改工作表的名称；插入新工作表或图表工作表；将工作表移动或复制到另一工作簿中。当勾选保护“窗口”选项后，将不能进行以下操作：打开工作簿时，更改工作簿窗口的大小和位置；移动窗口、调整窗口大小或关闭窗口。(三)报表的后续使用如果上报的表格名称是按周数或日期命名，比如类似“甲公司营运周报（5/17-5/23）”的格式，仍可使用简单的单元格链接。但为了能使用查找替换将汇总表表格的公式对上周表格的引用更改为对本周表格的引用。要做到以下二点：1、上报表格的名称应规范，同类型工作簿、工作表应使用统一格式的文件名（一致性原则）。如“甲公司营运周报（5/17-5/23）”、“丁公司营运周报（5/24-5/30）”2、各公司表格应放在同一文件夹下（整体性原则）。在满足以上条件后，在收集齐各矿井下一周最新报表时，将上周各公司的报表删除，将本周各公司上报的表格放入营运周报文件夹，然后打开《集团公司汇总》表格，使用查找替换，比如：查找“（5/17-5/23）”，替换为“（5/24-5/30）”，即可实现报表的快速翻新，具体操作请参阅第二章第三节报表翻新的技术。龙逸凡的图书作品：