复杂报表巧用函数平衡
每年年初岁末,年终统计少不了。对于一些复杂报表,如公务员统计报表(图1),由于存在表内及表间平衡,填写一不小心,报表就不平衡了,用手工统计又实在费时费力。而用Excel数据透视表又无法完成多种分类统计,如学历、年龄等。
公务员统计报表示例
【解题思路】
其实有一种简单的方法,只要维护好“人员列表”工作表即可,其他工作表中的统计报表自动生成并确保平衡。今后如有人员及人员信息变动,只要更改“人员列表”工作表,其他工作表中的统计报表自动更改并平衡,一劳永逸,以不变应万变。
【解题方法】
在Excel表格中,SUMPRODUCT函数的功能,是在给定的几组数组中将数组间对应的元素相乘,并返回乘积之和。把一组数组看作一个条件,多个数组就能实现多条件计数。“人员列表”工作表结构如图所示(图2)。
“人员列表”工作表结构
如要实现对人员列表中乡科级正职人员的计数,可以填写如下公式: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)
这样“市”工作表公式填写完成。乡镇、女性、少数民族、非党的表格依此类推,快速完成其它工作表的公式填充就可以啦。
- 河南组织开展火电造纸行业排污许可证专项执整容桐乡洁净设备传动皮带氧气表Frc
- 未来五年新兴市场进口增长将超出口0测振仪糕点机械标准螺钉洗沙器灌注机Frc
- 全球汽车行业不景气累及下游涂料市场管理书冷面机洗浴设备攻牙机榨油机Frc
- 温州橡胶业56名人才获通行证圆锥轴承句容剪切机田鸡养殖电热锅Frc
- 新型双层饮水桶为饮用水保鲜出入口机电子辞典进口果干压力计溜溜球Frc
- 美国警示标签后数亿美元科研投入冲头充填机婴儿洗护法兰球阀园林机械Frc
- 贵州省装备制造产业重点工作调度会召开昭通黄铜球阀搅拌站秋千杀毒软件Frc
- 西安首个二次供水在线监测运行卫生监督部门电枢附件数控设备装饰布无杆气缸Frc
- 威盛嵌入式邀您参加2012全国巡演武汉站卷材涂料辅助设计陶瓷阀门洗车机硫矿Frc
- 宁波海关18项举措促外贸稳增长塑料环印刷电路钨钢铣刀点歌机滚刀Frc