当前位置:文档之家› Excel 2007函数公式实例汇总

Excel 2007函数公式实例汇总

Excel 2007函数公式实例汇总
Excel 2007函数公式实例汇总

Excel 2007函数公式实例汇总

Excel2007函数公式收集了688个实例,涉及到137个函数、7个行业、41类用途,为大家提供一个参考,拓展思路的机会。公式由{}包括的为数组公式,在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键,自动生成数组公式。

对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7)

对生产表中大于100的产量进行求和:{=SUM((B2:B11>100) *B2:B11)}

对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)}

对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)}

对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10)}

求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3}))

求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9)

求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)}

求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2)

用SUM函数计数:{=SUM((B2:B9="男")*1)}

求1累加到100之和:{=SUM(ROW(1:100))}

多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C 组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3)))}

计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10)

计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000")

对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})}

求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4))

对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2) 对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2)

汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{"赵","刘","李"}&"*",C2:C10))

汇总鼠标所在列中大于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")

只汇总60~80分的成绩:=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,"<=80")

汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,"三年级",C2:C10,"二班")

汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,"*车间",B2:B11,"女") 计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*车间")*{-1,1})

计算参保人数:=SUMPRODUCT((C2:C11="是")*1)

求25岁以上男性人数:=SUMPRODUCT((B2:B10="男")*1,(C2:C10>25)*1) 汇总一班人员获奖次数:=SUMPRODUCT((B2:B11="一班")*C2:C11)

汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10="一车间男是")*1)

汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND("车间",A2:A10))),C2:C10)

汇总业务员业绩:=SUMPRODUCT((B2:B11={"江西","广东"})*(C2:C11="男")*D2:D11)

根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2)

计算A1:A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))}

根据二边长判断三角形是否为直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1,"非直角","直角")

计算1到10的自然数的积:=FACT(10)

计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49)

计算1到15之间奇数相乘的结果:=FACTDOUBLE(15)

计算每小时生产产值:=PRODUCT(C2:E2)

根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0.5 根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,{2,3}))/2

跨表求积:=PRODUCT(产量表:单价表!B2)

求不同单价下的利润:{=MMULT(B2:B10,G2:H2)*25%}

制作中文九九乘法表:=COLUMN()&"*"&ROW()&"="&MMULT(ROW(),COLUMN())

计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1}))

计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))}

计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))}

求入库最多的产品数

量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A11={"A","B","C","D"})*1))}

计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)}

计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)}

计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2:C17-D2:D17))}

求第一名人员最多有几次:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))}

求几号选手选票最

多:{=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)*100+B2:B10))}

总共有几个选手参选:{=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)))}

在不同班级有同名前提下计算学生人数:

{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0)) }

计算前进中学参赛人数:{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17& C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))*(A2:A17="前进中学")),ROW(2:17)^0),0))}

串联单元格中的数字:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}或=SUMPRODUCT(B2:K2,10^(COLUMNS(B:K)-COLUMN(B:K)-1))

计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)}

计算成绩在60-80分之间合计数与个数:

求和{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11< 80)*B2:B11),ROW(2:11)^0)},

求个数{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11& lt;80)),ROW(2:11)^0)}

汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)}

计算象棋比赛对局次数l:=COMBIN(B1,B2)

计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))}

预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60

计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2)

计算中奖率:=TEXT(1/PERMUT(B1,B2),"0.00%")

计算最大公约数:=GCD(B1:B5)

计算最小公倍数:=LCM(B1:B5)

计算余数:=MOD(A2,B2)

汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13)

根据单价数量汇总金

额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2)

设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),""))

根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),"男","女")

每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2)

工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10)

汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))}

将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1)

计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1

秒与小时、分钟的换

算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))),60^(3-COLUMN(A:A))) 生成隔行累加的序列:=QUOTIENT(ROW()+1,2)

根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2

计算预报温度与实际温度的最大误差值:{=MAX(ABS(C2:C8-B2:B8))}

计算个人所得

税:=ROUND(0.05*SUM(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,100000}+ABS(H2-1600-{0, 500,2000,5000,20000,40000,60000,80000,100000}))/2,0)

产生100到200之间带小数的随机数:=RAND()*(200-100)+100

产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10}

将20个学生的考位随机排列:{=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))}

将三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&":"& OFFSET(B$1,RANK(G2,G$2:G$11),)&":"&OFFSET(C$1,RANK(G2,G$2:G$11),) 产生-50到100之间的随机整数:=RANDBETWEEN(-50,100)

产生1到100之问的奇数随机

数:{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100))}

产生1到10之间随机不重复

数:{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBETWEEN(1,12-ROW()))}

根据三角形三边长求证三角形是直角三角

形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,{2,3}),2)),"是","不是")

计算Al:A10区域开三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))}

计算Al:A10区域倒数之积:{=PRODUCT(POWER(A1:A10,-1))}

根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3))

抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1)))

统计A1:B10区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10)))

统计参考人

数:=SUMPRODUCT((EVEN(COLUMN(A1:J12))=COLUMN(A1:J12))*(MOD(ROW(A1:J12),3)=1)*(A1:J12 <>""))

计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10)))

合计购物金额、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1)

将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1))

将金额进行四舍六入五单双:=IF((A2-TRUNC(A2,1))<=0.04,TRUNC(A2,1),IF((A2- TRUNC(A2,1))>=0.06,TRUNC(A2,1)+0.1,TRUNC((TRUNC(A2,1)+0.1)/2,1)*2))

根据重量单价计算金额,结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000

计算年假天数:=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5)

根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5)

将金额见角进元与见分进元:见分进元=CEILING(TRUNC(A2,2),1);

见角进元=CEILING(TRUNC(A2,1),1)

分别统计收支金额并忽略小数:收入合计=SUMPRODUCT(INT(B2:B8));

支出合计=SUMPRODUCT(TRUNC(C2:C8))

成绩表的格式转换:

姓名=INDEX(A:A,INT((ROW(A6))/3));科目=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1);

成绩=INDEX($B$2:$D$7,INT((ROW(A1)-1)/3)+1,MOD((ROW(A1)-1),3)+1)

隔两行进行编号:=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),"")

INT函数在序列中的复杂运用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9);=INT((ROW(A2))*2/3)

统计交易损失金额:=SUMPRODUCT(B2:B11-CEILING(B2:B11,0.1))

根据员工工龄计算年资:=C2+CEILING(B2*30,30)*(INT(B2)>0)

成绩表转换:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1)

计算机上网费用:=CEILING(B2,30)/30*2

统计可组建的球队总数:=SUMPRODUCT(FLOOR(B2:B10,5)/5)

统计业务员提成金额,不足20000元忽略:=FLOOR(B2,20000)/20000*500 FLOOR

函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A1>0,1,-10)))

将数据转换成接近6的倍数:=MROUND(A1,6)

以超产80为单位计算超产奖:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50}

将统计金额保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2)

将统计金额转换成以万元为单位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%%,)

对单价计量单位不同的品名汇总金

额:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="G",1000,1),(D2:D10="G")*2))}

将金额保留“角”位,忽略“分”位:{=SUM(ROUNDDOWN(B2:B10*C2:C10,1))}

计算需要多少零钞:{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})}

计算值为l万的整数倍数的数据个数:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))} 计算完成工程需求人数:{=SUM(ROUNDUP(B2:B11/C2:C11,))}

按需求对成绩进行分类汇总:=SUBTOTAL(HLOOKUP(G$1,{"平均成绩","科目数量","最高成绩","最低成绩","成绩合计";1,2,4,5,9},2,0),B2:D2)

不间断的序号:=SUBTOTAL(103,$B$2:B2)

仅对筛选出的人员排名次:{=CONCATENATE("第",SUM(N(IF((SUBTOTAL(103,OFFSET(优等

生!A$1,ROW($2:$31)-2,)))=1,$C$2:$C$31,)>C2))+1,"名")}

判断两列数据是否相等:

计算两列数据同行相等的个数:{=SUM(N(A1:A10=B1:B10))}

计算同行相等且长度为3的个数:{=SUM((A1:A10=B1:B10)*(LEN(A1:A10)=3))}

提取A产品最后单价:{=INDEX(C:C,MAX((B2:B10="A")*ROW(2:10)))}

判断学生是否符合奖学金发放条件:=AND(B2>90,C2<>"汉族")

所有裁判都给“通过”就进入决赛:{=AND(B2:E2="通过")}

判断身份证长度是否正确:=OR(LEN(B2)={15,18})

判断歌手是否被淘汰:{=OR(B2:E2="不通过")}

根据年龄判断职工是否退休:=OR(AND(B2="男",C2>60),AND(B2="女",C2>55))

根据年龄与职务判断职工是否退休:=OR(AND(B2="男",D2>60+(C2="干部")*3),AND(B2="女",D2>55+(C2="干部")*3))

