复杂报表巧用函数“平衡”

余翔鑫2015-02-10 09:02应用 标签:报表 函数

每年年初岁末,年终统计少不了。对于一些复杂报表,如公务员统计报表(图1),由于存在表内及表间平衡,填写一不小心,报表就不平衡了,用手工统计又实在费时费力。而用Excel数据透视表又无法完成多种分类统计,如学历、年龄等。

1406exlph1

公务员统计报表示例

【解题思路】

其实有一种简单的方法,只要维护好“人员列表”工作表即可,其他工作表中的统计报表自动生成并确保平衡。今后如有人员及人员信息变动,只要更改“人员列表”工作表,其他工作表中的统计报表自动更改并平衡,一劳永逸,以不变应万变。

【解题方法】

在Excel表格中,SUMPRODUCT函数的功能,是在给定的几组数组中将数组间对应的元素相乘,并返回乘积之和。把一组数组看作一个条件,多个数组就能实现多条件计数。“人员列表”工作表结构如图所示(图2)。

1406exlph2

“人员列表”工作表结构

如要实现对人员列表中乡科级正职人员的计数,可以填写如下公式:SUMPRODUCT((人员列表!$E$2:$E$1000="乡科级正职")*1)。乘1的目的是要把逻辑判断值变为数值,是乡科级正职的为1,不是的为0,求和以实现计数,1000为人员列表预设计算人数,多于1000人可在人员列表中插入行即可增加。

如果要多一条件则加上一组条件,如要实现对人员列表中乡科级正职大学本科人员的计数,则填上如下公式:SUMPRODUCT((人员列表!$E$2:$E$1000="乡科级正职")*(人员列表!$G$2:$G$1000="大学本科"))。再加条件,依此类推。

在工作表“县”中依次输入公式。为提高输入公式的效率,可以引用表格标题行和列,如大学本科、乡科乡正职等,这样填写一行公式后可以拖动公式。如在“县”工作表中乡科级正职大学本科单元格可以填写如下公式:SUMPRODUCT((人员列表!$E$2:$E$1000=$A13)*(人员列表!$G$2:$G$1000=L$3)*(人员列表!$F$2:$F$1000="县"))。$A13等于乡科级正职,L$3等于大学本科,这个公式可以向上下左右拖动,把所有学历单元格都填好。

填写其他工作表公式。由于市、县、乡镇公务员基本情况表基本一样,在填写完成“县”工作表后,可以把“县”工作表公式全部复制到“市”工作表,然后用查找替换功能把所有计数条件是县的全部替换成地市(图3)

1406exlph3

这样“市”工作表公式填写完成。乡镇、女性、少数民族、非党的表格依此类推,快速完成其它工作表的公式填充就可以啦。