Excel2007函数公式收集了688个实例

5+(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())&\计算本月需要交货的数量:

{=SUM((MONTH(B2:B11)=MONTH(TODAY()))*C2:C11)} 计算8月份笔筒和毛笔的进货数量:

{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1=\笔筒\毛笔\

计算交货起止月:

{=MIN(MONTH(B2:B11))&\月-\月\有几个月要交货:

{=COUNT(0/FREQUENCY(MONTH(B2:B11),MONTH(B2:B11)))} 哪几个月要交货:

{=IFERROR(SMALL(IF(FREQUENCY(MONTH(B$2:B$11),MONTH(B$2:B$11)),MONTH(B$2:B$11)),ROW(A1))&\月\

统计家具类和文具类产品在1月份的出库次数:{=SUM((B2:B11={\文具类\家具类\

计算今年平均每月天数:

{=AVERAGE(DAY(DATE(YEAR(TODAY()),ROW(2:13),0)))}

计算员工转正时间:=DATE(YEAR(B2),MONTH(B2)+3+(DAY(B2)>15),16) 统计本月下旬出库数量:{=SUM(C2:C11*(DAY(B2:B11)>20))} 计算生产速度是否达标:=YEARFRAC(C2,D2)<=(E2/B2) 计算截至今天的利息:=B2*D2*YEARFRAC(C2,NOW()) 计算还款日期:=TEXT(EDATE(B2,C2),\计算2008年到2010年共有多少天:

{=SUM(DAY(EDATE(\

提示合同续约:=TEXT(EDATE(B2,C2*12)-TODAY(),\合同过期;[<=10]即将到期;;\

计算借款日期到本月底的天数:

=EOMONTH(B2,MONTH(TODAY())-MONTH(B2))-B2 计算本季度天数:

=SUM(DAY(EOMONTH(NOW(),{0,1,2}-MOD(MONTH(NOW())-1,3)))) 生成工资结算日期:=TEXT(EOMONTH(B2,0)+1,\年M月D日\统计两倍工资的加班小时数:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&\AAA\六\

计算员工工作天数和月数:=DATEDIF(B2,C2,\根据进厂日期计算员工可假休天数:

=MIN(IF(DATEDIF(B2,TODAY(),\=12,3,3+2*(DATEDIF(B2,TODAY(),\

根据身份证号码计算年龄(包括年月天):

=CONCATENATE(DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),\AY(),\年\月\天\

计算年资:

=10*MIN(DATEDIF(B2,TODAY(),\)-15,0)*5

计算临时工的工资:

=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,\分\小时\

计算本日工时工资:

=(HOUR(C2-TIMEVALUE(\ALUE(\))*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

=IF(HOUR(B2)>=18,-(ROUNDUP((HOUR(B2-\,0))*3,(ROUNDDOWN((HOUR(\

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

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

汇总第一个星期的出库数量:

{=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(\\

按周汇总产量:

{=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(\()),MONTH(NOW()),ROW(INDIRECT(\

W()),\

计算周末奖金补贴:

=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B2&\罗列值班日期:

{=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(\

今天是本年度第几周:=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)),\

统计某月第四周的支出金额:

{=SUM((WEEKNUM(A2:A11*1,1)-WEEKNUM(YEAR(A2:A11)&\TH(A2:A11)&\

判断本月休息日:

{=(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,{\计算工程完工日期:

{=WORKDAY(A2,B2,EOMONTH(A2,ROW(INDIRECT(\)))}

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

=NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{\

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

=EOMONTH(NOW(),3-MONTH(NOW()))-EOMONTH(NOW(),-MONTH(NOW()))-NETWORKDAYS(EOMONTH(NOW(),-MONTH(NOW()))+1,EOMONTH(NOW(),3-MONTH(NOW())),{\

计算今天离国庆节还有多少个工作日:

=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),10,1)),10,1))

填充12个月的月份名:

=CONCATENATE(\第\月\产生“坐标”:=CHAR(64+COLUMN(A1)) 检查日仓库报表日期是否正确:

{=IF(SUM(N((11-RANK(A2:A11,A2:A11))=(ROW(2:11)-1)=FALSE)),\非递增\递增\

检查字符串中哪一个字符出现次数最多:

{=CHAR(MODE(IFERROR(CODE(MID(A2,ROW(1:16),1)),\产生每两行累加1的编号:

=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,\最后一次不及格是哪次测试:

{=INDEX(A:A,MAX((B2:B11<60)*ROW(2:11)))} 计算第11名到第30名学员的平均成绩:

{=AVERAGE(IF(RANK(B2:B101,B2:B101)=TRANSPOSE(ROW(11:30)),B2:B101))}

计算成绩排名，不能产生并列名次:

=SUMPRODUCT(--((A$2:A$15=A2)*(($C$2:$C$15)+1/ROW($C$2:$C$15))>C2+1/ROW(2:2)))+1

计算第一次收入金额大于30元时的金额是多少:

=INDEX(B:B,MIN(IF((A2:A11=A2)*(B2:B11>30),ROW(2:11)))) 计算扣除所有扣款后的最高薪资:

{=MAX(B2:B10-MMULT(C2:G10*1,ROW(1:5)^0))} 对班级和成绩升序排列:

{=1*MID(SMALL(1*($A2:$A12&TEXT($B2:$B12,\2)-1),{1,2},{1,3})}

罗列今日销售的诺基亚手机型号:

{=T(INDEX(B:B,SMALL(IF(ISERROR(FIND(\诺基亚\

统计图书数量:

{=IF(B2=\罗列第一名学生姓名:

{=T(INDEX(A:A,SMALL(IF($B$2:$B$11=MAX(B$2:B$11),ROW($2:$11),12),ROW(A1))))}

罗列1到1000之间的质数:

{=INDEX(A:A,SMALL(IF(A$2:A$1000<>\)))&\

判断某数是否为质数: {=IF(A2<2,\非质非合\A2))),2)=A2,\质数\合数\

计算某个数的约数个数及罗列所有约数:

约数个数{=COUNT(0/(MOD(A2,ROW(INDIRECT(\罗列约数

{=IFERROR(SMALL(IF(MOD(A$2,ROW(INDIRECT(\NDIRECT(\

将六个号码组合成一个:{=SUM(B1:B6*10^(2*(ROWS(B1:B6)-ROW(1:6))))} 将每个人的贷款重新分组:

{=INDEX($C:$C,SMALL(IF($A$2:$A$11=$E2,ROW($2:$11),ROWS($1:$12)),COLUMN(A1)))}

检测每个志愿是否与之前的重复:=MATCH(B2,$B$2:$B$10,)<>ROWS($2:2) 将列标转换成数字:=COLUMN(INDIRECT(A2&1))