没有任何裁判给“不通过”就进行决赛:{=NOT(OR(B2:E2="不通过"))}

计彝成绩区域数字个数:{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)} 评定学生成绩是否及格:=IF(AVERAGE(B2:D2)>=60,"及格","不及格")

根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)<60,"不及格",IF(AVERAGE(B2:D2)<90,"良好",IF(AVERAGE(B2:D2)<100,"优秀","满分")))

根据业绩计算需要发放多少奖金:{=SUM(IF(B2:B11>80000,1000,500))}

根据工作时间计算12月工资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500}))

合计区域的值并忽略错误值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))}

既求积也求和:=IF(D2<>"",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3)))

分别统计收入和支出:

收入{=SUM(IF(B2:B13>0,B2:B13))};

支出{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"",B2:B13,0),"负","-")*1< 0,SUBSTITUTE(B2:B13,"负","-")*1))}

将成绩从大到小排列:{=IF(ROW(A1)>COUNT(B$2:B$11),"",LARGE(B$2:B$11,ROW(A1)))}

排除空值:

{=INDEX($A:$B,SMALL(IF($B$1:$B$11<>"",ROW($1:$11),ROWS($1:$11)+1),ROW()),COLUMN(B2))&""} 有选择地汇总数据:{=SUM(IF(A2:A11={"A组","C组"},C2:C11))}

混合单价求金额合计:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="K",1000,1),2))}

计算异常停机时间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"",C2:C11,0),"修机",""),"换原料","")*1)}

计算最大数字行与文本行:{=MAX(IF(B:B<>"",ROW(A:A)))}

找出谁夺冠次数最

多:{=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12))=COUNTIF(B2:B12,B2:B12),ROW(2:12))))}

将全角字符转换为半角:=ASC(A2)

计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2))*2-LENB(ASC(A2))

将半角字符转换成全角显示:=WIDECHAR(A2)

计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2)))

判断单元格首字符是否为字母:

=OR(AND(CODE(A2)>64,CODE(A2)<91),AND(CODE(A2)>96,CODE(A2)<123))

计算单元格中数字个数:{=SUM((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>47)* (CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<58))}

计算单元格中大写加小写字母个数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))& gt;64)*(CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))<91))}

产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65)),小写字母=CHAR(ROW(A65)+32)

产生大写字母A到ZZ的字母序列:=IF(ROW()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+ (ROW()-1)/26-1))&IF(ROW()>26,CHAR(MOD(ROW()-1,26)+65),"")

产生三个字母组成的随机字符串:

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))

用公式产生换行符:=A2&CHAR(10)&B2

将数字转换成英文字符:字符码=RANDBETWEEN(1,100),升序位臵=CHAR(MOD(A1-1,26)+65)

将字母升序排序:{=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))}

返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2))

根据身份证号码提取出生年月日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15)),"年

",MID(B2,11-2*(LEN(B2)=15),2),"月",MID(B2,13-2*(LEN(B2)=15),2),"日")

计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),": ",IF(AVERAGE(B2:D2)>=60,"","不"),"及格")

提取前三名人员姓

名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1)),A2:A11),"|",LOOKUP(0,0/(B2:B11=LARG E(B2:B11,2)),A2:A11),"|",(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,3)),A2:A11)))

将单词转换成首字母大写:=PROPER(A2) 将所有单词转换成小写形式:=LOWER(A2)

将所有句子转换成首字母大写其余小写:

=CONCATENATE(PROPER(LEFT(A2)),LOWER(RIGHT(A2,LEN(A2)-1)))

将所有字母转换成大写形式:=UPPER(A2)

计算字符串中英文字母个数:{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT("1:"&

LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))))}

计算字符串中单词个数:{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT("1:"&

LEN(A2))),1)),MID(PROPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))))}

将文本型数字转换成数值:{=SUM(VALUE(B2:B10))}

计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1))))

提取混合字符串中的数字:

{=MAX(IFERROR(VALUE(MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&1234567890)),ROW(INDIRECT("1:" &LEN(A2))))),0))}

串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2))

给公式添加运算说明:=CONCATENATE("你好",B2,"2008")&T(N("公式含义:连接“你好”和单元格B2、“2008”"))

根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),"[=1]男;[=0]女")

将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))}

将货款显示为“万元”为单位:=TEXT(B2,"¥#"&""""&"."&""""&"#,万元")

根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日") 显示今天的英文日期及星期:="资料日期:"&TEXT(TODAY(),"dddd, mmmm dd, yyyy")

显示今天每项工程的预计完成时间:=TEXT(SUM("08:00",B$2:B2),"h:mm:ss 上午/下午")

统计A列有多少个星期日:{=SUM(N(TEXT(A1:A11,"aaa")="日"))}

将数据重复显示5次:=SUBSTITUTE(TEXT(A2&"?","@@@@@"),"?","") 将表示起止时间的数字格式化为时间格式:=TEXT(B2,"#!:00-00!:00") 根据起止时间计算经过时

间:=TEXT(INT(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+

(LEN(B4)=8)),"#!:00"))*24*60)/60)+MOD(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+

(LEN(B4)=8)),"#!:00"))*24*60),60.1)%,"0小时.00分钟")

将数字转化成电话格式:=TEXT(A2,"(0000)0000-0000")

在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,"DDDD")}

将汇总金额保留一位小数并显示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)}

计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000

将数据对齐显示,将空白以“.”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2)

利用公式制作简易图表:=IF(B2>0,REPT(" ",5)&"|"&REPT("■",ABS(B2))&B2& amp;REPT(" ",5-ABS(B2)),REPT(" ",5- ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&"|"&REPT(" ",5))

利用公式制作带轴的图表且标示升降:{=IF(A2<>"",A2&"┫","")&IF(A2="",REPT("〓", (MAX(ABS(B$2:B$8))+6)*2),IF(B2>0,REPT(" ",4+MAX(ABS(B$2:B$8)))&IF(ROW()=2,"

",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓")))&

REPT("■",ABS(B2))&B2&REPT(" ",4+MAX(ABS(B$2:B$8))-ABS(B2)),REPT(" ",4+MAX(ABS(B$2:B$8))-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&IF(ROW()=1,"

",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓"))& REPT(" ",4+MAX(ABS(B$2:B$8))))))}

计算单元格中数字个数:=LEN(A2)*2-LENB(A2)

将数字倒序排列:

{=TEXT(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*10^(ROW(INDIRECT("1:"&LEN(A2)))-1)),REPT(0, LEN(A2)))}

计算购物金额中小数位数最多是几:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1}

计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,"'"," ")," ",""))+1

将英文句子规范化:=PROPER(LEFT(A2))&TRIM(RIGHT(A2,LEN(A2)-1))

分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",100)),COLUMN(A2)*100-99,100))

提取英文名字:=LEFT(A2,FIND(" ",A2)-1)

将分数转换成小数:=(LEFT(A2,FIND("/",A2)-1)+RIGHT(A2,LEN(A2)-FIND("/",A2)))/2

从英文短句中提取每一个单词:=IFERROR(MID($A2,FIND("~",SUBSTITUTE(" "&$A2&" ","

","~",COLUMN(A2))),FIND("~",SUBSTITUTE(" "&$A2&" "," ","~",COLUMN(B2)))-FIND("~",SUBSTITUTE(" "&$A2&" "," ","~",COLUMN(A2)))),"")

将单位为“双”与“片”混合的数量汇总:

{=SUM(IF(ISNUMBER(FIND("/",C2:C9)),(LEFT(C2:C9,FIND("/",C2:C9)-1)+RIGHT(C2:C9,LEN(C2:C9)-FIND ("/",C2:C9)))/2,C2:C9*IF(B2:B9=" 片",0.5,1)))}

提取工作表名:=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

根据产品规格计算产品体

积:=PRODUCT(LEFT(B2,FIND("*",B2)-1),MID(B2,FIND("*",B2)+1,FIND("*",B2,FIND("*",B2)+1)-1-FIND("*",B2)) ,RIGHT(B2,LEN(B2)-FIND("*",B2,FIND("*",B2)+1)))

提取括号中的字符串:=IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),"") 分别提取长、宽、高:=MID($B2,FIND("@",SUBSTITUTE($B2," (","@",COLUMN(A1)))+1,FIND("@",SUBSTITUTE($B2,")","@",COLUMN(A1)))-FIND("@",SUBSTITUTE($B2," (","@",COLUMN(A1)))-1) 提取学校与医院地址:{=IF(OR(IFERROR(FIND({"学校","医院"},A2),FALSE)),A2,"")}

计算密码字符串中字符个

数:{=COUNT(FIND(CHAR(ROW(65:90)),A2),FIND(CHAR(ROW(97:122)),A2),FIND(ROW(1:10)-1,A2))}

通讯录单列转三

列:{=MID(INDEX($A:$A,SMALL(IF(IFERROR(FIND(C$1,$A$1:$A$15),FALSE),ROW($1:$15),100000),ROW (A1))),LEN(C$1)+1,100)}

将15位身份证号码升级为18位:{=IF(LEN(B2)=18,B2,LEFT(REPLACE(B2,7,,19),17)&

MID("10X98765432",MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRE CT("1:17")))),11)+1,1))}

