Excel读书笔记27——费用分析报表——日期函数的多功能应用示例
在财务管理及分析工作中，费用的管理和分析是一个重要的组成部分。费用看似简单，但是却涉及费用项目、归集部门、列支期间等多个分析维度。一般情况下，决策者至少需要了解各部门各个费用项目的当年累计发生额、当期发生额和一些通过查询方式获取的对比信息。
本节中，我们就以逸凡公司2014年一季度管理费用为例，讨论如何用Excel来设计一套高效实用的“费用报表”（参见示例文件“表5-2费用报表”）。
一、基本框架与功能展示
“费用报表”由管理费用明细清单（简称“明细清单”，见图5-16）、管理费用累计报表（简称“累计报表”，见图5-17）、管理费用本月报表（简称“本月报表”，见图5-18）、管理费用期间查询报表（简称“期间查询报表”，见图5-19）和管理费用部门查询报表（简称“部门查询报表”，见图5-20）组成。此外，为了提高“费用报表”的拓展性，我们还需要一个基础设置表，稍后我们再让它出场。
细清单是典型的清单型报表。它实际上就是一个逐笔记录费用的流水账，该表A～I列的信息一般可以来源于财务记账系统中相关明细表的导入。J列及以后的信息属于设置公式后自动生成的项目，它们将服务于报表的生成。
明由于不同财务记账系统的格式差异，该表的设计也是因人而异的。但是应尽量避免录入字段与公式字段的混搭，以方便录入信息时一次性到位，提高信息录入的效率。
文章图片
图5-16 明细清单
文章图片
图5-17 累计报表（自动生成）
文章图片
图5-18 本月报表（自动生成）
文章图片
图5-19 期间查询报表（输入查询参数后自动生成）
文章图片
图5-20 部门查询报表（输入查询参数后自动生成） 我们的目标是：只需要在明细清单中手工录入期间、部门、核算科目代码、核算费用科目以及金额（单位：元）后，就可以自动实现以下功能。
1.主要信息功能
自动计算生成本年累计费用及当期费用的费用项目与归集部门组合二维报表。
2.辅助信息功能
（1）用户录入待查询起始期间后，自动计算生成该期间累计费用项目与归集部门组合二维费用报表。
【Excel读书笔记27——费用分析报表——日期函数的多功能应用示例】（2）用户录入待查询部门后，自动计算生成该部门累计费用项目与月度组合二维费用报表。如果查询部门参数为空值，则将统计整个公司的费用信息。
3.逻辑校验功能
（1）自动校验各类报表与费用明细清单的数据是否匹配。
（2）自动提示无效费用科目及无效归集部门。
二、基本前提及假设
1.报表费用科目（简称“报表科目”）以二级科目进行列报且以万元为单位
一般情况下，财务核算的费用科目体系会根据核算管理需要设立多级明细科目，并以元为单位进行金额核算。但是报表作为一个具有高度概括功能的经营管理工具，不可能也没有必要进行过于微观的反映，所以本案例中的“费用报表”仅列示费用的二级科目，且金额以万元为单位。
2.费用按自然月度归集
费用的统计、查询均仅支持自然月度汇总。如果想统计或查询诸如2014年1月20日～2014年2月19日的费用情况，需对公式进行升级处理，由于其不具备广泛应用性，故不属于本节涉及的内容。
三、注意事项
1.拿来主义的兼容性
前面已经提到，“费用报表”中明细清单表的主要信息都来源于财务记账系统导出的信息，但是我们在享受拿来主义的便捷时，也要注意其兼容性。很多系统导出的Excel表，格式都不那么规范，诸如数据都是文本格式之类的不胜枚举。所以，在将系统中导出的信息粘贴到明细清单表之前，应首先检查其格式是否达标，对于不合格的格式应及时予以纠正。否则，一旦出现各种不兼容的情况，再完美的公式设计也会失效。
2.多条件求和公式与数据透视表的共通性
本案例中的核心元素是多条件求和。在多条件求和时，除了使用SUMIFS等公式外，还可以通过数据透视表来实现。本节应用SUMIFS函数来实现相关功能。
四、知识点装备
在阅读本节下面的内容前，请各位读者朋友首先确认大脑中是否已经基本装备了图5-21中的相关知识点。
文章图片
图5-21 相关知识点 五、主要信息的公式设计方法
在对“费用报表”的框架、功能和相关注意事项有了大致的认识后，我们就通过逸凡公司2014年一季度管理费用的案例来讨论“费用报表”的设计了。
【案例5-2】逸凡公司管理费用（科目代码6602）核算按明细科目及部门核算项目进行归集。此外，技术部发生的期间费用通过研发支出（费用化）（科目代码5301.01）科目进行核算，且在月末将研发支出（费用化）科目的所有余额全部结转至管理费用。逸凡公司管理费用及研发支出（费用化）的科目体系如图5-22所示。
文章图片
图5-22 费用科目表（简表） 此外，纳入管理费用核算的部门有：总经办、财务部、人力资源部、行政部、技术部和采购部。逸凡公司2014年一季度发生的费用流水账（简表）如图5-16中A3:I26单元格区域所示。
结合“费用报表”结构，相关设计方法如下。
1.明细清单表的公式设计方法
（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）。这样，我们就可以根据该对照表轻松实现明细清单中报表科目代码与报表科目的联姻了。
基础设置表里的部门信息又是干什么用的呢？先不着急。稍后会有介绍。现在我们先将明细清单表中报表科目的公式完成。
K4单元格的公式为：=IF(J4="","",VLOOKUP(J4,基础设置! $B$4:$C$23,2,0))
执行列填充后，即可获得与报表科目代码匹配的报表费用科目（见图5-25）。
文章图片
图5-24 基础设置表
文章图片
图5-25 报表科目代码的公式 （2）以万元表示金额（L4:L27单元格区域）。
L4单元格的公式为：=I4/10000
执行列填充后，即可将费用金额转换为万元。注意，为避免大量四舍五入积少成多引起合计金额的重大偏差，此处一般不使用ROUND函数进行小数点限定。
（3）累计费用合计（I2单元格）。
不是说清单型报表不应该有求和项吗？那是说的不要在清单表的主体框架（A3:N27单元格区域）中出现求和。我们把求和项放在清单表主体框架之外的顶部，就没有任何不良影响了。
I2单元格公式为：=SUM(L4:L27)
（4）本月费用合计（L2单元格）。
本月费用合计就是只针对当前会计期间（F2单元格）的费用进行求和。一般情况下，“费用报表”的最大累计期间为一个自然年度，所以我们就可以直接根据期间来进行识别了（见图5-26）。
L2单元格的公式为：=SUMIF($B$4:$B$27,MONTH(F2),L4:L27)
文章图片
图5-26 本月费用合计的公式 为使报表数据更具有宏观性，我们假定“费用报表”中所有统计数据均以万元为单位。如果需要以元为单位，只需把相关公式中的L替换为I即可。
2.累计报表的公式设计方法
累计报表的功能是统计各部门各项费用的本年累计发生额。这显然是一个交给SUMIFS函数来解决的多条件求和的问题。
需要注意的是，本案例中的四个报表都属于二维报表。这就意味着在设置公式时，我们就需要考虑相对复杂一点的区域填充（先行填充，再列填充）了。
C4单元格的公式为：=SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,
$B4,明细清单!$E$4:$E$27,C$3)
执行区域填充后，累计报表的统计数据就将全部生成（见图5-27）。
文章图片
图5-27 累计报表的区域填充公式 最后，我们来完成费用科目及部门费用的合计公式设置。还记得第一章中那个自动求和的案例吗？现在可以练练手了，此处不再赘述。
在进入下一张报表公式设计方法的分析之前，我们先来继续讨论一下本节注意事项部分中提到的拓展性问题。
前面已经提及，本案例中的部门和费用科目都存在着拓展性的需求，如果每次调整我们都一个一个地在每张表中进行增加或删减，就比较费事。所以，我们可以考虑为相关信息建立一个基础设置表进行统一维护，同时使用“=”自动关联到各个报表对应的位置上。这样，只要对现有信息进行变更，报表就能立即同步完成调整。该方法不仅能提高工作效率，更有助于在整套报表中规避同物不同名的情况，而且操作也极其简单，所以非常值得使用。
在基础设置表中，我们编制的报表科目代码与报表科目对照表，不仅仅是为VLOOKUP函数服务的，同样也可以为报表同步关联费用科目服务。同理，部门信息在基础信息表中出现，也是为了拓展性的需要。此外，我们还可以将基础信息表中的期间参数与明细清单和各报表进行同步关联，以实现期间变更的批量化操作。
现在回到“费用报表”的问题上来，在报表中费用科目的同步填充非常顺手。
B4单元格的公式为：=IF(基础设置!C4="","",基础设置!C4)
执行列填充后，即可实现费用科目的同步（见图5-28）。
文章图片
图5-28 通过基础设置表同步费用科目信息 部门的关联就比较麻烦了，它在报表中的列示是横向的，与其在基础设置中的纵向列示方向性上不匹配，所以肯定是无法直接实现快捷的关联填充的。怎么办？难道需要一个一个地去关联？当然不用，下面我们就来看看如何利用第三章第一节提到的替换功能和选择性粘贴中的转置功能来攻克这个别扭的堡垒。
（1）我们在累计报表中找一块空地，先按纵向方式关联填列。
C17单元格的公式为：=基础设置!A4
列填充至C24单元格后，即可同步关联费用归集部门（见图5-29）。
文章图片
图5-29 纵向同步部门 （2）将上述单元格中的“=”替换为一个在公式中未出现过的字符（比如“X”）。此时，由于构成公式的必要元素“=”被篡改，导致其公式失效，在单元格中只能以文本风格显示（见图5-30）。
文章图片
图5-30 将公式中的“=”替换为“X” （3）复制上述单元格，并将其以【选择性粘贴】→【转置】的方式粘贴到报表中的C3:K3单元格区域（见图5-31）。
文章图片
图5-31 将被破坏的公式选择性粘贴到部门区域 （4）将部门区域单元格中的“X”替换为“=”。公式又重新恢复，关联设置顺利完成（见图5-32）。
文章图片
图5-32 将被破坏的公式还原完成部门同步公式设置 上述步骤是一个灵活运用“替换”结合公式对“=”的依赖性特点实施“偷梁换柱”出奇招的案例。它再一次告诉我们，对于Excel的一些应用或函数功能，我们除了知道其基本应用外，还需要发挥想象力挖掘其潜在的组合运用价值。
最后再啰唆一句，我们还应该考虑到相关信息未来的增加需求，所以在每个关联信息的维护区域留出一定的待定席位，为以后的拓展留好余地。
3.本月报表的公式设计方法
本月报表的功能是统计各部门各项费用在报表日当月的发生额。和刚刚讨论完的累计报表相比，其实就是在多条件求和时，再加上一个期间的控制。即除了累计报表公式中限定的条件外，还要求费用发生的期间必须是报表日所在的期间。于是，我们就可以很容易得出相关公式了。
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 本月报表的区域填充公式 六、辅助信息的公式设计方法
前面我们已经完成了“费用报表”中累计报表以及本月报表的公式设计，但是这两个报表仅仅提供了累计和本月两种条件下的信息，明显不能满足费用管理中的查询及分析需求。所以，我们还需要编制几个常用的辅助性查询报表，以提升报表的使用价值。
1.期间查询报表的公式设计方法
当我们需要查询一个任意连续期间（本年累计除外）的费用累计情况时，就有必要在本月报表的基础上进行升级，即把仅能统计单月费用升级为可统计任意连续月份费用。功能强大了，但是公式却只需要做少量的补充即可。
假设我们将期间查询报表的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))
执行区域填充后，用户只需输入查询的起止期间，即可自动生成该期间累计费用报表（见图5-34）。
文章图片
图5-34 期间查询表的区域填充公式 2.部门查询表的公式设计方法
针对整个公司或者某个部门的费用进行月度对比分析也是费用管理及分析工作中的一个重要方式。所以，我们也有必要设计一个可以对查询主体的各月费用进行直观显示的报表。
相对于前面三张报表，部门查询表看似模版变了脸，但是公式逻辑依然是万变不离其宗的多条件求和。需要注意的是，由于我们要求当查询部门为空值时，自动计算生成所有部门（公司整体）各月费用的汇总数据。所以，在多条件求和前，先得进行一个两条件判断。我们就分两步来攻克这个公式。
第一步：I2单元格为空值（所有部门费用查询）。
如果I2单元格为空值，则应该统计所有部门费用，此时的多条件就只需要考虑费
用项目与期间的匹配。
C4单元格的第一步公式为：
=IF($I$2="",SUMIFS(明细清单!$L$4:$L$27,明细清单!$K$4:$K$27,$B4,明细清单!$B$4:$B$27,MONTH(C$3)),进入第二步)
第二步：I2单元格不为空值（单个部门费用查询）。
进入第二步，说明是对具体的某个部门进行查询，所以除了第一步中应满足的条件外，还需要增加一个部门匹配的条件。
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，图5-35a为查询财务部费用汇总，图5-35b为查询所有部门费用汇总）。
文章图片
图5-35 部门查询表的区域填充公式
文章图片
图5-35 （续） 需要友情提醒的是，为了确保输入查询部门的准确性，建议对I2单元格做数据有效性的序列设置。
此外，用户还可以根据具体的管理需求，建立预算报表并衍生出各类预算与实际发生额对比报表等。思路都大同小异，有兴趣的读者朋友可以自行研究。
七、逻辑校验信息的公式设计方法
1.各类报表与明细清单的逻辑校验
（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）。2）费用合计校验。
L15单元格的公式为：
=IF(ROUND(SUM(明细清单!L4:L27)-L14,2)=0,"OK","偏差"&ROUND
(SUM(明细清单!L4:L27)-L14,2))
文章图片
图5-36 累计报表的各部门校验公式 （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,MO NTH($G2))-C14,2))
执行行填充至K15单元格即可完成上述校验公式的设置（见图5-37）。2）费用合计校验。
L15单元格的公式为：
=IF(ROUND(SUM(明细清单!L4:L27)-L14,2)=0,"OK","偏差"&ROUND(SUM(明细清单!L4:L27)-L14,2))
（3）期间查询报表与明细清单的逻辑校验。
期间查询报表与明细清单校验的逻辑与累计报表基本一致，只是在明细清单中校验的期间仅限于查询期间内。
文章图片
图5-37 本月报表的各部门校验公式 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))
执行行填充至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))
（4）部门查询表与明细清单的逻辑校验。
部门查询表与明细清单校验的逻辑是：部门查询表中所查询的部门（或所有部门）的各月（即累计）费用合计数=明细清单上记录的属于该查询部门（或所有部门）的对应期间（即累计）费用合计数。
1）各期间费用校验。
第一步：I2单元格为空值（所有部门费用查询）。
如果I2单元格为空值，说明是统计所有部门费用。此时，只需要对月份进行匹配。为了公式格式统一，我们这次杀鸡用牛刀，让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)),进入第二步)
第二步：I2单元格不为空值（单个部门费用查询）。
进入第二步，说明是对具体的某个部门进行查询，所以除了第一步中应满足的条件外，还需要增加一个部门（I2单元格）匹配的条件。
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函数粉墨登场。
第一步：I2单元格为空值（所有部门费用查询）。
O15单元格的第一步公式为：
=IF(I2="",IF(ROUND(明细清单!I2-O14,2)=0,"OK","偏差"&ROUND(明细清单!I2-O14,2)),进入第二步)
第二步：I2单元格不为空值（单个部门费用查询）。
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 部门查询表的合计校验公式 上述各表仅仅是对部门数据或各期间数据进行了校验，如果还需要对各报表中各项费用进行校验，也可参照上述逻辑设计相关公式，本文不再赘述。
2.费用科目及归集部门有效性及同步性校验
上面我们完成了各类报表数据与费用明细清单数据的逻辑校验，校验通过当然皆大欢喜。但是一旦校验出现偏差，如果慌不择路不讲方法乱找，就会很悲催了。所以纠错也是需要先分析原因。
如果校验出现总数与明细数据存在误差，首先基本可以断定明细清单的金额是正确的，因为明细清单的数据是所有报表的源头。而各报表是根据明细清单中的报表科目、部门等信息进行若干条件匹配后自动计算生成数据的。就算明细清单数据有误，也只是导致各类报表数据连带犯错，并不能导致明细清单数据与报表数据出现偏差。所以，出现偏差的最大可能性是明细清单中报表科目的关键字段和报表产生了脱节。例如，某个报表科目（或部门）在明细清单里有，但是在报表中没有，这样报表自然无法统计该报表科目（或部门）的金额，于是偏差就出现了。
找到了症结就好对症下药了。
除了金额外，明细清单中参与报表计算的关键字段包括期间、部门和费用科目。期间通过筛选很容易排查错误。所以，我们重点需要对明细清单中的报表科目和部门进行有效性校验。由于各报表中的费用项目和部门信息都来自基础设置表，所以，我们的校验逻辑就是看看明细清单中使用的报表科目和部门，是否在基础设置中出现。
假设数据有效时显示“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）。
E4单元格的公式为（部门同步性校验）：
=IF(A4="","",IF(ISERROR(HLOOKUP(A4,累计报表!$C$3:$K$3,1,0)),"未同步","OK"))
执行列填充后即可完成部门同步性校验公式的设置（见图5-44）。
文章图片
图5-43 科目同步性的校验公式
文章图片
图5-44 部门同步性的校验公式
推荐阅读
- EffectiveObjective-C2.0|EffectiveObjective-C2.0 笔记 - 第二部分
- “成长”读书社群招募
- Android中的AES加密-下
- 【0212读书感悟】
- 【读书笔记】贝叶斯原理
- 【韩语学习】（韩语随堂笔记整理）
- 2018-07-27读书心得
- 人性的弱点-笔记
- 读书笔记:博登海默法理学|读书笔记:博登海默法理学 —— 正义的探索（1）
- D034+3组苏曼＋《写作这回事》读书笔记