|
禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH("长?*宽?*高?*",B2)) 自动记录进库时间:=IF(ISBLANK(B2),"",IF(C2="",NOW(),C2)) 记录历史最高值:=MAX(B:B,D2) 解一元二次方程:X+100=X^2+10 解二元一次方程:X=10X=(100-5Y)/25,Y=Y/5=(200 +4X）/4
1。多条件计算人数=SUMPRODUCT((I241:I250="是")*1) 公式说明：SUMPRODUCT函数的功能是在给定的几组数组中，将数组间对应的参数相乘，并返回乘积之和。如果只有一组，则直接返回该数组之和。本例SUMPRODUCT函数就只有一个数组参数I241:I250，返回其中等于"是"的个数。 使用注意：1、SUMPRODUCT函数的参数可以是1至255个数组参数。如果只有一个参数，则返回该数组参数之和，如果有多组参数，则将所有数组对应的值相乘再将乘积汇总。所以SUMPRODUCT函数其实也就是多条件求和。2、SUMPRODUCT函数如果有多个数组参数，各数组的大小必须相等，例如第一参数是I241:I250，第二参数就不能是I241:I249等。3、如果SUMPRODUCT函数的参数中有非数值型数据，函数在计算时将非数值型的数组元素作为0处理。4、SUMPRODUCT函数的参数中如果有逻辑值TRUE或者FALSE，也都当做0处理。通常使用"*1"或者"--"将之转换成数值，然后再计算。 2、求指定年龄、性别人数=SUMPRODUCT((H252:H260="男")*1,(I252:I260>25)*1) 公式说明：本例公式中SUMPRODUCT函数有两个数组参数，都是需要进行运算的比较表达式。表达式运算结果是包含TRUE和FALSE的数组，为了让数组中的TRUE能转换为1，FALSE能转换成0参与运算，分别对两个数组"*1"。最后将两个数组中的对应元素分别相乘，并将乘积的结果累加，得到符合条件的人数总和。 使用注意：1、SUMPRODUCT函数进行的运算是数组运算，在输入公式时不需要利用【Ctrl+Shift+Enter】组合键输入数组，但是它的运算结果完全等同于数组公式。2、对于本例的需求，也可以用SUM函数的数组公式来完成:{=SUM((H252:H260="男")*(I252:I260>25))}。用SUM函数的数组形式替换SUMPRODUCT函数的普通公式，优点是公式更短，缺点是每次编辑后必须以【Ctrl+Shift+Enter】组合键结束，如果无意中双击了单元格再按【Enter】键结束，那么公式将产生错误的运算结果。3、本例的公式也可以修改为一个参数，使公式进行简化:=SUMPRODUCT((H252:H260="男")*(I252:I260>25))。这种形式对两个包含逻辑值的数组相乘，本身就将逻辑值转换为数值了，所以不再需要"*1"。 3、汇总一班人员获奖次数=SUMPRODUCT((H265:H274="一班")*I265:I274) 公式说明：本公式中SUMPRODUCT函数有两个数组参数:"H265:H274="一班""和"I265:I274"。根据本函数的特点，可以将两个数组通过乘号连接成一个参数，从而不管两个数组由数值组成还是由逻辑值组成，都可以不需要转换而直接得到运算结果。 使用注意：1、本例中也可以将两个数组分成两个参数，但第一参数需要利用"*1"或者其他方式将逻辑值转换成数值: 4、汇总一车间男性参保人数=SUMPRODUCT((G276:G284&H276:H284&I276:I284="一车间男是")*1) 公式说明：本公式中将G列、H列、I列的数据通过文本连接符"&"(等同函数CONCATENATE)连接，然后与设定的三个条件"一车间男是"进行比较，若相同就得到一个逻辑值TRUE，再用"*1"将逻辑值转换成数值，最后用SUMPRODUCT函数汇总数据。 使用注意：1、本例中公式思路只适用于所有条件中不存在">"、"<"、"<>"、"<="、">="运算符的条件，直接将所有数据串连起来，也将所有条件按同样顺序串连起来进行比较即可。比较的结果是一个由逻辑值组成的数组。2、本例公式也可以改用其他两种方式:=SUMPRODUCT((G276:G284="一车间")*(H276:H284="男")*(I276:I284="是"))和=SUMPRODUCT((G276:G284="一车间")*1,(H276:H284="男")*1,(I276:I284="是")*1)。3、如果计算二车间和三车间女性参保人数，可以采用以下公式:=SUMPRODUCT((G276:G284<>"一车间")*(H276:H284&I276:I284="女是"))。本公式对车间名称使用不等于号来排除一车间，相比罗列两个车间名称会简洁一些。 5、汇总所有车间人员工资=SUMPRODUCT(--NOT(ISERROR(FIND("车间",G286:G294))),I286:I294) 公式说明：鉴于SUMPRODUCT函数的参数不支持通配符，故本公式首先利用FIND函数在G286:G294区域中查找"车间"二字，如果找到则运算结果为一个数字，表示"车间"在该单元格中的出现位数；如果找不到将产生一个错误值。然后利用NOT(ISERROR())函数嵌套来判断哪些单元格中包括"车间"二字，得到一个由TRUE和FALSE组成的数组，再用"--"将这组逻辑值转换成数值，最后与I286:I294区域对应的数据相乘并汇总。 使用注意：1、在不支持通配符的所有函数中，都可以利用NOT(ISERROR(FIND()))的嵌套组合来实现通配符类似的功能。在本例中相当于"*车间*"，如果部门名称是"*车间"形式则可以改用以下公式:=SUMPRODUCT(--(RIGHT(G286:G294,2)="车间"),I286:I294)。2、根据本例的特点，也可以用ISNUMBER函数来代替NOT(ISERROR())组合，公式如下:=SUMPRODUCT(--(ISUMBER(FIND("车间",G286:G294))),I286:I294) 6、汇总业务员业绩=SUMPRODUCT((H296:H305={"江西","广东"})*(I296:I305="男")*J296:J305) 公式说明：SUMPRODUCT函数的参数支持二维数组，这使它不仅可以汇总同时满足多个条件的数据，还可以不借助其他函数的嵌套就可以达到在多条件中符合条件之一，即求和的需要。在本公式中，参数"{"江西","广东"}"可以使SUMPRODUCT函数统计两个省区的数据，这比SUMIF函数需要外套SUM函数简单些。 使用注意：1、本例公式也可以不使用数组，改用"+"连接两个条件，公式如下:=SUMPRODUCT(((H296:H305="江西")+(H296:H305="广东"))*(I296:I305="男")*J296:J305)。2、公式中“+”连接的条件表示满足任意条件就求和，而“*”连接的条件则表示同时满足所有条件才求和。 |