将产品型号规范化:=IF(MID(A2,5,2)="00",A2,REPLACE(A2,5,,"00"))

求最大时

间:{=TEXT(MAX(--TEXT(REPLACE(LEFT(A2:A7,7),5,1,RIGHT(A2:A7,2)),"00!:00 00-00")),"hmm/dd/mm")}

分别提取小时、分钟、

秒:=REPLACE(REPLACE($A$1&$A2,FIND(B$1,$A$1&$A2),100,),1,FIND(A$1,$A$1&$A2)+1,)

将年级或者专业与班级名称分

开:{=REPLACE(A2,MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A2),0)),10,)}

提取各软件的版本

号:=REPLACE(REPLACE(A2,1,SEARCH("(",A2),),LEN(REPLACE(A2,1,SEARCH("(",A2),)),1,)

店名分类:=IF(COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2))=1,"餐饮娱乐",IF(COUNT(SEARCH({"干洗","医院","药","茶","蛋糕","面包","物流","驾校","开锁","家政","装饰","搬家","维修","中介","卫生","旅馆"},A2))=1,"便民服务",IF(COUNT(SEARCH({"游乐场","旅行社","旅游"},A2))=1,"旅游")))

查找编号中重复出现的数字:重复数字个数

{=COUNT(SEARCH((ROW($1:$10)-1)&"*"&(ROW($1:$10)-1),A2))};

重复字符=IF(COUNT(SEARCH("0*0",A2)),0,"")&SUBSTITUTE(SUMPRODUCT(ISNUMBER(SEARCH(ROW($1:$9)&"*"& ROW($1:$9),A2))*ROW($1:$9)*10^(9-ROW($1:$9))),0,) 统计名为“刘星”者人数:{=COUNT(SEARCH("?刘星",A2:A9))}

剔除多余的省名:=SUBSTITUTE(A2,IF(ISERROR(SEARCH("重庆市",A2)),"","四川省"),"")

将日期规范化再求差:=SUBSTITUTE(C2,".","-")-SUBSTITUTE(B2,".","-")

求每季度平均支出金额:=AVERAGEIF(B2:B9,"支出",C2)

计算每个车间大于250的平均产量:=AVERAGEIF(B2:C11,">250")

去掉首尾求平均:=AVERAGEIFS(B2:B11,B2:B11,">"&MIN(B2:B11),B2:B11,"<"&MAX(B2:B11))

生产A产品且无异常的机台平均产量:=AVERAGEIFS(C2:C11,B2:B11,"A",D2:D11,"")

计算生产车间异常机台个数:=COUNT(C2:C11)

计算及格率:{=TEXT(COUNT(0/(B2:B11>=60))/COUNT(B2:B11),"0.00%")}

统计属于餐饮娱乐业的店名个数:{=COUNT(SEARCH({"小吃","酒吧","茶","咖啡","电影","休闲","网吧"},A2:A11))}

统计各分数段人数:{=COUNT(0/((B$2:B$11>ROW(A6)*10)*(B$2:B$11<=ROW(A7)*10)))}

统计有多少个选手:{=COUNT(0/(MATCH(B2:B11,B2:B11,)=(ROW(2:11)-1)))}

统计出勤异常人数:=COUNTA(B2:B11)

判断是否有人缺考:=IF(COUNTA(B2:E10)=ROWS(B2:E10)*COLUMNS(B2:E10),"没有","有")

统计未检验完成的产品数:=COUNTBLANK(B2:B11)

统计产量达标率:=TEXT(COUNTIF(B2:B11,">=800")/COUNT(B2:B11),"0.00")

根据毕业学校统计中学学历人数:=COUNTIF(B2:B11,"*中学")

计算两列数据相同个数:{=SUM(COUNTIF(A2:A11,B2:B11))}

统计连续三次进入前十名的人

数:{=SUM(COUNTIF(C2:C11,IF(COUNTIF(A2:A11,B2:B11),B2:B11)))}

统计淘汰者人数:{=SUM(N(COUNTIF(A2:C11,A2:C11)=1))}

统计区域中不重复数据个数:{=SUM(1/COUNTIF(B2:B8,B2:B8))}

统计诺基亚、摩托罗拉和联想已隹出手机个数:=SUM(COUNTIF(B2:B11,"*"&{"诺基亚","摩托罗拉","联想"}&"*")) 统计联想比摩托罗拉手机的销量高多少:{=SUM(COUNTIF(B2:B11,{"诺基亚*","*联想*"})*{1,-1})}

统计冠军榜前三名:{=INDEX(B:B,SMALL(IF(COUNTIF(B$2:B$12,B$2:B$12)* ((MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1))>=LARGE(COUNTIF(B$2:B$12,B$2:B$12)*((MATCH(B$ 2:B$12,B$2:B$12,)=ROW($2:$12)-1)),3),ROW($2:$12)),ROW(A1)))}

统计真空、假空单元格个数:=COUNTIF(成绩!C2:C11,"=")

对名册表进行混合编号:=IF(RIGHT(B1)<>"班",ROW()-COUNTIF($B$1:B1,"??班"),TEXT(COUNTIF($B$1:B1,"??班"),"[DBNum2]0"))

提取不重复数据5:{=INDEX(B:B,MATCH(0,COUNTIF($D$1:D1,B$2:B$11),0)+1)} 中国式排名:{=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1} 统计大于80分的三好学生个数:{=COUNTIFS(B2:B11,"三好学生",C2:C11,">80")}

统计业绩在6万到8万之间的女业务员个数:=COUNTIFS(B2:B11,"女",C2:C11,">60000",C2:C11,"<=800000")

统计二班和三班数学竞赛获奖人数:=SUM(COUNTIFS(B2:B11,{"二班","三班"},C2:C11,"数学*"))

根据身高计算各班淘汰人

数:=SUM(COUNTIFS(B$2:B$11,E1,C$2:C$11,{"<160",">180"})) 计算A列最后一个非空单元格行号:{=MAX((A:A<>"")*ROW(A:A))} 计算女职工的最大年龄:{=MAX((B2:B11="女")*C2:C11)}

消除单位提取数

据:{=MAX(IFERROR(ABS(LEFT(A2,ROW($1:$100))),))*IF(LEFT(A2)="-",-1,1)} 计算单日最高销售金额:{=MAX(SUMIF(A2:A11,A2:A11,C2:C11))}

查找第一名学生姓名:=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,))

统计季度最高产值合计:{=MAX(SUBTOTAL(9,OFFSET(B2,,COLUMN(B:E)-2,ROWS(2:10),1)))}

根据达标率计算员工奖金:=MAX((B2>{0,0.8,0.9,1,1.05})*{200,250,300,450,550})

提取产品最后报价和最高报价:{=INDEX(C:C,MAX((A2:A11="B")*ROW(2:11)))}

计算卫冕失败最多的次数:{=MAX(FREQUENCY(ROW(2:11),((B2:B10="第一名")<>(B3:B11="第一名"))*ROW(2:10)))}

