\多:{=TEXT(MODE(B2:B9*1),\ 罗列出被投诉多次的工作人员编

哪种产品生产次数最

号:{=IFERROR(TEXT(MODE(IF(COUNTIF($D$1:D1,$B$2:$B$11)=0,$B$2:$B$11*1)),\ 对学生成绩排名:=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=\成功\ 计算三个不连续区间的频率分

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

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

计算小学加初中人数及中专加大学人数:{=FREQUENCY((B2:B11<>\小学\初中\ 计算文本的频率分布:{=FREQUENCY(CODE(B2:B11),CODE(D2:D5))} 夺 冠排行榜:{=IF(ROW(A1)>

SUM(1/COUNTIF($B$2:$B$11,$B$2:$B$11)),\($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),ROW($2:$11),1048576),ROW(A1)))&\记录当前日期与时间:=TEXT(NOW(),\月d日 h:m:s\确定是否已到加油时间:=TEXT(NOW()-B2,\国庆倒计时:=TEXT(\ 统计发货到收款天数:=ROUNDUP(IF(B2<>\统计已到达收款时间的货品数量:=COUNTIF(B2:B10,\

本月需要完成几批货物生产:{=SUM(N(B2:B11=TEXT(TODAY(),\ 计算本季度收款的合

计:{=SUM(IF(ROUNDUP(B2:B11/3,0)=ROUNDUP(TEXT(TODAY(),\ 判 断今年是否闰年:=OR((MOD(TEXT(TODAY(),\

(MOD(TEXT(TODAY(),\ 计算2008年有多少个星期

日:{=SUM(N(TEXT(DATE(2008,1,ROW(INDIRECT(\日\ 计算本月有多少天:=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),\确定今年母亲节的日 期:=DATE(YEAR(TODAY()),5,14-WEEKDAY(DATE(YEAR(TODAY()),4,30),2)) 今 年包含多少个星 期:{=SUM(N(WEEKDAY(DATE(YEAR(TODAY()),1,ROW(INDIRECT(\

(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())&\计算本月需要交货的数量:{=SUM((MONTH(B2:B11)=MONTH(TODAY()))*C2:C11)} 计算8月份笔筒和毛笔的进货数量:{=SUM(IF(MONTH(A2:A11)=8,IF((B1:H1=\笔筒\毛笔\

计算交货起止月:{=MIN(MONTH(B2:B11))&\月-\月\年 \\天\

计算年资:=10*MIN(DATEDIF(B2,TODAY(),\ 计算临时工的工资:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B2,\分\小时\ 计算本日工时工

资:=(HOUR(C2-TIMEVALUE(\计算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-\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=\支出\ 汇总第一个星期的出库数

量:{=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))} 按周汇总产

量:{=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(\ONTH(NOW(),0))))),2)=2,DATE(YEAR(NOW()),MONTH(NOW()),ROW(INDIRECT(\NOW(),0)))))),ROW()),\ 计算周末奖金补

贴:=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(\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)),\ 统计某月第四周的支出金

额:{=SUM((WEEKNUM(A2:A11*1,1)-WEEKNUM(YEAR(A2:A11)&\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,{\

计算工程完工日期:{=WORKDAY(A2,B2,EOMONTH(A2,ROW(INDIRECT(\ 计算2008年第一季度有多少个工作