《Excel财务日常数据与实例讲解》_从理论到实践的一大步_5.3 制作部门费用表

【摘要】 本书摘自《Excel财务日常数据与实例讲解》一书中第5章，第3节，作者是韩小良、贾春雷。

5.3 从科目余额表直接制作部门费用表

很多情况下会从系统导出明细科目余额表，而且这样的明细表数据很不规范， 但也很有规律，此时可以使用函数直接从导出的明细表中制作需要的报告。

5.3.1 费用与部门保存在一列的情况

案例5-5

图5-64所示是一个从系统导出的管理费用科目明细表，费用名称和部门名称保存在一 列，现在的任务是：如何在不实施分列的情况下，制作各个部门、各个费用的汇总表?汇总 表结构如图5-65所示。

在单元格C3 输入下面的公式，往右往下复制，就得到各个部门、各个费用数据的汇总表， 如图5-66所示。

=IFERROR(VLOOKUP(CS2,OFFSET (科目余额表!SBS1,MATCH(SB3,科目余额表!SB:$B,0)- 1,,COUNTIF(科目余额表!$A:SA,INDEX(科目余额表!$A:$A,MATCH($B3,科目余额

表!$B:$B,0)}),2),2,0),"")

这个公式看起来很长，其实逻辑思路是很简单的：由于每个费用下各个部门的数据区域 在不同的位置，而在每个区域中，部门在左，结果在右，因此基本思路是想办法取出每个费 用的数据区域，然后再使用VLOOKUP 函数查找每个部门的数据。

这里的难点是如何引用每个费用的数据区域，每个费用区域的宽度是2列，但高度(行数) 是不同的，行数可以通过A 列科目代码个数来统计，也就是说，先查找每个费用对应的科目 代码，再用COUNTIF 统计该代码的个数。这样，就可以使用OFFSET 函数来引用每个费用 的数据区域了。

5.3.2 费用与部门保存在两列的情况

案例5-6

图5-67所示是另外一种情况：费用和部门分别保存在两列，并且科目代码仅仅是费用所 在的行。那么，如何制作每个部门、每个费用的汇总表呢?

一个简单的方法是，先对A 列 和B 列填充空单元格，如图5-68所示。然后使用SUMIFS 函数进行汇总，公式如下，如图5-69所示。

=SUMIFS (科目余额表!$D:SD,科目余额表!$B:$B,SB3,科目余额表!SC:$C,C$2)

但是，如果不想填充，而是直接利用原始数据进行汇总呢?

此时，思路仍然是想办法获取每个费用的数据区域，并使用VLOOKUP 函数从每个费用 区域中查找各个部门的数据。

将汇总表的各个费用项目次序设置为与明细表一样(这一点很重要),如图5-70所示。

单元格 C3 的公式如下。

=IFERROR(VLOOKUP(CS2,OFFSET (科目余额表!$C$1,MATCH($B3, 科目余额表!$B$2: SBS1000,0),IF($B3="差旅费",100,MATCH(SB4, 科目余额表!$BS2:SBS1000,0)-MATCH($B3, 科目余额表!$BS2:SB$1000,0}),2),2,0),"")

这个公式很好理解：统计出每个费用项目的位置，两个位置相减就是某费用区域的行数。 不过，最后一个项目(这里是差旅费)就不能相减，直接设置一个较大的行数即可。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。