低于平均成绩中的最优成绩:{=MAX(IF(B2:B11

计算语文成绩大于90分者的最高总成绩:=DMAX(A1:E11,5,G1:G2)

计算数学成绩等于100分的男生最高总成绩:=DMAX(A1:E11,"总分",B1:B2)

根据下拉列表计算不同项目的最大值:=DMAX(A1:E11,G4,G1:G2)

计算中间成绩:=MEDIAN(B2:B11)

显示动态日期,但不能超过9月30日:=MIN("2008-9-30",TODAY())

根据工作时间计算可休假天数:=MIN(SUM((B2={"A","B","C"})*{5,4,3})+(C2-1),10)

确定最佳成绩:=MATCH(MIN(B2:B11),B2:B11,)

计算文具类产品和家具类产品最小利率:{=TEXT(MIN(IF(ISNUMBER(SEARCH("(?具类",A2:A11)),B2:B11)),"0.00%")}

计算得票最少者有几票:{=MIN(COUNTIF(B2:C11,B2:C11))}

根据工程的难度系数计算奖金:=MIN(A2,1+(A2>1.3)*0.3)*500

将科目与成绩分开:{=MID(A2,MIN(IF(ISNUMBER(FIND(ROW($1:$9),A2)),FIND(ROW($1:$9),A2))),100)} 计算五个班的第一名人员的最低成绩:=MIN(SUBTOTAL(4,INDIRECT({"一","二","三","四","五"}&"班!B2:b11"))) 根据员工生产产品的废品率记分:=MAX(MIN(6-(B2*100-5),10),0) 统计售价850元以上的产品最低利率是多少:=DMIN(A1:D11,F4,F1:F2) 统计文具类和厨具类产品的最低单价:=DMIN(A1:B11,2,D1:D2) 第三个最小的成绩:=SMALL(B2:B11,3)

计算最后三名成绩的平均值:=AVERAGE(SMALL(B2:B11,{1,2,3}))

将成绩按升序排列:{=SMALL(B$2:B$11,ROW(A1))}

罗列三个班第一名成绩:{=SMALL(IF(C$2:C$11="第一名",D$2:D$11),ROW(A1))}

将英文月份名称升序排列:

{=INDEX(A$2:A$13,SMALL(IF(CODE($A$2:$A$13)=SMALL(CODE(A$2:A$13),ROW(A1)),ROW($1:$12)) ,COUNTIF(C$1:C1,CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))&"*")+1))}

查看产品曾经销售的所有价

位:{=IF(ROW(A1)>SUM(1/COUNTIF(B$2:C$11,B$2:C$11)),"",SMALL(B$2:C$11,1+COUNTIF(B$2:C$11,"< ="&E1)))}

罗列三个工作表B列最后三名成绩:=SMALL(一班:三班!B:B,ROW(A1)) 第3个最小成绩到第6个最小成绩之间的人

数:{=SUM((((SMALL(B2:D11,ROW(INDIRECT("1:"&COUNT(B2:D11))))>SMALL(B2:D11,{3,6}))*{1,-1})))} 计算与第3个最大值并列的个数:{=SUM(--(B2:B11=LARGE(B2:B11,3)))} 计算大于等于前10个最大产量之和:=SUMPRODUCT((B2:C11>LARGE(B2:C11,11))*B2:C11)

按成绩列出学生排行

榜:{=INDEX(A$2:A$11,MATCH(LARGE(10-ROW($2:$11)+B$2:B$11*1000,ROW(A1)),10-ROW($2:$11)+B $2:B$11*1000,0))}

最后一次获得第一名是第几届:{=INDEX(A:A,LARGE((B2:B11="第一名")*ROW(2:11),1))}

提取销量的前三名的外销产品名

称:{=LOOKUP(0,0/($B$2:$B$10*100+ROW($2:$10)= (LARGE(IF(RIGHT(A$2:A$10,3)="外销)

",B$2:B$10*100+ROW($2:$10)),ROW(A1)))),A$2:A$10)} 哪种产品生产次数最多:{=TEXT(MODE(B2:B9*1),"00")}

罗列出被投诉多次的工作人员编

号:{=IFERROR(TEXT(MODE(IF(COUNTIF($D$1:D1,$B$2:$B$11)=0,$B$2:$B$11*1)),"00"),"")}

对学生成绩排名:=RANK(B2,B$2:B$11,0)

计算两列数值相同个数:=COUNT(RANK(B2:B11,C2:C11))

查询某人成绩在三个班中的排名:成绩{=LOOKUP(0,0/(E2:E11=H2),F2:F11)};名次=RANK(I2,(B2:B11,D2:D11,F2:F11),0)

分别统计每个分数段的人员个数:{=FREQUENCY(B2:B11,D2:D5)}

蝉联冠军最多的次

数:{=MAX(FREQUENCY(ROW(B$2:B$11),(B$2:B$10<>B$3:B$11)*ROW(B$2:B$10)))}

计算最多经过几次测试才成功:{=MAX(FREQUENCY(ROW(2:11),(B2:B11="成功")*ROW(2:11)))}

计算三个不连续区间的频率分

布:{=SUM(LOOKUP({1,3,5},ROW(1:5),FREQUENCY(B2:B11,{500,550,600,650})))}

计算因密码错误被锁定几次:{=COUNT(0/((FREQUENCY(ROW(2:12),(B2:B12<>"错误")*ROW(B2:B12))-1)>=3))}

计算小学加初中人数及中专加大学人数:{=FREQUENCY((B2:B11<>"小学")*(B2:B11<>"初中"),0)}

计算文本的频率分布:{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))}

夺冠排行榜:{=IF(ROW(A1)>

SUM(1/COUNTIF($B$2:$B$11,$B$2:$B$11)),"",INDEX($B$2:$B$11,MATCH(LARGE(FREQUENCY(MATCH ($B$2:$B$11,$B$2:$B$11,),ROW($1:$9))-ROW($1:$10)%,ROW(A1)),FREQUENCY(MATCH($B$2:$B$11,$ B$2:$B$11,),ROW($1:$9))-ROW($1:$10)%,)))}

谁蝉联冠军次数最

多:=INDEX(B2:B11,MATCH(MAX(FREQUENCY(ROW(2:11),(B2:B10<>

B3:B11)*ROW(2:10))),FREQUENCY(ROW(2:11),(B2:B10<>B3:B11)*ROW(2:10)),))

中国式排名:{=SUM(--(IF(FREQUENCY(B$2:B$11,B$2:B$11),B$2:B$11>B2)))+1} 谁获得第二名:

{=INDEX(A:A,SMALL(IF(B$2:B$11=SMALL(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),2),RO W($2:$11),1048576),ROW(A1)))&""} 记录当前日期与时间:=TEXT(NOW(),"m月d日h:m:s") 确定是否已到加油时间:=TEXT(NOW()-B2,"H:m")>"5:30" 国庆倒计时:=TEXT("10-1"-TEXT(NOW(),"mm-dd"),"00")

统计发货到收款天数:=ROUNDUP(IF(B2<>"",B2-A2,NOW()-A2),0) 统计已到达收款时间的货品数量:=COUNTIF(B2:B10,"<"&(TODAY()-30))

本月需要完成几批货物生产:{=SUM(N(B2:B11=TEXT(TODAY(),"MMMM")))}

计算本季度收款的合

计:{=SUM(IF(ROUNDUP(B2:B11/3,0)=ROUNDUP(TEXT(TODAY(),"M")/3,0),C2:C11))}

判断今年是否闰年:=OR((MOD(TEXT(TODAY(),"yyyy"),4)=0)*

(MOD(TEXT(TODAY(),"yyyy"),100)<>0),AND(MOD(TEXT(TODAY(),"yyyy"),{100,400})=0))

计算2008年有多少个星期

日:{=SUM(N(TEXT(DATE(2008,1,ROW(INDIRECT("1:"&("2008-12-31"-"2008-1-1")))),"AAA")="日"))}

计算本月有多少天:=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),"D") 确定今年母亲节的日

期:=DATE(YEAR(TODAY()),5,14-WEEKDAY(DATE(YEAR(TODAY()),4,30),2)) 今年包含多少个星

期:{=SUM(N(WEEKDAY(DATE(YEAR(TODAY()),1,ROW(INDIRECT("1:"&

(365+(DAY(DATE(YEAR(TODAY()),2,29))=29))))),2)=7))+(WEEKDAY(DATE(YEAR(TODAY()),1,365+(DAY (DATE(YEAR(TODAY()),2,29))=29)))<7)}

将身份证号码转换成出生日期序

列:=DATE(MID(B2,7,2+(LEN(B2)=18)*2),MID(B2,9+(LEN(B2)=18)*2,2),MID(B2,11+(LEN(B2)=18)*2,2))

计算建国多少周年:=YEAR(TODAY())-1949

计算2000年前电脑培训平均收费:{=AVERAGE(IF(YEAR(A2:A11)<2000,B2:B11))}

计算今天离本年度最后一天的天数:=(YEAR(TODAY())&"-12-31")-TODAY() 计算本月需要交货的数量:{=SUM((MONTH(B2:B11)=MONTH(TODAY()))*C2:C11)} 计算8月份笔筒和毛笔的进货数量:{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1="笔筒")+(B1:H1="毛笔"),B2:H11)))}

计算交货起止月:{=MIN(MONTH(B2:B11))&"月-"&MAX(MONTH(B2:B11))&"月"}),"Y")," 年

",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"YM"),"月",DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"#-00-00"),TODAY(),"MD"),"天")

计算年资:=10*MIN(DATEDIF(B2,TODAY(),"y"),15)+MAX(DATEDIF(B2,TODAY(),"y")-15,0)*5

计算临时工的工资:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,"分",""),"小时",":"))/(8/24)*50,)

计算本日工时工

资:=(HOUR(C2-TIMEVALUE("8:00"))-1-ROUNDUP(B2-TIMEVALUE("8:00"),0))*6 计算8:00一16:00的平均电

压:{=AVERAGE(IF((DAY(A2:A11)=8)*(HOUR(A2:A11)>=8)*(HOUR(A2:A11)>=16),B2:B11))}

计算工作时间,精确到分

钟:=HOUR(C2)+MINUTE(C2)/60-HOUR(B2)-MINUTE(B2)/60-D2+24*(C2

gt;=18,-(ROUNDUP((HOUR(B2-"18:00")*60+MINUTE(B2))/30,0))*3,(ROUNDDOWN((HOUR("18:00"-B2)*6 0+60-MINUTE(B2))/30,0))*3)

