excel多条件查找与多条件计数.doc

excel 多条件查找和多条件计数姓名 部门 工资 张三 安全部 2000 李四 安全部 1500 王五 后勤 1000 马六 接待 1500 现在要求做出下面的表：按部门分类后 看了你的表格，用SUMPRODUCT()可以多条件求和及计数=sumproduct((A:A=条件1)*(B:B=条件2)*(C:C=条件3)...) 不知道你的具体情况，所以只能这样了，不会可了HI我。 在统计表中的D2中输入： =IF(MATCH(A2,北京!B:B,0)=MATCH(B2,北京!C:C,0),SUMPRODUCT((北京!B:B=A2)*(北京!C:C=B2)*(北京!D:D)),) 往下复制公式即可。 验证通过。 假如ABC分别为姓名、部门、工资，第3至10000行是数据；E至S列是各项的统计（E为部门，F为高于平均值人数，依次类推），E3:S5是数据统计。 1、高于平均值人数： 在F3中输入 =SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C10000AVERAGE($C3:$C10000))) 2、低于平均值人数： 在G3中输入 =SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C10000AVERAGE($C3:$C10000))) 3、低于平均值25%的人数： 在H3中输入 =SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C1000025%*AVERAGE($C3:$C10000))) 4、在平均值25%-50%之间人数： 在I3中输入 =SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C1000050%*AVERAGE($C3:$C10000)))-SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C1000025%*AVERAGE($C3:$C10000))) 5、在平均值50%-75%之间人数： 在J3中输入 =SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C1000075%*AVERAGE($C3:$C10000)))-SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C1000050%*AVERAGE($C3:$C10000))) 6、大于平均值75%人数： 在K3中输入 =SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C1000075%*AVERAGE($C3:$C10000))) 7、最高人员信息—姓名： 在L3中输入 =IF(COUNTIF($C3:$C10000,MAX(IF($B3:$B10000=$E3,$C3:$C10000)))(1+COLUMN(A1))/2,,INDEX($A3:$A10000,SMALL(IF(($C3:$C10000=MAX(IF($B3:$B10000=$E3,$C3:$C10000))),ROW($3:$10000)-2), (COLUMN(A1)+1)/2))) 这是数组公式，输入完毕后要按Ctrl+Shift+Enter结束。 8、最高人员信息—工资： 在M中输入 =IF(L3=,,MAX(IF($B3:$B10000=$E3,$C3:$C10000))) 这是数组公式，输入完毕后要按Ctrl+Shift+Enter结束。 考虑到工资最高的人员有可能有并列第一的，该员工的姓名和工资可能有多个，可以选中L3:M3,向右拉，即可显示并列最高工资的人员信息。如果有更多，这NO列中间就要多插入几列了。 如果要求最低工资人员信息，将L3和M3的公式中的最大值MAX，改为最小值MIN即可。 9、低于均值25%人员信息—姓名： 在P3中输入 =IF(SUMPRODUCT(($B3:$B10000=$E3)*($C3:$C1000025%*AVERAGE($C3:$C10000)))(1+COLUMN(A1))/2,,INDEX($A3:$A10000,SMALL(IF(($C3:$C1000025%*AVERAGE($C3:$C10000)),ROW($3:$10000)-2), (COLUMN(A1)+1)/2))) 这是数组公式，输入完毕后要按Ctrl+Shift+Enter结束。 10、低于均值25%人员信息—姓名： 在Q3中输入 =IF(P3=,,INDEX($C3:$C10000,SMALL(IF(($C3:$C1000025%*AVERAGE($C3:$C10000)),ROW($3:$10000)-2), (COLUMN(A1)+1)/2))) 这