## EXCEL常用函数公式及技巧搜集之六

2017-01-29

EXCEL常用函数公式及技巧搜集之六

=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)

=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))

=TRIMMEAN(A1:A7,4/7)*(7-4)

=SUMPRODUCT(LARGE(A1:A7,ROW(A1:A7))*(ROW(1:7)>2)*(ROW(1:7)<6))

=SUMPRODUCT((A\$1:A\$7SMALL(A\$1:A\$7,2))*A\$1:A\$7)

=SUM(A!:A7)-LARGE(A!:A7,1)-LARGE(A!:A7,2)-SMALL(A!:A7,1)-SMALL(A!:A7,2)

=TRIMMEAN(A1:G1,4/7)*(7-4)

=SUMPRODUCT(LARGE(A1:G1,COLUMN(A1:G1))*(COLUMN(A:G)>2)*(COLUMN(A:G)<6))

1=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以对A列数值自动求和。

2=SUM(INDIRECT("R2C:R[-1]C",FALSE))

3、=SUM(INDIRECT("A2:A"&ROW()-1))

G3={SUM(IF((\$A\$2:\$A\$19=E3)*(\$B\$2:\$B\$19=F3),\$C\$2:\$C\$19))}

G4:G11公式为G3公式下拖.

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

=SUMPRODUCT((\$B\$2:\$B\$446=\$E2)*(\$C\$2:\$C\$446=F\$1))

=SUMPRODUCT((\$B\$2:\$B\$446=E3)*(\$C\$2:\$C\$446=F\$1))

=SUMPRODUCT((\$B\$2:\$B\$446=\$E2)*(\$C\$2:\$C\$446=F\$1))

=SUM((\$B\$2:\$B\$446=\$E2)*(\$C\$2:\$C\$446=\$F\$1))

=SUM((\$B\$2:\$B\$446=F2)*(\$C\$2:\$C\$446=\$G\$1)*\$D\$2:\$D\$446)

=SUMPRODUCT((\$B\$2:\$B\$446=\$E2)*(\$C\$2:\$C\$446=G\$1))

C17单元格中输入公式：

=COUNTIF(B1:B13,">=80")

A1: abc-ded-abc-def-abc-ded-ded-abc , 如何计算出A1里有几个abc

=IF(A1>15,"",COUNTIF(B2:B10,"<=8.5"))

累加值（求和）：

=IF(A1>15,"",SUMIF(B2:B10,"<=8.5"))

=DATEDIF(F2,TODAY(),"Y")     （周岁，自动显示年龄）

=YEAR(TODAY())-YEAR(F2)

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)

=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})

countif(a:a,">40")-countif(a:a,">50")

SUM(COUNTIF(a:a,">"&{40,50})*{1,-1})

SUMPRODUCT((A1:A7>40)*(A1:A7<50))

=COUNTIF(D2:D12,D2)

=SUMPRODUCT((B2:B12="")*(D2:D12=7))

=COUNTA(A:A)

=COUNTIF(A:A,"> ")

=COUNTA(A1:A10,D1:D10)

=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

=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})

=SUMPRODUCT((B3:B17="")*(C3:C17>=20)*(C3:C17<=50))

90—100   =COUNTIF(B2:B43,">=90")

80—89    =COUNTIF(B2:B43,">=80")-COUNTIF(B2:B43,">=90")

70—79    =COUNTIF(B2:B43,">=70")-COUNTIF(B2:B43,">=80")

60—69    =COUNTIF(B2:B43,">=60")-COUNTIF(B2:B43,">=70")

50—59    =COUNTIF(B2:B43,">=50")-COUNTIF(B2:B43,">=60")

A B C D E F

1 50 15 8 11 15 3

=COUNT(1/MOD(A1:E1-1,2))

=Sum(Mod(a1:e1+1,2))

{=SUM(--((A1:F1)/2=INT((A1:F1)/2)))}    算是一法,长了点

=SUMPRODUCT((MOD(A1:E1,2)=0)*1)

=SUMPRODUCT(1-MOD(A1:E1,2))

=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))

1、使用下面的数组公式： {=SUM(IF(\$A\$1:\$E\$10>\$A\$11,1))}

2、输入以下函数： =COUNTIF(A1E10,">"&A11)

李六       12

王武       50

李六       18

陈丰       187

李六       49

王武       135

陈丰       1584

王武的：    =SUMIF(B307:B313,C323,C307:C313)

陈丰的：    =SUMIF(B307:B313,D323,C307:C313)

=SUM(OFFSET(\$1,(ROW()-2)*10+1,,10,1))

258

247大家好

=SUMPRODUCT(LENB(ASC(A1:A6))-LEN(A1:A6))   11    仅统计汉字字符个数

=SUMPRODUCT(LEN(A1:A6))                     23     如果还混杂有其它字符

 购进日期 付款期 7月5日 2007-8-25 6月5日 2007-7-25 7月18日 2007-9-15 7月26日 2007-9-15

=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)<15,TEXT(A2+60-DAY(A2+45),"mmdd"),TEXT(A2+70-DAY(A2+45),"mmdd"))

=DATE(YEAR(A2+45),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,"<>")

=INDIRECT("b"&MATCH(ROW(A1),\$D\$2:\$D\$20,0)+1)

=INDIRECT("c"&MATCH(ROW(A1),\$D\$2:\$D\$20,0)+1)

[1] A1到A10单元格中, 数字4连在一起, 最大的连续次数, 公式为 :

