Excel多条件求和及三种方法.docVIP

窗体底端 Excel多条件求和的三种方法 2004-10-19 16:09作者：陈秀峰 原创出处：天极网责任编辑：Shiny - 　　图1是一种典型的员工基本情况登记表，现在我们要求统计性别为“男”性、职称为“工程师”的员工的工资总和，可以用下面三种方法来实现。文章末尾提供.xls文件供大家下载参考。 （图片较大，请拉动滚动条观看） 　　方法一、自动筛选法 　　1、打开登记表，选中数据区域任意一个单元格，执行“数据→筛选→自动筛选”命令，进入“自动筛选”状态（此时，每个列标题右侧出现一个下拉按钮，参见图1）。 　　2、选中J103（此处假定有100名员工）单元格，输入公式：=SUBTOTAL(9,J3:J102)，用于统计基本工资数据。 　　3、先点击“性别”列右侧的下拉按钮，在随后弹出的下拉列表（如图2）中选择“男”；再点击“职称”列右侧的下拉按钮，在随后弹出的下拉列表（参见图2）中选择“工程师”。 　　符合条件的数据被筛选出来，工资之和出现的J103单元格中（如图3）。 （图片较大，请拉动滚动条观看） 　　方法二、数组公式法 　　打开登记表，选中保存统计结果数值的单元格（如J104），输入公式：=SUM((C3:C102=男)*(I3:I102=工程师)*(J3:J102))，输入完成后，按下“Ctrl+Shift+Enter”组合键确认公式即可。 　　注意：这是一个数组公式，输入完成后，不能直接用“Enter”键进行确认，需要用“Ctrl+Shift+Enter”组合键进行确认，确认完成后，公式两端出现一对数组公式标志、一对大括号（{}，如图4）。 （图片较大，请拉动滚动条观看）方法三、条件求和向导法 　　1、打开登记表，执行“工具加载宏”命令，打开“加载宏”对话框（如图5），选中“条件求和向导”选项，按下“确定”按钮，然后按提示操作加载“条件求和向导”功能。 　　注意：第一次使用这个功能时，需要加载的，以后就可以直接使用了；在加载这一功能时，需要用到Office的安装盘。 　　2、执行“工具→向导→条件求和”命令，打开“条件求和向导—4步骤之一”对话框（如图6），在“请输入需要进行求和计算的区域……”下面的方框中输入：$A$2:$J$102，按下“下一步”按钮。 　　3、在随后打开的“条件求和向导—4步骤之二”对话框（如图7）中，将“求和列”设置为“工资”；将“条件列、运算符、比较值”分别设置为“性别、=、男”，再单击一下“添加条件”按钮；再将“条件列、运算符、比较值”分别设置为“职称、=、工程师”，再按一下“添加条件”按钮。 　　4、按“下一步”按钮，打开“条件求和向导—4步骤之三”对话框（如图8）。 　　5、直接按“下一步”按钮，打开“条件求和向导—4步骤之四”对话框（如图9），在其中的方框中输入用于保存求和结果的单元格（如J105），按下“完成”按钮就完成了。 　　注意：其实“条件求和向导”也是在相应的单元格中输入了另外一个数组公式（如图10），如果在相应的单元格中直接输入图中所示的数组公式，确认后同样可以达到多条件统计的目的。 .xls文件下载 有朋友提出这么一个要求（好像是在QQ群中提的，具体是哪位我忘了，不好意思哈）：求一个数中各位数字的最大的一个。比如2364，最大的数字是6；71200，最大的数字是7。我用的是下面的数组公式：{=MAX(MID(A1,ROW(INDIRECT(1:LEN(A1))),1)*1)} 数组常量的使用数组公式中还可使用数组常量，但必须自己键入花括号“{ }”将数组常量括起来，并且用“，”和“；”分离元素。其中“，”分离不同列的值，“；”分离不同行的值. 下面介绍两个使用数组公式的例子。???? 1 有如图所示的工作表，需分别计算各商品的销售额，可利用数组公式来实现。 ?????? 单元格F2中的公式为：{=SUM（IF（A2：A11=″商品1″，B2：B11＊C2：C11，0））}.这个数组公式创建了一个条件求和，若在A2：A11中出现值“商品1”，则数组公式将B2：B11和C2：C11中与其相对应的值相乘并累加，若是其他值则加零。同时，虽然数组B2：B11和C2：C11均在工作表中，但其相乘的数组B2：B11＊C2：C11不在工作表中，因此必须使用数组公式。??? 2 假设要将A1：A50区域中的所有数值舍入到2位小数位，然后对舍入的数值求和。很自然地就会想到使用公式：=ROUND（A1，2）＋ROUND（A2，2）＋…＋ROUND（A50，2）。???? 有没有更简捷的算法呢？有。因为数组ROUND（A1：A50，2）并不在工作表中，因此要使用数组的方式输入公式，即：{=SUM（ROUND（A1：A50，2））}. “逻辑