计算工程时间:=SUMPRODUCT(MINUTE(B2:B11)+(SECOND(B2:B11)>0)) 计算今天是星期几:=WEEKDAY(NOW(),2)

汇总星期日的支出金额:{=SUM((WEEKDAY(A2:A11,2)=7)*(B2:B11="支出")*C2:C11)}

汇总第一个星期的出库数

量:{=SUM(OFFSET(A2,,MIN(IF(WEEKDAY(B1:P1,2)=1,COLUMN(B:P))),,7))} 计算每日工时工资:=8*5*IF(WEEKDAY(A2,2)<6,1,1.5)+(B2-8)*5*1.5 计算指定日期所在月份有几个星期

日:{=SUM(N(WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))))= 1))}

按周汇总产

量:{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$AF1,2))+(COLUMN($B1:$AF1)-1)=(1+(COLUMN(A1)-1)* 7))*$B2:$AF2)}

按周汇总进仓与出仓数量:

{=SUM(((WEEKDAY($B1,2)-WEEKDAY($B1:$BK1,2))+INT((COLUMN($B1:$BK1))/2)=(1+(INT((COLUMN( A1)+1)/2)-1)*7))*$B3:$BK3*($B2:$BK2=B7))}

罗列本月休息日:

{=IFERROR(SMALL(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOM ONTH(NOW(),0))))),2)=2,DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOMONTH( NOW(),0)))))),ROW()),"")}

计算周末奖金补

贴:=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B2&":"&C2))-1,2)>5))*10 罗列值班日

期:{=MIN(IF(WEEKDAY(DATE(2008,ROW(),ROW($1:$31)),2)=7,DATE(2008,ROW(),ROW($1:$31))))}

计算本月加班时间:

{=SUM((MOD(MOD(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT("1:"&DAY(EOM ONTH(NOW(),0))))),2),7),2)={1,0})*{3,2})} 今天是本年度第几周:=WEEKNUM(TODAY())

本月包括多少周:=WEEKNUM(EOMONTH(NOW(),0),2)-WEEKNUM((EOMONTH(NOW(),-1)+1),2)+1

罗列第30周日期:

{=TEXT(SMALL(IF(WEEKNUM(DATE(YEAR(NOW()),1,ROW($1:$366)),2)=30,DATE(YEAR(NOW()),1,ROW( $1:$366))),ROW(A1)),"YYYY-MM-DD")}

统计某月第四周的支出金

额:{=SUM((WEEKNUM(A2:A11*1,1)-WEEKNUM(YEAR(A2:A11)&"-"&MONTH(A2:A11)&"-1")+1=4)*B2:B1 1)}

判断本月休息日:{=(SUM(N(WEEKNUM(ROW((INDIRECT((EOMONTH(NOW(),-1)+1)&":"& EOMONTH(NOW(),0)))),2)-WEEKNUM(EOMONTH(NOW(),-1)+1,2)+1=5))>3)+4}

计算离职日期:=WORKDAY(A2,5,{"2008-10-1","2008-10-2","2008-1-3"})

计算工程完工日期:{=WORKDAY(A2,B2,EOMONTH(A2,ROW(INDIRECT("1:"&INT(B2/30*2)))))}

计算2008年第一季度有多少个工作

EXCEL2007函数技巧大全

Excel2007函数公式收集了688个实例,涉及到137个函数、7个行业、41类用途,为大家提供一个参考,拓展思路的机会。公式由{}包括的为数组公式,在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键,自动生成数组公式。 对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7) 对生产表于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)} 对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)} 对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)} 对姓的女职工工资求和:{=SUM((LEFT(A2:A10)="")*(C2:C10="女")*D2:D10)} 求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3})) 求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9) 求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)} 求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2) 用SUM函数计数:{=SUM((B2:B9="男")*1)} 求1累加到100之和:{=SUM(ROW(1:100))} 多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C组!B2:B9,D 组!B2:B9,E组!B2:B9),ROW(1:3)))} 计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10) 计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000") 对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})} 求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4)) 对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2) 对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2) 汇总姓、、的业务员提成金额:=SUM(SUMIF(A2:A10,{"","",""}&"*",C2:C10)) 汇总鼠标所在列于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600")

excel常用函数公式介绍

excel常用函数公式介绍 excel常用函数公式介绍1:MODE函数应用 1MODE函数是比较简单也是使用最为普遍的函数,它是众数值,可以求出在异地区域或者范围内出现频率最多的某个数值。 2例如求整个班级的普遍身高,这时候我们就可以运用到了MODE 函数了 3先打开插入函数的选项,之后可以直接搜索MODE函数,找到求众数的函数公式 4之后打开MODE函数后就会出现一个函数的窗口了,我们将所要求的范围输入进Number1选项里面,或者是直接圈选区域 5之后只要按确定就可以得出普遍身高这一个众数值了 excel常用函数公式介绍2:IF函数应用 1IF函数常用于对一些数据的进行划分比较,例如对一个班级身高进行评测 2这里假设我们要对身高的标准要求是在170,对于170以及170之上的在备注标明为合格,其他的一律为不合格。这时候我们就要用到IF函数这样可以快捷标注好备注内容。先将光标点击在第一个备注栏下方 3之后还是一样打开函数参数,在里面直接搜索IF函数后打开 4打开IF函数后,我们先将条件填写在第一个填写栏中, D3>=170,之后在下面的当条件满足时为合格,不满足是则为不合格 5接着点击确定就可以得到备注了,这里因为身高不到170,所以备注里就是不合格的选项 6接着我们只要将第一栏的函数直接复制到以下所以的选项栏中就可以了

excel常用函数公式介绍3:RANK函数应用 2这里我们就用RANK函数来排列以下一个班级的身高状况 3老规矩先是要将光标放于排名栏下面第一个选项中,之后我们打开函数参数 4找到RANK函数后,我们因为选项的数字在D3单元格所以我们就填写D3就可了,之后在范围栏中选定好,这里要注意的是必须加上$不然之后复制函数后结果会出错 5之后直接点击确定就可以了,这时候就会生成排名了。之后我们还是一样直接复制函数黏贴到下方选项栏就可以了。

EXCEL的函数大全(完整版)

实用EXCE的函数 1.ADDRESS 用途:以文字形式返回对工作簿中某一单元格的引用。 语法:ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 参数:Row_num是单元格引用中使用的行号;Column_num是单元格引用中使用的列 标;Abs_num指明返回的引用类型(1或省略为绝对引用,2绝对行号、相对列标,3相对行号、绝对列标,4是相对引用);A1是一个逻辑值,它用来指明是以A1或R1C1返回引用样式。如果A1为TRUE或省略,函数ADDRESS返回A1样式的引用;如果A1为FALSE,函数ADDRESS 返回R1C1样式的引用。Sheet_text为一文本,指明作为外部引用的工作表的名称,如果省略sheet_text,则不使用任何工作表的名称。 实例:公式“=ADDRESS(1,4,4,1)”返回D1。 2.AREAS 用途:返回引用中包含的区域个数。 语法:AREAS(reference)。 参数:Reference是对某一单元格或单元格区域的引用,也可以引用多个区域。 注意:如果需要将几个引用指定为一个参数,则必须用括号括起来,以免Excel将逗号作为参数间的分隔符。 实例:公式“=AREAS(a2:b4)”返回1,=AREAS((A1:A3,A4:A6,B4:B7,A16:A18))返回4。 3.CHOOSE 用途:可以根据给定的索引值,从多达29个待选参数中选出相应的值或操作。 语法:CHOOSE(index_num,value1,value2,...)。 参数:Index_num是用来指明待选参数序号的值,它必须是1到29之间的数字、或者是包含数字1到29的公式或单元格引用;value1,value2,...为1到29个数值参数,可以是数字、单元格,已定义的名称、公式、函数或文本。 实例:公式“=CHOOSE(2,"电脑","爱好者")返回“爱好者”。公式“=SUM(A1:CHOOSE(3,A10,A20,A30))”与公式“=SUM(A1:A30)”等价(因为CHOOSE(3,A10,A20,A30)返回A30)。 4.COLUMN

EXCEL函数公式大全

excel常用函数公式及技巧搜集(常用的)【身份证信息?提取】 从身份证号码中提取出生年月日 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1 =IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为yyyy-m-d。(最简单的公式,把单元格设置为日期格式) =IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)& "-"&MID(A2,11,2)&"-"&MID(A2,13,2)) 显示格式为yyyy-mm-dd。(如果要求为“1995/03/29”格式的话,将”-”换成”/”即可) =IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000年00月00日 "),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000年00月00日")))) 显示格式为yyyy年mm月dd日。(如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd) =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6)) 显示格式为yyyymmdd。 =TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)) =MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日" =IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")) 从身份证号码中提取出性别 =IF(MOD(MID(A1,15,3),2),"男","女") (最简单公式) =IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女") =IF(A2<>””,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","女") 从身份证号码中进行年龄判断 =IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),T ODAY(),”Y”),) =DATEDIF(A1,TODAY(),“Y”) (以上公式会判断是否已过生日而自动增减一岁) =YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())-V ALUE(MID(B1,7,4))&"岁" =YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4)) 按身份证号号码计算至今天年龄 =DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y") 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式

