巧用函数"平衡"复杂报表

来源： 本站小编 2015/02/10 11:12

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

小编推荐《Office2015下载地址》

Excel函数使用技巧

公务员统计报表示例

【解题思路】

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

【解题方法】

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

Excel函数使用技巧

“人员列表”工作表结构

如要实现对人员列表中乡科级正职人员的计数，可以填写如下公式：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）

Excel函数使用技巧