Excel复杂报表巧用函数平衡
每年年初岁末，年终统计少不了。对于一些复杂报表，如公务员统计报表（图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）
这样“市”工作表公式填写完成。乡镇、女性、少数民族、非党的表格依此类推，快速完成其它工作表的公式填充就可以啦。
绛旓細鍦‥xcel琛ㄦ牸涓紝SUMPRODUCT鍑芥暟鐨勫姛鑳斤紝鏄湪缁欏畾鐨勫嚑缁勬暟缁勪腑灏嗘暟缁勯棿瀵瑰簲鐨勫厓绱犵浉涔橈紝骞惰繑鍥炰箻绉箣鍜銆傛妸涓缁勬暟缁勭湅浣滀竴涓潯浠讹紝澶氫釜鏁扮粍灏辫兘瀹炵幇澶氭潯浠惰鏁般傗滀汉鍛樺垪琛ㄢ濆伐浣滆〃缁撴瀯濡傚浘鎵绀猴紙鍥2锛夈傗滀汉鍛樺垪琛ㄢ濆伐浣滆〃缁撴瀯 濡傝瀹炵幇瀵逛汉鍛樺垪琛ㄤ腑涔＄绾ф鑱屼汉鍛樼殑璁℃暟锛屽彲浠ュ～鍐欏涓嬪叕寮忥細SUMPRODUCT((浜...
绛旓細鍦Excel涓瀹炵幇璇曠畻骞宠 鐨勮繃绋嬬浉褰撶洿瑙傘傞鍏堬紝浣犻渶瑕佸湪鍊熻捶鍙屾柟鐨勯噾棰濆垪椤堕儴锛浣跨敤姹傚拰鍏紡锛堜緥濡係UM鍑芥暟锛夎绠楁瘡涓鏂圭殑鎬诲拰銆傝繖鏍凤紝浣犲氨鍙互寰楀埌涓や釜鍚堣鏁帮紝鍒嗗埆浠ｈ〃璧勪骇鍜岃礋鍊虹殑鎬昏銆傛帴鐫锛杩愮敤鏉′欢鏍煎紡鍔熻兘鏉ュ寮哄彲璇绘с備綘鍙互閫夋嫨"鏍煎紡鍖栧崟鍏冩牸"锛岀劧鍚庤缃竴涓潯浠讹紝姣斿褰撲袱涓悎璁℃暟涓嶇浉绛夋椂锛屽崟鍏冩牸...
绛旓細=IF(AND(A2+A3=A4+A5+A6,A2+A3=A20+A30),"鐩哥瓑","涓嶇浉绛")
绛旓細鍒╃敤鑸嶅叆鍑芥暟ROUND锛堬級锛屽皢鎵寰楃◣鐢卞舰寮忎笂鐨勪袱浣嶅皬鏁板彉涓哄疄闄呯殑涓や綅灏忔暟锛屽嵆鍙В鍐宠繖涓闂锛屽嵆灏嗏滄墍寰楃◣鈥漃3涓殑鍏紡鏀逛负锛氣=IF(O3>1500,ROUND((O3-1500)*0.1+500*0.05,2),IF(O3>1000,ROUND((O3-1000)*0.05,2),0))锛屽苟灏嗚鍏紡澶嶅埗鍒扳淧4锛歅12鈥濄傚埌姝わ紝鐢excel灏卞埗浣滃嚭浜嗕竴寮...
绛旓細鐢ㄤ竴涓狪F鍑芥暟灏卞彲浜嗗晩 鍙鎶婃í鐨勬牳绠楀ソ銆佸垪绔栨牳绠楀ソ锛屽垽鏂叾锛氬亣濡傜浉绛夊氨骞宠 銆佸惁鍒欏氨涓嶅钩琛锛屽叕寮忎妇渚嬶細=IF(?=?,"","鏈〃涓嶅钩琛★紝璇烽噸鏂板鏍")
绛旓細濡傛灉鍐嶇敤鏉′欢鏍煎紡鎴朓F鍑芥暟瀵硅繖涓や釜鍚堣鏁拌繘琛屽垽鏂紝濡傛灉鏁版嵁涓嶄竴鏍峰氨鐢ㄩ啋鐩殑棰滆壊鎴栨枃瀛楄繘琛屾彁绀 濡備笂鍥撅紝绗簩涓孩鑹叉柟妗嗗唴灏辨槸鍊熻捶鍙屾柟姹傚拰鐨勬暟鎹紝绗竴涓氨鏄敤IF鍑芥暟鏉ュ垽鏂繖涓ゆ暟鎹槸鍚︾浉绛夛紝杩斿洖瀵瑰簲鐨勬樉绀烘枃鏈紝鍐嶉厤鍚堟潯浠舵牸寮忚繘琛屾彁绀洪鑹茬殑璁剧疆銆
绛旓細A3=1,A4=1),"骞宠 ","")鎰忔濇槸锛氬鏋淎1銆丄2銆丄3鍙夾4杩欏洓涓崟鍏冩牸閮界瓑浜1锛屽氨鍦ㄦ墍鍦ㄥ崟鍏冩牸涓樉绀衡滃钩琛♀濓紝鍚﹀垯灏变粈涔堥兘涓嶆樉绀恒傚綋鐒讹紝浣犺繕鍙互鍦ㄦ渶鍚庣殑涓や釜鑻辨枃鐘舵佺殑鍙屽紩鍙蜂腑鍔犲叆濡傛灉鏄滃惁鍒欌濈殑璇濓紝瑕佹樉绀轰粈涔堝唴瀹癸紝濡傗滀笉骞宠 鈥濈瓑锛屽鏋滆鏄剧ず鐨勬槸鏁板艰屼笉鏄枃鏈紝灏变笉鐢ㄥ姞鍙屽紩鍙蜂簡銆
绛旓細涓:濡傛灉鏁版嵁濡傚浘:"J1鍗曞厓鏍煎叕寮忎负:=IF(SUM(H:H)-SUM(I:I)=0,"骞宠 ","涓嶅钩琛")浜屽鏋滄暟鎹鍥:"J1鍗曞厓鏍煎叕寮忎负:=IF(SUMPRODUCT((H2:H10000="鍊")*I2:I10000)-SUMPRODUCT((H2:H10000="璐")*I2:I10000)=0,"骞宠 ","涓嶅钩琛")
绛旓細鍏舵锛屽浜庡憳宸ュ伐鍙风鐞嗭紝TEXT鍑芥暟鍙互甯姪鏍煎紡鍖栧苟娣诲姞閮ㄩ棬淇℃伅銆備緥濡傦紝鍏紡=C2&TEXT(A2,"000")锛屽彲浠ュ皢閮ㄩ棬鍚嶇О鍜屼笁浣嶆暟鐨勫伐鍙峰悎骞讹紝涓嶈冻涓変綅鏃惰嚜鍔ㄨˉ闆躲傛帴涓嬫潵锛孴EXT鍑芥暟杩樿兘鍦ㄧ泩浜骞宠 鍒ゆ柇涓彂鎸ヤ綔鐢紝閫氳繃鏍煎紡浠ｇ爜"鐩堝埄0.00涓;浜忔崯0.00涓;骞宠 ;"锛屾牴鎹敹鍏ュ拰鏀嚭鏁版嵁蹇熷垎绫绘樉绀恒傛棩鏈熺殑鐗规畩...
绛旓細鍙互閫氳繃Excel鐨鍑芥暟鏉ヨ绠楃泩浜骞宠 鐐癸紝鍏蜂綋鏂规硶濡備笅锛1. 鎵撳紑Excel锛屽苟杈撳叆琛ㄥご锛屽寘鎷滈攢鍞暟閲忊濄佲滄瘡涓骇鍝佹垨鏈嶅姟鐨勪环鏍尖濄佲滄瘡涓骇鍝佹垨鏈嶅姟鐨勫彲鍙樿垂鐢ㄢ濆拰鈥滃浐瀹氳垂鐢ㄢ濄2. 鍦ㄨ〃鏍间腑杈撳叆鏁版嵁锛屽垎鍒緭鍏ユ瘡涓骇鍝佹垨鏈嶅姟鐨勪环鏍笺佸彲鍙樿垂鐢ㄥ拰鍥哄畾璐圭敤锛屾渶鍚庤緭鍏ラ攢鍞暟閲忓垪銆3. 鍦ㄧ泩浜忓钩琛＄偣鍒椾腑杈撳叆鍏紡...