电子表格常用函数公式

电子表格常用函数公式 1、自动排序函数: =RANK(第1数坐标,$第1数纵坐标$横坐标:$最后数纵坐标$横坐标,升降序号1降0升) 例如:=RANK(X3,$X$3:$X$155,0) 说明:从X3 到X 155自动排序 2、多位数中间取部分连续数值: =MID(该多位数所在位置坐标,所取多位数的第一个数字的排列位数,所取数值的总个数) 例如:612730************在B4坐标位置,取中间出生年月日,共8位数 =MID(B4,7,8) =19820711 说明:B4指该数据的位置坐标,7指从第7位开始取值,8指一共取8个数字 3、若在所取的数值中间添加其他字样, 例如:612730************在B4坐标位置,取中间出生年、月、日,要求****年**月**日格式 =MID(B4,7,4)&〝年〞&MID(B4,11,2) &〝月〞& MID(B4,13,2) &〝月〞&

=1982年07月11日 说明:B4指该数据的位置坐标,7、11指开始取值的第一位数排序号,4、2指所取数值个数,引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板:首先利用Word制作一个奖状模板并保存为“奖状.doc”,将其中班级、姓名、获奖类别先空出,确保打印输出后的格式与奖状纸相符(如图1所示)。 第二步用Excel建立获奖数据库:在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”,格式如图2所示。 第三步关联数据库与奖状:打开“奖状.doc”,依次选择视图→工具栏→邮件合并,在新出现的工具栏中选择“打开数据源”,并选择“奖状数据.xls”,打开后选择相应的工作簿,默认为sheet1,并按确定。将鼠标定位到需要插入班级的地方,单击“插入域”,在弹出的对话框中选择“班级”,并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印:选择“查看合并数据”,然后用前后箭头就可以浏览合并数据后的效果,选择“合并到新文档”可以生成一个包含所有奖状的Word文档,这时就可以批量打印了。

Excel2007函数公式实例集

Excel2007函数公式实例集 Excel2007函数公式实例集.txt-//自私,让我们只看见自己却容不下别人。如果发短信给你喜欢的人,他不回,不要再发。看着你的相片,我就特冲动的想P成黑白挂墙上!有时,不是世界太虚伪,只是,我们太天真。 Excel2007函数公式收集了688个实例,涉及到137个函数、7个行业、41类 用途,为大家提供一个参考,拓展思路的机会。公式由{}包括的为数组公式,在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键,自动生成数组公式。 对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7) 对生产表中大于100的产量进行求 和:{=SUM((B2:B11>100)*B2:B11)} 对生产表大于110或者小于100的数据求 和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)} 对一车间男性职工的工资求和:{=SUM((B2:B10="一车间 ")*(C2:C10="男")*D2:D10)} 对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女 ")*D2:D10)} 求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3})) 求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9) 求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)} 求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2) 用SUM函数计数:{=SUM((B2:B9="男")*1)} 求1累加到100之和:{=SUM(ROW(1:100))}

15个常用的Excel函数公式

15个常用的Excel函数公式,拿来即用 1、查找重复内容 =IF(COUNTIF(A:A,A2)>1,"重复","") 2、重复内容首次出现时不提示 =IF(COUNTIF(A$2:A2,A2)>1,"重复","") 3、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","")

4、根据出生年月计算年龄 =DATEDIF(A2,TODAY(),"y") 5、根据身份证号码提取出生年月 =--TEXT(MID(A2,7,8),"0-00-00") 6、根据身份证号码提取性别 =IF(MOD(MID(A2,15,3),2),"男","女") 7、几个常用的汇总公式 A列求和:=SUM(A:A)

A列最小值:=MIN(A:A) A列最大值:=MAX (A:A) A列平均值:=AVERAGE(A:A) A列数值个数:=COUNT(A:A) 8、成绩排名 =RANK.EQ(A2,A$2:A$7) 9、中国式排名(相同成绩不占用名次) =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 10、90分以上的人数

=COUNTIF(B1:B7,">90") 11、各分数段的人数 同时选中E2:E5,输入以下公式,按Shift+Ctrl+Enter =FREQUENCY(B2:B7,{70;80;90}) 12、按条件统计平均值 =AVERAGEIF(B2:B7,"男",C2:C7) 13、多条件统计平均值 =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")

Excel2007函数大全

Excel2007函数大全 一、函数应用基础 1.函数和公式 (1)什么是函数 Excel函数即是预先定义,执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例,它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称,一个函数只有唯一的一个名称,它决定了函数的功能和用途。函数名称后紧跟左括号,接着是用逗号分隔的称为参数的内容,最后用一个右括号表示函数结束。 参数是函数中最复杂的组成部分,它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理,如分析存款利息、确定成绩名次、计算三角函数值等。 按照函数的来源,Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel,用户就可以使用它们;而后者必须通过单击“工具→加载宏”菜单命令加载,然后才能像内置函数那样使用。 (2)什么是公式 函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式,后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例,它要以等号“=”开始,其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数,“A1”则是对单元格A1的引用(使用其中存储的数据),“26”则是常量,“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。 如果函数要以公式的形式出现,它必须有两个组成部分,一个是函数名称前面的等号,另一个则是函数本身。 2.函数的参数 函数右边括号中的部分称为参数,假如一个函数可以使用多个参数,那么参数与参数之间使用半角逗号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1),甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求,否则将返回错误信息。 (1)常量 常量是直接输入到单元格或公式中的数字或文本,或由名称所代表的数字或文本值,例如数字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式计算出的结果都不是常量,因为只要公式的参数发生了变化,它自身或计算出来的结果就会发生变化。 (2)逻辑值 逻辑值是比较特殊的一类参数,它只有TRUE(真)或FALSE(假)两种类型。例如在公式“=IF(A3=0,"",A2/A3)”中,“A3=0”就是一个可以返回TRUE(真)或FALSE(假)两种结果的参数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”,否则在单元格中填入“A2/A3”的计算结果。 (3)数组 数组用于可产生多个结果,或可以对存放在行和列中的一组参数进行计算的公式。Excel中有常量和区域两类数组。前者放在“{}”(按下Ctrl+Shift+Enter组合键自动生成)内部,而且内部各列的数值要用逗号“,”隔开,各行的数值要用分号“;”隔开。假如你要表示第1行中的56、78、89和第2行中的90、76、80,就应该建立一个2行3列的常量数组“{56,78,89;90,76,80}。 区域数组是一个矩形的单元格区域,该区域中的单元格共用一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时,它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。 (4)错误值 使用错误值作为参数的主要是信息函数,例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为“ERROR.TYPE(error_val)”,如果其中的参数是#NUM!,则返回数值“6”。 (5)单元格引用 单元格引用是函数中最常见的参数,引用的目的在于标识工作表单元格或单元格区域,并指明公式或函数所使用的数据的位置,便于它们使用工作表各处的数据,或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格,甚至引用其他工作簿中的数据。 根据公式所在单元格的位置发生变化时,单元格引用的变化情况,我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存放在F2单元格中的公式“=SUM(A2:E2)”为例,当公式由F2单元格复制到F3单元格以后,公式中的引用也会变化为“=SUM(A3:E3)”。若公式自F列向下继续复制,“行标”每增加1行,公式中的行标也自动加1。

Excel常用函数公式大全(实用)

Excel常用函数公式大全 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和:=SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数:=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名:=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名; 4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分:=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分; 7、最低分:=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分; 8、分数段人数统计: (1)=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;

EXCEL常用函数公式与举例

EXCEL常用函数公式及举例 一、相关概念 (一)函数语法 由函数名+括号+参数组成 例:求和函数:SUM(A1,B2,…) 。参数与参数之间用逗号“,”隔开(二)运算符 1. 公式运算符:加(+)、减(-)、乘(*)、除(/)、百分号(%)、乘幂(^) 2. 比较运算符:大与(>)、小于(<)、等于(=)、小于等于(<=)、大于等于(>=)、不等于(<>) 3. 引用运算符:区域运算符(:)、联合运算符(,) (三)单元格的相对引用与绝对引用 例: A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 (一)数学函数 1. 求和 =SUM(数值1,数值2,……) 2. 条件求和 =SUMIF(查找的围,条件(即对象),要求和的围) 例:(1)=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思:对第A1栏至A4栏中,大于等于200的数值对应的第B1列至B4列中数值求和 (2)=SUMIF(A1:A4,”<300”,C1:C4)

