(Excel)常用函数公式及操作技巧之六汇总计算与统计(二).doc

(Excel)常用函数公式及操作技巧之六：汇总计算与统计（二）通过知识共享树立个人品牌。统计数值大于等于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列中15时不进行统计？个数：=IF(A115,COUNTIF(B2:B10,15,SUMIF(B2:B10,$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=20)-COUNTIF(C3:C17,50)=SUMPRODUCT(C3:C17=20)*(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:a740)*(A1:A7 )如何统计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)=SUMPRODUCT(C3:C17=20)*(C3:C17=&20,51)*1,-1)如何计算男20-50岁的人数?=SUMPRODUCT(B3:B17=男)*(C3:C17=20)*(C3:C17=90)8089 =COUNTIF(B2:B43,=80)-COUNTIF(B2:B43,=90)7079 =COUNTIF(B2:B43,=70)-COUNTIF(B2:B43,=80)6069 =COUNTIF(B2:B43,=60)-COUNTIF(B2:B43,=70)5059 =COUNTIF(B2:B43,=50)-COUNTIF(B2:B43,=60)有什么方法统计偶数例如：A1到E1有5个数如何统计着五个数中有几个是偶数A B C D E F1 50 15 8 11 15 3在F1中的3要用什么公式能统计出来统计偶数的个数=COUNT(1/MOD(A1:E1-1,2)=Sum(Mod(a1:e1+1,2)将偶数转化成奇数，再求奇数的个数。请在编辑栏中选择部分公式按F9观察每一步的计算过程。=SUM(-(A1:F1)/2=INT(A1:F1)/2) 算是一法,长了点=SUMPRODUCT(MOD(A1:E1,2)=0)*1)=SUMPRODUCT(1-MOD(A1:E1,2)如何显示如果D220那E2就显示$200、如果D230那E2就显示$300依此类推解答：=INT(D2/10)*100 ，当然，你的单元格格式设置成$格式就可以了。否则用，=$&INT(D2/10)*100 则该单元格成字符型 。当然，你也可以用IF函数，但它有7层的限制。= IF (D230, 300,IF(D220,200)工资统计中的问题问题：表一和表二中的职工姓名相同,但不在同一个位置上。怎样用公式求出表一中职工在表二中对应的工资、奖金和值班费的总额。要求，不能用表二中先加入一列，然后求和，再用公式导入表一的方法。我想知道能否在表一中用一个公式就可实现，而表二不动。=SUMPRODUCT(表二!$B$3:$B$42=A3)*(表二!$C$3:$E$42)+(表二!$G$3:$G$42=A3)*(表二!$H$3:$J$42)=IF(COUNTIF(表二!$B$3:$B$42,A3),SUMPRODUCT(VLOOKUP(A3,表二!$B$3:$E$42,2;3;4,),SUMPRODUCT(VLOOKUP(A3,表二!$G$3:$J$42,2;3;4,)=IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0),SUM(OFFSET(表二!$G$2,MATCH(A3,表二!$G$3:$G$42,0),1,3),SUM(OFFSET(表二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,3)=IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0),SUM(INDIRECT(表二!H&MATCH(A3,表二!$G$3:$G$42,0)+2&:J&MATCH(A3,表二!$G$3:$G$42,0)+2),SUM(INDIRECT(表二!C&MATCH(A3,表二!$B$3:$B$42,0)+2&:J&MATCH(A3,表二!$B$3:$B$42,0)+2)=IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0),VLOOKUP(A3,表二!$G$3:$J$42,4,0),VLOOKUP(A3,表二!$B$3:$F$42,4,0)统计数据问题一例如果我想统计50个数据中大于某个值的数据个数，（这个值是在使用时才输入某个单元格的），请问用什么函数。 如数据单元格为A1：E10，值的单元格为A11。1、使用下面的数组公式： =SUM(IF($A$1:$E$10$A$11,1)2、输入以下函数： =COUNTIF(A1：E10,&A11)根据给定的条件，对数据进行合计实例： 姓名 件数 （姓名在B307-B313中；件数在C307-C313中）李六 12 王武 50 李六 18 陈丰 187 李六 49 王武 135 陈丰 1584 目的： 对上面三个人的件数分别进行统计分析 步骤： 李六的： =SUMIF(B307:B313,B323,C307:C313) 王武的： =SUMIF(B307:B313,C323,C307:C313) 陈丰的： =SUMIF(B307:B313,D323,C307:C313) 姓名： 李六 王武 陈丰（分别在B323、C323、D323单元格中）结果： 79 185 1771 十列数据合计成一列=SUM(OFFSET($1,(ROW()-2)*10+1,10,1)统计汉字字符个数中国 A1中中国,A2中人民,A3中是空白,A4中是幸福,A5,A6中是空白人民258 258 幸福 247大家好 中国147 函数 结果 说明=SUMPRODUCT(LENB(ASC(A1:A6)-LEN(A1:A6) 11 仅统计汉字字符个数 =SUMPRODUCT(LEN(A1:A6) 23 如果还混杂有其它字符关于取数购进日期付款期7月5日2007-8-256月5日2007-7-257月18日2007-9-157月26日2007-9-15注：我想在B列的付款期中得到这样的结果：付款期=（购进日期+45天），但我们的付款期只有每月15和25号，如果按购进日期加上45天后不正好是付款日，那就得再往后延到最近的一个付款日，也就是15或25号。=MIN(IF(DAY(A2+ROW($45:$70)=15,25,A2+ROW($45:$70)=MIN(IF(DAY(A2+ROW($45:$70)=15,25,A2+ROW($45:$70),999999)=MIN(IF(DAY(A2+ROW($45:$67)=15)+(DAY(A2+ROW($45:$67)=25),A2+ROW($45:$67)=IF(DAY(A2+45)25,MONTH(A2+45)+1,MONTH(A2+45),IF(DAY(A2+45)=15,15,IF(DAY(A2+45)=25,25,15)统计单元格内不为空的格数如下图，怎么自动统计单元格内的“”，而空白的单元格则不计入内？=counta(a2:a31),下拉=countif(a2:a31,)=COUNTIF(a2:a31,)自动将销量前十名的产品代号及销量填入表中如：产品代号在“B”列，销量在“C”列=INDIRECT(b&MATCH(ROW(A1),$D$2:$D$20,0)+1)=INDIRECT(c&MATCH(ROW(A1),$D$2:$D$20,0)+1)统计最大的连续次数如图，请问如何编写公式求出A1到A10单元格中数字4连在一起的次数，本例中答案应为3（A1到A3）和2（A9到A10）。1 A1到A10单元格中, 数字4连在一起, 最大的连续次数, 公式为 :=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),),IF(A1:A104,ROW(A1:A10),),1)2 次大的连续长次数, 公式为 :=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),),IF(A1:A104,ROW(A1:A10),),2)3个“不重复”个数统计=SUM(-IF(MATCH(B$2:B$21,B$2:B$21,0)=ROW(B$2:B$21)-1,B$2:B$21B2)+1=SUM(-(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21B2)+1=SUM(-(FREQUENCY(IF(B$2:B$21B2,B$2:B$21),B$2:B$21)0)+1在一列有重复的姓名中，如何统计出具体有几人如果第一个张三在A1单元格，在B1处输入： =IF(COUNTIF($A$1:A1,A1)1,A1) 向下复制即可用数组公式也可以解决呀:假设你要统计A1到A100可以这样: =sum(1/countif(a1:a100,a1:a100),然后按住crtl,shift,和回车就可以了。计数的问题这个例子主要是计数的问题：共有三列数据，分别统计每列字母的个数、每列有几个不同的字母，最后把它们分别列出来。对每列字母个数统计，字符用COUNTA()，数字可以用COUNT()和COUNTA()。公式分别为：=COUNT(A2:A12)=COUNTA(B2:B12)=COUNTA(C2:C12)每列不相同的字母，公式分别为：=SUM(1/COUNTIF(A$2:A$12,A$2:A$12)=SUM(1/COUNTIF(B$2:B$12,B$2:B$12)=SUM(1/COUNTIF(C$2:C$12,C$2:C$12)分别列出来，公式分别为：=IF(SUM(1/COUNTIF(A$2:A$12,A$2:A$12)=ROW(A1),INDEX(A$2:A$12,SMALL(IF(ROW(A$2:A$12)-1=MATCH(A$2:A$12,A$2:A$12,0),ROW(A$2:A$12)-1,0),ROW(A1),END)=IF(SUM(1/COUNTIF(B$2:B$12,B$2:B$12)=ROW(B1),INDEX(B$2:B$12,SMALL(IF(ROW(B$2:B$12)-1=MATCH(B$2:B$12,B$2:B$12,0),ROW(B$2:B$12)-1,0),ROW(B1),END)=IF(SUM(1/COUNTIF(C$2:C$12,C$2:C$12)=ROW(C1),INDEX(C$2:C$12,SMALL(IF(ROW(C$2:C$12)-1=MATCH(C$2:C$12,C$2:C$12,0),ROW(C$2:C$12)-1,0),ROW(C1),END)列1列2列31mB2nB3mC1nD1mA2mB3nC2nD1mA2nA1mB对每列字母个数统计:111111每列不相同的字母有:324它们分别是:1mB2nC3ENDDENDAEND如何分班统计男女人数姓名班别性别高健丽1女蔡美燕2女张玉玫3女蔡文文4女陈娇娇5女吴振宇1男周婷婷6女肖欣6女梁丽宝5女邱晓雯4女李春梅3女龙玉桦2女阮梅英1女梁光昕2男班别男女总人数129457423044743304474431437453044746304575男=SUMPRODUCT($B$2:$B$446=$E2)*($C$2:$C$446=F$1)女=SUMPRODUCT($B$2:$B$446=$E2)*($C$2:$C$446=G$1)男=SUM($B$2:$B$446=$E2)*($C$2:$C$446=$F$1)女=SUM($B$2:$B$446=$E2)*($C$2:$C$446=$G$1)男=SUM($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)女=SUM($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)增加d列，输入公式：=B2&C2，合并数据后再利用countif公式对D列统计。=COUNTIF($B$2:$B$446,E2)在几百几千个数据中发现重复项我的意思不是查找功能,那个我会用,比如有几百个人的名字输入单元格中,但我面对那么多名字真无法短时间内看出谁重复了,该如何办?假设判断区域为A1:D10,格式/条件格式，选公式(不是数值)，输入： =COUNTIF($A$1:$D$10,A1)1 然后在格式中设置一个字体或图案颜色,确定,这样重复数据就变成了有色单元格。统计互不相同的数据个数例如,在 3 * 3 的区域中统计互不相同的数据个数, 1 2 3 3 2 1 1 2 0 结果应为 4 (4 个互不相同的数据)数组公式=sum(1/countif(a1:c3,a1:c3)还可以公式: =COUNT(IF(FREQUENCY(A1:C3,A1:C3),1) 多个工作表的单元格合并计算=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)单个单元格中字符统计假设 A1单元格中有数据sdfsfjksfhweofiefondsfljsdfisdofjei如何用公式统计出A1单元格中有多个不重复的字符?=SUMPRODUCT(-(LEN(A1)-LEN(SU