Excel常用函数公式及操作技巧之六

Excel常用函数公式及操作技巧之六查找符合２个条件的值+SUMPRODUCT函数查找符合２个条件的值查找与右表中品名和型号相符的代码，返回相应的值？＝可以在表1增加辅助列，表2用vlookup引用，如附件所示：或=SUMPRODUCT((A2=Sheet1!$A$2:$A$15)*(B2=Sheet1!$B$2:$B$15)*Sheet1!$C$2:$C$15)一个条件，用vlookup；多个条件，用数组：sumproduct就是数组，在a2:a15里面找a2,b2:b15里面找b2,然后在c2:c15里面找到满足条件的值=INDEX(Sheet1!$C$2:$C$15,MATCH(Sheet2!A2&Sheet2!B2,Sheet1!$A$2:$A$15&Sheet1!$B$2:$B$15,))数组公式。=LOOKUP(1,0/((A2=Sheet1!$A$2:$A$15)*(Sheet2!B2=Sheet1!$B$2:$B$15)),Sheet1!$C$2:$C$15)SUMPRODUCT函数在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT（array1,array2,array3,...）SUMPRODUCT(数组1,数组2,数组3,……)返回数组（区域）中对应项相乘后的和。sum就是简单求和;SUMIF就是有条件的求和;SUMPRODUCT就是数组求和法.Array1,array2,array3,...为2到30个数组，其相应元素需要进行相乘并求和。•数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!。•函数SUMPRODUCT将非数值型的数组元素作为0处理。例如下图所示的工作表数据：要求将列A和列B中同一行的数据相乘后的结果相加，即：A1×B1+A2×B2+A3×B3+…+A10×B10可使用如单元格C3中的公式：=SUMPRODUCT(A1:A10,B1:B10)或=SUMPRODUCT(DataA,DataB)，如果将单元格区域A1：A10命名为“DataA”，将单元格区域B1：B10命名为“DataB”（应用）基于两个条件汇总数据例如，下表所示的数据：华东区域产品B的销售量=SUMPRODUCT((C2:C10)*(A2:A10=”华东”)*(B2:B10=”B”))＝结果为100。其中：C2：C10为包含销售量的单元格区域；通过A2：A10=”华东”产生一个数值为1，0的数组，即如果列A中的数据为华东则值为1，否则为0；同理，通过B2:B10=”B”产生一个数值为1，0的数组；SUMPRODUCT函数将这些数组分别相乘后相加即得到结果如果不合适地应用SUMPRODUCT函数，将会得不到正确的结果，如，现在要求华中区域与目标日时间差为负数的项目的销售额。其中，将单元格区域“A2:A10”命名为“区域”，将单元格区域“B2:B10”命名为“销售额”，将单元格区域“C2:C10”命名为“时差”。如果我们输入下面的公式：=SUMPRODUCT((时差0),(区域=华中),销售额)，将得不到正确的结果。由于某种原因，SUMPRODUCT函数不能正确处理布尔值，因此公式无效。下面的公式在SUMPRODUCT函数中将布尔值乘以1，将得到正确的结果：=SUMPRODUCT(1*(时差0),1*(区域=华中),销售额)或=SUMPRODUCT(销售额*(区域=”华中”)*(时差0))其中，布尔值包括true和false两个值。在逻辑中，真值或逻辑值是指示一个陈述在什么程度上是真的。在计算机编程上多称作布尔值。在经典逻辑中，唯一可能的真值是真和假。但在其他逻辑中其他真值也是可能的:模糊逻辑和其他形式的多值逻辑使用比简单的真和假更多的真值。在代数上说，集合{真,假}形成了简单的布尔代数。可以把其他布尔代数用作多值逻辑中的真值集合，但直觉逻辑把布尔代数推广为Heyting代数。BCDE1Array1Array2Array3Array4234273866741953公式说明（结果）=SUMPRODUCT(B2:C4,D2:E4)两个数组的所有元素对应相乘，然后把乘积相加，即3*2+4*7+8*6+6*7+1*5+9*3。(156)=SUMPRODUCT((G1:G3=男)*(E1:E3=60))这个公式的意思是统计，G1－G3是男的，同时E1－E3数值小于等于60的人数。首先这是一个数组公式，要按Ctrl+Shift+Enter结束。然后看计算过程：假如G1＝男，G2，G3都为女，然后E1＝65，E2＝60，E3＝80。这时候公式变为=SUMPRODUCT((TRUE，FALSE，FALSE)*(TRUE，FALSE，FALSE))理解：因为G1=男，所以第一个值为TRUE。第二个不为男，值就为FALSE。接下来，TRUE和FALSE分别代表1和0。所以公式变为：=SUMPRODUCT((1，0，0)*(1，0，0))；然后接下来就是SUMPRODUCT的计算过程了=1*1+0*0+0*0=1，所以最后的结果等于1。SUMPRODUCT函数是返回乘积之和，SUMPRODUCT（A1:A4,B1:B4)=A1*B1+A2*B2+A3*B3+A4*B4,这是此函数的基本用法.另：1、怎样用SUMPRODUCT函数统计出“岗位”为“干部”，而且“性别”为“女”的人数？=SUMPRODUCT((D2:D9=干部)*(A2:A9=女))2、怎样用SUMPRODUCT函数统计出“岗位”为“工人”，而且“性别”为“女”，而且25“年龄”40的人数？=SUMPRODUCT((D2:D9=工人)*(A2:A9=女)*(B2:B940)*(B2:B925))举例：SUMPRODUCT（（周一！M28：M34=“龙腾”）*（周一！P28：P34））公式的意思是：P28:P34中对应于M28:M34中是“龙腾”的单元格的数据之和，假如：M29,M31,M32中为“龙腾”，则公式值等于P29+P31+P32.诀窍：用*号分隔组合条件特别说明：区域的选择必须是具体的区域，不能是整个一列，否则会提示错误。条件1：日期=2008-2-29条件2,（销售管理!U8:U115=合同)条件3（销售管理!Y8:Y115=颜敬)或者（销售管理!Y8:Y115=敬颜）三个条件同时满足，第三个条件有一个并列条件：=SUMPRODUCT((销售管理!T8:T115=DATE(2008,2,29))*(销售管理!U8:U115=合同)*((销售管理!Y8:Y115=颜敬)+(销售管理!Y8:Y115=敬颜)),销售管理!M8:M115)“且”用*号，“或”不能用OR而是用+号：至于OR这个函数，不能用在这里。在这个数组公式中，每一个条件得到的应该是一个数组，而不是单一值（当然有特殊的情况）。用+能得出一个数组来，用OR却必然返回单一值。这是重大的区别。请看附件，我想在Sheet1中C3单元格引用Sheet2的C列的工资数据，VLOOKUP可以实现，但是表中有人员当天在多岗位工作有好几处工资：表中曹瑞林就是这种情况出现了3次，怎么在查找引用后同样信息的可以求和（把3处的工资加在一起），函数怎么设？O(∩_∩)O谢谢，急。。。操作说明：1、选中《sheet2》B3:B20,在“名称框”输入任一名字如“姓名”，将该区域定义名称。2、在《sheet1》,“数据/筛选/高级筛选/将筛选结果复制到其他位置/列表区域/输入‘姓名’两字/条件区域/空白/复制到/B2/确定”，即可获得不重复姓名如绿色区域所示。3、在C3输入公式“=SUMIF(Sheet2!$B$3:$B$20,B3,Sheet2!$C$3:$C$20)”，向下复制到C18，即可得到每人工资额合计，如黄色区域。在SHEET1的C3单元格中输入“=SUMPRODUCT((Sheet2!$B$3:$B$20=$B3)*Sheet2!C$3:C$20)”或“=SUMIF(Sheet2!$B$3:$B$20,$B3,Sheet2!C$3:C$20)”，下拉，右拉。表一的C3输入：=SUMIF(Sheet2!$B:$B,$B3,Sheet2!C:C)，右拖下拉汇总计算与统计（二）统计数值大于等于80的单元格数目在C17单元格中输入公式：=COUNTIF(B1:B13,=80)确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。计算出A1里有几个abcA1:abc-ded-abc-def-abc-ded-ded-abc,如何计算出A1里有几个abc公式=(LEN(A1)-LEN(SUBSTITUTE(A1,abc,)))/LEN(abc)有条件统计如何统计当A1=15时，统计B列中=8.5的累加值和个数，而15时不进行统计？个数：=IF(A115,,COUNTIF(B2:B10,=8.5))累加值（求和）：=IF(A115,,SUMIF(B2:B10,=8.5))如何统计各年龄段的数量需分别统计20岁以下、21-30岁、31-40岁、41-50岁、50岁以上年龄段的数量。根据“出生日期”用以下公式，得到“自动显示年龄”。先将F列的出生日期设置为“1976年5月”格式，在G列公式为：=DATEDIF(F2,TODAY(),Y)（周岁，自动显示年龄）=YEAR(TODAY())-YEAR(F2)再根据年龄段：20岁以下、21-30岁、31-40岁、41-50岁、50岁以上，用以下公式，求出不同年龄段人数。在J2公式为：=SUMPRODUCT(($G$2:$G$34$H1)*($G$2:$G$34=$H2)*($C$2:$C$34=J$1))｛=SUM(($G$2:$G$34=VALUE(MID(I2,1,2)))*1)｝或数组公式：｛=SUM(($G$2:$G$34=VALUE(MID(I3,4,2)))*1)-SUM($J$2:J2)｝如何计算20-50岁的人数?=COUNTIF(C3:C17,=20)-COUNTIF(C3:C17,50)=SUMPRODUCT((C3:C17=20)*(C3:C17=50))=FREQUENCY(C3:C17,50)-FREQUENCY(C3:C17,19)｛=SUM(COUNTIF(C3:C17,=&{20,51})*{1,-1})｝如何统计40-50岁的人的个数＝countif(a:a,40)-countif(a:a,50)＝SUM(COUNTIF(a:a,&{40,50})*{1,-1})数组公式{=sum((a1:a740)*(a1:a750))}＝SUMPRODUCT((A1:A740)*(A1:A750))要统计出7岁的女生人数=COUNTIF(D2:D12,D2)=SUMPRODUCT((B2:B12=女)*(D2:D12=7))统计人数=COUNTA(A:A)=COUNTIF(A:A,)如何统计A1:A10，D1:D10中的人数？=COUNTA(A1:A10,D1:D10)如何让EXCEL自动从头统计到当前单元格情况如下：C列要根据A列的内容来统计B列的数据，范围从A1：An，即当A列中An有数据时，Cn自动根据An的值，统计B1：Bn的数据。｛=SUM(INDIRECT(B1:B&LARGE((A1:A65535)*(ROW(A1:A65535)),1)))｝统计人数建议提建议人员姓名提建议人数建议1王、李、赵、孙、钱、胡6建议2张、王、李、赵、孙、钱、胡7建议3张、王、李、孙、钱、胡6=LEN(B2)-LEN(SUBSTITUTE(B2,、,))+1=LEN(SUBSTITUTE(B2,、,))统计人数见表：性别年龄男6女35男3男55男21男53.5女55女56男65女45女53男51如何计算20-50岁的人数?=COUNTIF(C3:C17,=20)-COUNTIF(C3:C17,50)=SUMPRODUC