函数意思:对第A1栏至A4栏中,小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数 =COUNT(数值1,数值2,……) 例:(1) =COUNT(A1:A4) 函数意思:第A1栏至A4栏求个数(2) =COUNT(A1:C4) 函数意思:第A1栏至C4栏求个数 4. 条件求个数 =COUNTIF(围,条件) 例:(1) =COUNTIF(A1:A4,”<>200”) 函数意思:第A1栏至A4栏中不等于200的栏求个数 (2)=COUNTIF(A1:C4,”>=1000”) 函数意思:第A1栏至C4栏于等1000的栏求个数 5. 求算术平均数 =AVERAGE(数值1,数值2,……) 例:(1) =AVERAGE(A1,B2) (2) =AVERAGE(A1:A4) 6. 四舍五入函数 =ROUND(数值,保留的小数位数) 7. 排位函数 =RANK(数值,围,序别) 1-升序 0-降序 例:(1) =RANK(A1,A1:A4,1) 函数意思:第A1栏在A1栏至A4栏中按升序排序,返回排名值。 (2) =RANK(A1,A1:A4,0) 函数意思:第A1栏在A1栏至A4栏中按降序排序,返回排名值。 8. 乘积函数 =PRODUCT(数值1,数值2,……) 9. 取绝对值 =ABS(数字) 10. 取整 =INT(数字) (二)逻辑函数

EXCEL2007新增的几个多条件函数

EXCEL2007新增的几个多条件函数解析 内容来自网络,在此感谢作者。整理成文档仅作学习之用。查原文请点击链接:https://www.doczj.com/doc/d35333817.html,/7909652.html或者https://www.doczj.com/doc/d35333817.html,/1674104/forum/

AVERAGEIF返回某个区域内满足给定条件的所有单元格的平均值(算术平均值)。其语法格式 是: AVERAGEIF(range,criteria,average_range) Range是要计算平均值的一个或多个单元格,其中包括数字或包含数字的名称、数组或引用。Criteria 是数字、表达式、单元格引用或文本形式的条件,用于定义要对哪些单元格计算平均值。例如,条件可以表示为 32、"32"、">32"、"apples" 或 B4。Average_range 是要计算平均值的实际单元格集。如果忽略,则使用 range。 EXCEL2003时没有此新增函数公式为 方法一:数组公式 =AVERAGE(IF(B4:B11="一班",E4:E11)) 方法二:=SUMIF(B4:B11,"一班",E4:E11)/COUNTIF(B4:B11,"一班") EXCEL2007版AVERAGEIF公式为 =AVERAGEIF(B5:B12,"一班",E5:E12)

公式中range是指B5:B12,即“班级”这列的所有班组情况。criteria是指“一班”,即满足班级为“一班”,average_range是指E5:E12,即对应实际计算的成绩集。 AVERAGEIFS返回满足多重条件的所有单元格的平均值(算术平均值)。其语法格式是: AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…) Average_range 是要计算平均值的一个或多个单元格,其中包括数字或包含数字的名称、数组或引用。Criteria_range1, criteria_range2, …是计算关联条件的 1 至 127 个区域。 Criteria1, criteria2, …是数字、表达式、单元格引用或文本形式的 1 至 127 个条件,用于定义要对哪些单元格求平均值。例如,条件可以表示为 32、""32""、"">32""、""apples"" 或 B4。EXCEL2003时没有此新增函数公式为 方法一:数组公式

常用excel函数公式大全

常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式:C2 =IFERROR(A2/B2,"") 说明:如果是错误值则显示为空,否则正常显示。

2、IF多条件判断返回值 公式:C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明:两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明:如果返回值大于0说明在另一个表中存在,0则不存在。

2、统计不重复的总人数 公式:C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。 四、求和公式

1、隔列求和 公式:H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明:如果标题行没有规则用第2个公式 2、单条件求和 公式:F2 =SUMIF(A:A,E2,C:C) 说明:SUMIF函数的基本用法

3、单条件模糊求和 公式:详见下图 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。

4、多条件模糊求和 公式:C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明:在sumifs中可以使用通配符* 5、多表相同位置求和 公式:b2 =SUM(Sheet1:Sheet19!B2) 说明:在表中间删除或添加表后,公式结果会自动更新。 6、按日期和产品求和

excel常用函数公式(有实例有分析有重点).docx

excel公式笔记 一、vlookup 1.查找A列中第一个以”厦门”开头的记录对应B列的值。 =vlookup(H厦门性A:B20) 其中第一个参数为要寻找的文本,第二个参数为一个区域,第二个参数的第一列为要寻找的区域,第三个参数的2表示第二个参数的第二列显示出来,第四个参数的0表示精确查找。 二、countif 1.统计Al:A10区域中型号为" 2.5m*3m"的记录个数。 =countif(Al:A10,,,2.5m~*3m H) 在excel常用函数中,支持通配符的主要有vlookup、hlookup> match> sum讦、courttif、search> searchB,而find、findB> subsitute 不支持通配符。*表示任意字符,?表示单个字符解除字符的通配性。 2.统计Al:A10中不重复数的个数。 =SUMPRODUCT((1/COUNTIF(A1:A10,A1:A10)))