{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),1)}

[2] 次大的连续长次数, 公式为 :

{=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,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\$21>B2))+1

=SUM(--(IF(FREQUENCY(B\$2:B\$21,B\$2:B\$21),B\$2:B\$21>B2)))+1

=SUM(--(FREQUENCY(IF(B\$2:B\$21>B2,B\$2:B\$21),B\$2:B\$21)>0))+1

=IF(COUNTIF(\$A\$1:A1,A1)>1,"",A1)

=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 列3 1 m B 2 n B 3 m C 1 n D 1 m A 2 m B 3 n C 2 n D 1 m A 2 n A 1 m B 对每列字母个数统计: 11 11 11 每列不相同的字母有: 3 2 4 它们分别是: 1 m B 2 n C 3 END D END A END

 姓名 班别 性别 高健丽 1 女 蔡美燕 2 女 张玉玫 3 女 蔡文文 4 女 陈娇娇 5 女 吴振宇 1 男 周婷婷 6 女 肖欣 6 女 梁丽宝 5 女 邱晓雯 4 女 李春梅 3 女 龙玉桦 2 女 阮梅英 1 女 梁光昕 2 男 … … …

 班别 男 女 总人数 1 29 45 74 2 30 44 74 3 30 44 74 4 31 43 74 5 30 44 74 6 30 45 75

=SUMPRODUCT((\$B\$2:\$B\$446=\$E2)*(\$C\$2:\$C\$446=F\$1))

=SUMPRODUCT((\$B\$2:\$B\$446=\$E2)*(\$C\$2:\$C\$446=G\$1))

=COUNTIF(\$B\$2:\$B\$446,E2)

=COUNTIF(\$A\$1:\$D\$10,A1)>1

1 2 3

3 2 1

1 2 0

=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))

=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)

=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1))

=SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2)))))

=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1))

=sumif(a:a,"*"&"某一字符"&"*",数据区)

=A2&TEXT(COUNTIF(\$A\$2:A2,A2),"00")

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

【数值取整及进位】

907.51034.21500要改变为90810351500公式为：

=CEILING(A1,1)

90710341500要改变为91010401500公式为：

=CEILING(A1,10)

=CEILING(A1,100)

=TRUNC(A1,0)

=ROUNDDOWN(A1,0)

=ROUND()

=ROUND(\$B\$1*A1,2)

=ROUND(B1*A1,2)

=round(a1,0)

=round(a1,0)*0.95

=INT(B2*100+0.5)/100

=IF((A3-INT(A3))>=0.3,IF((A3-INT(A3))>=0.8,1,0.5),0)+INT(A3)

IF(RIGHT(FIXED(A1,2),2)>B1,TRUNC(A2)+1,A2)

IF(INT(A1)<>A1,INT(A1)+1,A1)

=ROUNDUP(A1,0)

=CEILING(A9,1)

=INT(A9+1)

=ROUND(C6*D6,2)

=ROUND(A2*0.001,)*1000

=ROUND(A2,-3)

=--FIXED(A2,-3)

=ROUND(A2/1000,0)*1000

=ROUND(A1,2)

12512.2514     12512.3

=ROUND(B23,1)

=IF(MOD(INT(A1),2)=0,IF(MOD(A1,1)=0.5,INT(A1),INT(A1+0.5)),INT(A1+0.5))

=IF(AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE,IF(INT(A1)/2=INT(INT(A1)/2),INT(A1),ROUND(A1,0)),ROUND(A1,0))

AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE 判断是否为一位小数,且是0.5，如果不符合上术要条件,按普通四舍五入法则处理,否则判断整数部分的奇偶。

=IF(RIGHT(A1,1)*1<5,INT(A1),IF(RIGHT(A1,1)*1>5,INT(A1)+1,IF(MOD(ROUND(A1,),2)=0,ROUND(A1,),ROUNDDOWN(A1,))))

=IF(ROUNDUP(A1*2,)=A1*2,IF(MOD(ROUND(A1,),2)=1,ROUNDDOWN(A1,),ROUNDUP(A1,)),ROUND(A1,))

=ROUND(A1*2,0)/2

=CEILING(A1,0.5)

=IF((A1-INT(A1))<=0.2,INT(A1),IF((A1-INT(A1))<=0.5,INT(A1)+0.5,IF((A1-INT(A1))<=0.7,INT(A1),INT(A1)+1)))

=CEILING(A1-0.2,0.5)

=FLOOR(A1+0.2,0.5)

=CEILING(ROUND(A1/1000,0),1)*1000

=round(a1,-3)

=mround(A1,1000)

ROUND函数的四舍五入不进位的解决方法？

329.84----->329.90

329.86----->329.90

=roundup(*,2)=round(a1+0.04,1)

=round(原数值+0.001,2)

=INT(A1)+IF((A1-INT(A1)<=0.3),0,IF((A1-INT(A1)>0.7),1,0.5))

=INT(A1)+0.5*((A1-INT(A1)>0.3)+(A1-INT(A1)>0.7))

int函数取整数部分，A1-int(A1)取小数部分，根据你的意思：<=0.30算，0.3~0.7()0.5算，0.7~0.99……按+1

12.30    变成         12.00

45.32                 45.00

25.38                 25.00

6.54                   6.00

13.02                 13.00

59.68                 59.00

23.62                 23.00

=Rounddown(A1,0)

=INT(A1)

=TRUNC(A1,0)

0条评论