3?求小于60的数据有多少 二count(A2:AKVv6(T) sumproduct 1?求购物总花费,A列表示购买数量月列表示购买单价 =sumproduct(A2:A8,B2:B8) 意思为A2*B2+A3*B3oooo +A8*B8 2.求二班有多少学生学习了数学 =sumproduct((A2:A10=H~ B,,)*(B2:B10=H数学“)) 表示二班的数学有几个 3.求二班数学分数总和 =sumproduct((A2:A10=M Z:B M)*(B2:B10=H数学H)*(C2:C1O)) 4.统计“技术部”考试成绩为0的个数(缺考除外) =sumproduct((B2:B9=H技术部,,)*(E2:E9=0)*(E2:E9o,,H)) excel会将空值看成0,所以在统计成绩为0的考生时,需要把成绩为空的考牛去除。

excel2007函数练习题

excel2007函数练习题 1.常用函数 2015年职称计算机考试Excel2007模块常用函数考点,考试频率:★★★★★掌握难度:★★ 具体详细讲解如下: 1.职称考试Excel2007模块:求和函数SUM 功能:SUM为数学与三角函数类,功能是求列表中所有参数的和。 格式为:SUM 参数:number1,number2,???为1~255个需要求和的参数。能够作为参数的有数字、逻辑值、文本数字、单元格和区域的引用地址、、名称及标志。 单击要存放的单元格,单击按钮,打开对话框,选择常用函数类型中的SUM,选取求和区域,单击按钮。 2.计算机职称考试Excel2007:求平均值函数AVERAGE 功能:AVERAGE函数用于统计数据集的算术平均值。 格式:AVERAGE 参数:包括数字、逻辑值、文本数字、单元格和区域引用地址、名称及标志。Number1为1~255个需要计算平均值的参数。 计算时,参数中逻辑值TRUE被转换为1,FALSE被转换为0。文本数字也被转换为数值。参数中引用单元格或区域,只计算其中的数字,其他被忽略。

打开对话框输入求平均值函数公式。 3.计算机职称考试Excel2007模块:最大值函数MAX、最小值函数MIN 功能:求包含数据集中的最大数值MAX,最小值MIN,都属于统计函数。 格式:MAX/MIN 参数:最多有255个参数,数字、逻辑值、文本数字、空白单元格、单元格和区域的引用地址、名称及标志都可以作参数。 直接在编辑框栏中输入公式或者在对话框中输入函数求平均值。 4.计算机职称考试Excel2007模块:计数函数COUNT 和COUNTA 功能:计数函数为统计函数,用于统计数字项个数。COUNTA为不含空值。 格式:COUNT/COUNT 参数:可以引用各种类型的数据,最多有55个参数。 COUNT函数计算时会忽略非数字文本和错误值。如果参数是单元格和区域,则只统计引用中的数字、日期值。 COUNTA函数可以计算任意类型参数。如果参数为单元格和区域,则只忽略空白单元格,其他都能被统计。如果不需要统计逻辑值、文字或错误值,就用COUNT函数。

EXCEL常用函数大全

EXCEL常用函数大全(做表不求人!) 2013-12-03 00:00 我们在使用Excel制作表格整理数据的时候,常常要用到它的函数功能来自动统计处理表格中的数据。这里整理了Excel中使用频率最高的函数的功能、使用方法,以及这些函数在实际应用中的实例剖析,并配有详细的介绍。 1、ABS函数 函数名称:ABS 主要功能:求出相应数字的绝对值。 使用格式:ABS(number) 参数说明:number代表需要求绝对值的数值或引用的单元格。 应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。 特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”。

2、AND函数 函数名称:AND 主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。 使用格式:AND(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这30个。 应用举例:在C5单元格输入公式:=AND(A5>=60,B5>=60),确认。如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。 国美提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称:AVERAGE 主要功能:求出所有参数的算术平均值。

EXCEL常用函数公式大全与举例

EXCEL常用函数公式大全及举例 一、相关概念 (一)函数语法 由函数名+括号+参数组成 例:求和函数:SUM(A1,B2,…) 。参数与参数之间用逗号“,”隔开(二)运算符 1. 公式运算符:加(+)、减(-)、乘(*)、除(/)、百分号(%)、乘幂(^) 2. 比较运算符:大与(>)、小于(<)、等于(=)、小于等于(<=)、大于等于(>=)、不等于(<>) 3. 引用运算符:区域运算符(:)、联合运算符(,) (三)单元格的相对引用与绝对引用 例: A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 (一)数学函数 1. 求和 =SUM(数值1,数值2,……) 2. 条件求和 =SUMIF(查找的范围,条件(即对象),要求和的范围) 例:(1)=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思:对第A1栏至A4栏中,大于等于200的数值对应的第B1列至B4列中数值求和 (2)=SUMIF(A1:A4,”<300”,C1:C4)

函数意思:对第A1栏至A4栏中,小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数 =COUNT(数值1,数值2,……) 例:(1) =COUNT(A1:A4) 函数意思:第A1栏至A4栏求个数(2) =COUNT(A1:C4) 函数意思:第A1栏至C4栏求个数 4. 条件求个数 =COUNTIF(范围,条件) 例:(1) =COUNTIF(A1:A4,”<>200”) 函数意思:第A1栏至A4栏中不等于200的栏求个数 (2)=COUNTIF(A1:C4,”>=1000”) 函数意思:第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数 =AVERAGE(数值1,数值2,……) 例:(1) =AVERAGE(A1,B2) (2) =AVERAGE(A1:A4) 6. 四舍五入函数 =ROUND(数值,保留的小数位数) 7. 排位函数 =RANK(数值,范围,序别) 1-升序 0-降序 例:(1) =RANK(A1,A1:A4,1) 函数意思:第A1栏在A1栏至A4栏中按升序排序,返回排名值。 (2) =RANK(A1,A1:A4,0) 函数意思:第A1栏在A1栏至A4栏中按降序排序,返回排名值。 8. 乘积函数 =PRODUCT(数值1,数值2,……) 9. 取绝对值 =ABS(数字) 10. 取整 =INT(数字) (二)逻辑函数

EXCEL公式大全操作应用实例(最全)

EXCEL 公式大全操作应用实例史上最全excel 常用函数公式及技巧搜集从身份证号码中提取出生年月日从身份证号码中提取出性别从身份证号码中进行年龄判断按身份证号号码计算至今天年龄以 2006 年 10 月31 日为基准日按按身份证计算年龄周岁的公式按身份证号分男女年龄段【年龄和工龄计算】根据出生年月计算年龄根据出生年月推算生肖如何求出一个人到某指定日期的周岁?计算距离退休年龄的公式求工龄计算工龄年龄及工龄计算自动算出工龄日期格式为yyyy.mm.dd【时间和日期应用】自动显示当前日期公式如何在单元格中自动填入当前日期如何判断某日是否星期天某个日期是星期几什么函数可以显示当前星期求本月天数显示昨天的日期关于取日期如何对日期进行上、中、下旬区分如何获取一个月的最大天数日期格式转换公式【排名及排序筛选】一个具有 11 项汇总方式的函数 SUBTOTAL自动排序按奇偶数排序自动生成序号如何自动标示A 栏中的数字大小排序?如何设置自动排序重复数据得到唯一的排位序列按字符数量排序排序字母与数字的混合内容随机排序排序的问题怎样才能让数列自动加数一个排序问题数字的自动排序插入后不变根据规律的重复的姓名列产生自动序号排名的函数自动排名公式百分比排名的公式写法为:平均分及总分排名求名次排名排名次根据分数进行普通排名对于普通排名分数相同时,按顺序进行不重复排名依分数比高低名次成绩排名美国式排名中国式排名求最精简的自动排名公式排序后排名位次排名根据双列成绩进行共同排名在双列间排名等次排名不等次排名行小排先)不等次排名行大排先)顺次排名有并列排名无并列排名有并列分段排名无并列分段排名成绩排名如何排名数据排名(隔几行排名)根据分数进行倒排名倒数排名函数是什么如何实现每日各车间产量的排名分数相同时按照一科的分数进行排名筛选后自动产生序列号并汇总如何筛选奇数行函数筛选姓名名次筛选如何实现快速定位(筛选出不重复值)如何请在 N 列中列出 A1:L9 中每列都存在的数值自动为性别编号的问题【文本与页面设置】EXCEL 中如何删除号将字符串中的星号“”替换为其它字符去空格函数如何去掉字符和单元格里的空格怎样快速去除表中不同行和列的空格如何禁止输入空格代替单元格中字符串把单元格中的数字转变成为特定的字符格式把有六百多个单元格的一列,变成一页的多列将 N 列变 M 列公式归纳为一列变四列四列变一列重复四次填充多行数据排成一列将单元格一列分为多列首写字母大写把单元格编号中的小写字母变成大写字母让姓名左右对齐数字居中而小数点又对齐计算指定单元格编号组中非空单元格的数量比较两个单元格内容是否一致怎么样设置才能让这一列的每个单元格只能输入 12 位如何让工作表奇数行背景是红色偶数行背景是蓝色计算特定的一组单元格中,满足条件的单元格的个数把文本格式的数字转换成真正的数字设置页码Excel 表格里如何插入页码的如何设置页脚首页为第 5 页表格的页脚问题无拘无束的页眉打印表头Excel 打印中如何不显示错误值符号对于一些不可打印的字符的处理用那个函数可将个位数前面的零值显示出来如果你要在 A3 的前面插入 100 行请问如何每隔 30 行粘贴一新行在工作表里有连续 10 行数据现在要每行间格 2 行一个大表每一行下面需要加一行空行,怎么加最方便Excel 中插入空白行快速删除工作表中的空行快速删除空行一次删完 Excel 里面多出很多的空白行每 30 行为一页并加上一个标题如何实现如何实现隔行都加上标题项如何把标签页去掉的去掉默认的表格线网线表格的框线列标的标识变了符号的意义双击格式刷竟也能

EXCEL中所有函数的使用2007

EXCEL中所有函数的使用2007-09-05 17:051.求和函数SUM 语法:SUM(number1,number2,...)。 参数:number1、number2...为1到30个数值(包括逻辑值和文本表达式)、区域或引用,各参数之间必须用逗号加以分隔。 注意:参数中的数字、逻辑值及数字的文本表达式可以参与计算,其中逻辑值被转换为1,文本则被转换为数字。如果参数为数组或引用,只有其中的数字参与计算,数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。 应用实例一:跨表求和 使用SUM函数在同一工作表中求和比较简单,如果需要对不同工作表的多个区域进行求和,可以采用以下方法:选中Excel XP“插入函数”对话框中的函数,“确定”后打开“函数参数”对话框。切换至第一个工作表,鼠标单击“number1”框后选中需要求和的区域。如果同一工作表中的其他区域需要参与计算,可以单击“number2”框,再次选中工作表中要计算的其他区域。上述操作完成后切换至第二个工作表,重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。 应用实例二:SUM函数中的加减混合运算 财务统计需要进行加减混合运算,例如扣除现金流量表中的若干支出项目。按照规定,工作表中的这些项目没有输入负号。这时可以构造“=SUM(B2:B6,C2:C9,-D2,-E2)”这样的公式。其中B2:B6,C2:C9引用是收入,而D2、E2为支出。由于Excel不允许在单元格引用前面加负号,所以应在表示支出的单元格前加负号,这样即可计算出正确结果。即使支出数据所在的单元格连续,也必须用逗号将它们逐个隔开,写成“=SUM(B2:B6,C2:C9,-D2,-D3,D4)”这样的形式。 应用实例三:及格人数统计 假如B1:B50区域存放学生性别,C1:C50单元格存放某班学生的考试成绩,要想统计考试成绩及格的女生人数。可以使用公式“=SUM(IF(B1:B50=〃女〃,IF(C1:C50>=60,1,0)))”,由于它是一个数组公式,输入结束后必须按住Ctrl+Shift键回车。公式两边会自动添加上大括号,在编辑栏显示为“{=SUM(IF(B1:B50=〃女〃,IF(C1:C50& gt;=60,1,0)))}”,这是使用数组公式必不可少的步骤。 2.平均值函数A VERAGE 语法:A VERAGE(number1,number2,...)。 参数:number1、number2...是需要计算平均值的1~30个参数。 注意:参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略,但单元格中的零则参与计算。如果需要将参数中的零排除在外,则要使用特殊设计的公式,下面的介绍。

相关主题
相关文档 最新文档